Retail star schema

A dimensional model you can actually practise joins on: one fact table, four dimensions, 47,051 rows, every join resolving and every total known.

Every free mock-data generator hands you one flat CSV, and a flat file has no joins. That makes it useless for the exact skill most people downloading practice data are trying to learn. This is a real star schema: a chain of 20 stores selling to 2,000 loyalty members over two years, with fact_sales joined to date, product, customer and store dimensions, no orphaned rows and no duplicate keys. Two things are declared rather than sampled: each category's share of two years' revenue is exact to the cent, and all 24 months of revenue land on a stated curve. Around those, it behaves like a shop: a few regulars place most of the lines, members shop at their home store, Saturdays are busiest, and the seasons move what sells. Your GROUP BY has an answer that is known to be right, so you can check your own SQL instead of eyeballing whether it looks plausible.

  • star schema
  • dimensional model
  • BI practice
  • Power BI
  • Tableau
  • SQL joins
01,5003,000Jan 2024Apr 2024Jul 2024Oct 2024Jan 2025Apr 2025Jul 2025Oct 2025Dec 2025
Rows per month in fact_sales.date_key → dim_date.date, counted from the file.

Explore

Every table, profiled

Each column's type, spread, empties and most common values, measured from the CSVs in the download. Switch to the first rows to see the data exactly as it sits in the file.

fact_sales.csv

43,900 rows · 11 columns · 4 foreign keys

sale_idprimary key
unique on every row
43,900 distinctno empties
customer_keyforeign key
points to dim_customer.customer_key
2,000 distinctno empties
store_keyforeign key
points to dim_store.store_key
20 distinctno empties
product_keyforeign key
points to dim_product.product_key
400 distinctno empties
date_keyforeign key
points to dim_date.date_key
731 distinctno empties
time_of_daytext
  • “10:30:07”
  • “10:39:31”
  • “10:40:54”
26,772 distinct8 chars on averageno empties
categorycategory
  • Grocery
    62%
  • Home
    15%
  • Apparel
    14%
  • Electronics
    8.8%
4 valuesno empties
quantitynumber
16
mean 1.58median 11 to 6no empties
discount_pctnumber
00.3
mean 0.098median 0.10 to 0.3no empties
revenuenumber
1.44log scale920.8
mean 56.24median 13.781.24 to 4,374no empties
unit_pricenumber
1.38log scale531.3
mean 39.68median 9.841.16 to 1,575.8no empties

Keys

Every join resolves

4 foreign keys, 175,600 references checked against the table each one points at. None points at a row that does not exist.

fact_sales

  • customer_keydim_customer.customer_key0 orphans
  • store_keydim_store.store_key0 orphans
  • product_keydim_product.product_key0 orphans
  • date_keydim_date.date_key0 orphans

Checks

What we checked

  • Zero orphaned rows joining fact_sales to all four dimensions
  • Zero duplicate keys in dim_date, dim_product, dim_customer and dim_store
  • Over the two years Electronics is 42.00% of revenue, Home 26.00%, Apparel 20.00%, Grocery 12.00%
  • All 24 months of revenue land on the declared curve to the cent: 82,000.00 in January 2024, 148,000.00 in December 2025
  • revenue = quantity × unit_price × (1 − discount_pct) on every row, and no sale is dated before its customer joined
  • Checked with DuckDB against these exact files, which shares no code with the generator

The same checks ship in the zip as INTEGRITY.txt, so you can rerun them in any SQL engine. How we verify

Questions, answered

Worth asking it

