Data Warehousing
Intermediate 13 min read Data Warehousing

Redshift and AWS Data Warehouse

A cluster whose performance is decided at load time

Amazon Redshift is a shared-nothing columnar cluster: nodes own slices, distkeys/sortkeys decide data distribution, and COPY from S3 is the fast-loading king. Choose it when you are already in AWS and want openness over full SaaS.

RedshiftdistkeysortkeycolumnarCOPYspectrum
Prerequisites: Warehouse Architecture
Before we start

What you will learn

  • Explain distkey vs sortkey effects on joins and pruning
  • Use COPY from S3 for high-velocity bulk loads
  • Describe Redshift Spectrum for lake scans
Introduction

What is this?

Redshift is Amazon's columnar MPP warehouse: a cluster of nodes, each with slices, using distribution keys to colocate join partners and sort keys to prune. Data lands fast via COPY from S3, and Spectrum lets SQL reach data sitting in S3 without storing it in the cluster.

Motivation

Why do you need it?

  • Deep AWS integration and predictable cluster pricing make it a default choice inside AWS.
  • distkey/sortkey design done at table creation is permanent-ish and decides query performance.
  • COPY from S3 is the canonical bulk-ingest path every AWS-focused pipeline uses.
Think about it

Mental model

Imagine

A team of accountants each owning regional ledgers

Think of

Shared-nothing distribution

Joins are cheap when partners (customer + sales) live on the same ledger — that's choosing a distkey. Sorting each ledger by date before filing makes finding a period cheap — that's the sortkey.

Mechanics

How it works

  • Tables divide across slices; EVEN distributes evenly, KEY colocates on a hash of the distkey, ALL replicates small tables.
  • Sortkey orders rows so zone/block pruning skips ranges; choose sort columns that appear in range/equality predicates.
  • COPY reads gzip/parquet/orc from S3, routes to slices, and is the fastest path into the cluster.
  • Columnar compression (AZ64, ZSTD) plus cluster-wide columnar storage shrinks scans.
  • Spectrum extends SELECT over external tables in S3; WLM (workload management) queues shape concurrency.

Distribution and sort working in a Redshift cluster.

COPY / INSERT
Distribution KEY
Sort order
Slices scan
Leader returns
Reference

Key commands

COPY sales FROM 's3://...' IAM_ROLE '...' FORMAT AS PARQUET;

Fastest ingestion: parallel COPY from object storage.

VACUUM;

Re-sort the table if deletes/updates left unsorted regions.

ANALYZE sales;

Refresh planner statistics after large loads.

EXPLAIN SELECT ...;

Check dist/sort effectiveness and any broadcast joins.

Hands-on

Examples

The distkey/sortkey that makes a join local

The distkey/sortkey that makes a join local
CREATE TABLE sales (
  customer_id int, sold_date date, amount decimal(12,2)
) DISTKEY (customer_id) SORTKEY (sold_date);
 
CREATE TABLE customers (
  customer_id int distkey, name varchar(200), region varchar(50)
) SORTKEY (region);
 
-- COPY from S3, the canonical bulk path:
COPY sales FROM 's3://my-bucket/sales/' 
  IAM_ROLE 'arn:aws:iam::...:role/redshift-copy'
  FORMAT AS PARQUET;

Same distkey colocates sales+customers for local joins; sortkey prunes date ranges.

Real world

In production

  • A data pipeline loads per-hour S3 parquet with COPY, then models via curated SQL.
  • An HR fact uses ALL distribution for a small geography table, avoiding shuffle each join.
  • Analytics teams query cold history through Redshift Spectrum from the S3 lake, keeping the cluster lean.
Guidelines

Best practices

  • Pick distkey to make the biggest join local; use ALL for small giant-dimension tables.
  • Set sortkey around your most common range predicate (typically a date).
  • Load in bulk via COPY rather than row-by-row inserts (or the fuse process punishes throughput).
  • Vacuum+Analyze after heavy DML; unsorted regions silently erode pruning.
Watch out

Common mistakes

  • Choosing a low-cardinality distkey and creating a hot slice that becomes the cluster bottleneck.
  • Ignoring sortkey so every scan reads the full table despite date filters.
  • Row-by-row INSERTs from Python, producing tiny unsorted blocks and brutal COPY-fuse behavior.
When things break

Debugging tips

  • EXPLAIN showing a broadcast of a large table means your distkeys don't colocate; adjust or use ALL.
  • Slow scans despite date predicates = unsorted regions; VACUUM to recover pruning.
  • COPY timeouts usually trace to role/permissions on the S3 path or file format mismatch.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

Which design decision most reduces cross-node shuffling in Redshift joins?

2

COPY from S3 in Parquet is generally faster than row-by-row INSERTs into Redshift.

Go deeper

Official documentation

Amazon Redshift key concepts
docs
Redshift best practices
docs
Distribution styles
docs