AWS Learning Journey

When Athena Isn't Enough: Amazon Redshift for Data Warehousing

Redshift Serverless vs. provisioned clusters, distribution styles, sort keys, compression, COPY and UNLOAD, Spectrum, workload management, materialized views, data sharing, security, and tuning.

8 min read · 1530 words
Amazon RedshiftData WarehouseAWSData EngineeringSQL
When Athena Isn't Enough: Amazon Redshift for Data Warehousing cover image

AWS from Zero to Production — Phase 9, Part 5 of 11 · See the full series roadmap


Athena is brilliant for exploring the lake. But then the business wants dashboards — dozens of them, refreshed constantly, used by hundreds of people, with complex joins across many tables, and answers in under a second.

Running all of that on Athena, scanning S3 on every click, gets slow and expensive. That’s when you reach for a data warehouse.

Amazon Redshift is AWS’s cloud data warehouse: columnar storage, massively parallel processing, a SQL interface your BI tools already speak — and deep integration with your S3 lake.

What you’ll walk away with


Data warehouse concepts

A warehouse stores modeled, cleaned data for analytics. Common modeling patterns:

             dim_customer
                  |
dim_product -- fact_orders -- dim_date
                  |
             dim_region

Redshift stores data by column, compresses it heavily, and splits queries across many compute slices that work in parallel — MPP (massively parallel processing).


Redshift Serverless vs. provisioned clusters

Redshift Serverless

Provisioned clusters

Default choice: Start with Redshift Serverless. Consider provisioned clusters once usage is steady and well understood.


Table design

Three choices drive Redshift performance: distribution, sort keys, and compression.

Distribution styles

Distribution decides which slice stores each row. Joins are fastest when matching rows live on the same slice.

Watch out: A KEY column with skewed values — like a few customers owning most orders — puts most data on a few slices, and they become the bottleneck.

Sort keys

A sort key stores rows in order. Redshift keeps min and max values per block, and skips blocks that can’t match — like partition pruning, inside the table.

Compression encoding

Each column is compressed with an encoding — like AZ64 for numbers and dates, and ZSTD or LZO for text. With ENCODE AUTO (the default), Redshift chooses for you.

CREATE TABLE fact_orders (
  order_id     BIGINT,
  customer_id  BIGINT,
  item         VARCHAR(200),
  total        DECIMAL(12,2),
  created_at   TIMESTAMP
)
DISTSTYLE KEY
DISTKEY (customer_id)
SORTKEY (created_at);

COPY: loading data

COPY loads data from S3 in parallel across all slices. It’s far faster than INSERT statements:

COPY fact_orders
FROM 's3://acme-lake-curated/sales/orders/'
IAM_ROLE default
FORMAT AS PARQUET;

UNLOAD: exporting data

UNLOAD writes query results back to S3, in parallel:

UNLOAD ('SELECT order_date, COUNT(*) AS orders FROM fact_orders GROUP BY 1')
TO 's3://acme-lake-consumption/sales/daily_orders/'
IAM_ROLE default
FORMAT AS PARQUET
PARTITION BY (order_date);

Use it to share warehouse results with the lake, ML pipelines, or other teams.


Redshift Spectrum: query the lake from the warehouse

Spectrum lets Redshift query data in S3 directly, through the Glue Data Catalog:

CREATE EXTERNAL SCHEMA lake
FROM DATA CATALOG
DATABASE 'sales_curated'
IAM_ROLE default;

SELECT d.region, SUM(o.total)
FROM lake.orders o            -- in S3
JOIN dim_region d             -- in Redshift
  ON o.region_id = d.region_id
WHERE o.order_date >= '2026-10-01'
GROUP BY d.region;

The pattern this enables:


Workload management (WLM)

Not every query is equally important. Workload management decides how queries share resources:

Redshift Serverless handles scaling automatically, with usage limits and query limits to control cost and runaway queries.


Materialized views

A materialized view stores the precomputed result of a query:

CREATE MATERIALIZED VIEW mv_daily_revenue
AUTO REFRESH YES
AS
SELECT DATE_TRUNC('day', created_at) AS day, SUM(total) AS revenue
FROM fact_orders
GROUP BY 1;

Data sharing

Data sharing gives other Redshift warehouses live, read-only access to your data — with no copying:

A common pattern: one ETL warehouse loads and models data; separate BI and data science warehouses consume it through data shares.


Redshift security


Integration with S3 and Glue

Redshift fits the lakehouse:

aws redshift-data execute-statement \
  --workgroup-name analytics \
  --database dev \
  --sql "SELECT COUNT(*) FROM fact_orders"

Performance tuning


Athena or Redshift?

Most platforms use both — on the same S3 data and the same catalog.


Common mistakes


Key takeaways


Quick self-check

Test yourself before moving on:

  1. Which distribution style suits a small, frequently joined dimension table?
  2. Why is COPY faster than INSERT?
  3. How can Redshift query historical data that stays in S3?
  4. What feature masks a credit card column for most users but not for the fraud team?

Answers

  1. DISTSTYLE ALL.
  2. It loads in parallel across all slices, from many files at once.
  3. Redshift Spectrum, through the Glue Data Catalog.
  4. Dynamic data masking.

What’s next

In Part 6, we scale processing: Amazon EMR and Apache Spark — EMR on EC2, Serverless, and EKS, partitioning and shuffles, data skew, memory, Spot Instances, and troubleshooting.

Redshift, Snowflake, BigQuery, or something else — what’s your team’s warehouse? Tell me in the comments.


Share this article

LinkedIn post draft