Home › Data engineering › Gold data modelling › Silver to gold › Dimension examples in SQL

Silver to gold · Examples in SQL · 19 min read

Dimension table examples in SQL: eleven kinds, each run and checked

Eleven dimension tables written as plain SQL on tiny tables: overwrite, history, previous value, date, junk, bridge and more. 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 eleven examples at a glance

Start with Type 1 and Type 2: they cover most dimensions. The others solve one specific problem each; reach for one only when you have that problem.

ExampleThe question it answersVerdict
Type 1: overwriteWhat is this product called today? (Nobody needs to know what it was called last year.)Pursue: The default for names, codes, descriptions
Type 2: keep historyWhich region was this dealer in on the day the order was promised?Pursue: Whenever the past matters for grouping
Type 3: previous valueShow each dealer's region now, and the region they were in before, side by side.Pursue with care: Only for 'moved from X to Y' or a transition period
Mini-dimensionReport orders by the dealer's volume band and risk band, as they were that month, without a new dealer row every month.Pursue with care: Only for big dimensions with fast-changing bands
Date dimensionGroup by fiscal quarter, weekday or weekend without writing date maths in every report.Pursue: Every fact has a date
Role-playing dateWhich weekday was the delivery made on, and how many days after the promised day?Pursue: No second date table, ever
Junk dimensionCount orders by rush, payment mode and gift wrap, without three more columns of text on a huge fact.Pursue with care: Only when several small flags crowd a big fact
Degenerate dimensionShow me everything on order O1.Pursue: Free: keep the number in the fact
Bridge (many-to-many)Revenue by service, when one dealer offers fitting, balancing and alignment, without counting the same money three times.Pursue with care: Only for true many-to-many; always weight
Flattened hierarchyGroup orders by region, state or city, with no recursive queries in the report.Pursue: Flatten once so reports never recurse
Unknown and inferred membersAn order arrives for dealer D9 before D9 is in the dealer list. What does the fact point at?Pursue: Facts that beat their dimension are normal

1. SCD Type 1

Type 1: overwrite the old value

The question: What is this product called today? (Nobody needs to know what it was called last year.)

The dimension keeps one row per product. When the source changes a name, the row is rewritten. The product keeps its key, so every old fact still points at it and now shows the new name.

silver_products

Silver in

product_idproduct_namebrandsize
P1Highway PlusApex205/55R16
P2City GripApex185/65R15
P3Trail KingRoadster265/65R17
Setup SQL to copy and run
CREATE TABLE silver_products (product_id VARCHAR, product_name VARCHAR, brand VARCHAR, size VARCHAR);
INSERT INTO silver_products VALUES
  ('P1','Highway Plus','Apex','205/55R16'),
  ('P2','City Grip','Apex','185/65R15'),
  ('P3','Trail King','Roadster','265/65R17');

silver_products_day2

Silver in

product_idproduct_namebrandsize
P1Highway PlusApex205/55R16
P2City Grip 2Apex185/65R15
P3Trail KingRoadster265/65R17
P4Snow MasterRoadster195/65R15
Setup SQL to copy and run
CREATE TABLE silver_products_day2 (product_id VARCHAR, product_name VARCHAR, brand VARCHAR, size VARCHAR);
INSERT INTO silver_products_day2 VALUES
  ('P1','Highway Plus','Apex','205/55R16'),
  ('P2','City Grip 2','Apex','185/65R15'),   -- renamed
  ('P3','Trail King','Roadster','265/65R17'),
  ('P4','Snow Master','Roadster','195/65R15'); -- new

1. Night 1: build the dimension. The key is a number gold hands out; the source's own id stays as a column.

CREATE TABLE dim_product AS
SELECT row_number() OVER (ORDER BY product_id) AS product_key,
       product_id, product_name, brand, size
FROM silver_products;

Result

product_keyproduct_idproduct_namebrandsize
1P1Highway PlusApex205/55R16
2P2City GripApex185/65R15
3P3Trail KingRoadster265/65R17

2. Night 2, first half: rewrite rows whose attributes changed. The key does not move.

UPDATE dim_product d
SET product_name = s.product_name, brand = s.brand, size = s.size
FROM silver_products_day2 s
WHERE d.product_id = s.product_id
  AND (d.product_name IS DISTINCT FROM s.product_name
    OR d.brand        IS DISTINCT FROM s.brand
    OR d.size         IS DISTINCT FROM s.size);

