Start here
Lessons and reference
Look things up
Start here
SQL
Power Query
Data modeling
Power BI and DAX
Finance playbooks
Run queries
Build and check
A retail data warehouse built to teach modeling: a star schema with snowflaked product and store dimensions, customers that change over time (type 2), a price history, a month-end inventory snapshot, an order pipeline with a date for each stage, a factless promotion table, four currencies with daily exchange rates, and budgets at a different grain.
Retail data warehouse (retailwh): Star and snowflake schemas, slowly changing customers, price history, inventory and order-pipeline snapshots, a factless table, multi-currency and budgets, in one retail warehouse.
Write each as a query in the SQL editor, or ask an AI assistant to walk you through it.
Server learnsql.cpetoday.com,1433, database retailwh, in Excel or Power BI (Import mode) or
DBeaver. These tables are in the dbo schema, so once you have picked the database you can use plain names such as bridge_product_tag.
Download the Excel connection (.odc)
How to use it: download the file and open it. Excel opens it as a connection to retailwh
and asks for the SQL Server username and password from your CPE Today course.
The file holds no password. The first table is selected by default, and you can pick others from the Data window.
Setup details for other tools are on Connect.
Click a table to preview it in the Playground.
What each column means, its type and keys, and for codes and statuses the values seen in this copy. 11 of 25 tables are described so far. Read it as Markdown.
Click a table in the diagram above to jump to it.
Sales channels.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
channel_key |
int | PK | Unique channel key. | 1 1 2 1 3 1 4 1 |
channel_name |
varchar(20) | Channel name. | In-store 1 Marketplace 1 Online 1 Phone 1 | |
is_digital |
bit | 1 for digital channels. | False 2 True 2 |
Customers, one row per version: slowly changing history with valid_from, valid_to and is_current.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
customer_key |
int | PK | Unique row key (a customer can have several). | |
customer_id |
varchar(10) | Business customer ID. | ||
customer_name |
varchar(80) | Name. | ||
email |
varchar(100) | Not described yet | ||
segment |
varchar(16) | Customer segment (see the values). | Consumer 15613 Small business 2448 Wholesale 968 | |
loyalty_tier |
varchar(10) | Loyalty tier (see the values). | Bronze 7753 Silver 6481 Gold 3559 Platinum 1236 | |
region_key |
int | FK to dim_region | Region (dim_region). | 2 3836 1 3802 3 3797 5 3070 6 2425 4 2099 |
city |
varchar(60) | Not described yet | ||
postal_code |
varchar(10) | Not described yet | ||
latitude |
decimal | Not described yet | ||
longitude |
decimal | Not described yet | ||
signup_date |
date | Not described yet | ||
valid_from |
date | First date this version applies. | ||
valid_to |
date | Last date this version applies. | ||
is_current |
bit | 1 for the current version. | True 12000 False 7029 | |
change_reason |
varchar(20) | Why this version was created. | (blank) 7661 Tier change 5552 Signup 4339 Moved 1109 Segment change 368 |
One row per calendar day with calendar and fiscal labels.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
date_key |
int | PK | Date key. | |
calendar_date |
date | The calendar date. | ||
calendar_year |
smallint | Calendar year. | 2024 366 2023 365 2025 365 2026 365 2022 275 2027 90 | |
calendar_quarter |
tinyint | Not described yet | ||
calendar_month |
tinyint | Calendar month number. | 1 155 3 155 5 155 7 155 8 155 10 155 12 155 4 150 6 150 9 150 11 150 2 141 | |
month_name |
varchar(12) | Not described yet | ||
day_of_month |
tinyint | Not described yet | ||
day_of_week |
tinyint | Not described yet | ||
day_name |
varchar(12) | Not described yet | ||
is_weekend |
bit | 1 on a weekend day. | False 1304 True 522 | |
iso_week |
tinyint | Not described yet | ||
fiscal_year |
smallint | Fiscal year. | 2024 366 2023 365 2025 365 2026 365 2027 365 | |
fiscal_quarter |
tinyint | Fiscal quarter. | 2 460 3 460 1 455 4 451 | |
fiscal_period |
tinyint | Fiscal period. | 2 155 4 155 5 155 7 155 9 155 10 155 12 155 1 150 3 150 6 150 8 150 11 141 | |
fiscal_period_name |
varchar(12) | Not described yet | ||
is_holiday |
bit | 1 on a holiday. | False 1776 True 50 | |
holiday_name |
varchar(40) | Not described yet |
Products: SKU, name, subcategory, brand, size and cost.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
product_key |
int | PK | Unique product key. | |
sku |
varchar(14) | Stock keeping unit. | ||
product_name |
varchar(80) | Product name. | ||
subcategory_key |
int | FK to dim_subcategory | Subcategory (dim_subcategory). | |
brand_key |
int | FK to dim_brand | Brand (dim_brand). | |
color |
varchar(20) | Color. | (blank) 38 Olive 34 Slate 34 White 33 Navy 30 Sand 30 Rust 29 Forest 27 Charcoal 25 Black 24 Sky 19 | |
size_label |
varchar(12) | Size label. | (blank) 247 XL 20 XS 17 L 16 M 13 S 10 | |
unit_cost_usd |
decimal | Cost per unit in USD. | ||
launch_date |
date | Date the product launched. | ||
discontinued_date |
date | Date discontinued, if it has been. |
Promotions with discount and dates.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
promotion_key |
int | PK | Unique promotion key. | |
promotion_name |
varchar(60) | Name. | ||
promotion_type |
varchar(20) | Type of promotion (see the values). | Seasonal 25 Holiday 10 Clearance 5 Category 4 | |
discount_pct |
decimal | Discount percent. | 15.0 20 20.0 14 25.0 5 30.0 5 | |
start_date |
date | First day. | ||
end_date |
date | Last day. | ||
category_key |
int | FK to dim_category | Not described yet |
Stores and online channels, with location, size and open or close dates.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
store_key |
int | PK | Unique store key. | |
store_id |
varchar(8) | Not described yet | ||
store_name |
varchar(60) | Store name. | ||
store_type |
varchar(12) | Type of store (see the values). | Mall 12 Outlet 11 Street 9 Flagship 7 Online 4 | |
region_key |
int | FK to dim_region | Region (dim_region). | 2 9 1 8 3 8 4 6 5 6 6 6 |
city |
varchar(60) | Not described yet | ||
state_province |
varchar(30) | Not described yet | ||
postal_code |
varchar(10) | Not described yet | ||
latitude |
decimal | Not described yet | ||
longitude |
decimal | Not described yet | ||
opened_date |
date | Opening date. | ||
closed_date |
date | Closing date, if closed. | (blank) 40 2024-01-31 1 2025-02-28 1 2026-05-31 1 | |
square_feet |
int | Floor area. | ||
is_online |
bit | 1 for online stores. | False 39 True 4 |
Budgeted sales by fiscal period, category and region.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
budget_id |
int | PK | Unique budget row ID. | |
fiscal_year |
smallint | Fiscal year. | 2023 360 2024 360 2025 360 2026 360 2027 360 | |
fiscal_period |
tinyint | Fiscal period. | 1 150 2 150 3 150 4 150 5 150 6 150 7 150 8 150 9 150 10 150 11 150 12 150 | |
category_key |
int | FK to dim_category | Category (dim_category). | 1 360 2 360 3 360 4 360 5 360 |
region_key |
int | FK to dim_region | Region (dim_region). | 1 300 2 300 3 300 4 300 5 300 6 300 |
budget_usd |
decimal | Budget in USD. |
Stock by product and store at each month end.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
snapshot_id |
int | PK | Unique row ID. | |
month_end_date_key |
int | FK to dim_date | Month-end date (dim_date). | |
product_key |
int | FK to dim_product | Product (dim_product). | |
store_key |
int | FK to dim_store | Store (dim_store). | |
on_hand_units |
int | Units on hand. | ||
on_order_units |
int | Units on order. | ||
units_sold_in_month |
int | Units sold in the month. | ||
is_stockout |
bit | 1 when the item was out of stock. | False 189137 True 59865 |
Each order's progress through fulfillment, with a date key for each step.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
pipeline_id |
int | PK | Unique row ID. | |
order_id |
varchar(14) | Order. | ||
customer_key |
int | FK to dim_customer | Customer (dim_customer). | |
store_key |
int | FK to dim_store | Store (dim_store). | 40 38355 42 10110 43 8331 41 6986 |
channel_key |
int | FK to dim_channel | Channel (dim_channel). | 2 44903 4 11334 3 7545 |
order_date_key |
int | FK to dim_date | Date ordered. | |
paid_date_key |
int | FK to dim_date | Date paid. | |
picked_date_key |
int | FK to dim_date | Not described yet | |
shipped_date_key |
int | FK to dim_date | Date shipped. | |
delivered_date_key |
int | FK to dim_date | Date delivered. | |
returned_date_key |
int | FK to dim_date | Date returned, if any. | |
order_value_usd |
decimal | Order value in USD. | ||
carrier |
varchar(20) | Shipping carrier. | FedEx 12517 UPS 12503 USPS 12434 DHL 11353 Canada Post 6832 Royal Mail 3358 Hermes 3355 (blank) 1430 | |
status |
varchar(12) | Where the order is now (see the values). | Delivered 50044 Returned 12140 Cancelled 1321 Shipped 168 Processing 109 |
Items returned against a sale line.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
return_id |
int | PK | Unique return ID. | |
sale_line_id |
int | FK to fact_sales | Sale line returned (fact_sales). | |
return_date_key |
int | FK to dim_date | Date of the return (dim_date). | |
quantity |
smallint | Units returned. | 1 22570 2 3115 3 952 4 588 | |
refund_usd |
decimal | Refund in USD. | ||
reason |
varchar(30) | Reason for the return (see the values). | Changed mind 7997 Wrong size 7661 Defective 4980 Not as described 3926 Arrived late 2661 |
Sales in a star-schema warehouse: one row per order line, in local currency and in USD.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
sale_line_id |
int | PK | Unique line ID. | |
order_id |
varchar(14) | Order the line belongs to. | ||
line_number |
tinyint | Line number within the order. | 1 164204 2 119604 3 61681 4 23731 5 7309 6 1838 7 370 8 63 | |
order_date_key |
int | FK to dim_date | Order date (dim_date). | |
ship_date_key |
int | FK to dim_date | Ship date (dim_date). | |
customer_key |
int | FK to dim_customer | Customer (dim_customer). | |
product_key |
int | FK to dim_product | Product (dim_product). | |
store_key |
int | FK to dim_store | Store (dim_store). | |
channel_key |
int | FK to dim_channel | Channel (dim_channel). | 1 234538 2 101523 4 25638 3 17101 |
promotion_key |
int | FK to dim_promotion | Promotion applied (dim_promotion). | |
currency_key |
int | FK to dim_currency | Currency (dim_currency). | 1 226560 3 60900 4 49583 2 41757 |
quantity |
smallint | Units sold. | 1 284094 2 64216 3 19116 4 11374 | |
unit_price_local |
decimal | Price per unit in local currency. | ||
discount_local |
decimal | Discount in local currency. | ||
net_amount_local |
decimal | Net sales in local currency. | ||
fx_rate_to_usd |
decimal | Exchange rate to USD on the order date. | ||
net_amount_usd |
decimal | Net sales in USD. | ||
cost_usd |
decimal | Cost in USD. |