Query Engines and Performance Optimization
This domain tests engine selection and tuning: when to use Presto versus Spark, how Iceberg layout affects scan cost, and which knobs actually change a slow query.
Engines
Presto is the MPP SQL engine for interactive, federated queries across catalogs; it is read-optimized. Spark handles batch ETL, complex transforms, and ML. Both can query the same Iceberg tables.
Performance rules
- Use columnar Parquet or ORC, not CSV, for repeated analytics.
- Compact small files; too many small files dominates planning and scan time.
- Partition on low-to-moderate cardinality columns that appear in filters; over-partitioning creates small files.
- Keep statistics current (ANALYZE or Iceberg metadata) so the optimizer can prune and choose joins.
- Filter early and avoid SELECT *; predicate pushdown only helps when the predicate reaches the scan.
- Size engines for concurrency, not just data volume; use auto-scale and suspend.
Worked example
A Presto query joins a 10 GB fact table to a 5 MB dimension and runs slowly. The fact table is partitioned by ingestion timestamp, a high-cardinality column no query filters on, creating thousands of tiny files. Fix: rewrite the table partitioned by sale_date, compact files to a reasonable target size, collect stats, and let Presto broadcast the small dimension. Reasoning: partition pruning removes data; compaction reduces file overhead; broadcast avoids shuffling the large table.
Traps
- Partitioning by a unique ID or ingestion timestamp.
- Assuming a bigger engine fixes a bad file layout.
- Using Presto for large writes; Spark is the ETL engine.
- Cross-catalog joins without checking that both catalogs are reachable from the engine.