Day 1 - Amazon Redshift: Distribution and Sort Keys and Workload Optimization

📌 Key Takeaways

• Redshift is a columnar MPP warehouse in which the leader node plans queries and the slices of the compute nodes execute them in parallel. Performance comes down to "how the data is laid out across slices."
• Distribution styles: KEY (co-locate rows on the same slice by join key), ALL (replicate small, rarely changing dimension tables to every node), EVEN (round-robin), AUTO (the default, which changes automatically as the table grows). A skewed DISTKEY creates data skew.
• A sort key lets the min/max values recorded for each 1 MB block (the zone map) skip blocks that fall outside a range condition. COMPOUND suits filters on the leading columns, and INTERLEAVED suits filters on varied combinations of columns.
• RA3 separates compute from storage, and Spectrum reads external tables on S3 without loading (billed by data scanned, read-only).
• Auto WLM + concurrency scaling + SQA is the standard answer to a burst of concurrent queries. For loading, COPY from multiple split files is the baseline.

Redshift Architecture Overview

Amazon Redshift is a petabyte-scale columnar MPP (Massively Parallel Processing) data warehouse. A cluster consists of one leader node and several compute nodes.

Client (SQL) ──► Leader node : parsing · query plan · result aggregation
                        │
        ┌───────────────┼───────────────┐
   Compute node 1   Compute node 2   Compute node 3
   [slice][slice] ...            ← slice = smallest unit of parallel processing

• The leader node plans the query, hands pieces of it to the compute nodes, and merges the intermediate results that come back.
• Each compute node is divided into several slices. The rows of a table are stored spread across slices, and every slice processes its own share at the same time.
• Storage is columnar. The values of one column are gathered into 1 MB blocks and compressed, so a query that aggregates "just a few columns out of hundreds of millions of rows" is fast.

💡 Related Theory: The performance of an MPP system is set by its slowest slice, because the query is not finished until every slice is finished. If data piles up on one slice (skew), the query waits for that slice even while the other slices sit idle. The goal of distribution key design is "spread evenly, but keep rows that will be joined in the same place."

Distribution Style

This determines how the rows of a table are placed across slices.

-- KEY distribution: rows with the same value in the specified column go to the same slice
CREATE TABLE orders (
  order_id   BIGINT,
  customer_id BIGINT,
  amount     DECIMAL(12,2)
)
DISTSTYLE KEY
DISTKEY (customer_id);
 
-- ALL distribution: replicate the entire table to every node (for small dimension tables)
CREATE TABLE dim_region (region_id INT, region_name VARCHAR(50))
DISTSTYLE ALL;
 
-- EVEN distribution: spread evenly by round-robin (when the join key is unclear)
CREATE TABLE event_log (event_id BIGINT, payload VARCHAR(2000))
DISTSTYLE EVEN;

What happens during a join is the criterion for choosing a distribution style.

• If two tables have the same column as their DISTKEY, the rows to be joined are already on the same slice. This is a co-located join, in which no rows move across the network.
• If one side is ALL, every node already has a copy, so again there is nothing to move.
• Otherwise, just before the join, the rows of one table (or both) are redistributed among the slices by the join key. In EXPLAIN, DS_DIST_NONE and DS_DIST_ALL_NONE mean no redistribution, while DS_BCAST_INNER (the inner table is broadcast to all nodes) and DS_DIST_BOTH (both sides are redistributed) are signs of high cost.

A table has one column as its DISTKEY. For a fact table that joins to several tables, choose the key of the largest and most frequently joined partner, and set the remaining small dimensions to ALL.

• ALL is a better fit for a "slowly changing table" than for a "small table," because every write is repeated on each node. The official documentation also notes that for a very small dimension table the redistribution cost during a query is already low, so ALL gains little. On the exam, the pattern "small dimension table + eliminate join redistribution = ALL" is what gets asked.
• The work of AUTO changing the style happens in the background.

