Articles

Redshift Query Optimization Interview Questions: Distribution Skew, Sort Keys, and EXPLAIN

Practice Redshift query optimization with distribution skew, sort-key pruning, EXPLAIN estimates, runtime evidence, and correctness checks.

A Redshift query has a colocated join and still runs slowly. Another query broadcasts a small dimension and finishes quickly. If your interview answer treats every redistribution label as a verdict, it can miss the real issue: the amount of data involved and where the work lands.

Redshift query optimization interview questions are about connecting a plan to execution evidence. First establish whether the query scans too much, moves too much data, or assigns too much work to a small part of the system. Then test a change against the workload and the correct result.

Evidence boundary: AWS documentation supplies the Redshift behavior below. The tenant-order report, evidence ledger, and comparison protocol are original practice exercises. They are not candidate reports, real production measurements, or claims about an employer's questions. Practice the modeling side with Design an e-commerce analytics warehouse.

What should you establish before changing a distribution key?

Ask for the query, expected result grain, table sizes, filters, data distribution, and the workload that shares those tables. Identify whether the environment is provisioned or serverless and which monitoring views and permissions are available.

For the original exercise, imagine a large orders fact table and an accounts dimension. The report returns paid order revenue by account segment for one day. Account IDs are not globally unique: they are unique within a tenant. One tenant owns a disproportionate share of recent orders.

Before discussing speed, require the join to include both tenant_id and account_id. Otherwise, the report may match accounts from different tenants and multiply revenue. A faster plan for that query would still be wrong.

Record the time range and status predicate precisely. Does “one day” use UTC or a business time zone? Are refunded orders excluded? Does the amount represent the order total or a line-item total? These details determine whether a proposed rewrite preserves the result.

Then ask whether the delay is execution time or time waiting for resources. A physical table change is an indirect response to a queueing problem. Keep the resource environment stable enough that before-and-after comparisons mean something.

What does EXPLAIN tell you, and what does it not?

Official AWS behavior: Redshift EXPLAIN shows the plan without executing the query. Its estimated costs and row counts are planning information, not measured elapsed times. Do not import another database's EXPLAIN ANALYZE workflow without checking the Redshift interface.

Read from the scans through joins, redistribution, and aggregation. Name the rows that each stage is expected to produce. A large final estimate may be less informative than the first lower stage where the row count becomes implausible.

For the tenant-order exercise, trace the daily filter, paid-status filter, account join, and segment aggregation. If the account join is expected to multiply the order rows, check key uniqueness and join conditions before evaluating physical distribution.

Pair the plan with an actual query execution in an appropriate test environment. Record its query ID, elapsed execution, scanned work, movement, skew, and spill evidence using the available system views. Do not label a plan estimate “actual rows” merely because it came from the database.

A useful interview answer distinguishes observation from hypothesis: “The plan predicts redistribution of both inputs. I would measure the bytes and step duration to see whether that movement dominates before changing table layout.”

How should you interpret distribution labels?

Official plan terminology: AWS's query-plan evaluation guide describes DS_DIST_NONE as no redistribution for the join, DS_BCAST_INNER as broadcasting the inner input, and DS_DIST_BOTH as redistributing both inputs. DS_DIST_ALL_INNER can concentrate the inner input onto a single slice because of the outer table's ALL distribution.

The label identifies a movement pattern. Its practical cost depends on the rows and bytes that reach that step. Broadcasting a genuinely small filtered dimension can be a reasonable trade-off; broadcasting a wide, large intermediate result is a different situation.

Use this decision ledger rather than a blanket rule:

Plan or execution evidence	Next question	Candidate experiment
Both large inputs redistributed	Can a frequent join be colocated?	Compare a representative alternative distribution
Small dimension broadcast	Is its actual filtered size small?	Measure before adding replication cost
No redistribution but slow join	Is work concentrated or spilling?	Inspect skew and per-step work
Large join output	Is the join grain correct?	Validate composite keys and duplicate dimension rows
Single-slice concentration	Does distribution choice serialize work?	Test a layout that restores parallel work

A colocated join can still be expensive. It may process too many rows, receive a skewed key distribution, or build an oversized hash table. Removing network movement does not eliminate every other resource cost.

Likewise, optimizing one join can make other important joins worse. A fact table participates in a workload, not just the statement shown in the interview. Describe which joins and filters justify the choice.

How can a distribution key create skew?

Official physical-design options: AWS's distribution-style documentation describes AUTO, EVEN, KEY, and ALL approaches. They trade off placement, colocation, and replication. AUTO lets Redshift manage the distribution decision as table characteristics evolve.

For the exercise, distributing by tenant_id might colocate tenant-based work, but a dominant tenant can place a large share of that work together. A high number of distinct tenants does not guarantee balance if one tenant owns most of the rows.

Measure the distribution that matters after filtering as well as across the full table. A table can look balanced over several years while today's selected rows are concentrated. Conversely, a storage imbalance does not prove it is the critical cause for a query that touches a small, well-distributed subset.

Profile nulls, frequent values, and the relationship between the candidate key and common predicates. Compare the largest groups with typical groups. The relevant question is whether the intended parallel work is balanced enough for this workload.

