Downloads

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.

Last updated

View as Markdown For AI agents All guides as text

On this page

You'll be able to

  • Read star and snowflake schemas, and tell them apart.
  • Handle slowly changing customers and price history with validity dates.
  • Query inventory and order-pipeline snapshots without double counting.
  • Model multi-currency sales and budgets in Power BI.

retailwh is a data warehouse for a fictional retailer with 43 locations in the United States, Canada, the United Kingdom and Germany, plus an online, phone and marketplace "store" for each country. It is not an application database: it is organized the way a warehouse is, with fact tables (what happened) around dimension tables (who, what, where, when), so it is the place to learn modeling patterns before you build them in Power BI or Excel. April-March fiscal year, FY2023 through 24 October 2026 (the start of FY2027), about 787,000 rows in 25 tables.

All customers, products, brands, suppliers and amounts are invented. Region boundaries are deliberately rough rectangles.

Connect to it

The tables

Pattern Tables What they hold
Dates and money dim_date, dim_currency, fx_rates A daily calendar with fiscal columns and holidays; four currencies and a daily rate to USD
Places dim_country, dim_region, dim_store 8 countries, 6 selling regions (with a rough boundary polygon), 43 locations with latitude and longitude
Product snowflake dim_category, dim_subcategory, dim_product, dim_brand, dim_supplier, dim_product_price, dim_tag, bridge_product_tag 323 products in 17 subcategories and 5 categories, 24 brands, 14 suppliers, a price history and tags
Customers (type 2) dim_customer 12,000 customers as about 19,000 rows: a new row when a customer moves, changes tier or changes segment
Other dimensions dim_channel, dim_promotion In-store, online, phone and marketplace; 44 promotions
Sales fact_sales, fact_returns About 379,000 order lines at the grain of one product on one order, and the lines that came back
Inventory fact_inventory_snapshot Month-end stock by product and store
Orders in flight fact_order_pipeline One row per online, phone or marketplace order, with a date for each stage
Promotions fact_promo_coverage Which products were on offer in which stores for which promotion (a factless table)
Plan and summary fact_budget, fact_sales_period_agg Budgets by fiscal period, category and region; and actual sales summarized to the same grain

Five views flatten the common joins: vw_sales_detail (the whole star in one row), vw_customer_current, vw_inventory_latest, vw_order_cycle_time and vw_budget_vs_actual.

How the tables join

dim_date ───────────┐   (role-playing: order date, ship date, and six dates on the pipeline)
dim_customer ───────┤
dim_channel ────────┤
dim_promotion ──────┼──< fact_sales >── dim_product >── dim_subcategory >── dim_category      (snowflake)
dim_currency ───────┤        │               └─────── dim_brand >── dim_supplier >── dim_country >── dim_currency
dim_store >── dim_region >── dim_country >── dim_currency
                    fact_returns >── fact_sales          dim_product >──< dim_tag  (via bridge_product_tag)
fact_order_pipeline, fact_inventory_snapshot, fact_promo_coverage: other facts on the same dimensions
fact_budget and fact_sales_period_agg: facts at a coarser grain, joined on category and region

Each pattern, with a query

Star schema: one fact, many dimensions

vw_sales_detail joins the star for you. Sales and margin by category for the 2026 fiscal year:

SELECT fiscal_year, category_name, ROUND(SUM(net_amount_usd), 0) AS sales_usd, ROUND(SUM(margin_usd) * 100.0 / SUM(net_amount_usd), 1) AS margin_pct
FROM vw_sales_detail
WHERE fiscal_year = 2026
GROUP BY fiscal_year, category_name
ORDER BY sales_usd DESC;

Snowflake: follow the chain

Product reaches its category through the subcategory, and its supplier country through brand and supplier. Nothing is wrong with this, but each hop is another join and another relationship in Power BI:

SELECT cat.category_name, sub.subcategory_name, b.brand_name, sup.supplier_name, co.country_name AS supplier_country,
       COUNT(DISTINCT p.product_key) AS products, ROUND(SUM(s.net_amount_usd), 0) AS sales_usd
