Amazon Redshift: Warehouse SQL and Data Transformation

Intermediate⏱75 minutes📖36 min readLesson 4 of 16

Amazon Redshift: Warehouse SQL and Data Transformation

Take the load path from Lesson 3 all the way into the warehouse: how RA3 compute and Redshift Managed Storage split the bill, when Redshift Serverless, Spectrum, cross-database queries and data sharing beat making a copy, and exactly where COPY, UNLOAD and MERGE differ line by line, plus PL/pgSQL stored procedures, materialized views and their February 2026 refresh change, federated queries, distribution and sort keys worked through real join and skew examples, automatic VACUUM/ANALYZE and workload management (auto WLM, manual WLM, SQA), and a Redshift-versus-Athena decision matrix, with a sourced October 2026 update box, four AWS customer case studies and fourteen exam-style questions.

Lesson 3 ended with data landing in S3. This lesson is what happens next. Amazon Redshift is the service the DEA-C01 exam guide names in every domain: as a read source (skills 1.1.1, 1.1.2), as a transformation engine (1.2.5), as "SQL queries to transform data … for example, Amazon Redshift stored procedures" (1.4), as a migration/remote-access method through federated queries, materialized views and Spectrum (2.1.5), as the subject of load and unload operations (2.3.1), as a schema-design target (2.4.1), and as a permissions and data-sharing surface (4.2.3, 4.2.4, 4.5.1). That is why one lesson has to cover architecture, SQL, physical design and cost together: on this exam they are never asked in isolation.

plaintext

====================================================================
 THE REDSHIFT DECISION SURFACE (DEA-C01, all four domains)
====================================================================
  Compute ....... provisioned RA3 cluster (from $0.543/hour)
                  Redshift Serverless (from $1.50/hour, RPU-metered)
  Storage ........ Redshift Managed Storage: SSD cache + S3,
                   $0.024/GB-month - billed INDEPENDENTLY of compute
  Reaching data .. Spectrum (external tables, $5.00/TB scanned)
                  cross-database queries (one cluster, no copy)
                  data sharing (live, no copy, cross-account)
                  federated queries (read-only, RDS/Aurora)
  Load / unload .. COPY (+ MANIFEST, IAM_ROLE, compression)
                  UNLOAD (CSV/JSON/Parquet, PARTITION BY)
                  MERGE (+ REMOVE DUPLICATES)
  In-warehouse SQL .. stored procedures (PL/pgSQL), materialized
                   views, plain views, late-binding views
  Physical design . DISTSTYLE AUTO|EVEN|KEY|ALL
                  SORTKEY AUTO|COMPOUND|INTERLEAVED
  Autonomics ..... auto VACUUM delete/sort, auto ANALYZE, ATO
  Concurrency .... automatic or manual WLM + SQA + concurrency scaling
  Not this ....... ad-hoc one-off SQL over raw S3 = Amazon Athena
====================================================================

In this lesson you will:

read RA3 architecture: leader node, slices, and how Redshift Managed Storage bills separately from compute;
size and reason about Redshift Serverless: RPUs, base capacity, the 10× ceiling, workgroup versus namespace;
price Spectrum on bytes scanned and explain why columnar format, compression and partition pruning are cost controls, not just speed controls;
separate cross-database queries (inside one cluster) from data sharing (across clusters and accounts) and from federated queries (read-only into RDS/Aurora);
master COPY versus UNLOAD versus MERGE: manifests, IAM roles, COMPUPDATE, PARALLEL OFF and the exact MERGE restrictions;
write PL/pgSQL stored procedures to the exam's stated scope, including what a procedure may not contain;
choose between a plain view and a materialized view, and know what the 27 February 2026 auto-refresh change did;
pick distribution styles and sort keys with worked join, redistribution and skew examples you can compute by hand;
apply automatic VACUUM, ANALYZE and WLM (auto, manual and short query acceleration) with AWS's own thresholds;
decide Redshift or Athena from a decision matrix instead of from habit;
study four AWS customer case studies (GE Aerospace, PayU, Nasdaq and Amazon Customer Service) and a sourced October 2026 update box;
practise with 14 exam-style questions plus four interactive checks.

1. RA3 architecture and Redshift Managed Storage

1.1 The leader node, compute nodes and slices

AWS's architecture page states it plainly: "A compute node is partitioned into slices… The leader node manages distributing data to the slices and apportions the workload for any queries or other database operations to the slices." The leader node parses, plans, optimises and coordinates; it does not store table data. Compute nodes do the work, each partitioned into slices that run in parallel. Clients connect to the leader node, which is why RA3 clusters cap client connections at 2,000 (AWS Redshift limits, accessed Oct 2026).

💡 Tip: Use Ctrl + Mouse Wheel to zoom, or click the fullscreen icon

Ordering Question

How to answer

Ordering exercise with 5 items. Move them into the correct order, then check your answer.

Order the life of a Redshift query from the client's request to the returned rows:

Client sends SQL to the leader-node connection endpoint (RA3 allows up to 2,000 connections)

Leader node parses, plans and optimises the statement

Leader node apportions the work to the slices on the compute nodes

Slices execute in parallel, reading cold blocks from Amazon S3 through the Redshift Managed Storage SSD cache

Leader node collects the slice results and returns the answer to the client

1.2 RA3 node shapes and what a slice means for your SQL

RA3 size	vCPU	Memory	Slices
`ra3.large`	2	16 GiB	2
`ra3.xlplus`	4	32 GiB	2
`ra3.4xlarge`	12	96 GiB	4
`ra3.16xlarge`	48	384 GiB	16

Slice count matters for two examinable rules: COPY wants a file count that is a multiple of the slice count, and one table has exactly one distribution key, so collocation is evaluated per slice, not per node (details in Section 10).

1.3 Redshift Managed Storage: the bill splits in two

