# Retail Data Warehouse data dictionary

Sample database `retailwh` on learnsql.cpetoday.com (SQL Server login: CPE Today course). Each table lists its columns with the type, keys, what the column means, and, for codes and statuses, the values seen in the sample copy (with row counts when the dictionary was written).
11 of 25 tables are described. Undocumented tables are listed at the end.

## dim_channel

Sales channels.

Grain: one row per channel

| 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) |

## dim_customer

Customers, one row per version: slowly changing history with valid_from, valid_to and is_current.

Grain: one row per customer version

| 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) |  |  |  |
| `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) |  |  |  |
| `postal_code` | varchar(10) |  |  |  |
| `latitude` | decimal |  |  |  |
| `longitude` | decimal |  |  |  |
| `signup_date` | date |  |  |  |
| `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) |

## dim_date

One row per calendar day with calendar and fiscal labels.

Grain: one row per day

| 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 |  |  |  |
| `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) |  |  |  |
| `day_of_month` | tinyint |  |  |  |
| `day_of_week` | tinyint |  |  |  |
| `day_name` | varchar(12) |  |  |  |
| `is_weekend` | bit |  | 1 on a weekend day. | False (1304), True (522) |
| `iso_week` | tinyint |  |  |  |
| `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) |  |  |  |
| `is_holiday` | bit |  | 1 on a holiday. | False (1776), True (50) |
| `holiday_name` | varchar(40) |  |  |  |

## dim_product

Products: SKU, name, subcategory, brand, size and cost.

Grain: one row per product

| 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. |  |

## dim_promotion

Promotions with discount and dates.

Grain: one row per promotion

| 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 |  |  |

## dim_store

Stores and online channels, with location, size and open or close dates.

Grain: one row per store

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `store_key` | int | PK | Unique store key. |  |
| `store_id` | varchar(8) |  |  |  |
| `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) |  |  |  |
| `state_province` | varchar(30) |  |  |  |
| `postal_code` | varchar(10) |  |  |  |
| `latitude` | decimal |  |  |  |
| `longitude` | decimal |  |  |  |
| `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) |

## fact_budget

Budgeted sales by fiscal period, category and region.

Grain: one row per budget line

| 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. |  |

## fact_inventory_snapshot

Stock by product and store at each month end.

Grain: one row per product, store and 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) |

## fact_order_pipeline

Each order's progress through fulfillment, with a date key for each step.

Grain: one row per order

| 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 |  |  |
| `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) |

## fact_returns

Items returned against a sale line.

Grain: one row per return

| 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) |

## fact_sales

Sales in a star-schema warehouse: one row per order line, in local currency and in USD.

Grain: one row per order line

| 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. |  |

## Undocumented tables

`bridge_product_tag`, `dataset_info`, `dim_brand`, `dim_category`, `dim_country`, `dim_currency`, `dim_product_price`, `dim_region`, `dim_subcategory`, `dim_supplier`, `dim_tag`, `fact_promo_coverage`, `fact_sales_period_agg`, `fx_rates`