3. Night 2, second half: add products gold has never seen, with the next free keys.

INSERT INTO dim_product
SELECT (SELECT COALESCE(max(product_key), 0) FROM dim_product)
         + row_number() OVER (ORDER BY s.product_id),
       s.product_id, s.product_name, s.brand, s.size
FROM silver_products_day2 s
WHERE NOT EXISTS (SELECT 1 FROM dim_product d WHERE d.product_id = s.product_id);

Result

product_keyproduct_idproduct_namebrandsize
1P1Highway PlusApex205/55R16
2P2City Grip 2Apex185/65R15
3P3Trail KingRoadster265/65R17
4P4Snow MasterRoadster195/65R15
Use it whenNames, descriptions and codes where a correction should show everywhere, and history has no business value.
The trapHistory is gone on purpose. If someone later asks 'what was it called when we sold it?', Type 1 cannot answer. Also never re-number: the key must stay the same on every run, or old facts point at the wrong product.

2. SCD Type 2

Type 2: keep history, one row per version

The question: Which region was this dealer in on the day the order was promised?

When a dealer moves region, the old row is closed (it gets an end date) and a new row opens. Both stay. Each version has its own key, and a fact picks the version that was true on the event date.

silver_dealer_changes

Silver in

dealer_idnameregiontierchanged_on
D1Sharma TyresNorthGold2026-11-01
D1Sharma TyresNorthGold2026-11-08
D1Sharma TyresNorthPlatinum2026-11-14
D7Patel MotorsNorthSilver2026-11-01
D7Patel MotorsWestSilver2026-11-12
Setup SQL to copy and run
CREATE TABLE silver_dealer_changes (dealer_id VARCHAR, name VARCHAR, region VARCHAR, tier VARCHAR, changed_on DATE);
INSERT INTO silver_dealer_changes VALUES
  ('D1','Sharma Tyres','North','Gold',     DATE '2026-11-01'),
  ('D1','Sharma Tyres','North','Gold',     DATE '2026-11-08'),  -- re-sent, nothing changed
  ('D1','Sharma Tyres','North','Platinum', DATE '2026-11-14'),
  ('D7','Patel Motors','North','Silver',   DATE '2026-11-01'),
  ('D7','Patel Motors','West', 'Silver',   DATE '2026-11-12');

silver_order_lines

Silver in

line_iddealer_idpromised_date
L1D12026-11-10
L3D72026-11-11
L4D72026-11-13
L6D12026-11-15
Setup SQL to copy and run
CREATE TABLE silver_order_lines (line_id VARCHAR, dealer_id VARCHAR, promised_date DATE);
INSERT INTO silver_order_lines VALUES
  ('L1','D1',DATE '2026-11-10'), ('L3','D7',DATE '2026-11-11'),
  ('L4','D7',DATE '2026-11-13'), ('L6','D1',DATE '2026-11-15');

1. Drop rows where nothing changed (the re-sent 8 Nov row), by comparing each row with the one before it.

CREATE TABLE dealer_changes_clean AS
SELECT *
FROM (SELECT *,
             concat_ws('|', name, region, tier) AS sig,
             lag(concat_ws('|', name, region, tier))
               OVER (PARTITION BY dealer_id ORDER BY changed_on) AS prev_sig
      FROM silver_dealer_changes)
WHERE prev_sig IS NULL OR sig <> prev_sig;

Result

dealer_idnameregiontierchanged_on
D1Sharma TyresNorthGold2026-11-01
D1Sharma TyresNorthPlatinum2026-11-14
D7Patel MotorsNorthSilver2026-11-01
D7Patel MotorsWestSilver2026-11-12

2. Each version ends where the next one starts. The last version stays open (a far-future end date, so range joins need no NULL handling).

CREATE TABLE dim_dealer AS
SELECT row_number() OVER (ORDER BY dealer_id, changed_on) AS dealer_key,
       dealer_id, name, region, tier,
       changed_on AS valid_from,
       COALESCE(lead(changed_on) OVER (PARTITION BY dealer_id ORDER BY changed_on),
                DATE '9999-12-31')                          AS valid_to,
       lead(changed_on) OVER (PARTITION BY dealer_id ORDER BY changed_on) IS NULL AS is_current
