Lab 01 — Query Optimizer & Cost Estimator

Phase: 10 — Query & Analytics Engines | Difficulty: ⭐⭐⭐⭐☆ | Time: 5–6 hours

Trino, Athena, and Spark SQL all live or die by the same optimizer decisions. This lab builds them: projection + partition pruning → bytes scanned → Athena $/TB cost, broadcast vs partitioned join selection, and cost-based join reordering (small tables first). After this, "diagnose the slow/expensive query" is arithmetic.

What you build

  • bytes_scanned — combine columnar projection and partition pruning into the bytes the engine actually reads
  • athena_cost_usd — $5/TB (the law that makes layout a cost feature)
  • pushdown_savings — optimized vs naive full scan (the 10–100× ratio)
  • choose_join_strategy — broadcast the small side vs partitioned (shuffle) hash join
  • join_order / estimate_join_cost — CBO: small tables first minimizes the sum of intermediate results (even though the final size is order-independent)

Key concepts

ConceptWhat to understand
Projectionread only needed columns → fewer bytes (P08)
Partition pruningfilter on partition col → skip partitions; else full scan
$/TBscan cost is the bill; layout is the lever
Broadcast vs partitionedsmall side broadcast = no shuffle
Join ordersmall-first keeps intermediates small (the CBO win)

Run

pip install -r requirements.txt
pytest test_lab.py -v
LAB_MODULE=solution pytest test_lab.py -v

Success criteria

  • All 14 tests pass.
  • You can explain why a filter on a non-partition column doesn't prune (and what would).
  • You can explain why join order changes cost even when the final result size doesn't.
  • You can turn a "this query is expensive" complaint into a bytes-scanned + $ estimate.

Extensions

  • Add dynamic filtering (build a filter from the dimension side of a join to prune the fact scan at runtime) and show the bytes saved.
  • Add a CBO that uses column min/max stats (P08) to estimate selectivity per predicate instead of a global selectivity.
  • Add Lake Formation column/row masking to the scan (P14): a restricted principal reads fewer columns → different bytes + a governance check.
  • Cost a CTE materialized once vs recomputed and pick based on reuse count.