Amazon Redshift and Spectrum

By the end of this lesson you will be able to

• Explain what a data warehouse is for, and why Redshift (OLAP) and RDS or Aurora (OLTP) are not interchangeable.
• Describe how Redshift answers a query: columnar storage, compression and zone maps, a leader node that plans and merges, compute nodes and slices that work in parallel (MPP), and the distribution and sort keys that make it fast or slow.
• Choose between a provisioned cluster (RA3, reserved nodes, pause and resume) and Redshift Serverless (RPUs, pay per second), and know which node types are being retired.
• Load data the right way: Data Firehose through S3, COPY from S3 in parallel with an IAM role, large batched inserts over JDBC, Enhanced VPC Routing, and zero-ETL integrations.
• Protect a warehouse: automated and manual snapshots, retention, restore to a new cluster, cross-Region snapshot copy with a KMS snapshot copy grant, Multi-AZ and relocation.
• Use Redshift Spectrum to query S3 without loading it, and pick between Athena, Redshift and Spectrum in an exam question.

#Why a data warehouse

Lesson 16.1 split databases into two worlds. OLTP is the day-to-day work of an app: place an order, move money, many tiny transactions on a few rows each. OLAP is asking big questions about history: sales by district and month for three years, one query reading hundreds of millions of rows but only a few columns. Running the second kind of work on the first kind of database slows the app for everyone. This lesson is about the AWS service built for the second world.

Amazon Redshift is AWS’s fully managed, petabyte-scale data warehouse. You load data from many sources into relational tables, and analysts, dashboards and BI tools ask SQL questions of it all day. It is an OLAP engine, not an OLTP one.

#What Redshift is

The notes and slides describe Redshift in a few lines. Here they are, with what each line really means.

• Fully managed: AWS provisions the machines, patches them, replaces failed nodes, takes backups and monitors the cluster.
• Based on PostgreSQL, but not for OLTP: Redshift started from PostgreSQL, so its SQL dialect and client drivers feel familiar (JDBC, the PostgreSQL wire protocol), but the engine underneath is completely different. It is built for big scans and joins, not single-row transactions.
• Columnar storage and MPP (massively parallel processing): data is stored column by column, compressed, and every query is split across many nodes that work at the same time. These two ideas are the whole reason Redshift is fast for analytics. The next two sections show them.
• Petabyte scale: an RA3 cluster can hold up to 16 PB of managed storage.
• SQL interface: analysts use plain SQL; BI tools such as Amazon QuickSight (lesson 17.5) or Tableau connect to it directly.
• Two modes: a provisioned cluster that you size, or Redshift Serverless that sizes itself.

#Columnar storage

A row store, like RDS, keeps each row’s columns together on disk: perfect when you read or write a whole order at once. A column store keeps each column together: all the dates in one place, all the amounts in another. Three things follow:

1. A query that needs 2 columns out of 50 reads only those 2.
2. A column holds similar values (dates next to dates), so it compresses very well, often several times smaller, which means less to read.
3. For each 1 MB block Redshift remembers the minimum and maximum value, a zone map. If the table is sorted by date (a sort key), a query for October skips every block whose dates are outside October without reading it.

The price: fetching one complete row means visiting every column, and changing one row rewrites blocks. That is why Redshift is bad at OLTP. Try it:

#Leader node and compute nodes

A provisioned cluster has one leader node and one or more compute nodes.

• The leader node is the cluster’s endpoint. Clients connect to it with JDBC or the PostgreSQL driver. It parses each query, builds the execution plan, compiles it, sends it to the compute nodes, and aggregates their partial results into the final answer. It stores no table data. You do not pay for it separately.
• Compute nodes store the data and run the plan. Each node is split into slices, and each slice owns a share of every table and works on it in parallel. Adding nodes adds slices, so the same query has less work per slice.
• A single-node cluster shares one machine for both roles; fine for a test, not recommended for production.

How rows are spread over the slices is the distribution style: AUTO (let Redshift decide, the default), EVEN (round-robin), KEY (rows with the same value of one column go to the same slice, so joins on that column happen locally) or ALL (a full copy of a small table on every node). A bad KEY choice causes skew: one node holds most of the data and the whole query waits for it.