⚠️ Pitfall: Choosing a DISTKEY just because it is the join key causes skew. A column with only a few values (status, country) or with rows piled onto one value (a column that is mostly NULL, or a customer_id where one large customer accounts for half of the orders) makes a single slice heavy. If skew_rows in SVV_TABLE_INFO is large, change the key or switch to EVEN.

-- Check distribution style, sort key, skew, and unsorted ratio for each table
SELECT "table", diststyle, sortkey1, skew_rows, unsorted, stats_off
FROM svv_table_info
ORDER BY skew_rows DESC;

Sort Key

A sort key stores rows on disk in sorted order. Redshift holds column data in 1 MB blocks and keeps the minimum and maximum values of each block as metadata (the zone map). When a range condition is applied, blocks whose min–max range does not overlap the condition are skipped entirely.

CREATE TABLE sales (
  sale_date  DATE,
  region     VARCHAR(20),
  amount     DECIMAL(12,2)
)
DISTKEY (region)
COMPOUND SORTKEY (sale_date, region);

• If five years of data are sorted by date and you query one month, most blocks are never read. Without sorting, the values are scattered across blocks, the min–max ranges become wide, and there are no blocks left to skip.
• A zone map exists even without a sort key. What a sort key does is gather values together so that blocks can be skipped.

• If you set SORTKEY AUTO, Redshift looks at the query patterns and chooses the sort key. This is the default recommendation in the official documentation.
• A condition that wraps the sort key in a function (WHERE TRUNC(sale_ts) = ...) defeats block skipping. Compare the column as it is.
• Concurrency scaling cannot take queries on tables that have an INTERLEAVED sort key.

RA3, Managed Storage, and Redshift Serverless

• The core of RA3 is that "you do not have to add compute just because storage is full." Data sharing, zero-ETL targets, and concurrency scaling for write operations require RA3 or Serverless.
• The capacity unit of Serverless is the RPU (1 RPU = 16 GB of memory). The default base capacity is 128 RPUs, and it can be set from 4 RPUs. When there are no queries, no compute charge is incurred. You put a cap on cost with a maximum capacity and a maximum RPU-hours limit.
• The current pricing page lists a new node type called RG alongside RA3 as the current generation. Exam materials generally explain things as RA3 versus DC2.

Redshift Spectrum

You query files on S3 directly as external tables without loading the data into Redshift.

-- Spectrum external schema + join S3 data with an internal table
CREATE EXTERNAL SCHEMA spectrum_ext
FROM DATA CATALOG DATABASE 'datalake_db'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftSpectrumRole';
 
SELECT d.region, SUM(s.amount)
FROM spectrum_ext.raw_events s   -- External table on S3
JOIN dim_region d ON s.region_id = d.region_id  -- Internal Redshift table
WHERE s.dt = '2026-06-26'        -- Partition column of the external table → pruning
GROUP BY d.region;

• The definition of an external table lives in the Glue Data Catalog (or a Hive metastore). You use the same tables that Athena uses.
• Heavy work such as condition filtering and aggregation is pushed down to the Spectrum layer, which is separate from the cluster. It uses fewer cluster resources, and several clusters can query the same S3 data without copies.
• Billing is based on the bytes scanned (10 MB minimum per query). So, just as with Athena, partitioning, Parquet, and compression translate directly into cost.
• External tables are read-only. Changes such as UPDATE are not supported.
• The cluster and the bucket must be in the same Region.
• Keeping hot data in Redshift and large, older history on S3 + Spectrum, and combining them in a view, is the lakehouse pattern.

WLM, Concurrency Scaling, and SQA

Workload management (WLM) puts queries into queues and divides resources among the queues.

• The default queue of manual WLM starts with a concurrency of 5. When you increase the slots, memory per slot drops, and a query that used to finish in memory now writes intermediate results to disk and slows down. This is the cause behind "I raised concurrency and it got slower."
• A query is assigned to a queue by user group or by a query group label (SET query_group TO 'critical_dashboards';).
• Query monitoring rules (QMR): a query whose metrics, such as run time or rows scanned, exceed a threshold is logged or aborted, or, under manual WLM, moved to another queue (hop), or, under auto WLM, has its priority changed. It is a safeguard against runaway queries. The queue-level WLM timeout is no longer recommended and is replaced by QMR.
• SQA (Short Query Acceleration): handles queries expected to finish quickly ahead of long ones rather than lining them up behind them. It removes the need to create a separate queue just for short queries.