FROM fact_sales s
JOIN dim_product p ON p.product_key = s.product_key
JOIN dim_subcategory sub ON sub.subcategory_key = p.subcategory_key
JOIN dim_category cat ON cat.category_key = sub.category_key
JOIN dim_brand b ON b.brand_key = p.brand_key
JOIN dim_supplier sup ON sup.supplier_key = b.supplier_key
JOIN dim_country co ON co.country_key = sup.country_key
GROUP BY cat.category_name, sub.subcategory_name, b.brand_name, sup.supplier_name, co.country_name
ORDER BY sales_usd DESC
OFFSET 0 ROWS FETCH FIRST 6 ROWS ONLY;

Slowly changing dimension (type 2)

When a customer changes, dim_customer gets a new row instead of changing the old one. Each version has valid_from, valid_to (9999-12-31 for the current one), is_current and a change_reason. Here are six customers' histories:

SELECT customer_id, customer_name, loyalty_tier, segment, city, valid_from, valid_to, is_current, change_reason
FROM dim_customer
WHERE customer_id IN ('C000010', 'C000011', 'C000012', 'C000013', 'C000014', 'C000015')
ORDER BY customer_id, valid_from;

fact_sales.customer_key points at the version that was true on the order date, so a sale keeps the tier and segment the customer had at the time. That is what tier_at_sale in the view means:

SELECT tier_at_sale, COUNT(DISTINCT customer_id) AS customers, COUNT(DISTINCT order_id) AS orders, ROUND(SUM(net_amount_usd), 0) AS sales_usd,
       ROUND(SUM(net_amount_usd) / COUNT(DISTINCT order_id), 2) AS avg_order_usd
FROM vw_sales_detail
WHERE fiscal_year = 2026
GROUP BY tier_at_sale
ORDER BY sales_usd DESC;

To count customers rather than versions, count DISTINCT customer_id. To report on today's attributes, join through customer_id to the row where is_current = 1 (see vw_customer_current).

Price history with validity dates

dim_product_price is a type 2 table too: a list price with the dates it applies. Prices change most Aprils, and electronics get cheaper:

SELECT p.sku, p.product_name, pp.valid_from, pp.valid_to, pp.list_price_usd
FROM dim_product p
JOIN dim_product_price pp ON pp.product_key = p.product_key
WHERE p.product_key IN (SELECT product_key FROM dim_product_price GROUP BY product_key HAVING COUNT(*) >= 4)
ORDER BY p.sku, pp.valid_from
OFFSET 0 ROWS FETCH FIRST 8 ROWS ONLY;

Multi-currency

Each sale line keeps the local amount, the exchange rate on the day, and the USD amount. The effective rate is not the average of the daily rates, because it is weighted by sales:

SELECT cur.currency_code, COUNT(*) AS line_count, ROUND(SUM(s.net_amount_local), 0) AS sales_local, ROUND(SUM(s.net_amount_usd), 0) AS sales_usd,
       ROUND(SUM(s.net_amount_usd) / SUM(s.net_amount_local), 4) AS effective_rate, ROUND(MIN(s.fx_rate_to_usd), 4) AS lowest_rate, ROUND(MAX(s.fx_rate_to_usd), 4) AS highest_rate
FROM fact_sales s
JOIN dim_currency cur ON cur.currency_key = s.currency_key
GROUP BY cur.currency_code
ORDER BY sales_usd DESC;

Periodic snapshot: do not add a balance across time

fact_inventory_snapshot records stock at each month-end. You can add it across products and stores, but not across months, because the same units appear in every month. Look at one month at a time, take the last month, or average:

SELECT dd.fiscal_period_name, SUM(i.on_hand_units) AS on_hand_units_all_stores, SUM(i.units_sold_in_month) AS units_sold, SUM(CAST(i.is_stockout AS INT)) AS stockouts
FROM fact_inventory_snapshot i
JOIN dim_date dd ON dd.date_key = i.month_end_date_key
WHERE dd.fiscal_year = 2026
GROUP BY dd.fiscal_period_name
ORDER BY dd.fiscal_period_name;

units_sold_in_month is additive across months; on_hand_units is not.

Accumulating snapshot: an order moving through stages

fact_order_pipeline has one row per order and a date for each stage; a stage the order has not reached is null. vw_order_cycle_time turns the dates into days:

