Amazon Redshift Deep Dive: RA3, Spectrum, WLM, Sort/Dist Keys & Concurrency Scaling

Amazon Redshift performance is not one decision — it is a stack of five coupled knobs, and the reason senior data engineers keep getting the interview wrong is that they memorise definitions ("RA3 decouples storage") instead of reasoning about how the knobs interact under a real workload. The same cluster can return a dashboard in 200 milliseconds or three minutes depending entirely on whether the fact table's distribution key collocates the join, whether the sort key lets the scan skip 95% of the blocks, whether the query landed in a starved WLM queue, and whether the cold partitions it touched live in local managed storage or out in S3 behind Redshift Spectrum. None of those knobs is hard on its own; the difficulty — and the interview signal — is knowing which one is your bottleneck and what it costs to turn it.

This guide is the senior-data-engineering walkthrough for the five knobs an interviewer will actually drill: RA3 nodes and managed storage (scale compute and storage independently), table design via distribution style and sort keys (collocate joins, skip blocks, avoid skew), workload management with concurrency scaling (queue by priority, kill runaways, burst reads to a transient cluster), Spectrum external tables (query partitioned S3 data in place), and the VACUUM / ANALYZE maintenance that keeps local tables fast. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, sharpen query tuning on the optimization practice library →, and firm up the modelling fundamentals on the database practice library →.

---

On this page

• Why the Redshift design choices determine everything downstream
• RA3 nodes and managed storage
• Distribution style and sort keys
• WLM and concurrency scaling
• Redshift Spectrum and table maintenance
• Cheat sheet — Amazon Redshift tuning recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why the Redshift design choices determine everything downstream

Five coupled knobs, one shared query plan — the choices bind the cluster for its whole life

The one-sentence invariant: Amazon Redshift is a massively-parallel columnar warehouse where a fixed set of design choices — node type (RA3 vs DC2), table distribution and sort, WLM queues plus concurrency scaling, Spectrum for external S3 data, and VACUUM/ANALYZE hygiene — jointly determine how much data every query moves across the network, how many blocks it scans, and how long it waits in a queue, and those choices are baked into the physical layout in a way you cannot undo without rewriting tables. A warehouse is not "slow" in the abstract; it is slow because a specific query redistributed a billion rows across slices, or scanned an unsorted table end to end, or sat behind a runaway report in a memory-starved queue. Every one of those is a knob you chose — or defaulted — months earlier.

The four axes interviewers actually probe.

• Data distribution and skew. How are the rows of each table spread across the compute slices? A good distribution key collocates the two sides of a join on the same slice so no data moves; a bad one broadcasts the inner table to every slice or piles a low-cardinality value onto one slice while the others idle. Interviewers open here because distribution is the single biggest lever on join cost and the one most people get wrong.
• Compute–storage coupling. On DC2 the disk is the node — grow storage and you pay for compute you may not need. On RA3 the compute nodes are decoupled from Redshift Managed Storage on S3, so you size them separately. Knowing which model you are on decides whether "we're out of disk" means "resize the cluster" or "you already have room."
• Concurrency under load. Redshift runs a bounded number of queries at once per queue; the rest wait. WLM decides how memory and slots are carved up; concurrency scaling decides whether a burst of read queries spills onto a transient cluster or forms a queue. Getting this wrong turns a fast cluster into a slow one at 9 a.m. on Monday.
• Cost. RA3 charges compute-hours plus managed storage per GB; Spectrum charges per byte scanned in S3; concurrency scaling burns credits. The senior answer never optimises latency without naming the dollar it trades — pausing an idle cluster, pruning Spectrum partitions, and right-sizing WLM are cost knobs as much as speed knobs.

The 2026 reality — sensible defaults, but the defaults are not free.

• RA3 + managed storage is the default node family for any new cluster: you size compute for the query workload and let storage grow on S3 behind the scenes. DC2 lingers only for tiny, storage-light, latency-sensitive clusters; DS2 is legacy.
• AUTO distribution and Automatic WLM are the out-of-the-box defaults, and they are genuinely good — but they are heuristics. A skewed key or a priority-inverted queue still needs a human who can read SVV_TABLE_INFO and STL_WLM_QUERY and override the default.
• Concurrency scaling absorbs read bursts transparently and bills in credits, one free hour accrued per day of use. It is not a substitute for fixing a badly-distributed table — it scales reads, not writes, and it cannot rescue a query that is slow because it scans an unsorted table.
• Spectrum turns Redshift into a lake-house front end: hot data lives in managed storage, cold history lives as Parquet in S3, and an external schema over the Glue Data Catalog lets one query span both. The cost model flips from "compute-hours" to "bytes scanned," which rewards partitioning and columnar formats and punishes SELECT * over unpartitioned JSON.

What interviewers listen for.

• Do you reason about the distribution key from the join, not from "the primary key"? — senior signal.
• Do you say a sort key lets the scan skip blocks via zone maps, rather than "it sorts the table"? — required answer.
• Do you separate RA3 compute from managed storage when asked about scaling? — required answer.
• Do you name WLM queue design and QMR when asked why a cluster is slow under load, not just "add nodes"? — senior signal.
• Do you describe Spectrum cost as bytes scanned and reach for partitioning and Parquet? — senior signal.

Worked example — the five-knob tuning map

Detailed explanation. The single most useful artifact for a Redshift interview is a memorised map of the five knobs, what each one controls, and the symptom you see when it is set wrong. Every senior Redshift discussion converges on this map within the first ten minutes; having it in your head is what separates a fluent diagnosis from a guess-and-check one. Walk through building the map for a hypothetical retail cluster with an orders fact table joined to a customers dimension and a decade of cold history.

• The cluster. 4× ra3.4xlarge, ~6 TB in managed storage, ~200 dashboards, nightly ETL plus daytime BI.
• The hot path. orders ⋈ customers on customer_id, filtered by order_date, aggregated by region.
• The cold path. Ten years of orders_archive as Parquet in S3, queried monthly for trend reports.
• The pain. Dashboards are fast at 6 a.m., slow at 9 a.m.; the monthly trend report scans terabytes.

Question. Build the five-knob map for this cluster and name the symptom each knob produces when it is set wrong.

Input.

Knob	Controls	Symptom when wrong
Node type (RA3)	compute vs storage sizing	"out of disk" on DC2; over-paying for idle compute
Distribution	data movement across slices	joins broadcast/redistribute; one slice hot
Sort key	blocks scanned per query	full-table scans on a date-filtered query
WLM + concurrency	queueing under load	fast at 6 a.m., queued at 9 a.m.
Spectrum + VACUUM	cold data + table hygiene	terabyte scans; bloated unsorted local tables

Code.

-- Inspect the levers before touching anything

-- 1. Distribution + sort health per table (skew, unsorted %, dist style)
SELECT "table",
       diststyle,
       sortkey1,
       skew_rows,          -- ratio of rows on the most- vs least-loaded slice
       unsorted            -- % of rows not in sort order (VACUUM candidate)
FROM   svv_table_info
ORDER  BY size DESC
LIMIT  20;

-- 2. Which queries redistributed the most data (join-cost hot spots)
SELECT query, SUM(rows) AS rows_redistributed
FROM   stl_dist
GROUP  BY query
ORDER  BY rows_redistributed DESC
LIMIT  10;

-- 3. Which queries queued and for how long (WLM pressure)
SELECT service_class, num_queued_queries, avg_queue_time_us / 1e6 AS avg_queue_s
FROM   stl_wlm_query
WHERE  service_class > 5
ORDER  BY avg_queue_s DESC;

Step-by-step explanation.

1. svv_table_info is the first place a senior engineer looks: diststyle, sortkey1, skew_rows, and unsorted in one row per table tell you whether distribution, sort, and hygiene are healthy before you ever run EXPLAIN. A skew_rows far above 1.0 means one slice holds most of the table.
2. stl_dist records how many rows each query redistributed across the network. High values mean the join's distribution key is wrong — the planner had to move rows to line the join up, which is pure overhead you can design away.
3. stl_wlm_query exposes queue time per service class. Non-zero avg_queue_s on a BI queue is the "slow at 9 a.m." symptom — the queries are not slow, they are waiting, which is a WLM and concurrency-scaling problem, not a table-design problem.
4. The map forces you to localise the bottleneck before acting. "The cluster is slow" is not actionable; "the orders table has skew_rows = 8 and the region rollup redistributes 900M rows" is. Each of the three queries above maps to exactly one knob.
5. Fixing knobs in the wrong order wastes weeks. Distribution and sort are physical-layout changes (rewrite the table); WLM is a config change (minutes); concurrency scaling is a toggle. Diagnose first, then fix cheapest-reversible-first.

Output.

Symptom observed	Knob to turn	Fix
orders region rollup redistributes 900M rows	distribution	DISTKEY(customer_id) on both sides
date-filtered scan reads whole table	sort key	SORTKEY(order_date) + VACUUM
BI queued 40 s at 9 a.m.	WLM + concurrency	separate BI queue + concurrency scaling ON
monthly report scans 4 TB	Spectrum	partition archive by month; prune
orders 45% unsorted	VACUUM	scheduled VACUUM + let auto-vacuum run

Rule of thumb. Never tune a Redshift cluster by adding nodes first. Read svv_table_info, stl_dist, and stl_wlm_query, localise the bottleneck to exactly one of the five knobs, then turn that knob. More nodes hide a bad distribution key; they do not fix it.

Worked example — what a senior Redshift interview actually probes

Detailed explanation. The senior Redshift interview has a predictable shape: the interviewer describes a slow query or a scaling problem, then narrows to test whether you know which knob owns the symptom. Candidates who name the knob and its evidence table score highest; candidates who say "add more nodes" or "it's just big data" score lowest. Walk through the grading rubric.

• Opener. "This dashboard query got slow after we 10×'d the data — what do you check?" — invites you to name distribution and sort.
• Follow-up 1. "Storage is almost full — how do you scale?" — probes the RA3/DC2 axis.
• Follow-up 2. "It's fast alone but slow during business hours." — probes WLM and concurrency.
• Follow-up 3. "We keep ten years of history but rarely query it." — probes Spectrum.
• Follow-up 4. "Deletes and updates made a table slow again." — probes VACUUM/ANALYZE.

Question. Draft a 5-minute senior Redshift answer that covers all five knobs without waiting to be asked each one.

Input.

Interview signal	Weak answer	Senior answer
Slow join	"add nodes"	"check the DISTKEY collocates the join in EXPLAIN"
Storage full	"resize"	"on RA3 storage is managed; grow compute only if compute-bound"
Slow under load	"it's busy"	"route BI to its own WLM queue; turn on concurrency scaling"
Cold history	"keep it in Redshift"	"archive to Parquet in S3, query via Spectrum, partition by month"
Degraded table	"rebuild it"	"VACUUM to re-sort + reclaim, ANALYZE to refresh stats"

Code.

Senior Redshift answer template (5 minutes)
============================================

Minute 1 — localise, don't guess
  "First I'd read svv_table_info and the query's EXPLAIN. Slow joins are
   almost always distribution: is the plan DS_DIST_NONE, or is it
   broadcasting the inner table? Slow scans are almost always sort: is
   the sort key the column we filter on, and is the table VACUUM'd?"

Minute 2 — node type / storage
  "We're on RA3, so storage is decoupled — managed storage grows on S3
   automatically. 'Almost full' rarely means resize; it means check
   managed-storage usage. I only add/resize compute if we're CPU- or
   memory-bound, and I'd elastic-resize for that."

Minute 3 — WLM + concurrency
  "Fast-alone-slow-at-9am is queueing, not query cost. I'd put ETL and
   BI in separate Automatic WLM queues with priorities, add a QMR to
   abort runaway scans, and turn on concurrency scaling so read bursts
   spill to a transient cluster instead of queueing."

Minute 4 — Spectrum for cold data
  "Ten years of rarely-touched history shouldn't sit in managed storage.
   I'd unload it to partitioned Parquet in S3 and expose it via a
   Spectrum external table, then UNION hot + cold in a view. Cost becomes
   bytes scanned, so partition by date and the monthly report prunes to
   one month."

