The Data Engineering Picture Book
sources app DB APIs logs ingestbatch/stream lakeraw files transformclean · model useBI · ML Data engineering = reliable, repeatable movement of data get data out of messy sources · land it safely · shape it for questions make it correct, fresh, documented and cheap · fix it when it breaks at 3 a.m. analysts and data scientists depend on it being right
PLATE 0 — Data work is plumbing: get it from where it lives to where it's useful, clean.
A picture book for grown-ups · with SQL and Python

Data Engineering

Nine spreads: pipelines, ingestion, storage, SQL modelling, transformation with dbt, orchestration with Airflow, streaming, data quality, and the modern stack.

Say it like a pro

"ELT into a warehouse, modelled in layers with dbt, orchestrated by Airflow, tested for quality, and observable end to end."

Use the tabs or the ← → keys.

ETL (older) Extract Transform (server) Load ELT (modern) Extract Load raw Transform in the warehouse (SQL) Batch vs streaming batch: process chunks on a schedule (hourly, daily) · simple, cheap, minutes to hours late streaming: process events as they arrive · seconds late, more moving parts Why ELT won cloud warehouses are cheap and fast · raw data kept, so you can re-transform later transformations are just SQL in git, with tests
PLATE 1 — Land the raw data first. Shape it afterwards, where the compute is.
Chapter 1 · Pipelines, ETL & ELT

The shape of every data pipeline

source
operational DBs, SaaS APIs, files, events, logs
idempotent
re-running a job gives the same result (essential for retries and backfills)
backfill
reprocess historical data after a bug or schema change
freshness / SLA
how late the data may be; set it with the people who use it
lineage
where each column came from and what depends on it
# the smallest honest pipeline: extract → load raw → transform with SQL (Python + DuckDB)
import duckdb, requests, pandas as pd
rows = requests.get("https://api.example.com/orders?since=2026-10-01", timeout=30).json()
con = duckdb.connect("warehouse.duckdb")
con.execute("CREATE SCHEMA IF NOT EXISTS raw")
con.register("df", pd.DataFrame(rows))
con.execute("CREATE OR REPLACE TABLE raw.orders AS SELECT *, now() AS _loaded_at FROM df")
con.execute("""
  CREATE OR REPLACE TABLE daily_revenue AS
  SELECT date_trunc('day', created_at) AS day, sum(total_cents)/100.0 AS revenue
  FROM raw.orders WHERE status = 'paid' GROUP BY 1 ORDER BY 1""")
print(con.sql("SELECT * FROM daily_revenue LIMIT 5"))
Say it like a pro

"Extract, load raw with a load timestamp, transform in SQL, and every step is idempotent so we can re-run safely."

Full refreshcopy everythingsimple, small tables IncrementalWHERE updated_at > lastmisses deletes CDCread the DB loginserts, updates, deletes Reliable ingestion habits keep a watermark (last timestamp/ID loaded) in state, not in someone's head overlap windows a little and dedupe on the primary key land raw data unchanged, add load metadata (_loaded_at, _source_file) handle schema drift: new columns shouldn't break the load respect API rate limits · retry with backoff · paginate Tools so you don't write every connector Airbyte · Fivetran · dlt · Meltano · Debezium (CDC) · Kafka Connect
PLATE 2 — Copy it all, copy what changed, or listen to the database's own change log.
Chapter 2 · Ingestion

Getting data in, incrementally

-- incremental load with a watermark and an upsert (PostgreSQL warehouse or staging)
-- 1. read the last position
SELECT coalesce(max(updated_at), '1970-01-01') AS wm FROM raw.orders;

-- 2. extract from source:   SELECT * FROM orders WHERE updated_at > :wm - interval '5 minutes'   (small overlap)

-- 3. upsert so reruns and overlaps are harmless
INSERT INTO raw.orders (id, status, total_cents, updated_at)
VALUES (...)
ON CONFLICT (id) DO UPDATE
  SET status = EXCLUDED.status, total_cents = EXCLUDED.total_cents, updated_at = EXCLUDED.updated_at
  WHERE raw.orders.updated_at < EXCLUDED.updated_at;
# dlt: declarative ingestion with schema inference and incremental state
import dlt
@dlt.resource(primary_key="id", write_disposition="merge")
def orders(updated_at=dlt.sources.incremental("updated_at", initial_value="2026-01-01")):
    for page in api_pages("/orders", since=updated_at.last_value):
        yield page
