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 71%).

• 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.49 to 1.33, 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 77.9, mean 1.15
• fact_store_item_day.demand_unitsTrue demand drawn around the expectation, including units nobody could buy.0 to 77, mean 1.15
• fact_store_item_day.lost_unitsDemand lost because the shelf was empty: the gap between demand and sales.0 to 52, 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,463,018 over 1,303,240 units at a 33.2% 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.36x the baseline on promotion days, and price-sensitive products lift more: 2.16x for elasticity below -1.8, 1.48x 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: about one fresh unit in eight shrinks.
• Baskets: median 2 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.86 with the truth, median error 0.12.
• Returns: 0.96% of lines; Fresh 0.41%, Frozen 0.28%, General merchandise 5.50%, Grocery 0.21%, Health & beauty 1.63%, Household 0.76%.

• 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,101,202 rows · 19 cols
• fact_sales_transactionBaskets: receipt number, time, till, channel, payment, the loyalty card and the member's profile version in force, and totals372,583 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,601 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

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