Amazon Redshift Interview Questions 2026: Distribution Styles, VACUUM, WLM and Answers That Hold Up

Redshift is AWS's managed, petabyte-scale data warehouse built for analytical (OLAP) workloads. Three design choices separate it from OLTP engines. First, columnar storage: data is stored per column in 1 MB blocks, so a query touching three of a hundred columns reads only those three. Second, massively parallel processing: each query is compiled and executed in parallel across compute nodes and slices. Third, aggressive per-column compression, which shrinks I/O further. It descends from PostgreSQL, so drivers and most SQL feel familiar, but it deliberately drops OLTP machinery: no B-tree indexes, no enforced primary or foreign keys, and single-row inserts and updates are slow by design. Use it for scans and aggregations over billions of rows — not for transactional point lookups and high-frequency writes.

A provisioned cluster has one leader node and one or more compute nodes. The leader node accepts client connections, parses SQL, builds and compiles the query plan, distributes work to compute nodes, and aggregates their intermediate results. Each compute node has its own CPU, memory, and storage, and is subdivided into slices; every slice owns a portion of the node's memory and disk and processes its share of the data in parallel. Rows are assigned to slices according to the table's distribution style, which is why distribution decisions directly control parallelism and join behavior. Some SQL runs entirely on the leader node and never touches compute nodes. In Redshift Serverless the same engine runs underneath, but capacity is measured in RPUs and scaled automatically instead of you choosing node counts.

Distribution style controls which slice each row lands on. KEY: rows with the same value in the distribution column go to the same slice — use it on large fact tables joined on that column so joins are co-located and avoid network shuffles. ALL: a full copy of the table sits on every node — right for small, slowly changing dimension tables; wrong for large ones, since storage and load time multiply per node. EVEN: round-robin placement — a safe choice when there is no dominant join column or when the candidate key would skew. AUTO, the default: Redshift starts small tables as ALL, switches to EVEN as they grow, and Automatic Table Optimization can later assign a KEY based on observed query patterns. In interviews, always tie the choice back to join co-location and skew.

The sort key defines the physical order of rows on disk. Redshift maintains zone maps — the minimum and maximum value of each 1 MB block — and when a query filters on the sort key, the engine compares the predicate against zone maps and skips every block that cannot contain matching rows. Filtering one week out of a year of data can eliminate the vast majority of I/O this way. Sorted data also enables efficient merge joins when two tables are sorted and distributed on the same join column, and it accelerates range predicates like BETWEEN and date filters. The classic choice is the timestamp column your queries filter on most, placed as the leading column of a compound sort key. Note that new loads accumulate in an unsorted region until a vacuum sorts them.

Updates and deletes in Redshift are logical: an UPDATE writes a new row version and marks the old one deleted, and deleted rows keep occupying disk until reclaimed. VACUUM reclaims that space and re-sorts rows that recent loads appended to the unsorted region, restoring the effectiveness of the sort key and zone maps. ANALYZE refreshes table statistics — row counts and value distributions — that the query planner uses to choose join order and strategy; stale statistics lead to bad plans. Modern Redshift runs auto vacuum delete, auto vacuum sort, and auto analyze in the background during quiet periods, so routine maintenance is largely automatic. After a massive one-off delete or bulk backfill, though, running VACUUM and ANALYZE explicitly — and checking unsorted percentage and stats_off in SVV_TABLE_INFO — is still the right move.

A compound sort key orders rows by the first column, then the second, and so on — like a phone book. Zone-map pruning is excellent for filters on the leading columns but weak for filters that touch only trailing ones. An interleaved sort key gives equal weight to up to eight columns, so filters on any subset prune reasonably well. That sounds strictly better, but interleaved keys are expensive to maintain: they need VACUUM REINDEX, their benefit degrades badly with monotonically increasing values such as timestamps or identity columns, and load and vacuum times worsen. In practice, AWS guidance and most production teams default to compound keys led by the most common filter column — usually a date. Reserve interleaved for large, mostly static tables queried on unpredictable column combinations. Explaining why interleaved backfires on append-only time-series data is exactly the tradeoff reasoning interviewers want.