Other things worth knowing at this level: workload management (WLM) puts queries into queues so a giant report does not block small dashboard queries; concurrency scaling adds temporary capacity when many users query at once; data sharing lets another cluster or account read live data without copying it; and result caching answers a repeated query from memory.

#Provisioned or Serverless

#Provisioned clusters

You choose a node type and the number of nodes in advance, and pay per node-hour. For steady, predictable use you can buy reserved nodes for one or three years for a large discount (like EC2 Reserved Instances, lesson 4.4). You can pause a cluster (you then pay only for storage) and resize it.

With RA3 the data you keep is not limited by the local disks: blocks that are not used often live only in S3, and you pay one storage price either way. That is why the slides and notes can say “scale to petabytes”.

#Redshift Serverless

Redshift Serverless runs the same engine with no cluster to size. You create a namespace (the databases, users and data) and a workgroup (the compute). Capacity is measured in Redshift Processing Units (RPUs, 16 GB of memory each): you set a base capacity (default 128 RPUs, from 4 up to 512, or 1,024 in some Regions) and Serverless scales up for heavy queries and down when idle. You pay for RPU-hours per second, with a 60-second minimum, only while queries run, plus managed storage.

#Getting data in

The slides’ rule is simple: large inserts are much better than small ones. Redshift is built to swallow big batches. There are four common paths.

1. Amazon Data Firehose → S3 → COPY. Firehose buffers streaming records, writes them as files to an S3 bucket, then runs COPY on the cluster for you (lesson 13.6). Near real time: a few minutes behind (the buffer interval, 300 seconds by default and adjustable from 0 to 900, plus the COPY). As lesson 13.6 explains, Firehose reaches the cluster from outside your VPC.

2. COPY from S3. The fastest way to load. One command loads every file under a prefix, in parallel, each slice taking files. Split big data into several files of similar size, ideally a multiple of the number of slices, compressed (gzip, bzip2, zstd…) or columnar (Parquet, ORC). Permission to read S3 comes from an IAM role attached to the cluster:

   copy sales
   from 's3://shop-staging-example/sales/2026/10/'
   iam_role 'arn:aws:iam::111122223333:role/RedshiftCopyRole'
   format as parquet;
   

3. Inserts from an app over JDBC, for example from EC2. Works, but write in batches: multi-row inserts of thousands of rows in one transaction, never one row per statement. For large volumes, write files to S3 and COPY them.

4. Zero-ETL integrations. Aurora MySQL and PostgreSQL, RDS for MySQL, PostgreSQL and Oracle, and DynamoDB can replicate into Redshift continuously, within seconds, with no pipeline to build (lesson 7.3). The exam clue is “analytics on Aurora/DynamoDB data in Redshift without building ETL”.

Enhanced VPC Routing. Normally COPY and UNLOAD traffic between the cluster and S3 travels outside your VPC. Turn on Enhanced VPC Routing and Redshift forces it through your VPC, so security groups, network ACLs, VPC endpoint policies and VPC Flow Logs apply. Then the VPC must have a route to S3: an S3 gateway endpoint (lesson 22.2), or a NAT or internet gateway, or the load fails.

#Snapshots and disaster recovery

Redshift backs itself up with snapshots: point-in-time, incremental backups (only changed blocks are saved), stored in S3 that Redshift manages for you. You do not see that bucket in your S3 console.

A restore always creates a new cluster; queries can start before every block has arrived. On Serverless, recovery points are taken every 30 minutes (or every 5 GB per node changed) and kept for 24 hours, and you can take manual snapshots too.

Cross-Region snapshot copy is the classic Redshift DR answer. Turn it on, choose one destination Region, and every automated and manual snapshot is copied there automatically. Copied automated snapshots are kept 7 days by default in the destination (you can change it). If the source Region fails, restore a new cluster in the destination Region (strategies in lesson 23.1).

For a KMS-encrypted cluster the copy needs one more piece. KMS keys are Regional (lesson 21.2), so in the destination Region you create a snapshot copy grant that lets Redshift use a customer managed key there, and choose that grant when you enable the copy.

#Multi-AZ and relocation

A provisioned cluster normally runs in one Availability Zone: its nodes need fast links to each other. With RA3 your data is already safe in managed storage, but if that AZ goes down, the compute is gone.

