Analytics Data Warehouse with Redshift

Build a star schema data warehouse on Redshift Serverless with optimized joins.

Introduction

30 Second Summary

Every time someone buys a concert ticket, that transaction joins millions of others in a data warehouse. Analysts query that warehouse to find patterns that shape pricing, marketing, and event planning.

In this project, you will build an analytics data warehouse on Amazon Redshift Serverless, load it with ticket-sales data from S3, and prove how your architectural decisions about data placement directly control query performance.

What You'll Build

A fully functional analytics data warehouse using the TICKIT ticket-sales dataset with intentional distribution key and sort key designs, bulk-loaded data, and documented EXPLAIN plan comparisons.

By the end of this project, you'll have:

  • A running Redshift Serverless workgroup loaded with sample ticket-sales data using the bulk-loading COPY command.
  • A set of analytical queries with EXPLAIN plans that reveal how data is shuffled between compute nodes during joins.
  • A side-by-side comparison proving how changing a table's distribution key eliminates network redistribution and produces collocated joins.
  • Secret Mission: Build a pre-aggregated summary table using CTAS and compare single-table scan performance against multi-join star-schema queries.

Are there any prerequisites?

You need an AWS account that has never used Redshift Serverless before (this qualifies you for the $300 free trial credit, valid for 90 days). You should also be comfortable writing SQL queries with joins and aggregations.

Set Up Redshift Serverless and Connect

Before you can design schemas or load data, you need a running data warehouse. Amazon Redshift Serverless handles all the infrastructure provisioning for you. No cluster sizing or node selection required.

In this step, you'll create a Redshift Serverless workgroup using the free trial, give it permission to read from S3, and connect through the built-in Query Editor v2.

In this step, get ready to:
  • Create a Redshift Serverless workgroup with the free trial.
  • Attach an IAM role for S3 bucket access.
  • Connect to your data warehouse via Query Editor v2.
Create a Redshift Serverless workgroup using the free trial

Redshift Serverless offers a free trial with $300 of credit over 90 days. The "Use default settings" option creates both a namespace (your database objects and users) and a workgroup (your compute resources) in one click.

  • Sign in to the AWS Management Console.
  • Open the Amazon Redshift console at https://console.aws.amazon.com/redshiftv2/.
  • Choose Try Redshift Serverless Free Trial.
  • Under Configuration, choose Use default settings.
  • Choose Save configuration.
  • Wait until the workgroup status shows as Available. This may take a few minutes.

What are namespaces and workgroups?

A namespace is a collection of database objects (tables, schemas, users). A workgroup is a collection of compute resources that run your queries.

The default setup creates default-namespace and default-workgroup for you. Your data lives in the namespace. Your queries run on the workgroup.

What about the free trial?

The Redshift Serverless free trial gives you $300 of credit that covers both compute and storage for 90 days. You're eligible if your AWS account has never used Redshift Serverless before.

Redshift Serverless only charges when queries are running (billed per second with a 60-second minimum). When idle, there are no compute charges.

Not seeing the free trial option?

If you don't see the free trial banner, your account may have previously used Redshift Serverless. You can still complete this project using on-demand pricing (starts at $1.50/hour when queries run), but be aware of potential charges.

If you see a VPC-related error, your default VPC needs at least three subnets across three availability zones with available IP addresses. Most accounts have this by default.

help me troubleshoot Redshift Serverless setup issues

Attach an IAM role for S3 access

Later in the project, you'll load data from a public S3 bucket using the COPY command. For Redshift to read from S3, it needs an IAM role with the right permissions attached to your namespace.

  • In the Redshift Serverless console, choose Namespace configuration from the left navigation.
  • Select default-namespace.
  • Choose the Security and encryption tab.
  • Choose Manage IAM roles.
  • Choose Create IAM role.
  • For S3 bucket access level, select Any S3 bucket.
  • Choose Create IAM role as default.
  • Choose Save changes.

Why set it as the default IAM role?

When you set an IAM role as the default for a namespace, your COPY commands can use the shorthand IAM_ROLE default instead of pasting the full role ARN every time. This keeps your SQL cleaner and portable.

Connect via Query Editor v2

Query Editor v2 is a browser-based SQL editor built into the Redshift console. It connects directly to your workgroup so you can write and run SQL without installing any client tools.

  • In the Redshift Serverless console, choose Query data to launch Query Editor v2.
  • In the tree-view panel on the left, choose the default-workgroup name.
  • For authentication, leave Federated user selected.
  • Choose Create connection.

Now verify your connection is working. Run the following query in the editor:

  • Verify your connection by running this query:
SELECT current_database(), current_user;

What should I see?

The query should return one row showing dev as the current database and your federated user identity as the current user.

The dev database is the default database that Redshift Serverless creates automatically inside your namespace. All your tables will live here.

Connection not working?

Make sure you selected default-workgroup in the tree-view panel, not a different workgroup or the namespace name.

If you see an authentication error, confirm that Federated user is selected in the connection dialog. This uses your current AWS console session for authentication.

help me connect to Redshift Serverless via Query Editor v2

Your data warehouse is up and running. Next up, you'll design and create tables with intentional distribution and sort key choices that control how Redshift physically stores your data across compute nodes.