An alternative key might spread orders more evenly but increase movement for the account join. That can still be better if the skew reduction outweighs redistribution. Treat it as an experiment with clear measurements, not an abstract contest between perfectly balanced data and perfectly colocated joins.

What does a sort key change?

Official AWS mechanism: sort-key guidance explains that Redshift tracks block value ranges and can skip blocks outside qualifying predicates. A useful sort order can reduce scanned work; AUTO can manage the choice through automatic table optimization.

For the daily report, a leading timestamp sort key is a plausible candidate when recent time-range queries dominate. The reason is the opportunity to eliminate irrelevant blocks, not a claim that a sort key is a conventional B-tree index.

Write the predicate as an explicit range:

WHERE o.created_at >= TIMESTAMP '2026-09-01 00:00:00'
  AND o.created_at <  TIMESTAMP '2026-09-02 00:00:00'
  AND o.status = 'paid'

This exercise assumes the timestamp column and bounds use the same intended time convention. A half-open interval avoids double-counting the next midnight when adjacent daily reports are combined.

Do not assume that a date predicate automatically proves efficient pruning. Inspect the actual scan evidence and the table's physical condition. If the query touches most blocks anyway, explain why the proposed sort order did not deliver the expected benefit.

Also examine the workload trade-off. A table used mainly for tenant lookups may have a different useful order from a table scanned globally by day. A physical design should support the common expensive patterns, with load and maintenance costs included.

When do statistics matter more than sorting?

Official AWS behavior: ANALYZE updates statistical metadata used by the planner. Redshift also performs automatic analysis. Statistics and physical sort order are different concerns; updating one is not a guarantee that the other changed.

If the estimate predicts a tiny filtered result and execution processes a much larger one, investigate the earliest estimate mismatch. Recent loading, changed value distributions, or unusual parameters may make the planning assumptions unrepresentative.

Do not recommend a blanket maintenance command before checking what is already current. A scheduled or automatic process may have performed the necessary work. Record the evidence for stale statistics and choose a targeted action when warranted.

After refreshing relevant statistics, compare the plan and execution again. If estimates improve but time does not, the main cost may be scanning, movement, skew, or resource contention. A correct estimate is useful, but it does not make an intrinsically large workload small.

If sorting is the concern, inspect that separately and use the environment's supported maintenance and automatic-optimization behavior. Avoid treating “run ANALYZE” as shorthand for reorganizing the table.

Which runtime signals should you collect?

Official monitoring reference: SYS_QUERY_DETAIL exposes execution information at multiple metric levels, including rows, bytes, blocks, skew indicators, and local or remote spill fields. Not every field applies to every query type, and visibility depends on the user's access.

Choose a consistent metric level before comparing or aggregating rows. Summing parent and child measurements together can double-count work and create a misleading diagnosis. Associate the execution evidence with the query and relevant plan step.

For the original report, build a compact record: query identity, parameters, result row count, execution duration, scan work, redistribution evidence, skew, and spills. Keep queueing and compilation context separate so that a faster warm run is not mistaken for an improvement caused by your table change.

Spill is a clue, not an automatic instruction to add memory. Check whether a missing join predicate inflated the input, whether unneeded wide columns were carried forward, and whether filtering can happen earlier without changing semantics.

If you cannot access runtime metrics in the interview, say what evidence is missing and give a conditional recommendation. “If the redistribution step dominates, I would compare colocation; if a colocated step is skewed, I would test a better-balanced layout.” That is stronger than inventing execution facts from an estimated plan.

How do you compare alternatives without changing the answer?

Use representative data and multiple parameter sets: a typical tenant, the largest tenant, a narrow day, and a broader reporting period. Include the load or refresh path if the layout adds replication or maintenance work.

For each candidate, compare result counts and aggregates at the intended grain. A total alone can conceal offsetting errors. Reconcile by tenant and segment, check unmatched accounts, and include duplicate dimension-key fixtures to expose accidental fanout.

The original exercise's correctness invariant is that each qualifying order contributes its amount exactly once to its tenant's account segment. That invariant must survive pre-aggregation, predicate movement, and a physical-layout change.

Change one major factor at a time. Keep query text, data snapshot, and capacity comparable when evaluating distribution. Keep the layout fixed when evaluating a query rewrite. Document cache and compilation conditions rather than selecting the fastest convenient run.

A small experiment matrix can be enough: current layout, alternative balanced key, and a justified dimension strategy. Record why each exists and what outcome would reject it. Do not create an exhaustive grid of every possible key when the evidence points to a narrower question.

Keep a rollback path. A benchmark that helps one report but slows ingestion or common dashboards may fail the workload-level objective even when its headline query is faster.

What should your interview answer conclude?

Lead with the observed bottleneck and its uncertainty. “The join is colocated, but the selected tenant concentrates most work. I would compare a distribution that spreads that workload, measure the added movement, and preserve the composite tenant/account join.”

Or: “The daily filter still scans most of the table. I would inspect sort behavior and compare an order that improves time-range pruning before changing join distribution.”

The recommendation follows the evidence. Explain what would change your mind, how you would verify the same results, and which other workload costs you would measure before keeping the change.