Star Schema Data Warehouse on Redshift

Design a star schema data warehouse and write analytical SQL on Redshift.

Introduction

30 Second Summary

Every time you order something online, a warehouse picks your item, a carrier ships it, and somewhere a database records what happened. Companies use this shipment data to find delays, cut costs, and decide which carriers to trust.

In this project, you will build a supply chain analytics data warehouse on Amazon Redshift Serverless. You will design a star schema, load realistic shipment data, and write the exact analytical SQL patterns that show up in Business Intelligence Engineer interviews.

What You'll Build

A star schema data warehouse tracking shipments across European warehouses, products, and carriers, with advanced analytical queries that demonstrate window functions, CTEs, and aggregation patterns.

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

  • A working star schema with 4 dimension tables and 1 fact table, each with documented distribution and sort key choices.
  • A collection of 8+ analytical SQL queries progressing from foundational JOINs and aggregations to window functions like LAG, RANK, and running totals.
  • Hands-on experience navigating the AWS Management Console and writing queries in Redshift Query Editor v2.
  • Secret Mission: Build a cost anomaly detection query that flags carrier cost spikes using statistical methods.

Are there any prerequisites?

None. This guide walks you through creating an AWS account and setting up Redshift Serverless from scratch. You do not need prior SQL experience, though familiarity with basic database concepts will help you move faster.

Set Up AWS Account and Redshift Serverless

Before you can build a data warehouse, you need a place to host it. Amazon Redshift Serverless is a cloud data warehouse that lets you run SQL queries without managing any infrastructure.

In this step, you will create an AWS account, activate the Redshift Serverless free trial, and connect to the Query Editor where you will write all your SQL.

In this step, get ready to:
  • Create an AWS account.
  • Activate the Redshift Serverless free trial.
  • Connect to the Query Editor v2 and select your database.
Create an AWS account

An AWS account gives you access to all of Amazon's cloud services. The free trial for Redshift Serverless provides $300 in credit for 90 days, so you will not be charged for this project.

  • Go to https://aws.amazon.com in your browser.
  • Click Create an AWS Account in the top right corner.
  • Enter your email address and choose an account name.
  • Verify your email by entering the code sent to your inbox.
  • Create a strong root user password.
  • Select Personal as your account type.
  • Enter your contact information and accept the AWS Customer Agreement.
  • Enter your credit or debit card details for payment verification.
  • Complete identity verification via SMS or voice call.
  • Select the Basic Support - Free plan.
  • Click Complete sign up.

Will I be charged?

AWS requires a payment method to create an account, but you will not be charged as long as you stay within the Redshift Serverless free trial. The trial provides $300 in credit valid for 90 days.

Account activation taking a while?

Account activation usually completes within a few minutes, but can take up to 24 hours. Check your email (including spam) for the activation confirmation before proceeding.

help me troubleshoot my AWS account activation

Activate Redshift Serverless free trial

Redshift Serverless spins up compute resources automatically when you run a query and pauses when you stop. You do not need to choose instance sizes or manage clusters.

  • Sign in to the AWS Management Console using the email and password you just created.
  • In the search bar at the top, type Redshift and select Amazon Redshift from the results.
  • Click Try Redshift Serverless Free Trial.
  • Under Configuration, choose Use default settings.
  • Click Save configuration.
  • Wait for the status to show Available. This takes 1-2 minutes.

What did default settings create?

Choosing default settings created two resources for you. A namespace called default-namespace (which holds your databases and users) and a workgroup called default-workgroup (which provides the compute power to run your queries).

Together, they form your fully managed data warehouse. The workgroup auto-pauses when idle to conserve your free trial credits.

Not seeing the free trial option?

The Redshift Serverless free trial is only available for accounts that have never used Redshift Serverless before. If you have used it previously on this account, the option will not appear.

help me access the Redshift Serverless free trial

Open the Query Editor v2

The Query Editor v2 is a web-based SQL client built into the Redshift console. You will use it for every query in this project.

  • In the Redshift console left sidebar, click Query editor v2.
  • When prompted, click Configure account to enable Query Editor v2. Leave the default AWS-owned encryption key selected.
  • In the left panel, expand Serverless: default-workgroup.
  • Click the workgroup name to connect. When prompted for authentication, leave Federated user selected and click Create connection.
  • You should see the database dev listed under the workgroup. Click it to select it.

You should now see a blank SQL tab with dev shown as your active database. You are ready to write SQL.

Why is it called dev?

Every new Redshift Serverless namespace comes with a default database named dev. This is where you will create your star schema tables in the next steps.

Cannot connect to the workgroup?

Make sure your workgroup status shows Available in the Redshift Serverless dashboard before trying to connect. If it still shows Creating, wait another minute and refresh the page.

help me connect to my Redshift Serverless workgroup in Query Editor v2

Your cloud data warehouse is live and ready. Next up, you will create the dimension tables that form the backbone of your star schema.

Create Dimension Tables

Your Redshift Serverless warehouse is live and ready for SQL. Now you need to design the tables that hold your data.

In a star schema, you start by creating dimension tables. These are small lookup tables (dates, warehouses, products, carriers) that describe the "who, what, when, where" of your business events. Once dimensions exist, you can build a central fact table that references them.

In this step, get ready to:
  • Create a date dimension table with Redshift-specific distribution settings.
  • Create warehouse, product, and carrier dimension tables.
  • Verify all four tables exist with the correct distribution style and sort keys.
Create dim_date

The date dimension is a calendar lookup table. It lets you slice shipment data by day, month, quarter, and year without doing date math in every query.

  • In the Query Editor v2, clear any existing text in the SQL tab.
  • Create the date dimension table by pasting and running this SQL:
-- Dimension Tables for Supply Chain Star Schema
-- DISTSTYLE ALL: copies small lookup tables to every node for local joins

CREATE TABLE dim_date (
    date_id INTEGER NOT NULL,
    full_date DATE NOT NULL,
    day_of_week VARCHAR(10) NOT NULL,
    month INTEGER NOT NULL,
    month_name VARCHAR(10) NOT NULL,
    quarter INTEGER NOT NULL,
    year INTEGER NOT NULL,
    PRIMARY KEY (date_id)
)
DISSTYLE ALL
SORTKEY (full_date);

What does this code do?

This creates a table called dim_date with seven columns that break a date into useful pieces (day of week, month name, quarter, year).

The two lines at the bottom are Redshift-specific performance settings:

  • DISTSTYLE ALL copies this entire table to every compute node. Because dimension tables are small, this means joins with the fact table never need to shuffle data across the network.
  • SORTKEY (full_date) physically orders rows on disk by date. When you filter queries by date range, Redshift can skip entire disk blocks that fall outside your range.
  • Click **Run** to execute the statement.
  • You should see a success message in the **Result** tab at the bottom of the editor.

