All lessons

Module 7: Run it in production · Lesson 7.5 · about 35 min

Performance, scale and when to change gear

Profile before fixing, make every layer incremental, compact small Parquet files, know when Iceberg or Delta help, and spot when a bigger engine is justified.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 7 · about 35 minutes · read first: 3.4 and 4.3. You’ll be able to profile a pipeline to find where the time really goes, make every layer incremental and measure the saving, size and sort Parquet files, decide when an open table format helps, and say with numbers when a platform needs a bigger engine.

Most slow pipelines are not short of power; they redo yesterday's work every day. Measure first, process only what changed, and change engine only when the numbers still say so.

1. Why this matters

At 18 restaurants everything is fast. At 500 restaurants with three years of history, the same code can take hours. Imagine a shop that counts every item on every shelf each night to know today’s sales, instead of reading today’s till rolls. It works while the shop is small. It falls apart as the shelves fill up.

A senior engineer knows where the time goes, fixes the right thing, and can say with numbers when the platform really needs a bigger engine.

2. Profile before you optimise

Profiling means measuring where time is spent instead of guessing. Start wide, then zoom in:

  1. Each asset’s duration: the Dagster UI shows it per run. Find the slowest asset and its trend.
  2. Inside the slow asset: wrap steps with time.perf_counter(); run py-spy record -o profile.svg -- python job.py for a picture of where Python spends time.
  3. SQL: EXPLAIN ANALYZE (Postgres and DuckDB both have it) runs the query and shows each step of the plan with its time.
  4. Polars: lf.profile() on a lazy query returns the result and a table of how long each node took.
import time
t0 = time.perf_counter()
df = read_day(bdate)
t1 = time.perf_counter()
out = clean(df)
t2 = time.perf_counter()
log.info("read %.1fs, clean %.1fs", t1 - t0, t2 - t1)

The usual findings, roughly in order: re-reading all history on every run; row-by-row Python loops; SQL filters that cannot use an index (such as where year(order_date) = 2026, see Lesson 4.5); many small network round trips; thousands of tiny files.

3. Incremental everything

Layer Full approach Incremental approach
Landing list and hash every file manifest, hash only names or sizes not seen before
Staging re-clean every file partition by business date, process only new partitions
Load truncate and reload merge or delete-insert by partition
dbt table incremental with a look-back, or microbatch
BI full refresh incremental refresh of the last N days
Day 30. A 150-restaurant chain adds about 0.6 million rows a day. A full rebuild reads 18 million rows; an incremental run with a 7-day look-back reads 8 days: 4.8 million.
Day 365. The full rebuild now reads 219 million rows every night. The incremental run still reads 4.8 million.
Day 1,095. Three years in, the full rebuild reads 657 million rows. The incremental run has not changed.
The speed-up. 1,095 days ÷ 8 days per run ≈ 137 times less work, and the gap keeps growing every day.
Profiling
Measuring where time goes, step by step, before changing anything.
Incremental
Processing only new or changed partitions (plus a short look-back), not the whole history.
Look-back
A few recent days reprocessed each run so late-arriving data is caught.
Small files problem
Thousands of tiny files, where the cost of opening each one outweighs reading the data.
Physical meaning
The overnight run that took 3 hours and pushed the 08:00 email to 09:30 now takes 4 minutes, and it will still take 4 minutes next year. Nobody bought a bigger server.
Maths and principle

Principle: incremental processing makes the cost follow the size of the change, not the size of the history.

Equation: with n‾ rows a day, after D days a full run reads about Dn‾ rows (growing every day), while an incremental run with a look-back of L days reads (1+L)n‾ (constant). So

speed-up≈Dn‾(1+L)n‾=D1+L

Analogy: keeping a running total in a notebook and adding today’s number, instead of re-adding every receipt since the shop opened.

Where it breaks: logic that truly needs all history (an all-time count of distinct customers, a full SCD2 rebuild) needs smarter saved state, such as per-customer running aggregates.