Design and Create Tables with Distribution and Sort Keys

Your Redshift Serverless environment is connected and ready for queries. Now it's time to design the database schema that holds our ticket-sales data.

In a traditional row-based database, table design focuses on normalization and indexing. Redshift adds another dimension: you control how data is physically distributed across compute nodes. The right distribution key and sort key choices determine whether joins require expensive network shuffling or happen locally on a single node.

In this step, get ready to:
  • Create dimension tables with distribution and sort key choices.
  • Create fact tables with distribution and sort key choices.
  • Verify table creation using system views.
Create dimension tables with distribution and sort key choices

The TICKIT dataset follows a star schema pattern: small dimension tables describe entities (categories, dates, users) and larger fact tables record transactions (events, sales).

For dimension tables, you place the DISTKEY and SORTKEY on the primary key column because that is the column fact tables join against.

  • In Query Editor v2, clear any existing query text and paste the following SQL to create the three dimension tables:
-- Dimension tables

CREATE TABLE category(
    catid smallint not null distkey sortkey,
    catgroup varchar(10),
    catname varchar(10),
    catdesc varchar(50)
);

CREATE TABLE date(
    dateid smallint not null distkey sortkey,
    caldate date not null,
    day character(3) not null,
    week smallint not null,
    month character(5) not null,
    qtr character(5) not null,
    year smallint not null,
    holiday boolean default('N')
);

CREATE TABLE users(
    userid integer not null distkey sortkey,
    username char(8),
    firstname varchar(30),
    lastname varchar(30),
    city varchar(30),
    state char(2),
    email varchar(100),
    phone char(14),
    likesports boolean,
    liketheatre boolean,
    likeconcerts boolean,
    likejazz boolean,
    likeclassical boolean,
    likeopera boolean,
    likerock boolean,
    likevegas boolean,
    likebroadway boolean,
    likemusicals boolean
);

What do DISTKEY and SORTKEY do?

DISTKEY tells Redshift which column to use when distributing rows across compute nodes. Rows with the same distribution key value land on the same node, so joins on that column happen locally without network traffic.

SORTKEY controls the physical order of rows on disk within each node. When you filter on the sort key column, Redshift can skip entire disk blocks that fall outside your range.

For these dimension tables, both keys are on the same primary key column. This means joins from fact tables and lookups by ID are both optimized.

  • Click the **Run** button (or press Ctrl+Enter) to execute all three statements.
  • You should see three success messages in the results panel, one for each CREATE TABLE.

Seeing a "relation already exists" error?

This means the table was already created from a previous attempt. Run DROP TABLE category; (or whichever table name is in the error) and then re-run the CREATE statement.

Help me fix a CREATE TABLE error in Redshift Query Editor v2.

Create fact tables with distribution and sort key choices

Fact tables record transactional data and are typically much larger than dimensions. Their distribution key choice is more impactful because shuffling millions of rows across nodes is expensive.

  • Below your dimension table statements in Query Editor v2, paste the following SQL for the two fact tables:
-- Fact tables

CREATE TABLE event(
    eventid integer not null distkey,
    venueid smallint not null,
    catid smallint not null,
    dateid smallint not null sortkey,
    eventname varchar(200),
    starttime timestamp
);

CREATE TABLE sales(
    salesid integer not null,
    listid integer not null distkey,
    sellerid integer not null,
    buyerid integer not null,
    eventid integer not null,
    dateid smallint not null sortkey,
    qtysold smallint not null,
    pricepaid decimal(8,2),
    commission decimal(8,2),
    saletime timestamp
);

Why do these tables use different columns for DISTKEY and SORTKEY?

Unlike the dimension tables, fact tables separate the distribution key from the sort key because joins and filters target different columns.

  • event uses eventid as DISTKEY (collocates with sales joins) and dateid as SORTKEY (optimizes time-range scans).
  • sales uses listid as DISTKEY (optimizes listing-based queries) and dateid as SORTKEY (optimizes time-range filtering).

The sales table's choice of listid as DISTKEY is intentionally suboptimal for event-based joins. In Step 5, you will redesign this choice and see the performance impact firsthand.

  • Select just the two new CREATE TABLE statements and click **Run**.
  • You should see two success messages in the results panel.

Seeing a syntax error?

Check that each CREATE TABLE statement ends with a semicolon. Also confirm that distkey and sortkey appear inline with the column definition, not as separate clauses.

Help me debug my Redshift CREATE TABLE syntax.

Verify table creation with system views

Redshift provides system views that let you inspect how your tables are physically configured. The SVV_TABLE_INFO view shows distribution style, sort keys, row counts, and storage metrics for every table in your database.

  • Run the following query to confirm all 5 tables were created with the correct distribution and sort key settings:
-- Verify table creation
SELECT "table", diststyle, sortkey1, tbl_rows
FROM svv_table_info
WHERE schema = 'public'
ORDER BY "table";

What should I see?

The query returns one row per table. Look at these columns:

  • diststyle should show KEY for all 5 tables (meaning distribution is based on a specific column).
  • sortkey1 should show the first sort key column name for each table (catid, dateid, userid, dateid, dateid).
  • tbl_rows should show 0 for all tables since you have not loaded data yet.

What is KEY vs ALL vs EVEN distribution?

