System design interview

Optimize Query Performance in Warehouse

MediumLocked55 min read

Speed up a 30-minute query on 10TB: pruning, clustering, MVs, rewrite, warehouse sizing.

system-designwarehouseperformancesql

Start here

The problem in plain words

A report query takes 30 minutes on a 10 TB table. The first instinct is to buy a bigger warehouse. But picture looking for one name in a phone book by reading every page. A faster reader still reads every page. The real fix is to stop reading pages you do not need.

Most slow analytical queries are slow because they read far more data than they use. They have no date filter, they select every column, or the table is laid out so the engine cannot skip anything. Fixing that is usually cheaper and faster than adding machines.

So the approach is detective work in a fixed order: read the query plan, see how many bytes are scanned, cut them down with filters and layout, rewrite the query, precompute if it is run often, and only then think about warehouse size.

Words you will meet

Warehouse
The compute that runs queries in a cloud data warehouse. Bigger means more power and more cost.
Full scan
Reading every row and column of a table.
Bytes scanned
How much data the query actually read. The best single number to watch.
Predicate / filter
A condition such as order_date >= a date that limits which rows are needed.
Pruning
Skipping whole partitions or files that cannot match the filter.
Partition
A table split into chunks by a column such as date, so a filter can skip chunks.
Clustering
Storing rows with similar values near each other, so filters on that column skip more.
Column projection
Reading only the columns you need. SELECT * does the opposite.
Query plan
The engine's own description of how it will run a query, and what it costs.
Materialized view (MV)
A precomputed result that is kept up to date, so repeated queries are instant.
30 minutes because it reads nearly all of 10 TB
scan allslowReport queryno date filter10 TB tableall partitions readResultafter 30 minutes
Press Play to watch it move

Each arrow is one thing happening. Play it step by step, or read the list below.

  1. 1.Without a date filter, the engine cannot skip anything. It reads almost every byte.
  2. 2.A bigger warehouse only reads the same bytes a little faster. The query is still wasteful.

The numbers, as things you can picture

  • 10 TB table, 30 minutesA selective date range could cut the read to a small fraction, but the query does not use it.

What the interviewer wants

Optimise the query step by step, using evidence at each step: plan and bytes, filters and layout, rewrite, materialised views, then warehouse size.

Interview room

10TB query takes 30 minutes: prune, cluster, MV, rewrite, size. Evidence before folklore.

Basics

Bytes scanned dominate. Partition pruning. Clustering. Rewrite without SELECT *. MVs for repeated grains. Size after scan health.

Prompt

Plan/bytes; layout; SQL; aggregates; sizing; keep metric meaning.

Good

Bytes first; prune; rewrite; cluster; MV; then size.

Clarify/scope

Interactive vs ELT; can add aggregates; concurrency.

Scoring lens: Optimize Query Performance in Warehouse

Interviewers reward incident-first teaching, a clear control loop, and named owners. Tool logos without that loop score poorly.

Weak vs strong answers on Optimize Query Performance in Warehouse

Weak: list products. Strong: tell the failure story, define the mechanism, draw the path, state rollout and monitors.

Scope control

Say what is out of scope for v1. Come back only if follow-ups demand it. This is a seniority signal for Optimize Query Performance in Warehouse.

Clarifying then committing

Ask a few high-use questions about SLAs, owners, and scale, then sketch. Endless questions without a diagram look evasive.

Before/after scoreboard

Record bytes scanned, partitions pruned, runtime, and warehouse size before and after each change. Optimization without numbers is storytelling.

Semantic safety

If a "rewrite" adds filters that drop late events, call it a metric change. Performance work that alters KPIs is a data incident.

Concurrency after scan health

Once queries scan 100GB not 10TB, warehouse sizing and isolation matter. Until then, XL is an expensive waiting room.

Interview framing detail 1

For lb-sd-36, spend the first minutes making the problem concrete: who gets hurt when this fails, what SLA is implied, and what is explicitly out of scope. Then teach the prerequisite concept before proposing boxes. A candidate who names vendors first usually loses the plot. Write two clarifying questions on the board and answer them with assumptions if the interviewer shrugs. Keep the first diagram small enough to redraw when a follow-up changes a constraint.