Try it: 3 years of daily partitions, 7-day look-back. Speed-up? (1,095 ÷ 8 ≈ 137 times.)

4. Files: size and layout

  • Aim for Parquet files of roughly 100 MB to 1 GB. Thousands of tiny files are slow in every engine. Compact daily partitions into monthly files for older history.
  • Sort within files by the columns people filter on (date, location). Parquet keeps the min and max of each column per chunk of rows (a row group), so a sorted file lets the engine skip whole chunks. It is like a phone book: because it is sorted, you never read the A to L pages to find “Patel”.
-- DuckDB: compact one month into one sorted file
copy (
  select * from read_parquet('data/staging/qsr_pos/business_date=2026-09-*/*.parquet')
  order by business_date, restaurant_id
) to 'data/history/qsr_pos/2026-09.parquet' (format parquet);

Open table formats (Apache Iceberg, Delta Lake) add transactions, schema changes, time travel and safe simultaneous writes on top of Parquet in object storage. DuckDB, Spark, Trino, ClickHouse and several cloud warehouses can read them (check each engine’s docs for what it supports). Use them when several engines write to or read from the lake. For one daily writer, partitioned Parquet plus a manifest is often enough.

5. When to change gear

Move beyond one Postgres server plus DuckDB only when several of these are true and the incremental work is done:

  • Daily windows are still missed with incremental runs on a reasonably sized machine.
  • Facts reach billions of rows and dashboards need sub-second totals over them → ClickHouse or a cloud warehouse.
  • Many teams run many heavy queries at once → an elastic cloud warehouse (BigQuery, Snowflake, Fabric, Databricks SQL).
  • Data science or ML features on very large data → Spark or Databricks.
  • Answers needed in minutes, not days → Kafka or Redpanda plus a streaming engine.

Each of those is a lesson in Data at Scale, the next course after this one.

“We need Spark.” At medium companies this usually ends with “we needed incremental loads and a columnstore”. Before proposing a bigger engine, show the profile, the incremental redesign and its measured effect. If that is still not enough, you now have the numbers that justify the spend, and finance will approve it because the cheap fix was tried first.

6. Try it

  1. Generate a big dataset: python generate_data.py --days 365 --out data_big with the reference widened to 150 restaurants (or loop the generator with different seeds into separate folders). Time the full pipeline.
  2. Profile it and record the three slowest steps with their times.
  3. Close the incremental gaps in landing, staging, load and dbt. Record the new times.
  4. Compact one month of daily Parquet into one sorted file with the query above. Time a DuckDB query for one restaurant and one week, before and after.
  5. Optional: write staging as an Iceberg or Delta table (the pyiceberg or deltalake Python packages) and query it from DuckDB.
  6. Write docs/performance.md: before and after numbers, and your “when to change gear” judgement for this chain.

What to check: the incremental run’s time stays flat when you add another month of data; the compacted query reads fewer bytes (DuckDB’s profile shows it); docs/performance.md has measured numbers, not guesses. Commit as perf: profiling, incremental gaps closed, compaction; before and after numbers.

  • Profile first: asset durations, then timers, py-spy, EXPLAIN ANALYZE or Polars profile inside the slow one.
  • Incremental runs cost (1 + look-back) days of work, so the speed-up is about D ÷ (1 + L) and keeps growing.
  • Aim for Parquet files of about 100 MB to 1 GB, sorted by common filters; compact small files.
  • Use Iceberg or Delta when several engines share the lake; one writer rarely needs them.
  • Change engine only when several signals hold after the incremental work, with numbers to prove it.

Check yourself

1A pipeline takes a little longer every month. What is the most likely cause?
2A lake holds 40,000 Parquet files of 50 KB each. What is the problem and the fix?
3When would you introduce Iceberg or Delta Lake?

Sources

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. All company data in the examples is synthetic. Tool versions and prices change: check the official docs before you rely on them.