Amazon Redshift Interview Questions: Architecture, Performance, and Design Scenarios

These questions cover the topics that appear most frequently in data engineer, solutions architect, and cloud specialist interviews for Redshift. The answers focus on the “why” rather than just the “what” — interviewers typically push beyond definitions.

Architecture

Q1: Describe the architecture of a Redshift cluster. What does each component do?

A Redshift cluster has a leader node and one or more compute nodes.

The leader node handles all client connections, parses SQL queries, builds query execution plans, compiles the plans into code, distributes code to compute nodes, collects results, and returns the final answer to the client. It stores no user data.

Compute nodes store data and execute the parts of the query plan sent by the leader node. They work in parallel, each processing its own portion of the data. Compute nodes do not communicate directly with the client.

Each compute node is subdivided into slices — independent processing units with dedicated CPU, memory, and disk. Data is distributed at the slice level. A cluster’s total parallelism equals the total number of slices across all compute nodes.

Q2: What is the difference between DC2 and RA3 node types?

DC2 nodes use directly attached local SSDs. Storage is fixed per node — if you need more storage, you resize the cluster. DC2 is faster for queries that do not hit S3 because local disk latency is lower.

RA3 nodes use local SSDs as a cache but store data persistently in Amazon S3. Storage scales independently from compute. You can add or remove compute nodes without data migration. RA3 enables Redshift features like Data Sharing, Redshift Serverless, and the separation of storage cost from compute cost. RA3 is the recommended choice for new deployments.

Q3: What happens if the leader node fails?

AWS automatically replaces a failed leader node. The replacement process takes a few minutes, during which the cluster is unavailable. Data is not at risk because the leader node holds no user data. For multi-node clusters, this failover is handled automatically with no data loss.

Data Distribution

Q4: What are the four distribution styles in Redshift? When do you use each?

KEY: You designate one column as the distribution key (DISTKEY). Rows with the same key value go to the same slice. Use for large fact tables where you frequently join on a specific column. The goal is collocated joins — when two tables are distributed on the same join key, the join happens locally on each slice without data movement.

EVEN: Rows are distributed round-robin across slices. Simple and balanced, but joins between two EVEN-distributed tables require broadcasting or redistributing data, which is slow. Use for staging tables or tables that do not participate in joins.

ALL: A complete copy of the table is stored on every compute node. Use for small dimension tables (a few million rows or less). Any join involving an ALL table is always local. The trade-off is higher storage cost and slower writes.

AUTO: Redshift chooses the style based on table size. Small tables get ALL; larger tables get EVEN. Convenient for beginners but less predictable for performance-critical workloads.

Q5: What is a collocated join? Why does it matter?

A collocated join happens when all matching rows from two joining tables are on the same slice, so the join executes locally without moving data between nodes. It is the highest-performance join type in Redshift.

Collocated joins occur when:

• Both tables have the same DISTKEY column and you join on that column
• One of the tables uses ALL distribution (the full table is on every node)

When a join is not collocated, Redshift must redistribute one or both tables by broadcasting rows between nodes — a significant network overhead for large tables.

Q6: What is data skew in Redshift, and how do you identify and fix it?

Data skew means some slices hold far more rows than others because many rows share the same distribution key value. The overloaded slice becomes a bottleneck — it finishes last and holds up the entire query.

Identify it by querying SVV_TABLE_INFO and looking at skew_rows, or by querying STL_DIST to examine row distribution across slices.

Fix it by:

• Choosing a better distribution key with higher cardinality (more distinct values)
• Adding a synthetic column that combines multiple low-cardinality columns
• Switching to EVEN distribution if no good key exists and joins are not the primary bottleneck

Sort Keys

Q7: What is a sort key? What are the two types, and how do you choose?

A sort key is one or more columns that physically sort the data on disk within each slice. Redshift maintains zone maps — metadata recording the minimum and maximum sort key value in each 1 MB data block. Queries that filter on the sort key skip blocks that cannot contain matching rows, dramatically reducing I/O.