SELECT ch.channel_name, od.fiscal_year, COUNT(*) AS orders, ROUND(AVG(o.days_to_ship * 1.0), 2) AS avg_days_to_ship, ROUND(AVG(o.days_to_deliver * 1.0), 2) AS avg_days_to_deliver
FROM vw_order_cycle_time o
JOIN dim_channel ch ON ch.channel_name = o.channel_name
JOIN dim_date od ON od.calendar_date = o.order_date
WHERE o.status IN ('Delivered', 'Returned') AND od.fiscal_year >= 2025
GROUP BY ch.channel_name, od.fiscal_year
ORDER BY ch.channel_name, od.fiscal_year;

Factless fact: what was offered, and what did not sell

fact_promo_coverage has no measures. It records that a product was on promotion in a store. Joining it to the sales shows the offers nobody took, which a sales table alone can never show:

SELECT pr.promotion_name, COUNT(*) AS offered_combinations,
       SUM(CASE WHEN x.line_count IS NULL THEN 1 ELSE 0 END) AS never_sold,
       ROUND(100.0 * SUM(CASE WHEN x.line_count IS NULL THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_never_sold
FROM fact_promo_coverage c
JOIN dim_promotion pr ON pr.promotion_key = c.promotion_key
LEFT JOIN (SELECT promotion_key, product_key, store_key, COUNT(*) AS line_count
           FROM fact_sales WHERE promotion_key IS NOT NULL
           GROUP BY promotion_key, product_key, store_key) x
       ON x.promotion_key = c.promotion_key AND x.product_key = c.product_key AND x.store_key = c.store_key
WHERE pr.start_date >= '2025-01-01' AND pr.start_date < '2026-01-01'
GROUP BY pr.promotion_name
ORDER BY pct_never_sold DESC
OFFSET 0 ROWS FETCH FIRST 6 ROWS ONLY;

Facts at different grain: budget against actual

Watch out

The budget is by fiscal period, category and region; sales are by order line. Compare them at the budget's grain.

The warehouse keeps a pre-summarized fact_sales_period_agg for this, which is also the standard fix when a view over a large fact table is slow:

SELECT category_name, SUM(budget_usd) AS budget_usd, SUM(actual_usd) AS actual_usd, ROUND(SUM(actual_usd) * 100.0 / SUM(budget_usd), 1) AS pct_of_budget
FROM vw_budget_vs_actual
WHERE fiscal_year = 2026
GROUP BY category_name
ORDER BY pct_of_budget DESC;

Many-to-many with a bridge table

A product can have several tags and a tag many products. Summing sales by tag counts a product once for each of its tags, so the totals do not add up to total sales. That is correct for this question, but be careful when you use tags as a filter:

SELECT t.tag_name, COUNT(DISTINCT p.product_key) AS products, ROUND(SUM(s.net_amount_usd), 0) AS sales_usd
FROM fact_sales s
JOIN dim_product p ON p.product_key = s.product_key
JOIN bridge_product_tag b ON b.product_key = p.product_key
JOIN dim_tag t ON t.tag_key = b.tag_key
GROUP BY t.tag_name
ORDER BY sales_usd DESC;

Role-playing dimension: one date table, several jobs

fact_sales has an order date and a ship date, both pointing at dim_date. Join the date table twice with different aliases. This finds December 2025 orders that shipped in January:

SELECT od.month_name AS order_month, sd.month_name AS ship_month, COUNT(*) AS line_count
FROM fact_sales s
JOIN dim_date od ON od.date_key = s.order_date_key
JOIN dim_date sd ON sd.date_key = s.ship_date_key
WHERE od.calendar_year = 2025 AND od.calendar_month = 12 AND sd.calendar_month <> od.calendar_month
GROUP BY od.month_name, sd.month_name;

Geography: points and polygons

Stores and customers have latitude and longitude, which is enough for a bubble map. dim_region.boundary_geojson holds a rough GeoJSON polygon for each region, to practice a shape map. Stores by region:

SELECT r.region_name, co.country_name, COUNT(*) AS stores, SUM(st.square_feet) AS square_feet
FROM dim_store st
JOIN dim_region r ON r.region_key = st.region_key
JOIN dim_country co ON co.country_key = r.country_key
WHERE st.is_online = 0
GROUP BY r.region_name, co.country_name
ORDER BY stores DESC;

Modeling it in Power BI and Excel

More structures, and what Power BI cannot do with them, are in Data structures and Power BI limits.

Things that happen in the data

Try it on the data

Open the retailwh database page, browse it in the Playground, write your own queries in the SQL editor, see how the tables connect in the schema explorer, or ask an AI assistant.