FROM dealer_changes_clean;

Result

dealer_keydealer_idnameregiontiervalid_fromvalid_tois_current
1D1Sharma TyresNorthGold2026-11-012026-11-14false
2D1Sharma TyresNorthPlatinum2026-11-149999-12-31true
3D7Patel MotorsNorthSilver2026-11-012026-11-12false
4D7Patel MotorsWestSilver2026-11-129999-12-31true

3. A fact looks up the version true on its event date (valid_from inclusive, valid_to exclusive).

SELECT o.line_id, o.dealer_id, o.promised_date,
       d.dealer_key, d.region, d.tier
FROM silver_order_lines o
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
ORDER BY o.line_id;

Result

line_iddealer_idpromised_datedealer_keyregiontier
L1D12026-11-101NorthGold
L3D72026-11-113NorthSilver
L4D72026-11-134WestSilver
L6D12026-11-152NorthPlatinum
Use it whenAttributes people group by and where the past matters: a dealer's region, a customer's segment, an employee's department.
The trapLooking up the dealer's current row instead of the row true on the date silently moves old orders to the new region. Also check that versions of one dealer never overlap: an overlap makes a fact match two rows and doubles its numbers.

3. SCD Type 3

Type 3: current value plus the one before

The question: Show each dealer's region now, and the region they were in before, side by side.

One row per dealer, with two columns: region now and region before. Only one step back is kept. Useful for a 'moved from X to Y' report, and for a transition period when a reorganisation happens.

silver_dealer_regions

Silver in

dealer_idnameregionfrom_date
D1Sharma TyresNorth2026-11-01
D7Patel MotorsNorth2026-11-01
D7Patel MotorsWest2026-11-12
Setup SQL to copy and run
CREATE TABLE silver_dealer_regions (dealer_id VARCHAR, name VARCHAR, region VARCHAR, from_date DATE);
INSERT INTO silver_dealer_regions VALUES
  ('D1','Sharma Tyres','North',DATE '2026-11-01'),
  ('D7','Patel Motors','North',DATE '2026-11-01'),
  ('D7','Patel Motors','West', DATE '2026-11-12');

1. Take each dealer's newest row, and bring in the region from the row before it.

CREATE TABLE dim_dealer_t3 AS
SELECT row_number() OVER (ORDER BY dealer_id) AS dealer_key,
       dealer_id, name,
       region          AS current_region,
       prev_region     AS previous_region,
       from_date       AS current_region_since
FROM (SELECT *,
             lag(region) OVER (PARTITION BY dealer_id ORDER BY from_date) AS prev_region,
             row_number() OVER (PARTITION BY dealer_id ORDER BY from_date DESC) AS rn
      FROM silver_dealer_regions)
WHERE rn = 1;

Result

dealer_keydealer_idnamecurrent_regionprevious_regioncurrent_region_since
1D1Sharma TyresNorth2026-11-01
2D7Patel MotorsWestNorth2026-11-12
Use it whenA reorganisation where you want old and new views at once, or a 'moved from' column on a report.
The trapTwo moves in a row lose the first region. If that matters, you wanted Type 2.

4. Mini-dimension

Mini-dimension: fast-changing attributes in their own small table

The question: Report orders by the dealer's volume band and risk band, as they were that month, without a new dealer row every month.

Volume band and risk band change often. Making a Type 2 version of the dealer each time would explode the dealer table. Instead the few possible band combinations go in a tiny table of their own, and the fact carries both keys.

silver_dealer_monthly_profile

Silver in

dealer_idmonthvolume_bandrisk_bandqty
D12026-10MediumLow40
D12026-11MediumLow55
D72026-10HighLow120
D72026-11HighMedium130
D92026-10LowMedium12
D92026-11MediumMedium30
Setup SQL to copy and run
CREATE TABLE silver_dealer_monthly_profile (dealer_id VARCHAR, month VARCHAR, volume_band VARCHAR, risk_band VARCHAR, qty INTEGER);
INSERT INTO silver_dealer_monthly_profile VALUES
  ('D1','2026-10','Medium','Low',    40), ('D1','2026-11','Medium','Low',    55),
  ('D7','2026-10','High',  'Low',   120), ('D7','2026-11','High',  'Medium',130),
  ('D9','2026-10','Low',   'Medium', 12), ('D9','2026-11','Medium','Medium', 30);

