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.

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
ThingDuckDB (used here)Spark / Databricks SQL
A run of daysunnest(generate_series(d1, d2, INTERVAL 1 DAY))explode(sequence(d1, d2, INTERVAL 1 DAY))
Days between two datesdate_diff('day', a, b)datediff(b, a)
Date as 20261113strftime(d, '%Y%m%d')date_format(d, 'yyyyMMdd')
Name of the weekdaydayname(d)date_format(d, 'EEEE')
Count rows that meet a conditioncount(*) FILTER (WHERE x)count_if(x) (the FILTER form also works)
Keep the first row per groupQUALIFY row_number() OVER (...) = 1A subquery with row_number() (QUALIFY exists on Databricks, not in open-source Spark 3.5)
Hand out surrogate keysrow_number() OVER (ORDER BY ...)An identity column, or a key map table, so keys never move between runs
MERGEMERGE 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.

ExampleThe question it answersVerdict
TransactionDid each order line arrive on time and in full? And how many tyres did we promise by region?Pursue: The default
Periodic snapshotHow many tyres did each warehouse hold at the end of each day, including quiet days when nothing moved?Pursue: For levels and balances
Accumulating snapshotHow many days does a line take from order to shipment, and which lines are still open?Pursue: For multi-step processes
FactlessHow 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
AggregateShow OTIF by region on a dashboard that must open instantly.Pursue with care: Only where a question is slow and frequent
ConsolidatedPer region and month: what did we plan, what did we deliver, and what is the gap?Pursue: For plan against actual
Allocated header amountFreight 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 rowsWhat 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

The question: Did each order line arrive on time and in full? And how many tyres did we promise by region?

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_idorder_iddealer_idproduct_idqty_orderedunit_pricepromised_date
L1O1D1P1101002026-11-10
L2O1D1P25802026-11-10
L3O2D7P181002026-11-11
L4O2D7P341502026-11-13
L5O3D9P26802026-11-13
L6O4TEST01P111002026-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 gold

silver_deliveries

Silver in

delivery_idline_idwarehouseqtydelivered_date
X1L1WH-A102026-11-10
X2L2WH-A52026-11-11
X3L3WH-B62026-11-11
X3bL3WH-B22026-11-12
X4L4WH-A42026-11-13
X5L5WH-A62026-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_keydealer_idnameregionvalid_fromvalid_to
-1?UnknownUnknown1900-01-019999-12-31
1D1Sharma TyresNorth2026-11-019999-12-31
2D7Patel MotorsNorth2026-11-012026-11-12
3D7Patel MotorsWest2026-11-129999-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_idorder_iddealer_keypromised_date_keyqty_orderedqty_deliveredline_amountis_otif
L1O112026111010101000true
L2O112026111055400false
L3O222026111188800false
L4O232026111344600true
L5O3-12026111366480true

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_WRONGtyres_ordered_RIGHT
4133
Use it whenAlmost always. If you are unsure which kind a fact is, start here.
The trapJoining child rows without adding them up first. And looking up the dealer's current row instead of the row true on the date.

2. Periodic snapshot

Periodic snapshot: stock level per warehouse, per product, per day

The question: How many tyres did each warehouse hold at the end of each day, including quiet days when nothing moved?

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

warehouseproduct_idmovement_dateqty_change
WH-AP12026-11-10100
WH-AP12026-11-12-30
WH-AP22026-11-1050
WH-AP22026-11-13-20
WH-BP12026-11-1160
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

warehouseproduct_iddaynet_movementclosing_qty
WH-AP12026-11-10100100
WH-AP12026-11-110100
WH-AP12026-11-12-3070
WH-AP12026-11-13070
WH-AP12026-11-14070
WH-AP22026-11-105050
WH-AP22026-11-11050
WH-AP22026-11-12050
WH-AP22026-11-13-2030
WH-AP22026-11-14030
WH-BP12026-11-116060
WH-BP12026-11-12060
WH-BP12026-11-13060
WH-BP12026-11-14060

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_warehouseswrong_added_over_daysright_average_stock
130650130
Use it whenLevels and balances: stock, bank balance, headcount, open orders at the end of each day.
The trapNever add a level across days: 100 on Monday plus 100 on Tuesday is not 200 tyres. Add across warehouses, then take the last day or the average over days. And a day before the first movement should show 0 or no row by design, not a surprise gap.

3. Accumulating snapshot

Accumulating snapshot: one row per order line, filled in as it moves

