Data engineering, hands on
Break a pipeline so you know how to build one.
Nine core ideas that every data platform is built on, each with a small working model you can poke. Run the same load twice, move a customer across the country, crash a job halfway, and see what each design choice does to the numbers.
-
1 · Pipelines
ETL vs ELT
Step a messy export through both pipelines, then change a business rule after the source has deleted its history.
Extract · transform · load · raw layer
-
2 · Latency
Batch vs streaming
Watch events wait for the next job. Trade freshness against the cost of running more often.
Schedules · micro-batch · freshness · cost
-
3 · Reliability
Idempotent loads
Run a load twice, crash it halfway, retry. Only some strategies get the revenue right.
Append · overwrite · merge/upsert · retries
-
4 · Change data
Incremental loads
Sync only what changed using a watermark, and find the rows it silently misses.
Watermarks · late commits · deletes · lookback
-
5 · Modeling
Normalized vs star schema
Ask business questions of two designs and count the joins. Then try to rename a category.
3NF · facts · dimensions · grain
-
6 · History
Slowly changing dimensions
A customer moves. Does last year's revenue move with them?
SCD type 1, 2 and 3 · surrogate keys
-
7 · Storage
Row vs columnar, partitioning
See exactly which bytes a query reads, and which files it never has to open.
Parquet · pruning · small files
-
8 · Trust
Data quality and contracts
Switch tests on and off, pick warn, quarantine or fail, and survive an upstream schema change.
Tests · quarantine · schema contracts
-
9 · Operations
Orchestration
Run a DAG with limited workers, break a task, retry it, and backfill three missed days.
DAGs · dependencies · retries · backfills
-
Wrap-up
Where this goes next
How the nine ideas fit together, and where the advanced site picks up.
Checklist · further topics
Every table here is a few dozen rows generated in your browser. Nothing is fetched, and none of it is sent anywhere; the page only records an anonymous visit count. The mechanics are real; the scale is not.
1 · Pipelines
ETL vs ELT
Every pipeline extracts data from somewhere, transforms it into something useful and loads it somewhere people can query. The order of the last two steps matters more than it looks.
Where does the transform run?
An online shop exports its orders as a CSV. It's messy: stray spaces, two date formats, mixed currency case, one order with no amount.
ETL (extract, transform, load) cleans the data on a pipeline server and loads only the finished table. It was the default when warehouse storage and compute were expensive.
ELT (extract, load, transform) copies the raw data into the warehouse first, then transforms it there with SQL. Cheap cloud storage made this the modern default, and tools like dbt are built around it.
Both give the same orders table on day one. The difference shows up later, when the
rules change.
The transform
Rule v1: clean, convert EUR at 1.10, drop rows with no amount
Finance changes the rule
Rule v2: keep the original currency, and flag missing amounts instead of dropping them
Rebuilt orders table
2 · Latency
Batch vs streaming
Data is created continuously, but it only becomes useful once it lands where people can query it. How often you move it is a trade between freshness, cost and complexity.
Waiting for the next job
Each dot on the top line is an order placed on the shop. It turns blue and drops to the bottom line when a job copies it into the warehouse. Orange dots are still waiting. Dashed lines are job runs.
A batch job processes everything that has piled up since the last run. With an interval of T, the average order waits about T/2.
Streaming handles each event as it arrives. The delay drops to seconds, but a worker has to run all the time, and it has to deal with restarts, duplicates and out-of-order events by itself.
Ask what the data is for. A fraud check needs seconds. A monthly finance report is happy with a nightly batch, and much cheaper to run and debug.
3 · Reliability
Idempotent loads
Jobs fail, schedulers retry, and someone always re-runs yesterday by hand. A load is idempotent if running it twice leaves the table exactly as running it once.
Run it again. And again.
Today's file has five orders. The sales table already holds two from yesterday.
- Append just inserts. Simple, fast, and every re-run duplicates the day.
- Overwrite partition deletes the whole day and re-inserts it inside one transaction. A crash rolls back cleanly, and re-runs are safe.
- Merge (upsert) matches on a key: update if it exists, insert if not. Safe to re-run even after a partial crash, and it handles corrections.
The price of safety: overwrite needs a clean partition boundary, and merge needs a reliable unique key and is slower on big tables.
daily_file for 2026-09-26
The load
sales table
Run log
4 · Change data
Incremental loads
Copying a whole table every run is simple and always correct, until the table has a billion rows. Incremental loads copy only what changed, and that is harder than it sounds.
High-water marks and what slips past them
The usual trick: remember the latest updated_at you've seen (the
watermark) and next time ask only for rows newer than it.
Two things break it:
- Late commits. A slow transaction stamps a row at 10:06 but commits at 10:11. By then the watermark has moved past 10:06, and the row is never read.
- Hard deletes. A deleted row has no
updated_atto find. It lives on in the warehouse forever.
Common fixes: re-read a lookback window behind the watermark (and merge, so re-reads don't duplicate), use soft deletes, and run a periodic full reload to catch drift. Log-based change data capture avoids both problems by reading the database's own log.
Source orders
What the app's database holds right now
Warehouse copy
Compared with the source, row by row
Sync log
5 · Modeling
Normalized vs star schema
The same shop data can be laid out for the app that writes it or for the analysts who read it. Those two jobs want opposite designs.
Built for writing, or built for reading
A normalized (third normal form) schema stores every fact exactly once. A city's
region lives in one row of cities. Updates are tiny and can't contradict each other. It's
what transactional apps use.
A star schema puts measurable events in a central fact table and describes them with a few wide dimension tables. Region is copied onto every customer row. That's redundant on purpose: questions need fewer joins, and the tables read like the business.
Decide the grain first: one row of fact_sales is one product on one
order. Everything else follows from that.
Normalized (3NF)
Star schema
6 · History
Slowly changing dimensions
Dimensions describe things that change: customers move, products get recategorized. When a dimension changes, you have to decide what happens to the facts that came before.
Does history move with the customer?
- Type 1 overwrites the old value. Simple, always current, and it rewrites history: past sales now report under the new city.
- Type 2 closes the old row and adds a new one with its own surrogate key and a validity range. Each sale keeps pointing at the version that was true when it happened.
- Type 3 adds a
previous_citycolumn. Handy for "before and after" comparisons, but it remembers one change only.
Type 1 is right for fixing typos. Type 2 is the default for anything you report history on. The
surrogate key is what makes it work: facts join on customer_sk, not on the business
key customer_id.
dim_customer
What a move does
Revenue by city
Facts joined to the dimension, vs where each sale really happened
fact_sales, newest first
7 · Storage
Row vs columnar, and partitioning
In the cloud you pay for bytes scanned. How a table is laid out on disk decides how many bytes a query has to touch.
Read less, pay less
Each small square is one value, colored by column and drawn in the order it sits on disk. Bright squares are the ones this query reads.
Row formats (CSV, Avro, most app databases) store one whole row after another. Great for writing or fetching a single record, but to sum one column you read them all.
Columnar formats (Parquet, ORC, every modern warehouse) store each column together. Analytics touch a few columns of many rows, so they read a fraction of the data, and a column of similar values compresses far better too.
Partitioning splits a table into folders by a column's value. A filter on that column lets the engine skip whole files without opening them, called pruning. Partition by something you filter on often and that has a modest number of values. Date is the classic choice.
amount in row format, then columnar. Add a filter on
date, then partition by date. Finally, partition by customer and watch the file count.Columns to select
One folder per customer, each holding a couple of rows. Real tables partitioned on a high-cardinality column end up with millions of tiny files, and opening them costs more than reading them. This is the small files problem.
8 · Trust
Data quality and contracts
A pipeline that runs without errors can still load garbage. Tests catch bad rows, and a policy decides what happens to them.
Test the data, not just the code
Today's batch has twelve orders and six problems: a missing email, a negative amount, a duplicate, a made-up country, a product that doesn't exist and an order from next year.
The classic tests (as in dbt or Great Expectations) are not null, unique, accepted values, ranges and relationships (foreign keys).
When a test fails, you choose:
- Warn: load everything, log it. Easy to ignore.
- Quarantine: load the good rows, set the bad ones aside for review.
- Fail: stop the pipeline. Stale data beats wrong data for things like billing.
A data contract goes further upstream: the producing team promises a schema, and a breaking change is rejected at the door instead of trickling through as NULLs.
Tests
Incoming batch
9 · Operations
Orchestration
Real platforms run hundreds of dependent jobs a day. An orchestrator (Airflow, Dagster, Prefect) runs them in the right order, retries what breaks and remembers what ran for which day.
A DAG, run by a scheduler
The pipeline is a directed acyclic graph: each task starts only when everything upstream has succeeded. Independent branches run in parallel, limited by the number of workers.
When a task fails, it's retried. That fixes flaky problems like a network blip. If it still fails, everything downstream is marked upstream failed and skipped, while unrelated branches carry on.
Each run is for a logical date. A backfill runs the DAG for past dates, say after fixing a bug. That only works if every task is idempotent (chapter 3), so re-running a day replaces it instead of doubling it.
extract_products once to make it flaky
and run again, then click it again to break it for good. Fix it, clear the failed tasks, and
backfill three days.Retries per task
DAG
Click a task to cycle it: healthy → flaky (fails its first try) → broken
Run history
One column per logical date, like Airflow's grid view
Wrap-up
Where this goes next
The nine ideas lean on each other. Here's how they combine into a pipeline you can trust, and what lies past the fundamentals.
A checklist for any new pipeline
Land raw data first
Keep an untouched copy so you can rebuild when the rules change (chapter 1).
Pick latency on purpose
Match the schedule to what the data is for, not to what sounds modern (chapter 2).
Make every load re-runnable
Overwrite a partition or merge on a key, so retries and backfills are safe (chapter 3).
Know what your increments miss
Plan for late commits and deletes, and reconcile with a periodic full load (chapter 4).
Model for the questions
Declare the grain, build facts and dimensions, keep history where it matters (chapters 5 and 6).
Lay out for the reads
Columnar formats, partitions on common filters, and no confetti of tiny files (chapter 7).
Test the data
Decide per table whether bad rows warn, quarantine or stop the line (chapter 8).
Orchestrate and observe
Dependencies, retries, alerts and backfills, all written down as code (chapter 9).
Beyond the fundamentals
These advanced topics have their own site, with the same hands-on demos: Advanced Data Engineering.
Stream processing in depth
Event time vs processing time, windows, watermarks for late events, stateful operators and exactly-once delivery.
Log-based CDC
Reading the database's write-ahead log (Debezium and friends) to capture every insert, update and delete without polling.
Open table formats
Iceberg, Delta Lake and Hudi: ACID transactions, time travel, compaction and schema evolution on object storage.
Distributed execution
Shuffles, data skew, spills, join strategies and why one partition can hold up a thousand-node job.
Lineage and governance
Column-level lineage, catalogs, access control, PII handling and data mesh ownership.
Semantic layers and cost
Metrics defined once for every tool, plus query optimization and warehouse cost control at scale.