KEY means rows are distributed by the value of a specific column (your DISTKEY). Matching values end up on the same node.

ALL copies the entire table to every node. This is useful for very small lookup tables but wastes storage at scale. EVEN distributes rows round-robin with no column awareness. It guarantees no skew but forces redistribution on every join.

✔️ Awesome, I've got everything!

Great. Make sure all your SQL is saved in the Query Editor v2 tab for reference in later steps.

ⓧ I'd like to double check the full code

Here is the complete SQL for this step. Compare it against what you have in Query Editor v2.

-- Dimension tables

CREATE TABLE category(
    catid smallint not null distkey sortkey,
    catgroup varchar(10),
    catname varchar(10),
    catdesc varchar(50)
);

CREATE TABLE date(
    dateid smallint not null distkey sortkey,
    caldate date not null,
    day character(3) not null,
    week smallint not null,
    month character(5) not null,
    qtr character(5) not null,
    year smallint not null,
    holiday boolean default('N')
);

CREATE TABLE users(
    userid integer not null distkey sortkey,
    username char(8),
    firstname varchar(30),
    lastname varchar(30),
    city varchar(30),
    state char(2),
    email varchar(100),
    phone char(14),
    likesports boolean,
    liketheatre boolean,
    likeconcerts boolean,
    likejazz boolean,
    likeclassical boolean,
    likeopera boolean,
    likerock boolean,
    likevegas boolean,
    likebroadway boolean,
    likemusicals boolean
);

-- Fact tables

CREATE TABLE event(
    eventid integer not null distkey,
    venueid smallint not null,
    catid smallint not null,
    dateid smallint not null sortkey,
    eventname varchar(200),
    starttime timestamp
);

CREATE TABLE sales(
    salesid integer not null,
    listid integer not null distkey,
    sellerid integer not null,
    buyerid integer not null,
    eventid integer not null,
    dateid smallint not null sortkey,
    qtysold smallint not null,
    pricepaid decimal(8,2),
    commission decimal(8,2),
    saletime timestamp
);

-- Verify table creation
SELECT "table", diststyle, sortkey1, tbl_rows
FROM svv_table_info
WHERE schema = 'public'
ORDER BY "table";

Your schema is in place with intentional distribution and sort key choices on every table. Next up, you will bulk-load data from S3 into these tables using the COPY command and see Redshift's parallel loading in action.

Bulk Load Data from S3 Using COPY

Your tables are ready with their distribution and sort keys defining how data will be physically organized across compute nodes.

Now it is time to fill them with data. Redshift's COPY command bulk-loads data directly from S3 in parallel across all compute slices. Instead of inserting rows one at a time, COPY splits the workload so every slice loads its portion simultaneously.

In this step, get ready to:
  • Load all 5 TICKIT tables from a public S3 bucket using COPY.
  • Validate row counts and check for load errors.
Load all tables from the public S3 bucket

AWS hosts the TICKIT sample dataset in a public S3 bucket at s3://redshift-downloads/tickit/. Each table has its own data file with a specific delimiter format.

  • In Query Editor v2, open a new query tab.
  • Load the three dimension tables by running these COPY commands:
-- Load dimension tables
COPY category
FROM 's3://redshift-downloads/tickit/category_pipe.txt'
DELIMITER '|'
REGION 'us-east-1'
IAM_ROLE default;

COPY date
FROM 's3://redshift-downloads/tickit/date2008_pipe.txt'
DELIMITER '|'
REGION 'us-east-1'
IAM_ROLE default;

COPY users
FROM 's3://redshift-downloads/tickit/allusers_pipe.txt'
DELIMITER '|'
TIMEFORMAT 'YYYY-MM-DD HH:MI:SS'
IGNOREHEADER 1
REGION 'us-east-1'
IAM_ROLE default;

What do these COPY options mean?

  • DELIMITER '|' tells Redshift that columns are separated by pipe characters in the source file.
  • REGION 'us-east-1' specifies where the S3 bucket lives so Redshift connects to the right endpoint.
  • IAM_ROLE default uses the default IAM role you attached to your namespace in Step 1 for S3 access.
  • TIMEFORMAT and IGNOREHEADER 1 are needed for the users file because it contains timestamp columns and a header row.
  • Click **Run** to execute all three COPY commands.
  • You should see a completion message for each COPY in the results pane.

Seeing an access denied error?

Make sure the IAM role you created in Step 1 is set as the default role for your namespace. Return to **Namespace configuration**, select your namespace, check **Security and encryption**, and confirm the role shows as default.

help me fix an S3 access denied error on my Redshift COPY command

  • Load the two fact tables by running these COPY commands:
-- Load fact tables
COPY event
FROM 's3://redshift-downloads/tickit/allevents_pipe.txt'
DELIMITER '|'
TIMEFORMAT 'YYYY-MM-DD HH:MI:SS'
IGNOREHEADER 1
REGION 'us-east-1'
IAM_ROLE default;

COPY sales
FROM 's3://redshift-downloads/tickit/sales_tab.txt'
DELIMITER '\t'
TIMEFORMAT 'MM/DD/YYYY HH:MI:SS'
IGNOREHEADER 1
REGION 'us-east-1'
IAM_ROLE default;

