"Why Is My Query So Slow?"
Concepts covered: Small File Problem
Interviewers rarely say 'small files'. They describe a symptom and watch whether you reach for bytes or for objects. The signature is a mismatch: the data a query reads stays flat while its latency climbs, usually with planning time or request count climbing too. Whenever cost tracks the number of files instead of the number of bytes, you are in this question, whatever its wording.
The same question in 5 disguises
- Slower over time, same volume: Bytes per day are steady, so the layout changed. Ask when the write cadence last moved; if it didn't, check the writer count and the partition key.
- Planning outlasts execution: Listing keys and reading per-file metadata is work that scales with file count and runs before a single row is read.
- Requests outgrow storage: Object stores charge per request, so a layout with 2,400 times more files issues roughly 2,400 times more reads per scan.
- Review this streaming design: No symptom is given. You are expected to predict the file count before anyone measures it, by multiplying commits by tasks and by the partitions each commit touches.
- A fine-grained partition key: Each partition owns its files, so bytes per partition shrink while file count multiplies with every new key value.
Diagnose with file counts first
Per partition, ask for the file count and the median file size, with total bytes alongside them. Then compare the median with a target size. For columnar files scanned in bulk, teams commonly choose between 128 MB and 512 MB; this lesson uses 256 MB. A table format exposes a queryable file inventory, and a plain directory needs a listing job to build one. Given an inventory table trip_events_files with 1 row per data file, a single query is enough to make the diagnosis.
SELECT
date_trunc('day', partition_hour) AS event_day,
count(*) AS file_count,
approx_percentile(file_size_bytes, 0.5) / 1048576.0 AS median_mb,
sum(file_size_bytes) / 1073741824.0 AS total_gb
FROM trip_events_files
GROUP BY date_trunc('day', partition_hour)
ORDER BY event_day DESC| event_day | file_count | median_mb | total_gb |
|---|---|---|---|
| 2026-10-22 | 288,000 | 0.1 | 30.1 |
| 2026-10-21 | 288,000 | 0.1 | 29.8 |
| 2026-07-14 | 4,800 | 6.4 | 30.2 |
The July row predates the cadence change. Volume matches on both sides, while file count rose 60 times and the median file shrank from 6.4 MB to around 0.1 MB. That comparison moves the conversation from 'the engine is slow' to 'the layout is wrong'. The second check is the query profile: if planning, which covers listing keys and reading per-file metadata, takes tens of seconds on 30 GB, metadata is the bottleneck and extra compute will not move it.
- trip_queue (queue, message queue)
- minute_writer (transform, streaming job); freshness: 1 min; parallelism: 200 tasks
- trip_events (storage, lake table, hourly partitions)
- trips_dashboard (consumer, dashboard)
- trip_queue -> minute_writer
- minute_writer -> trip_events
- trip_events -> trips_dashboard
Scope the answer before giving it
- What freshness do consumers need: 1 minute for every reader, or only for an operations screen?
- Which predicates do the top queries use, and on which columns?
- Do other jobs update or delete rows in this table, and how often?
- How late can events arrive, and must late rows reach the curated data?
- Who owns table maintenance: each pipeline, or a shared platform service?
Each answer moves the design. A 1-minute freshness need forces a compaction step after the writer; a frequent filter on city justifies sorting; row-level deletes create conflicts with any rewrite; late arrivals decide how compaction picks its partitions; a shared maintenance service changes who gets paged when file counts climb. Ask these before you propose anything.
Try it. Before the next section does the arithmetic, estimate it yourself: each file costs 2 requests, footer then data, at 20 ms each, and the cluster has 100 reader slots. How long does opening yesterday's 288,000 files take before any row is decoded?
About this lesson
This section is part of the Small Files and Compaction: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.