1. The mini-dimension holds one row per band combination that exists, not one per dealer.

CREATE TABLE dim_dealer_profile AS
SELECT row_number() OVER (ORDER BY volume_band, risk_band) AS profile_key,
       volume_band, risk_band
FROM (SELECT DISTINCT volume_band, risk_band FROM silver_dealer_monthly_profile);

Result

profile_keyvolume_bandrisk_band
1HighLow
2HighMedium
3LowMedium
4MediumLow
5MediumMedium

2. The monthly fact carries the profile key that was true that month.

CREATE TABLE fact_dealer_month AS
SELECT p.dealer_id, p.month, m.profile_key, p.qty
FROM silver_dealer_monthly_profile p
JOIN dim_dealer_profile m USING (volume_band, risk_band);

Result

dealer_idmonthprofile_keyqty
D12026-10440
D12026-11455
D72026-101120
D72026-112130
D92026-10312
D92026-11530

3. Ask the question: tyres by risk band, per month. D7's Medium risk in November shows up in November only.

SELECT f.month, m.risk_band, sum(f.qty) AS qty
FROM fact_dealer_month f JOIN dim_dealer_profile m USING (profile_key)
GROUP BY f.month, m.risk_band ORDER BY f.month, m.risk_band;

Result

monthrisk_bandqty
2026-10Low160
2026-10Medium12
2026-11Low55
2026-11Medium160
Use it whenA big dimension (millions of customers) with a few attributes that change monthly: a credit band, an activity band, a loyalty level.
The trapBands must be small, banded values (Low, Medium, High), never raw numbers like a credit score: raw numbers make the mini-dimension as big as the fact.

5. Type 0 (fixed), generated

Date dimension: generated, never changes

The question: Group by fiscal quarter, weekday or weekend without writing date maths in every report.

No source table at all. You generate one row per day for as many years as you need. Rows never change (Type 0), and every fact points at a date by a whole-number key like 20261113.

1. One row per day for an Indian financial year (April to March). In Spark or Databricks, replace the generator with explode(sequence(DATE'2026-04-01', DATE'2027-03-31', INTERVAL 1 DAY)).

