Redshift Interview Questions and Answers

Redshift interview questions return again and again to two table design choices: how rows are distributed across the cluster's slices, and how they are sorted within them. Both decide how much work each query does, and both are easy to describe and hard to reason about without numbers. Here they are modelled on three million real taxi trips from January 2024 (NYC TLC: trip record data). This is a model of Redshift's documented behaviour, not a Redshift cluster, but the effects it shows are the ones interviewers ask about.

import numpy as np
import pandas as pd

trips = pd.read_parquet("yellow_tripdata_2024-01.parquet", columns=["VendorID", "tpep_pickup_datetime", "PULocationID"])
trips = trips[(trips.tpep_pickup_datetime >= "2024-01-01") & (trips.tpep_pickup_datetime < "2024-02-01")].reset_index(drop=True)
SLICES = 8                                                      # a model of an 8-slice cluster, not Redshift itself
print(f"{len(trips):,} trips on {SLICES} modelled slices")


def spread(slice_of_row):
    counts = np.bincount(slice_of_row, minlength=SLICES)
    return f"busiest slice {counts.max() / counts.mean():.2f}x the average, {np.count_nonzero(counts)} of {SLICES} slices used"


print("\nR1 distribution styles")
print("   EVEN (round robin):        ", spread(np.arange(len(trips)) % SLICES))
print("   KEY on PULocationID:       ", spread(trips.PULocationID.to_numpy() % SLICES))
print("   KEY on VendorID:           ", spread(trips.VendorID.to_numpy() % SLICES), f"| distinct vendors: {trips.VendorID.nunique()}")

print("\nR2 joining trips to the zone table on pickup zone: rows that must move between slices")
home = {"EVEN": np.arange(len(trips)) % SLICES, "KEY PULocationID": trips.PULocationID.to_numpy() % SLICES}
needed = trips.PULocationID.to_numpy() % SLICES                  # zones distributed by LocationID live here
for style, where in home.items():
    print(f"   trips {style:<17} -> {np.mean(where != needed):.1%} of trip rows redistributed (zones distributed by key)")
print("   zones DISTSTYLE ALL        -> 0.0% of trip rows move; every slice holds a full copy of the 265 zones")

print("\nR3 sort key and zone maps: blocks read for one day (15 January)")
ROWS_PER_BLOCK = 2**20 // 8                                     # one 1 MB block of uncompressed 8-byte timestamps
ts = trips.tpep_pickup_datetime.to_numpy()
day_start, day_end = np.datetime64("2024-01-15"), np.datetime64("2024-01-16")
for name, order in (("sorted by pickup time", np.sort(ts)), ("in random order", np.random.default_rng(0).permutation(ts))):
    blocks = [order[i:i + ROWS_PER_BLOCK] for i in range(0, len(order), ROWS_PER_BLOCK)]
    read = sum(1 for b in blocks if b.min() < day_end and b.max() >= day_start)
    print(f"   {name:<22} {read:>2} of {len(blocks)} blocks overlap the day")

2,964,606 trips on 8 modelled slices

R1 distribution styles
   EVEN (round robin):         busiest slice 1.00x the average, 8 of 8 slices used
   KEY on PULocationID:        busiest slice 1.57x the average, 8 of 8 slices used
   KEY on VendorID:            busiest slice 6.03x the average, 3 of 8 slices used | distinct vendors: 3

R2 joining trips to the zone table on pickup zone: rows that must move between slices
   trips EVEN              -> 87.5% of trip rows redistributed (zones distributed by key)
   trips KEY PULocationID  -> 0.0% of trip rows redistributed (zones distributed by key)
   zones DISTSTYLE ALL        -> 0.0% of trip rows move; every slice holds a full copy of the 265 zones

R3 sort key and zone maps: blocks read for one day (15 January)
   sorted by pickup time   2 of 23 blocks overlap the day
   in random order        23 of 23 blocks overlap the day

R1. What are Redshift's distribution styles?

AUTO, EVEN, KEY and ALL (AWS: distribution styles). EVEN deals rows round-robin, so the model's slices were perfectly balanced. KEY sends every row with the same key value to the same slice, which is only as even as the key: pickup zone gave a busiest slice 1.57 times the average. VendorID, with only 3 distinct values, used 3 of 8 slices and loaded the busiest with 6 times the average, so most of the cluster sat idle while one slice did the work. Distribution skew makes some nodes do more work than others (AWS: data distribution). A good key has many distinct values with similar counts, and is used in the biggest joins.

R2. Why does the distribution key matter for joins?

Because rows that join must meet on the same slice. If the trips table is distributed EVEN and the zone table by its key, 87.5% of trip rows had to move to reach their zone, which is seven-eighths, as chance predicts with 8 slices. Distributing trips by pickup zone collocated them with the zones, and no trip rows moved. A fact table has only one distribution key, so collocate it with the dimension it joins most and at the greatest volume (AWS: choosing a distribution key).

For small dimensions, use ALL. A copy of the 265-row zone table on every node means no trip rows move for that join, at a small cost in storage and load time. That trade-off becomes expensive for large tables.

R3. What do sort keys do?

They let Redshift skip blocks. Data is stored in 1 MB blocks with the minimum and maximum value of each block recorded, so a query with a range filter can ignore blocks whose range does not overlap (AWS: sort keys). With trips sorted by pickup time, a query for 15 January overlapped 2 of 23 modelled blocks; with the same rows in random order, all 23. Real blocks hold compressed data and far more rows, so the numbers would differ, but the ratio is the point. Put the column most often used for range filters, usually a timestamp, first in the sort key.

R4. Should you just use AUTO?

Usually start there. AWS recommends DISTSTYLE AUTO and SORTKEY AUTO, which let automatic table optimisation choose and change styles as the table grows: for example, a small table starts with ALL and may later switch to KEY. In an interview, say that you would start with AUTO, then override only where query plans and skew statistics show a clear benefit, such as a large fact table that is always joined on one key.

R5. What has changed recently that interviewers may ask about?

Python UDFs. AWS documentation states that Redshift no longer supports Python UDFs after 30 June 2026, enforced in phases (AWS: sort keys page notice). Logic that used them has to move to SQL UDFs, Lambda UDFs or the pipeline outside the warehouse. Knowing current platform changes like this is an easy way to stand out in an interview.

Other questions to expect

• COPY versus INSERT: load bulk data with COPY from S3, which loads in parallel; row-by-row INSERTs are slow.
• VACUUM and ANALYZE: reclaim space and re-sort after deletes and updates, and refresh statistics for the planner. Redshift automates both in the background, but heavy changes can still call for manual runs.
• Spectrum and data lakes: query files in S3 without loading them, useful for cold or rarely used data.
• Workload management: queues and concurrency settings that stop one heavy query from starving short dashboard queries; describe how you would separate ETL loads from interactive users.

How to answer Redshift questions

• Describe the data's distribution before choosing a key: distinct values and their counts.
• Name the joins you want collocated.
• Match the sort key to the commonest range filter.

The service is explained in Amazon Redshift explained and modelling in data modelling in Redshift. Platform-neutral design questions are in data warehouse interview questions, the BigQuery counterpart in BigQuery interview questions, and the wider AWS round in AWS interview questions for data engineers.

Warehouse design is part of the Data Engineering course.

Sources

• Distribution styles (Amazon Redshift Database Developer Guide) — Amazon Web Services
• Data distribution for query optimization (Amazon Redshift) — Amazon Web Services
• Sort keys (Amazon Redshift Database Developer Guide) — Amazon Web Services
• Choose the best distribution style (Amazon Redshift) — Amazon Web Services
• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission