Phase 10 — Query & Analytics Engines

Difficulty: ⭐⭐⭐⭐☆ Estimated Time: 1.5 weeks (24–32 hours) Prerequisites: Phase 08 (file layout), Phase 09 (table formats), Phase 06 (Spark SQL/joins)

Why This Phase Exists

This is the query layer the JD demands you "optimize at the plan level … join strategies, predicate pushdown, partition pruning, statistics, CTE materialization, broadcast joins, dynamic filtering, and cost-based optimization … diagnose slow Athena/Trino/Hive/Spark SQL queries … design lakehouse tables for high-concurrency analytics … know when Athena is sufficient and when a dedicated Trino/Spark/Flink architecture is needed." The query engine is where users feel the platform, and where the cost shows up on the bill ($5/TB). Get the layout (P08) and table format (P09) right and the optimizer can be brilliant; get them wrong and no engine can save the query.

Concepts

  • The engines: Trino/Presto (distributed MPP, federates over many sources, no storage of its own), Athena (serverless managed Trino over S3 + Glue, pay-per-TB), Spark SQL (Catalyst optimizer, batch+interactive), Hive (legacy, P07), Flink SQL (streaming, P04). Same SQL, different runtimes and cost models.
  • The optimizer pipeline: parse → logical plan → rule-based rewrites (predicate/ projection pushdown, constant folding, CTE handling) → cost-based choices (join order & strategy) using statistics → physical plan.
  • Pushdown & pruning: predicate pushdown (filter at the scan), projection pushdown (read only needed columns), partition pruning (skip partitions), file/row-group skipping (P08 stats), dynamic filtering (prune the fact side from the dimension side at runtime).
  • Joins: broadcast vs partitioned (shuffle) hash join vs sort-merge; join reordering (small tables first); the role of statistics (ANALYZE) and stale-stats failures.
  • Cost on Athena/Trino: $5/TB scanned → layout (partitioning, columnar, compaction, sorting) is the cost program; workgroup controls and per-query data limits.
  • Concurrency & resource management: Trino's resource groups, memory limits, spill; designing tables for high-concurrency BI.
  • Catalog & governance: Glue Data Catalog (the metastore, P07), Lake Formation (fine-grained table/column/row access + masking — handoff to P14), workgroups.
  • When Athena vs dedicated Trino/Spark/Flink: serverless ad-hoc vs always-on tuned cluster vs streaming — the architecture decision (P00 dials).

Labs

Lab 01 — Query Optimizer & Cost Estimator (flagship, implemented)

FieldValue
GoalBuild projection + partition pruning → bytes scanned → Athena cost, broadcast vs partitioned join selection, and cost-based join reordering
ConceptsPushdown, pruning, $/TB, join strategy, CBO reordering
How to Testpytest test_lab.py -v — 14 tests
Talking PointsWhy doesn't a non-partition filter prune? Why does join order matter if final size is fixed? When Athena vs Trino cluster?
Resume bulletBuilt a cost-based query optimizer model (pushdown, partition pruning, join strategy + reordering, $/TB scan cost) for diagnosing slow/expensive analytics

→ Lab folder: lab-01-query-optimizer/

Extension Project A — Diagnose a real slow query (spec)

On Athena/Trino over an Iceberg/Parquet table: EXPLAIN a slow query, find the full scan or bad join, fix it with partitioning/sorting (P08/P09), ANALYZE for stats, and measure bytes-scanned + cost before/after.

Extension Project B — Athena-vs-Trino decision memo (spec)

For a stated workload (ad-hoc BI vs steady high-concurrency dashboards vs federated joins), choose Athena vs a dedicated Trino cluster vs Spark SQL with the deciding dial and an ADR.

Integrated-Scenario Hooks

  • This phase's scan cost is paid down by P08 layout + P09 compaction/clustering — the through-line of "layout is a cost feature."
  • Its Lake Formation masking is built in P14.
  • It is the analytics access layer of capstone Problems 1 and 5 (P16).

Guides in This Phase

Key Takeaways

  • The optimizer is rule-based rewrites (pushdown/pruning) + cost-based choices (join order/ strategy) over statistics.
  • Athena/Trino cost is $5/TB — partition, project, sort, and compact to cut it 10–100×.
  • Broadcast the small side; reorder joins small-first; keep stats fresh (ANALYZE).
  • Athena for serverless ad-hoc; dedicated Trino for steady high-concurrency; Flink SQL for streaming.
  • The engine can only exploit a good layout — P08/P09 decide what's possible here.

Deliverables Checklist

  • Lab 01 implemented; all 14 tests pass
  • You can read an EXPLAIN and spot a full scan / bad join
  • You can quantify a query's bytes scanned and $ cost, and the fix
  • You can choose Athena vs Trino vs Spark SQL for a workload
  • Extension A diagnosis or Extension B decision memo