Compound sort key: Sorts by multiple columns in a strict hierarchy. A sort key of (year, month, day) is optimal for queries filtering on year, or year+month, or year+month+day. It is much less useful for queries filtering only on month, because the data is not sorted by month in isolation. Use for tables where most queries filter on the leading column.

Interleaved sort key: Gives equal weight to each column. Useful when queries filter on any of several different columns without a dominant leading one. The trade-off is slower VACUUM and COPY operations due to the more complex internal index structure.

Q8: What happens to sort key effectiveness over time, and how do you fix it?

New rows are appended to the table unsorted, regardless of the sort key. Over time, a large fraction of the table becomes unsorted. Zone maps become less effective because unsorted blocks have wide min/max ranges that do not filter well.

Fix this by running VACUUM SORT ONLY tablename. This resorts the unsorted region and reclaims the zone map efficiency. For tables with heavy inserts, Auto VACUUM maintains sort order in the background, but very active tables may need manual vacuum intervention.

Performance Tuning

Q9: A query that used to run in 30 seconds is now taking 15 minutes. How do you investigate?

Start with the system tables:

1. STL_QUERY and STL_QUERYTEXT: Find the exact query and its execution time
2. SVL_QUERY_SUMMARY: Step-by-step breakdown of where time was spent (which steps took longest)
3. SVL_QUERY_REPORT: Per-slice execution to identify uneven work distribution (skew)
4. EXPLAIN <query>: The query execution plan — look for hash redistribution steps (DS_DIST_BOTH or DS_BCAST_INNER) which indicate join data movement
5. SVV_TABLE_INFO: Check skew_rows and unsorted percentage

Common causes of performance regression:

• Table grew and the distribution key is now skewed
• A large unsorted region developed (run VACUUM)
• Statistics are stale (run ANALYZE)
• A new query pattern is causing unplanned redistribution

Q10: What is the COPY command, and why is it preferred for bulk loads over INSERT?

COPY is Redshift’s optimised bulk load command. It reads data in parallel from S3, DynamoDB, or an SSH connection and distributes it directly to compute slices. It uses all available compute resources and bypasses the leader node for the actual data transfer.

INSERT routes every row through the leader node, serialises write operations, and uses commit overhead for each statement. For loading millions of rows, INSERT is typically 50-100x slower than COPY.

Best practices for COPY:

• Split large files into multiple files (one per slice is optimal)
• Use compressed data (gzip or bzip2) — COPY decompresses transparently
• Use Parquet or ORC format for typed data
• Specify the COMPUPDATE option to update column compression encodings

Q11: What is the UNLOAD command?

UNLOAD exports query results from Redshift to S3. It runs in parallel — each slice writes its own output file. By default, output files are pipe-delimited text. You can specify Parquet format for efficient downstream consumption.

Use UNLOAD for:

• Creating data exports for downstream systems
• Archiving data from Redshift to S3
• Generating extracts for reporting systems that read from S3

WLM and Concurrency

Q12: What is Workload Management (WLM) in Redshift?

WLM lets you define multiple query queues, each with dedicated memory allocation and concurrency (number of simultaneous queries). Queries are routed to queues based on user group membership or query group labels.

Without WLM, a single heavy analytical query can consume all cluster memory and block fast dashboard queries. WLM prevents this by giving high-priority interactive queries a dedicated queue with reserved resources.

Q13: What is Short Query Acceleration (SQA)?

SQA uses a machine learning model to predict query duration before execution. Queries predicted to complete quickly (typically under 30 seconds) are routed to a dedicated SQA queue that bypasses the standard WLM queues. This ensures fast queries are not queued behind long-running ones.

SQA is enabled by default in newer Redshift versions and requires no configuration for basic use.

Q14: What is Concurrency Scaling, and when does it activate?

Concurrency Scaling automatically adds transient Redshift compute capacity when a WLM queue backs up. The transient cluster starts in approximately 60 seconds and handles overflow queries transparently. When the queue clears, the transient capacity is released.