Why are the fact table options different?

Both fact tables have header rows and timestamp columns, so they need IGNOREHEADER 1 and a TIMEFORMAT.

The sales file uses tab-delimited format (DELIMITER '\t') and a different timestamp pattern (MM/DD/YYYY HH:MI:SS) compared to the pipe-delimited dimension files. The COPY command adapts to whatever format the source file uses.

  • Click **Run** to execute both COPY commands.
  • You should see a completion message for each COPY in the results pane.

How does COPY load data in parallel?

COPY distributes the loading work across all compute slices in your workgroup simultaneously. Each slice reads its portion of the data file and writes rows directly to disk. This parallel loading is what makes COPY dramatically faster than inserting rows one at a time.

Seeing a data type or delimiter error?

Double check that you used DELIMITER '\t' (tab) for the sales COPY and DELIMITER '|' (pipe) for all other tables. Mixing these up causes columns to load incorrectly.

help me troubleshoot a COPY command failure in Redshift

Validate row counts and check for errors

After loading, you need to confirm that each table received the expected number of rows. A quick UNION ALL query gives you all row counts at once.

  • Verify all tables loaded correctly by running this validation query:
-- Validate row counts
SELECT 'category' AS tablename, COUNT(*) AS row_count FROM category
UNION ALL
SELECT 'date', COUNT(*) FROM date
UNION ALL
SELECT 'users', COUNT(*) FROM users
UNION ALL
SELECT 'event', COUNT(*) FROM event
UNION ALL
SELECT 'sales', COUNT(*) FROM sales
ORDER BY tablename;

What should I see?

Your results should show these approximate row counts:

  • category: 11 rows.
  • date: 365 rows.
  • users: approximately 49,990 rows.
  • event: approximately 8,798 rows.
  • sales: approximately 172,456 rows.
  • If any COPY command failed earlier, diagnose the issue by running this error check query:
-- Check for load errors (if any COPY failed)
SELECT query, filename, line_number, colname, type, col_length, raw_line, err_reason
FROM stl_load_errors
ORDER BY starttime DESC
LIMIT 10;

What does STL_LOAD_ERRORS show?

STL_LOAD_ERRORS is a system table that captures details about every row that failed to load. The err_reason column tells you exactly what went wrong (mismatched data types, extra columns, wrong delimiter). If all your loads succeeded, this query returns zero rows.

Row counts don't match?

If a table shows 0 rows, the COPY command likely failed silently. Run the stl_load_errors query above to see the specific error. Common causes are wrong delimiters or mismatched timestamp formats.

If you need to reload a table, run TRUNCATE tablename; first to clear any partial data, then re-run the COPY command.

help me fix a COPY load error in Redshift

✔️ Awesome, I've got everything!

Great. Make sure you have saved your query tab in Query Editor v2 so you can reference it later.

ⓧ I'd like to double check the full code

Here is the complete SQL for this step. Compare it against what you have in Query Editor v2:

-- Load dimension tables
COPY category
FROM 's3://redshift-downloads/tickit/category_pipe.txt'
DELIMITER '|'
REGION 'us-east-1'
IAM_ROLE default;

COPY date
FROM 's3://redshift-downloads/tickit/date2008_pipe.txt'
DELIMITER '|'
REGION 'us-east-1'
IAM_ROLE default;

COPY users
FROM 's3://redshift-downloads/tickit/allusers_pipe.txt'
DELIMITER '|'
TIMEFORMAT 'YYYY-MM-DD HH:MI:SS'
IGNOREHEADER 1
REGION 'us-east-1'
IAM_ROLE default;

-- Load fact tables
COPY event
FROM 's3://redshift-downloads/tickit/allevents_pipe.txt'
DELIMITER '|'
TIMEFORMAT 'YYYY-MM-DD HH:MI:SS'
IGNOREHEADER 1
REGION 'us-east-1'
IAM_ROLE default;

COPY sales
FROM 's3://redshift-downloads/tickit/sales_tab.txt'
DELIMITER '\t'
TIMEFORMAT 'MM/DD/YYYY HH:MI:SS'
IGNOREHEADER 1
REGION 'us-east-1'
IAM_ROLE default;

-- Validate row counts
SELECT 'category' AS tablename, COUNT(*) AS row_count FROM category
UNION ALL
SELECT 'date', COUNT(*) FROM date
UNION ALL
SELECT 'users', COUNT(*) FROM users
UNION ALL
SELECT 'event', COUNT(*) FROM event
UNION ALL
SELECT 'sales', COUNT(*) FROM sales
ORDER BY tablename;

-- Check for load errors (if any COPY failed)
SELECT query, filename, line_number, colname, type, col_length, raw_line, err_reason
FROM stl_load_errors
ORDER BY starttime DESC
LIMIT 10;

Your data warehouse now has real data to work with. Next up, you will write analytical queries against this dataset and use EXPLAIN plans to see exactly how Redshift distributes the work across nodes.

Write Analytical Queries and Read EXPLAIN Plans

Your data warehouse is loaded and ready for analysis. Now comes the payoff: writing queries that answer real business questions across multiple tables, then peeking under the hood to see how Redshift actually executes them.