Minute 5 — maintenance
  "Heavy delete/update churn leaves rows unsorted and dead. VACUUM re-sorts
   and reclaims space; ANALYZE refreshes the stats the planner needs.
   Auto-vacuum and auto-analyze handle most of it now, but I still
   schedule VACUUM on high-churn tables and watch the unsorted %."

Step-by-step explanation.

1. Minute 1 is the crucial framing: localise before you fix. Naming svv_table_info and EXPLAIN signals you diagnose from evidence, not vibes. Weak candidates reach for "more nodes" before they know whether the problem is distribution, sort, or queueing.
2. Minute 2 preempts the storage trap. On RA3 "storage is full" usually is not a resize event, because managed storage grows on S3. Separating the storage question from the compute question is the senior tell.
3. Minute 3 reframes "slow under load" as queueing. The fix is WLM queue design plus concurrency scaling, not table redesign — a different knob entirely, and naming it unprompted scores.
4. Minute 4 shows cost awareness: cold data in managed storage is money wasted, and Spectrum's bytes-scanned model rewards partitioning. Reaching for the lake-house pattern is a senior signal.
5. Minute 5 closes the loop on hygiene. Knowing that VACUUM re-sorts and reclaims while ANALYZE refreshes stats — and that both are mostly automatic now but still worth scheduling on hot tables — shows you operate clusters, not just design them.

Output.

Grading criterion	Weak score	Senior score
Localises via EXPLAIN / system tables	rare	mandatory
Separates RA3 storage from compute	rare	required
Names WLM + concurrency for load	occasional	mandatory
Reaches for Spectrum on cold data	rare	senior signal
Names VACUUM + ANALYZE roles distinctly	rare	senior signal

Rule of thumb. The senior Redshift answer is a 5-minute monologue that walks all five knobs and names the evidence table for each. Rehearse it once; deploy it every time an interviewer says "the warehouse is slow."

Worked example — the "which knob" decision tree

Detailed explanation. Given a slow query or a scaling ask, the senior engineer runs a short decision tree in their head. Codifying the tree makes the diagnosis reproducible: any interviewer can hand you a symptom and you can walk the tree out loud to a knob. Walk through the tree with three canonical symptoms.

• Q1. Is the query slow alone, or only under load? → under load = WLM/concurrency; alone = go to Q2.
• Q2. Does EXPLAIN show redistribution (DS_DIST_INNER / DS_BCAST_INNER)? → yes = distribution key; no = go to Q3.
• Q3. Does the query filter on a column but still scan most blocks? → yes = sort key (or needs VACUUM); no = go to Q4.
• Q4. Is most of the scanned data cold history rarely queried? → yes = Spectrum + partitioning; no = go to Q5.
• Q5. Has the table seen heavy delete/update churn (high unsorted %, dead rows)? → yes = VACUUM + ANALYZE.

Question. Walk the decision tree for three symptoms and record the knob each ends up at.

Input.

Symptom	Q1 (under load?)	Q2 (redistribution?)	Q3 (scans too much?)	Q4 (cold?)
Region rollup slow alone	no	yes	—	—
BI slow only 9–11 a.m.	yes	—	—	—
Monthly trend report scans TBs	no	no	yes	yes

Code.

# Decision-tree helper (illustrative)
def pick_redshift_knob(slow_only_under_load: bool,
                       plan_redistributes: bool,
                       scans_too_many_blocks: bool,
                       mostly_cold_history: bool,
                       heavy_churn: bool) -> str:
    """Return the Redshift knob that owns a given symptom."""
    if slow_only_under_load:
        return "WLM + concurrency scaling"
    if plan_redistributes:
        return "distribution key"
    if scans_too_many_blocks:
        if mostly_cold_history:
            return "Spectrum + partitioning"
        return "sort key (VACUUM if unsorted)"
    if heavy_churn:
        return "VACUUM + ANALYZE"
    return "already tuned; consider elastic resize"


print(pick_redshift_knob(False, True,  False, False, False))
# → distribution key

print(pick_redshift_knob(True,  False, False, False, False))
# → WLM + concurrency scaling

print(pick_redshift_knob(False, False, True,  True,  False))
# → Spectrum + partitioning

Step-by-step explanation.

1. Symptom 1 — the region rollup is slow even with no other queries running, and EXPLAIN shows DS_DIST_INNER. Q1 = no, Q2 = yes → the distribution key is wrong; collocate the join.
2. Symptom 2 — BI is fast at 6 a.m. and slow at 9 a.m. Q1 = yes → this is queueing, owned by WLM and concurrency scaling; no table change will help.
3. Symptom 3 — the monthly report scans terabytes of ten-year-old data. Q1 = no, Q2 = no, Q3 = yes, Q4 = yes → move the cold history to S3 and query it via Spectrum, partitioned by month so the report prunes.
4. The tree is ordered by cheapest correct fix first: a WLM change is minutes, a VACUUM is a job, but redistribution or a sort-key change means rewriting the table — so you only get there when the cheaper branches are ruled out.
5. If the tree bottoms out with no knob (already tuned, no churn, no cold data, no redistribution), then the honest answer is "it's genuinely compute-bound; elastic-resize." That is the one case where more nodes is the right call — and you reach it last, not first.

Output.

Symptom	Knob	Action
Region rollup slow alone	distribution key	DISTKEY(customer_id) on both tables
BI slow 9–11 a.m.	WLM + concurrency	dedicated BI queue; concurrency scaling ON
Monthly report scans TBs	Spectrum	archive to Parquet; partition by month

Rule of thumb. The five-knob decision tree is a whiteboard-friendly answer. Practise walking it end-to-end so an interviewer can hand you any symptom and get a knob name — with its evidence table — in under 60 seconds.

Senior interview question on Redshift bottleneck diagnosis

A senior interviewer often opens with: "A dashboard query on a 4-node ra3.4xlarge cluster degraded from 300 ms to 25 s after the orders table grew 10×. It joins orders to customers on customer_id and filters on order_date. Walk me through how you'd diagnose it, what you'd look for in EXPLAIN and the system tables, and the exact table changes you'd make."

Solution Using EXPLAIN-driven diagnosis, a collocating DISTKEY, and a date SORTKEY

-- Step 1 — read the plan; look for redistribution + sequential scan cost
EXPLAIN
SELECT c.region, COUNT(*) AS orders, SUM(o.total_cents) AS revenue
FROM   orders    o
JOIN   customers c ON c.id = o.customer_id
WHERE  o.order_date >= DATE '2026-08-01'
GROUP  BY c.region;
-- Look for: "DS_BCAST_INNER" or "DS_DIST_INNER" (redistribution),
--           and a Seq Scan on orders with a high estimated cost.

-- Step 2 — confirm skew, sort, and dist style from the catalog
SELECT "table", diststyle, sortkey1, skew_rows, unsorted, tbl_rows
FROM   svv_table_info
WHERE  "table" IN ('orders', 'customers');

-- Step 3 — rebuild orders to collocate the join and sort on the filter column
CREATE TABLE orders_new
    DISTSTYLE KEY
    DISTKEY (customer_id)
    SORTKEY (order_date)
AS SELECT * FROM orders;

-- Distribute the dimension the same way so the join is DS_DIST_NONE
ALTER TABLE customers ALTER DISTSTYLE KEY DISTKEY (id);

-- Swap in the rebuilt table
ALTER TABLE orders     RENAME TO orders_old;
ALTER TABLE orders_new RENAME TO orders;

-- Step 4 — refresh stats + re-sort so the planner and zone maps are current
ANALYZE orders;
VACUUM  orders;

Step-by-step trace.

Step	Before (grown 10×)	After (rebuilt)
Join plan	DS_BCAST_INNER — customers broadcast to every slice	DS_DIST_NONE — collocated on customer_id
orders scan	Seq Scan, all blocks (unsorted)	zone-map skip to the order_date range
skew_rows	6.4 (one slice hot)	~1.1 (even)
unsorted	38%	0% after VACUUM
Rows moved per run	~900M redistributed	~0
Latency	25 s	~350 ms

After the rebuild, the join is collocated so no rows cross the network, the date filter uses the sort key's zone maps to read only August blocks, and skew drops so every slice does equal work. The ANALYZE refreshes the row-count and distribution statistics the planner relies on; the VACUUM sorts the freshly-loaded rows so the zone maps are tight.

Output:

Metric	Before	After
Query latency	25 s	~350 ms
Join redistribution	900M rows broadcast	0 (DS_DIST_NONE)
Blocks scanned on orders	full table	one month via zone map
Slice skew (skew_rows)	6.4	~1.1
Planner stats	stale	fresh (ANALYZE)

Why this works — concept by concept:

• DISTKEY on the join column — distributing both orders and customers by customer_id/id places matching rows on the same slice, so the join runs locally and the planner reports DS_DIST_NONE instead of broadcasting the inner table to every slice.
• SORTKEY on the filter column — sorting orders by order_date lets each block's zone map (min/max) tell the scanner which blocks can possibly match >= 2026-08-01, so it skips the other eleven months' blocks entirely.
• ANALYZE refreshes statistics — the cost-based planner chooses join order and distribution from row-count and value-distribution stats; after a 10× growth those stats are stale, so ANALYZE is what makes the planner pick the collocated plan.
• VACUUM re-sorts new rows — CREATE TABLE AS and bulk loads leave rows in load order, not sort order; VACUUM sorts them so the zone maps are tight and the block-skipping actually pays off.
• Cost — one table rewrite (O(rows), one-time) plus ongoing ANALYZE/VACUUM. The eliminated cost is ~900M rows redistributed per query and a full-table scan per run — turning an O(table) query into an O(one-month, collocated) query. Net: 70× lower latency for a one-time rebuild.

SQL
Topic — optimization
Query optimization and EXPLAIN-plan problems

Practice →

SQL Topic — sql SQL warehouse and analytics query problems

Practice →

---

2. RA3 nodes and managed storage

RA3 nodes decouple compute from Redshift Managed Storage — size the two independently and stop paying compute prices for cold bytes

The mental model in one line: an RA3 node is a compute node (vCPU, memory, and a large local SSD used purely as a cache) that reads and writes its data from Redshift Managed Storage (RMS) backed by Amazon S3, so you scale the number and size of compute nodes for your query workload and let storage grow on RMS independently — whereas the older DC2 nodes bundle a fixed local SSD with the compute, forcing you to add compute you may not need just to get more disk. This decoupling is the single biggest architectural change in modern Redshift, and it is the first thing a scaling question should make you reach for.

The four axes for RA3.