Choose the column your largest tables are most frequently joined or aggregated on, so matching rows are co-located and the plan avoids redistribution. The key needs high cardinality and an even value distribution: a column like country on India-heavy data pushes most rows onto one slice, and the slowest slice sets the query's speed. Nullable columns are risky because all NULLs hash to the same slice. Detect skew through SVV_TABLE_INFO — skew_rows is the ratio between the largest and smallest slice's row counts — and at query time through SVL_QUERY_REPORT, where one slice shows far more rows processed than its peers. Fixes: switch to a higher-cardinality key, fall back to EVEN and accept some redistribution, or make the smaller side of the join DISTSTYLE ALL so co-location no longer matters.

WLM controls how memory and concurrency are shared across queries. With manual WLM you define up to eight queues, each with a memory percentage and a concurrency slot count, and route queries by user group or query group. It is predictable but brittle — mis-sized slots either waste memory or force queries to spill to disk. Automatic WLM lets Redshift set concurrency and per-query memory dynamically; you express intent through query priorities, for example critical for the ETL service user and low for ad-hoc exploration. Short Query Acceleration predicts a query's runtime with a machine-learning model and routes short queries into dedicated capacity so they are not stuck behind long scans. The current default recommendation is automatic WLM plus priorities; manual WLM survives where teams need hard isolation guarantees. Mentioning that you monitor queue waits through system views signals real operational experience.

When queue depth builds — many users, dashboards firing at 9 a.m. — concurrency scaling spins up transient, Redshift-managed clusters that serve eligible queued queries against the same data, then disappear once the burst passes. You enable it per WLM queue by setting the concurrency scaling mode. On billing: credits accrue while your main cluster runs, and usage beyond accrued credits is charged per second of scaling-cluster time. The crucial interview point is what it solves: concurrency, meaning many queries queued at once. It does not make a single slow query faster — a badly distributed join runs just as slowly on a scaling cluster. Contrast it with resizing, which adds permanent capacity, and with Serverless, which scales RPUs automatically. Matching the right lever to the symptom — queueing versus slow execution — is what the question is really probing.

Spectrum lets you query data in S3 without loading it. You create an external schema pointing at the AWS Glue Data Catalog (or a Hive metastore) and define external tables over S3 paths; a fleet of Spectrum nodes managed by AWS scans S3, and your cluster joins those results with local tables. Because Spectrum is billed per data scanned, discipline matters: store files in columnar Parquet or ORC so only referenced columns are read, partition by common filter columns such as date and always filter on partition columns to prune, compress files, and avoid millions of tiny objects. The standard architecture answer: keep hot, recent data local and historical years in S3 behind Spectrum, giving one SQL interface over both. Distinguish it clearly from COPY — Spectrum reads in place, COPY ingests into the cluster.

DC2 nodes couple compute and storage: data lives on local SSDs, and when storage fills you add nodes — buying CPU you may not need. RA3 separates the two. Data resides in Redshift Managed Storage, which is S3-backed with a large local SSD cache, and you pay for compute and storage independently. The consequences are what interviewers listen for: you size the cluster for query load rather than dataset size; storage is effectively unconstrained; and it enables data sharing, where consumer clusters or Serverless workgroups query a producer's data live without copies — letting teams isolate BI from ETL on separate compute. Snapshots and Serverless build on the same storage layer. This question often doubles as a freshness check: an answer that still assumes "disk full means add nodes" reads as pre-RA3 experience.

COPY is the bulk-loading path — it loads in parallel from S3 (also EMR, DynamoDB, or remote hosts over SSH). Best practices: split input into multiple files, ideally a multiple of the cluster's slice count so every slice participates; compress files with gzip or ZSTD; use a manifest file to load an exact list of objects atomically rather than relying on prefix matching; and load columnar Parquet directly where available. On a first load into an empty table, automatic compression analysis chooses column encodings; keep planner statistics fresh via auto analyze or STATUPDATE. What to avoid is equally important: single-row INSERTs in a loop, since each is a leader-node round trip that bloats commits. For trickle loads, batch into multi-row inserts or stage to a temporary table and INSERT INTO ... SELECT. Loading in sort-key order reduces subsequent vacuum work.

