Guides

Data engineering guides

These are the must-read guides for learning data engineering. Each one covers a single important topic with a small example you can check by eye. Every query and every output shown was run before it was published, and each guide ends with problems you can solve in the browser. For short answers to single questions, use the interview question bank; for a full path, start with the Learn tracks.

New to data engineering? Start here

10 guides in the order to read them. Each one builds on the one before it, and none assumes any experience.

Data engineer vs data analyst vs data scientist: the differences, explained simplyCareer
SQL for beginners: write your first queries with SELECT, WHERE and ORDER BYSQL
SQL GROUP BY and HAVING explained for beginners, with real outputSQL
SQL joins explained for beginners: INNER, LEFT, RIGHT and FULL with real outputSQL
Python for data engineering beginners: what to learn first, with runnable codePython
pandas for beginners: filter, group, merge and clean a table in PythonPython
What is a data pipeline? A beginner's guide with a tiny example in SQLPipelines
What is ETL? ETL vs ELT explained for beginners, with a runnable examplePipelines
How to become a data engineer as a fresher: a step-by-step roadmapCareer
Data engineering projects for beginners: 6 projects to build, in orderCareer

SQL

4 min read6 sectionsPrimary key vs foreign key explained for beginners, with SQL examplesA primary key identifies 1 row. A foreign key points to a row in another table. Together they are how tables stay connected. This guide creates both, then breaks them on purpose so you can see the errors.Read the guide
4 min read7 sectionsDELETE vs TRUNCATE vs DROP in SQL: the differences, with examplesThree commands that all make data disappear, and a very common interview question. This guide runs each one on the same small table so you can see exactly what is left afterwards.Read the guide
4 min read8 sectionsUNION vs UNION ALL in SQL: what is the difference?UNION and UNION ALL both stack the results of 2 queries on top of each other. The only difference is duplicates, and that one difference can quietly make your totals wrong. This guide shows how.Read the guide
4 min read7 sectionsSQL for beginners: write your first queries with SELECT, WHERE and ORDER BYYou do not need any experience. This guide starts with what a table is, then writes 10 small queries one at a time, and shows exactly what each one returns.Read the guide
5 min read8 sectionsSQL joins explained for beginners: INNER, LEFT, RIGHT and FULL with real outputReal data lives in more than one table. A join puts 2 tables side by side using a column they share. This guide runs every join type on the same two small tables, so you can see exactly which rows each one keeps.Read the guide
4 min read9 sectionsSQL GROUP BY and HAVING explained for beginners, with real outputAn aggregate like SUM adds up the whole table. GROUP BY splits the table into groups first, so you get one answer per city or per customer. This guide shows it step by step, including the difference between WHERE and HAVING.Read the guide
4 min read7 sectionsSQL subquery vs CTE for beginners: what they are and when to use eachSome questions need 2 steps: first work out the average, then find who is above it. A subquery and a CTE are two ways to write the steps in one query. This guide shows both, side by side, with real output.Read the guide
4 min read7 sectionsSQL NULL explained for beginners: IS NULL, COALESCE and the trapsNULL means there is no value, and it behaves unlike anything else in SQL. This guide shows, on a small table, why = NULL finds nothing, what aggregates do with missing values, and how to replace them.Read the guide
8 min read11 sectionsSQL interview questions for data engineers: joins, NULLs, pivots and hierarchies9 questions that come up in data engineering SQL rounds, each run on a small table: the join row counts, the NULL that breaks NOT IN, the self join, the pivot and the recursive hierarchy.Read the guide
7 min read8 sectionsHow to find and remove duplicate rows in SQL, without deleting the wrong onesA sign-ups table says 8 rows, but only 4 real people signed up. This guide finds the duplicates, shows which method fixes which kind, deletes them safely and stops them coming back.Read the guide
10 min read12 sectionsSQL window function interview questions, with worked answers10 window function questions that come up in data engineering SQL rounds, each answered on the same small table, with the real output and the tie or missing-date trap that interviewers are testing.Read the guide

Python

5 min read9 sectionsPython asyncio for data ingestion: gather, semaphores and timeoutsMost of the time spent pulling data from an API is waiting. Learn to wait for many calls at once, keep the results in order, stay inside the API's limits and survive a failed call.Read the guide
4 min read11 sectionsPython for data engineering beginners: what to learn first, with runnable codeYou do not need all of Python. For data engineering, a small part of it does most of the work. This guide teaches that part on a tiny CSV, one step at a time, and ends with a 10-line pipeline.Read the guide
4 min read11 sectionspandas for beginners: filter, group, merge and clean a table in Pythonpandas is how Python works with tables. If you know a little SQL, you already know most of it. This guide does 10 everyday table jobs in pandas, shows the real output of each, and puts the SQL equivalent beside it.Read the guide
5 min read11 sectionsPython interview questions for freshers: 10 common questions with runnable answers10 questions that come up again and again in fresher Python interviews. Each has a plain-English answer, a short piece of code, its real output, and a note on what the interviewer is really checking.Read the guide

Data modeling