• Cluster relocation (RA3): if the AZ has a problem, Redshift moves the cluster to another AZ in the same Region, keeping the same endpoint. Minutes of downtime.
• Multi-AZ deployment (RA3, generally available since late 2023): Redshift places the same number of nodes in two AZs, both active, behind one endpoint, reading the same managed storage. If one AZ fails, the other keeps serving. The SLA rises from 99.9% to 99.99%. You pay for twice the compute. The slides’ “Multi-AZ mode for some clusters” means exactly this: RA3 only.

Neither protects you from a Region failure; that is still cross-Region snapshot copy.

#Redshift Spectrum

Normally Redshift can query only data that has been loaded into it. Redshift Spectrum lets a cluster query data that is already in S3, without loading it, and even join it with tables inside the cluster.

How it works: you define an external schema that points at a data catalog, usually the AWS Glue Data Catalog (an Athena catalog or an Apache Hive metastore also work; lesson 17.6). The leader plans the query, and the S3 part is handed to the Spectrum layer, thousands of AWS-run nodes outside your cluster that read the files in parallel, filter and aggregate, and return only the small results. You pay per terabyte scanned in S3 (about 5 USD per TB, like Athena), so Parquet and partitions matter just as much as in 17.1.

• You must have a Redshift cluster (or Serverless workgroup) available to start the query. Spectrum is a feature of Redshift, not a standalone service.
• On RA3 and DC2 clusters the S3 data must be in the same Region as the cluster.
• External tables are read-mostly: you cannot update or delete rows in them.

The classic use is hot and cold data: the last 12 months of sales live in Redshift for fast dashboards, the 5 years before that stay cheap in S3 as Parquet, and one query can span both.

#Athena, Redshift or Spectrum?

The notes’ exam tricks, word for word in meaning: occasional ad-hoc queries → Athena; complex OLAP, reporting and repeated heavy queries on huge data → Redshift; “query data in S3 without loading into the Redshift cluster” → Redshift Spectrum.

#What goes wrong

#Redshift in system design

• Separate OLTP from OLAP. The app writes to Aurora or DynamoDB; Redshift gets a copy through zero-ETL, Firehose or batch COPY.
• Land in S3 first. Raw files in an S3 data lake, loaded into Redshift for hot data and queried with Spectrum or Athena for cold data. S3 stays the cheap, durable record.
• Design tables for the questions: sort keys for common filters, distribution keys for big joins, ALL for small dimension tables, or let AUTO decide.
• Size for the pattern: provisioned with reserved nodes for constant heavy use, Serverless for spiky or occasional use.
• Plan both failures: Multi-AZ (or relocation) for an AZ, cross-Region snapshot copy for a Region, with a KMS grant if encrypted.
• Lock it down: private subnets, security groups, Enhanced VPC Routing for regulated data, KMS encryption at rest, TLS in transit, IAM roles for COPY and UNLOAD, audit logging to S3 or CloudWatch.
• Visualise: QuickSight on top for dashboards (lesson 17.5); the full pipeline from IoT devices to dashboards is lesson 17.8.

#Try it yourself

These commands create billable resources. Region ap-southeast-1; outputs are shortened. Delete everything at the end.

#A small provisioned cluster

aws redshift create-cluster \
  --cluster-identifier shop-dw \
  --node-type ra3.large --number-of-nodes 2 \
  --master-username admin --manage-master-password \
  --iam-roles arn:aws:iam::111122223333:role/RedshiftCopyRole \
  --encrypted --enhanced-vpc-routing \
  --cluster-subnet-group-name private-dw
#   "ClusterStatus": "creating", "NodeType": "ra3.large", "NumberOfNodes": 2,
#   "EnhancedVpcRouting": true, "AutomatedSnapshotRetentionPeriod": 1

aws redshift describe-clusters --cluster-identifier shop-dw \
  --query 'Clusters[0].[ClusterStatus,Endpoint.Address]'
#   [ "available", "shop-dw.c1abcxyz.ap-southeast-1.redshift.amazonaws.com" ]

#Load and query

create table sales (
  order_id   bigint,
  order_date date sortkey,
  district   varchar(32),
  amount     decimal(12,2)
) diststyle auto;

