Redshift and the warehouse
Lesson 3 of 5 in Choosing a Data Store.
A lake decouples storage from compute and lets any engine read the bytes. A warehouse does the opposite: it owns its storage layout, and everything good and bad about Amazon Redshift follows from that ownership.
Physically, a cluster is a leader node that parses, plans and compiles queries, and compute nodes that hold the data and execute. Each compute node is divided into slices, and a slice is the real unit of parallelism: it owns a share of every table and runs its portion of the plan independently. A query finishes when its slowest slice finishes — which is why uneven data placement costs more than it looks, and why loading a single large uncompressed file is slow (one slice reads it while the others wait).
Because Redshift owns the layout, it can do three things a lake cannot:
Compress each column with an encoding chosen for that column. A low-cardinality status column becomes a dictionary; a monotonically rising timestamp becomes a delta. Columnar compression on a warehouse table routinely reaches three to four times, and the compressed blocks are what the scan reads.
Keep zone maps. Redshift records the minimum and maximum value of every column in every 1 MB block. A predicate on the sort key can therefore discard whole blocks without reading them — the same mechanism as partition pruning in a lake, at a far finer grain and with no directory structure involved.
Place rows deliberately across slices. This is the distribution style, and it is the single highest-leverage physical decision in the warehouse, because it decides whether a join needs the network at all.
| Style | What it does | Choose when | What it costs |
|---|---|---|---|
AUTO | Redshift decides and may change its mind: small tables start | the default, and the right answer whenever a stem asks for least operational effort | you give up deterministic placement, and the change happens in the background |
KEY | hashes one column so equal values land on the same slice, collocating a join | a large fact joins a large dimension on a high-cardinality column, repeatedly | a table has only ONE distribution key, so a second big join is not collocated |
ALL | stores a complete copy of the table on every node | a small dimension that joins almost everything, or one that cannot be collocated | storage multiplied by node count, plus slower loads, vacuums and updates |
EVEN | round-robins rows across slices, ignoring every column value | the table does not participate in joins, or there is no defensible key | any join against it needs redistribution first |
Four more pieces of Redshift show up in store questions, and each one exists to answer a different objection to "just use a warehouse".
Redshift Managed Storage is what makes storage and compute separable on RA3 node types: the durable copy of a table lives in Amazon S3, local SSD caches the hot blocks, and you size compute for your query workload rather than adding nodes to get disk. It is also why a snapshot is quick — it references blocks already in managed storage instead of bulk-copying them.
Redshift Serverless removes the cluster. You create a namespace (the data, users and databases) and a workgroup (compute and networking), set a base capacity in RPUs, and pay per RPU-second while queries run. It suits spiky, intermittent or unpredictable analytics; a steady round-the-clock workload with a known shape is usually cheaper on provisioned RA3 nodes with a reservation. That trade-off is the whole of most Redshift cost questions.
Redshift Spectrum queries S3 files through an external schema — usually backed by the Glue Data Catalog — and lets them be joined to local tables in one statement. Scans are billed per terabyte, so lake rules apply: partition it, store it columnar, project only what you need. The pattern it enables is the standard lifecycle answer: recent data in managed storage where joins are fast, older history left in S3, and a view that unions the two so analysts see one table.
Zero-ETL integrations replicate continuously from Aurora MySQL and PostgreSQL, RDS for MySQL, DynamoDB and several applications into Redshift, within seconds and with no pipeline to operate. Read the limit carefully, because it decides questions: zero-ETL does not transform. The target mirrors the source's shape, so conforming, enriching and history-keeping still happen in Redshift afterwards.
And two levers for contention rather than capacity. Workload management routes queries to queues by user, group or query group, each with its own memory share and concurrency, so an analyst's runaway scan cannot starve the nightly load; query monitoring rules then abort, reprioritise or log a query that exceeds a measured threshold. Concurrency scaling adds transient clusters when read queries start queueing, paid for with credits — one hour earned per 24 hours of main-cluster uptime, accumulating up to 30 hours — which is why a daily burst is usually free and a sustained overload is not. Neither helps with one enormous query; that is a materialized view or a modelling problem.
Warehouse, lake, or both
Interactive decision tree — outcomes:
- Provisioned Redshift on RA3
A steady, join-heavy workload is what a sized cluster is for, and a reservation makes it the cheapest form of that capacity. Add concurrency scaling for the daily peak and workload management to protect the load window. Then ask the history question: if the working set is a fraction of the data, keep the rest in S3 behind Spectrum rather than buying managed storage for it.
- Redshift Serverless
Billing per RPU-second while queries run is the answer to a cluster that would be idle most of the day. You still get the warehouse engine — distribution, sort keys, materialized views, Spectrum — without sizing nodes. Watch the base capacity: set too high, it removes the saving the model exists to deliver.
- Athena over the lake
No cluster, no loading, billed per terabyte scanned. Right for occasional questions and for data that already lives in S3 in a queryable layout. Its weakness is exactly the warehouse’s strength: repeated multi-table joins with sub-second expectations, where a planner with statistics and collocated data wins decisively.
- Both — warehouse plus Spectrum
The most common real answer and a frequent exam one. Load the working set into managed storage, leave the history as partitioned Parquet in S3, define external tables over it, and expose a view that unions the two. Analysts see one table; the bill reflects that eight of ten years are cold.
Key terms: Amazon Redshift, Distribution style, Sort key (SORTKEY), Amazon Redshift Spectrum, Amazon Redshift Serverless, Zero-ETL integration
Interactive checkpoint quiz (2 questions) — open this page in a browser to take it.