Every join in a distributed database has a hidden cost. If the rows you need to join live on different compute nodes, Redshift must shuffle data across the network before it can combine them. EXPLAIN plans reveal exactly where that shuffling happens, and whether your distribution key choices are paying off.

In this step, get ready to:
  • Write multi-table join queries with aggregations and window functions.
  • Run EXPLAIN plans and identify data redistribution labels.
  • Observe how sort keys reduce the amount of data Redshift scans.
Write multi-table join and aggregation queries

The TICKIT dataset models a ticket-sales business with dimension tables (who, what, when) and fact tables (transactions). Analytical queries join these tables to answer questions like "which category earns the most revenue?" or "who are the top spenders?"

  • In Query Editor v2, open a new editor tab by clicking the + icon in the query tab bar.
  • Paste the following query that calculates total revenue and sales count by event category:
-- Query 1: Revenue by event category (3-table join)
SELECT c.catname,
       SUM(s.pricepaid) AS total_revenue,
       COUNT(s.salesid) AS total_sales
FROM sales s
JOIN event e ON s.eventid = e.eventid
JOIN category c ON e.catid = c.catid
GROUP BY c.catname
ORDER BY total_revenue DESC;

What does this query do?

This joins three tables: sales connects to event on eventid, then event connects to category on catid.

SUM(s.pricepaid) totals revenue per category, and COUNT(s.salesid) counts the number of transactions. The result shows which event categories generate the most ticket-sales income.

  • Click Run (or press Ctrl+Enter on Windows, Cmd+Enter on macOS).
  • You should see a results table with category names like Shows, Concerts, and Musicals, each with a revenue total and sales count.

Query returns zero rows?

Confirm you are connected to the dev database by checking the tree-view panel on the left. If you see a different database name, right-click your workgroup and choose Create connection again.

help me debug why my category revenue query returns no results

Next, you will write a query that shows monthly revenue trends with a cumulative running total. This uses a window function to compute a value across rows without collapsing them into a single group.

  • Paste the following query below your first one in the same editor tab:
-- Query 2: Monthly revenue trend with running total (window function)
SELECT d.month,
       d.year,
       SUM(s.pricepaid) AS monthly_revenue,
       SUM(SUM(s.pricepaid)) OVER (ORDER BY d.year, MIN(d.dateid)) AS running_total
FROM sales s
JOIN date d ON s.dateid = d.dateid
GROUP BY d.month, d.year
ORDER BY d.year, MIN(d.dateid);

What is a window function?

A window function performs a calculation across a set of rows related to the current row. Unlike GROUP BY which collapses rows, window functions keep all rows visible while adding a computed column.

SUM(SUM(s.pricepaid)) OVER (ORDER BY d.year, MIN(d.dateid)) computes a running total that accumulates month by month. The inner SUM calculates monthly revenue, and the outer SUM ... OVER adds each month's result to all previous months.

  • Highlight just this second query and click Run.
  • You should see 12 rows (one per month in 2008) with month names, monthly revenue amounts, and a running total that grows each row.

The final analytical query identifies the top spending buyers using two ranking window functions: RANK() and NTILE().

  • Paste this query below the previous two:
-- Query 3: Top 20 buyers ranked by spend (RANK and NTILE window functions)
SELECT u.firstname,
       u.lastname,
       SUM(s.pricepaid) AS total_spent,
       RANK() OVER (ORDER BY SUM(s.pricepaid) DESC) AS spending_rank,
       NTILE(100) OVER (ORDER BY SUM(s.pricepaid) DESC) AS percentile
FROM sales s
JOIN users u ON s.buyerid = u.userid
GROUP BY u.firstname, u.lastname
ORDER BY total_spent DESC
LIMIT 20;

What do RANK and NTILE do?

RANK() assigns a rank based on spending (1 = highest spender). Ties get the same rank, and the next rank skips ahead.

NTILE(100) divides all buyers into 100 equal-sized buckets (percentiles). A buyer in bucket 1 is in the top 1% of spenders. This is useful for segmenting customers into tiers.

  • Highlight this query and click Run.
  • You should see 20 rows with first names, last names, total spend amounts, a rank column (1 through 20), and a percentile column showing 1 for all top spenders.

Seeing a syntax error on the window function?

Check that the OVER clause is placed directly after the aggregate function with no comma between them. For example: RANK() OVER (ORDER BY ...) with no comma before OVER.

help me fix my window function syntax

Run EXPLAIN on the category revenue query

Running a query tells you the answer. Running EXPLAIN tells you how Redshift computed that answer. The execution plan reveals which join strategies Redshift chose and whether data had to be shuffled between compute nodes.

  • Paste this EXPLAIN statement below your existing queries and run it:
-- EXPLAIN plan for the category revenue query
EXPLAIN
SELECT c.catname,
       SUM(s.pricepaid) AS total_revenue
FROM sales s
JOIN event e ON s.eventid = e.eventid
JOIN category c ON e.catid = c.catid
GROUP BY c.catname
ORDER BY total_revenue DESC;

How do I read the EXPLAIN output?

