Amazon Redshift interviews test massively parallel processing design: distribution and sort keys, compression, loading with COPY, VACUUM and ANALYZE, workload management, Spectrum, Serverless and reading query plans. AWS's developer guide, checked November 17, 2026, says AUTO is the default distribution style, COMPOUND the default sort type, and that Redshift no longer supports Python UDFs after June 30, 2026, with enforcement in phases. Below are 30 questions by level, five scenarios and how interviews test Redshift.
Distribution styles
| Style | How rows are placed | Use for |
|---|---|---|
| AUTO (default) | Redshift starts small tables as ALL and may switch to KEY or EVEN as they grow | Most tables, unless you know better |
| EVEN | Round-robin across slices | Tables not joined, or with no clear key |
| KEY | Rows with the same key value on the same slice | Large tables joined on that column |
| ALL | A full copy on every node | Small, slowly changing tables; AWS notes little benefit for small dimensions |
Fundamentals
- Describe Redshift's architecture. A leader node parses and plans queries and coordinates compute nodes, which are split into slices that each process part of the data. RA3 nodes keep data in Redshift Managed Storage, separating compute from storage; Redshift Serverless measures capacity in RPUs.
- Why is Redshift fast at analytics? Columnar storage in 1 MB blocks with zone maps (min and max values per block), compression, and parallel execution across slices. A one-month filter on five years of date-sorted data can skip most blocks.
- How do you choose a distribution key? A column used in large joins, with high cardinality and an even spread of values. Avoid keys with skew, such as a column that is mostly null or dominated by one customer.
- Compound or interleaved sort key? Compound, the default, sorts by columns in order and works best when filters use a prefix of them; it suits regularly updated tables. Interleaved weights up to eight columns equally for filters on any of them, but costs more to maintain.
- Which compression encodings matter? Redshift assigns AZ64 to numeric, date and timestamp columns by default and RAW to sort key columns. Check suggestions with
ANALYZE COMPRESSION table_name;. - How should you load data? With COPY from Amazon S3, which loads in parallel across slices:
COPY sales FROM 's3://bucket/sales/' IAM_ROLE '<role-arn>' FORMAT AS PARQUET;Avoid single-row INSERTs for bulk data. - How many files should a COPY load? Redshift splits uncompressed CSV and Parquet or ORC files of 128 MB or more automatically. Files it can't split, such as gzipped CSV or JSON, should be split into similar files of 1 MB to 1 GB after compression, in a multiple of the number of slices.
- What do VACUUM and ANALYZE do? VACUUM reclaims space from deleted rows and restores sort order; ANALYZE refreshes planner statistics. Redshift runs automatic vacuum delete and automatic table sort in the background, but a manual VACUUM after a big load can still help.
- Are primary and foreign keys enforced? No. They are informational, though the planner uses them, so your pipeline must prevent duplicates and orphans itself.
- What is Redshift Spectrum? Querying data in Amazon S3 through external tables in an external schema, typically backed by the AWS Glue Data Catalog, billed by data scanned. Partition and compress the S3 data to cut cost.
- Provisioned cluster or Serverless? Serverless scales capacity in RPUs and bills for compute used, which suits variable workloads. Provisioned RA3 clusters suit steady, predictable workloads, especially with reserved pricing.
Intermediate
- How do you read join steps in EXPLAIN? Look at the redistribution labels: DS_DIST_NONE and DS_DIST_ALL_NONE need no data movement, DS_BCAST_INNER broadcasts the inner table, and DS_DIST_BOTH redistributes both sides, usually the costliest. Fix with distribution keys on join columns.
- How do you find skew? Query
SVV_TABLE_INFOfor skew_rows and the unsorted percentage, then change the distribution key or use EVEN for the skewed table. - How does workload management work? Automatic WLM manages memory and concurrency with query priorities; manual WLM defines queues. Short query acceleration fast-tracks short queries, and query monitoring rules log, hop or abort runaway queries.
- What is concurrency scaling? Redshift adds transient capacity to absorb bursts of eligible queries, then removes it, so dashboards don't queue at peak times. Check eligibility and cost for your workload.
- When do you use materialized views? For expensive joins and aggregations queried often. Redshift can refresh them automatically and rewrite eligible queries to use them.
- What is a late-binding view? A view created
WITH NO SCHEMA BINDING, which doesn't lock the tables it references, so you can drop and recreate them. Views over Spectrum tables must be late binding. - How do you upsert? Load to a staging table with COPY, then
MERGE INTO target USING stage ON target.id = stage.id WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ..., or delete-then-insert inside one transaction. - How do you export data?
UNLOAD ('SELECT ...') TO 's3://bucket/export/' IAM_ROLE '<role-arn>' FORMAT AS PARQUET;writes files in parallel, optionally partitioned. - What is data sharing? Live, read-only access to data across clusters, workgroups or accounts without copying it, which lets you isolate consumer workloads on their own compute.
- How do you handle semi-structured data? Load JSON into the SUPER type and query it with PartiQL navigation and unnesting, or shred frequently used fields into typed columns.
Experienced and senior
- Design tables for a 5 TB fact joined to a large customer dimension. Distribute both on customer_id if they are joined that way and the key is even, sort the fact by date for range filters, keep small dimensions on AUTO or ALL, then check skew and EXPLAIN on real queries.
- When does ALL distribution hurt? It multiplies storage by the number of nodes and slows loads and updates, so it fits only small, slowly changing tables. AWS notes that redistributing small tables is cheap, so ALL brings little benefit for small dimensions.
- Which system views do you use to troubleshoot? SYS_QUERY_HISTORY and the other SYS monitoring views, which also cover Serverless, plus SVV_TABLE_INFO for table health and STL_LOAD_ERRORS for COPY failures on provisioned clusters.
- What are zero-ETL integrations? Managed replication from sources such as Amazon Aurora and DynamoDB into Redshift, which removes custom pipelines for near-real-time analytics on operational data.
- What does the Python UDF end of support mean? AWS says Redshift no longer supports Python UDFs after June 30, 2026, with enforcement in phases. Move logic to SQL UDFs, or to Lambda UDFs where you need external code.
- How do you secure Redshift? IAM roles for COPY and UNLOAD, column-level grants, row-level security policies, dynamic data masking, encryption at rest and in transit, and private VPC access.
- How do you isolate workloads? Separate WLM queues and priorities on one cluster, or data sharing to give ELT, BI and data science their own clusters or Serverless workgroups.
- A query got slower after a large delete. Why? Deleted rows still occupy blocks and stats are stale until vacuum and analyze catch up. Check the unsorted and stats-off columns in SVV_TABLE_INFO, then VACUUM and ANALYZE the table.
- Redshift, Snowflake or BigQuery? Redshift gives you distribution and sort control with tight AWS integration; Snowflake separates warehouses you size; BigQuery is serverless by bytes or slots. Compare our BigQuery interview questions and Snowflake questions for experienced engineers.
Five scenarios interviewers give
- A COPY fails halfway. Read the load error views for the file, line and column, fix the format or type issue, and use a manifest so only the intended files load.
- One slice holds ten times more data than the others. The distribution key is skewed. Change it or use EVEN, then rebuild the table.
- EXPLAIN shows DS_DIST_BOTH on the biggest join. Neither table is distributed on the join key. Distribute the larger tables on it.
- A query runs out of disk mid-run. Usually a join multiplying rows or a broadcast of a huge table. Check the plan and join keys before resizing.
- Dashboards queue every month-end. Use concurrency scaling, short query acceleration and a separate queue or workgroup for BI.
How interviews test Redshift
Employers don't publish question banks, but AWS data engineer candidates commonly report SQL, a table-design exercise with distribution and sort keys, plan reading, and AWS ecosystem questions on S3, Glue and IAM; see our AWS interview questions and SQL interview questions.
Where CoPilot Interview fits
CoPilot Interview is an AI interview assistant whose free AI mock interview builds Redshift and data engineering questions from your résumé and the job description, reads them aloud and scores each spoken answer out of 10. The browser demo at /try has 145 practice questions in 10 tracks, scored out of 10, with no signup. The free plan gives 15 minutes a day of live help in sessions of up to 15 minutes, with AI mock interviews included; Standard costs $24.99 a month and Pro $39.99. For data roles, see our guide to practicing data engineer interviews with AI. Live coding tasks, take-homes and online assessments usually forbid outside help, so prepare for those; in any live interview, follow your employer's rules.
Disclosure: CoPilot Interview is our product, so weigh what we say about it accordingly.
Frequently asked questions
What is the difference between a distribution key and a sort key?
A distribution key decides which slice stores each row, which affects joins and data movement; a sort key decides the order within storage, which lets range filters skip blocks.
Is Redshift a columnar database?
Yes. It stores data by column in 1 MB blocks with zone maps and compression, which suits large scans and aggregations.
Does Redshift enforce primary keys?
No. Constraints are informational and used by the planner, so deduplicate in your load process.
What is Redshift Serverless?
A deployment option that provisions capacity automatically, measured in RPUs, and bills for compute used, with no cluster to size or manage.
How do I prepare for a Redshift interview?
Load a sample star schema with COPY, try different distribution and sort keys, compare EXPLAIN plans, and practice explaining why one design moved less data.
Practice this interview before the real one
The AI mock interview builds questions from your resume and the job description, reads them aloud and scores each spoken answer out of 10. Included on the free plan.
Start a mock interview