CREATE TABLE dim_date AS
SELECT CAST(strftime(d, '%Y%m%d') AS INTEGER)                    AS date_key,
       d                                                          AS date,
       dayname(d)                                                 AS day_name,
       isodow(d) >= 6                                             AS is_weekend,
       strftime(d, '%Y-%m')                                       AS year_month,
       'FY' || (year(d) + CASE WHEN month(d) >= 4 THEN 0 ELSE -1 END)
            || '-' || right(CAST(year(d) + CASE WHEN month(d) >= 4 THEN 1 ELSE 0 END AS VARCHAR), 2) AS fiscal_year,
       'Q' || CAST(((month(d) + 8) % 12) // 3 + 1 AS INTEGER)       AS fiscal_quarter
FROM (SELECT CAST(unnest(generate_series(DATE '2026-04-01', DATE '2027-03-31', INTERVAL 1 DAY)) AS DATE) AS d);

Result

date_keydateday_nameis_weekendyear_monthfiscal_yearfiscal_quarter
202611132026-11-13Fridayfalse2026-11FY2026-27Q3
202611142026-11-14Saturdaytrue2026-11FY2026-27Q3
202611152026-11-15Sundaytrue2026-11FY2026-27Q3
202703312027-03-31Wednesdayfalse2027-03FY2026-27Q4

2. Add the unknown date, so a fact whose date is missing still joins.

INSERT INTO dim_date VALUES (-1, NULL, 'Unknown', FALSE, 'Unknown', 'Unknown', 'Unknown');

Result

date_keydateday_nameis_weekendyear_monthfiscal_yearfiscal_quarter
-1UnknownfalseUnknownUnknownUnknown
202611132026-11-13Fridayfalse2026-11FY2026-27Q3
Use it whenAlways. Every fact has at least one date.
The trapFiscal year and quarter are the part people get wrong: check the first and last day of the year against finance's own calendar before anyone reports on it.

6. Role-playing

Role-playing: one date table, used as promised date and delivered date

The question: Which weekday was the delivery made on, and how many days after the promised day?

A fact often has several dates. You do not build a second date table. The same date dimension plays two roles, and gets a different name each time you join it. (This example uses the dim_date built in the previous one.)

fact_order_line_dates

Silver in

line_idpromised_date_keydelivered_date_key
L12026111020261110
L22026111020261111
L32026111120261112
L720261113
Setup SQL to copy and run
CREATE TABLE fact_order_line_dates (line_id VARCHAR, promised_date_key INTEGER, delivered_date_key INTEGER);
INSERT INTO fact_order_line_dates VALUES
  ('L1', 20261110, 20261110), ('L2', 20261110, 20261111), ('L3', 20261111, 20261112), ('L7', 20261113, NULL);

1. Give each role its own view, so report tools show 'Promised date' and 'Delivered date' as separate pickers.

CREATE VIEW dim_promised_date  AS SELECT date_key AS promised_date_key,  date AS promised_date,  day_name AS promised_day  FROM dim_date;
CREATE VIEW dim_delivered_date AS SELECT date_key AS delivered_date_key, date AS delivered_date, day_name AS delivered_day FROM dim_date;

2. Join both roles to the same fact. A LEFT JOIN on the second keeps L7, which has not been delivered yet.

SELECT f.line_id, p.promised_day, d.delivered_day,
       date_diff('day', p.promised_date, d.delivered_date) AS days_after_promise
FROM fact_order_line_dates f
JOIN dim_promised_date p USING (promised_date_key)
LEFT JOIN dim_delivered_date d USING (delivered_date_key)
ORDER BY f.line_id;

Result

line_idpromised_daydelivered_daydays_after_promise
L1TuesdayTuesday0
L2TuesdayWednesday1
L3WednesdayThursday1
L7Friday
Use it whenAny fact with more than one date, or more than one dealer, warehouse or employee role (ship-from and ship-to).
The trapAn INNER JOIN on the second role quietly drops every row that has no value yet, such as lines not yet delivered. Use LEFT JOIN, or point the key at the unknown row.

7. Junk

Junk dimension: a handful of flags in one small table

The question: Count orders by rush, payment mode and gift wrap, without three more columns of text on a huge fact.

Small flags that do not belong anywhere else are put together in one little table with a row per combination that exists. The fact carries one key instead of three flags.

silver_orders

Silver in

order_idis_rushpayment_modegift_wrapamount
O1YCODN1400
O2NPrepaidN800
O3YCODN300
O4NPrepaidY500
Setup SQL to copy and run
CREATE TABLE silver_orders (order_id VARCHAR, is_rush VARCHAR, payment_mode VARCHAR, gift_wrap VARCHAR, amount INTEGER);
INSERT INTO silver_orders VALUES
  ('O1','Y','COD',    'N', 1400), ('O2','N','Prepaid','N', 800),
  ('O3','Y','COD',    'N',  300), ('O4','N','Prepaid','Y', 500);

1. One row per combination that really occurs. (If the flags are few, you can instead cross join every possible value so the table is complete up front.)

CREATE TABLE dim_order_flags AS
SELECT row_number() OVER (ORDER BY is_rush, payment_mode, gift_wrap) AS order_flags_key,
       is_rush, payment_mode, gift_wrap
FROM (SELECT DISTINCT is_rush, payment_mode, gift_wrap FROM silver_orders);

Result

order_flags_keyis_rushpayment_modegift_wrap
1NPrepaidN
2NPrepaidY
3YCODN

2. The fact keeps order_id (see the next example) and one key for all three flags.

CREATE TABLE fact_order AS
SELECT o.order_id, f.order_flags_key, o.amount
FROM silver_orders o
JOIN dim_order_flags f
  ON f.is_rush = o.is_rush AND f.payment_mode = o.payment_mode AND f.gift_wrap = o.gift_wrap;

Result

order_idorder_flags_keyamount
O131400
O21800
O33300
O42500
Use it whenThree to ten low-cardinality flags and codes sitting on a fact: rush, payment mode, channel, gift wrap.
The trapA new flag value (a new payment mode) needs a new row before the fact load, or the join drops the order. Add the new combination first, then load.

8. Degenerate

Degenerate dimension: the order number stays in the fact

The question: Show me everything on order O1.

An order number is something you filter and group by, so it behaves like a dimension. But it has nothing to describe (no name, no region), so it gets no table. The number simply sits in the fact.

fact_order_line

Silver in

order_idline_noproduct_idqty
O11P110
O12P25
O21P18
O22P34
Setup SQL to copy and run
CREATE TABLE fact_order_line (order_id VARCHAR, line_no INTEGER, product_id VARCHAR, qty INTEGER);
INSERT INTO fact_order_line VALUES
  ('O1',1,'P1',10), ('O1',2,'P2',5), ('O2',1,'P1',8), ('O2',2,'P3',4);

1. No join, no table. Group and filter on the order number directly.

SELECT order_id, count(*) AS lines, sum(qty) AS tyres
FROM fact_order_line
GROUP BY order_id ORDER BY order_id;

Result

order_idlinestyres
O1215
O2212
Use it whenOrder numbers, invoice numbers, ticket numbers: identifiers with no attributes of their own.
The trapIf the order later gains attributes (customer note, approver), it stops being degenerate: give it a real dimension then.

9. Bridge table

Bridge table: a dealer offers several services

The question: Revenue by service, when one dealer offers fitting, balancing and alignment, without counting the same money three times.

One dealer, many services: a join would repeat the dealer's revenue once per service. A bridge table sits between them and gives each pair a weight. Weights for one dealer add up to 1, so the totals still add up.

silver_dealer_services

Silver in

dealer_idservice
D1Fitting
D1Balancing
D7Fitting
D7Alignment
D7Balancing
Setup SQL to copy and run
CREATE TABLE silver_dealer_services (dealer_id VARCHAR, service VARCHAR);
INSERT INTO silver_dealer_services VALUES
  ('D1','Fitting'), ('D1','Balancing'),
  ('D7','Fitting'), ('D7','Alignment'), ('D7','Balancing');

fact_dealer_revenue

Silver in

dealer_idrevenue
D11000
D71500
Setup SQL to copy and run
CREATE TABLE fact_dealer_revenue (dealer_id VARCHAR, revenue INTEGER);
INSERT INTO fact_dealer_revenue VALUES ('D1',1000), ('D7',1500);

1. The bridge: one row per dealer and service, weight = 1 divided by how many services the dealer offers.

CREATE TABLE bridge_dealer_service AS
SELECT dealer_id, service,
       1.0 / count(*) OVER (PARTITION BY dealer_id) AS weight
FROM silver_dealer_services;

Result

dealer_idserviceweight
D1Balancing0.5
D1Fitting0.5
D7Alignment0.3333
D7Balancing0.3333
D7Fitting0.3333

2. The wrong way and the right way, side by side. Revenue is really 2,500.

SELECT b.service,
       sum(f.revenue)                       AS revenue_naive,
       round(sum(f.revenue * b.weight), 2)  AS revenue_weighted
FROM fact_dealer_revenue f
JOIN bridge_dealer_service b USING (dealer_id)
GROUP BY b.service ORDER BY b.service;

Result

servicerevenue_naiverevenue_weighted
Alignment1500500
Balancing25001000
Fitting25001000
Use it whenGenuine many-to-many: a patient with several diagnoses, a dealer with several services, a product in several categories.
The trapThe naive join is the classic double count. Keep weights at full precision (0.3333 three times is 0.9999). Always test that weighted totals equal the fact's own total, and publish only the weighted view.

10. Hierarchy

Hierarchy: city, state and region in one row

The question: Group orders by region, state or city, with no recursive queries in the report.

The source stores places as a tree: each place points at its parent. For reporting, you flatten the tree so each city row also carries its state and region. Reports then just pick a column.

silver_geography

Silver in

geo_idnamelevelparent_id
R1Northregion
R2Westregion
S1DelhistateR1
S2MaharashtrastateR2
C1New DelhicityS1
C2MumbaicityS2
C3PunecityS2
Setup SQL to copy and run
CREATE TABLE silver_geography (geo_id VARCHAR, name VARCHAR, level VARCHAR, parent_id VARCHAR);
INSERT INTO silver_geography VALUES
  ('R1','North','region',NULL),       ('R2','West','region',NULL),
  ('S1','Delhi','state','R1'),        ('S2','Maharashtra','state','R2'),
  ('C1','New Delhi','city','S1'),     ('C2','Mumbai','city','S2'), ('C3','Pune','city','S2');

1. A fixed three-level tree is just two self-joins. (A tree of unknown depth needs a recursive query; do it once here, never in reports.)

CREATE TABLE dim_geography AS
SELECT row_number() OVER (ORDER BY c.geo_id) AS geography_key,
       c.name AS city, s.name AS state, r.name AS region
FROM silver_geography c
JOIN silver_geography s ON s.geo_id = c.parent_id AND s.level = 'state'
JOIN silver_geography r ON r.geo_id = s.parent_id AND r.level = 'region'
WHERE c.level = 'city';

Result

geography_keycitystateregion
1New DelhiDelhiNorth
2MumbaiMaharashtraWest
3PuneMaharashtraWest
Use it whenProduct to category to department; city to state to country; employee to manager to director.
The trapAn INNER JOIN drops a city whose state is missing in the source. Use LEFT JOINs plus a check that counts cities in silver against cities in gold.

11. Late-arriving member

Late dealer: the unknown row and the inferred row

The question: An order arrives for dealer D9 before D9 is in the dealer list. What does the fact point at?

Two safe answers. The unknown row (key -1) says 'we do not know yet' and the fact is re-keyed when D9 appears. The inferred row creates a placeholder for D9 now, with a real key, and fills it in when the real details arrive. The fact never changes.

dim_dealer_i

Silver in

dealer_keydealer_idnameregionis_inferred
-1?UnknownUnknownfalse
1D1Sharma TyresNorthfalse
2D7Patel MotorsWestfalse
Setup SQL to copy and run
CREATE TABLE dim_dealer_i (dealer_key INTEGER, dealer_id VARCHAR, name VARCHAR, region VARCHAR, is_inferred BOOLEAN);
INSERT INTO dim_dealer_i VALUES
  (-1,'?','Unknown','Unknown',FALSE),
  ( 1,'D1','Sharma Tyres','North',FALSE),
  ( 2,'D7','Patel Motors','West',FALSE);

silver_late_order_lines

Silver in

line_iddealer_idqty
L1D110
L4D74
L5D96
Setup SQL to copy and run
CREATE TABLE silver_late_order_lines (line_id VARCHAR, dealer_id VARCHAR, qty INTEGER);
INSERT INTO silver_late_order_lines VALUES ('L1','D1',10), ('L4','D7',4), ('L5','D9',6);

1. Create a placeholder for every dealer id the facts mention but the dimension does not know.

INSERT INTO dim_dealer_i
SELECT (SELECT max(dealer_key) FROM dim_dealer_i) + row_number() OVER (ORDER BY dealer_id),
       dealer_id, 'Unknown (inferred)', 'Unknown', TRUE
FROM (SELECT DISTINCT dealer_id FROM silver_late_order_lines o
      WHERE NOT EXISTS (SELECT 1 FROM dim_dealer_i d WHERE d.dealer_id = o.dealer_id));

Result

dealer_keydealer_idnameregionis_inferred
-1?UnknownUnknownfalse
1D1Sharma TyresNorthfalse
2D7Patel MotorsWestfalse
3D9Unknown (inferred)Unknowntrue

2. Build the fact. Every line now has a real key, even L5.

CREATE TABLE fact_line AS
SELECT o.line_id, d.dealer_key, o.qty
FROM silver_late_order_lines o JOIN dim_dealer_i d USING (dealer_id);

Result

line_iddealer_keyqty
L1110
L424
L536

3. Days later the real D9 arrives from silver. Fill the placeholder in. Its key, and so the fact, does not change.

UPDATE dim_dealer_i
SET name = 'Gupta Wheels', region = 'West', is_inferred = FALSE
WHERE dealer_id = 'D9';

Result

line_iddealer_keydealer_idnameregionis_inferred
L11D1Sharma TyresNorthfalse
L42D7Patel MotorsWestfalse
L53D9Gupta WheelsWestfalse
Use it whenAny feed where facts can arrive before their dimension: new customers, new dealers, new products. This is normal.
The trapWith a Type 2 dimension, the first real version of D9 should start from the placeholder's first-seen date, not from today, or earlier facts look up no version.