This page walks through a complete retail sales star schema designed with the Kimball four-step method: choose the business process, declare the grain, identify the dimensions, and identify the facts. The sections below define each table, then show the diagram, the load process, working SQL and the design trade-offs.
Sales — capturing each product sold to a customer at a point in time.
One row per product per order per day
This sales dimensional model captures transactional sales at line-item grain using a star schema optimized for analytics.
The fact table sits at the centre and holds one row per product per order line. Its foreign keys point to five dimensions, and its numeric measures (quantity, gross, discount, net, tax, cost) are additive, so they can be summed across any combination of dimension attributes. Dimensions are wide and denormalised: dim_product stores brand, category and subcategory in one table rather than snowflaking them into separate tables, which keeps every query to one join per dimension.
A typical nightly batch extracts new and changed orders, loads dimensions first, then the fact table. For dim_customer, which is Slowly Changing Dimension Type 2, a change in a tracked attribute such as region closes the current row (sets expiration_date and current_flag = 'N') and inserts a new row with a new surrogate key. Fact rows are then assigned the surrogate key that was current on the order date, so history is reported as it was at the time of sale. Late-arriving or unmatched customers are mapped to an "Unknown" member rather than dropped, which keeps fact totals reconciled to the source.
-- SCD Type 2: close changed rows, then insert new versions
UPDATE dim_customer d
SET expiration_date = CURRENT_DATE - 1, current_flag = 'N'
FROM stg_customer s
WHERE d.customer_id = s.customer_id AND d.current_flag = 'Y'
AND (d.region <> s.region OR d.customer_segment <> s.customer_segment);
INSERT INTO dim_customer (customer_id, first_name, last_name, gender, birth_date,
customer_segment, region, effective_date, expiration_date, current_flag)
SELECT s.customer_id, s.first_name, s.last_name, s.gender, s.birth_date,
s.customer_segment, s.region, CURRENT_DATE, DATE '9999-12-31', 'Y'
FROM stg_customer s
LEFT JOIN dim_customer d ON d.customer_id = s.customer_id AND d.current_flag = 'Y'
WHERE d.customer_id IS NULL;
-- Net sales by category and month
SELECT p.category, d.year, d.month, SUM(f.net_sales_amount) AS net_sales
FROM fact_sales f
JOIN dim_product p ON p.product_key = f.product_key
JOIN dim_date d ON d.date_key = f.date_key
GROUP BY p.category, d.year, d.month
ORDER BY d.year, d.month, net_sales DESC;
-- Top 10 customers by revenue this year
SELECT c.customer_id, c.first_name, c.last_name, SUM(f.net_sales_amount) AS revenue
FROM fact_sales f
JOIN dim_customer c ON c.customer_key = f.customer_key
JOIN dim_date d ON d.date_key = f.date_key
WHERE d.year = 2026
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY revenue DESC
LIMIT 10;
-- Promotion effectiveness: margin with and without promotion
SELECT pr.promotion_type,
SUM(f.net_sales_amount - f.cost_amount) AS margin,
SUM(f.quantity_sold) AS units
FROM fact_sales f
JOIN dim_promotion pr ON pr.promotion_key = f.promotion_key
GROUP BY pr.promotion_type;
Note that the customer query groups by the natural key customer_id, not the surrogate key, so a customer with several SCD2 versions is counted once. Grouping by customer_key would split their revenue across versions.
| Choice | This model | Alternative and when to use it |
|---|---|---|
| Grain | Order line | Daily summary per product and store: smaller and faster, but cannot answer basket questions |
| Customer history | SCD Type 2 | Type 1 overwrite when history of attributes does not matter |
| Product hierarchy | Flattened in one dimension | Snowflaked tables when the hierarchy is huge and shared, at the cost of more joins |
| Order number | Degenerate dimension | Separate order dimension only if orders have their own descriptive attributes |
| No-promotion sales | Point to a "No Promotion" row | Nullable key: avoid, because nulls break inner joins and counts |