The plan reads bottom-to-top. Look for the join nodes (XN Hash Join or XN Merge Join) and their distribution labels:

  • DS_DIST_NONE means rows are already collocated on the same node. No network traffic needed.
  • DS_DIST_INNER or DS_DIST_OUTER means one side of the join must be redistributed across nodes. This is network-expensive.
  • DS_BCAST_INNER means the inner (smaller) table is broadcast to every node. Common for small dimension tables like category.
  • Look at the results panel. Find the join between sales and event.
  • You should see a redistribution label (DS_DIST_INNER or DS_DIST_OUTER) on this join. This happens because sales is distributed on listid while event is distributed on eventid. The rows are on different nodes.
  • Find the join involving category. You should see DS_BCAST_INNER because the 11-row category table is small enough to broadcast to every node.

Why does sales-to-event require redistribution?

You designed sales with listid as its distribution key and event with eventid. When you join on eventid, Redshift cannot guarantee matching rows are on the same node. It must redistribute one table's data across the network to align them.

In Step 5, you will fix this by creating a version of sales distributed on eventid and watch the redistribution disappear.

Observe sort key effectiveness

Sort keys control the physical order of data on disk. When you filter on a sort key column, Redshift can skip entire disk blocks that fall outside your range. This is called a range-restricted scan.

  • Paste this modified EXPLAIN statement that adds a WHERE clause on dateid (the sort key for sales) and run it:
-- EXPLAIN with sort key filter (observe range-restricted scan)
EXPLAIN
SELECT c.catname,
       SUM(s.pricepaid) AS total_revenue
FROM sales s
JOIN event e ON s.eventid = e.eventid
JOIN category c ON e.catid = c.catid
WHERE s.dateid BETWEEN 1900 AND 1920
GROUP BY c.catname
ORDER BY total_revenue DESC;

What changed in this plan?

Compare the cost estimates (shown as cost= values) between your two EXPLAIN outputs. The filtered version should show lower cost estimates on the scan of sales.

Because dateid is the sort key, Redshift knows which disk blocks contain rows in the 1900-1920 range and skips everything else. Without the sort key, it would scan all ~172,000 rows.

  • Compare the two EXPLAIN outputs side by side. Notice the reduced cost on the scan node when the sort key filter is present.

✔️ Awesome, I've got everything!

Great. Make sure all five SQL statements (three analytical queries and two EXPLAIN plans) are saved in your editor tab for reference.

ⓧ I'd like to double check the full code

Here is the complete step4_analytical_queries.sql for comparison:

-- Query 1: Revenue by event category (3-table join)
SELECT c.catname,
       SUM(s.pricepaid) AS total_revenue,
       COUNT(s.salesid) AS total_sales
FROM sales s
JOIN event e ON s.eventid = e.eventid
JOIN category c ON e.catid = c.catid
GROUP BY c.catname
ORDER BY total_revenue DESC;

-- Query 2: Monthly revenue trend with running total (window function)
SELECT d.month,
       d.year,
       SUM(s.pricepaid) AS monthly_revenue,
       SUM(SUM(s.pricepaid)) OVER (ORDER BY d.year, MIN(d.dateid)) AS running_total
FROM sales s
JOIN date d ON s.dateid = d.dateid
GROUP BY d.month, d.year
ORDER BY d.year, MIN(d.dateid);

-- Query 3: Top 20 buyers ranked by spend (RANK and NTILE window functions)
SELECT u.firstname,
       u.lastname,
       SUM(s.pricepaid) AS total_spent,
       RANK() OVER (ORDER BY SUM(s.pricepaid) DESC) AS spending_rank,
       NTILE(100) OVER (ORDER BY SUM(s.pricepaid) DESC) AS percentile
FROM sales s
JOIN users u ON s.buyerid = u.userid
GROUP BY u.firstname, u.lastname
ORDER BY total_spent DESC
LIMIT 20;

-- EXPLAIN plan for the category revenue query
EXPLAIN
SELECT c.catname,
       SUM(s.pricepaid) AS total_revenue
FROM sales s
JOIN event e ON s.eventid = e.eventid
JOIN category c ON e.catid = c.catid
GROUP BY c.catname
ORDER BY total_revenue DESC;

-- EXPLAIN with sort key filter (observe range-restricted scan)
EXPLAIN
SELECT c.catname,
       SUM(s.pricepaid) AS total_revenue
FROM sales s
JOIN event e ON s.eventid = e.eventid
JOIN category c ON e.catid = c.catid
WHERE s.dateid BETWEEN 1900 AND 1920
GROUP BY c.catname
ORDER BY total_revenue DESC;

You have now seen the cost of a mismatched distribution key in action. The redistribution on the sales-to-event join is real network overhead. In the next step, you will eliminate it by redesigning the sales table's distribution key and watching DS_DIST_NONE appear in its place.

Optimize Distribution and Compare Performance

In the previous step, your EXPLAIN plans revealed something important: Redshift was redistributing data across nodes during the sales-to-event join. This redistribution happens because sales is distributed on listid while event is distributed on eventid.

In this step, you will prove that matching distribution keys eliminates that redistribution entirely. You will create a second version of the sales table distributed on eventid, run the same join query, and compare EXPLAIN plans side by side to see the difference.

In this step, get ready to:
  • Create a redesigned sales table with eventid as the distribution key.
  • Compare EXPLAIN plans between original and redesigned tables.
  • Check distribution health using system views.
Create a redesigned sales table with eventid as distribution key