On RA3, local disk is a tier-1 SSD cache; the durable copy lives in Amazon S3, with blocks placed by temperature, age and workload. The exam consequence is arithmetic, not branding: compute and storage are metered independently; you can add or remove nodes without re-buying storage, and storage keeps accruing whether or not the cluster is busy.

Worked example E1: managed storage on the October 2026 rate card

Input	Value
Managed storage rate	$0.024 per GB-month (AWS Redshift pricing, as of Oct 2026)
Warehouse size	40 TB = $40 \times 1{,}024 = 40{,}960$ GB
Monthly storage cost	$40{,}960 \times 0.024 = $ \mathbf{983.04}$ / month

That $983.04 is charged on top of node hours, and it does not disappear when you pause compute. Answers that quote one blended "per TB" warehouse price are wrong on both mechanics and billing.

📚 Did you know? DC2 deprecation was announced in April 2025, pushing customers to RA3 or Serverless; which is why AWS's documented best practice for new workloads is RA3/RG, and why "just add another DC2 node" is a distractor (AWS Big Data Blog, DC2 migration approach, 2026-03-11). RA3 is not "local-only storage": separating compute from storage is the whole point of the generation.

1.4 Concurrency scaling: burst capacity with a free-hour budget

When a WLM queue runs with mode = auto, overflow queries are routed to transient clusters instead of queueing. Eligibility: EC2-VPC clusters of dc2/rg/ra3 created with 32 or fewer compute nodes; tables with interleaved sort keys and DISTSTYLE ALL targets are excluded from write scaling, and write scaling itself exists only on RA3/RG. max_concurrency_scaling_clusters defaults to 1 inside a quota of 10.

Worked example E2: AWS's own burst bill (as of Oct 2026)

plaintext

Cluster: 10 x rg.4xlarge in US-West-1          $33.66 / hour
Transient rate:  33.66 / 3600                  $0.00935 / second
Free credit:     1 hour per 24 h per active cluster,
                 bankable to 30 hours
Burst used:      2 clusters x 300 seconds beyond credit
                 0.00935 x 300 x 2             = $5.61
Hour total:      $33.66 + $5.61                = $39.27

Drill worth memorising: one credited hour spread over 4 simultaneous transient clusters buys only $60 \div 4 = 15$ minutes of free burst each: credits are consumed in real time, not split as whole hours.

2. Redshift Serverless

2.1 Capacity is measured in RPUs

Serverless capacity is expressed in RPU-hours, and AWS defines 1 RPU = 16 GB of memory. There is no cluster to size; instead you set a base capacity and a price-performance target (default Balanced, an AI-driven scaling mode), and Redshift scales compute for the workload.

Serverless parameter	Value (AWS docs, accessed Oct 2026)
Unit	1 RPU = 16 GB memory
Base capacity	default 128; configurable 4 to 512 in steps of 8; up to 1024 in supported Regions
Scaling ceiling	up to 10× base capacity
MaxRPU (per workgroup hard ceiling)	top value 5,632
Billing	per second, 60-second minimum
Included in the RPU-hour	Spectrum and concurrency scaling
Entry price	from $1.50/hour (as of Oct 2026)

Worked example E3: what "base capacity" buys you

plaintext

Base capacity           128 RPU  x 16 GB  =  2,048 GB memory
Scale ceiling  10 x 128          = 1,280 RPU x 16 GB = 20,480 GB
MaxRPU workgroup ceiling         = 5,632 RPU (the hard stop)

AWS's own sizing guidance (re:Post, 2026-06-11): 4 RPU for under 32 TB; 8/16/24 RPU for under 128 TB; 32 RPU or more above 128 TB. And because billing is per second with a 60-second minimum, a two-second query still costs a full minute of RPU time: serverless does not mean free-when-idle, it means no idle at all between queries.

2.2 Workgroup is compute; namespace is identity

Object	What it owns	Exam sentence
Workgroup	Compute boundary: capacity settings, VPC, price-performance target	"Which capacity setting do I change?" → workgroup
Namespace	Database identity: schemas, users, encryption keys; identified by a GUID	"Which database objects are shared?" → namespace

This distinction returns in Section 5: a datashare is bound to one source database inside a namespace, and the consumer command quotes that GUID.

3. Amazon Redshift Spectrum

3.1 External tables: query S3 without loading it

Spectrum lets a Redshift cluster create external tables over data that stays in Amazon S3, with metadata registered in the AWS Glue Data Catalog, and it is billed on bytes scanned: not rows returned, not bytes stored.

Spectrum fact	Value (AWS pricing, as of Oct 2026)
Rate (us-east-1)	$5.00 per TB scanned, rounded to MB, 10 MB minimum per query
Free of charge	DDL statements and failed queries
Still billed	Cancelled queries, for the bytes already read
On Serverless	No separate Spectrum charge: included in the RPU-hour
On RG instances	Not required: RG includes a built-in data lake query engine

3.2 Worked example E4: three ways to make one Spectrum query cheaper

One table of 100 columns; the query reads one column:

Layout of the same data	Bytes scanned	Spectrum cost
Uncompressed text	4 TB	$4 \times 5.00 = \mathbf{\$ 20.00}$
GZIP at 4:1	1 TB	$1 \times 5.00 = \mathbf{\$ 5.00}$
GZIP + Parquet, one column read	0.25 TB	$0.25 \times 5.00 = \mathbf{\$ 0.05}$

Three levers, three different mechanisms: compression shrinks every byte, columnar format lets the engine skip columns it does not need, and partition pruning skips whole files. A fourth lever is file hygiene: AWS recommends external files of 64 MB or larger, and setting TABLE PROPERTIES (numRows = …) because Redshift never runs ANALYZE on external tables and otherwise assumes an external table is the largest table in the join.

📌Important

Spectrum bills what you scan, not what you return. SELECT * over wide, uncompressed CSV pays for every column of every row read. The Athena price in Section 12 is the same $5.00/TB scanned with a 10 MB minimum: the two services meter identically, which is exactly why the choice between them is architectural, not financial (as of Oct 2026).