The question: How many days does a line take from order to shipment, and which lines are still open?

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_ideventevent_date
L1ordered2026-11-08
L1picked2026-11-09
L1shipped2026-11-09
L1delivered2026-11-10
L2ordered2026-11-08
L2picked2026-11-09
L2shipped2026-11-10
L3ordered2026-11-09
L3picked2026-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_idordered_datepicked_dateshipped_datedelivered_datedays_order_to_shipis_open
L12026-11-082026-11-092026-11-092026-11-101false
L22026-11-082026-11-092026-11-102true
L32026-11-092026-11-10true

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_idordered_datepicked_dateshipped_datedelivered_datedays_order_to_shipis_open
L12026-11-082026-11-092026-11-092026-11-101false
L22026-11-082026-11-092026-11-102026-11-112false
L32026-11-092026-11-102026-11-112true
L42026-11-11true

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_shipopen_linesall_lines
1.6724
Use it whenAnything with a start, several steps and an end: orders, loan applications, claims, support tickets.
The trapEvery update is effectively a restatement, so log changes you have already published. And a line that never reaches the last step needs the open flag, or an average hides it.

4. Factless fact

Factless fact: visits (an event) and promo coverage (what could have happened)

The question: How many times did our reps visit each dealer? And which dealers were offered the November promo but never ordered?

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_iddealer_idrepvisit_date
V1D1Asha2026-11-03
V2D1Asha2026-11-10
V3D7Ravi2026-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_idname
D1Sharma Tyres
D7Patel Motors
D11Khan Auto
D12Rao 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_iddealer_idstart_dateend_date
NOV-DEALD12026-11-102026-11-14
NOV-DEALD72026-11-102026-11-14
NOV-DEALD112026-11-102026-11-14
NOV-DEALD122026-11-102026-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_iddealer_idrepvisit_date
V1D1Asha2026-11-03
V2D1Asha2026-11-10
V3D7Ravi2026-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

namevisits
Sharma Tyres2
Patel Motors1
Khan Auto0
Rao Wheels0

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_iddealer_idname
NOV-DEALD11Khan Auto
NOV-DEALD12Rao Wheels
Use it whenAttendance, visits, logins, eligibility, enrolment: events with nothing to measure, and 'who did not' questions.
The trap'What did NOT happen' cannot be read from the events alone. Keep a coverage fact (who was in scope) or compare with the full dimension.

5. Aggregate fact

Aggregate fact: OTIF by region and day, built from the fact above

The question: Show OTIF by region on a dashboard that must open instantly.

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

regionpromised_datelinesotif_linesqty_orderedotif_pct
North2026-11-10211550
North2026-11-111080
Unknown2026-11-13116100
West2026-11-13114100

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

regionwrong_avg_of_percentsright_percent_from_counts
North2533.3
Unknown100100
West100100
Use it whenA question that is slow and asked all the time, over a fact that is big.
The trapPercents cannot be added or averaged across days or regions: keep the counts. And a summary nobody reads is pure cost: drop it.

6. Consolidated fact

Consolidated fact: plan next to actual, at one shared grain

The question: Per region and month: what did we plan, what did we deliver, and what is the gap?

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

regionmonthplanned_qty
North2026-11100
West2026-1160
South2026-1140
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

regionmonthplanned_qtyactual_qtygap
North2026-1110023-77
South2026-11400-40
Unknown2026-11066
West2026-11604-56
Use it whenPlan against actual, budget against spend, forecast against outcome: any comparison of two sources.
The trapAn INNER or LEFT join silently drops the region that exists on only one side, which is exactly the row someone must look at. Use FULL OUTER JOIN, and reconcile both totals to their sources.

7. Transaction fact with allocation

Spreading an order-level amount (freight) over its lines

The question: Freight is charged per order, but profit is reported per product. How is it shared out without a cent going missing?

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_idline_idline_amount
A1A1-1100.00
A1A1-2100.00
A1A1-3100.00
B1B1-11000.00
B1B1-2400.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_idfreight
A1100.00
B190.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_idfreightallocated_naive
A1100.0099.99
B190.0090

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_idline_idline_amountfreight_share
A1A1-1100.0033.33
A1A1-2100.0033.34
A1A1-3100.0033.33
B1B1-11000.0064.29
B1B1-2400.0025.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)

Use it whenFreight, discounts, taxes and fees recorded per order or invoice but analysed per line, product or customer.
The trapChoose the share rule (by value, by quantity, equally) once, with the business, and write it down. Changing the rule later restates history.

8. Fact with adjustment rows

Adjustment rows: corrections as new rows, never edits

The question: What is line L1 worth now, and what did we report for it on 15 November?

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_idline_idrecorded_onamountreason
E1L12026-11-101000original
E2L12026-11-20-200price corrected
E3L12026-11-2250late surcharge
E4L22026-11-10400original
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_idline_idrecorded_onamountreason
E1L12026-11-101000original
E2L12026-11-20-200price corrected
E3L12026-11-2250late surcharge
E4L22026-11-10400original

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_idamount_nowamount_reported_on_15_nov
L18501000
L2400400
Use it whenFinance-grade numbers where 'what did we say then?' is a real question and silently changed history is not acceptable.
The trapCounting entries is not counting sales: a correction row is not a new sale. Count lines from the line grain, and add up amounts from the ledger.