Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 70 ยท Databases

Amazon Redshift

Learn Amazon Redshift: columnar storage, leader and compute nodes, distribution styles, sort keys, provisioned vs serverless, loading with COPY, Spectrum and data sharing.

The database behind your shop, an RDS instance, is built for OLTP: many small transactions, such as "insert this order" or "fetch customer 42". Management then asks a very different kind of question: "What was revenue per product category per month for the last three years?" That query reads millions or billions of rows, but only two or three columns, and it runs on a schedule or in a dashboard. This is OLAP, analytics, and the right tool is a data warehouse.

Amazon Redshift is AWS's managed data warehouse. It speaks PostgreSQL-flavored SQL, so your BI tools and analysts can connect with standard drivers, and it can scan huge tables quickly because of how it stores and distributes data. This lesson explains that design and how to load and query data. You will use some standard SQL, and the SQL tutorial covers it from scratch.

Redshift cannot run on this site. SQL and CLI snippets are real and read-only. The Python programs are stand-ins that model two of its core ideas, columnar scanning and data distribution, with invented sizes.

OLTP or OLAP?

OLTP (RDS, Aurora, DynamoDB) OLAP (Redshift)
Typical query One row or a few rows Aggregate over millions of rows
Writes Many small inserts and updates Bulk loads in batches
Storage layout Row by row Column by column
Users An application Analysts and dashboards
Concurrency Thousands of short queries Tens or hundreds of heavy queries

Do not use Redshift as the database of a busy application: single-row operations are slow compared with a row store. Load data into it from your operational databases and from S3.

Architecture

A provisioned cluster has one leader node and several compute nodes. The leader receives the SQL, plans it, sends work to the compute nodes, and combines their results. Each compute node is divided into slices, and each slice holds a part of every table and processes its part in parallel. This is called massively parallel processing (MPP).

flowchart TB
    CL["SQL client / BI tool"] --> L["Leader node<br/>parses, plans, aggregates"]
    L --> N1["Compute node 1<br/>slices 0, 1"]
    L --> N2["Compute node 2<br/>slices 2, 3"]
    L --> N3["Compute node 3<br/>slices 4, 5"]
    N1 --- S3[("S3 and managed storage")]
    N2 --- S3
    N3 --- S3

The leader plans, the compute nodes work on their slices in parallel.

Two ways to run it:

  • Provisioned clusters: you choose a node type and count. Current RA3 nodes separate compute from managed storage (S3-backed), so you can scale them independently. You pay for the nodes by the hour, and you can pause a cluster you do not need.
  • Redshift Serverless: no cluster to size. You set a base capacity in RPUs (Redshift Processing Units), the warehouse scales automatically, and you pay for the compute used while queries run, per second, plus storage. It suits spiky or unpredictable use, and teams that do not want to manage capacity.

Columnar storage

A row store keeps all the fields of one row together. Redshift is columnar: it stores each column separately, in compressed blocks. A query that needs only category and amount reads only those two columns, and skips the others entirely. Similar values stored together also compress very well, which reduces the I/O further. Redshift also keeps zone maps, the minimum and maximum value of each block, so it can skip blocks that cannot match a filter.

Python Why columnar storage reads less data

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Distribution styles and sort keys

When you create a table you decide how rows are spread across slices and in what order they are stored. These two choices are the heart of Redshift tuning.

Distribution style (DISTSTYLE):

  • AUTO (the default): Redshift chooses, starting with ALL for small tables and moving to EVEN or KEY as the table grows. Start here.
  • EVEN: rows go round-robin across slices. Balanced, but joins need data movement.
  • KEY: rows with the same value of the distribution key land on the same slice. If two big tables are distributed on the join column, the join happens locally, with no network shuffle. Pick a column with many values and an even spread.
  • ALL: a full copy of the table on every node. Good for small dimension tables that join to everything.

Sort key (SORTKEY): the order in which rows are stored on disk. With zone maps, a filter on the sort key (usually a date) lets Redshift skip most blocks. AUTO can also choose sort keys.

SQL
CREATE TABLE sales (
  sale_id     BIGINT,
  customer_id INT,
  product_id  INT,
  amount      DECIMAL(10,2),
  sold_at     TIMESTAMP
)
DISTKEY (customer_id)      -- joins with customers on customer_id are local
SORTKEY (sold_at);         -- date filters skip blocks

CREATE TABLE countries (code CHAR(2), name VARCHAR(80))
DISTSTYLE ALL;             -- tiny table copied to every node

The stand-in distributes rows by hash and measures balance. A skewed key leaves some slices doing most of the work, so the whole query waits for the slowest slice.

Python Distribution keys and skew

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Loading data with COPY