dlt.pipeline("shop", destination="duckdb", dataset_name="raw").run(orders())
CDC
captures deletes and every change; needs DB log access (Postgres logical replication, MySQL binlog)
file formats
CSV (simple, ambiguous) · JSON (flexible, bulky) · Parquet (columnar, compressed, typed: the default)
Say it like a pro

"Incremental with a watermark and overlap, upserted by key, and CDC where we need deletes."

Row store (OLTP) fast: read/write one record Column store (OLAP) fast: scan a few columns over billions of rows Medallion layers bronzeraw, as landed silvercleaned, typed goldbusiness-ready marts lake: cheap files (Parquet) in S3/GCS · warehouse: managed SQL tables (BigQuery, Snowflake, Redshift) lakehouse: table formats (Delta, Iceberg, Hudi) add transactions on lake files small team? Postgres or DuckDB goes surprisingly far
PLATE 3 — Analytics reads a few columns of many rows, so store data by column.
Chapter 3 · Storage: lake, warehouse & formats

Where the data rests

$ pip install duckdb pyarrow pandas
$ python - <<'EOF'
import duckdb
duckdb.sql("COPY (SELECT * FROM read_csv_auto('orders.csv')) TO 'orders.parquet' (FORMAT parquet, COMPRESSION zstd)")
print(duckdb.sql("SELECT status, count(*) FROM 'orders.parquet' GROUP BY 1"))       -- query files directly
EOF
$ ls -lh orders.csv orders.parquet                                 # Parquet is usually several times smaller
# partitioned Parquet in a lake: skip whole folders when filtering
s3://acme-lake/silver/orders/
    year=2026/month=10/day=29/part-0001.parquet
    year=2026/month=10/day=30/part-0001.parquet
-- WHERE day = '2026-10-30' reads one folder, not everything
NeedReach for
app transactionsPostgreSQL / MySQL (row store)
analytics, BIBigQuery · Snowflake · Redshift · ClickHouse · DuckDB
cheap raw storageS3 / GCS / ADLS + Parquet
big-data computeSpark · Trino · Databricks
partitioning
split by a common filter (date); choose columns that queries actually use
small-files problem
thousands of tiny files slow everything; compact them
Say it like a pro

"Raw lands as Parquet partitioned by date, gets cleaned in silver, and business marts live in the warehouse."

fact_ordersorder_id · amounts+ foreign keys dim_customername · segment dim_productcategory · brand dim_dateday · month · qtr dim_storeregion · size facts = events/measures · dimensions = who, what, when, where star schema: simple joins, fast scans, analyst-friendly
PLATE 4 — Measurements in the middle, descriptions around them.
Chapter 4 · Data modelling

Facts, dimensions, and grain

State the grain first: "one row per order line". Everything else follows. Mixed grain is the root of most wrong numbers.

CREATE TABLE dim_customer (
  customer_key BIGINT PRIMARY KEY,           -- surrogate key
  customer_id  TEXT NOT NULL,                -- natural/business key
  name TEXT, segment TEXT,
  valid_from DATE, valid_to DATE, is_current BOOLEAN   -- SCD type 2: keep history
);
CREATE TABLE fact_order_lines (
  order_id TEXT, line_no INT,
  date_key INT, customer_key BIGINT, product_key BIGINT,
  qty INT, revenue_cents BIGINT,
  PRIMARY KEY (order_id, line_no)            -- the grain
);

-- the question a star schema answers well
SELECT d.month, p.category, c.segment, sum(f.revenue_cents)/100.0 AS revenue
FROM fact_order_lines f
JOIN dim_date d USING (date_key)
JOIN dim_product p USING (product_key)
JOIN dim_customer c USING (customer_key)
WHERE c.is_current
GROUP BY 1, 2, 3 ORDER BY revenue DESC;
SCD
slowly changing dimension: overwrite (1), version rows (2), or add columns (3)
wide tables / OBT
pre-joined "one big table" for BI speed; fine on columnar engines
data vault / 3NF
alternative integration layers for many sources
Say it like a pro

"Grain declared first, facts for measures, conforming dimensions, and history kept with SCD 2 where it matters."

raw.orders raw.users raw.events stg_orders stg_users stg_events int_orders_users fct_orders dim_users What dbt gives you SQL models in git · dependency graph via ref() · incremental builds tests (unique, not_null, relationships) · documentation and lineage Layers staging: rename, cast, dedupe, 1:1 with sources · intermediate: joins · marts: for the business
PLATE 5 — SQL files that build on each other, tested and documented like code.
Chapter 5 · Transformation with dbt

SQL as software