• Compute sizing. You choose the node size (ra3.xlplus, ra3.4xlarge, ra3.16xlarge) and count. Each node contributes vCPU, memory, and a set of slices that run query steps in parallel. Add nodes when queries are CPU- or memory-bound (spilling to disk, long execution alone), not when you are "out of space."
• Storage sizing. RMS grows automatically on S3; you pay per GB-month for what you store, independent of compute. "Out of disk" — the classic DC2 emergency — mostly disappears; the new discipline is watching managed-storage cost, not managed-storage capacity.
• The local cache. Each RA3 node keeps hot blocks on its local SSD so working-set queries hit local NVMe, not S3. Cold blocks are fetched from RMS on demand. A cluster that is too small for its working set thrashes the cache — the RA3 version of "not enough RAM."
• Elasticity and economics. RA3 supports elastic resize (add/remove nodes in minutes), pause/resume (stop paying for compute on an idle cluster while keeping the data), and data sharing (read another cluster's data without copying). These are the levers that make RA3 cheaper than a permanently-on DC2 fleet for spiky workloads.

RA3 vs DC2 vs DS2 — the node-family anatomy.

• RA3 (default in 2026). Managed storage decoupled from compute; local SSD is a cache, not the store of record. Best for almost everything: growing data, variable compute, and any cluster large enough to benefit from separating the two.
• DC2 (dense compute). Local SSD is the storage; compute and disk grow together. Only sensible for small (< a few TB), latency-sensitive clusters where the whole dataset fits in local SSD and you never need to scale storage past compute.
• DS2 (dense storage). Legacy HDD-backed nodes; migrate off them to RA3 — they are slower and no longer the right cost point.
• The migration tell. "We keep adding nodes just to fit the data" is the canonical reason to move DC2 → RA3: you are buying compute to hold bytes, which RA3 fixes by billing storage separately.

Data sharing, pause/resume, and elastic resize — the RA3 superpowers.

• Data sharing. A producer cluster exposes schemas that consumer clusters query live, no copy, no ETL. Enables a "one source, many compute" pattern — a heavy ETL producer plus lightweight BI consumers — each sized and billed on its own.
• Pause/resume. Stop compute billing on a dev or nightly cluster while it is idle; the data stays in RMS. Resume in minutes. This is a pure RA3 cost win — impossible on DC2 where pausing would strand the only copy of the data.
• Elastic resize. Change node count in minutes (data is redistributed across the new slice count). Use it to scale compute up for a heavy month-end and back down after — the storage never moves.

Common interview probes on RA3.

• "Storage is nearly full — what do you do?" — required answer: on RA3, managed storage grows automatically; check whether you are actually compute-bound before resizing.
• "When would you still pick DC2?" — small, storage-light, latency-sensitive clusters whose data fits in local SSD.
• "How do you serve BI and ETL from the same data without contention?" — data sharing: one producer, separate consumer clusters.
• "How do you cut cost on a cluster used only 8 hours a day?" — pause/resume off-hours; elastic-resize for peaks.

Worked example — sizing an RA3 cluster from the working set

Detailed explanation. The canonical RA3 sizing exercise: separate the storage question (how much data, answered by RMS pricing) from the compute question (how big is the hot working set and how concurrent is the workload, answered by node size and count). Walk through sizing a cluster for a 20 TB warehouse whose queries touch ~2 TB of recent data.

• Total data. 20 TB in managed storage.
• Hot working set. ~2 TB (last 90 days) touched by 95% of queries.
• Concurrency. ~15 concurrent BI queries at peak, plus nightly ETL.
• The lever. Size compute for the 2 TB working set + concurrency; let RMS hold all 20 TB.

Question. Choose an RA3 node size and count for this workload and justify each half of the decision independently.

Input.

Dimension	Value	Sizing driver
Total stored	20 TB	RMS (pay per GB; not a compute driver)
Hot working set	2 TB	local SSD cache size → node size × count
Peak concurrency	15 queries	slots × memory → node count
Nightly ETL	large writes	memory per slice → node size

Code.

-- 1. Measure the actual working set (bytes read from RMS vs served from cache)
SELECT SUM(CASE WHEN source = 'remote' THEN blocks ELSE 0 END) AS rms_blocks,
       SUM(CASE WHEN source = 'local'  THEN blocks ELSE 0 END) AS cache_blocks
FROM   (
    SELECT btrim(source) AS source, COUNT(*) AS blocks
    FROM   stl_scan
    WHERE  starttime > GETDATE() - INTERVAL '7 days'
    GROUP  BY 1
) t;
-- A high rms_blocks : cache_blocks ratio means the working set
-- doesn't fit in local SSD → go up a node size or add nodes.

-- 2. Check managed-storage usage (the STORAGE bill, not a capacity limit)
SELECT SUM(used_mb) / 1024.0 / 1024.0 AS managed_storage_tb
FROM   svv_storage_usage;         -- illustrative view name

-- 3. Elastic resize when compute-bound (storage stays put on RMS)
ALTER CLUSTER my_cluster
    SET node_type = 'ra3.4xlarge',
        number_of_nodes = 6;

# Illustrative sizing sketch
TOTAL_TB        = 20     # → RMS, billed per GB regardless of compute
WORKING_SET_TB  = 2      # → must fit comfortably in aggregate local SSD cache
PEAK_CONCURRENCY = 15    # → drives slot/memory needs → node count

# ra3.4xlarge: ~2.6 TB managed local cache per node (illustrative)
CACHE_TB_PER_NODE = 2.6
nodes_for_cache = max(2, round((WORKING_SET_TB * 1.5) / CACHE_TB_PER_NODE))  # headroom
print(f"Compute: {nodes_for_cache}x ra3.4xlarge sized for the {WORKING_SET_TB} TB hot set")
print(f"Storage: {TOTAL_TB} TB in RMS, billed separately — not a node-count driver")

Step-by-step explanation.

1. stl_scan tells you how much data is served from the local SSD cache (local) versus fetched from RMS (remote). A working set that does not fit in cache shows up as a high remote:local ratio and slow first-touch queries — the signal to go up a node size or add nodes.
2. Managed-storage usage is a cost line, not a capacity emergency: 20 TB in RMS bills per GB-month whether you run 2 nodes or 20. This is exactly the decoupling that lets you size compute for the 2 TB hot set and ignore the other 18 TB when choosing node count.
3. The sizing sketch drives node count from the working set (must fit in aggregate cache with headroom) and from concurrency (enough slots and memory so 15 queries do not queue), never from total data volume — that is the RA3 mental shift.
4. Elastic resize changes node count in minutes and redistributes data across the new slice count; because storage lives in RMS, the resize moves cache assignments, not the 20 TB itself, so it is fast and reversible.
5. If the workload were 20 TB hot (every query touches everything), you would size compute much larger — the point is that most warehouses have a small hot set and a large cold tail, which RA3 is built to exploit (and Spectrum, in §5, pushes even further).

Output.

Decision	Chosen	Driven by
Node type	ra3.4xlarge	working set + concurrency, not total data
Node count	~2–3 for cache, bumped for concurrency	hot set fits cache; 15 queries don't queue
Storage	20 TB in RMS	billed per GB; independent of node count
Scaling lever	elastic resize / pause	minutes; storage never moves

Rule of thumb. Size RA3 compute for the hot working set and the concurrency, and let managed storage hold the rest. If you find yourself adding nodes "to fit the data," you are thinking in DC2 terms — on RA3 that is a storage line item, not a compute decision.

Worked example — pause/resume and data sharing to cut cost

Detailed explanation. Two RA3 economics moves that never existed on DC2: pause an idle cluster to stop compute billing while keeping the data, and share one cluster's data to many consumer clusters without copying. Walk through applying both to a company with a heavy nightly ETL cluster and a daytime BI audience.

• The waste. A single always-on cluster runs ETL 2–5 a.m. and serves BI 8 a.m.–6 p.m.; it is idle 6 p.m.–2 a.m. and over-provisioned for BI at night.
• The fix. A producer cluster owns and loads the data; a right-sized BI consumer reads it via data sharing during the day and pauses overnight; the ETL cluster resumes only for its window.

Question. Design the pause/resume schedule and data-sharing topology, and estimate the compute saved.

Input.

Component	Before	After
Topology	1 always-on cluster	1 producer + 1 BI consumer (shared data)
ETL compute	24×7	resume 2–5 a.m. only
BI compute	24×7	8 a.m.–6 p.m., paused otherwise
Data copies	1	1 (shared, not copied)

Code.

-- Producer cluster: expose schemas to consumers (no copy)
CREATE DATASHARE sales_share;
ALTER  DATASHARE sales_share ADD SCHEMA analytics;
ALTER  DATASHARE sales_share ADD ALL TABLES IN SCHEMA analytics;
GRANT  USAGE ON DATASHARE sales_share TO NAMESPACE '<bi-consumer-namespace>';

-- Consumer (BI) cluster: mount the shared data as a local database
CREATE DATABASE sales_db FROM DATASHARE sales_share
    OF NAMESPACE '<producer-namespace>';
-- Queries now read producer data live; no ETL, no duplication.

# Scheduled pause/resume (e.g. from an EventBridge-triggered Lambda)
# BI consumer: run 08:00, pause 18:00
aws redshift resume-cluster --cluster-identifier bi-consumer      # 08:00
aws redshift pause-cluster  --cluster-identifier bi-consumer      # 18:00

# ETL cluster: resume for the load window only
aws redshift resume-cluster --cluster-identifier etl-producer     # 02:00
aws redshift pause-cluster  --cluster-identifier etl-producer     # 05:00

Step-by-step explanation.

1. Data sharing lets the BI consumer query the producer's analytics schema live, with no copy and no ETL job to keep a duplicate in sync — the consumer reads the same RMS bytes the producer wrote, governed by the datashare grant.
2. Pausing a cluster stops compute billing while the data remains in RMS; resume restores it in minutes. This is only possible because RA3 storage is decoupled — on DC2, pausing would strand the sole copy of the data on the node's local disk.
3. The BI consumer is sized for daytime query concurrency and paused 6 p.m.–8 a.m., cutting ~14 hours of compute billing per weekday. The ETL producer resumes only for its 3-hour load window and pauses the other 21 hours.
4. Because the two workloads are on separate clusters sharing one dataset, ETL writes never contend with BI reads for slots or memory — a cleaner isolation than WLM queues alone can give, at the cost of a second (mostly-paused) cluster.
5. The net compute reduction is roughly the paused hours: the BI consumer bills ~10 hours/weekday instead of 24, and the ETL producer ~3 instead of 24 — while storage is billed once, shared, and unchanged.

Output.

Cluster	Billed compute before	Billed compute after
Single always-on	24 h/day	— (retired)
ETL producer	—	~3 h/day (resume for load)
BI consumer	—	~10 h/weekday (paused nights)
Data copies	1	1 (shared, not duplicated)

Rule of thumb. On RA3, treat compute as something you turn off. Pause idle clusters, resume for their window, and use data sharing so one dataset feeds many right-sized consumer clusters — you pay for storage once and for compute only while it runs.

Senior interview question on RA3 node selection

A senior interviewer might ask: "You inherit a 3-node dc2.8xlarge cluster that keeps hitting 90% disk usage; the team's fix has been to add nodes. Data is 14 TB and growing 1 TB/month, but only the last 60 days (~3 TB) is queried regularly. Recommend a node strategy, justify RA3 vs DC2, and explain how you'd size compute versus storage."

Solution Using an RA3 migration sized for the hot set with managed storage for the tail

-- Step 1 — measure the working set vs total (before choosing anything)
SELECT DATE_TRUNC('day', starttime)::date AS day,
       COUNT(*) AS queries,
       SUM(CASE WHEN btrim(source) = 'remote' THEN 1 ELSE 0 END) AS remote_scans
FROM   stl_scan
WHERE  starttime > GETDATE() - INTERVAL '30 days'
GROUP  BY 1 ORDER BY 1;
-- Confirms ~3 TB hot; the other ~11 TB is rarely scanned.

-- Step 2 — migrate DC2 → RA3 via classic resize / snapshot restore
-- (choose node size for the 3 TB hot set + concurrency, NOT the 14 TB total)
--   candidate: 4x ra3.4xlarge  (compute for the hot set, RMS holds all 14 TB)

-- Step 3 — after migration, verify storage is decoupled
SELECT node, used_mb          -- local SSD cache usage per node (not total data)
FROM   stv_partitions
WHERE  owner = 1;

-- Step 4 — set up pause/resume + (optionally) archive the cold tail to Spectrum (see §5)

Step-by-step trace.

Concern	DC2 (before)	RA3 (after)
Storage model	local SSD = storage; coupled	managed storage on S3; decoupled
"90% disk" fix	add nodes (buy compute for bytes)	grow RMS automatically
Compute sizing driver	total 14 TB	hot ~3 TB + concurrency
Node count	3 (rising to hold data)	~4, stable (sized for compute)
1 TB/month growth	more nodes every quarter	RMS absorbs it; compute unchanged
Idle-hours cost	always on	pause/resume available

After the migration, storage lives in RMS and grows with the 1 TB/month tail without touching compute; the four RA3 nodes are sized for the ~3 TB hot set and daytime concurrency, so query latency holds steady while the disk-full firefighting stops. The cold 11 TB is a storage line item, and §5's Spectrum pattern can push it out of managed storage entirely.

Output:

Metric	Before (DC2)	After (RA3)
Node count trajectory	rising to fit data	flat, sized for compute
Disk-full incidents	monthly	none (managed storage)
Compute paid for cold data	yes	no
Growth handling	resize every quarter	automatic in RMS
Off-hours cost	full	pausable

Why this works — concept by concept:

• RA3 managed storage — storage lives in RMS on S3 and grows automatically, so 1 TB/month of cold data no longer forces a node purchase; the local SSD becomes a cache for hot blocks rather than the store of record.
• Compute sized to the hot set — node size and count are chosen for the ~3 TB working set and the concurrency, not the 14 TB total, which is why the node count goes flat instead of rising with data.
• Decoupling ends disk-full firefighting — on DC2 "90% disk" meant "add compute"; on RA3 it is a storage-cost signal, so the recurring emergency simply disappears.
• Pause/resume + Spectrum on the tail — idle-hour compute can be paused, and the cold 11 TB can later be archived to S3 and read via Spectrum, removing it from managed storage cost altogether.
• Cost — one migration (O(data), one-time) trades a rising DC2 node count for a flat RA3 compute footprint plus per-GB RMS. For a warehouse with a small hot set and a large cold tail, this is strictly cheaper as data grows — compute stops tracking bytes.

Design
Topic — design
Design problems on warehouse compute and storage

Practice →

SQL Topic — optimization Optimization problems on cluster sizing and cost

Practice →

---

3. Distribution style and sort keys

The distribution style decides what moves across the network; the sort key decides what blocks you skip

The mental model in one line: table design in Redshift is two orthogonal choices — the distribution style (KEY, ALL, EVEN, or AUTO) controls how rows are spread across the compute slices and therefore how much data a join has to move, while the sort key (compound or interleaved) controls the physical order of rows within each slice and therefore how many 1 MB blocks a filtered scan can skip via zone maps — get the distribution right and joins run locally; get the sort right and scans read a fraction of the table. These are the two knobs that most often turn a 20-second query into a 200-millisecond one, and they are the two an interviewer will make you reason about from the query, not from the schema.

The distribution styles — what each one does.

• DISTSTYLE KEY. Rows are hashed on the chosen distribution key column so all rows with the same value land on the same slice. Pick the column the biggest join uses; when both tables share the key, the join is collocated (DS_DIST_NONE) and no rows move. The risk is skew — a low-cardinality or lopsided key piles rows onto a few slices.
• DISTSTYLE ALL. A full copy of the table is stored on every node. For small, slowly-changing dimensions this makes every join to it local for free — no redistribution ever. The cost is storage × node-count and slower writes, so it is a dimension trick, not a fact-table one.
• DISTSTYLE EVEN. Rows are spread round-robin across slices regardless of value. No skew, but no collocation either — every join redistributes. A fine default for a staging table with no dominant join.
• DISTSTYLE AUTO. Redshift picks and can switch styles as the table grows (often ALL when small, KEY/EVEN when large). The 2026 default; good, but override it when you know the join pattern better than the heuristic does.

Redistribution tokens in EXPLAIN — reading the join cost.

• DS_DIST_NONE. Best case — both sides already collocated on the join key; nothing moves. This is the target for every big fact→dim join.
• DS_DIST_INNER. The inner table is redistributed on the join key to match the outer. Some data moves; acceptable when one side is small.
• DS_BCAST_INNER. The entire inner table is broadcast to every slice. Cheap only when the inner is tiny; catastrophic when it is large — the classic "wrong DISTKEY" plan.
• DS_DIST_BOTH. Both tables redistributed — the most expensive; usually means neither side is distributed on the join key.

The sort key — compound vs interleaved, and zone maps.

• Zone maps. Redshift stores the min and max value of the sort-key column(s) for every 1 MB block. A filter on the sort key lets the scanner skip any block whose range cannot match — so a date-filtered query on a date-sorted table reads only the relevant blocks.
• Compound sort key. Sorts by the columns in order (SORTKEY(order_date, region)). Excellent when filters lead with the first column; near-useless for a filter on only the second column. The default and the right choice for most time-series fact tables.
• Interleaved sort key. Gives equal weight to each sort column, so filters on any one of them prune well — but it is more expensive to maintain and needs VACUUM REINDEX. Use only when queries filter on several columns independently.
• The pairing. Distribute on the join column, sort on the filter column. They answer different questions and are chosen independently.

Common interview probes on table design.

• "How do you choose a DISTKEY?" — required answer: from the largest/most-frequent join, so both sides collocate.
• "What is distribution skew and how do you detect it?" — uneven rows per slice; svv_table_info.skew_rows or SVV_DISKUSAGE.
• "Compound vs interleaved sort key?" — compound for leading-column filters; interleaved for multi-column independent filters, at higher VACUUM cost.
• "What does DS_BCAST_INNER in EXPLAIN mean?" — the inner table is broadcast to every slice; fine if tiny, a red flag if large.

Worked example — choosing a DISTKEY to collocate a fact→dim join

Detailed explanation. The canonical distribution exercise: a large orders fact joins a customers dimension on customer_id. Distribute both on customer_id and the join becomes DS_DIST_NONE; distribute orders on its primary key id instead and the join broadcasts or redistributes. Walk through both plans.

• The join. orders o JOIN customers c ON c.id = o.customer_id.
• Bad design. orders DISTKEY(id), customers DISTKEY(id) — join key not the dist key on orders.
• Good design. orders DISTKEY(customer_id), customers DISTKEY(id) — both hashed on the join value.

Question. Show the DDL for both designs and the EXPLAIN redistribution token each produces.

Input.

Table	Rows	Join column	Good DISTKEY
orders (fact)	2B	customer_id	customer_id
customers (dim)	40M	id	id

Code.

-- BAD: orders distributed on its own PK, not the join key
CREATE TABLE orders_bad (
    id          BIGINT,
    customer_id BIGINT,
    order_date  DATE,
    total_cents BIGINT
) DISTSTYLE KEY DISTKEY (id)          -- join key is customer_id, not id!
  SORTKEY (order_date);

-- GOOD: orders distributed on the join key
CREATE TABLE orders (
    id          BIGINT,
    customer_id BIGINT,
    order_date  DATE,
    total_cents BIGINT
) DISTSTYLE KEY DISTKEY (customer_id)  -- collocates the customers join
  SORTKEY (order_date);

-- Dimension distributed on its PK (which is the join key on its side)
CREATE TABLE customers (
    id      BIGINT,
    region  TEXT,
    tier    TEXT
) DISTSTYLE KEY DISTKEY (id)
  SORTKEY (id);

EXPLAIN
SELECT c.region, SUM(o.total_cents)
FROM   orders o JOIN customers c ON c.id = o.customer_id
GROUP  BY c.region;

Step-by-step explanation.

1. In the bad design, orders is hashed on id, but the join is on customer_id. Rows that must join are scattered across slices with no relationship to where the matching customers rows live, so the planner must move data to line them up.
2. Because customers (40M rows) is the smaller side, the planner broadcasts it to every slice — DS_BCAST_INNER. At 40M rows that is a large per-slice copy repeated on every query; it works but wastes network and memory.
3. In the good design, both orders and customers are hashed on the join value (customer_id and id respectively). Every orders row and its matching customers row are guaranteed to be on the same slice.
4. With both sides collocated, the planner reports DS_DIST_NONE: the join runs entirely within each slice, in parallel, with zero network movement — the single biggest win available to a fact→dim join.
5. The sort key (order_date) is chosen independently and serves the filter, not the join; it is identical in both designs. Distribution and sort are orthogonal — this example isolates the distribution effect.

Output.

Design	orders DISTKEY	EXPLAIN token	Data moved per query
Bad	id	DS_BCAST_INNER	40M-row broadcast
Good	customer_id	DS_DIST_NONE	0

Rule of thumb. Choose the distribution key from the join, never from the primary key by reflex. If the fact table's hottest join is on customer_id, distribute on customer_id — then the dimension distributed on its matching PK collocates for free and EXPLAIN reads DS_DIST_NONE.

Worked example — distribution skew from a low-cardinality key

Detailed explanation. A DISTKEY collocates joins only if it also spreads rows evenly. Distribute a billion-row table on a column with a handful of dominant values (like country where 70% of rows are one country) and one slice holds most of the data while the others idle — the join is collocated but the cluster runs at the speed of the one overloaded slice. Walk through detecting and fixing skew.

• The mistake. DISTKEY(country) on a table where one country dominates.
• The symptom. svv_table_info.skew_rows far above 1.0; one slice's queries run long while others finish instantly.
• The fix. Distribute on a high-cardinality column that still serves the join (e.g. customer_id), or EVEN if no join dominates.

Question. Detect the skew, quantify it, and choose a better distribution.

Input.

Column	Distinct values	Row share of top value	Skew risk
country	~50	70%	severe
customer_id	40M	tiny	none
id (PK)	2B	unique	none (but no join value)

Code.

-- 1. Detect skew: rows per slice for the table
SELECT slice, COUNT(*) AS rows_on_slice
FROM   orders_by_country
GROUP  BY slice
ORDER  BY rows_on_slice DESC;      -- one slice dwarfs the rest = skew

-- 2. Quantify from the catalog
SELECT "table", diststyle, skew_rows      -- skew_rows >> 1.0 confirms it
FROM   svv_table_info
WHERE  "table" = 'orders_by_country';

-- 3. Fix: redistribute on a high-cardinality join column
CREATE TABLE orders AS
SELECT * FROM orders_by_country;
ALTER TABLE orders ALTER DISTSTYLE KEY DISTKEY (customer_id);

-- (If no single join dominates, EVEN spreads rows perfectly at the cost
--  of always redistributing joins:)
-- ALTER TABLE orders ALTER DISTSTYLE EVEN;

Step-by-step explanation.

1. Grouping by the internal slice pseudo-column shows the physical row distribution. When one slice holds 70% of the rows (because 70% share one country value), that slice becomes the bottleneck for every scan and join step.
2. svv_table_info.skew_rows is the catalog shortcut: it is the ratio of rows on the most-loaded slice to the least-loaded. A value near 1.0 is even; a value of 6, 8, or higher is the skew signature.
3. A skewed DISTKEY is doubly bad: the overloaded slice serialises the query (parallelism is lost) and its memory pressure can force spills to disk. The collocation benefit is real but swamped by the imbalance.
4. Redistributing on customer_id — high cardinality and still the join key — keeps joins collocated and spreads rows evenly, because millions of distinct customer values hash across all slices uniformly.
5. When no single column both serves the dominant join and has high cardinality, EVEN is the honest choice: it sacrifices collocation (joins redistribute) to guarantee no skew. Never keep a DISTKEY that produces skew_rows of 6+ just to save a redistribution.

Output.

Distribution	skew_rows	Join	Verdict
DISTKEY(country)	~7.0	collocated but on one hot slice	bad
DISTKEY(customer_id)	~1.1	collocated + even	best
DISTSTYLE EVEN	~1.0	redistributes	acceptable fallback

Rule of thumb. A good distribution key must satisfy two tests at once: it is the join column and it is high-cardinality enough to spread rows evenly (skew_rows near 1.0). If a column passes the join test but fails the skew test, distribute on a finer join column or fall back to EVEN — never ship a skew_rows of 6+.

Worked example — compound vs interleaved sort key

Detailed explanation. The sort key choice hinges on how queries filter. A compound key sorts by columns in order and prunes brilliantly when filters lead with the first column; an interleaved key weights all sort columns equally so filters on any one of them prune, at the cost of a heavier VACUUM REINDEX. Walk through choosing for two different query patterns.

• Pattern A. Almost every query filters WHERE order_date BETWEEN ... (and sometimes also region). → compound SORTKEY(order_date, region).
• Pattern B. Queries filter on order_date or region or product_id independently, no dominant leader. → interleaved SORTKEY(order_date, region, product_id).

Question. Show both sort-key definitions and the block-pruning behaviour each gives for a filter on the second column alone.

Input.

Query pattern	Leading filter	Best sort key
A: time-series, date-led	order_date	compound(order_date, region)
B: multi-facet, independent	any of 3	interleaved(order_date, region, product_id)

Code.

-- Compound: great for leading-column (order_date) filters
CREATE TABLE orders_compound (
    id BIGINT, customer_id BIGINT, order_date DATE, region TEXT, product_id BIGINT, total_cents BIGINT
) DISTKEY (customer_id)
  COMPOUND SORTKEY (order_date, region);

-- Interleaved: equal weight, prunes on any of the three columns
CREATE TABLE orders_interleaved (
    id BIGINT, customer_id BIGINT, order_date DATE, region TEXT, product_id BIGINT, total_cents BIGINT
) DISTKEY (customer_id)
  INTERLEAVED SORTKEY (order_date, region, product_id);

-- A filter on the SECOND column only:
EXPLAIN SELECT * FROM orders_compound    WHERE region = 'EMEA';  -- poor pruning
EXPLAIN SELECT * FROM orders_interleaved WHERE region = 'EMEA';  -- good pruning

-- Interleaved needs periodic reindexing as data grows
VACUUM REINDEX orders_interleaved;

Step-by-step explanation.

1. The compound key sorts rows first by order_date, then by region within each date. Zone maps prune superbly for order_date filters, but a filter on region alone barely prunes — region values are scattered across every date block, so few blocks can be skipped.
2. The interleaved key gives order_date, region, and product_id equal weight in the physical ordering (via a Z-order-style interleave). A filter on any single one of them prunes well, which is exactly what Pattern B's independent filters need.
3. Interleaved's cost is maintenance: as rows are added, the interleave degrades and must be rebuilt with VACUUM REINDEX, which is heavier than an ordinary VACUUM. On a high-ingest table that maintenance can dominate.
4. For the overwhelmingly common time-series case (Pattern A — everything filters by date), compound is the right answer: cheaper to maintain and near-perfect pruning on the leading column. Reserve interleaved for genuine multi-facet access.
5. The distribution key (customer_id) is identical in both — again, sort and distribution are chosen independently. The sort-key decision is driven purely by the filter columns and their access pattern.

Output.

Sort key	Filter on order_date	Filter on region alone	VACUUM cost
compound(order_date, region)	excellent pruning	poor pruning	normal VACUUM
interleaved(order_date, region, product_id)	good pruning	good pruning	VACUUM REINDEX

Rule of thumb. Default to a compound sort key led by the column you filter on most (usually a date). Reach for an interleaved sort key only when queries genuinely filter on several columns independently — and budget for VACUUM REINDEX, because an un-reindexed interleaved table loses the pruning you paid for.

Senior interview question on distribution and sort keys

A senior interviewer might ask: "You have a 5-billion-row events fact table joined to a small dim_users table on user_id, and 90% of queries filter WHERE event_date >= .... The current design uses DISTSTYLE EVEN and no sort key, and joins are slow. Redesign the physical layout, justify the distribution and sort choices, and show how you'd verify the improvement in EXPLAIN and the system tables."

Solution Using a collocating DISTKEY, DISTSTYLE ALL on the dimension, and a date SORTKEY

-- 1. Distribute the fact on the join key; sort on the filter column
CREATE TABLE events_new
    DISTSTYLE KEY
    DISTKEY  (user_id)
    COMPOUND SORTKEY (event_date)
AS SELECT * FROM events;

-- 2. Make the small dimension replicated so its join is always local
CREATE TABLE dim_users_new
    DISTSTYLE ALL              -- full copy on every node; join never redistributes
    SORTKEY (user_id)
AS SELECT * FROM dim_users;

-- 3. Swap in
ALTER TABLE events     RENAME TO events_old;     ALTER TABLE events_new     RENAME TO events;
ALTER TABLE dim_users  RENAME TO dim_users_old;  ALTER TABLE dim_users_new  RENAME TO dim_users;

-- 4. Refresh stats + sort, then verify
ANALYZE events; VACUUM events;

EXPLAIN
SELECT u.country, COUNT(*) 
FROM   events e JOIN dim_users u ON u.user_id = e.user_id
WHERE  e.event_date >= DATE '2026-08-01'
GROUP  BY u.country;

SELECT "table", diststyle, sortkey1, skew_rows, unsorted
FROM   svv_table_info WHERE "table" IN ('events', 'dim_users');

Step-by-step trace.

Input	Before	After
events distribution	EVEN	DISTKEY(user_id)
dim_users distribution	EVEN	DISTSTYLE ALL (replicated)
Join plan	DS_DIST_BOTH	DS_DIST_NONE
events sort	none	SORTKEY(event_date)
Date-filter scan	full table	zone-map skip to date range
skew_rows	~1.0 (even, but no collocation)	~1.1 (even + collocated)

Walk the plan: events distributed on user_id collocates the fact side; dim_users as DISTSTYLE ALL puts a full copy on every node, so its side of the join is always local — together the join is DS_DIST_NONE. The event_date sort key means the 90%-of-queries date filter skips every block outside the requested range. ANALYZE refreshes the stats that let the planner pick this plan; VACUUM sorts the loaded rows so the zone maps are tight.

Output:

Metric	Before	After
Join redistribution	DS_DIST_BOTH (both moved)	DS_DIST_NONE
Blocks scanned (date filter)	all	one date range
Dimension join	redistributed each query	always local (ALL)
Stats freshness	stale	fresh (ANALYZE)
Verified via	EXPLAIN + svv_table_info	same

Why this works — concept by concept:

• DISTKEY(user_id) on the fact — hashing the 5B-row events on the join key means matching rows can be found on the same slice, eliminating the fact side's redistribution.
• DISTSTYLE ALL on the small dimension — a full replica of dim_users on every node makes its side of the join local everywhere, so no matter how events is scanned the join never moves the dimension; this is the classic small-dim trick that yields DS_DIST_NONE.
• COMPOUND SORTKEY(event_date) — because 90% of queries lead with a date filter, sorting by event_date lets zone maps skip all out-of-range blocks, turning a full scan into a date-range scan.
• ANALYZE + VACUUM — ANALYZE gives the planner current statistics to choose the collocated plan; VACUUM physically sorts the freshly-loaded rows so the zone-map pruning actually engages.
• Cost — one table rewrite plus DISTSTYLE ALL storage (dimension copied per node — cheap because it is small). The eliminated cost is redistributing both tables on every join and scanning the whole fact per query — O(both tables moved) becomes O(collocated, date-pruned). The ALL replica is the only added cost and it is bounded by the dimension's small size.

SQL
Topic — optimization
Optimization problems on distribution and sort keys

Practice →

SQL Topic — sql SQL join and aggregation problems

Practice →

---

4. WLM and concurrency scaling

WLM decides who waits and with how much memory; concurrency scaling decides whether read bursts queue or spill to a transient cluster

The mental model in one line: Workload Management is the traffic controller that assigns each query to a queue with a fixed share of cluster memory and a bounded number of concurrency slots — Automatic WLM sizes slots and memory dynamically by priority while manual WLM lets you carve them by hand — and concurrency scaling is the release valve that, when eligible read queries would otherwise queue, spins up a transient cluster to run them in parallel and bills the extra capacity in credits. Almost every "the warehouse is fast alone but slow under load" complaint is a WLM-and-concurrency problem, not a query-cost problem, and the two are tuned together.

Manual WLM vs Automatic WLM.

• Manual WLM. You define queues, and for each: a memory percentage of the cluster and a fixed number of concurrency slots. A query gets memory% / slots of memory. More slots = more concurrency but less memory per query = more spilling to disk. You own the trade-off entirely — powerful and easy to misconfigure.
• Automatic WLM. You define queues by priority (and optionally rules); Redshift decides slots and memory per query dynamically based on the query's needs and the cluster's load. The 2026 default and the right starting point — it avoids the classic "too many slots, everything spills" mistake.
• Query priority. In Automatic WLM each queue has a priority (HIGHEST … LOWEST); under contention, higher-priority queries get memory and slots first and can preempt lower-priority ones. This is how you keep the finance close ahead of ad-hoc exploration.
• Short Query Acceleration (SQA). A dedicated fast lane for short-running queries so a five-second dashboard tile is not stuck behind a five-minute report. On by default with Automatic WLM.

Query Monitoring Rules (QMR) — guardrails against runaways.

• What they are. Per-queue rules on metrics like query_execution_time, scan_row_count, return_row_count, nested_loop_join_row_count, or memory used.
• The actions. log (record it), hop (move the query to a different matching queue), or abort (kill it). A rule like "abort if nested_loop_join_row_count > 1e9" stops a cartesian-join accident from monopolising the cluster.
• Why they matter. One runaway query — a missing join predicate, a SELECT * over a huge unsorted table — can starve every other query. QMR is the automated on-call that kills it before a human notices.

Concurrency scaling — the burst release valve.

• What it does. When eligible queries would queue, Redshift routes them to a transient concurrency-scaling cluster that runs them in parallel, then tears it down. To users it looks like the main cluster simply got faster under load.
• Eligibility. Primarily read queries (and, in recent versions, some writes). It scales concurrency, not single-query speed — it will not make one slow scan faster, only let more queries run at once.
• Cost. Billed in concurrency-scaling credits; clusters accrue roughly one free hour of scaling per day of use, and you pay per-second beyond that. Cheap insurance against Monday-morning queueing — but not a fix for a badly-distributed table.
• The boundary. Turn it on for spiky BI read workloads; do not expect it to rescue a query that is slow because it scans an unsorted table or broadcasts a huge inner — those are §3 problems.

Common interview probes on WLM.

• "Fast alone, slow under load — why?" — required answer: queueing; fix WLM queues + concurrency scaling.
• "Manual vs Automatic WLM?" — Automatic by default (priority-driven); manual only when you must hand-carve memory/slots.
• "How do you stop a runaway query?" — a QMR that aborts on execution time / row-count / nested-loop thresholds.
• "Does concurrency scaling make queries faster?" — no; it adds concurrency for read bursts, not single-query speed.

Worked example — an Automatic WLM config with priorities and QMR

Detailed explanation. The canonical WLM setup: separate ETL from BI into two Automatic-WLM queues with different priorities, add a QMR to abort runaway scans on the BI queue, and turn on concurrency scaling so BI read bursts do not queue behind each other. Walk through the config JSON.

• Queue 1 — ETL. Priority HIGH; large loads and transforms must finish the nightly window.
• Queue 2 — BI. Priority NORMAL; many short dashboard queries; concurrency scaling ON; QMR to abort runaways.
• Routing. By user group / query group so each workload lands in its queue.

Question. Write the Automatic WLM configuration with two queues, a QMR abort rule, and concurrency scaling on the BI queue.

Input.

Queue	Priority	Concurrency scaling	QMR
ETL	HIGH	off	none
BI	NORMAL	on	abort if runs > 120 s

Code.

// Automatic WLM configuration (wlm_json_configuration parameter)
[
  {
    "query_group": ["etl"],
    "user_group": ["etl_role"],
    "priority": "high",
    "concurrency_scaling": "off",
    "queue_type": "auto"
  },
  {
    "query_group": ["bi"],
    "user_group": ["bi_role"],
    "priority": "normal",
    "concurrency_scaling": "auto",
    "queue_type": "auto",
    "rules": [
      {
        "rule_name": "abort_long_bi",
        "predicate": [
          { "metric_name": "query_execution_time", "operator": ">", "value": 120 }
        ],
        "action": "abort"
      },
      {
        "rule_name": "hop_big_scan",
        "predicate": [
          { "metric_name": "scan_row_count", "operator": ">", "value": 5000000000 }
        ],
        "action": "log"
      }
    ]
  },
  {
    "short_query_queue": true
  }
]

-- Route a session to a queue by setting its query group
SET query_group TO 'bi';
-- ... run dashboard query ...
RESET query_group;

-- Watch queueing + concurrency-scaling usage after rollout
SELECT service_class, num_queued_queries, num_executed_queries,
       avg_queue_time_us / 1e6 AS avg_queue_s
FROM   stl_wlm_query
WHERE  service_class > 5;

Step-by-step explanation.

1. Each queue is "queue_type": "auto", so Redshift sizes slots and memory per query dynamically — you specify priority, not slot counts. This sidesteps the manual-WLM trap where too many fixed slots starve every query of memory.
2. The ETL queue is HIGH priority so nightly loads win memory and slots under contention; the BI queue is NORMAL so it yields to ETL when they overlap but otherwise runs freely. Priority is the lever that keeps the close ahead of ad-hoc work.
3. The BI queue's first QMR aborts any query running longer than 120 seconds — a hard guardrail against a runaway dashboard query monopolising the queue. The second rule only logs very large scans so you can find them without killing them.
4. "concurrency_scaling": "auto" on the BI queue means when dashboard queries would queue, Redshift spins up a transient cluster to run the overflow in parallel — the "slow at 9 a.m." symptom disappears without adding permanent nodes.
5. SET query_group routes a session into a queue; stl_wlm_query after rollout confirms the effect — num_queued_queries on the BI service class should drop toward zero as concurrency scaling absorbs bursts, while ETL keeps its priority.

Output.

Metric (BI queue)	Before (single default queue)	After (2 queues + CS + QMR)
Avg queue time at 9 a.m.	~40 s	~0 (bursts scaled out)
ETL vs BI contention	shared, unpredictable	isolated by priority
Runaway query impact	starves everyone	aborted at 120 s
Concurrency during bursts	fixed	elastic (transient cluster)

Rule of thumb. Start with Automatic WLM: two or three priority queues (ETL high, BI normal, ad-hoc low), concurrency scaling on the read-heavy BI queue, and a QMR that aborts on execution time. Only drop to manual WLM when you have a proven reason to hand-carve memory and slots.

Worked example — the memory-slot trade-off and spill to disk

Detailed explanation. In manual WLM, memory per query is queue_memory% / slots. Increase slots to run more queries at once and each query gets less memory — past a point, queries that need more memory than their slot grants spill to disk, and a spilling query is often 5–50× slower. Walk through the trade-off and how to detect spills.

• The temptation. "Set 50 slots so nothing ever queues."
• The consequence. Each slot gets 2% of queue memory; any hash/sort/aggregate bigger than that spills to disk.
• The detection. svl_query_summary.is_diskbased = 't'; stl_wlm_query queue vs execution time.
• The fix. Fewer slots (more memory each) + concurrency scaling for the burst, or just use Automatic WLM.

Question. Diagnose a queue where raising slots made queries slower, and choose the right slot/memory balance.

Input.

Config	Slots	Memory per query	Spill?
A	5	20% of queue	rare
B	50	2% of queue	frequent

Code.

-- 1. Find queries that spilled to disk (memory-starved)
SELECT q.query, q.label,
       s.step, s.is_diskbased, s.rows, s.workmem / 1024 / 1024 AS workmem_mb
FROM   svl_query_summary s
JOIN   stl_query        q ON q.query = s.query
WHERE  s.is_diskbased = 't'
  AND  q.starttime > GETDATE() - INTERVAL '1 day'
ORDER  BY s.rows DESC
LIMIT  20;

-- 2. Correlate with WLM: were they in a high-slot, low-memory queue?
SELECT service_class, slot_count, avg_queue_time_us/1e6 AS q_s,
       avg_exec_time_us/1e6 AS exec_s
FROM   stl_wlm_query
WHERE  service_class > 5;

-- 3. Manual-WLM fix: fewer slots per queue (more memory each)
--    + let concurrency scaling absorb the burst instead of adding slots
--    (or migrate the queue to Automatic WLM)

Step-by-step explanation.

1. svl_query_summary.is_diskbased = 't' flags steps that spilled to disk because the query's memory grant was too small for its hash table, sort, or aggregation. Disk-based steps are the direct cost of over-slotting.
2. Config B (50 slots) gives each query 2% of queue memory. A join building a large hash table needs more than that, so it spills — and a spilling hash join can be an order of magnitude slower than an in-memory one. The extra concurrency bought a latency regression.
3. Config A (5 slots) gives each query 20% of queue memory — enough to keep typical joins and aggregates in memory. Fewer queries run concurrently, but each finishes fast; total throughput is often higher than the over-slotted config.
4. The right answer to "we need more concurrency" is rarely "more slots"; it is "fewer slots for memory headroom, plus concurrency scaling to absorb the burst on a transient cluster" — you get concurrency without starving memory.
5. Automatic WLM removes the manual trade-off entirely: it grants memory per query based on need and load, so you stop hand-tuning the slot/memory ratio. Manual WLM is worth it only when a specific workload demands hand-carved isolation.

Output.

Config	Concurrency	Memory/query	Spills	Effective speed
A (5 slots)	lower	20%	rare	fast per query
B (50 slots)	higher	2%	frequent	slow (disk spill)
Auto WLM + CS	elastic	per-need	minimal	fast + no queue

Rule of thumb. More slots is not more speed. Each extra slot shrinks per-query memory and pushes big joins and sorts to spill to disk. Prefer fewer slots with generous memory, and reach for concurrency scaling — not slot inflation — when you need to absorb a burst.

Senior interview question on WLM and concurrency scaling

A senior interviewer might ask: "Your cluster serves nightly ETL and daytime BI on one default WLM queue. ETL sometimes overruns into the morning and blocks dashboards; on Monday mornings BI queries queue for 30+ seconds; and occasionally one analyst's cartesian-join mistake freezes everyone. Design a WLM strategy — queues, priorities, QMR, and concurrency scaling — and explain how you'd verify each part."

Solution Using Automatic WLM priority queues, QMR abort rules, and concurrency scaling

// Automatic WLM: isolate ETL, protect BI, cap ad-hoc, guard runaways
[
  {
    "user_group": ["etl_role"], "query_group": ["etl"],
    "priority": "highest", "concurrency_scaling": "off", "queue_type": "auto"
  },
  {
    "user_group": ["bi_role"], "query_group": ["bi"],
    "priority": "high", "concurrency_scaling": "auto", "queue_type": "auto",
    "rules": [
      { "rule_name": "abort_runaway_bi",
        "predicate": [{ "metric_name": "query_execution_time", "operator": ">", "value": 300 }],
        "action": "abort" }
    ]
  },
  {
    "user_group": ["analyst_role"], "query_group": ["adhoc"],
    "priority": "low", "concurrency_scaling": "auto", "queue_type": "auto",
    "rules": [
      { "rule_name": "abort_cartesian",
        "predicate": [{ "metric_name": "nested_loop_join_row_count", "operator": ">", "value": 1000000000 }],
        "action": "abort" }
    ]
  },
  { "short_query_queue": true }
]

-- Verification after rollout
-- (a) ETL no longer blocks BI: check priority + queue isolation
SELECT service_class, num_queued_queries, avg_queue_time_us/1e6 AS q_s
FROM   stl_wlm_query WHERE service_class > 5;

-- (b) Concurrency scaling absorbing Monday bursts
SELECT DATE_TRUNC('hour', start_time) AS hr, SUM(queries) AS scaled_queries
FROM   svcs_concurrency_scaling_usage    -- illustrative usage view
GROUP  BY 1 ORDER BY 1;

-- (c) Runaway cartesian join aborted, not cluster-wide freeze
SELECT query, rule_name, action FROM stl_wlm_rule_action
WHERE  action = 'abort' ORDER BY recordtime DESC LIMIT 20;

Step-by-step trace.

Concern	Config lever	Result
ETL blocks BI	ETL highest, BI high, separate queues	ETL wins memory but BI is isolated
Monday BI queueing	concurrency_scaling: auto on BI	bursts run on transient cluster
Analyst cartesian freeze	QMR abort on nested_loop_join_row_count	offending query killed, others fine
Ad-hoc greed	analyst queue low priority	exploration yields to ETL/BI
Tiny dashboard tiles	short_query_queue: true (SQA)	short queries jump the line

Walk it: ETL runs at highest priority in its own queue, so even if it overruns it competes on priority rather than sharing BI's memory pool; BI at high with concurrency scaling absorbs the Monday burst on a transient cluster instead of queueing; the analyst queue at low priority with a nested_loop_join_row_count abort rule kills a cartesian-join mistake before it monopolises the cluster; and SQA keeps sub-second dashboard tiles moving.

Output:

Symptom	Before	After
ETL overrun blocks BI	dashboards stall	BI isolated by queue + priority
Monday-morning BI queue	30+ s	~0 (concurrency scaling)
Cartesian-join freeze	cluster-wide	single query aborted
Ad-hoc vs production	equal footing	ad-hoc yields (low priority)
Sub-second tiles	queued behind reports	SQA fast lane

Why this works — concept by concept:

• Priority queues (Automatic WLM) — assigning ETL highest, BI high, ad-hoc low lets Redshift allocate memory and slots by importance under contention, so production work wins without hand-carving slot counts.
• Queue isolation — separate queues per user group mean ETL's memory pressure and long transactions cannot starve BI; they compete on priority, not on a shared slot pool.
• Concurrency scaling on reads — the BI and ad-hoc read bursts spill to a transient cluster instead of queueing, so Monday-morning latency stays flat without permanently over-provisioning nodes.
• QMR abort rules — a rule on query_execution_time and one on nested_loop_join_row_count turn "an analyst froze the cluster" into "one query was aborted," automatically and without paging anyone.
• Cost — WLM changes are config-only (no rewrite); concurrency scaling bills in credits (roughly one free hour/day, per-second beyond). The eliminated cost is cluster-wide stalls and the temptation to add permanent nodes for a burst that lasts an hour a day — you pay for burst capacity only when a burst happens.

SQL
Topic — optimization
Optimization problems on workload and concurrency

Practice →

Design Topic — design Design problems on workload management

Practice →

---

5. Redshift Spectrum and table maintenance

Redshift Spectrum queries S3 in place so cold data leaves managed storage; VACUUM and ANALYZE keep the local tables that remain fast

The mental model in one line: Redshift Spectrum lets a query read external tables whose data lives as files in Amazon S3 — registered through an external schema over the AWS Glue Data Catalog — so you keep hot data in managed storage for speed and push cold history to cheap columnar files that you query in place, while VACUUM (re-sort and reclaim) and ANALYZE (refresh planner statistics) keep the local tables healthy after the delete/update churn that would otherwise leave them bloated and unsorted. Spectrum's cost model is bytes scanned in S3, which rewards partitioning and Parquet and punishes SELECT * over unpartitioned data — and that cost model is exactly what a senior interviewer will probe.

Redshift Spectrum — the external-table anatomy.

• External schema. CREATE EXTERNAL SCHEMA points Redshift at a Glue Data Catalog database (or a Hive metastore) and an IAM role; tables in that schema are metadata pointers to S3 prefixes, not stored in the cluster.
• File formats. Columnar formats — Parquet, ORC — are dramatically cheaper because Spectrum reads only the columns and row groups a query needs. Row formats (CSV, JSON) force full-file scans and cost far more bytes.
• Partitioning. Registering partitions (e.g. by year/month/day S3 prefixes) lets Spectrum prune whole partitions from the scan when a query filters on the partition column — the single biggest Spectrum cost lever.
• Compute. Spectrum runs the scan/filter/aggregate on a fleet of AWS-managed Spectrum nodes, not your cluster's slices, and returns results to the cluster to finish the query. Your cluster does the join and final aggregation; Spectrum does the heavy S3 read.

The lake-house pattern — hot in RMS, cold in S3.

• The split. Recent data (frequently queried) stays in local Redshift tables; old data (rarely queried) is unloaded to partitioned Parquet in S3 and exposed as a Spectrum external table.
• The UNION view. A view UNION ALLs the hot local table and the cold external table, so consumers query one object and Spectrum is transparent — recent filters hit local storage, historical filters reach into S3.
• The economics. You stop paying managed-storage prices for years of cold data, and you only pay Spectrum's per-byte cost when someone actually queries the history. For the classic "small hot set, huge cold tail" warehouse, this is a large cost win.

VACUUM and ANALYZE — table hygiene.

• Why tables degrade. DELETE marks rows dead but does not reclaim their space; UPDATE is a delete + insert, leaving the old row dead and the new row unsorted. Over time a churned table is bloated with dead rows and its sort order decays, so zone-map pruning stops working.
• VACUUM FULL. Re-sorts the table and reclaims space from deleted rows. The default and the one you want after heavy churn.
• VACUUM SORT ONLY / DELETE ONLY. Re-sort without reclaiming, or reclaim without re-sorting — cheaper, targeted variants. VACUUM REINDEX rebuilds an interleaved sort key.
• ANALYZE. Refreshes the statistics (row counts, value distributions) the cost-based planner uses to pick join order and distribution. Stale stats after a big load make the planner choose bad plans even when the physical layout is fine.
• 2026 automation. Auto-vacuum (sort + delete) and auto-analyze run in the background during low load and cover most tables — but high-churn, latency-critical tables still benefit from a scheduled VACUUM/ANALYZE in the ETL window, and you should watch svv_table_info.unsorted and stats_off.

Common interview probes on Spectrum and maintenance.

• "What is Spectrum's cost model?" — required answer: bytes scanned in S3; partition and use Parquet to cut it.
• "When query-in-place vs load into Redshift?" — cold/rarely-queried and huge → Spectrum; hot/frequent → local managed storage.
• "Do you still need VACUUM in 2026?" — mostly automatic, but schedule it on high-churn tables and watch the unsorted %.
• "What does ANALYZE do that VACUUM doesn't?" — refreshes planner statistics; VACUUM fixes physical layout, ANALYZE fixes the plan.

Worked example — partitioned external table with partition pruning

Detailed explanation. The canonical Spectrum setup: an external schema over Glue, an external table pointing at partitioned Parquet in S3, and a query that filters on the partition column so Spectrum prunes to a single month. Walk through the DDL and the cost difference.

• Layout. s3://lake/orders_archive/year=YYYY/month=MM/ with Parquet files.
• External schema. Over a Glue database, with an IAM role for S3 read.
• Query. Filters WHERE year = 2026 AND month = 8 → scans one month, not ten years.

Question. Create the external schema and partitioned external table, register partitions, and show the bytes-scanned difference for a one-month query.

Input.

Component	Value
S3 prefix	s3://lake/orders_archive/year=/month=/
Format	Parquet (columnar)
Partition columns	year, month
Query filter	year = 2026 AND month = 8

Code.

-- 1. External schema over the Glue Data Catalog
CREATE EXTERNAL SCHEMA spectrum_lake
FROM DATA CATALOG
DATABASE 'lake'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftSpectrumRole'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

-- 2. Partitioned external table (data stays in S3)
CREATE EXTERNAL TABLE spectrum_lake.orders_archive (
    id           BIGINT,
    customer_id  BIGINT,
    order_date   DATE,
    total_cents  BIGINT
)
PARTITIONED BY (year INT, month INT)
STORED AS PARQUET
LOCATION 's3://lake/orders_archive/';

-- 3. Register partitions (or let a crawler / partition projection do it)
ALTER TABLE spectrum_lake.orders_archive
    ADD PARTITION (year=2026, month=8)
    LOCATION 's3://lake/orders_archive/year=2026/month=8/';

-- 4. Query that PRUNES to one month (scans one partition, not the whole archive)
SELECT customer_id, SUM(total_cents) AS spend
FROM   spectrum_lake.orders_archive
WHERE  year = 2026 AND month = 8       -- partition filter → prune
GROUP  BY customer_id;

-- Inspect bytes scanned (Spectrum cost) for the query
SELECT query, SUM(s3_scanned_bytes) / 1024 / 1024 / 1024 AS gb_scanned
FROM   svl_s3query_summary
WHERE  query = pg_last_query_id()
GROUP  BY query;

Step-by-step explanation.

1. CREATE EXTERNAL SCHEMA ... FROM DATA CATALOG links Redshift to a Glue database and an IAM role that grants S3 read. Tables created in this schema are metadata only — the rows live in S3 and are never copied into the cluster.
2. PARTITIONED BY (year, month) plus STORED AS PARQUET is the cost-critical pair: partitioning enables pruning, and Parquet means Spectrum reads only the needed columns and row groups rather than whole files.
3. Registering the (year=2026, month=8) partition maps that S3 prefix into the table. In production a Glue crawler or partition projection registers partitions automatically as new prefixes land.
4. The query's WHERE year = 2026 AND month = 8 filter matches partition columns, so Spectrum prunes every other partition from the scan — it opens only the August 2026 Parquet files, not the ten-year archive.
5. svl_s3query_summary.s3_scanned_bytes reports exactly how many bytes S3 charged for — the Spectrum cost. A pruned, Parquet, column-projected query scans a tiny fraction of what a SELECT * over unpartitioned JSON would, which is the whole point.

Output.

Query shape	Bytes scanned (relative)	Cost
SELECT *, unpartitioned JSON	100% (all files)	highest
Column-projected, Parquet, no prune	~15%	medium
Column-projected, Parquet, partition-pruned	~1% (one month)	lowest

Rule of thumb. Spectrum bills bytes scanned, so make queries scan less: store data as partitioned Parquet, filter on the partition columns so whole partitions prune, and select only the columns you need. An unpartitioned SELECT * over JSON is the most expensive query you can write against S3.

Worked example — the lake-house UNION view and a VACUUM/ANALYZE job

Detailed explanation. Two operational pieces complete the picture: a view that unions hot local data with cold Spectrum data so consumers see one table, and a maintenance job that keeps the hot local table healthy after churn. Walk through both.

• The view. orders_all = recent local orders UNION ALL cold spectrum_lake.orders_archive.
• The maintenance. After nightly loads and deletes, VACUUM re-sorts and reclaims, then ANALYZE refreshes stats.

Question. Build the lake-house UNION view and a maintenance step that vacuums and analyzes the hot table only when it needs it.

Input.

Component	Value
Hot table	local orders (last 90 days)
Cold table	spectrum_lake.orders_archive (older)
Consumer object	view orders_all
Maintenance trigger	unsorted > 10% or stats_off > 10%

Code.

-- 1. Lake-house view: hot local + cold S3, transparent to consumers
CREATE OR REPLACE VIEW orders_all AS
SELECT id, customer_id, order_date, total_cents
FROM   orders                               -- hot: last 90 days, managed storage
UNION ALL
SELECT id, customer_id, order_date, total_cents
FROM   spectrum_lake.orders_archive;        -- cold: S3 via Spectrum

-- 2. Conditional maintenance — only vacuum/analyze tables that need it
SELECT "table", unsorted, stats_off
FROM   svv_table_info
WHERE  "table" = 'orders';
-- If unsorted > 10%  → VACUUM;  if stats_off > 10% → ANALYZE.

-- 3. The maintenance statements (run in the ETL window)
VACUUM FULL orders;         -- re-sort + reclaim dead rows from deletes/updates
ANALYZE orders;             -- refresh planner statistics after the load

# Airflow-style maintenance task: vacuum/analyze only when thresholds trip
import psycopg2

def maintain_orders(conn):
    with conn.cursor() as cur:
        cur.execute("""
            SELECT unsorted, stats_off
            FROM   svv_table_info
            WHERE  "table" = 'orders'
        """)
        unsorted, stats_off = cur.fetchone()

    # VACUUM cannot run inside a transaction block → autocommit
    conn.autocommit = True
    with conn.cursor() as cur:
        if unsorted and unsorted > 10:
            cur.execute("VACUUM FULL orders;")
        if stats_off and stats_off > 10:
            cur.execute("ANALYZE orders;")
    conn.autocommit = False

Step-by-step explanation.

1. The orders_all view UNION ALLs the hot local table and the cold Spectrum external table, so every consumer queries one object. A filter on recent dates reads only local storage; a historical filter reaches into S3 — and Spectrum prunes partitions on that side.
2. svv_table_info.unsorted and stats_off tell you whether the hot table actually needs maintenance: unsorted is the percentage of rows out of sort order (a VACUUM candidate), stats_off is how stale the planner stats are (an ANALYZE candidate).
3. VACUUM FULL re-sorts the table so zone-map pruning works again and reclaims the space left by deleted/updated rows; after heavy nightly churn this is what stops the hot table from bloating and slowing down.
4. ANALYZE refreshes the statistics the planner uses; a big nightly load can make row-count and distribution stats stale enough that the planner picks a bad join order even though the physical layout is fine. VACUUM fixes the layout; ANALYZE fixes the plan.
5. Running VACUUM/ANALYZE conditionally — only when unsorted or stats_off cross a threshold — avoids wasting the ETL window on tables that auto-vacuum already handled. Note VACUUM cannot run inside a transaction block, hence the autocommit toggle.

Output.

State after nightly churn	Metric	Action taken
22% of rows unsorted	unsorted = 22	VACUUM FULL orders
stats stale after load	stats_off = 35	ANALYZE orders
< 10% unsorted, fresh stats	below threshold	skip (auto-vacuum handled it)
consumers	query orders_all	hot + cold transparently

Rule of thumb. Expose a lake-house UNION view so Spectrum is invisible to consumers, and drive VACUUM/ANALYZE off svv_table_info thresholds rather than blindly nightly. Let auto-vacuum handle the quiet tables; reserve scheduled VACUUM FULL + ANALYZE for the high-churn hot tables where the unsorted percentage actually climbs.

Senior interview question on Spectrum and maintenance

A senior interviewer might ask: "Your orders table holds ten years of history in managed storage, but 95% of queries touch the last 90 days. Storage cost is climbing and month-end trend reports scan the whole table. Design a lake-house layout using Spectrum, keep consumers on one queryable object, and specify the maintenance that keeps the hot table fast — then justify the cost model."

Solution Using an archived Parquet lake, a Spectrum external table, a UNION view, and scheduled maintenance

-- 1. Unload cold history (older than 90 days) to partitioned Parquet in S3
UNLOAD ($$
    SELECT id, customer_id, order_date, total_cents,
           EXTRACT(year  FROM order_date) AS year,
           EXTRACT(month FROM order_date) AS month
    FROM   orders
    WHERE  order_date < DATEADD(day, -90, CURRENT_DATE)
$$)
TO 's3://lake/orders_archive/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftSpectrumRole'
FORMAT AS PARQUET
PARTITION BY (year, month) ALLOWOVERWRITE;

-- 2. External schema + partitioned external table over the archive
CREATE EXTERNAL SCHEMA IF NOT EXISTS spectrum_lake
FROM DATA CATALOG DATABASE 'lake'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftSpectrumRole';

CREATE EXTERNAL TABLE spectrum_lake.orders_archive (
    id BIGINT, customer_id BIGINT, order_date DATE, total_cents BIGINT
) PARTITIONED BY (year INT, month INT)
  STORED AS PARQUET
  LOCATION 's3://lake/orders_archive/';

-- 3. Delete archived rows from the hot table, then reclaim + re-sort
DELETE FROM orders WHERE order_date < DATEADD(day, -90, CURRENT_DATE);
VACUUM FULL orders;      -- reclaim space from the mass delete + re-sort
ANALYZE orders;          -- refresh stats for the now-smaller table

-- 4. One queryable object for consumers (hot local + cold S3)
CREATE OR REPLACE VIEW orders_all AS
SELECT id, customer_id, order_date, total_cents FROM orders
UNION ALL
SELECT id, customer_id, order_date, total_cents FROM spectrum_lake.orders_archive;

Step-by-step trace.

Layer	Component	Purpose
Archive	UNLOAD ... PARTITION BY (year, month)	cold history → partitioned Parquet in S3
External	orders_archive external table	query S3 in place; prune by year/month
Hot table	DELETE old rows + VACUUM FULL	shrink + reclaim + re-sort managed storage
Stats	ANALYZE orders	planner stats current for the smaller table
Consumer	orders_all UNION view	one object; hot + cold transparent
Cost	RMS shrinks; Spectrum billed per byte	pay storage for 90 days, scan-cost for history

Walk it: the UNLOAD writes ten years minus 90 days of history to partitioned Parquet; the external table exposes it for query-in-place with partition pruning; the DELETE + VACUUM FULL shrink the hot table and reclaim the deleted space so managed-storage cost drops and zone maps stay tight; ANALYZE keeps the planner honest; and the orders_all view means dashboards keep querying one name while recent filters hit local storage and month-end reports prune to a month in S3.

Output:

Metric	Before	After
Managed storage held	10 years	90 days
Month-end report scan	whole local table	one month pruned in S3
Storage cost	high (all history in RMS)	low (90 days in RMS)
History query cost	compute-hours	Spectrum bytes scanned
Consumer changes	—	none (query orders_all)

Why this works — concept by concept:

• UNLOAD to partitioned Parquet — writing the cold history as columnar, year/month-partitioned files in S3 is what makes later Spectrum queries prunable and cheap; Parquet lets Spectrum read only needed columns and row groups.
• Spectrum external table — the archive is queried in place from S3, never copied back into the cluster, so it leaves managed storage yet stays queryable — the core of the lake-house split.
• DELETE + VACUUM FULL on the hot table — removing archived rows shrinks managed storage, and VACUUM FULL reclaims the space the delete marked dead and re-sorts what remains so zone-map pruning keeps working.
• ANALYZE + UNION view — ANALYZE refreshes stats for the now-smaller hot table so the planner stays optimal, and the orders_all view keeps every consumer on one object while the hot/cold boundary is invisible.
• Cost — one-time UNLOAD + DELETE + VACUUM (O(archived rows)), then ongoing: managed storage bills only 90 days, and history queries bill Spectrum per byte scanned (minimised by partition pruning). For a 95%-hot workload this trades a large fixed storage bill for a small storage bill plus a rare per-scan charge — a clear net win.

SQL
Topic — sql
SQL external-table and lake query problems

Practice →

Data Processing
Topic — data-processing
Data-processing problems on lake-house pipelines

Practice →

---

Cheat sheet — Amazon Redshift tuning recipes

• Node-type decision matrix. RA3 is the 2026 default: compute nodes with a local-SSD cache read/write from Redshift Managed Storage on S3, so you size compute for the hot working set + concurrency and let storage grow independently (billed per GB). Pick DC2 only for small (< a few TB), latency-sensitive clusters whose whole dataset fits in local SSD; migrate DS2 → RA3. Symptom "we keep adding nodes to fit the data" = move to RA3. Use pause/resume for idle clusters and data sharing for one-producer-many-consumers.
• DISTKEY / SORTKEY selection checklist. Distribute on the column the biggest join uses so both sides collocate (DS_DIST_NONE); the key must also be high-cardinality enough that svv_table_info.skew_rows stays near 1.0. Use DISTSTYLE ALL for small dimensions (replicated everywhere, join never redistributes), EVEN when no join dominates, and AUTO when you trust the heuristic. Sort on the column you filter on most (usually a date) with a COMPOUND key; reserve INTERLEAVED for genuine multi-column independent filters and budget for VACUUM REINDEX.
• EXPLAIN redistribution-token cheat card. DS_DIST_NONE = collocated, nothing moves (target). DS_DIST_INNER = inner redistributed on the join key (OK if small). DS_BCAST_INNER = inner broadcast to every slice (fine only if tiny; red flag if large — usually a wrong DISTKEY). DS_DIST_BOTH = both sides moved (worst; neither is on the join key). Read the plan before you touch a table.
• Automatic WLM + QMR + concurrency-scaling template. Start with Automatic WLM: separate queues by priority (ETL highest, BI high, ad-hoc low), concurrency_scaling: auto on read-heavy queues, and short_query_queue: true for SQA. Add QMR abort rules — query_execution_time > N, nested_loop_join_row_count > 1e9 — to kill runaways before they starve the cluster. Drop to manual WLM only to hand-carve memory/slots; remember memory-per-query = queue_memory% / slots, so more slots means more disk spills.
• Concurrency scaling boundary. It adds concurrency for eligible read bursts by spinning up a transient cluster, billed in credits (~1 free hour/day, per-second beyond). It does not make a single query faster — a slow scan on an unsorted table or a DS_BCAST_INNER join is a §3 problem, not a concurrency problem. Turn it on for spiky BI; never use it to paper over a bad DISTKEY.
• Spectrum external-table DDL + pruning. CREATE EXTERNAL SCHEMA ... FROM DATA CATALOG + CREATE EXTERNAL TABLE ... PARTITIONED BY (...) STORED AS PARQUET LOCATION 's3://...'. Cost = bytes scanned in S3, so store columnar (Parquet/ORC), partition by the columns queries filter on (date), and select only needed columns. Register partitions via crawler or partition projection. An unpartitioned SELECT * over JSON is the most expensive query you can write.
• Lake-house pattern. Hot data in managed storage, cold history UNLOADed to partitioned Parquet in S3 and exposed as a Spectrum external table, joined by a orders_all UNION ALL view so consumers query one object. Managed-storage cost drops to the hot window; history bills Spectrum per byte only when queried.
• VACUUM / ANALYZE runbook. VACUUM FULL re-sorts + reclaims (use after heavy churn); VACUUM SORT ONLY / DELETE ONLY are targeted; VACUUM REINDEX rebuilds interleaved keys. ANALYZE refreshes planner stats (run after big loads). Drive both off svv_table_info.unsorted and stats_off thresholds rather than blindly nightly; let auto-vacuum/auto-analyze handle quiet tables. VACUUM cannot run inside a transaction block.
• System tables to memorise. svv_table_info (diststyle, sortkey, skew_rows, unsorted, stats_off), stl_dist (rows redistributed per query), stl_wlm_query (queue vs exec time), svl_query_summary (is_diskbased spills), svl_s3query_summary (Spectrum bytes scanned), stl_scan (local vs remote block reads). Diagnose from these before adding nodes.
• Cost knobs, ranked. Pause/resume idle clusters (biggest quick win); archive cold data to Spectrum (shrinks RMS); right-size WLM + concurrency scaling instead of permanent nodes; fix DISTKEY/SORTKEY so queries scan less; use DISTSTYLE ALL only on genuinely small dimensions. Every latency fix has a dollar side — name it.
• Migration order between knobs. Diagnose first (svv_table_info, EXPLAIN), then fix cheapest-reversible-first: WLM config (minutes) → concurrency scaling toggle → VACUUM/ANALYZE (a job) → distribution/sort rewrite (O(rows), last). Elastic-resize for genuinely compute-bound workloads only — after the tree bottoms out with no other knob.

Frequently asked questions

What is Amazon Redshift in one sentence?

Amazon Redshift is a fully-managed, massively-parallel, columnar cloud data warehouse that spreads each table's rows across the slices of a compute cluster and answers analytical SQL by scanning compressed column blocks in parallel. Its performance and cost are governed by a handful of design choices — node type (RA3 nodes with managed storage vs DC2), table distribution and sort keys, workload management with concurrency scaling, Redshift Spectrum for querying S3 in place, and VACUUM/ANALYZE maintenance — and a senior interview is almost always a test of whether you can reason about which of those knobs owns a given symptom. Getting the physical layout right is what turns a multi-second query into a sub-second one.

RA3 vs DC2 — which node type should I pick?

Pick RA3 for almost every new or growing cluster: RA3 nodes decouple compute from Redshift Managed Storage on S3, so you size compute for your query workload and concurrency while storage grows independently and is billed per GB — the local SSD is just a cache for hot blocks. That ends the DC2 pattern of buying compute nodes only to hold more bytes, and it unlocks pause/resume and data sharing. Choose DC2 only for small, latency-sensitive clusters (a few TB or less) whose entire dataset fits in local SSD and where you will never need to scale storage past compute; migrate legacy DS2 clusters to RA3. The tell that you should be on RA3 is "we keep adding nodes just to fit the data" — on RA3 that is a storage line item, not a compute decision.

What's the difference between a distribution key and a sort key?

A distribution key controls where each row lives — which compute slice it is hashed to — and its job is to collocate the two sides of a join so the query runs locally with no data movement (DS_DIST_NONE in EXPLAIN); choose it from the biggest join, and make sure it is high-cardinality enough to avoid skew. A sort key controls the physical order of rows within each slice, and its job is to let a filtered scan skip blocks: Redshift keeps min/max zone maps per 1 MB block, so a filter on the sort-key column reads only the blocks whose range can match. They are orthogonal — distribute on the join column, sort on the filter column — and you choose both independently for each table. A compound sort key is best for filters that lead with its first column; an interleaved key prunes on any of its columns but costs a heavier VACUUM REINDEX.

How does concurrency scaling work and when does it kick in?

Concurrency scaling is Redshift's burst release valve: when eligible queries would otherwise wait in a WLM queue, Redshift automatically spins up a transient cluster, runs the overflow queries there in parallel, and tears it down when the burst passes — to users the main cluster simply appears faster under load. It applies mainly to read queries and scales concurrency, not single-query speed, so it fixes "fast alone, slow at 9 a.m." queueing but will not rescue a query that is slow because it scans an unsorted table or broadcasts a huge inner table. It is billed in credits, with roughly one free hour of scaling accrued per day of cluster use and per-second charges beyond that, which makes it cheap insurance for spiky BI workloads — but never a substitute for fixing a bad distribution key.

What is Redshift Spectrum and when should I use it?

Redshift Spectrum lets a Redshift query read external tables whose data lives as files in Amazon S3 — registered through an external schema over the AWS Glue Data Catalog — without ever loading that data into the cluster. Use it for large, cold, rarely-queried data (typically historical archives) so you stop paying managed-storage prices for years of history you touch monthly; keep hot, frequently-queried data in local managed storage for speed. The classic lake-house pattern unloads cold history to partitioned Parquet in S3, exposes it as a Spectrum external table, and unions it with the hot local table in a view so consumers see one object. Spectrum's cost model is bytes scanned in S3, so partition by the columns you filter on (date) and store columnar Parquet — an unpartitioned SELECT * over JSON is the most expensive thing you can run against S3.

Do I still need to run VACUUM on Redshift in 2026?

Mostly not by hand — auto-vacuum (sort and delete) and auto-analyze now run in the background during low-load periods and keep the majority of tables healthy. But you still need to understand and occasionally schedule it: DELETE leaves dead rows and UPDATE leaves both a dead row and an unsorted new one, so a high-churn table can drift into a bloated, unsorted state where zone-map pruning stops working. VACUUM FULL re-sorts and reclaims that space; VACUUM REINDEX rebuilds interleaved sort keys. ANALYZE is the separate step that refreshes the statistics the planner uses to choose join order and distribution — VACUUM fixes the physical layout, ANALYZE fixes the plan. Watch svv_table_info.unsorted and stats_off, and schedule VACUUM FULL + ANALYZE in the ETL window for the high-churn, latency-critical tables the automation cannot fully keep up with.

Practice on PipeCode

• Drill the SQL practice library → for the warehouse joins, window functions, and analytical queries Redshift interviews lean on.
• Sharpen query tuning on the optimization practice library → for the EXPLAIN-plan, distribution, sort-key, and WLM reasoning that separates senior answers.
• Firm up modelling fundamentals on the database practice library → for the fact/dimension and physical-layout decisions behind DISTKEY and SORTKEY choices.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the five-knob tuning map against real graded inputs.

Lock in Amazon Redshift tuning muscle memory

Docs explain the knobs. PipeCode drills explain the decision — when a DISTKEY collocates a join versus broadcasts it, when a sort key skips blocks, when WLM queueing (not query cost) is the bottleneck, when Spectrum earns its place, and when VACUUM/ANALYZE is what a degraded table actually needs. Pipecode.ai is Leetcode for Data Engineering — tuning-first practice built around the production trade-offs senior data engineers face.

Practice optimization problems →
Practice SQL problems →