You accrue 1 hour of free Concurrency Scaling credits per 24 hours of main cluster usage. Beyond the free credits, you pay per second of transient cluster time.

Spectrum, Data Sharing, and Advanced Features

Q15: What is Redshift Spectrum? How does it differ from a standard Redshift query?

Spectrum allows Redshift to query data in S3 without loading it into the cluster. Spectrum uses a dedicated fleet of nodes (outside the cluster) that read and filter S3 data and return results to the cluster for final aggregation.

You pay $5 per TB scanned by Spectrum. You can reduce this cost by using Parquet or ORC format (columnar, reads only needed columns) and partitioned data in S3 (lets Spectrum skip entire partitions irrelevant to the query).

Q16: What is Data Sharing in Redshift?

Data Sharing lets a producer cluster share live data with consumer clusters — in the same account or across accounts — without copying the data. The consumer reads the data directly from the producer’s storage in real time.

The producer grants a datashare to the consumer’s account. The consumer creates a local database from the datashare. Queries against the shared tables hit the producer’s storage.

Q17: What is the difference between VACUUM SORT ONLY and VACUUM DELETE ONLY?

VACUUM SORT ONLY: Re-sorts the unsorted region of a table. Does not reclaim space from deleted rows. Faster than a full VACUUM.

VACUUM DELETE ONLY: Reclaims space from rows marked as deleted. Does not re-sort. Useful after large DELETE operations.

VACUUM FULL (default): Sorts and reclaims space. Most thorough but most resource-intensive.

Design Scenarios

Q18: You are designing a 10 TB sales fact table in Redshift that is frequently joined to a 500-row stores dimension table and a 2-million-row products table. How would you set distribution and sort keys?

Sales fact table:

• DISTKEY: Choose the column used most frequently in joins — likely product_id if most queries involve products, or store_id if most queries are store-specific. Examine the access patterns first.
• SORTKEY: (sale_date) for range queries by date, or a compound key of (sale_date, store_id) if many queries filter on both.

Stores dimension (500 rows):

• DISTSTYLE ALL — 500 rows is tiny, replicate to every node for always-local joins

Products dimension (2 million rows):

• DISTKEY: product_id (same as the fact table’s DISTKEY) — collocated joins
• Or DISTSTYLE ALL if 2 million rows is still small relative to your node count (typically acceptable up to ~10 million rows depending on column count)

Q19: A data analyst complains that their dashboard queries take 2 minutes during the day but complete in 5 seconds at night. What is the likely cause and how do you fix it?

Daytime contention with ETL jobs or other users consuming cluster resources. During business hours, multiple teams are running heavy queries simultaneously, exhausting query slots.

Solutions:

• Configure WLM to give dashboard users a high-priority queue with reserved concurrency and memory
• Enable Short Query Acceleration
• Enable Concurrency Scaling to add capacity during peak hours
• Schedule resource-intensive ETL jobs for off-peak hours
• Consider separating read and write workloads using Redshift Data Sharing (reader cluster for dashboards, writer cluster for ETL)

Q20: Describe how you would migrate a 5 TB Oracle data warehouse to Amazon Redshift with minimal downtime.

1. Use AWS Schema Conversion Tool (SCT) to convert Oracle DDL to Redshift-compatible SQL. SCT identifies incompatible objects and suggests equivalents.
2. Export historical data from Oracle to S3 using AWS DMS full-load task. Load into Redshift using COPY.
3. Set up CDC (Change Data Capture) with DMS to continuously replicate changes from Oracle to Redshift while the historical load runs.
4. Validate data between source and target — row counts, checksums on key columns.
5. Schedule the cutover during a low-traffic window. Stop DMS CDC, apply any outstanding changes, redirect applications to Redshift.

For the DDL conversion, pay particular attention to Oracle-specific data types (NUMBER with precision, DATE which includes time in Oracle, CLOB/BLOB), sequences and auto-increment patterns, and stored procedures that may need to be moved to Lambda or Glue.