5 min read7 sectionsDatabase normalization explained for beginners: 1NF, 2NF and 3NF with SQLStart with one big messy table. Watch it break when data changes. Then fix it in 3 steps, 1NF, 2NF and 3NF, until every fact is stored in exactly one place.Read the guide
6 min read6 sectionsStar schema vs snowflake schema: the same report built both waysBoth designs answer the same questions. This guide builds a small sales warehouse as a star and as a snowflake, runs one report on each, and shows what the extra tables cost and what they protect.Read the guide
9 min read8 sectionsSCD Type 2 explained with SQL: keep the history when a customer changes cityA customer moves from Pune to Bengaluru. Overwrite the city and last month's report changes. This guide builds a Type 2 dimension step by step, loads a change, joins facts to the right version and checks the table is healthy.Read the guide

Architecture

4 min read5 sectionsData warehouse vs data lake vs lakehouse: the differences, explained simply3 words that sound the same and mean different things. This guide explains each with an everyday comparison, puts them side by side in a table, and shows how they fit together in a real company.Read the guide
3 min read4 sectionsOLTP vs OLAP explained for beginners, with SQL examplesA shop's order system and its sales dashboard use the same data in very different ways. OLTP is for recording things quickly, and OLAP is for analysing them. This guide shows both kinds of query and why they need different systems.Read the guide
4 min read5 sectionsCSV vs JSON vs Parquet: which file format should you use?Data has to be stored in files somewhere. CSV, JSON and Parquet are the three you will meet first, and each is good at something different. This guide explains how they store data and which to choose.Read the guide
7 min read8 sectionsMedallion architecture (bronze, silver, gold) explained with a runnable exampleNine messy payment rows arrive from 3 files. This guide moves them through bronze, silver and gold in SQL, sets the bad rows aside instead of losing them, and proves that the row counts add up.Read the guide

Pipelines

4 min read7 sectionsWhat is Apache Airflow? A beginner's guide to DAGs, tasks and schedulingA data pipeline has to run every night, in the right order, and tell someone when it breaks. Apache Airflow is the tool most teams use to do that. This guide explains the ideas, shows a small example, and says when you do not need it.Read the guide
4 min read6 sectionsWhat is dbt? A beginner's guide to dbt models, tests and dbt builddbt turns the SQL you already know into a tested, organised pipeline inside your warehouse. This guide explains what a dbt model is, how models connect, what its tests check, and when to use run, test and build.Read the guide
5 min read8 sectionsWhat is Apache Kafka? A beginner's guide to topics, partitions and consumer groupsWhen many systems need to share a stream of events, connecting each one to each other becomes a tangle. Kafka sits in the middle as a durable log of events. This guide explains its few core ideas with simple pictures.Read the guide
4 min read6 sectionsWhat is ETL? ETL vs ELT explained for beginners, with a runnable exampleETL and ELT are two ways to move data from where it is created to where people can use it. The letters are the same, and the order is different. This guide explains why that matters, and runs a small ELT example.Read the guide
3 min read6 sectionsWhat is a data pipeline? A beginner's guide with a tiny example in SQLA data pipeline is the path data takes from where it is created to where it is used, set up so it runs by itself. This guide explains each part, runs a tiny one in SQL, and shows the most important rule: running it twice must be safe.Read the guide
4 min read6 sectionsBatch vs stream processing explained for beginners, with a SQL exampleShould the sales total update once a night, or every time an order arrives? That question is the difference between batch and stream processing. This guide shows both on the same 6 events.Read the guide

PySpark

5 min read7 sectionsspark.sql.shuffle.partitions explained: what it does and how to set itThe default of 200 is right for some jobs and wasteful or too small for others. See what the setting controls on a tiny job, then learn how to reason about the number.Read the guide
4 min read7 sectionsPySpark collect_list vs collect_set, and explode vs explode_outerTwo functions turn many rows into one array, and two turn an array back into many rows. Each pair differs in one small way that changes your row counts, and interviewers ask about both.Read the guide
11 min read13 sectionsPySpark production problems: data skew explained with visualsA visual guide to slow Spark jobs, partitions and performance troubleshooting. Start with a real problem, see what goes wrong inside Spark, then fix it step by step and learn how to explain it in an interview.Read the guide
4 min read7 sectionsWhat is Apache Spark? A beginner's guide to how it worksWhen data is too big for one computer, the work has to be shared. Spark does that sharing for you. This guide explains how it works, in plain terms, and when you should not use it.Read the guide
10 min read9 sectionsScenario-based PySpark interview questions, with runnable answersEight pipeline problems interviewers describe as stories, each reproduced on a small DataFrame with the real Spark output, the cause, and the fix you would explain.Read the guide

Career

4 min read7 sectionsData engineer vs data analyst vs data scientist: the differences, explained simply3 job titles that sound alike and are easy to mix up. This guide explains what each person does in a normal working day, what they need to know, and how a beginner can decide which one to aim for.Read the guide
5 min read9 sectionsData engineering projects for beginners: 6 projects to build, in orderA project is the best proof that you can do the job. This guide gives you six, from a SQL report to a scheduled pipeline, in the order to build them, with what each one teaches and how to explain it in an interview.Read the guide
9 min read10 sectionsHow to become a data engineer as a fresher: a step-by-step roadmapMost roadmaps are a long list of tools. This one is an order: 5 stages, what to learn in each, what to build, and how to tell when you can move on. It is written for a student or fresher starting from SQL and Python.Read the guide