Each answer is the query's result on these exact files. Open one for its SQL and rows, then check your own query against it.

  1. 1Which category earns most in which region?Top category by region: East Electronics (40.5%); North Electronics (41.7%); South Electronics (43.1%); West Electronics (41.7%).
    with r as (
      select s.region, f.category, sum(f.revenue) as revenue
      from fact_sales f
      join dim_store s on s.store_key = f.store_key
      group by s.region, f.category
    )
    select region, category, round(revenue, 2) as revenue,
           round(100 * revenue / sum(revenue) over (partition by region), 1) as share_pct
    from r
    order by region, revenue desc
    regioncategoryrevenueshare_pct
    EastElectronics217,332.6740.5
    EastHome140,524.3226.2
    EastApparel114,281.6721.3
    EastGrocery64,691.1512.1
    NorthElectronics145,155.2441.7
    NorthHome87,526.6425.2
    NorthApparel71,393.6920.5
    NorthGrocery43,670.1912.6
    SouthElectronics417,272.943.1
    SouthHome250,158.625.8
    SouthApparel184,987.8519.1
    SouthGrocery115,898.0412
    WestElectronics257,109.9441.7
    WestHome163,662.8126.6
    WestApparel123,084.7720
    WestGrocery71,989.411.7
  2. 2How does revenue grow month over month across two years?$82,000 in 2024-01 to $148,000 in 2025-12, 1.80x over 24 months.
    with m as (
      select d.year, d.month, sum(f.revenue) as revenue
      from fact_sales f
      join dim_date d on d.date_key = f.date_key
      group by d.year, d.month
    )
    select year, month, round(revenue, 2) as revenue,
           round(100 * (revenue / lag(revenue) over (order by year, month) - 1), 1) as growth_pct
    from m
    order by year, month
    yearmonthrevenuegrowth_pct
    2,024182,000
    2,024280,685.5-1.6
    2,024390,419.7212.1
    2,024490,056.72-0.4
    2,024594,353.124.8
    2,024692,115.97-2.4
    2,024793,627.121.6
    2,024897,052.83.7
    2,024990,940.42-6.3
    2,0241094,377.643.8
    2,02411102,693.518.8
    2,02412115,00012
    2,025189,809.32-21.9
    2,025289,864.410.1
    2,0253102,360.1713.9
    2,0254103,576.271.2
    2,0255110,201.276.4
    2,0256109,211.86-0.9
    2,0257112,633.473.1
    2,0258118,423.735.1
    2,0259112,510.59-5
    2,02510118,347.465.2
    2,02511130,478.8110.3
    2,02512148,00013.4
  3. 3What is the gross margin by brand tier (Premium, Standard, Budget)?Standard 35.3%, Premium 32.3%, Budget 29.5% gross margin.
    select p.subcategory as tier,
           round(sum(f.revenue), 2) as revenue,
           round(sum(f.quantity * p.unit_cost), 2) as cost,
           round(100 * (1 - sum(f.quantity * p.unit_cost) / sum(f.revenue)), 1) as gross_margin_pct
    from fact_sales f
    join dim_product p on p.product_key = f.product_key
    group by p.subcategory
    order by gross_margin_pct desc
    tierrevenuecostgross_margin_pct
    Standard729,669.31472,000.5635.3
    Premium1,127,298.12763,618.0932.3
    Budget611,772.45431,227.529.5
  4. 4Which customers shop away from their home city, and where?The busiest away trip is from Hartford to Austin (60 lines by 23 customers).
    select c.city as home_city, s.city as store_city,
           count(*) as lines, count(distinct f.customer_key) as customers
    from fact_sales f
    join dim_customer c on c.customer_key = f.customer_key
    join dim_store s on s.store_key = f.store_key
    where c.city <> s.city
    group by c.city, s.city
    order by lines desc
    limit 10
    home_citystore_citylinescustomers
    HartfordAustin6023
    PittsburghAustin5633
    Salt Lake CityAustin5129
    CharlotteAustin4837
    DetroitAustin4722
    PhiladelphiaAustin4624
    AtlantaAustin4534
    PhoenixAustin4222
    SacramentoAustin3731
    AustinAtlanta3627

Every query runs on the files in the download as they are. ANSWERS.sql holds all of them; run it in DuckDB from the dataset's folder.

Full version · $15

This sample, or the full Retail Data Warehouse

Two fiscal years of a grocery chain, from the shelf to the receipt. Try its free preview before you decide.

This sampleRetail Data Warehouse
Rows47,0512,790,852
Tables514
Columns49194
FormatsCSVCSV, Parquet, SQL DDL, worked SQL
AuditKeys and the checks listed above117 checks, all listed
LicenceCC0, freeCommercial use, $15 once
Built forstar schema, dimensional model, BI practice, Power BIStar schema and BI, Demand forecasting, Promotion and price analytics, Inventory and supply chain, Customer analytics, Returns and fraud-style exercises

Use it

Take it, or make one shaped like yours

How it was made

Made by the Misata engine at seed 21, from the design the README in the zip describes. Its build script is not public; Studio makes one shaped like it from a description.

Your own tables, your own numbers

Describe the data you need in Studio and get connected tables with the totals you state, profiled like this and checked before you download.

The full version, and what goes with it

All premium
  • Weekly sales, at regular price and on promotion

    Grocery chain, 10 stores, fiscal 2024 and 2025

    Two fiscal years of a grocery chain, from the shelf to the receipt

    Answer key

    • baseline_units
    • price_effect
    • promo_lift
    • +7 more
    Rows
    2,790,852
    Tables
    14
    Checks
    117 of 117
    Free preview18-question projectProfile and preview
  • Card spending each month, debit and credit

    2,500 US households at five banks and seven card issuers, two years

    Five banks' raw statement lines, with every label behind them

    Answer key

    • description
    • merchant_name
    • category
    • +6 more
    Rows
    7,627,186
    Tables
    14
    Checks
    50 of 50
    Free previewProfile and preview
  • transactions, rows per monthMay 2025 – Oct 2026

    One UAE bank, 10,000 customers, eighteen months

    A UAE bank's whole trail from KYC to STR, with the laundering nobody caught

    Answer key

    • is_suspicious
    • typology
    • ring_id
    • +7 more
    Rows
    11,711,393
    Tables
    16
    Checks
    129 of 129
    Free previewProfile and preview

Related free datasets

All 9

Fully synthetic. No real person, company or transaction is represented, and no production data was read to make it. CC0: use it anywhere, no attribution needed.