Seeing an error instead of a success message?

  • Make sure you are connected to the dev database. Check the left panel shows your workgroup expanded with dev selected.
  • Check for typos in the SQL. Column names and keywords must match exactly.
  • If you see a "table already exists" message, you may have run this before. That means the table is already created and you can move on.

Help me debug my CREATE TABLE error in Redshift Query Editor v2.

Create dim_warehouse, dim_product, and dim_carrier

The remaining three dimensions follow the same pattern. Each uses DISTSTYLE ALL because they are small lookup tables.

  • Clear the editor, then create the warehouse dimension by pasting and running this SQL:
CREATE TABLE dim_warehouse (
    warehouse_id INTEGER NOT NULL,
    warehouse_name VARCHAR(50) NOT NULL,
    city VARCHAR(30) NOT NULL,
    country VARCHAR(30) NOT NULL,
    capacity_sqft INTEGER NOT NULL,
    PRIMARY KEY (warehouse_id)
)
DISSTYLE ALL
SORTKEY (country);

Why sort by country?

The SORTKEY (country) means queries that filter or group by country can skip disk blocks that don't contain the target country. In supply chain analytics, grouping shipments by country is one of the most common patterns.

  • Click **Run** and confirm you see a success message.
  • Clear the editor, then create the product dimension by pasting and running this SQL:
CREATE TABLE dim_product (
    product_id INTEGER NOT NULL,
    product_name VARCHAR(50) NOT NULL,
    category VARCHAR(30) NOT NULL,
    weight_kg DECIMAL(6,2) NOT NULL,
    unit_price DECIMAL(8,2) NOT NULL,
    PRIMARY KEY (product_id)
)
DISSTYLE ALL
SORTKEY (category);
  • Click **Run** and confirm you see a success message.
  • Clear the editor, then create the carrier dimension by pasting and running this SQL:
CREATE TABLE dim_carrier (
    carrier_id INTEGER NOT NULL,
    carrier_name VARCHAR(50) NOT NULL,
    transport_mode VARCHAR(20) NOT NULL,
    cost_per_km DECIMAL(5,3) NOT NULL,
    PRIMARY KEY (carrier_id)
)
DISSTYLE ALL
SORTKEY (transport_mode);
  • Click **Run** and confirm you see a success message.

What is a dimension table?

A dimension table holds descriptive attributes about your business entities. It answers questions like "which warehouse?", "which product?", and "which carrier?" without repeating that information in every row of the fact table.

Keeping dimensions separate from facts means you can update a carrier's name in one place and every query instantly reflects the change.

One of the tables failed to create?

  • Make sure you cleared the editor between each statement. Running two CREATE TABLE statements at once can cause the second to fail if they share the same editor tab content.
  • Double-check that parentheses and commas are correct. A missing comma after a column definition is the most common SQL syntax error.

Help me fix my dimension table creation error.

✔️ Awesome, I've got everything!

Great. Make sure all four tables completed without errors before moving on.

ⓧ I'd like to double check the full code

Here is the complete SQL for all four dimension tables. Compare it against what you ran:

-- Dimension Tables for Supply Chain Star Schema
-- DISTSTYLE ALL: copies small lookup tables to every node for local joins

CREATE TABLE dim_date (
    date_id INTEGER NOT NULL,
    full_date DATE NOT NULL,
    day_of_week VARCHAR(10) NOT NULL,
    month INTEGER NOT NULL,
    month_name VARCHAR(10) NOT NULL,
    quarter INTEGER NOT NULL,
    year INTEGER NOT NULL,
    PRIMARY KEY (date_id)
)
DISSTYLE ALL
SORTKEY (full_date);

CREATE TABLE dim_warehouse (
    warehouse_id INTEGER NOT NULL,
    warehouse_name VARCHAR(50) NOT NULL,
    city VARCHAR(30) NOT NULL,
    country VARCHAR(30) NOT NULL,
    capacity_sqft INTEGER NOT NULL,
    PRIMARY KEY (warehouse_id)
)
DISSTYLE ALL
SORTKEY (country);

CREATE TABLE dim_product (
    product_id INTEGER NOT NULL,
    product_name VARCHAR(50) NOT NULL,
    category VARCHAR(30) NOT NULL,
    weight_kg DECIMAL(6,2) NOT NULL,
    unit_price DECIMAL(8,2) NOT NULL,
    PRIMARY KEY (product_id)
)
DISSTYLE ALL
SORTKEY (category);

CREATE TABLE dim_carrier (
    carrier_id INTEGER NOT NULL,
    carrier_name VARCHAR(50) NOT NULL,
    transport_mode VARCHAR(20) NOT NULL,
    cost_per_km DECIMAL(5,3) NOT NULL,
    PRIMARY KEY (carrier_id)
)
DISSTYLE ALL
SORTKEY (transport_mode);
Verify tables were created

Now confirm that all four tables exist with the correct distribution and sort settings.

  • Clear the editor, then verify your tables by pasting and running this query:
SELECT tablename, diststyle, sortkey1
FROM svv_table_info
WHERE schema = 'public'
ORDER BY tablename;

What does this query do?

The system view svv_table_info is built into Redshift. It reports metadata about every table in your database, including the distribution style and sort key columns.

By filtering on schema = 'public', you only see the tables you created (not internal system tables).

  • Click **Run**.
  • You should see four rows in the results: dim_carrier, dim_date, dim_product, and dim_warehouse. Each should show ALL in the diststyle column.

Don't see all four tables?

  • If a table is missing, scroll up in your query history and check whether that CREATE TABLE statement produced an error.
  • Make sure the WHERE schema = 'public' clause is present. Without it, you may see system tables mixed in or no results at all.

Help me figure out why my dimension tables aren't showing up in svv_table_info.

Why doesn't Redshift enforce primary keys?

You defined PRIMARY KEY on each table, but Redshift does not enforce primary key or foreign key constraints. The query optimizer uses them when generating query plans, but it trusts you to maintain data validity.

This is a common interview question. The answer is that enforcing constraints on billions of rows would slow down bulk loads, so Redshift trades enforcement for speed.

Your dimension layer is complete. Next up, you will build the central fact table and load data into all five tables.

Build the Fact Table and Load Data

Your dimension tables define the context around business events: who shipped it, where it went, when it happened, and what product moved. The fact table is the center of your star schema. It stores the actual measurements you want to analyze.

In this step, you'll create the fact table with Redshift-specific optimizations for join speed and query filtering. Then you'll populate all five tables with realistic European supply chain data.

