SQL for Data Engineers (DE-102)

You already write SELECT, WHERE, and GROUP BY without thinking. Then a pipeline you inherited starts producing revenue that is quietly double what finance reports, a nightly deduplication step takes 40 minutes, and a query that was instant on the sample data crawls on the real table. None of these are "advanced SQL" in the interview-question sense. They are the everyday SQL of a data engineer — and this course is about that SQL.

DE-102 takes you past the analyst's dialect into the query patterns that live inside pipelines: the joins that reconcile two systems, the window functions that deduplicate a change feed, and the execution plans that tell you why a query is slow before you guess. You will finish able to write and reason about the SQL that transforms data on its way from source to serving.

🎯 What you'll learn

• Choose the correct join type for a pipeline task and explain how a join can silently drop or duplicate rows
• Use set operations to reconcile two datasets and find what is missing from each
• Write aggregation queries and window functions, and say precisely how they differ
• Deduplicate a dataset to one row per key using ROW_NUMBER()
• Read a query execution plan and tell a full scan from an indexed lookup
• Add an index and prove from the plan that the engine now uses it

Who this course is for

DE-102 is a Foundation course in the School of Data Engineering. It is written for two kinds of learner:

• The analyst crossing over who writes SQL daily, is comfortable in a BI tool, and now needs the query patterns that pipelines depend on.
• The backend engineer pivoting into data work who knows how databases work but has not written SQL as a transformation language.

Either way, the assumption is that SELECT, WHERE, ORDER BY, and a basic GROUP BY are familiar. Everything past that, this course builds.

Prerequisites

• DE-101 Data Engineering Foundations is recommended for the pipeline vocabulary (source, ingestion, transformation, serving) this course leans on.
• Comfort reading and writing basic SQL: SELECT, WHERE, ORDER BY, and a simple GROUP BY.
• Python 3.10 or newer for the lab. It uses only the standard-library sqlite3 module — nothing to install, no server, no cost.

You do not need a database server, a cloud account, or any paid tool. Practice runs in the SQL Kingdom playground, and the lab runs entirely on your machine.

Modules

DE-102 is roughly fifteen hours of effort across seven modules. This slice of the course delivers three core lessons — the pipeline joins, window functions, and execution-plan reading at the heart of the course — plus a hands-on lab that pulls them together.

#	Module	What you leave with
1	DDL and schema lifecycle	CREATE, ALTER, constraints, and migrations
2	Transactions and isolation	What ACID buys a pipeline
3	Advanced joins and set operations	Reconciling datasets without dropping or doubling rows
4	Window functions and deduplication	One row per key, running totals, and rankings
5	Incremental patterns	MERGE/upsert, watermarks, and idempotent loads
6	Reading execution plans	Scans, searches, indexes, and where SQL is the bottleneck
7	SQL in production	Parameterization, injection, and testing queries

The three lessons and the lab below cover the core of Modules 3, 4, and 6 and give you the analytical-query skill the rest of the course extends.

Outcomes

By the end of this course slice you can:

• Reconcile two systems with a join or a set operation and name exactly which rows each approach keeps.
• Collapse a table with aggregation, or keep every row and annotate it with a window function, and choose the right one on purpose.
• Deduplicate a messy feed to one authoritative row per key.
• Read an execution plan, spot a full table scan, add an index, and confirm the engine now searches instead of scanning.

Where this leads

The analytical queries you write here are the transformation logic of a real pipeline. In DE-201 you will design the schemas these queries fill, and in DE-202 you will express the same join, aggregation, and deduplication logic at scale in PySpark. The SQL judgment you build in DE-102 — which join, when to use a window, whether an index will help — carries straight into both.

tip

Do the lessons in order. Joins come first because the window-function and performance lessons both build on multi-table queries, and the lab assembles all three into a single analytical query you run yourself.