4. Cross-database queries

A single Redshift cluster can host several databases. Cross-database queries let you read and write across them using the three-part name database.schema.object (or an external-schema alias): with no data copies, against a transactionally consistent snapshot. They are supported on RG, RA3 and Serverless.

sql

-- Connected to database "analytics"; two other databases, zero copies
SELECT s.order_id,
       e.event_ts
FROM   analytics.sales          s
JOIN   staging.raw_events     e USING (order_id)
WHERE  s.order_date >= '2026-10-01'
AND    e.source = 'mobile-app';

-- Same cluster, one database away: three-part name is database.schema.table
SELECT count(*) FROM otherdb.reporting.weekly_rollup;

Supported	Not supported (exam limits)
Read/write across databases in one cluster	Column-level privilege tables from another database
Views on other databases: late-binding views and materialized views only	Regular views on other-database objects
External schemas as a shorthand alias	Writes into the external schema of a database you are not connected to
RG, RA3, Serverless	Interleaved sort keys on the remote object
:	Three-part `information_schema` / `pg_catalog` references

📚 Did you know? The two most-confused features on this exam differ by one word and one boundary: cross-database stays inside a single cluster, while data sharing crosses clusters, accounts and Regions. Neither one copies data, but only data sharing needs a NAMESPACE '<guid>' GUID, an explicit authorize/associate handshake and two encrypted clusters.

A datashare is a bundle of objects + permissions + consumers, bound to one source database. The consumer creates a database from it:

sql

-- Consumer side: local (same account) or remote (other account)
CREATE DATABASE consumer_sales
FROM DATASHARE orders_share
OF NAMESPACE '1a2b3c4d-5e6f-7a8b-9c0d-1e2f3a4b5c6d';

💡 Tip: Use Ctrl + Mouse Wheel to zoom, or click the fullscreen icon

Rule	Detail (AWS data-sharing docs, accessed Oct 2026)
Consistency	Live and transactionally consistent: no copy, no refresh lag
Writes	Read-only by default; writes require explicit grants (`WITH PERMISSIONS` gives per-object grants, otherwise `USAGE` implies every object)
Chaining	Not allowed: a datashare cannot be shared on from the consumer
Views	Only late-binding views and materialized views over shared objects
Joins to local data	Use cross-database queries (Section 4)
What is shareable	Databases, schemas, tables, views, UDFs

Two things are deliberately absent from that table: AWS does not document a price for data sharing, and it does not document Region availability for it, so neither "it is free" nor "it costs X" belongs in an exam answer (see Section 17 of the digest: unverified).

6. COPY, UNLOAD and MERGE: the three load-path verbs

6.1 COPY: the default answer for volume

AWS's rule of thumb: load bulk rows with COPY; use SQL inserts only for small changes, and if you must insert in SQL, use a multi-row insert whenever possible, because "data compression is inefficient when you add data only one row or a few rows at a time."

Worked example E5: COPY with a manifest and an IAM role (the sketch to memorise)

sql

COPY public.order_items
FROM 's3://acme-orders-prod/2026/10/orders.manifest'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftCopyRole'
MANIFEST
FORMAT AS CSV
GZIP
COMPUPDATE ON
MAXERROR 100
TRUNCATECOLUMNS;

The manifest it points at is a single JSON file, not a prefix:

json

{"entries":[
  {"url":"s3://acme-orders-prod/2026/10/part-0001.gz","mandatory":true},
  {"url":"s3://acme-orders-prod/2026/10/part-0002.gz","mandatory":false}
]}

COPY rule	Value / behaviour
Authorization	`IAM_ROLE 'arn:…'` or `IAM_ROLE 'default'` (role pre-attached to the cluster); access keys are discouraged and mutually exclusive with `IAM_ROLE`
Manifest	Optional `{"entries":[{url, mandatory}]}`; you must add the `MANIFEST` keyword: omit it and COPY parses your JSON as data and fails
Duplicate manifest entry	The file loads twice
File sizing	Count = a multiple of slice count, each file 1 MB: 1 GB compressed
`COMPUPDATE` default	Samples only when the target table is empty and no encoding is declared
`COMPUPDATE ON / PRESET / OFF`	sample and maybe replace / assign by type without sampling / never
`COMPROWS`	100,000 rows per slice sampled by default (minimum 1,000)

⚠️Warning

COMPUPDATE silently does nothing on a populated table. Re-running COPY into a table that already has rows will not re-derive encodings under the default setting. If the exam says "re-derive compression on an existing table", the answer is an explicit COMPUPDATE ON/PRESET, or, better, let automatic table optimization own it (Section 11).

6.2 UNLOAD: export, with format and file-shape control

sql