In this step, get ready to:
  • Create the fact_shipment table with optimized distribution and sort keys.
  • Load synthetic data into all dimension and fact tables.
  • Verify row counts across all five tables.
Create the fact_shipment table

The fact table records every shipment event with numeric measurements like quantity, cost, and delivery time. Each row points back to the dimension tables through foreign keys, forming the "star" shape of the schema.

  • Create the central fact table by pasting and running this SQL in the Query Editor:
-- Fact Table: Central shipment events
-- DISTKEY(warehouse_id): colocates with dim_warehouse for fastest joins
-- COMPOUND SORTKEY(date_id, warehouse_id): enables block-skipping on date-range queries

CREATE TABLE fact_shipment (
    shipment_id INTEGER NOT NULL,
    date_id INTEGER NOT NULL,
    warehouse_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    carrier_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    distance_km INTEGER NOT NULL,
    shipping_cost DECIMAL(10,2) NOT NULL,
    delivery_days INTEGER NOT NULL,
    is_delayed BOOLEAN NOT NULL,
    PRIMARY KEY (shipment_id),
    FOREIGN KEY (date_id) REFERENCES dim_date(date_id),
    FOREIGN KEY (warehouse_id) REFERENCES dim_warehouse(warehouse_id),
    FOREIGN KEY (product_id) REFERENCES dim_product(product_id),
    FOREIGN KEY (carrier_id) REFERENCES dim_carrier(carrier_id)
)
DISTSTYLE KEY
DISTKEY (warehouse_id)
COMPOUND SORTKEY (date_id, warehouse_id);

What do DISTKEY and SORTKEY do here?

DISTKEY(warehouse_id) tells Redshift to physically store shipment rows on the same compute node as the matching dim_warehouse row. This eliminates network shuffling when you join on warehouse_id.

COMPOUND SORTKEY (date_id, warehouse_id) physically orders rows on disk by date first, then warehouse. When you filter by a date range, Redshift can skip entire disk blocks that fall outside the range. This can eliminate up to 98% of blocks from a scan.

The foreign key constraints tell the query optimizer how tables relate. Redshift does not enforce these constraints at write time, but uses them to generate faster query plans.

  • Click Run (or press Ctrl+Enter).
  • You should see a success message in the results panel at the bottom confirming the table was created.

Seeing a relation already exists error?

This means you already ran the CREATE TABLE statement. You can drop it first with DROP TABLE fact_shipment; then re-run the CREATE statement.

If you see a reference error about a missing table, make sure all four dimension tables exist from the previous step.

Help me debug my fact table creation error.

Why DISTKEY on warehouse_id and not date_id?

Redshift best practice says to distribute the fact table on the same column as its largest dimension's primary key. In supply chain analytics, most queries group or filter by warehouse location. Distributing on warehouse_id means those joins never need to shuffle data across the network.

Date filtering is handled by the SORTKEY instead. The SORTKEY sorts data physically on disk, so date-range queries skip irrelevant blocks without needing colocation.

Load data into all tables

Dimensions must be loaded first because the fact table's foreign keys reference them. If you tried to insert a shipment pointing to a warehouse that does not exist yet, the optimizer would flag an integrity warning.

  • Load all four dimension tables by pasting and running these INSERT statements:
-- Load dimension data
INSERT INTO dim_date VALUES
(20240101, '2024-01-01', 'Monday', 1, 'January', 1, 2024),
(20240115, '2024-01-15', 'Monday', 1, 'January', 1, 2024),
(20240201, '2024-02-01', 'Thursday', 2, 'February', 1, 2024),
(20240215, '2024-02-15', 'Thursday', 2, 'February', 1, 2024),
(20240301, '2024-03-01', 'Friday', 3, 'March', 1, 2024),
(20240315, '2024-03-15', 'Friday', 3, 'March', 1, 2024),
(20240401, '2024-04-01', 'Monday', 4, 'April', 2, 2024),
(20240415, '2024-04-15', 'Monday', 4, 'April', 2, 2024),
(20240501, '2024-05-01', 'Wednesday', 5, 'May', 2, 2024),
(20240515, '2024-05-15', 'Wednesday', 5, 'May', 2, 2024),
(20240601, '2024-06-01', 'Saturday', 6, 'June', 2, 2024),
(20240615, '2024-06-15', 'Saturday', 6, 'June', 2, 2024);

INSERT INTO dim_warehouse VALUES
(1, 'Berlin Central', 'Berlin', 'Germany', 85000),
(2, 'Rotterdam Hub', 'Rotterdam', 'Netherlands', 120000),
(3, 'Lyon Depot', 'Lyon', 'France', 65000),
(4, 'Milan Store', 'Milan', 'Italy', 45000),
(5, 'Warsaw East', 'Warsaw', 'Poland', 70000);

INSERT INTO dim_product VALUES
(1, 'Laptop Pro 15', 'Electronics', 2.10, 899.99),
(2, 'Wireless Headphones', 'Electronics', 0.35, 149.99),
(3, 'Office Chair Ergo', 'Furniture', 18.50, 349.99),
(4, 'Standing Desk', 'Furniture', 35.00, 599.99),
(5, 'Running Shoes X1', 'Apparel', 0.80, 129.99),
(6, 'Winter Jacket Pro', 'Apparel', 1.20, 199.99),
(7, 'Coffee Machine DX', 'Appliances', 5.50, 249.99),
(8, 'Air Purifier 300', 'Appliances', 7.20, 179.99);

INSERT INTO dim_carrier VALUES
(1, 'EuroTrans GmbH', 'Road', 1.250),
(2, 'Nordic Rail AB', 'Rail', 0.850),
(3, 'Atlantic Freight', 'Sea', 0.450),
(4, 'SkyExpress EU', 'Air', 3.200),
(5, 'FastTrack Logistics', 'Road', 1.650);

What data did you just load?

  • dim_date: 12 rows covering January through June 2024 (1st and 15th of each month).
  • dim_warehouse: 5 European warehouses across Germany, Netherlands, France, Italy, and Poland.
  • dim_product: 8 products across 4 categories (Electronics, Furniture, Apparel, Appliances).
  • dim_carrier: 5 carriers using Road, Rail, Sea, and Air transport modes.
  • Click Run. You should see success messages for all four INSERT statements in the results panel.
  • Now load the 40 shipment records into the fact table by pasting and running this INSERT:
-- Load fact data
INSERT INTO fact_shipment VALUES
(1001, 20240101, 1, 1, 1, 50, 580, 725.00, 3, false),
(1002, 20240101, 2, 3, 2, 30, 920, 782.00, 5, false),
(1003, 20240101, 3, 5, 1, 200, 450, 562.50, 2, false),
(1004, 20240115, 1, 2, 4, 100, 1200, 3840.00, 1, false),
(1005, 20240115, 4, 7, 3, 80, 1500, 675.00, 8, true),
(1006, 20240115, 5, 4, 2, 25, 780, 663.00, 4, false),
(1007, 20240201, 2, 6, 1, 150, 340, 425.00, 2, false),
(1008, 20240201, 3, 8, 5, 60, 620, 1023.00, 3, false),
(1009, 20240201, 1, 1, 4, 40, 1100, 3520.00, 1, false),
(1010, 20240215, 4, 3, 1, 35, 890, 1112.50, 4, true),
(1011, 20240215, 5, 5, 2, 180, 560, 476.00, 3, false),
(1012, 20240215, 2, 7, 3, 90, 1400, 630.00, 7, false),
(1013, 20240301, 1, 2, 1, 120, 480, 600.00, 2, false),
(1014, 20240301, 3, 4, 5, 20, 750, 1237.50, 4, true),
(1015, 20240301, 2, 6, 2, 200, 410, 348.50, 3, false),
(1016, 20240315, 4, 8, 4, 45, 1050, 3360.00, 1, false),
(1017, 20240315, 5, 1, 1, 70, 620, 775.00, 3, false),
(1018, 20240315, 1, 3, 3, 40, 1600, 720.00, 9, true),
(1019, 20240401, 2, 5, 1, 250, 390, 487.50, 2, false),
(1020, 20240401, 3, 7, 2, 100, 850, 722.50, 4, false),
(1021, 20240401, 4, 2, 4, 80, 980, 3136.00, 1, false),
(1022, 20240415, 5, 4, 5, 15, 700, 1155.00, 3, false),
(1023, 20240415, 1, 6, 1, 180, 520, 650.00, 2, false),
(1024, 20240415, 2, 8, 3, 70, 1350, 607.50, 7, true),
(1025, 20240501, 3, 1, 4, 60, 1150, 3680.00, 1, false),
(1026, 20240501, 4, 3, 1, 45, 830, 1037.50, 4, false),
(1027, 20240501, 5, 5, 2, 220, 490, 416.50, 3, false),
(1028, 20240515, 1, 7, 5, 95, 580, 957.00, 3, false),
(1029, 20240515, 2, 4, 2, 30, 720, 612.00, 4, false),
(1030, 20240515, 3, 6, 1, 160, 430, 537.50, 2, false),
(1031, 20240601, 4, 8, 4, 55, 1080, 3456.00, 1, false),
(1032, 20240601, 5, 2, 1, 130, 550, 687.50, 3, false),
(1033, 20240601, 1, 5, 3, 190, 1450, 652.50, 8, true),
(1034, 20240615, 2, 1, 5, 75, 610, 1006.50, 3, false),
(1035, 20240615, 3, 3, 2, 40, 780, 663.00, 4, false),
(1036, 20240615, 4, 7, 1, 110, 470, 587.50, 2, false),
(1037, 20240615, 5, 4, 4, 20, 950, 3040.00, 1, false),
(1038, 20240101, 2, 8, 5, 65, 540, 891.00, 3, false),
(1039, 20240201, 4, 5, 1, 175, 680, 850.00, 3, true),
(1040, 20240301, 5, 7, 3, 85, 1320, 594.00, 7, false);

What does this data represent?

Each row is one shipment event. The columns capture the shipment ID, which date/warehouse/product/carrier it relates to (foreign keys), plus the measurements: how many units, how far, how much it cost, how long it took, and whether it was delayed.

The 40 rows span January to June 2024 across all 5 warehouses. Some shipments are marked is_delayed = true. You will use these delay flags in your analytical queries later.

  • Click Run. You should see a success message confirming 40 rows were inserted.

Seeing a foreign key or invalid reference error?

Make sure you ran the dimension INSERT statements first. The fact table references IDs that must exist in the dimension tables before loading.

If you see a "duplicate key value" error, it means the data was already loaded. Run TRUNCATE fact_shipment; to clear the table, then try the INSERT again.

Help me fix my data loading error in Redshift.

✔️ Awesome, I've got everything!

Great. All five tables should now have data. The next substep confirms the exact row counts.

ⓧ I'd like to double check the full code

Here is the complete data loading SQL. Compare your Query Editor contents against this:

-- Load dimension data
INSERT INTO dim_date VALUES
(20240101, '2024-01-01', 'Monday', 1, 'January', 1, 2024),
(20240115, '2024-01-15', 'Monday', 1, 'January', 1, 2024),
(20240201, '2024-02-01', 'Thursday', 2, 'February', 1, 2024),
(20240215, '2024-02-15', 'Thursday', 2, 'February', 1, 2024),
(20240301, '2024-03-01', 'Friday', 3, 'March', 1, 2024),
(20240315, '2024-03-15', 'Friday', 3, 'March', 1, 2024),
(20240401, '2024-04-01', 'Monday', 4, 'April', 2, 2024),
(20240415, '2024-04-15', 'Monday', 4, 'April', 2, 2024),
(20240501, '2024-05-01', 'Wednesday', 5, 'May', 2, 2024),
(20240515, '2024-05-15', 'Wednesday', 5, 'May', 2, 2024),
(20240601, '2024-06-01', 'Saturday', 6, 'June', 2, 2024),
(20240615, '2024-06-15', 'Saturday', 6, 'June', 2, 2024);

INSERT INTO dim_warehouse VALUES
(1, 'Berlin Central', 'Berlin', 'Germany', 85000),
(2, 'Rotterdam Hub', 'Rotterdam', 'Netherlands', 120000),
(3, 'Lyon Depot', 'Lyon', 'France', 65000),
(4, 'Milan Store', 'Milan', 'Italy', 45000),
(5, 'Warsaw East', 'Warsaw', 'Poland', 70000);

INSERT INTO dim_product VALUES
(1, 'Laptop Pro 15', 'Electronics', 2.10, 899.99),
(2, 'Wireless Headphones', 'Electronics', 0.35, 149.99),
(3, 'Office Chair Ergo', 'Furniture', 18.50, 349.99),
(4, 'Standing Desk', 'Furniture', 35.00, 599.99),
(5, 'Running Shoes X1', 'Apparel', 0.80, 129.99),
(6, 'Winter Jacket Pro', 'Apparel', 1.20, 199.99),
(7, 'Coffee Machine DX', 'Appliances', 5.50, 249.99),
(8, 'Air Purifier 300', 'Appliances', 7.20, 179.99);

INSERT INTO dim_carrier VALUES
(1, 'EuroTrans GmbH', 'Road', 1.250),
(2, 'Nordic Rail AB', 'Rail', 0.850),
(3, 'Atlantic Freight', 'Sea', 0.450),
(4, 'SkyExpress EU', 'Air', 3.200),
(5, 'FastTrack Logistics', 'Road', 1.650);

-- Load fact data
INSERT INTO fact_shipment VALUES
(1001, 20240101, 1, 1, 1, 50, 580, 725.00, 3, false),
(1002, 20240101, 2, 3, 2, 30, 920, 782.00, 5, false),
(1003, 20240101, 3, 5, 1, 200, 450, 562.50, 2, false),
(1004, 20240115, 1, 2, 4, 100, 1200, 3840.00, 1, false),
(1005, 20240115, 4, 7, 3, 80, 1500, 675.00, 8, true),
(1006, 20240115, 5, 4, 2, 25, 780, 663.00, 4, false),
(1007, 20240201, 2, 6, 1, 150, 340, 425.00, 2, false),
(1008, 20240201, 3, 8, 5, 60, 620, 1023.00, 3, false),
(1009, 20240201, 1, 1, 4, 40, 1100, 3520.00, 1, false),
(1010, 20240215, 4, 3, 1, 35, 890, 1112.50, 4, true),
(1011, 20240215, 5, 5, 2, 180, 560, 476.00, 3, false),
(1012, 20240215, 2, 7, 3, 90, 1400, 630.00, 7, false),
(1013, 20240301, 1, 2, 1, 120, 480, 600.00, 2, false),
(1014, 20240301, 3, 4, 5, 20, 750, 1237.50, 4, true),
(1015, 20240301, 2, 6, 2, 200, 410, 348.50, 3, false),
(1016, 20240315, 4, 8, 4, 45, 1050, 3360.00, 1, false),
(1017, 20240315, 5, 1, 1, 70, 620, 775.00, 3, false),
(1018, 20240315, 1, 3, 3, 40, 1600, 720.00, 9, true),
(1019, 20240401, 2, 5, 1, 250, 390, 487.50, 2, false),
(1020, 20240401, 3, 7, 2, 100, 850, 722.50, 4, false),
(1021, 20240401, 4, 2, 4, 80, 980, 3136.00, 1, false),
(1022, 20240415, 5, 4, 5, 15, 700, 1155.00, 3, false),
(1023, 20240415, 1, 6, 1, 180, 520, 650.00, 2, false),
(1024, 20240415, 2, 8, 3, 70, 1350, 607.50, 7, true),
(1025, 20240501, 3, 1, 4, 60, 1150, 3680.00, 1, false),
(1026, 20240501, 4, 3, 1, 45, 830, 1037.50, 4, false),
(1027, 20240501, 5, 5, 2, 220, 490, 416.50, 3, false),
(1028, 20240515, 1, 7, 5, 95, 580, 957.00, 3, false),
(1029, 20240515, 2, 4, 2, 30, 720, 612.00, 4, false),
(1030, 20240515, 3, 6, 1, 160, 430, 537.50, 2, false),
(1031, 20240601, 4, 8, 4, 55, 1080, 3456.00, 1, false),
(1032, 20240601, 5, 2, 1, 130, 550, 687.50, 3, false),
(1033, 20240601, 1, 5, 3, 190, 1450, 652.50, 8, true),
(1034, 20240615, 2, 1, 5, 75, 610, 1006.50, 3, false),
(1035, 20240615, 3, 3, 2, 40, 780, 663.00, 4, false),
(1036, 20240615, 4, 7, 1, 110, 470, 587.50, 2, false),
(1037, 20240615, 5, 4, 4, 20, 950, 3040.00, 1, false),
(1038, 20240101, 2, 8, 5, 65, 540, 891.00, 3, false),
(1039, 20240201, 4, 5, 1, 175, 680, 850.00, 3, true),
(1040, 20240301, 5, 7, 3, 85, 1320, 594.00, 7, false);
Verify the data load

A quick count query across all tables confirms everything loaded correctly.

  • Confirm the row counts by pasting and running this query:
SELECT 'fact_shipment' AS table_name, COUNT(*) AS row_count FROM fact_shipment
UNION ALL
SELECT 'dim_date', COUNT(*) FROM dim_date
UNION ALL
SELECT 'dim_warehouse', COUNT(*) FROM dim_warehouse
UNION ALL
SELECT 'dim_product', COUNT(*) FROM dim_product
UNION ALL
SELECT 'dim_carrier', COUNT(*) FROM dim_carrier;

What should I see?

The results panel should show five rows with these exact counts:

  • fact_shipment: 40 rows.
  • dim_date: 12 rows.
  • dim_warehouse: 5 rows.
  • dim_product: 8 rows.
  • dim_carrier: 5 rows.

Seeing 0 rows for a table?

Go back to the INSERT statement for that table and run it again. Make sure you selected the full statement (including the final semicolon) before clicking Run.

If you see duplicate counts (e.g. 80 for fact_shipment), the INSERT ran twice. Run TRUNCATE <table_name>; and re-run the INSERT once.

Help me fix my row counts in Redshift.

Your data warehouse is fully loaded. Next up, you'll write analytical queries that join across these tables to uncover supply chain insights.

Write Foundational Analytical Queries

Your star schema is loaded with 40 shipment records across five European warehouses, eight products, and five carriers. The structure is ready to answer real business questions.

In this step, you will write the bread-and-butter analytical queries that every Business Intelligence Engineer interview starts with. These queries combine data from multiple tables using JOINs, aggregate it with GROUP BY, and filter the results with HAVING.

In this step, get ready to:
  • Write multi-table JOIN queries with COUNT, SUM, AVG, and CASE aggregations.
  • Write filtered aggregation queries using HAVING to surface only meaningful results.
Write multi-table JOINs and aggregations

The power of a star schema is that dimension tables give business context to the raw numbers in the fact table. By joining fact_shipment to dimension tables, you can answer questions like "which country spends the most on shipping?" instead of just looking at warehouse IDs.

  • In the Query Editor, clear any existing SQL and paste this query to see total shipping cost by country:
-- Query 1: Total shipping cost by country
SELECT
    w.country,
    COUNT(*) AS total_shipments,
    SUM(f.shipping_cost) AS total_cost,
    ROUND(AVG(f.shipping_cost), 2) AS avg_cost_per_shipment
FROM fact_shipment f
JOIN dim_warehouse w ON f.warehouse_id = w.warehouse_id
GROUP BY w.country
ORDER BY total_cost DESC;
  • Click Run (or press Ctrl+Enter) to execute the query.

What does this query do?

  • JOIN dim_warehouse w ON f.warehouse_id = w.warehouse_id connects each shipment to its warehouse, pulling in the country name.
  • GROUP BY w.country collapses all shipments from the same country into one row.
  • COUNT(*), SUM(), and AVG() produce three different measures per country: volume, total spend, and average spend per shipment.

You should see 5 rows (one per country), ordered by total cost descending. The Netherlands should appear near the top because Rotterdam Hub handles a large volume of shipments.

Seeing an error instead of results?

  • Check that you are connected to the dev database. The left panel should show dev highlighted under your workgroup.
  • If you see "relation does not exist", the tables from Step 3 were not created. Re-run the CREATE TABLE and INSERT statements from the previous step.
  • help me debug a SQL error in Redshift Query Editor v2
  • Clear the editor and paste this query to find the top 3 carriers by shipment volume, including their delay rate:
-- Query 2: Top 3 carriers by volume with delay rate
SELECT
    c.carrier_name,
    c.transport_mode,
    COUNT(*) AS shipment_count,
    SUM(CASE WHEN f.is_delayed THEN 1 ELSE 0 END) AS delayed_count,
    ROUND(100.0 * SUM(CASE WHEN f.is_delayed THEN 1 ELSE 0 END) / COUNT(*), 1) AS delay_pct
FROM fact_shipment f
JOIN dim_carrier c ON f.carrier_id = c.carrier_id
GROUP BY c.carrier_name, c.transport_mode
ORDER BY shipment_count DESC
LIMIT 3;
  • Click Run to execute.

What does this query do?

  • CASE WHEN f.is_delayed THEN 1 ELSE 0 END converts the boolean into a number so you can sum it. This is the standard SQL pattern for calculating percentages from boolean columns.
  • LIMIT 3 keeps only the top 3 carriers after ordering by shipment count.
  • delay_pct tells you what percentage of each carrier's shipments arrived late. A high delay percentage combined with high volume is a red flag for supply chain managers.

You should see 3 rows showing carrier name, transport mode, shipment count, delayed count, and delay percentage. EuroTrans GmbH (Road) should appear at the top with the highest volume.

Getting NULL for delay_pct?

  • Make sure you typed 100.0 (with the decimal) in the percentage calculation. Without the decimal, SQL performs integer division and rounds down to 0.
  • Check that the CASE expression uses f.is_delayed (not is_delayed = true). Redshift evaluates booleans directly.
  • help me fix my CASE WHEN percentage calculation
Filter aggregations with HAVING

WHERE filters individual rows before aggregation. HAVING filters groups after aggregation. This distinction is one of the most commonly tested SQL concepts in interviews.

  • Clear the editor and paste this query to find product categories where average delivery exceeds 3 days:
-- Query 3: Product categories where avg delivery exceeds 3 days
SELECT
    p.category,
    ROUND(AVG(f.delivery_days), 1) AS avg_delivery_days,
    COUNT(*) AS shipment_count,
    SUM(f.quantity) AS total_units_shipped
FROM fact_shipment f
JOIN dim_product p ON f.product_id = p.product_id
GROUP BY p.category
HAVING AVG(f.delivery_days) > 3
ORDER BY avg_delivery_days DESC;
  • Click Run to execute.

What does this query do?

  • HAVING AVG(f.delivery_days) > 3 removes categories whose average delivery is 3 days or less. You only see the slow categories.
  • This surfaces problem areas. If a category averages over 3 days, a supply chain manager would investigate which carriers or routes are causing delays.

You should see only the categories that exceed the 3-day threshold. Categories with fast average delivery times are filtered out entirely.

Seeing zero rows returned?

  • If all categories have average delivery under 3 days, check that your fact_shipment data loaded correctly. Run SELECT COUNT(*) FROM fact_shipment; and confirm you have 40 rows.
  • Make sure HAVING references the aggregate function AVG(f.delivery_days) directly, not the alias avg_delivery_days. Redshift requires the full expression in HAVING clauses.
  • help me understand why my HAVING clause returns no rows
  • Clear the editor and paste this query to see the monthly cost breakdown for months where total shipping exceeded 5000:
-- Query 4: Monthly cost (only months exceeding 5000)
SELECT
    d.month_name,
    d.month,
    COUNT(*) AS shipments,
    SUM(f.shipping_cost) AS monthly_cost
FROM fact_shipment f
JOIN dim_date d ON f.date_id = d.date_id
GROUP BY d.month_name, d.month
HAVING SUM(f.shipping_cost) > 5000
ORDER BY d.month;
  • Click Run to execute.

What does this query do?

  • This joins to dim_date to get human-readable month names instead of raw date IDs.
  • HAVING SUM(f.shipping_cost) > 5000 surfaces only the high-spend months. Low-spend months are excluded so you can focus on where the big costs are.
  • ORDER BY d.month sorts chronologically so you can spot trends over time.

You should see rows for the months that exceeded the 5000 threshold, ordered from January through June. Each row shows the month name, shipment count, and total monthly cost.

Month names showing as NULL?

  • Verify the JOIN condition uses f.date_id = d.date_id (not f.date_id = d.full_date). The date_id column is an integer key, not a date value.
  • Make sure d.month_name appears in both the SELECT and GROUP BY clauses. Any column in SELECT that is not inside an aggregate must be in GROUP BY.
  • help me fix NULL values in my GROUP BY query results

✔️ Awesome, I've got everything!

Great. Make sure all four queries ran successfully and you can see results for each one.

ⓧ I'd like to double check the full code

Here are all four foundational queries together. You can run them one at a time or paste the full block and highlight individual queries to run them separately.

-- Query 1: Total shipping cost by country
SELECT
    w.country,
    COUNT(*) AS total_shipments,
    SUM(f.shipping_cost) AS total_cost,
    ROUND(AVG(f.shipping_cost), 2) AS avg_cost_per_shipment
FROM fact_shipment f
JOIN dim_warehouse w ON f.warehouse_id = w.warehouse_id
GROUP BY w.country
ORDER BY total_cost DESC;

-- Query 2: Top 3 carriers by volume with delay rate
SELECT
    c.carrier_name,
    c.transport_mode,
    COUNT(*) AS shipment_count,
    SUM(CASE WHEN f.is_delayed THEN 1 ELSE 0 END) AS delayed_count,
    ROUND(100.0 * SUM(CASE WHEN f.is_delayed THEN 1 ELSE 0 END) / COUNT(*), 1) AS delay_pct
FROM fact_shipment f
JOIN dim_carrier c ON f.carrier_id = c.carrier_id
GROUP BY c.carrier_name, c.transport_mode
ORDER BY shipment_count DESC
LIMIT 3;

-- Query 3: Product categories where avg delivery exceeds 3 days
SELECT
    p.category,
    ROUND(AVG(f.delivery_days), 1) AS avg_delivery_days,
    COUNT(*) AS shipment_count,
    SUM(f.quantity) AS total_units_shipped