Concurrency scaling automatically attaches temporary clusters when queries pile up in a queue, and runs the waiting queries there.

• Turn on the concurrency scaling mode of the WLM queue as auto. The number of clusters that can be attached is set by max_concurrency_scaling_clusters (default 1).
• It supports not only read queries but also write operations such as COPY, INSERT, DELETE, UPDATE, and CTAS (writes on the RA3 family only).
• Billing covers only the time the temporary clusters actually ran queries, per second. For every day the main cluster is running, it accrues 1 hour of free credit, accumulating up to 30 hours. That is why a short peak that hits once a day is usually absorbed at no extra cost.
• Queries it cannot accept: queries on tables with an INTERLEAVED sort key, temporary tables, queries that use Python or Lambda UDFs, and writes to DISTSTYLE ALL tables.

Concurrency scaling is a model of "leave the storage and the main cluster as they are, and borrow only processing capacity for a short time." It fits short, periodic bursts such as a dashboard refresh every morning. If the queue is full all day long, that is not a burst but a capacity shortage, so you resize the cluster or fix the query and key design.

Loading: Using COPY Properly

COPY sales
FROM 's3://acme-datalake-curated/sales/2026/06/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftCopyRole'
FORMAT AS PARQUET;

• COPY is a parallel load in which the slices divide up and read the S3 files. Repeating single-row INSERTs is an anti-pattern.
• For splittable files (uncompressed CSV, Parquet, ORC), Redshift automatically splits them and reads them in parallel when they are 128 MB or larger.
• Files that cannot be split (gzip-compressed CSV, JSON, and so on) must be split by you. Make them similar in size, 1 MB to 1 GB after compression, and make the number of files a multiple of the number of slices. One large gzip file is read by a single slice alone, which makes the load serial.
• Listing the files to load explicitly in a manifest file avoids stray files that happen to share the prefix.
• When you COPY into an empty table for the first time, COMPUPDATE selects the compression encoding of each column automatically.
• The reverse direction is UNLOAD. It exports query results to S3 in parallel (Parquet and partitioning can be specified).

Maintenance and Other Optimizations

• UPDATE and DELETE do not remove rows immediately; they only mark them as deleted. If disk usage does not shrink and scans are slow after a large delete, it is because VACUUM has not run yet.
• When statistics are stale (a large stats_off), the optimizer picks the wrong join order and distribution method. Check the statistics after a bulk load.
• A materialized view can also be built on top of a Spectrum external table, which reduces the cost of scanning S3 every time. Streaming ingestion, which pulls directly from Kinesis Data Streams or MSK, is also implemented with a materialized view.

Symptom → Cause → Action

Question Clue → Answer Lookup Table

Tomorrow we cover Athena, which queries S3 with SQL without a cluster, and its cost structure.

📖 Glossary

• MPP: An architecture that splits data across several nodes and processes it at the same time. Massively Parallel Processing.
• Slice: The unit of parallel processing into which a compute node is divided. The rows of a table are stored per slice.
• Distribution key (DISTKEY): The column that decides which slice a row is placed on. Rows with the same value go to the same slice.
• Co-located join: A join in which the rows to be joined are already on the same slice, so it runs without redistribution.
• Skew: A state in which data is concentrated on some slices. That slice slows down the whole query.
• Zone map: The minimum and maximum values recorded for each block. It is used to skip blocks that do not overlap the condition.
• Redshift Managed Storage (RMS): The managed storage, separated from compute, that RA3 and Serverless use.
• RPU: The capacity unit of Redshift Serverless. You are billed for the RPU-hours used.
• WLM: The workload management feature that divides queries into queues and allocates concurrency and memory.
• External table: A table whose data is on S3 and only whose definition is in the catalog. Spectrum reads it.