$ pip install dbt-duckdb        # or dbt-postgres, dbt-bigquery, dbt-snowflake
$ dbt init shop && cd shop
$ dbt run ; dbt test ; dbt build        # run models, run tests, or both in order
$ dbt docs generate && dbt docs serve    # browsable lineage graph
-- models/staging/stg_orders.sql
select
  id::bigint           as order_id,
  user_id::bigint      as user_id,
  status,
  total_cents / 100.0  as total,
  created_at::timestamp as created_at
from {{ source('raw', 'orders') }}
where id is not null

-- models/marts/fct_orders.sql  (incremental: only process new rows)
{{ config(materialized='incremental', unique_key='order_id') }}
select o.order_id, o.created_at, u.segment, o.total
from {{ ref('stg_orders') }} o
join {{ ref('stg_users') }} u using (user_id)
{% if is_incremental() %} where o.created_at > (select max(created_at) from {{ this }}) {% endif %}
# models/marts/schema.yml: tests are assertions about the data
models:
  - name: fct_orders
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: segment
        tests: [{ accepted_values: { values: [free, pro, team] } }]
Say it like a pro

"Staging, intermediate and mart layers in dbt, incremental where big, with tests on keys and accepted values."

extract load dbt run dbt test ✘ notify failed test stops downstream tasks and pages someone What an orchestrator does schedule jobs · order them by dependency · retry · alert · show history backfill a date range with one command Design rules tasks small, idempotent, and parameterized by the run date (not "now") no heavy work in the scheduler; run it in workers, containers, or the warehouse pass references (paths, table names) between tasks, not big data tools: Airflow · Dagster · Prefect · cloud schedulers (Cloud Composer, MWAA)
PLATE 6 — The scheduler is a conductor: it doesn't play, it keeps everyone in time.
Chapter 6 · Orchestration

Run it on time, in order, and retry sensibly

# dags/daily_shop.py (Airflow 2.x TaskFlow API)
from datetime import datetime, timedelta
from airflow.decorators import dag, task
from airflow.operators.bash import BashOperator

@dag(schedule="0 3 * * *", start_date=datetime(2026, 10, 1), catchup=False,
     default_args={"retries": 3, "retry_delay": timedelta(minutes=5)}, tags=["shop"])
def daily_shop():
    @task
    def extract(ds=None):                       # ds = the logical date, e.g. "2026-10-30"
        return extract_orders_to_s3(day=ds)     # returns a path, not data

    @task
    def load(path: str):
        copy_s3_to_warehouse(path, table="raw.orders")

    build = BashOperator(task_id="dbt_build", bash_command="cd /opt/shop && dbt build --target prod")
    load(extract()) >> build

daily_shop()
$ airflow dags test daily_shop 2026-10-30
$ airflow dags backfill daily_shop -s 2026-10-01 -e 2026-10-07
catchup
whether to run missed past intervals automatically
SLA misses & alerts
page on "data not fresh", not just "task failed"
Say it like a pro

"A DAG per pipeline, idempotent date-parameterized tasks, retries, and alerts on failed tests and stale data."

producer producer topic: page_views p0 ▮▮▮▮▮▮▮ ← offset 7 p1 ▮▮▮▮▮ ← offset 5 group A: DWH group B: alerts Concepts a topic is a durable, ordered log split into partitions; consumers track offsets many independent consumer groups can read the same data at their own pace Stream processing event time vs processing time · windows (tumbling, sliding, session) · late data, watermarks delivery is at-least-once; get "effectively once" with idempotent writes tools: Kafka · Redpanda · Kinesis · Pub/Sub · Flink · Spark Structured Streaming · ksqlDB use streaming only where minutes-old data isn't good enough
PLATE 7 — A stream is a log you can replay: producers append, consumers remember where they are.
Chapter 7 · Streaming

Process events as they happen

$ docker run -d --name redpanda -p 9092:9092 redpandadata/redpanda start --overprovisioned --smp 1 --kafka-addr 0.0.0.0:9092
$ pip install confluent-kafka
from confluent_kafka import Producer, Consumer
import json

p = Producer({"bootstrap.servers": "localhost:9092"})
p.produce("page_views", key="user42", value=json.dumps({"user": 42, "url": "/pricing", "ts": 1761800000}))
p.flush()

c = Consumer({"bootstrap.servers": "localhost:9092", "group.id": "warehouse-loader",
              "auto.offset.reset": "earliest", "enable.auto.commit": False})
c.subscribe(["page_views"])
while True:
    msg = c.poll(1.0)
    if msg is None or msg.error(): continue
    event = json.loads(msg.value())
    write_to_warehouse(event)        # must be idempotent (key by event id)
    c.commit(msg)                    # commit the offset AFTER the write succeeds
-- streaming SQL (Flink SQL): page views per minute, tolerating 30 s of lateness
SELECT window_start, url, count(*) AS views
FROM TABLE(TUMBLE(TABLE page_views, DESCRIPTOR(ts), INTERVAL '1' MINUTE))
GROUP BY window_start, window_end, url;
Say it like a pro

"Commit offsets after a successful idempotent write, windows on event time with a lateness allowance, and batch where seconds don't matter."

Freshness Volume Schema Uniqueness Validity Consistency A bad number is worse than a missing one fresh? (last row less than N hours old) · complete? (row count near yesterday's) unique keys? · no nulls where required? · values in range or in an allowed set? totals match the source system? (reconcile with finance) When a check fails stop the pipeline (or quarantine bad rows) · alert the owner · don't publish wrong data write data contracts with producers: agreed schema, meaning, and freshness tools: dbt tests · Great Expectations · Soda · Monte Carlo · OpenLineage
PLATE 8 — Trust is the product. One wrong dashboard costs more than a late one.
Chapter 8 · Data quality & observability

Test the data, not just the code

-- plain SQL checks that return rows only when something is wrong
-- 1) duplicates in a primary key
SELECT order_id, count(*) FROM fct_orders GROUP BY 1 HAVING count(*) > 1;
-- 2) orphaned foreign keys
SELECT f.order_id FROM fct_orders f LEFT JOIN dim_users u USING (user_id) WHERE u.user_id IS NULL;
-- 3) freshness: the newest row should be recent
SELECT 1 WHERE (SELECT max(created_at) FROM fct_orders) < now() - interval '6 hours';
-- 4) volume anomaly: today's count vs the 7-day average
WITH d AS (SELECT created_at::date AS day, count(*) n FROM fct_orders GROUP BY 1)
SELECT * FROM d WHERE day = current_date AND n < 0.5 * (SELECT avg(n) FROM d WHERE day >= current_date - 7 AND day < current_date);
# Great Expectations style assertion in Python
import great_expectations as gx
v = gx.get_context().sources.pandas_default.read_csv("orders.csv")
v.expect_column_values_to_not_be_null("order_id")
v.expect_column_values_to_be_between("total", min_value=0, max_value=100000)
v.expect_column_values_to_be_in_set("status", ["open", "paid", "refunded"])
print(v.validate().success)
data contract
producer and consumer agree the schema and semantics; breaking changes are reviewed
PII
mask, hash or restrict personal data; apply retention and access policies (GDPR)
cost
watch query bytes scanned and warehouse credits; partition and cluster tables
Say it like a pro

"Freshness, volume, uniqueness and reconciliation checks gate each run, with contracts at the source and alerts to an owner."

sources Airbytedlt warehouseBigQuery… dbt BI · MLMetabase orchestration (Airflow / Dagster) · quality · lineage · access control Learning ladder 1 · SQL (joins, windows, CTEs): the core skill 2 · Python + pandas / DuckDB 3 · warehouse + dbt modelling 4 · Airflow / Dagster orchestration 5 · cloud storage, IAM, cost 6 · Spark and streaming when scale demands Start small, stay boring Postgres/DuckDB + dbt + cron is a real pipeline · add tools when a pain appears document every dataset: owner, meaning, freshness, how to use it analytics engineering sits between data engineering and analysis
PLATE 9 — Fewer moving parts beat a fashionable stack you can't operate.
Chapter 9 · The modern stack & next steps

A project to build

  1. Pick a public API or CSV (GitHub events, weather, NYC taxi data).
  2. Ingest it daily into DuckDB or Postgres with dlt or a small Python script.
  3. Model staging → marts in dbt, with tests on keys and ranges.
  4. Schedule it with Airflow or Dagster (or GitHub Actions cron to start).
  5. Chart it in Metabase or Superset; add a freshness alert.
  6. Write a README: sources, schema, lineage, how to backfill.
$ pip install dlt[duckdb] dbt-duckdb dagster dagster-webserver
$ docker run -d -p 3000:3000 --name metabase metabase/metabase
$ dbt build && dagster dev
practise
Data Engineering Zoomcamp (free) · Mode SQL tutorial · pgexercises.com · dbt Learn
read next
"Fundamentals of Data Engineering" (Reis & Housley) · "The Data Warehouse Toolkit" (Kimball) · "Designing Data-Intensive Applications"
Say it like a pro

"Boring and reliable first: ingest, model, test, schedule, document, and only then reach for streaming or Spark."