🛸 Hitchhiker's Guide — Phase 10: Query & Analytics Engines

Read this if: you want Trino/Athena optimization and the $/TB cost model fast, and to ace "diagnose this slow/expensive query." Skim, read WARMUP, build the optimizer.

0. The 30-second mental model

Trino/Athena/Spark-SQL are compute that reads storage. The optimizer does rule-based rewrites (pushdown, pruning — always good) + cost-based choices (join order/strategy — need stats). On Athena you pay $5/TB scanned, so the whole game is read less. One sentence: the cheapest data is the data you never read — and what you can skip is decided by the layout you built in P08/P09.

1. Engine picker

Trino/Presto → MPP, federates many sources, no own storage; dedicated cluster
Athena       → serverless managed Trino over S3+Glue; pay $5/TB; ad-hoc/intermittent
Spark SQL    → Catalyst optimizer; same stack as your ETL; higher interactive latency
Flink SQL    → streaming (P04)

2. Optimizer pipeline

SQL → logical plan → RULE-BASED (pushdown, pruning, fold) → COST-BASED (join order/strategy, stats)
    → physical plan (broadcast vs hash, exchanges)
EXPLAIN shows it. Debugging = read the plan: where's the scan? how big? full scan? which join?

3. Read less (pushdown + pruning)

predicate pushdown  → filter at the scan (P08 stats skip)
projection pushdown → only needed columns (3 of 50 ≈ 6% bytes)
partition pruning   → filter on PARTITION col skips partitions
                      (filter on NON-partition col → NO pruning → full scan!)
dynamic filtering    → small dim side builds a filter that prunes the big fact scan

4. Joins

broadcast      → small side to all workers, no shuffle (risk: not-small → OOM)
partitioned    → hash both sides by key, shuffle (default for 2 big tables)
join ORDER     → small tables first → small intermediates (final size is order-independent)
stats (ANALYZE) drive CBO; stale stats → bad plans (broadcast a huge table → OOM)

5. The $/TB law

Athena/Trino-on-S3 = $5 / TB scanned
1 TB scan vs 10 GB scan of the SAME data = 100× cost → fix is LAYOUT not hardware
levers: partition + project + sort/Z-order + compact (P06/P08/P09)
guardrails: workgroup per-query scan limits; cost-allocation tags

6. Concurrency & table design

Trino resource groups + memory limits + spill → isolate tenants
high-concurrency BI: partition on date, sort on secondary filter, compact, pre-agg marts
keep ONE source of truth + lineage (don't fork business logic into every mart)

7. Catalog & governance

Glue Data Catalog = shared metastore (Athena/Trino/Spark/EMR all read it)
Lake Formation = table/column/row permissions + column masking, enforced at scan (P14)

8. Beginner mistakes that mark you

  1. Filtering on a non-partition column and expecting pruning.
  2. SELECT * (reads every column → full width → big bill).
  3. Querying a small-file swamp (compact first, P09).
  4. Unsorted data so row-group stats can't skip (sort/Z-order, P08).
  5. Stale stats → optimizer broadcasts a huge table → OOM.
  6. Throwing a bigger cluster at a layout problem (Athena has no such knob anyway).

9. War stories

  • "$50 query." → ~10 TB scanned: full scan + SELECT * + small files. Partition, project, compact → cents.
  • "Optimizer OOM'd on a join." → stale stats; broadcast a big table. ANALYZE.
  • "Dashboards slow at peak." → no resource groups / unpartitioned marts; isolate + pre-agg.

10. How this phase pays off later

  • Layout → cost closes the loop with P06/P08/P09.
  • Lake Formation masking → built in P14.
  • Athena-vs-Trino ADR → P15 decisions.
  • Analytics layer of capstone Problems 1 & 5 (P16).

Read WARMUP, build the optimizer, then P11: the other read path — low-latency serving (Cassandra/DynamoDB) for single-digit-ms lookups, not seconds-scale scans.