Retail Data Warehouse

• Star schema and BI

  a Kimball-style warehouse with two type 2 slowly changing dimensions, a fiscal calendar, a periodic snapshot fact, transaction facts at two grains and a factless range table. Power BI, Tableau, Looker, dbt, SQL courses and interview practice. schema.sql has every key; queries.sql has 17 worked analyses.

• Demand forecasting

  1,163,432 daily series with seasonality, holidays, promotions, price changes, launches, delistings and a new store. demand_units is true demand, not sales capped by stock, and the chain's own forecast is there to beat (WAPE 72%).

• Promotion and price analytics

  uplift, pantry loading, cannibalisation and elasticity, with the true factors (promo_lift, pantry_dip, cannibal_effect, price_effect, true_elasticity) to check an estimate against.

• Inventory and supply chain

  stock-flow identities hold on every row; out-of-stocks, lost sales, fresh waste, safety stock and a supplier shortage to study.

• Customer analytics

  RFM, churn, basket affinity and market-basket rules, home-store loyalty and tier changes.

• Returns and fraud-style exercises

  returns tied to the exact line and price paid.

• fact_store_item_day.baseline_unitsDemand the item would have that day at its regular price, before any effect.0 to 34.31, mean 1.2
• fact_store_item_day.price_effectThe multiplier from the shelf price against the reference, through the product's elasticity.0.48 to 1.35, mean 0.92
• fact_store_item_day.promo_liftThe multiplier from a promotion in force; 1 when there is none.1 to 9.72, mean 1.19
• fact_store_item_day.pantry_dipThe dip after a promotion in stock-up categories, as shoppers use what they bought; 1 when none.0.72 to 1, mean 0.98
• fact_store_item_day.cannibal_effectThe multiplier while a sibling product is on promotion.0.46 to 1, mean 0.88
• fact_store_item_day.expected_unitsThe product of those factors: the demand to expect.0 to 75.69, mean 1.14
• fact_store_item_day.demand_unitsTrue demand drawn around the expectation, including units nobody could buy.0 to 94, mean 1.14
• fact_store_item_day.lost_unitsDemand lost because the shelf was empty: the gap between demand and sales.0 to 41, mean 0.034
• dim_product.true_elasticityThe product's true price elasticity, to check an estimate against.-2.84 to -0.48, mean -1.4
• fact_sales_transaction.basket_missionWhy the basket was bought (breakfast, party, cookout and so on), for market-basket work.9 values: mixed, dinner, breakfast…

• Sales $7,560,130 over 1,292,563 units at a 33.5% gross margin; 21% of sales on promotion.
• Like-for-like growth +4.0% in the stores open both years; a new store opened 2025-04-06, first two weeks at 39% of its last 90 days.
• Seasons and events: ice cream July/January 2.0x; confectionery before Halloween 3.7x a normal day. Saturday is the busiest day, Tuesday the quietest.
• Promotions lift demand 2.35x the baseline on promotion days, and price-sensitive products lift more: 2.21x for elasticity below -1.8, 1.50x above -1.3 (20-30% off, no display or flyer). A sibling on promotion takes sales: 0.82x baseline while a sibling is promoted. Stock-up categories dip after a promotion and recover.
• The forecast under-plans promotions, so they run out: lost share of demand clearance 6.5%, promotion 7.2%, regular 1.3%. Shelves are out of stock on 1.4% of item-days; 3.0% of demand is lost overall.
• A bottler's shortage in August and September 2024: 43% of demand lost in the shortage, 5% outside.
• Fresh shrink: 12.9% of fresh units shrink.
• Baskets: median 3 lines of the tracked range; hypermarket 3.6, neighbourhood 1.8, supermarket 2.8 lines per basket by format. 56% of baskets scan a loyalty card; 87% of carded baskets are at the member's home store.
• Shopping missions (breakfast, dinner, party, cookout, baking, home care, personal care, school and gifts, or a mixed shop) put complements in the same basket: median lift 3.5 within a mission, 0.5 across, so market-basket rules find pasta with sauce and crisps with soft drinks. basket_mission is delivered as the answer key.
• Price elasticity is recoverable from the regular price changes: by subcategory, correlation 0.93 with the truth, median error 0.14.
• Returns: 0.95% of lines; Fresh 0.41%, Frozen 0.29%, General merchandise 5.56%, Grocery 0.21%, Health & beauty 1.50%, Household 0.72%.

• every primary key is unique and every foreign key resolves;
• the 4-5-4 calendar: 52 Sunday-to-Saturday weeks a year, 4-5-4 periods, closures on Thanksgiving and Christmas Day;
• both slowly changing dimensions chain without gaps or overlaps with one current version, and every fact carries the version in force at its time;
• prices: every list and shelf price sits on a price point (x.x9 under $10, .49 or .99 above), every price change moves the way its reason says; a price cut or clearance lands on the next price point down, a multi-buy or buy one get one free sells a unit for its share of the deal, to the cent;
• the shelf: close = open + received - sold - waste on every row, tomorrow opens with today's close, demand = sold + lost, a sale is lost only when the shelf ran empty, only fresh food spoils;
• demand is the product of its delivered factors and unbiased around it; the effects above are measured, not assumed;
• baskets: totals are the sums of their lines, lines ring up exactly the units the shelf sold, at that day's price and promotion; cards only after the member joined; returns after their sale, of what was bought, at what was paid.