The original sales table uses listid as its distribution key. This was fine for listing-based queries, but it forces Redshift to shuffle data across nodes whenever you join sales with event on eventid.

To prove the impact, you will create a new table called sales_by_event with identical columns but distributed on eventid instead. Then you will copy the existing data in without needing to re-load from S3.

  • In Query Editor v2, create the redesigned table and populate it by running this SQL:
-- Create sales table with eventid as distribution key (for collocated joins with event)
CREATE TABLE sales_by_event(
    salesid integer not null,
    listid integer not null,
    sellerid integer not null,
    buyerid integer not null,
    eventid integer not null distkey,
    dateid smallint not null sortkey,
    qtysold smallint not null,
    pricepaid decimal(8,2),
    commission decimal(8,2),
    saletime timestamp
);

-- Copy data from original sales table (no S3 reload needed)
INSERT INTO sales_by_event
SELECT * FROM sales;

What does this code do?

The CREATE TABLE statement defines sales_by_event with the same columns as sales, but places distkey on eventid instead of listid. This means rows with the same eventid will land on the same compute node as matching rows in the event table.

The INSERT INTO ... SELECT statement copies all ~172,000 rows directly from the existing sales table. Redshift redistributes the data according to the new eventid distribution key as it inserts. No S3 reload is needed.

  • Wait for both statements to complete. The INSERT may take a few seconds as Redshift redistributes the data across nodes.

Seeing a table already exists error?

If you already ran the CREATE TABLE and are re-running the full script, the table already exists. Run DROP TABLE sales_by_event; first, then re-run the CREATE and INSERT statements.

Help me troubleshoot my CREATE TABLE statement in Redshift.

Compare EXPLAIN plans for the same join query

Now for the key comparison. You will run the exact same join query twice: once against the original sales table and once against sales_by_event. The EXPLAIN plans will show you exactly where redistribution happens and where it disappears.

  • Run the EXPLAIN on the original sales table (distkey=listid) joined with event (distkey=eventid):
-- EXPLAIN: Original sales table (distkey=listid) joined with event (distkey=eventid)
-- Expect: redistribution step (DS_DIST_INNER or DS_DIST_OUTER)
EXPLAIN
SELECT e.eventname, SUM(s.pricepaid) AS revenue
FROM sales s
JOIN event e ON s.eventid = e.eventid
GROUP BY e.eventname
ORDER BY revenue DESC
LIMIT 10;

What should I see?

In the EXPLAIN output, look for the join step. You should see DS_DIST_INNER or DS_DIST_OUTER next to the Hash Join. This label means Redshift must redistribute (shuffle) one side of the join across the network to match rows between nodes.

  • Now run the same EXPLAIN using the redesigned sales_by_event table (distkey=eventid) joined with event (distkey=eventid):
-- EXPLAIN: Redesigned table (distkey=eventid) joined with event (distkey=eventid)
-- Expect: DS_DIST_NONE (collocated join, zero redistribution)
EXPLAIN
SELECT e.eventname, SUM(s.pricepaid) AS revenue
FROM sales_by_event s
JOIN event e ON s.eventid = e.eventid
GROUP BY e.eventname
ORDER BY revenue DESC
LIMIT 10;

What does DS_DIST_NONE mean?

DS_DIST_NONE means a collocated join. Because both tables are distributed on eventid, matching rows already live on the same compute node. Redshift performs the join locally with zero network shuffling.

This is the entire point of distribution key design. The collocated version eliminates an entire network shuffle step that the original had to perform.

  • Compare the two EXPLAIN outputs. The plan for sales_by_event should show DS_DIST_NONE where the original showed DS_DIST_INNER or DS_DIST_OUTER.

Both plans look the same?

Make sure the second query uses sales_by_event in the FROM clause, not sales. Also verify that the INSERT completed successfully by running SELECT COUNT(*) FROM sales_by_event; to confirm data exists.

Help me understand my EXPLAIN plan output in Redshift.

Check distribution health with system views

Choosing a good distribution key is not just about collocated joins. It also matters that data is evenly distributed across nodes. A skewed distribution key puts more rows on some nodes than others, creating bottlenecks.

  • Query SVV_TABLE_INFO to compare distribution health between both sales tables by running:
-- Compare distribution health
SELECT "table", diststyle, sortkey1, skew_rows, unsorted
FROM svv_table_info
WHERE "table" IN ('sales', 'sales_by_event');

What does this query show?

The skew_rows column shows how unevenly data is distributed across nodes. A value close to 0 means rows are spread evenly. A high value means some nodes hold disproportionately more data than others.

The eventid column has higher cardinality and more uniform distribution than listid for this dataset. The "right" distkey depends on which joins you run most often. There is no universal best choice.

  • Review the results. You should see both tables listed with their distribution style shown as KEY, and the skew_rows values for each.

Why is there no universal best distribution key?

Distribution key design is always a tradeoff. Distributing on eventid makes event-based joins collocated, but would force redistribution on any join using listid or buyerid. You pick the distkey that matches your most frequent and expensive join pattern.

SVV_TABLE_INFO returns no rows?

Check that the table names in your WHERE clause match exactly: 'sales' and 'sales_by_event'. Table names in Redshift are case-sensitive within string comparisons. Also confirm both tables exist by running SELECT DISTINCT "table" FROM svv_table_info WHERE schema = 'public';

