MPP Warehouse Architecture
MPP Warehouse Architecture
Redshift is a massively parallel SQL warehouse: a leader node plans queries and coordinates compute nodes, each holding a slice of the data stored in compressed, columnar tables. Because storage is columnar, scans skip untouched columns and compression keeps hot data cacheable.
RA3 nodes separate compute from capacity: cache lives on the nodes while the bulk of data sits on managed storage backed by S3, so you scale query power and storage independently. Unlike Athena — which queries files on demand — Redshift maintains materialized tables with statistics, making it the better fit for frequent complex joins, BI concurrency, and workloads that need transactions over curated data.
Distribution, Sort, and Load
Distribution, Sort, and Load
Load from S3 with a single command:
COPY orders FROM 's3://lake/orders/'
IAM_ROLE 'arn:aws:iam::123456789012:role/rs-copy'
FORMAT AS PARQUET;
Two keys steer performance. Distribution style decides where rows live: KEY co-locates join partners (e.g., orders.customer_id with customers.customer_id), ALL replicates small dimension tables to every node, and EVEN (or AUTO) spreads facts. Sort key orders rows on disk so zone maps skip irrelevant blocks — put the highest-filter column first.
Auto ANALYZE and auto VACUUM keep statistics and sorted data healthy after loads and updates unless you disable them.
Pitfalls and Interview Focus
Pitfalls and Interview Focus
A skewed distribution key (high-cardinality value with most rows on one slice) leaves one compute node hot while others idle — check slice row counts when a query will not speed up. Loading CSV without compression hints or splitting files wastes I/O; prefer columnar formats and parallel COPY.
Spectrum queries S3 in place but bills per data scanned — run filters that prune before aggregating. An always-on cluster idles at full price: pause/resume or move sporadic workloads to Redshift Serverless. Concurrency spikes queue behind WLM — use concurrency scaling or Serverless endpoints.
Interview: given a schema and top queries, name the join keys (distribution), the main filter (sort key), and the load path (COPY + IAM role).
Quiz
1. What is the leader node responsible for?
2. Which distribution style best serves a small dimension table joined to many facts?
3. What does a sort key primarily enable?
Flashcards
Question
Leader vs compute nodes
Click to reveal answer
Answer
Leader plans and coordinates; compute nodes store slices of every table.
Question
Distribution KEY
Click to reveal answer
Answer
Places rows by the key column so joins avoid network shuffles.
Question
Sort key
Click to reveal answer
Answer
Orders rows on disk; zone maps skip blocks that fail the WHERE filter.
Question
COPY from S3
Click to reveal answer
Answer
Bulk load with IAM_ROLE, parallel file reads, format hints (Parquet/CSV/GZIP).
Question
Spectrum
Click to reveal answer
Answer
Query S3 data in place; billed per byte scanned — prune aggressively.
Revision Notes
Key Takeaways
- 1. Columnar + MPP + good keys is where Redshift performance comes from
- 2. Distribution and sort keys are chosen from real query patterns, not defaults alone
- 3. Idle clusters and Spectrum scans are the cost surprises — pause and prune
Interview Tips
- • Take a schema + query set and reason aloud about distribution and sort keys
- • Explain skew: how you detect it (slice stats) and how you fix it (key or ALL/EVEN)
Cheat Sheet
Leader plans, nodes store slices. KEY=joins, ALL=small dims, EVEN=facts; sort key=zone-map pruning. Load with COPY+IAM_ROLE+Parquet. Watch slice skew; Spectrum bills per byte; pause idle clusters.