Ewance
← All challenges Database Administrator

Cut a 42-Minute Trino Query to Under Five Minutes

Duration
2 weeks
Level
Advanced
Type
Design
Team
1 to 5 people

The situation

The company is a Singapore-based cryptocurrency exchange whose analyst team depends on a single Trino query over an Iceberg-on-S3 warehouse to study trading behavior across products; at 80 runs a day the query's cost and latency have become a recurring operational drag.

Your task

Redesign a 42-minute, USD 18-per-run Trino analytics query so it runs under 5 minutes for under USD 4, and document the optimization pattern so other analysts can reuse it.

The brief

What you do

  1. Using the slow query, the EXPLAIN ANALYZE readout, the Iceberg table schemas, and the 30-day execution-metrics dataset provided, design an optimization proposal that cuts the cross-product trader analytics query from 42 minutes to under 5 minutes and from about USD 18 to under USD 4 per run.
  2. Profile the query against the EXPLAIN ANALYZE readout to name the top cost stages (expect broadcast joins on large tables, missing partition pruning, and redundant repeated subqueries).
  3. Propose four to six concrete optimizations spanning query rewrites, table-level changes (sort/cluster ordering, file compaction), and engine settings (worker concurrency, dynamic filtering), each justified against a specific stage in the readout or a column in the metrics dataset.
  4. Measure before and after on both a cold and a warm cache, and report dollar cost honestly including worker time.

Success looks like

Success looks like: targets met, every claimed gain backed by a measurement, and a pattern memo another analyst could follow on a different query without your help.

What you will practice

  • Read a Trino EXPLAIN ANALYZE plan to locate the stages that dominate runtime and bytes scanned
  • Apply Iceberg table-design levers (partition pruning, sort/cluster ordering, compaction) to reduce data read
  • Choose between query-level, table-level, and engine-level optimizations and justify each against evidence
  • Benchmark honestly across cold and warm caches and convert worker time into a defensible dollar cost
  • Distil a specific fix into a transferable pattern others can apply without the author present

A suggested way in

  1. Start from the EXPLAIN ANALYZE readout: rank stages by wall time and bytes scanned before changing anything
  2. Separate cause from cure — confirm a broadcast join or full scan is the bottleneck before rewriting it
  3. Measure one change at a time so each reported gain is attributable, and always record cold vs warm cache
  4. Express cost as worker-seconds times rate; state your assumptions so the USD figure is auditable

Skills you will practice

Trino, SQL optimization, Iceberg, Query tuning, Benchmarking, Documentation

Where it leads

Analytics Database Administrator, Data Performance Engineer, Analytics Platform Engineer

How it is assessed

  • Profiling depth25%

    Top cost stages are ranked with direct evidence from the provided EXPLAIN ANALYZE readout and the execution-metrics dataset.

  • Optimization effectiveness35%

    The rewritten query plus table and engine changes meet the sub-5-minute and sub-USD-4 targets against the provided Iceberg schemas and query.

  • Benchmark honesty20%

    Before/after results report both cold and warm cache and an auditable per-run dollar cost derived from the execution-metrics dataset.

  • Memo reusability20%

    The pattern memo lets another analyst apply the approach to a similar query without the author, generalising beyond the provided query.

Resources

  • TrinoTool

    The query engine being tuned; used for EXPLAIN ANALYZE profiling and before/after benchmarking.

  • Apache IcebergTool

    The table format whose partition specs, sort orders, and compaction are the table-level optimization levers.

  • Google SheetsTool

    To tabulate the cold/warm benchmark runs and compute the auditable per-run dollar cost.

  • NotionTool

    To author the optimization proposal and the reusable six-page pattern memo.

Sample Ewance certificate of completion, verified by LearnCoin
Sample certificate
Your proof

Finish it. Earn this.

A verified certificate with your name. Anyone can check it.

Time to practice.

Solve this challenge
Related challenges

More like this.