How to Design a Star Schema for UK Retail Sales Data

Retail data arrives in a hurry. A Monday morning trading report pulls from a till system, an ecommerce platform, a stock system and a payment provider, and by the time someone has stitched those sources together in a spreadsheet, the figures are already stale. A star schema fixes that by arranging the data around one central fact table and a ring of dimensions that describe it. Get the design right and the same model will answer questions about stores, products, promotions and channels for years without being rebuilt. Get it wrong and every analyst writes the same awkward joins forever.

Decide the grain before you draw anything

The grain is what a single row in your fact table represents. It is the first decision and the one that constrains everything else. For most UK retailers, the sensible starting point is one row per sales order line. That is fine enough to answer product, basket, store and channel questions, and coarse enough to keep the table manageable.

Write the grain down as a sentence and keep it visible: one row per product line per completed sale transaction, in the store or channel where it was recorded. Then apply a simple test to every column you consider adding. If it does not describe that single line, it belongs in a dimension or in a different fact table.

Two mistakes are common. The first is modelling at transaction level, which makes product mix analysis painful because basket contents are hidden inside one row. The second is modelling at daily summary level, which looks efficient until someone asks which size of a product sold best on Black Friday. Line level usually survives both tests.

The core dimension tables

Dimensions carry the descriptions, hierarchies and filters. They should be wide, flat and easy to browse, with short column names and no nested logic.

Date: build a retail calendar, not a calendar month

UK retailers rarely trade on calendar months. Many use a 4-5-4 retail calendar with weeks starting on Sunday and a 52/53-week financial year, so your date dimension needs to carry both views: calendar day, month, quarter and year alongside retail week, retail period, financial year and the week number used in trading reports. Add flags for bank holidays, Christmas trading days, school holidays and promotional peaks, plus a previous year date key so like-for-like comparisons are a simple join rather than a pile of date arithmetic. Build this once and no one has to write another CASE statement.

Product and location

For product, capture SKU, description, brand, category and subcategory, supplier, own-brand flag, pack size, unit of measure and launch or delist dates. Keep the merchandise hierarchy in one table unless it is genuinely ragged. For location, store number, name, format (superstore, convenience, outlet), region, postcode area, opening and closure dates. Give your website, app and marketplace listings rows in the same table so channel comparisons do not need a union.

Customer, promotion and channel

Customer dimensions only work where you have consent and a reliable identifier such as a loyalty card or account. Guest checkout is normal, so keep an unknown member row rather than dropping the sale. Promotion deserves its own table: mechanic (multibuy, percentage off, three-for-two), funding split between supplier and retailer, and start and end dates. Channel covers in-store, click-and-collect, home delivery and marketplace, and it is worth separating from store format so the two can be sliced independently.

Model returns, VAT and refunds as first-class measures

Returns are part of retail, not an exception. Rather than mixing negative rows into the sales table without a marker, either store a signed quantity and a transaction type, or create a fact_returns table that shares the same conformed dimensions. Either approach works; what matters is that returns are never silently netted off where you cannot separate them again.

Money needs the same care. Store gross amount (what the customer paid), VAT and net amount separately. The standard rate is 20%, but zero-rated food, children's clothing and books, and reduced-rate items mean a single tax column on the fact table will be wrong. Keep the rate with the product or the individual line so net and gross can be derived correctly at any level of the hierarchy. Discounts should be recorded as their own measure, before VAT, so promotional cost is visible.

Plan for change with slowly changing dimensions

Categories get reorganised, prices shift, stores are refurbished, customers move between loyalty tiers. Decide per attribute how you handle that:

• Type 1 (overwrite) for corrections and genuine errors — a misspelt store name does not need history.
• Type 2 (new row) with valid_from, valid_to and is_current for anything finance will report historically, such as product category, store format or customer segment.

If your finance team reports last year's sales under last year's category structure, type 2 is not optional. Retrofitting it later means rebuilding the fact table.

Keys, naming and the details that save time

Use surrogate integer keys in every dimension and never join on a natural key such as a SKU or store number; source systems recycle codes more often than anyone expects. Keep explicit unknown rows: -1 for unknown and 0 for not applicable, so foreign keys are never null and inner joins do not quietly lose rows.

Three more habits pay off. Store receipt number, order number and till ID as degenerate dimensions directly on the fact table. Bundle low-cardinality flags — click-and-collect, gift receipt, marketplace order, employee discount — into a single junk dimension instead of adding ten narrow columns. And carry an audit column or companion table recording when each row was loaded, because you will need it the first time a nightly job fails.

Test the design against real questions

Before building anything on top, write ten or fifteen queries you know the business will ask. Like-for-like sales by store for the last retail period. Gross margin by category excluding promotions. Click-and-collect penetration by region. Returns rate by product and reason. Run them all against the model.

If a question needs two fact tables joined at mismatched grains, you have found a missing dimension or a second fact table. Once the queries run cleanly, reconcile the totals against the till system's daily close or the finance ledger. A dashboard that disagrees with the numbers in the board pack will be abandoned within a month, however elegant the schema behind it.

Start narrow and extend later

A sales line fact with date, product, store, channel and promotion will answer most questions for a long time. Add inventory snapshots, footfall counts or basket-level facts as separate tables, each with its own declared grain and the same conformed dimensions. Document the grain in a table comment, keep the surrogate keys consistent, and the model will still hold up the next time the buying team reorganises every category in the business.

Photo: drewstewart / Pixabay