UNLOAD ('SELECT order_id, amount, updated_at
         FROM sales_fact
         WHERE order_date >= ''2026-10-01''
         ORDER BY order_date')
TO 's3://acme-curated/sales_fact/'
IAM_ROLE 'default'
FORMAT AS PARQUET
PARTITION BY (order_date)
PARALLEL OFF
ALLOWOVERWRITE;

UNLOAD clause	Why it is examinable
`FORMAT CSV \| JSON \| PARQUET`	Parquet unloads up to 2× faster and uses up to 6× less storage than text (AWS)
`PARTITION BY`	Writes Hive-style partitions so Athena/Spectrum can prune
`PARALLEL ON` (default)	One or more files per slice
`PARALLEL OFF`	Preserves `ORDER BY`: the only mode that does
`MAXFILESIZE`	6.2 GB ceiling (range 5 MB: 6.2 GB)
`MANIFEST [VERBOSE]`	Emits a manifest so a later `COPY … MANIFEST` can round-trip it exactly
`ALLOWOVERWRITE`, `ENCRYPTED`	Idempotent reruns; server-side encryption on write

6.3 MERGE: the idempotent upsert

sql

MERGE INTO sales_fact t
USING sales_staging s
   ON t.order_id = s.order_id
WHEN MATCHED THEN
     UPDATE SET amount = s.amount, updated_at = s.updated_at
WHEN NOT MATCHED THEN
     INSERT (order_id, amount, updated_at)
     VALUES (s.order_id, s.amount, s.updated_at);

MERGE restriction (AWS `MERGE` reference)	Consequence
Target cannot be a system, catalog or external table	You cannot MERGE straight onto a Spectrum table
Source ≠ target, and no `WITH` clause	Stage first, then merge
A target row must not match multiple source rows	Deduplicate the staging table, or use `MERGE … WHEN MATCHED THEN REMOVE DUPLICATES` for pure dedupe on identical schemas
Required privileges	`SELECT` on both sides plus `INSERT`/`UPDATE`/`DELETE` on the target
Manual alternative	Staged `UPDATE`/`DELETE`/`INSERT` must run in one transaction

Fill in the Blanks

How to answer

Fill-in-the-blank exercise with 5 blanks. Type the missing words, then check your answer.

Complete the load, unload and upsert statements with the correct Redshift keywords:

A 500-million-row daily drop in S3 is loaded with blank, whose JSON file list requires the blank keyword and is mutually exclusive with access keys when the blank clause is used. Exporting an ordered result set to a single file requires blank OFF, and the idempotent upsert statement is blank.

Word Bank, drag words into the blanks above:

COPY

MANIFEST

IAM_ROLE

PARALLEL

MERGE

INSERT

UNLOAD

LOAD

FORMAT

SPLIT

PARTITION BY

UPSERT

REPLACE

MERGE INTO DEDUP

Worked example E6: the copy-vs-insert arithmetic

plaintext

Table: 8 slices, target 500,000,000 rows from S3
COPY:   file count = 8 x k   (k files per slice), 1 MB-1 GB each
        -> one statement, parallel across slices, auto ANALYZE on an
           empty target, compression derived by COMPUPDATE
INSERT: 1 row per statement = 500,000,000 statements
        -> compression cannot be derived incrementally, and AWS says
           compression is "inefficient when you add data only one row
           or a few rows at a time"

7. Stored procedures (PL/pgSQL): the exam scope

sql

CREATE OR REPLACE PROCEDURE stg.load_orders()
LANGUAGE plpgsql AS $$
DECLARE
  v_rows bigint;
BEGIN
  COPY stg.orders_raw FROM 's3://acme-orders-prod/today/'
       IAM_ROLE 'default' GZIP;
  SELECT count(*) INTO v_rows FROM stg.orders_raw;
  IF v_rows = 0 THEN
    RAISE INFO 'no rows today - skipping';
  ELSE
    TRUNCATE stg.orders_clean;
    INSERT INTO stg.orders_clean
      SELECT * FROM stg.orders_raw WHERE valid;
    CALL stg.merge_orders();   -- nested CALL, max 16 levels
  END IF;
END $$;

CALL stg.load_orders();

Procedure fact	Detail (AWS stored-procedure docs)
Invocation	`CALL name(...)`; created with `CREATE [OR REPLACE] PROCEDURE … LANGUAGE plpgsql`
Versus a UDF	A procedure may contain DDL and DML (`COPY`, `UNLOAD`, `INSERT`, `CREATE TABLE`) and need not return a value
`DECLARE / BEGIN / END`	Grouping, not a transaction: a `BEGIN…END` block does not open a transaction
Default transaction	One implicit transaction per `CALL`; `COMMIT`/`ROLLBACK`/`TRUNCATE` are legal only outside a caller transaction block
`NONATOMIC`	Auto-commits per statement; a cursor loop then needs an explicit `START TRANSACTION` first
Body features	`IF/ELSIF/ELSE`, loops, `EXIT`, `EXECUTE` (dynamic SQL), `RAISE`, `refcursor` cursors
Limits	16 nested `CALL` levels; one open cursor per session; no subtransactions
Forbidden inside	`PREPARE`, `CREATE/DROP DATABASE`, `CREATE EXTERNAL TABLE`, `VACUUM`, `SET LOCAL`, `ALTER TABLE APPEND`

The exam framing is conditional pipeline logic: branch on a row count, TRUNCATE + INSERT…SELECT, then let Step Functions or EventBridge invoke the whole thing through the Redshift Data API. "Run VACUUM inside the procedure" is never the answer: maintenance is orchestrated outside, in Section 11.

📚 Did you know? A procedure's BEGIN/END and a transaction's BEGIN are different animals with the same keyword. That is why the documentation warns that grouping blocks are not transactions: a failed statement inside a CALL rolls back the whole implicit transaction, not just the block, unless the procedure was declared NONATOMIC.

8. Materialized views versus views

	View	Materialized view
What is stored	Only the SQL	The query results
Freshness	Always current: nothing to refresh	Stale until refreshed
Read speed	Re-executes the query	Reads precomputed rows
Cost	No storage, no refresh job	Storage plus refresh compute
Exam sentence	"Must always reflect the latest base-table change"	"Repeated expensive aggregation over a stable base"

8.1 Refresh mechanics

Refresh is either manual (REFRESH MATERIALIZED VIEW) or automatic (AUTO REFRESH YES: default is NO), and Redshift chooses incremental or full recomputation. A full recompute is forced after a manual VACUUM, a classic resize, ALTER DISTKEY, ALTER SORTKEY or a TRUNCATE.

💡 Tip: Use Ctrl + Mouse Wheel to zoom, or click the fullscreen icon

8.2 Automatic rewriting and AutoMV

Automatic query rewriting uses only fresh materialized views, and only for eligible SELECTs: no subqueries, outer joins, set operations, DISTINCT/window/HAVING, aggregates beyond SUM/COUNT/MIN/MAX/AVG, and no external or shared tables. The session kill switch is SET mv_enable_aqmv_for_session = FALSE. AutoMV creates materialized views from observed workload patterns (they appear as %_auto_mv_% inside EXPLAIN) under the same eligibility limits.

sql

CREATE MATERIALIZED VIEW mv_sales_by_region
AUTO REFRESH YES
AS
SELECT region_id, date_trunc('month', order_date) AS m, sum(amount) AS total
FROM   sales_fact
GROUP  BY 1, 2;

REFRESH MATERIALIZED VIEW mv_sales_by_region;

⚠️Warning

Refresh is not free, and it got louder in 2026. "Starting February 27, 2026, Auto REFRESH queries for Amazon Redshift materialized views are executed as user queries rather than background autonomic processes" (provisioned clusters on the CURRENT track, P198+; disabled on Serverless). A refresh therefore competes for WLM slots at user priority; which is precisely the interaction this lesson's Sections 8 and 11 are testing together.

9. Federated queries and the query surface around them

Federated queries let the Redshift leader node pull data from a relational source without moving it first:

Fact	Value (AWS federated-query docs)
Sources	Amazon RDS / Amazon Aurora PostgreSQL ≥ 9.6 and MySQL ≥ 5.6
Ports	5432 (PostgreSQL), 3306 (MySQL)
Direction	Read-only
Credentials	AWS Secrets Manager secret plus an IAM role
Concurrency scaling	Not available for federated queries
Exam use	Skill 2.1.5: "implement data migration or remote access methods (for example, Amazon Redshift federated queries, materialized views, Spectrum)"

The four "reach out" features now line up as one family, and the exam tests the differences:

Feature	Copies data?	Direction	Boundary
Cross-database queries	No	Read/write	One cluster, several databases
Data sharing	No	Read (write with explicit grants)	Clusters, accounts, Regions
Spectrum	No	Read	Redshift → S3 via Glue Data Catalog
Federated queries	No	Read only	Redshift → RDS / Aurora PostgreSQL or MySQL

Around that, Query editor v2 is the analyst surface: concurrent tabs and notebooks, session variables, temporary tables, ${param} placeholders, history for the last 1,000 queries, and scheduled queries driven by EventBridge + the Redshift Data API.

10. Distribution styles and sort keys: where the join cost is decided

10.1 Distribution styles

DISTSTYLE accepts AUTO | EVEN | KEY | ALL, with AUTO the default. AUTO starts small tables as ALL, moves them to KEY, and may settle on EVEN, adjusting in the background, and automatic table optimization keeps making those adjustments unless you pin an explicit style.

Style	What it does	Cost / trap
KEY	Collocates rows by the value of one distribution column: at most one distkey per table	The join column must match, or rows still redistribute
EVEN	Round-robin across slices	No collocation; fine for tables that are never joined
ALL	A full copy of the table on every node	Storage × node count; slower loads; excluded from concurrency-scaling writes
AUTO (default)	Background migration ALL → KEY → maybe EVEN	Explicit keys remove the table from automation

AWS's star-schema guidance: the distribution key should be the largest dimension in the most common join; use ALL for dimensions that cannot be collocated and whose redistribution cost exceeds the storage cost; AUTO everywhere else. And AWS's own sentence for why KEY works: "If you distribute a pair of tables on the joining keys, the leader node collocates the rows on the slices according to the values of the joining columns."

10.2 Sort keys

Sort key type	Cap	Notes
COMPOUND	400 columns	Leading-column prefix ranges; the classic choice
INTERLEAVED	8 columns	Equal-weight across columns; higher load and VACUUM cost, needs `REINDEX` (`interleaved_skew > 1.4`), blocks concurrency scaling, unsupported in cross-database queries
AUTO (default)	:	Lets automatic table optimization pick and revise the sort key

Put the join column as both sort key and distribution key and Redshift can choose a sort-merge join instead of a hash join: no hash build, no spill, no network motion.

Worked example E7: choose the distribution style for a star schema

plaintext

fact_orders   800 GB   joins dim_customer on customer_id in 90% of queries
dim_customer   40 GB   the largest dimension in that join
dim_date       10 MB   joins too, but tiny
fact_events    -       denormalized, never joined

💡 Tip: Use Ctrl + Mouse Wheel to zoom, or click the fullscreen icon

Worked example E8: reading the plan, with AWS's own numbers

sql

-- Skew and sort triage before you change anything
SELECT "table", diststyle, skew_rows, unsorted, stats_off, vacuum_sort_benefit
FROM   svv_table_info
ORDER  BY skew_rows DESC NULLS LAST;

plaintext

skew_rows        >= 4.00  -> change the DIST STYLE
unsorted         >  20 %  -> consider VACUUM, but read vacuum_sort_benefit
                              first (86% unsorted can still be 5% improvable)
stats_off        high     -> ANALYZE ... PREDICATE COLUMNS
interleaved_skew >  1.4   -> VACUUM REINDEX (slow, interleaved only)

EXPLAIN operator tags:
  DS_DIST_NONE / DS_DIST_ALL_NONE   good - no motion
  DS_BCAST_INNER                    fine only when the inner side is small
  DS_DIST_INNER / DS_DIST_OUTER     costly - one side is being moved
  DS_DIST_ALL_INNER                 serialises onto one slice
  DS_DIST_BOTH                      worst case
  Nested Loop                       usually a MISSING join predicate
  Hash Join                         can spill to disk
  Merge Join                        fastest - both sides dist + sorted on the keys

AWS published the effect of moving dimensions to DISTSTYLE ALL on one real plan: join cost fell from 3,272,334,142.59 to 14,142.59 (Amazon Redshift Prescriptive Guidance, accessed Oct 2026). The lesson is not "always use ALL"; it is that the plan, not the rule of thumb, is the arbiter.

Matching Question

How to answer

Matching exercise with 6 pairs. Match each item on the left with its answer on the right.

Match each Redshift physical-design situation to the correct choice:

800 GB fact joined to a 40 GB dimension on 90% of queries

Drop here

A wide fact table that is only ever aggregated, never joined

Drop here

A tiny dimension that cannot share the fact's distribution key

Drop here

Three different equality predicates queried with equal frequency

Drop here

A table scanned by leading-column date ranges

Drop here

A table queried by any of 4 to 8 independent columns

Drop here

Drag answers from here:

DISTSTYLE KEY on the join column: collocate both sides so the join stays local

DISTSTYLE EVEN: round-robin, there is nothing to collocate

DISTSTYLE ALL: full copy per node, watch storage x node count

DISTSTYLE AUTO / SORTKEY AUTO: let automatic table optimization revise it

COMPOUND sort key: up to 400 columns, prefix ranges, cheap to maintain

INTERLEAVED sort key: max 8 columns, but heavier VACUUM and no concurrency-scaling writes

Matching Question

Match each Redshift physical-design situation to the correct choice:

A800 GB fact joined to a 40 GB dimension on 90% of queries → ______

BA wide fact table that is only ever aggregated, never joined → ______

CA tiny dimension that cannot share the fact's distribution key → ______

DThree different equality predicates queried with equal frequency → ______

EA table scanned by leading-column date ranges → ______

FA table queried by any of 4 to 8 independent columns → ______

Drag answers from here: COMPOUND sort key: up to 400 columns, prefix ranges, cheap to maintain · DISTSTYLE ALL: full copy per node, watch storage x node count · DISTSTYLE AUTO / SORTKEY AUTO: let automatic table optimization revise it · DISTSTYLE EVEN: round-robin, there is nothing to collocate · DISTSTYLE KEY on the join column: collocate both sides so the join stays local · INTERLEAVED sort key: max 8 columns, but heavier VACUUM and no concurrency-scaling writes

Answer: 800 GB fact joined to a 40 GB dimension on 90% of queries → DISTSTYLE KEY on the join column: collocate both sides so the join stays local · A wide fact table that is only ever aggregated, never joined → DISTSTYLE EVEN: round-robin, there is nothing to collocate · A tiny dimension that cannot share the fact's distribution key → DISTSTYLE ALL: full copy per node, watch storage x node count · Three different equality predicates queried with equal frequency → DISTSTYLE AUTO / SORTKEY AUTO: let automatic table optimization revise it · A table scanned by leading-column date ranges → COMPOUND sort key: up to 400 columns, prefix ranges, cheap to maintain · A table queried by any of 4 to 8 independent columns → INTERLEAVED sort key: max 8 columns, but heavier VACUUM and no concurrency-scaling writes

Explanation: The star-schema rule is: distribution key = the largest dimension in the most common join; ALL only when redistribution costs more than the replicated storage; EVEN when there is no join. On sort keys, COMPOUND is the 400-column default-friendly choice, while INTERLEAVED is capped at 8 columns, requires VACUUM REINDEX when interleaved_skew exceeds 1.4, blocks concurrency scaling and is unsupported in cross-database queries.

11. Automatic maintenance and workload management

11.1 VACUUM, ANALYZE and the autonomics that replaced them

Redshift's autonomics are four background behaviours: automatic vacuum delete, automatic vacuum sort, automatic analyze and automatic table optimization (plus automated materialized views). They run in light-load windows and pause under high load or exclusive DDL locks; which is why "why didn't my VACUUM run?" is a scheduling question, not a permissions question.

Control	Default / threshold (AWS docs, accessed Oct 2026)
Automatic analyze	On by default; skips tables whose stats are current; `analyze_threshold_percent` = 10
Auto-analyse triggers	`COPY` into an empty target; `CREATE TABLE AS`
Automatic vacuum delete/sort	On, background, paused by load or locks
Manual `VACUUM` (default `FULL`)	Skips sorting below 95% unsorted: do not teach "VACUUM after every load"
`VACUUM REINDEX`	Interleaved only; much slower
Monitor	`unsorted` and `vacuum_sort_benefit` in `SVV_TABLE_INFO`
ATO	Applies sort/dist keys to `AUTO` tables within hours of enough queries; audit `SVV_ALTER_TABLE_RECOMMENDATIONS`

11.2 Workload management: three mechanisms, not one

WLM configuration lives in the parameter group (wlm_json_configuration), and AWS's hard rule is: never mix automatic and manual queues in one parameter group.

Mechanism	What it controls	Key numbers
Automatic WLM (default, recommended)	Redshift assigns concurrency and memory itself	Up to 8 queues (service classes 100 to 107); adds query priority and Query Monitoring Rules
Manual WLM	You set static memory % and concurrency per queue and route by user group	Concurrency 1 to 50 per queue, 50 slots total, AWS recommends ≤ 15
Short query acceleration (SQA)	An ML model predicts runtime; short queries skip the queue	Dynamic (0) or 1 to 20 seconds; on by default; service class 14; only CTAS and read-only queries are eligible
Query priority	Relative importance of a query within automatic WLM	`priority` exists only in auto WLM
Concurrency scaling	Overflow to transient clusters	Per queue with mode = auto; 1 free hour per 24 h

Default state of a fresh cluster: 1 queue with 5 concurrent queries. max_execution_time is deprecated: the modern equivalent is a QMR on query_execution_time, evaluated every 10 seconds, with severities log < hop < abort < change priority (note: abort never stops COPY, ALTER, ANALYZE or VACUUM, and change priority exists only in auto WLM).

💡 Tip: Use Ctrl + Mouse Wheel to zoom, or click the fullscreen icon

📚 Did you know? SQA and automatic WLM are frequently conflated and are different mechanisms: SQA (class 14) predicts runtime to short-circuit the queue, while automatic WLM (classes 100 to 107) decides slots and memory. Both may be enabled together, and neither is concurrency scaling, which adds whole clusters. Three layers, three names, one stem.

12. Redshift or Athena? The decision matrix

AWS's own framing: Amazon Athena is for "interactive ad hoc SQL queries against data on Amazon S3, without … infrastructure or clusters", while Redshift is "optimized … on complex queries that join large numbers of very large database tables". The teaching rule that follows is short: Athena = ad-hoc SQL over files in S3; Redshift = BI over a curated warehouse.

Signal in the question	Exam answer	Basis
Complex joins across many large curated tables; BI dashboards; repeated concurrent reports	Redshift	Warehouse-optimised for large joins
One-off, infrequent SQL over files already in S3; no infrastructure wanted	Athena	No cluster, no nodes
"Pay only when querying"	Athena: $5.00/TB scanned	Redshift bills node-hours or RPU-hours whether or not anyone queries
Keep data in S3 but still need warehouse joins and concurrency	Redshift Spectrum external tables	Same cluster, no load
Cheap long-term raw plus a fast curated layer	Both: `UNLOAD` Parquet to S3, Spectrum for cold, warehouse for hot	Lake house pattern
Log/event, semi-structured, schema-on-read exploration	Athena	Schema-on-read fit

💡 Tip: Use Ctrl + Mouse Wheel to zoom, or click the fullscreen icon

📌Important

Do not invent a crossover point. Plenty of third-party articles claim "Redshift becomes cheaper than Athena above X TB per month": AWS publishes no such number, so no exam answer should contain one (unverified, see the digest's section G). What AWS does publish is the metering shape: per-scan for Athena versus always-on node-hours / RPU-hours for Redshift, plus Spectrum's $5.00/TB scanned for S3 data queried from Redshift (as of Oct 2026).

📚 Did you know? The Athena half of this matrix moved too: managed query results (2025-06-03) are service-managed, encrypted and cost nothing extra, so no S3 result bucket is required any more, and Capacity Reservations now start at 4 DPU for 1 minute instead of 24 DPU for 60 minutes (2026-02-11). Both "Athena needs a result bucket in S3" and "Athena reservations begin at 24 DPU" are stale facts that still appear in live third-party material (AWS What's New, 2025-06-03 / 2026-02-11).

2026 Updates (as of October 2026)

ℹ️Note

What changed in Redshift between 2025 and October 2026: every line checked against a primary AWS source, and each one is examinable because the exam tests current behaviour:

Redshift can now write to Iceberg, not just read it: table creation/INSERT went GA 2025-11-17 (append-only, over the Glue Data Catalog), JIT ANALYZE followed on 2025-11-18, UPDATE/DELETE/MERGE on Iceberg tables (including Amazon S3 Tables) arrived 2026-04-23, and Iceberg materialized views on 2026-10-05: AWS What's New, 2025-11-17 / 2025-11-18 / 2026-04-23 / 2026-10-05.
Iceberg DML permissions were tightened (Patch 202): INSERT needs INSERT; DELETE needs DELETE; UPDATE/MERGE need INSERT + DELETE; and all Iceberg DML also needs ALTER: a grant of INSERT alone is no longer sufficient (Redshift behaviour-changes page, accessed Oct 2026).
Scalar Python UDFs are done: no new ones after 2025-10-30, unsupported after 2026-06-30: do not design new pipeline logic on them (Redshift documentation banner, accessed Oct 2026).
Serverless now has a commitment path: 1-year reservations at 20%/24% and 3-year at up to 45% (2026-02-23), plus 3-year all-upfront-upgrade reservations at up to 50% (2026-07-24), against an entry price of from $1.50/hour and a 4 RPU minimum: AWS What's New, 2026-02-23 and 2026-07-24; Redshift pricing, as of Oct 2026.
Materialized-view auto refresh changed on 2026-02-27: auto-refresh queries now run as user queries rather than background autonomic processes on provisioned CURRENT-track clusters (P198+), and remain disabled on Serverless: Redshift documentation, accessed Oct 2026.
Two dated behaviour changes are still landing: enhanced billing for Serverless and Redshift-managed snapshots from 2026-06-08, and a minimum TLS version change on 2026-10-31: Redshift behaviour-changes page, accessed Oct 2026. Certificate-handshake and snapshot-cost questions now have dates attached to them.
The shared Iceberg table got a third writer: Amazon S3 Tables added the Variant type for Iceberg v3 (2026-07-28), and since 2026-04-23 Redshift's UPDATE/DELETE/MERGE run over S3 Tables too, so one table can be written by Glue, updated by Redshift and read by Athena without a copy, AWS What's New, 2026-07-28 and 2026-04-23.
Iceberg format v3 is now mainstream in the AWS lake stack: AWS Glue 6.0 (2026-08-21) ships full Apache Iceberg v3 support, VARIANT + shredding, deletion vectors and row lineage, with a 30% price reduction, so "open table format" questions may assume v3 features (AWS What's New and AWS Big Data Blog, 2026-08-21).
Exam guide context: DEA-C01 guide v1.1 (2025-12-12) added skill 2.1.7 "Manage open table formats (for example Apache Iceberg)" with no removals, which is why Redshift-on-Iceberg is squarely in scope: DEA-C01 revisions page, 2025-12-12.

📚 Did you know? Two "stale fact" traps sit in the same topic: a widely quoted 2020 APN post claims Redshift "doesn't actually support materialized views" (false since 2021), and any option claiming Redshift can only read Iceberg has been wrong since 2025-11-17. On this exam, an old AWS post is not a source: the current documentation page is.

Real-World Case Studies

AWS publishes what these abstractions look like in production. Every figure below is customer- or AWS-claimed and unaudited, with the source named so you can check it: the examinable point is the pattern, not the marketing.

Case A, GE Aerospace: a legacy operational data store, rewritten on Redshift

Element	Detail
Customer	GE Aerospace, aerospace and supply-chain operations
Challenge	A large operational data store (ODS) with compliance and performance constraints, plus 150+ reports (some over 10,000 lines of code)
Services	Amazon Redshift, with AWS Solutions Architecture and Redshift engineering support
Outcomes	~70% better query performance; estimated over $500,000 per year; proof of concept Nov, Dec 2022, then a 9-month migration January, September 2023; reports that ran 90 minutes now run in 7 minutes
Exam domain	Domain 2 (store design and load/unload) with Domain 3 hooks (performance, cost)
Source	`aws.amazon.com/solutions/case-studies/ge-aerospace-case-study` (accessed Oct 2026)

"We were seeing queries that used to run for an hour and a half running in 7 minutes using Amazon Redshift.": Bejoy John, Senior Director of Data Analytics, GE Aerospace (AWS case study, accessed Oct 2026)

The examinable pattern: this was not a lift-and-shift. It was a nine-month programme with a formal PoC, and the win came from re-doing the physical design of a warehouse; which is exactly Sections 10 and 11 of this lesson (distribution, sort keys, maintenance, WLM), not from adding nodes.

Element	Detail
Customer	PayU, fintech/payments company
Challenge	Data siloed across ~40 production databases; queries taking 10 to 15 minutes; data freshness only once a day
Services	AWS Glue ETL → Amazon S3 → Amazon Redshift, plus Redshift Data Sharing across 5 clusters (2 ETL + 3 consumer)
Outcomes	Queries 10 to 15 min → under 1 min; freshness once/day → under 30 minutes (ML streaming under 5 s); $20,000/month saved; ~200 TB scanned per day; query volume 150,000 → 35,000 per month (−77%)
Exam domain	Domain 1 (transformation feeding the load) plus Domain 4 skills 4.5.1 (permissions for data sharing)
Source	`aws.amazon.com/solutions/case-studies/payu-redshift-case-study` (accessed Oct 2026)

"In 1 month, we cut down queries by 77 percent, which would have been a 6-month exercise in the previous environment.": PayU, AWS case study (accessed Oct 2026)

Read PayU as two independent levers: the architecture (Glue → S3 → Redshift, then data sharing so five clusters read one source without copies) and the demand rationalisation (−77% of queries). Answers that collapse both into "AWS cut costs by 77%" misread the number; the $20,000/month is the savings claim, the 77% is query count.

Case C, Nasdaq: a lake house with Redshift on the read path

Element	Detail
Customer	Nasdaq, stock exchange
Challenge	An overnight batch of orders, quotes and trades that must be queryable before the market opens, after a 2014 move off a legacy on-premises warehouse
Services	Amazon S3 data lake + Amazon Redshift + Redshift Spectrum, plus Amazon S3 Glacier archive and S3 Object Lock
Outcomes	The jump from 30 billion to 70 billion records a day (peak 113 billion, February 2020); 90% of the load available 5 hours sooner; queries 32% faster; a 15 TB lake queried in place
Exam domain	Domain 2 (store design and load/unload) with Domain 1 load-path hooks and Domain 4 retention/Object Lock
Source	`aws.amazon.com/solutions/case-studies/nasdaq-case-study` (accessed Oct 2026)

"We were able to easily support the jump from 30 billion records to 70 billion records a day because of the flexibility and scalability of Amazon S3 and Amazon Redshift.": Robert Hunt, VP Software Engineering, Nasdaq (AWS case study, accessed Oct 2026)

The examinable pattern is the split write path and read path: exchange messages land in S3 first, Redshift loads and queries from there, and Spectrum queries the 15 TB lake without moving it: storage absorbs the volume while compute stays sized for the queries. That is Section 3 and Section 5 of this lesson made concrete, and it is why "load everything into the warehouse first" is rarely the AWS answer for an exchange-scale feed.

Case D, Amazon Customer Service: RA3 and the compute/storage split

Element	Detail
Customer	Amazon Customer Service, an internal Amazon organization
Challenge	A DC2-era warehouse (`dc2.8xlarge`) where dashboards were slowing down and Redshift operating cost kept climbing
Services	Migration to RA3 with Redshift Managed Storage: the published comparison is 3 × ra3.16xlarge versus dc2.8xlarge
Outcomes	−55% Redshift operating cost per year; dashboards +47% faster; queries +25% faster
Exam domain	Domain 3 (operations and cost) with Domain 2 architecture hooks
Source	AWS Big Data Blog, "How Amazon Customer Service lowered Amazon Redshift costs and improved performance using RA3 nodes", 2021-05-20

This is the canonical compute/storage separation story, and it is the reason Section 1 of this lesson spends time on billing mechanics: once the durable copy lives in S3 at $0.024/GB-month (as of Oct 2026), the node choice becomes a compute decision rather than a disk decision, so you right-size vCPUs instead of buying local capacity you will not scan. It also explains why "add another DC2 node" is a distractor since DC2 deprecation was announced in April 2025 (AWS Big Data Blog, 2026-03-11).

Query runtime before vs after (minutes) - AWS customer case studies, as published

Hover any bar to read the claim: GE Aerospace publishes 90 → 7 minutes, PayU publishes 10 to 15 minutes → under 1 minute (charted at the upper end, 15 → 1). Both are single-customer measurements on a redesigned physical model: never a guarantee that your cluster will see the same ratio.

Case	Redshift pattern it demonstrates	Domain
GE Aerospace	Physical redesign of a legacy warehouse: performance and cost through design, not headcount	D2 Stores / D3 Analyze
PayU	Load path (Glue → S3 → Redshift) plus data sharing instead of replication	D1 Transform / D4 Permissions
Nasdaq	Decoupled lake house: write to S3, read through Redshift and Spectrum, archive with Glacier + Object Lock	D2 Stores / D1 Transform
Amazon Customer Service	RA3 + Managed Storage: right-sizing compute once storage is metered separately	D3 Analyze / D2 Stores

📚 Did you know? Every percentage in this section is one customer's measurement on one workload, not a specification: GE Aerospace's >$500,000/year is GE's own estimate, PayU's 77% counts queries, not dollars, and Amazon Customer Service's −55% is their operating cost. Generalising any case number to a different workload is a classic wrong option (AWS-published case material, accessed Oct 2026).