Help me query SVV_TABLE_INFO in Redshift.

✔️ Awesome, I've got everything!

Great. Double check you have all the queries saved in your Query Editor v2 session for reference.

ⓧ I'd like to double check the full code

Here is the complete SQL for this step. Compare your queries against this:

-- Create sales table with eventid as distribution key (for collocated joins with event)
CREATE TABLE sales_by_event(
    salesid integer not null,
    listid integer not null,
    sellerid integer not null,
    buyerid integer not null,
    eventid integer not null distkey,
    dateid smallint not null sortkey,
    qtysold smallint not null,
    pricepaid decimal(8,2),
    commission decimal(8,2),
    saletime timestamp
);

-- Copy data from original sales table (no S3 reload needed)
INSERT INTO sales_by_event
SELECT * FROM sales;

-- EXPLAIN: Original sales table (distkey=listid) joined with event (distkey=eventid)
-- Expect: redistribution step (DS_DIST_INNER or DS_DIST_OUTER)
EXPLAIN
SELECT e.eventname, SUM(s.pricepaid) AS revenue
FROM sales s
JOIN event e ON s.eventid = e.eventid
GROUP BY e.eventname
ORDER BY revenue DESC
LIMIT 10;

-- EXPLAIN: Redesigned table (distkey=eventid) joined with event (distkey=eventid)
-- Expect: DS_DIST_NONE (collocated join, zero redistribution)
EXPLAIN
SELECT e.eventname, SUM(s.pricepaid) AS revenue
FROM sales_by_event s
JOIN event e ON s.eventid = e.eventid
GROUP BY e.eventname
ORDER BY revenue DESC
LIMIT 10;

-- Compare distribution health
SELECT "table", diststyle, sortkey1, skew_rows, unsorted
FROM svv_table_info
WHERE "table" IN ('sales', 'sales_by_event');

Secret mission

Build a Pre-Aggregated Summary Table

Your star-schema queries work, but every report still joins 3-4 tables at runtime. In production data warehouses, teams pre-compute common aggregations into summary tables (data marts) that trade storage for dramatically faster reads. Can you build one and prove it outperforms the live joins?

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Redshift Serverless automatically pauses compute when no queries are running, so you are not charged for idle time during the free trial. Storage charges still apply against your $300 free trial credit.

Cost warning

After the 90-day / $300 credit free trial expires, charges accrue at the on-demand rate (starts at $1.50/hour when queries are running). Delete the namespace before the trial expires if you do not plan to continue using Redshift Serverless.

Resources you used:

  • Redshift Serverless workgroup (default-workgroup) and namespace (default-namespace). Consumes RPU-hours only when queries are running. Charged against the $300 free trial credit.
  • IAM role attached to the namespace for S3 access.
  • Tables in the dev database (category, date, users, event, sales, sales_by_event, daily_event_revenue). Stored in Redshift Managed Storage, also covered by the free trial credit.

Keep everything running

No action needed. Choose this if you are still actively experimenting with your data warehouse.

  • Redshift Serverless automatically pauses compute when no queries are running. You will not be charged for idle time.
  • Storage charges continue to accrue against your free trial credit while tables exist.
  • Monitor your remaining free trial credit on the Redshift Serverless dashboard under the Free trial section.

Pause - I'll come back to this later

Redshift Serverless pauses automatically when idle. There is no manual pause action required.

  • Close the Query Editor v2 browser tab when you are done working.
  • Your tables and data remain intact. Compute charges stop as soon as queries finish.
  • Small storage charges continue against your free trial credit.

Delete - I don't want to use this again

Remove all project resources permanently. This cannot be undone.

  • In the Redshift console, choose Workgroup configuration from the left menu.
  • Select default-workgroup, then choose Delete Amazon Redshift Serverless instance.
  • Type delete in the confirmation dialog and choose Delete.
  • Wait for the workgroup to finish deleting. This takes several minutes.
  • Choose Namespace configuration from the left menu.
  • Select default-namespace, then choose Actions and select Delete namespace.
  • In the confirmation dialog, type delete and choose Delete. This removes all tables, data, and the attached IAM role association permanently.

Why delete the workgroup first?

A namespace cannot be deleted while it still has an associated workgroup. You must delete the workgroup first, wait for it to finish, then delete the namespace.

That's a Wrap!

That's a Wrap!

Nice work! You just built a fully functional analytics data warehouse on Amazon Redshift Serverless, loaded it with real data, and proved how physical data placement decisions affect query performance at scale.

You've learned how to:

  • Design a star schema with intentional distribution keys and sort keys, then bulk-load data from S3 using the COPY command with parallel loading across compute slices.
  • Write analytical queries using multi-table joins and window functions (SUM OVER, RANK, NTILE), then read EXPLAIN plans to identify data redistribution steps in Redshift's execution strategy.
  • Prove that changing a table's distribution key from listid to eventid eliminates redistribution (DS_DIST_NONE) by collocating joined rows on the same compute node.
  • Secret Mission: Built a pre-aggregated summary table using CTAS and compared single-table scan performance against multi-join star schema queries.

Ready to quiz yourself?