copy sales
from 's3://shop-staging-example/sales/2026/10/'
iam_role 'arn:aws:iam::111122223333:role/RedshiftCopyRole'
format as parquet;
-- INFO: Load into table 'sales' completed, 48211906 record(s) loaded successfully.

select district, sum(amount) from sales
where order_date >= '2026-10-01' group by district order by 2 desc limit 3;
--  district  |     sum
--  Dhaka     | 912337410.50
--  Chattogram| 301442119.00
--  Gazipur   | 118902337.75

#Spectrum over S3

create external schema lake
from data catalog database 'clickstream'
iam_role 'arn:aws:iam::111122223333:role/RedshiftCopyRole'
create external database if not exists;

select s.segment, count(*)
from lake.clicks c join customers s on c.customer_id = s.customer_id
where c.year = 2023 and c.month = 10
group by s.segment;

#Snapshots and DR

aws redshift modify-cluster --cluster-identifier shop-dw \
  --automated-snapshot-retention-period 7

aws redshift create-cluster-snapshot \
  --cluster-identifier shop-dw --snapshot-identifier before-schema-change
#   "Status": "creating", "SnapshotType": "manual"

# In the destination Region: a grant for a KMS key there
aws redshift create-snapshot-copy-grant --region ap-south-1 \
  --snapshot-copy-grant-name dw-mumbai-grant \
  --kms-key-id arn:aws:kms:ap-south-1:111122223333:key/1234abcd-12ab-34cd-56ef-1234567890ab

aws redshift enable-snapshot-copy --cluster-identifier shop-dw \
  --destination-region ap-south-1 --retention-period 7 \
  --snapshot-copy-grant-name dw-mumbai-grant
#   "ClusterSnapshotCopyStatus": { "DestinationRegion": "ap-south-1", "RetentionPeriod": 7 }

# DR drill: restore in Mumbai from a copied snapshot
aws redshift restore-from-cluster-snapshot --region ap-south-1 \
  --cluster-identifier shop-dw-dr --snapshot-identifier copy:shop-dw-2026-10-30-06-00

#The Serverless way

aws redshift-serverless create-namespace --namespace-name shop
aws redshift-serverless create-workgroup --workgroup-name shop-wg \
  --namespace-name shop --base-capacity 8 \
  --subnet-ids subnet-0abc1234 subnet-0def5678 subnet-0aaa9999
#   "workgroup": { "baseCapacity": 8, "status": "CREATING", ... }

Clean up with aws redshift delete-cluster --cluster-identifier shop-dw --skip-final-cluster-snapshot, disable-snapshot-copy, delete-cluster-snapshot, and aws redshift-serverless delete-workgroup / delete-namespace.

#Summary

Key takeaways

• Redshift = fully managed OLAP data warehouse, based on PostgreSQL but not for OLTP; SQL interface; BI tools like QuickSight connect to it.
• Columnar storage (read only needed columns, strong compression, zone maps with sort keys) plus MPP make it fast. No classic indexes.
• Leader node plans and aggregates (the endpoint); compute nodes with slices store data and run the plan in parallel. Distribution AUTO, EVEN, KEY or ALL; a bad key causes skew.
• Provisioned (RA3 with managed storage, reserved nodes, pause/resize; DC2 retiring, RG new) vs Serverless (RPUs, per-second billing, 60 s minimum).
• Load in bulk: Firehose → S3 → COPY; COPY from S3 in parallel with an IAM role; batched JDBC inserts; zero-ETL from Aurora, RDS, DynamoDB. Enhanced VPC Routing forces COPY/UNLOAD through the VPC, which then needs an S3 endpoint or NAT.
• Snapshots: incremental, in Redshift-managed S3; automated every ~8 h / 5 GB per node / schedule, retained 1–35 days (default 1); manual kept until deleted; restore = new cluster.
• DR: cross-Region snapshot copy (copied automated kept 7 days by default); encrypted clusters need a snapshot copy grant in the destination. Multi-AZ (RA3) or relocation for AZ failures.
• Spectrum: query S3 without loading, needs a running cluster, thousands of Spectrum nodes, Glue Data Catalog, same Region, pay per TB scanned.
• Exam: ad-hoc on S3 → Athena; heavy repeated BI/OLAP → Redshift; S3 without loading, from Redshift → Spectrum.

Self-check: Amazon Redshift and Spectrum32 questions