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
- 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.
- 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).
- 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.
- 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
- Start from the EXPLAIN ANALYZE readout: rank stages by wall time and bytes scanned before changing anything
- Separate cause from cure — confirm a broadcast join or full scan is the bottleneck before rewriting it
- Measure one change at a time so each reported gain is attributable, and always record cold vs warm cache
- 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.
Finish it. Earn this.
A verified certificate with your name. Anyone can check it.
Time to practice.
Solve this challengeMore like this.
- Software EngineerGreedy Delivery-Slot Assignment for a Munich Grocery Startup
- Software EngineerDesign a Verified UART Receiver and Loopback for an FPGA Sensor Hub
- Data ArchitectDesigning a Data Warehouse for a Renewable Energy Firm
- AI Solutions ArchitectBuild an OWL Ontology for a Pharma R&D Knowledge Base
-
Greedy Delivery-Slot Assignment for a Munich Grocery Startup Software Engineer Using the provided order history sample, rider roster, and manual dispatch baseline, design and implement a greedy slot-assignment algorithm in Python greedy algorithmsalgorithm analysispython Open challenge → -
Design a Verified UART Receiver and Loopback for an FPGA Sensor Hub Software Engineer Propose and deliver a UART receiver for Helax's sensor hub that meets the functional requirements in the requirements brief (the 'uart-requirements-br veriloguartfpga Open challenge → -
Designing a Data Warehouse for a Renewable Energy Firm Data Architect You are given sample data from three sources: energy production logs, weather data, and equipment maintenance records. Your task is to: (1) design a s data warehousingstar schemaetl Open challenge → -
Build an OWL Ontology for a Pharma R&D Knowledge Base AI Solutions Architect You receive a CSV-form starter knowledge base (around 4,000 compounds, 600 targets, 1,200 assays) and a list of 12 competency questions the scientists ontology designowlknowledge representation Open challenge →