Warmup Guide — Query & Analytics Engines

Zero-to-principal primer for Phase 10: how Trino/Athena/Spark-SQL optimize a query, the pushdown/pruning/join decisions that determine speed and the $/TB bill, and when to pick serverless Athena vs a dedicated Trino cluster.

Table of Contents

Chapter 1: The Engine Landscape

A query engine is compute that reads storage — separate from the storage itself (the lakehouse split). The players:

  • Trino / Presto: a distributed MPP SQL engine with no storage of its own — it federates queries over S3/Iceberg/Hive/Cassandra/MySQL/… via connectors. Coordinator plans; workers execute in parallel, streaming data between stages (in memory, spilling if needed). Trino is the actively-developed fork; PrestoDB is the original.
  • Athena: AWS's serverless, managed Trino over S3 + Glue Catalog. No cluster to run; you pay per TB scanned. Great for ad-hoc and intermittent analytics.
  • Spark SQL: the Catalyst optimizer + Tungsten execution on Spark (P06) — batch and interactive; same engine as your ETL, good when you want one stack.
  • Hive (P07): the legacy SQL-on-Hadoop; mostly what you migrate from.
  • Flink SQL (P04): the streaming SQL engine — same dynamic-table semantics.

Same ANSI SQL, different runtimes, cost models, and latency. The principal skill is choosing (Ch. 8) and optimizing (the rest).

Chapter 2: The Optimizer Pipeline

Every engine turns SQL into an execution plan through the same stages:

  1. Parse → an abstract syntax tree → an unoptimized logical plan (a tree of relational operators: scan, filter, project, join, aggregate).
  2. Rule-based optimization (heuristics that are always good): predicate pushdown, projection pushdown, partition pruning, constant folding, CTE handling, subquery decorrelation. These rewrite the logical plan into an equivalent, cheaper one.
  3. Cost-based optimization (CBO): choices where the right answer depends on the data — join order and join strategy — decided using statistics (Ch. 5).
  4. Physical planning: pick operators (broadcast vs hash join, hash vs sort aggregate), add exchanges (shuffles), and produce the executable plan.

