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.
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.
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:
- Each asset’s duration: the Dagster UI shows it per run. Find the slowest asset and its trend.
- Inside the slow asset: wrap steps with
time.perf_counter(); runpy-spy record -o profile.svg -- python job.pyfor a picture of where Python spends time. - SQL:
EXPLAIN ANALYZE(Postgres and DuckDB both have it) runs the query and shows each step of the plan with its time. - 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 |
- 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 rows a day, after days a full run reads about rows (growing every day), while an incremental run with a look-back of days reads (constant). So
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
- Generate a big dataset:
python generate_data.py --days 365 --out data_bigwith the reference widened to 150 restaurants (or loop the generator with different seeds into separate folders). Time the full pipeline. - Profile it and record the three slowest steps with their times.
- Close the incremental gaps in landing, staging, load and dbt. Record the new times.
- 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.
- Optional: write staging as an Iceberg or Delta table (the
pyicebergordeltalakePython packages) and query it from DuckDB. - 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
Sources
- Polars user guide: Lazy API and query profiling https://docs.pola.rs/
- DuckDB documentation: Profiling and Parquet https://duckdb.org/docs/
- Apache Iceberg documentation https://iceberg.apache.org/docs/latest/
- Kleppmann, M. (2017), Designing Data-Intensive Applications, O'Reilly, Chapter 3 (storage and column-oriented storage) and Chapter 10 (batch processing)
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.