The DS_* labels on join steps describe data movement at query time. DS_DIST_NONE: no redistribution — the tables are co-located on the join key; this is the goal for large fact-to-fact joins. DS_DIST_ALL_NONE: the inner table is DISTSTYLE ALL, so no movement is needed — fine. DS_DIST_INNER or DS_DIST_OUTER: one side is rehashed across nodes — acceptable when the moved side is small. DS_BCAST_INNER: the entire inner table is broadcast to every node — expensive if that table is large. DS_DIST_BOTH: both sides are redistributed, the worst case, usually meaning the join column matches neither table's distribution key. Fixes: align one or both tables' DISTKEY with the join column, make genuinely small dimensions ALL, and refresh statistics, since bad row estimates push the planner toward broadcasts. Also check STL_ALERT_EVENT_LOG for nested-loop warnings caused by missing join predicates.

Because deletes and updates only mark rows, and because new loads land in an unsorted region, tables accumulate both dead rows and unsorted data. The variants: VACUUM FULL (the default) reclaims space and re-sorts; VACUUM DELETE ONLY reclaims without sorting — useful when data already arrives in sort order; VACUUM SORT ONLY does the reverse; VACUUM REINDEX re-analyzes interleaved sort key distributions before vacuuming and is required maintenance for interleaved keys; the BOOST option runs faster by taking more resources, at the cost of competing with the live workload. Auto vacuum now performs incremental delete-reclaim and sorting in the background, covering routine churn. Manual intervention still makes sense after a large one-off delete or bulk backfill. Two pro moves worth stating: prefer TRUNCATE over DELETE when clearing a table, since it reclaims immediately, and consider a deep copy (CREATE TABLE AS with correct keys) when most of a table is unsorted — it can beat a long vacuum.

Anchor on architecture, then derive behavior. Redshift is AWS-native: provisioned RA3 clusters (compute separated from managed storage) or Serverless RPUs, with the deepest integration into IAM, VPC, S3, Glue, and Kinesis; you still make physical-design choices — distribution and sort keys — which reward engineering effort with strong price-performance, though AUTO features have reduced the tuning burden. Snowflake is multi-cluster shared data across AWS, Azure, and GCP: independent virtual warehouses give clean workload isolation, micro-partitions remove explicit key management, and compute bills per second. BigQuery is fully serverless: no capacity to size, billed on-demand per data scanned or via reserved slots, excellent for spiky ad-hoc analytics. Sensible mapping: a deep-AWS shop with steady workloads defaults well to Redshift; heavy cross-cloud sharing or many isolated teams favors Snowflake; bursty scan-heavy analytics with minimal ops favors BigQuery. Refusing a universal winner and asking about workload shape is itself the senior signal.

Triage in order, ruling out each layer. First, separate queueing from execution: STL_WLM_QUERY shows queue time versus execution time — if the query mostly waited, the problem is WLM or concurrency, and levers are priorities, queue config, or concurrency scaling, not query tuning. Also check whether earlier runs were served from the leader node's result cache, which can make "it used to be fast" misleading. If execution is genuinely slow, read SVL_QUERY_SUMMARY and SVL_QUERY_REPORT for the slow step: look for disk-based steps (memory spill), broadcast or redistribution, or one slice processing far more rows than its peers (skew). Then check table health in SVV_TABLE_INFO — unsorted percentage, stats_off, skew_rows — and run ANALYZE or VACUUM if a bulk load degraded them. Finally, ask what changed: data volume crossing a threshold, a new join, a dropped sort-key filter, or another team's ETL contending for the cluster. Never jump to resizing before ruling these out.