EXPLAIN shows you this plan — reading it (where's the scan? how big? which join? a full table scan?) is the core debugging skill (Ch. 6 Q&A).

Chapter 3: Pushdown and Pruning

The rewrites that determine how much data is read (and thus the bill):

  • Predicate pushdown: push WHERE filters down to the scan so the engine reads fewer files/row-groups/pages (P08 stats do the skipping). WHERE country='US' evaluated at the scan, not after reading everything.
  • Projection pushdown: read only the columns the query needs (columnar, P08). Selecting 3 of 50 columns reads ~6% of the bytes.
  • Partition pruning: a filter on a partition column skips entire partitions/directories (P07/P09). The lab's bytes_scanned shows the combined projection × pruning effect — and the trap: a filter on a non-partition column can't prune partitions (it still reads all, then filters), which is why partition-column choice matters so much.
  • Dynamic filtering: in a join, build a filter from the (small) dimension side at runtime and push it into the (huge) fact scan — e.g. only scan fact partitions for the 10 dates the filtered dimension selected. Massive for star schemas.

The unifying idea: the cheapest data to process is the data you never read. Pushdown + pruning is the engine not reading what it doesn't need — and how effective it is depends entirely on the layout you built in P08/P09.

Chapter 4: Join Strategies and Reordering

Joins are the optimizer's hardest decisions:

  • Broadcast (replicated) join: ship the small side to every worker and hash-join locally — no shuffle. Fastest when one side fits memory/threshold (the lab's choose_join_strategy). The risk: broadcasting something not-actually-small → OOM.
  • Partitioned (shuffle) hash join: hash-partition both sides by the join key across workers, then join locally. The default for two large tables; pays a shuffle (P06).
  • Sort-merge join: shuffle + sort both, merge — for very large sorted joins.
  • Join reordering (CBO): for a multi-table join, the order changes the size of intermediate results. Joining small tables first keeps intermediates small (the lab's estimate_join_cost). The final result size is order-independent, but the work to get there is not — which is why a CBO with good stats can make a 10-table join tractable.

Chapter 5: Statistics and Cost-Based Optimization

CBO is only as good as its statistics:

  • Table/column stats: row counts, column min/max/distinct/null (P08's per-file stats aggregated). The optimizer uses these to estimate selectivity (how many rows a predicate keeps) and cardinality (rows out of a join), which drive join order and strategy.
  • ANALYZE TABLE populates/refreshes stats. Stale stats are the #1 cause of bad plans: the optimizer thinks a table is small and broadcasts it (OOM), or picks a bad join order. A shockingly common "the query got slow" cause is "stats are stale after a big load."
  • Modern engines also use runtime adaptivity (Spark AQE, P06; Trino's adaptive features) to correct compile-time misestimates with actual data — but fresh stats still matter.

Chapter 6: The $/TB Cost Model

On Athena/Trino-over-S3 the cost is brutally simple and brutally important: $5 per TB scanned (the lab's athena_cost_usd). Consequences:

  • A query scanning 1 TB costs 100× one scanning 10 GB of the same data — and the difference is layout, not hardware: partitioning + columnar projection + sorting + compaction (P06/P08/P09) cut bytes scanned by 10–100×.
  • This makes the query engine the place where bad upstream decisions (small files, no partitioning, JSON instead of Parquet) finally cost money — visibly, per query, forever.
  • Controls: Athena workgroups with per-query/per-workgroup data-scanned limits stop a runaway SELECT * from scanning a petabyte; cost-allocation tags attribute spend by team (P07/P13).
  • The principal framing: a slow query is usually an expensive query, and the fix is almost always layout, not a bigger cluster. "Diagnose the slow query" = "find the full scan and give it something to prune."

Chapter 7: Concurrency, Resource Management, and Table Design

  • Trino concurrency: a dedicated cluster serves many concurrent queries; resource groups allocate memory/CPU between tenants/queues; per-query memory limits + spill prevent one query from starving others. Athena handles concurrency for you (with service limits) but you pay per scan.
  • Table design for high-concurrency BI: partition on the common filter (date), sort/cluster on secondary filters (P08/P09), compact to target file sizes, pre-aggregate hot rollups into data marts (without duplicating business logic — keep one source of truth and lineage, P13). The goal: every dashboard query prunes hard and scans little.

Chapter 8: Catalog, Lake Formation, and When to Use What

  • Glue Data Catalog (P07): the shared metastore Athena/Trino/Spark/EMR read — "what tables exist, where, what schema/partitions/stats." One catalog, many engines.
  • Lake Formation (P14): fine-grained governance over the catalog + S3 — table/column/row level permissions and column masking for a principal. The query engine enforces these at scan time (a restricted user reads fewer columns/rows). Built in P14.
  • The Athena vs dedicated-Trino vs Spark-SQL decision (P00 dials):
    • Athena → serverless, intermittent/ad-hoc, no ops, pay-per-scan; perfect until concurrency/ cost at scale argues otherwise.
    • Dedicated Trino cluster → steady, high-concurrency BI/dashboards where always-on tuned compute + resource groups beat per-scan billing, and you want federation.
    • Spark SQL → you're already on Spark and want one engine for ETL + heavy transformations; higher latency for interactive.
    • Flink SQL → streaming/continuous (P04). Write the ADR on concurrency, latency, ops, cost shape, and federation needs — not fashion.

Lab Walkthrough Guidance

Lab 01 — Query Optimizer, suggested order:

  1. bytes_scanned — projection width × scanned-partition rows; non-partition filter = full scan.
  2. athena_cost_usd — $5/TB, 10 MB minimum.
  3. pushdown_savings — optimized vs naive full scan ratio.
  4. choose_join_strategy — broadcast the smaller side under threshold, else partitioned.
  5. join_order (ascending) + estimate_join_cost — small-first beats large-first on the sum of intermediates.

Success Criteria

You are ready for Phase 11 when you can, from memory:

  1. Contrast Trino/Athena/Spark-SQL/Flink-SQL and their cost models.
  2. Walk the optimizer pipeline (rule-based rewrites → CBO → physical plan).
  3. Explain predicate/projection/partition pushdown and dynamic filtering.
  4. Choose a join strategy and explain why join order changes cost.
  5. Explain why stale stats cause bad plans, and what ANALYZE fixes.
  6. Compute a query's bytes scanned and $ cost and name the layout fix.
  7. Choose Athena vs dedicated Trino vs Spark SQL for a workload.

Interview Q&A

Q: An Athena query costs $50 and takes 3 minutes. How do you make it cheap and fast? $50 means it scanned ~10 TB ($5/TB), so the fix is "read less." I'd EXPLAIN and check: is it a full scan because the filter isn't on a partition column (add/align partitioning — date is typical), or SELECT * reading every column (project only what's needed), or scanning a swamp of small files (compact, P09), or unsorted data so row-group pruning can't skip (sort/Z-order on the filter column, P08)? Usually it's several of these. The point is the fix is layout, not a bigger engine — Athena has no "bigger cluster" knob anyway; the only lever is bytes scanned, and bytes scanned is decided upstream in P08/P09.

Q: The optimizer chose a bad join and OOM'd. Why? Almost always stale or missing statistics: the optimizer estimated a table as small and broadcast it to every worker, but it was actually huge → OOM; or it picked a poor join order because cardinality estimates were wrong. The fix is ANALYZE TABLE to refresh stats so the CBO sees reality, and (belt and suspenders) rely on runtime adaptivity (Spark AQE / Trino's adaptive features) to correct misestimates mid-flight. If a specific table is reliably small, a broadcast hint encodes the intent. Bad plans are usually bad inputs (stats), not a bad optimizer.

Q: When do you move off Athena to a dedicated Trino cluster? When the workload becomes steady and high-concurrency — lots of dashboards hitting it all day. Athena's serverless per-scan model is perfect for intermittent/ad-hoc use (no ops, pay only when you query), but at sustained high concurrency a tuned always-on Trino cluster with resource groups can be cheaper and give predictable latency and isolation between tenants. I'd decide on the numbers: query concurrency, daily scan volume (× $5/TB vs cluster cost), latency SLOs, and whether we need cross-source federation. It's a cost-and-control crossover, captured in an ADR — not a one-size answer.

References