FROM fact_shipment f
JOIN dim_product p ON f.product_id = p.product_id
GROUP BY p.category
HAVING AVG(f.delivery_days) > 3
ORDER BY avg_delivery_days DESC;

-- Query 4: Monthly cost (only months exceeding 5000)
SELECT
    d.month_name,
    d.month,
    COUNT(*) AS shipments,
    SUM(f.shipping_cost) AS monthly_cost
FROM fact_shipment f
JOIN dim_date d ON f.date_id = d.date_id
GROUP BY d.month_name, d.month
HAVING SUM(f.shipping_cost) > 5000
ORDER BY d.month;

You now have four working analytical queries that join, aggregate, and filter real supply chain data. Next up, you will level up to window functions and CTEs, the advanced patterns that separate junior from senior candidates in interviews.

Write Advanced Analytical Queries

Your foundational queries proved you can join tables, aggregate data, and filter groups. Those patterns answer "what happened" questions. Now you need to answer "how is it changing" and "what ranks highest" questions.

Window functions let you compare rows against each other without collapsing them into groups. They are the single most tested SQL concept in Business Intelligence Engineer interviews.

In this step, get ready to:
  • Write window function queries using LAG and RANK to compare rows.
  • Build running totals and partitioned rankings with DENSE_RANK and ROWS UNBOUNDED PRECEDING.
Write window function queries using LAG and RANK

A window function performs a calculation across a set of rows that are related to the current row. Unlike GROUP BY, which collapses rows into one output row per group, window functions keep every row visible while adding a computed column alongside it.

  • In the Query Editor, paste and run Query 5 to calculate month-over-month cost changes using LAG:
-- Query 5: Month-over-month cost change using LAG
WITH monthly_costs AS (
    SELECT
        d.month,
        d.month_name,
        SUM(f.shipping_cost) AS total_cost
    FROM fact_shipment f
    JOIN dim_date d ON f.date_id = d.date_id
    GROUP BY d.month, d.month_name
)
SELECT
    month_name,
    total_cost,
    LAG(total_cost) OVER (ORDER BY month) AS prev_month_cost,
    ROUND(100.0 * (total_cost - LAG(total_cost) OVER (ORDER BY month))
        / LAG(total_cost) OVER (ORDER BY month), 1) AS mom_change_pct
FROM monthly_costs
ORDER BY month;

What does this query do?

This query introduces two powerful patterns at once: a CTE and a window function.

  • The WITH monthly_costs AS (...) block is a CTE (Common Table Expression). It creates a temporary named result set you can reference in the main query, like a subquery with a name.
  • LAG(total_cost) OVER (ORDER BY month) looks at the previous row's value. It lets you compare each month to the one before it without a self-join.
  • The mom_change_pct column calculates the percentage difference between this month and last month. January shows NULL because there is no previous month to compare.
  • Click Run in the Query Editor toolbar.
  • You should see 6 rows (one per month) with columns for total cost, previous month cost, and the percentage change between them.

Seeing NULL for every row?

If all rows show NULL in the prev_month_cost column, check that the ORDER BY month clause is inside the OVER() parentheses. Without it, LAG does not know which row is "previous."

Help me debug my LAG window function query

  • Clear the editor, then paste and run Query 6 to rank warehouses by cost efficiency:
-- Query 6: Rank warehouses by cost efficiency
SELECT
    w.warehouse_name,
    w.country,
    SUM(f.shipping_cost) AS total_cost,
    SUM(f.quantity) AS total_units,
    ROUND(SUM(f.shipping_cost) / SUM(f.quantity), 2) AS cost_per_unit,
    RANK() OVER (ORDER BY SUM(f.shipping_cost) / SUM(f.quantity) ASC) AS efficiency_rank
FROM fact_shipment f
JOIN dim_warehouse w ON f.warehouse_id = w.warehouse_id
GROUP BY w.warehouse_name, w.country
ORDER BY efficiency_rank;

What does this query do?

  • RANK() OVER (ORDER BY ... ASC) assigns a rank number to each row based on its cost-per-unit value. The lowest cost per unit gets rank 1 (most efficient).
  • Unlike the CTE approach in Query 5, this query applies the window function directly to the aggregated result. The GROUP BY runs first, then RANK operates on the grouped output.
  • If two warehouses had the same cost-per-unit, RANK would give them the same number and skip the next rank (e.g., 1, 2, 2, 4).
  • Click Run.
  • You should see 5 rows (one per warehouse) ranked from most to least efficient by cost per unit shipped.

Getting a column must appear in GROUP BY error?

Make sure w.warehouse_name and w.country are both listed in the GROUP BY clause. Every non-aggregated column in your SELECT must appear in GROUP BY.

Help me fix my RANK window function query

Write running totals and cumulative analysis queries

Running totals show how a metric accumulates over time. They answer questions like "how much have we spent so far this year?" DENSE_RANK with PARTITION BY lets you find the top item within each category without writing separate queries per category.

  • Clear the editor, then paste and run Query 7 to calculate a cumulative running total of shipping costs:
-- Query 7: Cumulative shipping cost (running total)
SELECT
    d.full_date,
    d.month_name,
    SUM(f.shipping_cost) AS daily_cost,
    SUM(SUM(f.shipping_cost)) OVER (ORDER BY d.full_date ROWS UNBOUNDED PRECEDING) AS cumulative_cost
FROM fact_shipment f
JOIN dim_date d ON f.date_id = d.date_id
GROUP BY d.full_date, d.month_name
ORDER BY d.full_date;

What does this query do?

  • SUM(SUM(f.shipping_cost)) OVER (...) is a nested aggregate. The inner SUM groups costs by date. The outer SUM is a window function that adds up all previous daily costs.
  • ROWS UNBOUNDED PRECEDING tells the window to include every row from the very first row up to the current row. This is what creates the "running" total that grows with each date.
  • The last row shows the grand total of all shipping costs across the entire dataset.
  • Click Run.
  • You should see 12 rows (one per date in dim_date) with the cumulative_cost column growing larger on each row.

Seeing the same value in every row of cumulative_cost?

Check that you have ROWS UNBOUNDED PRECEDING in the OVER clause. Without it, the default frame may include all rows in the partition, giving you the grand total on every row instead of a running total.

Help me debug my running total query

  • Clear the editor, then paste and run Query 8 to find the top product in each category by revenue using DENSE_RANK:
-- Query 8: Top product per category by revenue
WITH product_revenue AS (
    SELECT
        p.category,
        p.product_name,
        SUM(f.quantity * p.unit_price) AS total_revenue,
        DENSE_RANK() OVER (
            PARTITION BY p.category
            ORDER BY SUM(f.quantity * p.unit_price) DESC
        ) AS category_rank
    FROM fact_shipment f
    JOIN dim_product p ON f.product_id = p.product_id
    GROUP BY p.category, p.product_name
)
SELECT category, product_name, total_revenue, category_rank
FROM product_revenue
WHERE category_rank = 1
ORDER BY total_revenue DESC;

What does this query do?

  • PARTITION BY p.category restarts the ranking within each product category. Electronics products are ranked against other Electronics. Furniture against Furniture.
  • DENSE_RANK differs from RANK in how it handles ties. DENSE_RANK never skips numbers (1, 2, 2, 3) while RANK does (1, 2, 2, 4). For "top N" queries, DENSE_RANK is usually what you want.
  • The outer query filters to WHERE category_rank = 1 to show only the top-revenue product in each category.
  • Click Run.
  • You should see 4 rows, one per category (Appliances, Apparel, Electronics, Furniture), each showing the highest-revenue product in that category.

Getting more than 4 rows?

If you see all products listed instead of just the top one per category, check that the WHERE category_rank = 1 filter is in the outer SELECT, not inside the CTE. The CTE ranks everything. The outer query filters to winners only.

Help me fix my DENSE_RANK partitioned query

✔️ Awesome, I've got everything!

Great. Make sure all four advanced queries ran successfully and returned results you can interpret.

ⓧ I'd like to double check the full code

Here is the complete 05_advanced_queries.sql file with all four queries:

-- Query 5: Month-over-month cost change using LAG
WITH monthly_costs AS (
    SELECT
        d.month,
        d.month_name,
        SUM(f.shipping_cost) AS total_cost
    FROM fact_shipment f
    JOIN dim_date d ON f.date_id = d.date_id
    GROUP BY d.month, d.month_name
)
SELECT
    month_name,
    total_cost,
    LAG(total_cost) OVER (ORDER BY month) AS prev_month_cost,
    ROUND(100.0 * (total_cost - LAG(total_cost) OVER (ORDER BY month))
        / LAG(total_cost) OVER (ORDER BY month), 1) AS mom_change_pct
FROM monthly_costs
ORDER BY month;

-- Query 6: Rank warehouses by cost efficiency
SELECT
    w.warehouse_name,
    w.country,
    SUM(f.shipping_cost) AS total_cost,
    SUM(f.quantity) AS total_units,
    ROUND(SUM(f.shipping_cost) / SUM(f.quantity), 2) AS cost_per_unit,
    RANK() OVER (ORDER BY SUM(f.shipping_cost) / SUM(f.quantity) ASC) AS efficiency_rank
FROM fact_shipment f
JOIN dim_warehouse w ON f.warehouse_id = w.warehouse_id
GROUP BY w.warehouse_name, w.country
ORDER BY efficiency_rank;

-- Query 7: Cumulative shipping cost (running total)
SELECT
    d.full_date,
    d.month_name,
    SUM(f.shipping_cost) AS daily_cost,
    SUM(SUM(f.shipping_cost)) OVER (ORDER BY d.full_date ROWS UNBOUNDED PRECEDING) AS cumulative_cost
FROM fact_shipment f
JOIN dim_date d ON f.date_id = d.date_id
GROUP BY d.full_date, d.month_name
ORDER BY d.full_date;

-- Query 8: Top product per category by revenue
WITH product_revenue AS (
    SELECT
        p.category,
        p.product_name,
        SUM(f.quantity * p.unit_price) AS total_revenue,
        DENSE_RANK() OVER (
            PARTITION BY p.category
            ORDER BY SUM(f.quantity * p.unit_price) DESC
        ) AS category_rank
    FROM fact_shipment f
    JOIN dim_product p ON f.product_id = p.product_id
    GROUP BY p.category, p.product_name
)
SELECT category, product_name, total_revenue, category_rank
FROM product_revenue
WHERE category_rank = 1
ORDER BY total_revenue DESC;

Secret mission

Build a Cost Anomaly Detection Query

Your warehouse is running smoothly, but something is off. Some shipments are costing way more than they should for their carrier. Can you build a query that automatically flags these cost anomalies using statistical methods?

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. This project uses your $300 Amazon Redshift Serverless free trial credit, which covers 90 days of usage from sign-up.

Cost warning

Your Redshift Serverless workgroup consumes free trial credit while active. After the 90-day trial expires, you pay for workloads in RPU-hours on a per-second basis.

You can view your free trial credit balance in the Redshift console. Billing details for free trial usage do not appear in the billing console until after the trial ends.

Resources you used:

  • Amazon Redshift Serverless workgroup (default-workgroup).
  • Amazon Redshift Serverless namespace (default-namespace).

Keep everything running

No action needed. Choose this if you are still actively experimenting with your queries or plan to extend this project.

  • Your free trial covers 90 days of usage from sign-up.
  • Monitor your remaining credit by checking the free trial section in the Redshift console.
  • The workgroup auto-pauses after a period of inactivity, which helps conserve credit.

Pause - I'll come back to this later

The Redshift Serverless workgroup auto-pauses after idle time. No manual action is needed to pause it.

  • Close the Query Editor v2 browser tab when you are done.
  • Your tables and data remain intact. When you return, open Query Editor v2 and reconnect to continue where you left off.

Delete - I don't want to use this again

Remove all project resources and stop all credit usage immediately. Delete the workgroup first, then the namespace.

  • In the AWS Management Console, search for Redshift and open the Amazon Redshift console.
  • In the left sidebar, click Serverless dashboard.
  • Select the default-workgroup workgroup.
  • Click Delete and confirm the deletion.
  • Return to the Serverless dashboard and select the default-namespace namespace.
  • Click Delete and confirm. This removes all tables, data, and stops all credit usage.

Nice Work!

Nice Work!

You just built a production-patterned star schema data warehouse on Amazon Redshift Serverless from scratch. You can now design dimensional models, optimize for columnar storage, and write the analytical SQL that shows up in BIE interviews.

You've learned how to:

  • Design a star schema with 4 dimension tables using DISTSTYLE ALL and a fact table with explicit DISTKEY and COMPOUND SORTKEY choices on Amazon Redshift.
  • Write advanced analytical queries using window functions (LAG, RANK, DENSE_RANK, running totals) and CTEs to structure complex logic.
  • Load and query realistic supply chain data across 5 European warehouses, using GROUP BY, HAVING, and CASE statements to answer business questions.
  • Secret Mission: Built a statistical anomaly detection query using AVG, STDDEV, and z-score classification to flag carrier cost spikes.

Ready to quiz yourself?