Pipeline to Dashboardnihalsulaiman.com

Week 7 · Run it in production › Day 47

Day 47. Performance, scale and when to change gear

⏱ 4h · learn 70m · lab 135m · ship 20m · review 15m🏷 QSR at chain scaleBefore: Day 46

By the end of today you can

  • Profile a pipeline and find the real bottleneck
  • Make every layer incremental and measure the saving
  • Use partitioned Parquet and open table formats (Iceberg, Delta) appropriately
  • Recognise the signals that justify Spark, ClickHouse or a cloud warehouse

1. Why this matters

At 18 restaurants everything is fast. At 500 restaurants with three years of history, the same code takes hours. A senior person knows where time goes, fixes the right thing, and can say with numbers when the platform needs a bigger engine.

2. Profile before optimising

Measure each asset's duration (Dagster shows it), then inside the slow one: time.perf_counter() around steps, py-spy for Python hot spots, EXPLAIN ANALYZE for SQL, Polars .profile() for lazy queries. Typical findings, in order: re-reading all history every run; row-by-row Python loops; non-sargable SQL; network round trips; tiny files.

3. Incremental everything

Layer Full approach Incremental approach
Landing List and hash every file Manifest + new files only (hash only unseen names/sizes)
Staging Re-clean all files Partition by business date, process only new partitions
Load Truncate and reload Merge / delete-insert by partition
dbt table incremental with look-back; microbatch
BI Full refresh Incremental refresh (last N days)

4. Files: size and layout

  • Target Parquet files of roughly 100 MB–1 GB; thousands of tiny files are slow everywhere ("small files problem"). Compact daily partitions into monthly files for history.
  • Sort within files by common filters (date, location) so min/max stats skip row groups.
  • Open table formats (Apache Iceberg, Delta Lake) add transactions, schema evolution, time travel and safe concurrent writes on top of Parquet in object storage. DuckDB, Spark, Trino, ClickHouse and Snowflake read them. Use them when several engines write or read the lake; for a single-writer daily pipeline, plain partitioned Parquet plus a manifest is often enough.
Maths and principle

Principle: incremental processing changes cost from proportional to history to proportional to change.

Equation: full run cost \(\propto N_{\text{total}} = \sum_{d \le D} n_d \approx D \bar n\), growing linearly with time. Incremental cost \(\propto n_D + \text{look-back} \approx (1 + L)\bar n\), constant over time. Speed-up after \(D\) days \(\approx D / (1 + L)\).

Analogy: updating a running sum vs re-adding the whole list every day. Where it breaks: logic that genuinely needs history (rebuilt SCD2, all-time distinct counts) needs smarter state, like keeping per-entity aggregates.

Try it: 3 years of daily partitions, 7-day look-back. Approximate speed-up vs full rebuild? (1,095 / 8 ≈ 137×.)

5. When to change gear

Move beyond single-node Postgres + DuckDB when several of these are true and you've already done the incremental work:

  • Daily processing windows are missed even when incremental, on a reasonably sized machine.
  • Facts reach billions of rows and dashboards need sub-second aggregations across them → ClickHouse (or a cloud warehouse).
  • Many teams and many concurrent heavy queries → elastic cloud warehouse (Snowflake, BigQuery, Fabric, Databricks SQL).
  • Data science on very large data, ML features at scale → Spark / Databricks.
  • Streaming needs (minutes, not days) → Kafka/Redpanda + a streaming engine, or warehouse-native streaming.

The 5-year lens

Most "we need Spark" conversations at medium companies end with "we needed incremental loads and a columnstore". Before proposing a bigger engine, show the profile, the incremental redesign and its measured effect. If it's still not enough, you'll have the numbers that justify the spend, and the finance director will approve it because you tried the cheap fix first.

Lab

  1. Generate a big dataset: --days 365 and edit the generator's reference to 150 restaurants (or loop the generator with different seeds into separate folders). Measure your full pipeline run time.
  2. Profile: find the top 3 slowest steps. Record before numbers.
  3. Make landing, staging, load and dbt incremental (most already are; find the gaps). Record after numbers.
  4. Compact one month of daily Parquet into one file sorted by business_date, restaurant_id; compare a DuckDB query before and after.
  5. Optional: write the staging layer as an Iceberg or Delta table (pyiceberg or deltalake Python packages) and query it from DuckDB.
  6. Write docs/performance.md with before/after and your "when to change gear" judgment for this chain.

Ship

perf: profiling, incremental gaps closed, compaction; before/after numbers.

Check yourself

1. A pipeline takes longer every month. Most likely cause?

2. 40,000 Parquet files of 50 KB each. What's the problem and fix?

3. When would you introduce Iceberg or Delta?

My notes