Home › Data engineering › Gold data modelling › Silver to gold › Fact examples in SQL
Silver to gold · Examples in SQL · 19 min read
Fact table examples in SQL: eight kinds, each run and checked
Eight fact tables written as plain SQL on tiny tables. Each shows the silver in, the SQL, the gold out, when to use it and the trap that catches people. Every result is from a real run. This is one page of Silver to gold.
- How to read and run these
- The eight examples at a glance
- Transaction fact: one row per order line, with an on-time-in-full flag
- Periodic snapshot: stock level per warehouse, per product, per day
- Accumulating snapshot: one row per order line, filled in as it moves
- Factless fact: visits (an event) and promo coverage (what could have happened)
- Aggregate fact: OTIF by region and day, built from the fact above
- Consolidated fact: plan next to actual, at one shared grain
- Spreading an order-level amount (freight) over its lines
- Adjustment rows: corrections as new rows, never edits
Before you start
How to read and run these
Every example is plain SQL on tiny tables: silver in, SQL, gold out. The tables you see are the real results of running the SQL, in DuckDB 1.4, from _guides/code/silver_to_gold/ (run_sql.py runs all 19 examples; a table-driven test checks 60 claims such as "the shares add up to the freight"). Each Setup SQL box creates the silver table, so you can paste an example into any DuckDB and run it.
The tyre company is the one from the worked example. These pages show the business rule of each table, which is the part you write. Finding changed rows, picking windows and moving the bookmark is the harness's job, shown on the other pages.
If you run Spark or Databricks SQL instead: the few words that change
| Thing | DuckDB (used here) | Spark / Databricks SQL |
|---|---|---|
| A run of days | unnest(generate_series(d1, d2, INTERVAL 1 DAY)) | explode(sequence(d1, d2, INTERVAL 1 DAY)) |
| Days between two dates | date_diff('day', a, b) | datediff(b, a) |
| Date as 20261113 | strftime(d, '%Y%m%d') | date_format(d, 'yyyyMMdd') |
| Name of the weekday | dayname(d) | date_format(d, 'EEEE') |
| Count rows that meet a condition | count(*) FILTER (WHERE x) | count_if(x) (the FILTER form also works) |
| Keep the first row per group | QUALIFY row_number() OVER (...) = 1 | A subquery with row_number() (QUALIFY exists on Databricks, not in open-source Spark 3.5) |
| Hand out surrogate keys | row_number() OVER (ORDER BY ...) | An identity column, or a key map table, so keys never move between runs |
| MERGE | MERGE INTO ... USING ... (DuckDB 1.4) | MERGE INTO on a Delta table |
Which one?
The eight examples at a glance
Read them in order: the first builds fact_order_line, and the aggregate and plan-against-actual examples read it. The kind is chosen by the shape of the question, not by the data.
| Example | The question it answers | Verdict |
|---|---|---|
| Transaction | Did each order line arrive on time and in full? And how many tyres did we promise by region? | Pursue: The default |
| Periodic snapshot | How many tyres did each warehouse hold at the end of each day, including quiet days when nothing moved? | Pursue: For levels and balances |
| Accumulating snapshot | How many days does a line take from order to shipment, and which lines are still open? | Pursue: For multi-step processes |
| Factless | How many times did our reps visit each dealer? And which dealers were offered the November promo but never ordered? | Pursue: For coverage and 'who did not' questions |
| Aggregate | Show OTIF by region on a dashboard that must open instantly. | Pursue with care: Only where a question is slow and frequent |
| Consolidated | Per region and month: what did we plan, what did we deliver, and what is the gap? | Pursue: For plan against actual |
| Allocated header amount | Freight is charged per order, but profit is reported per product. How is it shared out without a cent going missing? | Pursue: Whenever a header amount must reach lines |
| Adjustment rows | What is line L1 worth now, and what did we report for it on 15 November? | Pursue: For finance-grade numbers that must not silently change |
1. Transaction fact
Transaction fact: one row per order line, with an on-time-in-full flag
One row means: One row per order line.
The workhorse. Something happened once (a line was ordered), so it gets one row, with the numbers that belong to it. Here the rule is: a line is OTIF when everything ordered was delivered, and the last delivery was on or before the promised date.
silver_order_lines
Silver in
| line_id | order_id | dealer_id | product_id | qty_ordered | unit_price | promised_date |
|---|---|---|---|---|---|---|
| L1 | O1 | D1 | P1 | 10 | 100 | 2026-11-10 |
| L2 | O1 | D1 | P2 | 5 | 80 | 2026-11-10 |
| L3 | O2 | D7 | P1 | 8 | 100 | 2026-11-11 |
| L4 | O2 | D7 | P3 | 4 | 150 | 2026-11-13 |
| L5 | O3 | D9 | P2 | 6 | 80 | 2026-11-13 |
| L6 | O4 | TEST01 | P1 | 1 | 100 | 2026-11-13 |
Setup SQL to copy and run
CREATE TABLE silver_order_lines (line_id VARCHAR, order_id VARCHAR, dealer_id VARCHAR, product_id VARCHAR,
qty_ordered INTEGER, unit_price INTEGER, promised_date DATE);
INSERT INTO silver_order_lines VALUES
('L1','O1','D1', 'P1',10,100,DATE '2026-11-10'),
('L2','O1','D1', 'P2', 5, 80,DATE '2026-11-10'),
('L3','O2','D7', 'P1', 8,100,DATE '2026-11-11'),
('L4','O2','D7', 'P3', 4,150,DATE '2026-11-13'),
('L5','O3','D9', 'P2', 6, 80,DATE '2026-11-13'),
('L6','O4','TEST01','P1', 1,100,DATE '2026-11-13'); -- a test dealer: never reaches goldsilver_deliveries
Silver in
| delivery_id | line_id | warehouse | qty | delivered_date |
|---|---|---|---|---|
| X1 | L1 | WH-A | 10 | 2026-11-10 |
| X2 | L2 | WH-A | 5 | 2026-11-11 |
| X3 | L3 | WH-B | 6 | 2026-11-11 |
| X3b | L3 | WH-B | 2 | 2026-11-12 |
| X4 | L4 | WH-A | 4 | 2026-11-13 |
| X5 | L5 | WH-A | 6 | 2026-11-13 |
Setup SQL to copy and run
CREATE TABLE silver_deliveries (delivery_id VARCHAR, line_id VARCHAR, warehouse VARCHAR, qty INTEGER, delivered_date DATE);
INSERT INTO silver_deliveries VALUES
('X1', 'L1','WH-A',10,DATE '2026-11-10'),
('X2', 'L2','WH-A', 5,DATE '2026-11-11'), -- a day late
('X3', 'L3','WH-B', 6,DATE '2026-11-11'), -- L3 comes in two deliveries
('X3b','L3','WH-B', 2,DATE '2026-11-12'),
('X4', 'L4','WH-A', 4,DATE '2026-11-13'),
('X5', 'L5','WH-A', 6,DATE '2026-11-13');dim_dealer
Silver in
| dealer_key | dealer_id | name | region | valid_from | valid_to |
|---|---|---|---|---|---|
| -1 | ? | Unknown | Unknown | 1900-01-01 | 9999-12-31 |
| 1 | D1 | Sharma Tyres | North | 2026-11-01 | 9999-12-31 |
| 2 | D7 | Patel Motors | North | 2026-11-01 | 2026-11-12 |
| 3 | D7 | Patel Motors | West | 2026-11-12 | 9999-12-31 |
Setup SQL to copy and run
CREATE TABLE dim_dealer (dealer_key INTEGER, dealer_id VARCHAR, name VARCHAR, region VARCHAR, valid_from DATE, valid_to DATE);
INSERT INTO dim_dealer VALUES
(-1,'?', 'Unknown', 'Unknown',DATE '1900-01-01',DATE '9999-12-31'),
( 1,'D1','Sharma Tyres', 'North', DATE '2026-11-01',DATE '9999-12-31'),
( 2,'D7','Patel Motors', 'North', DATE '2026-11-01',DATE '2026-11-12'),
( 3,'D7','Patel Motors', 'West', DATE '2026-11-12',DATE '9999-12-31');1. The build. Add the child rows (deliveries) up to one row per line first, then join. Then look up the dealer version true on the promised date, with -1 for a dealer silver has not got yet.
CREATE TABLE fact_order_line AS
WITH delivered AS ( -- one row per line, BEFORE joining
SELECT line_id, sum(qty) AS qty_delivered, max(delivered_date) AS last_delivered
FROM silver_deliveries GROUP BY line_id)
SELECT o.line_id,
o.order_id, -- degenerate dimension
COALESCE(d.dealer_key, -1) AS dealer_key,
CAST(strftime(o.promised_date, '%Y%m%d') AS INTEGER) AS promised_date_key,
o.qty_ordered,
COALESCE(x.qty_delivered, 0) AS qty_delivered,
o.qty_ordered * o.unit_price AS line_amount,
COALESCE(x.qty_delivered, 0) >= o.qty_ordered
AND x.last_delivered <= o.promised_date AS is_otif
FROM silver_order_lines o
LEFT JOIN delivered x ON x.line_id = o.line_id
LEFT JOIN dim_dealer d
ON d.dealer_id = o.dealer_id
AND o.promised_date >= d.valid_from AND o.promised_date < d.valid_to
WHERE o.dealer_id NOT LIKE 'TEST%';Result
| line_id | order_id | dealer_key | promised_date_key | qty_ordered | qty_delivered | line_amount | is_otif |
|---|---|---|---|---|---|---|---|
| L1 | O1 | 1 | 20261110 | 10 | 10 | 1000 | true |
| L2 | O1 | 1 | 20261110 | 5 | 5 | 400 | false |
| L3 | O2 | 2 | 20261111 | 8 | 8 | 800 | false |
| L4 | O2 | 3 | 20261113 | 4 | 4 | 600 | true |
| L5 | O3 | -1 | 20261113 | 6 | 6 | 480 | true |
2. Check the grain: one row per line. This must return no rows.
SELECT line_id, count(*) AS n
FROM fact_order_line GROUP BY line_id HAVING count(*) > 1;Result
(no rows: that is the right answer here)
3. The trap, on purpose: join the deliveries straight in. L3 has two deliveries, so it appears twice and its 8 tyres are counted as 16.
SELECT sum(o.qty_ordered) AS tyres_ordered_WRONG
FROM silver_order_lines o
LEFT JOIN silver_deliveries x ON x.line_id = o.line_id
WHERE o.dealer_id NOT LIKE 'TEST%';Result
| tyres_ordered_WRONG | tyres_ordered_RIGHT |
|---|---|
| 41 | 33 |
2. Periodic snapshot
Periodic snapshot: stock level per warehouse, per product, per day
One row means: One row per warehouse, per product, per day, from the first day it had stock.
Stock is a level, not an event. Nothing 'happens' on a quiet day, but the question 'how much on Wednesday?' still has an answer. So we write a row for every day, carrying the level forward.
silver_stock_movements
Silver in
| warehouse | product_id | movement_date | qty_change |
|---|---|---|---|
| WH-A | P1 | 2026-11-10 | 100 |
| WH-A | P1 | 2026-11-12 | -30 |
| WH-A | P2 | 2026-11-10 | 50 |
| WH-A | P2 | 2026-11-13 | -20 |
| WH-B | P1 | 2026-11-11 | 60 |
Setup SQL to copy and run
CREATE TABLE silver_stock_movements (warehouse VARCHAR, product_id VARCHAR, movement_date DATE, qty_change INTEGER);
INSERT INTO silver_stock_movements VALUES
('WH-A','P1',DATE '2026-11-10', 100), -- received
('WH-A','P1',DATE '2026-11-12', -30), -- dispatched
('WH-A','P2',DATE '2026-11-10', 50),
('WH-A','P2',DATE '2026-11-13', -20),
('WH-B','P1',DATE '2026-11-11', 60);1. Build a row for every day from each pair's first movement, then add up movements to date. Days with no movement get net 0 and keep the level. (In Spark or Databricks, replace the day generator with explode(sequence(...)).)
CREATE TABLE fact_stock_daily AS
WITH pairs AS (
SELECT warehouse, product_id, min(movement_date) AS first_day
FROM silver_stock_movements GROUP BY warehouse, product_id),
days AS (
SELECT CAST(unnest(generate_series(DATE '2026-11-10', DATE '2026-11-14', INTERVAL 1 DAY)) AS DATE) AS day),
spine AS (
SELECT p.warehouse, p.product_id, d.day
FROM pairs p JOIN days d ON d.day >= p.first_day),
moved AS (
SELECT warehouse, product_id, movement_date AS day, sum(qty_change) AS net
FROM silver_stock_movements GROUP BY warehouse, product_id, movement_date)
SELECT s.warehouse, s.product_id, s.day,
COALESCE(m.net, 0) AS net_movement,
sum(COALESCE(m.net, 0)) OVER (PARTITION BY s.warehouse, s.product_id
ORDER BY s.day) AS closing_qty
FROM spine s
LEFT JOIN moved m USING (warehouse, product_id, day);Result
| warehouse | product_id | day | net_movement | closing_qty |
|---|---|---|---|---|
| WH-A | P1 | 2026-11-10 | 100 | 100 |
| WH-A | P1 | 2026-11-11 | 0 | 100 |
| WH-A | P1 | 2026-11-12 | -30 | 70 |
| WH-A | P1 | 2026-11-13 | 0 | 70 |
| WH-A | P1 | 2026-11-14 | 0 | 70 |
| WH-A | P2 | 2026-11-10 | 50 | 50 |
| WH-A | P2 | 2026-11-11 | 0 | 50 |
| WH-A | P2 | 2026-11-12 | 0 | 50 |
| WH-A | P2 | 2026-11-13 | -20 | 30 |
| WH-A | P2 | 2026-11-14 | 0 | 30 |
| WH-B | P1 | 2026-11-11 | 60 | 60 |
| WH-B | P1 | 2026-11-12 | 0 | 60 |
| WH-B | P1 | 2026-11-13 | 0 | 60 |
| WH-B | P1 | 2026-11-14 | 0 | 60 |
2. What you may add up. Across warehouses on one day: fine. Across days: never. Compare the three answers for product P1.
SELECT
(SELECT sum(closing_qty) FROM fact_stock_daily
WHERE product_id = 'P1' AND day = DATE '2026-11-14') AS right_latest_day_all_warehouses,
(SELECT sum(closing_qty) FROM fact_stock_daily WHERE product_id = 'P1') AS wrong_added_over_days,
(SELECT round(avg(t), 1) FROM (SELECT day, sum(closing_qty) AS t
FROM fact_stock_daily WHERE product_id = 'P1' GROUP BY day)) AS right_average_stock;Result
| right_latest_day_all_warehouses | wrong_added_over_days | right_average_stock |
|---|---|---|
| 130 | 650 | 130 |
3. Accumulating snapshot
Accumulating snapshot: one row per order line, filled in as it moves
One row means: One row per order line, for its whole life.
A line goes through steps: ordered, picked, shipped, delivered. Instead of four rows, there is one row with four date columns. Empty at first, filled in as each step happens. This is the one fact kind whose rows change after they are written.
silver_line_events
Silver in
| line_id | event | event_date |
|---|---|---|
| L1 | ordered | 2026-11-08 |
| L1 | picked | 2026-11-09 |
| L1 | shipped | 2026-11-09 |
| L1 | delivered | 2026-11-10 |
| L2 | ordered | 2026-11-08 |
| L2 | picked | 2026-11-09 |
| L2 | shipped | 2026-11-10 |
| L3 | ordered | 2026-11-09 |
| L3 | picked | 2026-11-10 |
Setup SQL to copy and run
CREATE TABLE silver_line_events (line_id VARCHAR, event VARCHAR, event_date DATE);
INSERT INTO silver_line_events VALUES
('L1','ordered', DATE '2026-11-08'), ('L1','picked', DATE '2026-11-09'),
('L1','shipped', DATE '2026-11-09'), ('L1','delivered',DATE '2026-11-10'),
('L2','ordered', DATE '2026-11-08'), ('L2','picked', DATE '2026-11-09'), ('L2','shipped', DATE '2026-11-10'),
('L3','ordered', DATE '2026-11-09'), ('L3','picked', DATE '2026-11-10');1. The processor, as a view: turn milestone events into one row per line. It is pure: events in, rows out.
CREATE VIEW v_line_pipeline AS
SELECT line_id,
max(CASE WHEN event = 'ordered' THEN event_date END) AS ordered_date,
max(CASE WHEN event = 'picked' THEN event_date END) AS picked_date,
max(CASE WHEN event = 'shipped' THEN event_date END) AS shipped_date,
max(CASE WHEN event = 'delivered' THEN event_date END) AS delivered_date
FROM silver_line_events GROUP BY line_id;2. Night 1: build the table, with the measures worked out from the dates. Empty steps stay empty. An 'is_open' flag stops open lines hiding inside averages.
CREATE TABLE fact_line_pipeline AS
SELECT line_id, ordered_date, picked_date, shipped_date, delivered_date,
date_diff('day', ordered_date, shipped_date) AS days_order_to_ship,
delivered_date IS NULL AS is_open
FROM v_line_pipeline;Result
| line_id | ordered_date | picked_date | shipped_date | delivered_date | days_order_to_ship | is_open |
|---|---|---|---|---|---|---|
| L1 | 2026-11-08 | 2026-11-09 | 2026-11-09 | 2026-11-10 | 1 | false |
| L2 | 2026-11-08 | 2026-11-09 | 2026-11-10 | 2 | true | |
| L3 | 2026-11-09 | 2026-11-10 | true |
3. Night 2: new events arrive. L2 is delivered, L3 ships, and a new line L4 is ordered.
INSERT INTO silver_line_events VALUES
('L2','delivered',DATE '2026-11-11'), ('L3','shipped',DATE '2026-11-11'), ('L4','ordered',DATE '2026-11-11');4. Night 2, the writer: MERGE by line. Rows that exist are updated, new lines are inserted. Run it twice and nothing changes.
MERGE INTO fact_line_pipeline t
USING (SELECT line_id, ordered_date, picked_date, shipped_date, delivered_date,
date_diff('day', ordered_date, shipped_date) AS days_order_to_ship,
delivered_date IS NULL AS is_open
FROM v_line_pipeline) s
ON t.line_id = s.line_id
WHEN MATCHED THEN UPDATE SET
ordered_date = s.ordered_date, picked_date = s.picked_date,
shipped_date = s.shipped_date, delivered_date = s.delivered_date,
days_order_to_ship = s.days_order_to_ship, is_open = s.is_open
WHEN NOT MATCHED THEN INSERT VALUES
(s.line_id, s.ordered_date, s.picked_date, s.shipped_date, s.delivered_date,
s.days_order_to_ship, s.is_open);Result
| line_id | ordered_date | picked_date | shipped_date | delivered_date | days_order_to_ship | is_open |
|---|---|---|---|---|---|---|
| L1 | 2026-11-08 | 2026-11-09 | 2026-11-09 | 2026-11-10 | 1 | false |
| L2 | 2026-11-08 | 2026-11-09 | 2026-11-10 | 2026-11-11 | 2 | false |
| L3 | 2026-11-09 | 2026-11-10 | 2026-11-11 | 2 | true | |
| L4 | 2026-11-11 | true |
5. The question: average days to ship, and how many lines are still open. Open lines are counted, never averaged away.
SELECT round(avg(days_order_to_ship), 2) AS avg_days_to_ship,
count(*) FILTER (WHERE is_open) AS open_lines,
count(*) AS all_lines
FROM fact_line_pipeline;Result
| avg_days_to_ship | open_lines | all_lines |
|---|---|---|
| 1.67 | 2 | 4 |
4. Factless fact
Factless fact: visits (an event) and promo coverage (what could have happened)
One row means: Visits: one row per visit. Coverage: one row per promo per eligible dealer.
Some facts have nothing to add up. A rep visited a dealer: that is the whole fact. You count rows. A second kind records what was possible (who was offered a promo) so you can later ask what did not happen.
silver_rep_visits
Silver in
| visit_id | dealer_id | rep | visit_date |
|---|---|---|---|
| V1 | D1 | Asha | 2026-11-03 |
| V2 | D1 | Asha | 2026-11-10 |
| V3 | D7 | Ravi | 2026-11-04 |
Setup SQL to copy and run
CREATE TABLE silver_rep_visits (visit_id VARCHAR, dealer_id VARCHAR, rep VARCHAR, visit_date DATE);
INSERT INTO silver_rep_visits VALUES
('V1','D1','Asha',DATE '2026-11-03'), ('V2','D1','Asha',DATE '2026-11-10'),
('V3','D7','Ravi',DATE '2026-11-04');dim_dealer_names
Silver in
| dealer_id | name |
|---|---|
| D1 | Sharma Tyres |
| D7 | Patel Motors |
| D11 | Khan Auto |
| D12 | Rao Wheels |
Setup SQL to copy and run
CREATE TABLE dim_dealer_names (dealer_id VARCHAR, name VARCHAR);
INSERT INTO dim_dealer_names VALUES ('D1','Sharma Tyres'),('D7','Patel Motors'),('D11','Khan Auto'),('D12','Rao Wheels');silver_promo_offers
Silver in
| promo_id | dealer_id | start_date | end_date |
|---|---|---|---|
| NOV-DEAL | D1 | 2026-11-10 | 2026-11-14 |
| NOV-DEAL | D7 | 2026-11-10 | 2026-11-14 |
| NOV-DEAL | D11 | 2026-11-10 | 2026-11-14 |
| NOV-DEAL | D12 | 2026-11-10 | 2026-11-14 |
Setup SQL to copy and run
CREATE TABLE silver_promo_offers (promo_id VARCHAR, dealer_id VARCHAR, start_date DATE, end_date DATE);
INSERT INTO silver_promo_offers VALUES
('NOV-DEAL','D1', DATE '2026-11-10', DATE '2026-11-14'),
('NOV-DEAL','D7', DATE '2026-11-10', DATE '2026-11-14'),
('NOV-DEAL','D11',DATE '2026-11-10', DATE '2026-11-14'),
('NOV-DEAL','D12',DATE '2026-11-10', DATE '2026-11-14');1. The event fact has keys and no numbers. One row per visit.
CREATE TABLE fact_dealer_visit AS
SELECT DISTINCT visit_id, dealer_id, rep, visit_date -- DISTINCT: a re-sent visit is still one visit
FROM silver_rep_visits;Result
| visit_id | dealer_id | rep | visit_date |
|---|---|---|---|
| V1 | D1 | Asha | 2026-11-03 |
| V2 | D1 | Asha | 2026-11-10 |
| V3 | D7 | Ravi | 2026-11-04 |
2. Count the rows. Start from the dimension so dealers with no visits show 0 (a fact alone cannot show them).
SELECT d.name, count(v.visit_id) AS visits
FROM dim_dealer_names d
LEFT JOIN fact_dealer_visit v USING (dealer_id)
GROUP BY d.name ORDER BY visits DESC, d.name;Result
| name | visits |
|---|---|
| Sharma Tyres | 2 |
| Patel Motors | 1 |
| Khan Auto | 0 |
| Rao Wheels | 0 |
3. The coverage fact is silver_promo_offers: who was offered the promo. The question 'who did not order?' is an anti-join between coverage and what happened (orders in the promo window, from the transaction fact).
SELECT p.promo_id, p.dealer_id, n.name
FROM silver_promo_offers p
JOIN dim_dealer_names n USING (dealer_id)
WHERE NOT EXISTS (
SELECT 1 FROM silver_order_lines o
WHERE o.dealer_id = p.dealer_id
AND o.promised_date BETWEEN p.start_date AND p.end_date)
ORDER BY p.dealer_id;Result
| promo_id | dealer_id | name |
|---|---|---|
| NOV-DEAL | D11 | Khan Auto |
| NOV-DEAL | D12 | Rao Wheels |
5. Aggregate fact
Aggregate fact: OTIF by region and day, built from the fact above
One row means: One row per region per promised date.
A summary of another gold fact, kept because a busy dashboard asks the same question all day. Built from the transaction fact, never from silver, so the two always agree. Store the counts, not just the percent.
Reads fact_order_line and dim_dealer, built in the first example.
1. Group the transaction fact. Keep lines and otif_lines as counts.
CREATE TABLE fact_otif_region_day AS
SELECT d.region,
CAST(strptime(CAST(f.promised_date_key AS VARCHAR), '%Y%m%d') AS DATE) AS promised_date,
count(*) AS lines,
sum(CASE WHEN f.is_otif THEN 1 ELSE 0 END) AS otif_lines,
sum(f.qty_ordered) AS qty_ordered
FROM fact_order_line f
JOIN dim_dealer d USING (dealer_key)
GROUP BY d.region, f.promised_date_key;Result
| region | promised_date | lines | otif_lines | qty_ordered | otif_pct |
|---|---|---|---|---|---|
| North | 2026-11-10 | 2 | 1 | 15 | 50 |
| North | 2026-11-11 | 1 | 0 | 8 | 0 |
| Unknown | 2026-11-13 | 1 | 1 | 6 | 100 |
| West | 2026-11-13 | 1 | 1 | 4 | 100 |
2. Roll up to region. The percent of percents is wrong; the sum of counts is right.
SELECT region,
round(avg(100.0 * otif_lines / lines), 1) AS wrong_avg_of_percents,
round(100.0 * sum(otif_lines) / sum(lines), 1) AS right_percent_from_counts
FROM fact_otif_region_day
GROUP BY region ORDER BY region;Result
| region | wrong_avg_of_percents | right_percent_from_counts |
|---|---|---|
| North | 25 | 33.3 |
| Unknown | 100 | 100 |
| West | 100 | 100 |
6. Consolidated fact
Consolidated fact: plan next to actual, at one shared grain
One row means: One row per region per month.
Plan comes from finance, actuals from our own fact. They arrive at different grains. Bring both to region and month first, then put them side by side with a FULL OUTER JOIN, so a region with only a plan, or only actuals, still shows.
Reads fact_order_line and dim_dealer from the first example, and finance's plan below.
silver_sales_plan
Silver in
| region | month | planned_qty |
|---|---|---|
| North | 2026-11 | 100 |
| West | 2026-11 | 60 |
| South | 2026-11 | 40 |
Setup SQL to copy and run
CREATE TABLE silver_sales_plan (region VARCHAR, month VARCHAR, planned_qty INTEGER);
INSERT INTO silver_sales_plan VALUES ('North','2026-11',100), ('West','2026-11',60), ('South','2026-11',40);1. Two inputs brought to the same grain, then combined. COALESCE turns a missing side into 0 and gap shows what is missing.
CREATE TABLE fact_plan_vs_actual AS
WITH actual AS (
SELECT d.region,
strftime(CAST(strptime(CAST(f.promised_date_key AS VARCHAR), '%Y%m%d') AS DATE), '%Y-%m') AS month,
sum(f.qty_ordered) AS actual_qty
FROM fact_order_line f JOIN dim_dealer d USING (dealer_key)
GROUP BY 1, 2)
SELECT COALESCE(p.region, a.region) AS region,
COALESCE(p.month, a.month) AS month,
COALESCE(p.planned_qty, 0) AS planned_qty,
COALESCE(a.actual_qty, 0) AS actual_qty,
COALESCE(a.actual_qty, 0) - COALESCE(p.planned_qty, 0) AS gap
FROM silver_sales_plan p
FULL OUTER JOIN actual a ON a.region = p.region AND a.month = p.month;Result
| region | month | planned_qty | actual_qty | gap |
|---|---|---|---|---|
| North | 2026-11 | 100 | 23 | -77 |
| South | 2026-11 | 40 | 0 | -40 |
| Unknown | 2026-11 | 0 | 6 | 6 |
| West | 2026-11 | 60 | 4 | -56 |
7. Transaction fact with allocation
Spreading an order-level amount (freight) over its lines
One row means: One row per order line, with its share of the order's freight.
The amount belongs to the order (the header), but the fact is at line level. Share it in proportion to line value. Naive rounding loses or invents cents, so the share is worked out on a running total and each line gets the difference.
silver_alloc_lines
Silver in
| order_id | line_id | line_amount |
|---|---|---|
| A1 | A1-1 | 100.00 |
| A1 | A1-2 | 100.00 |
| A1 | A1-3 | 100.00 |
| B1 | B1-1 | 1000.00 |
| B1 | B1-2 | 400.00 |
Setup SQL to copy and run
CREATE TABLE silver_alloc_lines (order_id VARCHAR, line_id VARCHAR, line_amount DECIMAL(12,2));
INSERT INTO silver_alloc_lines VALUES
('A1','A1-1',100.00), ('A1','A1-2',100.00), ('A1','A1-3',100.00),
('B1','B1-1',1000.00), ('B1','B1-2', 400.00);silver_order_freight
Silver in
| order_id | freight |
|---|---|
| A1 | 100.00 |
| B1 | 90.00 |
Setup SQL to copy and run
CREATE TABLE silver_order_freight (order_id VARCHAR, freight DECIMAL(12,2));
INSERT INTO silver_order_freight VALUES ('A1',100.00), ('B1',90.00);1. The wrong way: round each line's share. Three equal lines each get 33.33, and 100.00 becomes 99.99.
SELECT order_id, freight, sum(share) AS allocated_naive
FROM (SELECT l.order_id, f.freight,
round(f.freight * l.line_amount / sum(l.line_amount) OVER (PARTITION BY l.order_id), 2) AS share
FROM silver_alloc_lines l JOIN silver_order_freight f USING (order_id))
GROUP BY order_id, freight ORDER BY order_id;Result
| order_id | freight | allocated_naive |
|---|---|---|
| A1 | 100.00 | 99.99 |
| B1 | 90.00 | 90 |
2. The right way: each line gets (rounded running share) minus (rounded running share of the line before it). The pieces add up to exactly the freight.
CREATE TABLE fact_line_freight AS
WITH running AS (
SELECT l.order_id, l.line_id, l.line_amount, f.freight,
sum(l.line_amount) OVER (PARTITION BY l.order_id ORDER BY l.line_id) AS cum_amount,
sum(l.line_amount) OVER (PARTITION BY l.order_id) AS total_amount
FROM silver_alloc_lines l JOIN silver_order_freight f USING (order_id)),
shares AS (
SELECT *, round(freight * cum_amount / total_amount, 2) AS cum_share FROM running)
SELECT order_id, line_id, line_amount,
cum_share - COALESCE(lag(cum_share) OVER (PARTITION BY order_id ORDER BY line_id), 0) AS freight_share
FROM shares;Result
| order_id | line_id | line_amount | freight_share |
|---|---|---|---|
| A1 | A1-1 | 100.00 | 33.33 |
| A1 | A1-2 | 100.00 | 33.34 |
| A1 | A1-3 | 100.00 | 33.33 |
| B1 | B1-1 | 1000.00 | 64.29 |
| B1 | B1-2 | 400.00 | 25.71 |
3. The check every allocation needs: the shares add back to the header, order by order. This returns no rows when it is right.
SELECT f.order_id, f.freight, sum(a.freight_share) AS allocated
FROM silver_order_freight f JOIN fact_line_freight a USING (order_id)
GROUP BY f.order_id, f.freight
HAVING sum(a.freight_share) <> f.freight;Result
(no rows: that is the right answer here)
8. Fact with adjustment rows
Adjustment rows: corrections as new rows, never edits
One row means: One row per amount entry (the original, then each correction).
When a number is corrected, don't change the old row. Add a new row with the difference, dated by when it was recorded. Add them all up for today's truth; add up to a date for what was known then.
silver_sales_entries
Silver in
| entry_id | line_id | recorded_on | amount | reason |
|---|---|---|---|---|
| E1 | L1 | 2026-11-10 | 1000 | original |
| E2 | L1 | 2026-11-20 | -200 | price corrected |
| E3 | L1 | 2026-11-22 | 50 | late surcharge |
| E4 | L2 | 2026-11-10 | 400 | original |
Setup SQL to copy and run
CREATE TABLE silver_sales_entries (entry_id VARCHAR, line_id VARCHAR, recorded_on DATE, amount INTEGER, reason VARCHAR);
INSERT INTO silver_sales_entries VALUES
('E1','L1',DATE '2026-11-10', 1000, 'original'),
('E2','L1',DATE '2026-11-20', -200, 'price corrected'),
('E3','L1',DATE '2026-11-22', 50, 'late surcharge'),
('E4','L2',DATE '2026-11-10', 400, 'original');1. The fact is the silver entries as they are: append-only, one row per entry.
CREATE TABLE fact_sales_ledger AS
SELECT entry_id, line_id, recorded_on, amount, reason FROM silver_sales_entries;Result
| entry_id | line_id | recorded_on | amount | reason |
|---|---|---|---|---|
| E1 | L1 | 2026-11-10 | 1000 | original |
| E2 | L1 | 2026-11-20 | -200 | price corrected |
| E3 | L1 | 2026-11-22 | 50 | late surcharge |
| E4 | L2 | 2026-11-10 | 400 | original |
2. Today's truth, and what we knew on 15 November. Same table, one extra filter.
SELECT line_id,
sum(amount) AS amount_now,
sum(amount) FILTER (WHERE recorded_on <= DATE '2026-11-15') AS amount_reported_on_15_nov
FROM fact_sales_ledger GROUP BY line_id ORDER BY line_id;Result
| line_id | amount_now | amount_reported_on_15_nov |
|---|---|---|
| L1 | 850 | 1000 |
| L2 | 400 | 400 |