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.
- How to read and run these
- The eleven examples at a glance
- Type 1: overwrite the old value
- Type 2: keep history, one row per version
- Type 3: current value plus the one before
- Mini-dimension: fast-changing attributes in their own small table
- Date dimension: generated, never changes
- Role-playing: one date table, used as promised date and delivered date
- Junk dimension: a handful of flags in one small table
- Degenerate dimension: the order number stays in the fact
- Bridge table: a dealer offers several services
- Hierarchy: city, state and region in one row
- Late dealer: the unknown row and the inferred row
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 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.
| Example | The question it answers | Verdict |
|---|---|---|
| Type 1: overwrite | What 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 history | Which region was this dealer in on the day the order was promised? | Pursue: Whenever the past matters for grouping |
| Type 3: previous value | Show 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-dimension | Report 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 dimension | Group by fiscal quarter, weekday or weekend without writing date maths in every report. | Pursue: Every fact has a date |
| Role-playing date | Which weekday was the delivery made on, and how many days after the promised day? | Pursue: No second date table, ever |
| Junk dimension | Count 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 dimension | Show 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 hierarchy | Group orders by region, state or city, with no recursive queries in the report. | Pursue: Flatten once so reports never recurse |
| Unknown and inferred members | An 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 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_id | product_name | brand | size |
|---|---|---|---|
| P1 | Highway Plus | Apex | 205/55R16 |
| P2 | City Grip | Apex | 185/65R15 |
| P3 | Trail King | Roadster | 265/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_id | product_name | brand | size |
|---|---|---|---|
| P1 | Highway Plus | Apex | 205/55R16 |
| P2 | City Grip 2 | Apex | 185/65R15 |
| P3 | Trail King | Roadster | 265/65R17 |
| P4 | Snow Master | Roadster | 195/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'); -- new1. 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_key | product_id | product_name | brand | size |
|---|---|---|---|---|
| 1 | P1 | Highway Plus | Apex | 205/55R16 |
| 2 | P2 | City Grip | Apex | 185/65R15 |
| 3 | P3 | Trail King | Roadster | 265/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_key | product_id | product_name | brand | size |
|---|---|---|---|---|
| 1 | P1 | Highway Plus | Apex | 205/55R16 |
| 2 | P2 | City Grip 2 | Apex | 185/65R15 |
| 3 | P3 | Trail King | Roadster | 265/65R17 |
| 4 | P4 | Snow Master | Roadster | 195/65R15 |
2. SCD Type 2
Type 2: keep history, one row per version
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_id | name | region | tier | changed_on |
|---|---|---|---|---|
| D1 | Sharma Tyres | North | Gold | 2026-11-01 |
| D1 | Sharma Tyres | North | Gold | 2026-11-08 |
| D1 | Sharma Tyres | North | Platinum | 2026-11-14 |
| D7 | Patel Motors | North | Silver | 2026-11-01 |
| D7 | Patel Motors | West | Silver | 2026-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_id | dealer_id | promised_date |
|---|---|---|
| L1 | D1 | 2026-11-10 |
| L3 | D7 | 2026-11-11 |
| L4 | D7 | 2026-11-13 |
| L6 | D1 | 2026-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_id | name | region | tier | changed_on |
|---|---|---|---|---|
| D1 | Sharma Tyres | North | Gold | 2026-11-01 |
| D1 | Sharma Tyres | North | Platinum | 2026-11-14 |
| D7 | Patel Motors | North | Silver | 2026-11-01 |
| D7 | Patel Motors | West | Silver | 2026-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_key | dealer_id | name | region | tier | valid_from | valid_to | is_current |
|---|---|---|---|---|---|---|---|
| 1 | D1 | Sharma Tyres | North | Gold | 2026-11-01 | 2026-11-14 | false |
| 2 | D1 | Sharma Tyres | North | Platinum | 2026-11-14 | 9999-12-31 | true |
| 3 | D7 | Patel Motors | North | Silver | 2026-11-01 | 2026-11-12 | false |
| 4 | D7 | Patel Motors | West | Silver | 2026-11-12 | 9999-12-31 | true |
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_id | dealer_id | promised_date | dealer_key | region | tier |
|---|---|---|---|---|---|
| L1 | D1 | 2026-11-10 | 1 | North | Gold |
| L3 | D7 | 2026-11-11 | 3 | North | Silver |
| L4 | D7 | 2026-11-13 | 4 | West | Silver |
| L6 | D1 | 2026-11-15 | 2 | North | Platinum |
3. SCD Type 3
Type 3: current value plus the one before
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_id | name | region | from_date |
|---|---|---|---|
| D1 | Sharma Tyres | North | 2026-11-01 |
| D7 | Patel Motors | North | 2026-11-01 |
| D7 | Patel Motors | West | 2026-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_key | dealer_id | name | current_region | previous_region | current_region_since |
|---|---|---|---|---|---|
| 1 | D1 | Sharma Tyres | North | 2026-11-01 | |
| 2 | D7 | Patel Motors | West | North | 2026-11-12 |
4. Mini-dimension
Mini-dimension: fast-changing attributes in their own small table
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_id | month | volume_band | risk_band | qty |
|---|---|---|---|---|
| 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 |
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_key | volume_band | risk_band |
|---|---|---|
| 1 | High | Low |
| 2 | High | Medium |
| 3 | Low | Medium |
| 4 | Medium | Low |
| 5 | Medium | Medium |
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_id | month | profile_key | qty |
|---|---|---|---|
| D1 | 2026-10 | 4 | 40 |
| D1 | 2026-11 | 4 | 55 |
| D7 | 2026-10 | 1 | 120 |
| D7 | 2026-11 | 2 | 130 |
| D9 | 2026-10 | 3 | 12 |
| D9 | 2026-11 | 5 | 30 |
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
| month | risk_band | qty |
|---|---|---|
| 2026-10 | Low | 160 |
| 2026-10 | Medium | 12 |
| 2026-11 | Low | 55 |
| 2026-11 | Medium | 160 |
5. Type 0 (fixed), generated
Date dimension: generated, never changes
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_key | date | day_name | is_weekend | year_month | fiscal_year | fiscal_quarter |
|---|---|---|---|---|---|---|
| 20261113 | 2026-11-13 | Friday | false | 2026-11 | FY2026-27 | Q3 |
| 20261114 | 2026-11-14 | Saturday | true | 2026-11 | FY2026-27 | Q3 |
| 20261115 | 2026-11-15 | Sunday | true | 2026-11 | FY2026-27 | Q3 |
| 20270331 | 2027-03-31 | Wednesday | false | 2027-03 | FY2026-27 | Q4 |
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_key | date | day_name | is_weekend | year_month | fiscal_year | fiscal_quarter |
|---|---|---|---|---|---|---|
| -1 | Unknown | false | Unknown | Unknown | Unknown | |
| 20261113 | 2026-11-13 | Friday | false | 2026-11 | FY2026-27 | Q3 |
6. Role-playing
Role-playing: one date table, used as promised date and delivered date
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_id | promised_date_key | delivered_date_key |
|---|---|---|
| L1 | 20261110 | 20261110 |
| L2 | 20261110 | 20261111 |
| L3 | 20261111 | 20261112 |
| L7 | 20261113 |
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_id | promised_day | delivered_day | days_after_promise |
|---|---|---|---|
| L1 | Tuesday | Tuesday | 0 |
| L2 | Tuesday | Wednesday | 1 |
| L3 | Wednesday | Thursday | 1 |
| L7 | Friday |
7. Junk
Junk dimension: a handful of flags in one small table
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_id | is_rush | payment_mode | gift_wrap | amount |
|---|---|---|---|---|
| O1 | Y | COD | N | 1400 |
| O2 | N | Prepaid | N | 800 |
| O3 | Y | COD | N | 300 |
| O4 | N | Prepaid | Y | 500 |
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_key | is_rush | payment_mode | gift_wrap |
|---|---|---|---|
| 1 | N | Prepaid | N |
| 2 | N | Prepaid | Y |
| 3 | Y | COD | N |
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_id | order_flags_key | amount |
|---|---|---|
| O1 | 3 | 1400 |
| O2 | 1 | 800 |
| O3 | 3 | 300 |
| O4 | 2 | 500 |
8. Degenerate
Degenerate dimension: the order number stays in the fact
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_id | line_no | product_id | qty |
|---|---|---|---|
| O1 | 1 | P1 | 10 |
| O1 | 2 | P2 | 5 |
| O2 | 1 | P1 | 8 |
| O2 | 2 | P3 | 4 |
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_id | lines | tyres |
|---|---|---|
| O1 | 2 | 15 |
| O2 | 2 | 12 |
9. Bridge table
Bridge table: a dealer offers several services
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_id | service |
|---|---|
| D1 | Fitting |
| D1 | Balancing |
| D7 | Fitting |
| D7 | Alignment |
| D7 | Balancing |
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_id | revenue |
|---|---|
| D1 | 1000 |
| D7 | 1500 |
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_id | service | weight |
|---|---|---|
| D1 | Balancing | 0.5 |
| D1 | Fitting | 0.5 |
| D7 | Alignment | 0.3333 |
| D7 | Balancing | 0.3333 |
| D7 | Fitting | 0.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
| service | revenue_naive | revenue_weighted |
|---|---|---|
| Alignment | 1500 | 500 |
| Balancing | 2500 | 1000 |
| Fitting | 2500 | 1000 |
10. Hierarchy
Hierarchy: city, state and region in one row
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_id | name | level | parent_id |
|---|---|---|---|
| R1 | North | region | |
| R2 | West | region | |
| S1 | Delhi | state | R1 |
| S2 | Maharashtra | state | R2 |
| C1 | New Delhi | city | S1 |
| C2 | Mumbai | city | S2 |
| C3 | Pune | city | S2 |
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_key | city | state | region |
|---|---|---|---|
| 1 | New Delhi | Delhi | North |
| 2 | Mumbai | Maharashtra | West |
| 3 | Pune | Maharashtra | West |
11. Late-arriving member
Late dealer: the unknown row and the inferred row
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_key | dealer_id | name | region | is_inferred |
|---|---|---|---|---|
| -1 | ? | Unknown | Unknown | false |
| 1 | D1 | Sharma Tyres | North | false |
| 2 | D7 | Patel Motors | West | false |
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_id | dealer_id | qty |
|---|---|---|
| L1 | D1 | 10 |
| L4 | D7 | 4 |
| L5 | D9 | 6 |
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_key | dealer_id | name | region | is_inferred |
|---|---|---|---|---|
| -1 | ? | Unknown | Unknown | false |
| 1 | D1 | Sharma Tyres | North | false |
| 2 | D7 | Patel Motors | West | false |
| 3 | D9 | Unknown (inferred) | Unknown | true |
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_id | dealer_key | qty |
|---|---|---|
| L1 | 1 | 10 |
| L4 | 2 | 4 |
| L5 | 3 | 6 |
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_id | dealer_key | dealer_id | name | region | is_inferred |
|---|---|---|---|---|---|
| L1 | 1 | D1 | Sharma Tyres | North | false |
| L4 | 2 | D7 | Patel Motors | West | false |
| L5 | 3 | D9 | Gupta Wheels | West | false |