• fact_store_item_dayOne row per ranged product per store per day: price status, discount, true demand and every factor in it, the chain's forecast, opening stock, deliveries, sales, lost sales, waste, closing stock, orders1,163,432 rows · 38 cols
• fact_sales_lineReceipt lines: product, quantity, regular and paid price, promotion, gross, discount, net, cost and margin, the basket's loyalty key1,093,372 rows · 19 cols
• fact_sales_transactionBaskets: receipt number, time, till, channel, payment, the loyalty card and the member's profile version in force, and totals369,883 rows · 17 cols
• dim_customer_historyType 2 slowly changing dimension of member profiles: tier changes and moves, with validity and the current flag93,958 rows · 10 cols
• dim_customerLoyalty members: home store, joined date, birth year, household size, shopper type36,000 rows · 7 cols
• fact_returnReturns against the line they came from: when, how many, why, refund and whether it went back on the shelf10,417 rows · 10 cols
• fact_store_dayOne row per store per day: open or closed, receipt lines and baskets6,853 rows · 7 cols
• dim_promotionPromotions: mechanic and the advertised deal (10 to 30% off, 3 for 2, 4 for 3, buy one get one free), the share it takes off a unit, weeks, display and flyer support2,493 rows · 12 cols
• dim_store_rangeWhich store ranges which product (the planogram), with safety stock and display minimum1,825 rows · 6 cols
• dim_product_price_historyType 2 slowly changing dimension of regular prices: every version with valid_from, valid_to, is_current and why it changed896 rows · 8 cols
• dim_dateEvery day of fiscal 2024 and 2025 on the NRF 4-5-4 calendar: fiscal week, period and quarter, holidays, trading events, pay days, the two days stores close728 rows · 17 cols
• dim_productSKUs: brand or private label, pack, base price (on a price point) and unit cost, velocity, the product's true price elasticity, launch and delisting dates240 rows · 16 cols
• dim_subcategory31 subcategories in 6 departments: seasonality, trading event, reference price, price elasticity, daily spoilage, return rate, whether promoted and stocked up on31 rows · 15 cols
• dim_storeStores: format (hypermarket, supermarket, neighbourhood), region, selling area, opening date (one opens mid-window), traffic, price zone, delivery cycle and lead time10 rows · 12 cols

Is this real data?

No. This is synthetic data generated by software. No row describes a real person, company, patient, store, machine or transaction. The patterns are modelled to be realistic and the statistics quoted are measured on these files, but they do not describe any real population or market. Use it for learning, testing, demos, benchmarks and prototyping, not as evidence about the real world. Provided as is, without warranty.

What do I get?

One zip of 109.2 MB: 14 linked tables and 2,780,138 rows as CSV, Parquet, SQL DDL (schema.sql), worked SQL analyses (queries.sql), a README of what each table holds and how the data behaves, INTEGRITY.json with the 110 audit checks, and RECIPE.json, the Misata blueprint that made these exact rows (seed 20240204).

Can I try it before buying?

Yes. The free preview (588 KB) is a slice of the same data with the keys intact, plus the README, so you can load it and check it fits before you pay.

How was it checked?

110 of 110 checks pass, re-run on the delivered files by an independent script with plain pandas: every primary key is unique and every foreign key resolves; the 4-5-4 calendar: 52 Sunday-to-Saturday weeks a year, 4-5-4 periods, closures on Thanksgiving and Christmas Day; both slowly changing dimensions chain without gaps or overlaps with one current version, and every fact carries the version in force at its time; prices: every list and shelf price sits on a price point (x.x9 under $10, .49 or .99 above), every price change moves the way its reason says; a price cut or clearance lands on the next price point down, a multi-buy or buy one get one free sells a unit for its share of the deal, to the cent; the shelf: close = open + received - sold - waste on every row, tomorrow opens with today's close, demand = sold + lost, a sale is lost only when the shelf ran empty, only fresh food spoils; demand is the product of its delivered factors and unbiased around it; the effects above are measured, not assumed; baskets: totals are the sums of their lines, lines ring up exactly the units the shelf sold, at that day's price and promotion; cards only after the member joined; returns after their sale, of what was bought, at what was paid.

Can I use it commercially?

Yes: in any project, course, benchmark, demo or product, commercial or not. You may not resell or redistribute the dataset itself as a dataset.

What should I know before using it?

Tracked range: 240 products stand for the chain's assortment, so a basket holds only the lines from that range (median 3); a real basket would have more lines of untracked products. Fiscal years are named for the calendar year they start in (fiscal 2024 runs 4 February 2024 to 1 February 2025). Money is in US dollars. true_elasticity, baseline_units and the demand factors are ground truth no retailer could see; use sales and prices alone for a realistic exercise, and the truth to score it. Stores, brands, products, suppliers and members are invented; no real retailer, brand or person is represented.

Can I get a bigger or different version?

Yes. RECIPE.json runs in Misata Studio or through Misata's MCP server to make a variant, or ask us to build one to your spec.

Want it bigger, in another setting, or with your own columns? Have us build it or make it in Studio.