Sign in Get started

Free course · 22 lessons · Intermediate

ETL and Data Pipelines

Build data pipelines that are correct, re-runnable and cheap to operate, through real production problems: ETL vs ELT, batch vs streaming, incremental extraction and change data capture, idempotent loads, late data, backfills, deduplication, star schemas and slowly changing dimensions, warehouses and dbt, orchestration, data quality, schema evolution, monitoring, partitioning, cost and privacy, with runnable Python and SQL in every lesson.

Start the course

1. Pipeline Foundations

What a data pipeline does and how to shape one: ETL versus ELT, batch versus streaming, incremental extraction with watermarks, and change data capture.

  1. ETL vs ELT: Where the Transform Happens — Why a nightly job that transforms data before loading it gets slower every month and can't answer new questions, and when loading raw data first (ELT) is the better shape.
  2. Batch, Micro-Batch or Streaming? — How a vague request for 'real-time' data leads to an expensive streaming platform nobody needed, and how to choose a pipeline style from a measurable freshness requirement.
  3. Incremental Extraction and Watermarks — Why 'WHERE updated_at > last run' silently misses rows, and how overlap windows, idempotent loads and commit-ordered positions make incremental extraction safe.
  4. Change Data Capture for Pipelines — Why polling tables misses deletes and overloads the primary database, and how log-based change data capture streams every insert, update and delete in commit order.

2. Loading Data Correctly

Make every load safe to repeat: idempotent upserts and partition overwrites, late and out-of-order data, backfills, and deduplication.

  1. Idempotent Loads: Safe to Run Twice — Why rerunning a failed job doubled last Tuesday's revenue, and how upserts and partition overwrites make every load safe to repeat.
  2. Late and Out-of-Order Data — Why yesterday's totals keep changing after the report was sent, and how event time, lookback windows and 'provisional' numbers handle data that arrives late.
  3. Backfills and Reprocessing — How a well-meant backfill doubled history and collided with the nightly job, and how partition-level tasks, an orchestrator and run limits make reprocessing safe.
  4. Deduplication and the Exactly-Once Myth — Why event counts were inflated by retried deliveries, and how to deduplicate with keys and window functions instead of hoping for exactly-once delivery.

3. Modelling the Warehouse

Shape data so it answers questions correctly and cheaply: grain and star schemas, slowly changing dimensions, warehouses and lakehouses, and SQL transformations managed with dbt.

  1. Star Schemas and Grain — Why revenue doubled after someone joined orders to order lines, and how declaring the grain of every table and modelling facts and dimensions prevents fan-out errors.
  2. Slowly Changing Dimensions — Why last year's sales by region changed when a customer moved city, and how Type 2 dimensions keep history so past reports stay true.
  3. Warehouses, Lakehouses and Scan Cost — Why one dashboard tile cost thousands a month, and how columnar storage, partitioning and clustering decide how much data each query reads.
  4. Managing SQL Transformations with dbt — Why a folder of SQL scripts run by cron produced stale numbers, and how dbt builds a dependency graph from ref(), tests models and makes transformations reviewable.

4. Running Pipelines in Production

Keep pipelines on time and trustworthy: orchestration and dependencies, data quality checks and contracts, schema evolution, monitoring freshness and volume, and publishing data atomically.

  1. Orchestration: Dependencies, Not Clock Times — Why a cron schedule that 'leaves an hour of buffer' produced half-empty reports on busy days, and how an orchestrator runs steps when their inputs are ready.
  2. Data Quality Checks and Data Contracts — How a renamed column upstream turned revenue into zero while every job stayed green, and how automated checks and data contracts catch bad data before people do.
  3. Schema Evolution — Why a source team changing an ID from a number to a string broke the nightly load, and which schema changes are safe, which break consumers, and how to roll them out.
  4. Monitoring Freshness, Volume and Data SLOs — Why a pipeline 'succeeded' every night for a week while loading zero rows, and how freshness, volume and distribution monitors with clear owners catch silent failures.
  5. Publishing Data Atomically — Why the morning dashboard sometimes showed half a day of sales, and how staging tables, validation and atomic swaps make readers see either the old data or the new data, never a mix.

5. Scale, Cost and Privacy

Keep pipelines fast, affordable and compliant as data grows: file sizes and formats, incremental processing and cost control, and handling personal data and deletion requests.

  1. Small Files and File Formats — Why queries on a data lake got slower every week while data volume barely grew, and how file formats, file sizes and compaction keep lakes fast.
  2. Controlling Pipeline Cost — Why the warehouse bill grew faster than the data, and how incremental processing, right-sized schedules and cost attribution keep pipelines affordable.
  3. Personal Data, Privacy and Retention — Why a single deletion request took three weeks and still missed copies, and how minimising, pseudonymising and tracking personal data makes privacy obligations practical.

6. Capstone Case Studies

Diagnose and redesign real pipelines end to end, with production constraints.

  1. Capstone: The Revenue Report That Didn't Match — Investigate why the warehouse's revenue is 4% higher than the payment provider's settlements, find every cause, and redesign the pipeline so it can't happen again.
  2. Capstone: From Nightly Batch to Near-Real-Time CDC — Plan the migration of a nightly full-copy pipeline to change data capture with 5-minute freshness, without losing data or breaking existing reports.