The right way to bulk load is the COPY command, which reads files from S3 in parallel across all slices. Do not load millions of rows with single-row INSERT statements.

SQL
COPY sales
FROM 's3://my-datalake/sales/2026/11/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftCopyRole'
FORMAT AS PARQUET;

Tips: use columnar Parquet if you can, split large CSV inputs into many files of similar size (about as many as there are slices, or a multiple), compress them, and let COPY pick compression encodings on the first load into an empty table. UNLOAD does the reverse and writes query results to S3. Pipelines often use Kinesis Firehose to drop data in S3 and COPY it, or load it automatically with an auto-copy job.

COPY parallelism depends on the files, which this stand-in shows: one file can only be read by one slice.

Python How file layout affects COPY

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Spectrum, data sharing and more

  • Redshift Spectrum queries data in S3 without loading it. You define an external schema over the AWS Glue Data Catalog, and Redshift reads Parquet or CSV files directly, joining them to local tables. It suits rarely queried history, so the warehouse holds only the hot data. Athena offers similar querying without a warehouse.
  • Data sharing lets one Redshift cluster or serverless namespace share live data with another, across accounts or Regions, with no copying. A producer team publishes, consumer teams query.
  • Materialized views precompute expensive aggregations and refresh automatically.
  • Concurrency scaling adds temporary capacity when many queries arrive at once.
  • Workload management and query queues keep dashboards from being starved by large ETL jobs.
  • Security: runs in your VPC with security groups, encrypts data with KMS, and supports IAM and column-level grants.
  • Redshift ML creates models with SQL (it uses SageMaker behind the scenes, see SageMaker and AI services).

Zero-ETL integrations

A classic pipeline copies operational data into the warehouse with a scheduled job: extract from the database, transform, load. A zero-ETL integration removes that job. AWS continuously replicates changes from a supported source (Aurora MySQL and PostgreSQL, RDS for MySQL and DynamoDB, among others) into a Redshift database, usually within seconds, so you can run analytics on near-current data without writing or operating the pipeline and without querying the production database.

Terminal
# read-only: create an integration from an Aurora cluster to a Redshift namespace
aws rds create-integration --integration-name orders-to-warehouse \
  --source-arn arn:aws:rds:eu-west-1:123456789012:cluster:orders \
  --target-arn arn:aws:redshift-serverless:eu-west-1:123456789012:namespace/analytics

You still enable the needed source settings (for example logical replication for PostgreSQL), and on the Redshift side the integration appears as a destination database that you query like any other. The data arrives in roughly the source shape, so you often build views or materialized views on top. If you need heavy transformation, filtering of sensitive columns, or data from sources that are not supported, a traditional pipeline (Glue, Firehose, or a custom job) is still the better tool.

Cost and common mistakes

You pay for node-hours (or RPU-seconds for serverless), managed storage, Spectrum scans by bytes read, and data transfer. Frequent causes of waste or slowness:

  • Using SELECT * instead of listing columns.
  • Choosing a distribution key with skew, or one that is never joined on.
  • Leaving a provisioned cluster running overnight when nobody uses it (pause it, or use serverless).
  • Loading with many small INSERTs instead of COPY.
  • Never running VACUUM and ANALYZE (these now run automatically in the background for most tables, but check).

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why is Redshift fast at aggregating a few columns across billions of rows?

    Columns are stored and compressed separately, so unneeded columns are not read, and each slice works on its part of the data in parallel.
  2. 2. Which distribution style copies a whole small table to every node?

    DISTSTYLE ALL keeps a full copy on each node, which makes joins with small dimension tables local.
  3. 3. What is the recommended way to load large amounts of data from S3?

    COPY loads in parallel across slices. Multiple files let all slices work at the same time.
  4. 4. What does Redshift Spectrum do?

    Spectrum reads external tables stored in S3, typically defined in the Glue Data Catalog, and can join them with local tables.
  5. 5. Which workload suits Redshift Serverless best?

    Serverless scales automatically and charges for usage, which fits irregular analytics. It is not a transactional key-value store.

Summary

  • Redshift is a managed, PostgreSQL-style data warehouse for OLAP: big scans and aggregations, not many small transactions.
  • A leader node plans queries and compute nodes process slices in parallel. Choose provisioned RA3 nodes or Serverless with RPUs.
  • Columnar storage and compression mean queries read only the columns they need.
  • Distribution style (AUTO, EVEN, KEY, ALL) and sort keys decide data placement. Start with AUTO and tune joins and filters later.
  • Load with COPY from S3 using several files. Spectrum queries S3 in place, and data sharing shares live data between warehouses.

Next: Lambda Functions