C OMPLETE REFERENCE GUIDE · KIMBALL 2024–25

M ETHODOLOGY EDITION

Dimensional
Modeling
◆

AUTHORED BY Debajyoti Mukhopadhyay

A comprehensive architect's handbook covering 10 modeling disciplines —

from Fact & Dimension design through Slowly Changing Dimensions, Bi-
Temporal patterns, Schema architectures, Hierarchy strategies, and
Enterprise bus design.
Grounded in Kimball methodology with modern cloud & data platform
extensions.

FACT TABLE DESIGN DIMENSION STRATEGIES SCD TYPES 0–7

BI-TEMPORAL MODELING SCHEMA PATTERNS STAR · SNOWFLAKE · GALAXY

DATA VAULT 2.0 HIERARCHY PATTERNS KEY STRATEGIES

MEDALLION ARCHITECTURE GRAIN CONCEPTS KIMBALL BUS ARCHITECTURE

RCD PATTERNS AUDIT & TECHNICAL STANDARDS

10 53 113 400+
CHAPTERS PATTERNS REAL-WORLD REFERENCE
EXAMPLES LINKS

01 Fact Tables 02 Dimension Types 03 SCD 0–7 04 RCD Patterns

05 Bi-Temporal 06 Schema Patterns 07 Hierarchies 08 Grain Concepts

09 Key Strategies 10 Architecture & Patterns

 NAVIGATION

Table of Contents

Fact Tables
01
Transaction · Periodic Snapshot · Accumulating Snapshot · Factless Fact

Dimension Types
02
Conformed · Degenerate · Junk · Role-Playing · Outrigger · Bridge · Inferred Member

SCD Types 0 – 7
03
Retain Original · Overwrite · Add Row · Add Column · History Table · Mini-Dim · Hybrid · Dual-Key

RCD Patterns
04
Mini-Dimension · Push to Snapshot · Junk Dim for Fast Flags

Bi-Temporal Modeling
05
Valid Time · Transaction Time · Four-Quadrant Query Model

Schema Patterns
06
Star · Snowflake · Galaxy · Data Vault 2.0 · One Big Table (OBT)

Hierarchy Patterns
07
Fixed-Depth · Ragged · Recursive / Parent-Child · Closure Table

Grain Concepts
08
Atomic Grain · Aggregate Grain · Grain Conflict — the #1 mistake

Key Strategies
09
Surrogate · Natural · Durable Supernatural · Hash Key (SHA-256)

Architecture & Design Patterns

10
Bus Matrix · Drill-Across · Medallion · Aggregate Awareness · Audit Columns · SCD Decision Guide

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 2 / 126

 Fact
CHAPTER 01

Tables

The central table in a star schema. Stores measurable, quantitative data about a business
process. Every fact table must declare a precise grain — one row represents exactly one
thing.

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 3 / 126

 Transaction Fact Table Most Common
FACT TYPE · 1.1

Description: One row per atomic business event. Each row represents a single discrete
transaction — a sale, a click, a payment, a login. Rows are never updated; new events
produce new rows. This is the most granular and most common fact table type.

🛒
Use Case: Retail SalesA supermarket chain captures every product scan at checkout.
Each line item (product × transaction) is one row. Analysts can then roll up to daily/
weekly sales, compare product performance, or join to customer and store dimensions.

SAMPLE DATA — FACTSALES

SALE_KEY (PK) DATE_KEY (FK) PRODUCT_KEY (FK) STORE_KEY (FK) CUSTOMER_KEY (FK) QUANTITY UN

100001 20240301 P-042 ST-07 C-8821 3 12.

100002 20240301 P-019 ST-07 C-8821 1 45.

100003 20240301 P-105 ST-12 C-4401 2 8.5

SCHEMA DIAGRAM

◈ STAR SCHEMA — TRANSACTION FACT

DimDate

date_key PK
full_date
day_name
month
quarter

FactSales

DimProduct date_key FK DimStore

product_key FK
product_key PK store_key PK
store_key FK
product_name store_name
customer_key FK
category city
quantity
brand region
revenue
discount_amt

DimCustomer

customer_key PK
customer_name
segment
city

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 4 / 126

 ✓ Maximum granularity ✓ Flexible aggregation ✓ Append-only — simple ETL ✗ Very
large row counts at scale

◈ REAL-WORLD PROJECT EXAMPLES

RETAIL BANKING

Walmart / D-Mart POS Sales HDFC / Citi Credit Card Transactions

Every product scan at checkout becomes one Each card swipe writes one row to
fact row: date_key, store_key, product_key, FactCardTransaction: transaction_date_key,
cashier_key, quantity, unit_price, merchant_key, customer_key, card_key,
discount_amount, revenue. A single store transaction_amount, category_code_key.
processes 80,000 transactions/day. Analysts Powers fraud detection, spend analytics,
slice by any dimension at any time without reward points calculation, and regulatory
pre-aggregation. reporting.

Grain: one row per sale line item · ~500M Grain: one row per card transaction · ~2B
rows/year for a large chain rows/year for a large issuer

⇗ Google Cloud — Retail Analytics ⇗ GCP FinServ Analytics

⇗ BigQuery Retail Reference ⇗ Card Txn DW Architecture

⇗ Kimball Transaction Fact ⇗ Kimball Transaction Fact

E-COMMERCE

Flipkart / Amazon Order Line Items

Each ordered product in an order is one fact
row linking order_key (degenerate),
customer_key, product_key, seller_key,
date_key, quantity, list_price, discount,
final_price. Supports category performance,
seller analytics, and customer LTV.

Grain: one row per order line item · ~10M

rows/day during peak season

⇗ AWS Retail Reference Architecture

⇗ BigQuery E-Commerce Patterns

⇗ dbt Mart Structure

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Transaction Fact ⇗ BigQuery — Fact Table Best Practices

⇗ dbt — Building Fact Tables ⇗ Microsoft — Designing Fact Tables

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 5 / 126

 ⬡ BENEFITS ✔ ADOPT WHEN

✦ Captures every atomic event — no information ✔ Business needs to analyze individual events —
lost at ingestion sales, clicks, payments, logins

✦ Can answer any question at any grain through ✔ Analysts require drill-through to the transaction
GROUP BY rollups level from any summary

✦ Supports drill-through from summary ✔ Source system produces discrete event records
dashboards to individual transactions (POS, order systems, web logs)

✦ Append-only pattern enables simple, reliable ✔ Ad-hoc queries are unpredictable — you can't
incremental ETL/ELT pre-define the rollup level

✦ Foundation for all higher-level aggregates and ✔ This is always the recommended starting point —
summary tables build atomic grain first

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 6 / 126

 Periodic Snapshot Fact Table Regular Intervals
FACT TYPE · 1.2

Description: One row per entity per time period, regardless of activity. The row is appended
at each period-end (daily, weekly, monthly) capturing the state at that point in time. Even if
nothing changed, a row is still inserted. Ideal for tracking balances, levels, and statuses over
time.

🏦
Use Case: Bank Account BalancesA bank captures every account's closing balance
at end of day. Even if a customer made no transactions today, a row is inserted with
yesterday's balance. This allows analysts to chart balance trends, compute averages,
and identify dormant accounts.

SAMPLE DATA — FACTDAILYACCOUNTBALANCE

DATE_KEY (FK) ACCOUNT_KEY (FK) OPENING_BALANCE CLOSING_BALANCE TOTAL_CREDITS TOTAL_DEBITS

20240301 ACC-1001 12,500.00 14,200.00 2,500.00 800.00

20240302 ACC-1001 14,200.00 14,200.00 0.00 0.00

20240303 ACC-1001 14,200.00 11,700.00 0.00 2,500.00

20240301 ACC-1002 3,100.00 3,100.00 0.00 0.00

Key insight: Row for 2024-03-02, ACC-1001 has zero transactions but is still present.
This is what distinguishes periodic snapshot from transaction fact — completeness of
the time series.

✓ Complete time series — no gaps ✓ Easy trend analysis ✗ Grows even with no
activity ✗ Not suitable for event-level drill-down

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 7 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

RETAIL / FMCG BANKING

Unilever / HUL Daily Inventory SBI / Axis Bank Account Balance

Snapshot Snapshot
Every night a snapshot is taken of each SKU at End-of-day balance for every account is
each distribution centre: date_key, captured as a periodic snapshot: date_key,
product_key, warehouse_key, account_key, customer_key, closing_balance,
closing_stock_qty, closing_stock_value, available_balance, hold_amount. Enables
days_of_supply. Even on Sundays with zero average daily balance calculations for interest
movement, the row exists. Enables days-on- accrual and regulatory liquidity reporting.
hand and stock-out trend analysis.
Grain: one row per account × day · ~300M
Grain: one row per product × warehouse × rows/day for a major bank
day · ~50M rows/month for a national
distributor
⇗ Kimball Periodic Snapshot

⇗ Kimball Periodic Snapshot ⇗ RBI Data Warehouse Guidelines

⇗ GCP Supply Chain Analytics ⇗ BigQuery Banking Analytics

⇗ dbt Snapshots

HR / WORKFORCE

Infosys / TCS Monthly Headcount

Snapshot
End-of-month headcount by BU, grade, and
location: date_key, bu_key, grade_key,
location_key, headcount, open_positions,
attrition_ytd. Trend line never has gaps —
even months with no org changes have a row.

Grain: one row per BU × grade × location

× month

⇗ Kimball Periodic Snapshot

⇗ Workday HR Analytics Patterns

⇗ dbt HR Mart Design

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Periodic Snapshot ⇗ BigQuery — Scheduled Snapshot Queries

⇗ dbt — Snapshot Materialization ⇗ Microsoft — Snapshot vs Transaction Fact

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 8 / 126

 ⬡ BENEFITS ✔ ADOPT WHEN

✦ Guarantees a complete, gapless time series — ✔ Business metric is a level or balance (inventory,
even periods with zero activity are represented headcount, account balance) not an event

✦ Trend and moving-average calculations are ✔ Reporting requires consistent time-series

trivial with no missing date gaps intervals regardless of activity

✦ Point-in-time balance/level lookups require no ✔ Need to answer "what was the state on date X?"
complex window functions without transaction-level scanning

✦ Decouples reporting from source system query ✔ Source system measures change continuously
load — data is pre-snapshotted and only the period-end value matters

✔ Common cadence: daily inventory, monthly

financials, weekly open tickets

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 9 / 126

 Accumulating Snapshot Fact Table Rows Get Updated
FACT TYPE · 1.3

Description: One row per entity tracking it through a multi-step pipeline lifecycle. Unlike
other fact tables, rows are updated as the entity progresses through stages. Multiple date
FKs represent each milestone. Lag metrics (days between stages) are computed directly in
the row.

📦
Use Case: Order Fulfillment PipelineAn e-commerce platform needs to track each
order from placement through delivery. A single row per order is updated as each
milestone is reached. This lets analysts compute average fulfillment cycle times,
identify bottlenecks, and measure SLA compliance.

LIFECYCLE — SAME ROW UPDATED OVER TIME

STAGE 1 STAGE 2 STAGE 3 STAGE 4 STAGE 5

Order Placed Payment Picked & Packed Shipped Delivered

2024-03-01 Confirmed 2024-03-02 2024-03-03 — pending
2024-03-01

SAMPLE DATA — FACTORDERLIFECYCLE

ORDER_KEY PLACED_DATE_KEY PAYMENT_DATE_KEY PACKED_DATE_KEY SHIPPED_DATE_KEY DELIVERED_DATE_K

ORD-5001 20240301 20240301 20240302 20240303 20240306

ORD-5002 20240301 20240301 20240302 20240303 NULL

Note: ORD-5002 (highlighted) is in-flight — delivered_date_key is NULL. The row will

be updated when delivery occurs. This is the only fact table type where UPDATE
operations are routine.

✓ Cycle time analysis in a single row ✓ Pipeline bottleneck identification ✗ UPDATE

complexity in ETL ✗ Requires careful NULL handling

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 10 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

E-COMMERCE INSURANCE

Myntra / Amazon Order Fulfillment LIC / Max Life Insurance Claim

Pipeline Processing
One row per order tracks the full lifecycle: One row per claim tracks: claim_filed_date,
order_placed_date, payment_confirmed_date, documents_received_date,
warehouse_picked_date, dispatched_date, initial_assessment_date,
out_for_delivery_date, delivered_date, investigation_complete_date,
return_requested_date. Enables SLA monitoring approved_date, payment_date. Identifies
— is the warehouse picking within 4 hours? Is bottleneck stages and measures compliance
delivery within 2 days? with IRDAI settlement TAT norms.

Grain: one row per order (updated at each Grain: one row per insurance claim ·
milestone) · Rows updated ~5–7 times per Claims remain open (active rows) for
order lifecycle weeks to months

⇗ Kimball Accumulating Snapshot ⇗ Kimball Accumulating Snapshot

⇗ AWS Supply Chain Analytics ⇗ IRDAI Digital Guidelines

⇗ Medium — Accumulating Snapshot Pattern ⇗ Insurance DW Architecture (Medium)

HEALTHCARE

Apollo Hospitals Patient Care Pathway

One row per hospital admission tracks:
admission_date, diagnosis_confirmed_date,
surgery_date, ICU_discharge_date,
ward_discharge_date, billing_complete_date,
insurance_settled_date. Measures length-of-
stay, bed turnaround, and billing cycle
efficiency.

Grain: one row per patient admission

episode · Multiple milestone date FKs to
DimDate

⇗ FHIR & Healthcare DW Patterns

⇗ Kimball Accumulating Snapshot

⇗ Azure Health Data Services

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Accumulating Snapshot ⇗ dbt — Modeling Pipeline Events

⇗ Medium — Accumulating Snapshot Pattern ⇗ BigQuery — Slowly Changing Dimensions

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 11 / 126

 ⬡ BENEFITS ✔ ADOPT WHEN

✦ Computes end-to-end cycle time and stage-to- ✔ Business process has a well-defined, ordered set
stage lag directly in a single row of stages (order → ship → deliver)

✦ Identifies pipeline bottlenecks — which stage ✔ Management needs cycle time and lag metrics
consistently adds the most delay across pipeline stages

✦ SLA monitoring is straightforward: compare ✔ Each entity (order, claim, ticket) has exactly one
milestone dates against targets row through its entire lifecycle

✦ Current status of every in-flight entity is always ✔ Pipeline has a definitive start and end — not an
queryable with a simple filter open-ended continuous process

✔ Examples: order fulfilment, insurance claims,

loan applications, HR onboarding

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 12 / 126

 Factless Fact Table No Measures
FACT TYPE · 1.4

Description: A fact table with no numeric measures — only foreign keys to dimension tables.
Used to record that an event occurred or that a relationship exists. The "fact" is the
occurrence of the event itself. COUNT(*) is the primary aggregation.

🎓
Use Case: Student Course AttendanceA university wants to track which students
attended which lectures on which date. There's no numeric measure — the presence of
the row is the fact. Analysts can answer: "How many students attended on Tuesday?" or
"Which lectures had < 50% attendance?"

SAMPLE DATA — FACTATTENDANCE

DATE_KEY (FK) STUDENT_KEY (FK) COURSE_KEY (FK) LOCATION_KEY (FK)

20240304 STU-201 CS-401 HALL-B

20240304 STU-305 CS-401 HALL-B

20240304 STU-201 MGT-201 ROOM-12

Coverage Factless Fact: A second common pattern — record all eligible

combinations (e.g., every product that was on promotion each day) so you can
compare actual sales against eligible products. "Which promoted products had ZERO
sales today?"

✓ Records events and coverage elegantly ✓ Enables "what didn't happen" queries ✗
COUNT(*) only — limited analytics

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 13 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

RETAIL HEALTHCARE / EDUCATION

Big Bazaar Promotion Coverage AIIMS / IIT Student Attendance

Every day, every product on promotion gets a Tracking
row in FactPromotionCoverage: date_key, Marks presence — no measure needed.
product_key, store_key, promotion_key — no FactAttendance: date_key, student_key,
measures. Then LEFT JOIN to FactSales to find course_key, instructor_key, room_key — one
promoted products with ZERO sales. "Which row per student who attended that class.
23% of promoted items had no sales on Day 1 "Which students attended fewer than 75% of
of the sale?" — only answerable with a sessions?" requires counting rows (COUNT(*))
factless coverage table. not summing measures.

Grain: one row per product × store × Grain: one row per student × session
promotion × day · Used exclusively for attended · Absence = no row (or compare
coverage vs. actuals comparison to FactScheduledClasses coverage table)

⇗ Kimball Factless Fact ⇗ Kimball Factless Fact

⇗ Retail Promo Analytics (GCP) ⇗ EdTech Analytics Patterns

⇗ Medium — Factless Facts ⇗ dbt Modeling Event Coverage

HR

Tata Steel Employee Training

Completion
FactTrainingCompletion: date_key,
employee_key, course_key, department_key —
no measures. Paired with
FactMandatoryTrainingSchedule (coverage) to
answer: "Which employees have NOT
completed mandatory safety training this
quarter?"

Grain: one row per training completion

event · Cross-join with coverage table to
find gaps

⇗ Kimball Factless Fact

⇗ Manufacturing HR Analytics

⇗ dbt Coverage Analysis Pattern

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Factless Fact Tables ⇗ Medium — Factless Fact Deep Dive

⇗ dbt — Modeling Event Coverage

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 14 / 126

 ⬡ BENEFITS ✔ ADOPT WHEN

✦ Models real-world events that have no numeric ✔ The business event has no meaningful numeric
measurement naturally and correctly measure — presence alone is the fact

✦ Enables "what didn't happen" queries — a ✔ Need to track attendance, eligibility, enrollment,
capability no other fact type supports directly or coverage relationships

✦ Coverage variant is the only way to compute ✔ Analysis requires identifying absence — products
promotion/eligibility effectiveness with no sales, students who missed class

✦ Very compact — only FK columns, no measures ✔ Modelling a many-to-many relationship that also
consuming storage carries a date or context dimension

✔ Examples: student attendance, promotion

coverage, employee training completion

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 15 / 126

 Fact Additivity — Additive, Semi- Fundamental Kimball Rule
Additive, Non-Additive
FACT CONCEPT · 1.5

Description: Every measure in a fact table must be classified by its additive behaviour —
whether it can be meaningfully summed across all dimensions (additive), only some
dimensions (semi-additive), or no dimensions at all (non-additive). This classification drives
which aggregation functions are valid and what errors to prevent in reports.

THREE ADDITIVITY CLASSES

✦ ADDITIVE ⚠ SEMI-ADDITIVE ✗ NON-ADDITIVE

Can be summed across Can be summed across Cannot be summed

ALL dimensions — date, SOME dimensions but across ANY dimension.
product, store, customer. NOT time. Summing a Ratios, percentages, and
The most common and balance across dates is unit prices must be
safest fact type. meaningless — use AVG recalculated from their
or point-in-time query component facts.
Examples: revenue,
instead.
quantity sold, cost, Examples: unit
discount amount, Examples: account price, profit margin
page views balance, inventory %, customer
level, headcount, satisfaction score,
WIP value temperature

SAMPLE DATA — FACTSALES WITH ALL THREE TYPES

DATE_KEY PRODUCT_KEY STORE_KEY REVENUE ✦ ADDITIVE QUANTITY ✦ ADDITIVE CLOSING_INVENTORY ⚠ SE

20240301 P-042 ST-07 38.97 3 150

20240302 P-042 ST-07 25.98 2 148

SUM (2 days) 64.95 ✓ Correct 5 ✓ Correct 298 ✗ Meaningless!

Classic mistake: Summing closing_inventory (150 + 148 = 298) gives a nonsensical

number — the inventory was 148 at end of day 2, not 298. Semi-additive facts should
use the LAST value, AVERAGE, or a point-in-time query — never SUM across dates.

For non-additive facts: Never store margin_pct directly in the fact table for
aggregation. Store revenue and cost as additive facts, then compute margin_pct =
(revenue - cost) / revenue at query time on aggregated components.

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 16 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

BANKING RETAIL / FMCG

Kotak / ICICI Account Analytics HUL Product P&L Dashboard

revenue (interest income) = ADDITIVE — sum net_revenue = ADDITIVE across all dims ✓.
across all accounts, all dates, all branches ✓. units_sold = ADDITIVE ✓. closing_inventory =
closing_balance = SEMI-ADDITIVE — sum SEMI-ADDITIVE (meaningful per SKU per day,
across accounts valid; sum across dates is not summed across weeks). gross_margin_pct
meaningless (use last-of-month value). = NON-ADDITIVE — store gross_profit and
interest_rate = NON-ADDITIVE — never sum net_revenue separately; compute margin% =
rates; store numerator/denominator gross_profit/net_revenue AFTER aggregating
separately and compute at query time. both additive components.

Classic semi-additive pattern: use Non-additive trap: if margin% is stored

LAST_VALUE() or MAX(date) filter, never in the fact table, analysts SUM it and
SUM(), for balance reporting get 840% instead of recalculating it

⇗ Kimball Additive/Semi-Additive Facts ⇗ Kimball Additive/Semi-Additive Facts

⇗ Semi-Additive Measures (MSFT) ⇗ FMCG Analytics (GCP)

⇗ BigQuery Analytics Banking ⇗ Non-Additive Facts Guide

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Additive, Semi, Non-Additive Facts ⇗ Microsoft — Semi-Additive Measures

⇗ dbt Metrics — Measure Types ⇗ Medium — Fact Additivity Explained

⬡ BENEFITS OF CORRECT ADDITIVITY ✔ ADOPT WHEN

CLASSIFICATION
✔ Always — classify every measure's additivity
✦ Prevents incorrect aggregations that produce before finalising the fact table design
misleading numbers in BI dashboards silently
✔ Store semi-additive facts in a periodic snapshot
✦ Drives correct BI tool metric definitions — fact table, not a transaction fact
semantic layer enforces the right aggregation
✔ Never store non-additive derived metrics — store
function per measure
their component additive facts instead and
✦ Forces derived measures (ratios, percentages) compute at query time
to be computed from their additive components
✔ Document additivity classification in your data
— always correct
dictionary so BI teams configure aggregation
✦ Documents the intent and constraints of each correctly
measure for data consumers and governance

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 17 / 126

 Slowly Changing Facts (SCF) Restated Values
FACT CONCEPT · 1.6

Description: Fact values themselves can change after being recorded — financial
restatements, error corrections, retroactive pricing adjustments, or revised measurements.
Unlike dimensions (which use SCD patterns), fact tables are typically append-only, so changed
fact values require a specific correction strategy to avoid corrupting historical analysis.

📋
Use Case: Financial RestatementA company discovers a revenue recognition error in
Q1. The original Q1 revenue of $450,000 must be restated to $420,000. The correction
must be recorded without destroying the audit trail of the original figure.

THREE APPROACHES TO SLOWLY CHANGING FACTS

APPROACH 1 · Overwrite APPROACH 2 · Reversal + APPROACH 3 · Versioned

New Row ✓ Recommended Fact
Update the fact row in-
place. Simple but loses the Insert a reversal row Add version number +
original value (negative of original) and effective date. Keep all
permanently. Only a new corrected row. versions; latest version =
acceptable if the original Preserves full audit trail. current truth. Complex but
was a pure data error with SUM always gives the full history queryable.
zero audit requirement. current correct value.

APPROACH 2 — REVERSAL + NEW ROW PATTERN

ROW_TYPE PERIOD PRODUCT_KEY REVENUE LOAD_DATE NOTE

ORIGINAL 2024-Q1 P-042 450,000 2024-04-01 Original recording

REVERSAL 2024-Q1 P-042 -450,000 2024-05-15 Restatement reversal

CORRECTION 2024-Q1 P-042 420,000 2024-05-15 Restated value

SUM → current truth 420,000 ✓ Correct net result

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 18 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

FINANCE / LISTED COMPANIES RETAIL

Tata Motors / Reliance Financial D-Mart / Spencer's Price Correction

Restatement A pricing error caused 3,000 POS transactions
Q3 revenue of ₹18,500 Cr was restated to on March 5th to record ₹299 instead of ₹399.
₹17,200 Cr after auditors found a revenue SCF reversal pattern: nightly job inserts
recognition error. Original row retained, reversal rows for all 3,000 transactions, then
reversal row (−₹18,500 Cr) inserted, correction rows with corrected amounts.
correction row (+₹17,200 Cr) inserted — all Period-end report automatically reflects
with load_date on the restatement date. SUM correct revenue without any in-place updates.
always gives ₹17,200 Cr. Auditors can see
original recording. Correction identified via price audit;
is_correction flag on reversal +
Used in SEBI-regulated financial correction rows enables audit queries
reporting where MCA audit trail is
mandatory; original row can never be
⇗ Kimball Corrected Facts
deleted

⇗ dbt Incremental Models

⇗ Kimball Late/Corrected Facts

⇗ BigQuery DML MERGE

⇗ SEBI Financial Reporting Standards

⇗ Audit Reversal Pattern (Medium)

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Late-Arriving & Corrected Facts ⇗ dbt — Handling Restated Data

⇗ Medium — Audit Reversal Pattern

⬡ BENEFITS OF REVERSAL PATTERN ✔ ADOPT WHEN

✦ Full audit trail — the original value and the ✔ Fact values are subject to correction,
correction are both permanently visible restatement, or retroactive adjustment after initial
load
✦ Append-only — the fact table never needs
UPDATE operations ✔ Regulated industries (finance, healthcare) where
an audit trail of the original recording is mandatory
✦ SUM always produces the net correct value
automatically ✔ Source systems issue correction transactions
(credit notes, reversal postings) naturally
✦ Load_date on each row enables "as-of" queries
to reconstruct any prior view ✔ Never overwrite fact rows unless there is zero
audit or regulatory obligation whatsoever

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 19 / 126

 Late-Arriving Facts Out-of-Order Loading
FACT CONCEPT · 1.7

Description: Facts that arrive in the data warehouse after their event date — because of
batch delays, offline transactions, manual reporting, or system outages. A sale made on
March 1st may not be loaded until March 8th. This creates "holes" in periodic snapshots and
causes previously published reports to change when late data lands.

🕐
Use Case: Field Sales Rep Offline OrdersSales reps capture orders offline on tablets
in remote areas. When they reconnect on March 5th, orders from March 1–4 all arrive
together. The March 1–4 periodic snapshots are already published and will need
reprocessing.

IMPACT ON FACT TABLE TYPES

FACT TABLE TYPE IMPACT OF LATE FACTS HANDLING STRATEGY

Transaction Fact Low — just append the late Append with correct date_key;
row with correct event date add load_date for lineage

Periodic Snapshot High — prior period snapshots Reprocess affected periods; use
must be reprocessed a "restated" flag or version
column

Accumulating Medium — update the correct Update the row with the late
Snapshot row's milestone dates milestone; capture actual vs
load timestamp

TRANSACTION FACT — LATE ARRIVAL HANDLING

EVENT_DATE_KEY LOAD_DATE PRODUCT_KEY REVENUE IS_LATE

20240301 2024-03-01 P-042 38.97 N

20240302 2024-03-02 P-019 45.00 N

20240301 2024-03-08 P-105 22.50 Y ← late arrival

load_date vs event_date: Always record both. event_date_key drives dimensional

joins and business analysis. load_date enables pipeline monitoring, SLA tracking, and
identifying which reports need reprocessing after late data arrives.

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 20 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

INSURANCE / HEALTHCARE E-COMMERCE / LOGISTICS

Star Health / Religare Insurance Meesho / Delhivery Last-Mile Delivery

Claims Delivery confirmations from agents in low-
Field agents in rural areas submit claims connectivity areas arrive in batch when agents
manually via paper forms. Claims dated March reconnect to WiFi. A March 2 delivery is
1–5 arrive at the processing centre on March confirmed on March 7. FactDeliveryEvent
8. event_date_key = 20240301; load_date = records event_date_key = 20240302,
2024-03-08; is_late = TRUE. March 1–5 load_date = 2024-03-07. Delivery SLA
periodic snapshots are flagged for reporting uses event_date; pipeline latency
reprocessing. SLA dashboard shows 3-day monitoring uses load_date.
average claim reporting lag.
Dual timestamp is critical: if load_date
is_late flag triggers a reprocessing is used for SLA reporting, all rural
pipeline for affected periodic snapshots deliveries appear "on time" artificially
in Cloud Composer/Airflow

⇗ Late-Arriving Data (Dataflow)

⇗ Kimball Late-Arriving Data

⇗ AWS Logistics Analytics

⇗ Dataflow Out-of-Order Events

⇗ Databricks Streaming Late Events

⇗ IRDAI Claims Processing Norms

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Late-Arriving Data ⇗ GCP Dataflow — Out-of-Order Events

⇗ Databricks — Handling Late Arriving Data ⇗ dbt — Incremental Late-Arriving Rows

⬡ BENEFITS OF EXPLICIT LATE-FACT ✔ ADOPT WHEN

HANDLING
✔ Source systems are known to produce out-of-
✦ Preserves analytical accuracy — late data is order or delayed transactions (offline apps, manual
attributed to the correct business period, not the reporting)
load date
✔ Business stakeholders need to know when a
✦ Dual timestamp (event + load) enables historical period's data is still accumulating vs.
detection of late-arriving data and measurement finalised
of pipeline latency
✔ Pipeline SLAs track data latency — dual
✦ is_late flag lets BI tools distinguish stable from timestamps are required for measurement
unstable report periods during reprocessing
✔ Always add a load_date column to all fact tables
windows
as a baseline hygiene practice

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 21 / 126

 Conformed Facts Cross-Mart Consistency
FACT CONCEPT · 1.8

Description: Facts (measures) that are defined identically — same business logic, same
formula, same grain — across multiple fact tables and data marts. Just as conformed
dimensions enable consistent entity identification across marts, conformed facts enable
consistent metric definitions. "Revenue" means exactly the same thing in FactSales,
FactReturns, and FactBudget.

⚖️
Use Case: Revenue Definition"Revenue" in the Sales mart excludes tax and refunds.
If the Finance mart defines "Revenue" to include tax, a drill-across comparison will
produce an unexplained discrepancy. Conformed facts prevent this by mandating a
single definition in the enterprise data dictionary.

NON-CONFORMED VS CONFORMED REVENUE — THE PROBLEM

MART MEASURE NAME FORMULA MAR 2024 VALUE CONFORMED?

Sales revenue gross_sales − refunds $420,000 ✗ NOT

Mart − tax conformed

Finance revenue gross_sales − refunds $441,000 ✗ NOT

Mart (tax included) conformed

Drill-across discrepancy → trust destroyed $21,000 unexplained gap

Rule: If the same measure name appears in two fact tables with different formulas,
one of them must be renamed. "Revenue (ex-tax)" and "Revenue (inc-tax)" are two
different facts — they must never share the same name.

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 22 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

ENTERPRISE RETAIL HR / FINANCE

Reliance Retail Cross-Mart Revenue Wipro / HCL Headcount Across Marts

FactSales (Sales Mart) and FactBudget HR mart "headcount" = active employees on
(Finance Mart) both have a "revenue" column. last day of month. Finance mart "headcount"
Finance defines revenue including GST; Sales = FTE equivalents including contractors × 0.5.
excludes GST. Result: ₹8,200 Cr vs ₹7,200 Cr Same name, different formula. When Finance
for the same period — ₹1,000 Cr unexplained questions HR numbers, trust erodes.
gap in board dashboards. Conformed fact Resolution: HR_headcount_permanent and
resolution: rename to revenue_ex_gst and FTE_equivalent defined in Collibra with owner,
revenue_inc_gst in the enterprise data formula, and lineage.
dictionary.
Conformed fact definition enforced via
Bus matrix review identified 7 non- dbt metrics YAML — single source of truth
conformed metrics across 4 marts; for formula logic
remediation took 3 sprints

⇗ Kimball Conformed Facts

⇗ Kimball Conformed Facts

⇗ dbt Metrics Overview

⇗ dbt Semantic Layer

⇗ HR Analytics Best Practices

⇗ Collibra Data Governance

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Conformed Facts ⇗ dbt — Semantic Layer & Metrics

⇗ Medium — Conformed Dimensions & Facts

⬡ BENEFITS ✔ ADOPT WHEN

✦ Drill-across queries produce consistent, ✔ The same KPI name appears in more than one
trustworthy numbers across all marts data mart or fact table

✦ Single definition in the data dictionary ✔ Cross-mart comparisons or drill-across reports

eliminates "which revenue?" debates in meetings are required by the business

✦ BI tools and semantic layers can safely expose ✔ A data governance or centre of excellence team
the same metric from multiple fact tables can own and enforce metric definitions

✦ Reduces the most common source of data trust ✔ If definitions differ legitimately — rename them
erosion in enterprise analytics explicitly; never reuse the same name for different
formulas

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 23 / 126

 Dimension
CHAPTER 02

Types

Dimensions provide the context for facts. They answer Who, What, Where, When, Why, and
How. Different dimension archetypes address different structural challenges in your data
model.

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 24 / 126

 Conformed Dimension Enterprise Standard
DIMENSION TYPE · 2.1

Description: A dimension that is shared identically (same columns, same grain, same values)
across multiple fact tables and data marts. Conformed dimensions enable "drilling across" —
joining results from two different fact tables using the shared dimension as a common spine.

🏢
Use Case: Enterprise DimDateA single DimDate table used by FactSales,
FactInventory, FactHR, and FactFinance. Because they all share the same date
dimension, analysts can compare sales revenue vs. headcount on the same date axis
without any join ambiguity.

DIAGRAM — SHARED ACROSS MARTS

◈ CONFORMED DIMDATE ACROSS THREE FACT TABLES

FactSales FactInventory FactHR

date_key FK → date_key FK → date_key FK →

↓ all point to same physical table ↓

DimDate (CONFORMED)

date_key PK
full_date
day_of_week
month_name
quarter
fiscal_year
is_holiday

✓ Cross-mart drill-across queries ✓ Single source of truth ✗ Governance needed to

maintain conformance

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 25 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

ENTERPRISE RETAIL BANKING

Tesco / Reliance Retail DimDate & Axis Bank / Yes Bank DimCustomer
DimProduct DimCustomer is shared across
DimDate is loaded once and used by FactCardTransactions, FactLoanRepayments,
FactSales, FactInventory, FactMarketing, FactSavingsSnapshots, and
FactHR — all 12 fact tables share the exact FactMobileBankingEvents. "Total customer
same date dimension. "Sales revenue by exposure" drill-across query works because all
month" and "Inventory level by month" use four fact tables join to the same customer_key
the same month_name from the same with identical customer segment definitions.
DimDate row — guaranteed consistent
reporting across all marts. Conformed customer_segment definition
prevents "Premium" in one mart vs "High
Without DimDate conformance: Sales shows Value" in another for the same customer
"Jan 2024", Inventory shows "January
2024" — BI tool treats them as different
⇗ Kimball Conformed Dimensions
values in drill-across

⇗ GCP Customer 360

⇗ Kimball Conformed Dimensions

⇗ RBI KYC Framework

⇗ dbt — Date Dimension Build

⇗ Power BI Shared Dims

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Conformed Dimensions ⇗ Microsoft — Conformed Dimensions in Power BI

⇗ dbt — Building Shared Dimensions ⇗ BigQuery — Shared Dimension Patterns

⬡ BENEFITS ✔ ADOPT WHEN

✦ Enables drill-across queries — comparing ✔ Multiple fact tables or data marts need to share
metrics from two different fact tables on the the same dimension (Date, Customer, Product)
same axis
✔ Business requires cross-process analysis — e.g.
✦ Eliminates inconsistent definitions of common compare sales vs. returns on the same product axis
entities like Date, Customer, Product across
✔ Enterprise DW with multiple subject areas
teams
managed by different teams
✦ Reduces redundant ETL work — one shared
✔ A "centre of excellence" or data governance
dimension loaded once, reused everywhere
team can own and maintain the shared dimension
✦ Enforces a common business vocabulary and
consistent hierarchies across the enterprise

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 26 / 126

 Degenerate Dimension No Dim Table
DIMENSION TYPE · 2.2

Description: A dimension attribute that lives directly in the fact table with no corresponding
dimension table. It acts as a grouping/filtering key but has no additional descriptive attributes
to justify a separate table. Common for operational transaction identifiers.

🧾
Use Case: Invoice NumberAn invoice number groups multiple line items (rows) in a
fact table. It's a meaningful identifier you filter and group by, but there are no additional
attributes (invoice date, customer etc.) that aren't already captured by other FK
dimensions. So it stays in the fact table as a degenerate dimension.

SAMPLE DATA — FACTSALESLINE WITH DEGENERATE DIMENSION

DATE_KEY CUSTOMER_KEY INVOICE_NUM (DD) LINE_ITEM REVENUE

20240301 C-8821 INV-9920 1 38.97

20240301 C-8821 INV-9920 2 45.00

20240301 C-4401 INV-9921 1 17.00

INV-9920 is the degenerate dimension. It groups two line items into one invoice.
No separate DimInvoice table exists — there's nothing more to say about the invoice
itself beyond what's already in the fact row.

◈ REAL-WORLD PROJECT EXAMPLES

RETAIL / FMCG BANKING

Big Bazaar / More Supermarkets HDFC NetBanking Transaction

Invoice Number Reference
FactSales has invoice_number as a degenerate FactBankTransaction stores
dimension — one invoice groups 8 line items. txn_reference_number as a degenerate
No DimInvoice table exists because the dimension. UTR number (Unique Transaction
invoice has no additional attributes beyond Reference) links NEFT/RTGS entries but carries
what's already on the fact rows. GROUP BY no additional attributes. Used for transaction-
invoice_number instantly groups line items of level reconciliation and customer dispute
the same receipt. resolution lookup.

invoice_number stored as VARCHAR in fact Degenerate dim avoids creating a 1:1

table; no SK needed; used for receipt- DimTransaction with zero additional
level drill-through only columns — confirmed anti-pattern in
Kimball

⇗ Kimball Degenerate Dimension

⇗ Kimball Degenerate Dimension

⇗ Microsoft — Degenerate Dims

⇗ NPCI UPI Transaction Reference Docs

⇗ Retail DW Design (Medium)

⇗ Banking DW Patterns (Medium)

⇗ REFERENCE DOCUMENTATION & FURTHER READING

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 27 / 126

 ⇗ Kimball — Degenerate Dimension ⇗ Microsoft — Degenerate Dimensions

⇗ Medium — Degenerate Dimension Explained

⬡ BENEFITS ✔ ADOPT WHEN

✦ Eliminates an unnecessary dimension table — ✔ The identifier (invoice#, order#, ticket#) has no
reduces join count and model complexity additional descriptive attributes of its own

✦ Transaction grouping is available directly in the ✔ All attributes of the grouping entity are already
fact table without a join captured by other FK dimensions

✦ Preserves meaningful operational identifiers ✔ The identifier is used only for grouping/filtering,
(invoice, order number) for traceability not for dimensional analysis

✦ No surrogate key overhead — the natural key ✔ Creating a full dimension table would result in a
lives directly in the fact row 1:1 mapping to the fact with no added value

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 28 / 126

 Junk Dimension Flag Consolidation
DIMENSION TYPE · 2.3

Description: Combines multiple low-cardinality, miscellaneous flag and indicator attributes

into a single dimension table. Instead of cluttering the fact table with many boolean/enum
columns, all combinations are pre-computed and stored in a junk dimension. The fact table
carries a single FK.

🏷️
Use Case: Order FlagsAn order fact table would otherwise have: is_rush_order (Y/N),
is_promotional (Y/N), payment_type (Card/Cash/Credit), channel (Web/App/Store).
Instead of 4 columns in the fact, pre-build all 2×2×3×3=36 possible combinations in
DimOrderFlags.

DIMORDERFLAGS (JUNK DIMENSION) FACTORDERS — REFERENCES FLA

FLAG_KEY PK IS_RUSH IS_PROMO PAYMENT_TYPE CHANNEL ORDER_KEY DATE_KEY FLAG_KEY FK R

1 N N Card Web ORD-1 20240301 1 1

2 Y N Card Web ORD-2 20240301 3 5

3 N Y Card App ORD-3 20240302 4 2

4 Y Y Cash Store
Result: 4 columns collapsed into 1 FK. Ju
… … … … …
dim never grows beyond total combinatio
its attributes.

◈ REAL-WORLD PROJECT EXAMPLES

E-COMMERCE BANKING / INSURANCE

Flipkart / Nykaa Order Flags ICICI / Bajaj Allianz Transaction Type

FactOrder has 5 flag columns: Flags
is_express_delivery (Y/N), is_cod (Y/N), FactTransaction has: transaction_channel (4
is_first_order (Y/N), channel_type (3 values), values), is_international (Y/N), reversal_flag (Y/
is_giftwrapped (Y/N). Combined: 2×2×2×3×2 N), dispute_flag (Y/N). 4×2×2×2 = 32
= 48 combinations. DimOrderFlags pre-builds combinations. DimTransactionProfile pre-
all 48 rows. The fact carries one FK. "COD + loaded with 32 rows. Fraud analysts can filter
Express + First Order" segment is a 3ms "International + Reversal + Dispute" with a
lookup. single DimTransactionProfile join.

Junk dim reduces 5 fact columns to 1 FK; Used in regulatory RBI reporting to
flag combination count is bounded and segment transactions by type combinations
static once defined without runtime flag evaluation

⇗ Kimball Junk Dimension ⇗ Kimball Junk Dimension

⇗ E-Commerce DW Design (Medium) ⇗ FinServ DW Patterns (GCP)

⇗ dbt Modeling Flags ⇗ RBI Transaction Reporting

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 29 / 126

 ⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Junk Dimension ⇗ Medium — Junk Dimensions Practical Guide

⇗ dbt — Modeling Low-Cardinality Flags

⬡ BENEFITS ✔ ADOPT WHEN

✦ Collapses many low-value columns in the fact ✔ Fact table has multiple low-cardinality flag/
table into a single clean FK indicator columns (Y/N, 3–4 enum values each)

✦ Pre-built combinations mean the junk dim is ✔ Total distinct combinations across all flags is
effectively static — no ongoing growth manageable (under a few thousand rows)

✦ Consolidates filtering and segmentation logic ✔ No individual flag justifies its own full dimension
into one queryable dimension table

✦ Avoids nullable columns and sparse boolean ✔ Flags frequently appear together in filter
flags scattered across the fact table conditions or segmentation logic

✔ Examples: order flags, transaction type

indicators, channel/payment/promo combos

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 30 / 126

 Role-Playing Dimension Single Table, Multiple Aliases
DIMENSION TYPE · 2.4

Description: A single physical dimension table that is referenced multiple times in the same
fact table, each time under a different alias (role). The most common example is DimDate
being used as OrderDate, ShipDate, and DeliveryDate simultaneously.

📅
Use Case: Multi-Date Order FactAn order has three meaningful dates: when it was
placed, when it was shipped, and when it was delivered. All three reference the exact
same DimDate table, just aliased differently. One physical table, three logical roles.

FACTORDERS — THREE FKS TO ONE DIMDATE

ORDER_KEY ORDER_DATE_KEY → SHIP_DATE_KEY → DELIVER_DATE_KEY → REVENUE

ORD-5001 20240301 20240303 20240306 189.99

ORD-5002 20240301 20240303 NULL 54.50

◈ THREE ALIASES → ONE PHYSICAL DIMDATE

DimDate (as OrderDate)

FactOrders
DimDate (as ShipDate)
order_date_key FK
ship_date_key FK
→
deliver_date_key FK
DimDate (as DeliverDate)

↑ same physical table, 3 SQL aliases

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 31 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

E-COMMERCE / LOGISTICS HR / PAYROLL

Amazon / Delhivery Order Date Roles TCS / Infosys Employee Event

FactOrder has four date FKs: order_date_key, Dimensions
payment_date_key, dispatch_date_key, FactEmployeeEvent has: employee_key
delivery_date_key — all point to the same (current employee), manager_key (their
physical DimDate table via SQL aliases (role- manager), approver_key (HR approver) — all
playing). "Average dispatch-to-delivery days" three point to the same DimEmployee table
is computed as delivery_date.full_date − via aliases. "Average time for manager
dispatch_date.full_date in a single query. approval" uses DimEmployee in three different
roles without any data duplication.
4 date FKs in fact table; DimDate loaded
once; Tableau handles role-playing Single DimEmployee table; 3 role aliases
aliases natively via separate joins defined in the semantic layer (Looker
explores / Power BI relationships)

⇗ Kimball Role-Playing Dimensions

⇗ Kimball Role-Playing Dimensions

⇗ Power BI Multi-Role Relationships

⇗ HR Analytics Architecture (GCP)

⇗ Tableau Date Role Aliases

⇗ dbt Role-Playing Dim Pattern

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Role-Playing Dimension ⇗ Power BI — Role-Playing Relationships

⇗ dbt — Modeling Role-Playing Dimensions ⇗ Medium — Role Playing Dimensions

⬡ BENEFITS ✔ ADOPT WHEN

✦ One physical table maintained instead of three ✔ A single fact table has multiple FKs to the same
copies — no sync risk or duplication dimension type (e.g. multiple date milestones)

✦ Changes to the dimension schema propagate to ✔ The same dimension appears in multiple
all roles automatically semantic roles across the model

✦ Analysts can filter by any date role ✔ Creating separate physical copies would lead to
independently without schema proliferation identical structures maintained in parallel

✦ Reduces storage and ETL maintenance to a ✔ BI tool supports aliasing — most modern tools
single dimension load process (Tableau, Power BI, Looker) handle this natively

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 32 / 126

 Outrigger Dimension Dim-to-Dim Reference
DIMENSION TYPE · 2.5

Description: A secondary dimension referenced by another dimension (not directly by the

fact table). Creates a two-hop join path from fact → primary dim → outrigger dim. Useful when
a dimension has a rich sub-dimension that is shared or reused.

🗺️
Use Case: Store → GeographyDimStore references DimGeography for city, state,
country, region. Multiple stores share the same geography entries. DimGeography is
never directly FKed from the fact — it's only reachable via DimStore. Fact → DimStore →
DimGeography.

◈ OUTRIGGER CHAIN: FACT → DIMSTORE → DIMGEOGRAPHY

DimStore
FactSales store_key PK
store_key FK
→ store_name
→
geo_key FK →

DimGeography (Outrigger)

geo_key PK
city
state
country
region

Warning: Outrigger dimensions introduce snowflaking — they add join complexity. Use
sparingly. If DimGeography is only for DimStore, it may be better to simply denormalize
city/state into DimStore itself.

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 33 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

RETAIL MANUFACTURING

DMart / Spencer's Store → Geography Tata Motors / Mahindra Product →

Outrigger Supplier Outrigger
DimStore contains store_key, store_name, DimProduct stores product_key, part_name,
size_sqft, format_type, and geo_key (FK to spec_code, and primary_supplier_key (FK to
DimGeography). DimGeography is shared DimSupplier). DimSupplier holds supplier
between DimStore and DimWarehouse — both details used by both procurement and quality
need city/state/zone/region. Geography dims. When a supplier changes their address
changes (new zone boundaries) update or certification, one DimSupplier row update
DimGeography once and propagate to both reflects across all products and procurement
Store and Warehouse reports automatically. facts.

DimGeography shared by 2 primary dims; Used only when DimSupplier is shared

join path: FactSales → DimStore → across multiple dims — if only used by
DimGeography (2 hops) DimProduct, denormalise supplier into
DimProduct instead

⇗ Kimball Outrigger Dimension

⇗ Kimball Outrigger Dimension

⇗ Medium — Outrigger vs Snowflake

⇗ Manufacturing DW Design (SAP)

⇗ Retail Geo Analytics (GCP)

⇗ Procurement Analytics (Oracle)

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Outrigger Dimension ⇗ Medium — Outrigger vs Snowflake

⬡ BENEFITS ✔ ADOPT WHEN

✦ Allows a rich sub-dimension to be shared across ✔ Multiple primary dimensions reference the same
multiple primary dimensions secondary dimension (e.g. Store and Warehouse
both reference Geography)
✦ Keeps the primary dimension lean by
externalizing reusable reference data ✔ The sub-dimension has enough attributes to
justify its own table — not just 1–2 columns
✦ Changes to the shared sub-dimension (e.g.
region boundaries) update in one place ✔ Performance is acceptable — the extra join hop is
tolerable for the query workload

✔ Prefer denormalizing into the primary dim if

Geography is only used by one dim

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 34 / 126

 Bridge Table Many-to-Many
DIMENSION TYPE · 2.6

Description: Resolves a many-to-many relationship between a fact table and a dimension. A

customer may have multiple accounts; an employee may have multiple skills; a product may
belong to multiple categories. A bridge table sits between the fact and dimension, with one
row per relationship pair and optionally a weighting factor.

👨‍👩‍👧‍👦
Use Case: Customer → Multiple AccountsA banking customer can own multiple
accounts (savings, checking, mortgage). FactTransactions references customer_key, but
the M:M relationship to accounts requires a bridge table to avoid double-counting.

DIMCUSTOMER BRIDGECUSTACCOUNT DIMACCOUNT

CUST_KEY NAME CUST_KEY ACCOUNT_KEY WEIGHT ACCOUNT_KEY TYPE

C-1 Alice C-1 ACC-10 0.5 ACC-10 Savings

C-2 Bob C-1 ACC-11 0.5 ACC-11 Checking

C-2 ACC-12 1.0 ACC-12 Mortgage

Weighting factor: When a fact (e.g., shared account transaction) must be attributed
to multiple customers, the weight column (0.5 each for Alice's two accounts) prevents
double-counting in aggregations.

◈ REAL-WORLD PROJECT EXAMPLES

BANKING HR

SBI / HDFC Customer Multi-Account Accenture / Deloitte Employee Multi-

Bridge Skill Matrix
A banking customer can hold savings, current, An employee holds multiple skills (Java,
FD, and loan accounts simultaneously. Python, AWS, Agile). FactProjectAssignment →
FactTransaction → BridgeCustomerAccount BridgeEmployeeSkill (emp_key, skill_key,
(cust_key, account_key, weight=1/n) → proficiency_level) → DimSkill. "Which projects
DimAccount. "Total customer asset value" have no employee with AWS skill?" uses the
sums across all accounts with weight=1.0 bridge to find gaps. Proficiency level as weight
each (no splitting). "Attributed transaction enables weighted skill-match scoring.
amount" uses fractional weights for shared
business accounts. Bridge essential for M:M; without it,
denormalising skills into employee dim
Bridge prevents double-counting: without creates unmaintainable multi-valued
it, a customer with 3 accounts appears 3× columns
in GROUP BY customer queries

⇗ Kimball Bridge Table M:M

⇗ Kimball Bridge Table M:M

⇗ Workday Skills Cloud

⇗ Power BI M:M Relationships

⇗ HR Skill Taxonomy (LinkedIn)

⇗ Banking Bridge Pattern (Medium)

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 35 / 126

 ⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Bridge Tables for M:M ⇗ Microsoft — M:M Relationships in Power BI

⇗ Medium — Bridge Tables Deep Dive

⬡ BENEFITS ✔ ADOPT WHEN

✦ Correctly models true many-to-many ✔ A fact row legitimately relates to multiple

relationships without denormalization or row dimension members (customer→accounts,
duplication product→categories)

✦ Weighting factor enables proportional ✔ Direct FK in the fact would cause row duplication
attribution — prevents double-counting in shared and double-counting in aggregations
scenarios
✔ Attribution/weighting across the M:M members is
✦ Clean separation of the relationship from both required for correct metric calculation
the fact and dimension tables
✔ Examples: customer multi-account banking,
✦ Relationship membership can be versioned or employee multi-skill HR, product multi-category
dated independently of either entity catalog

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 36 / 126

 Inferred Member (Late-Arriving Dimension) Late-Arriving Data
DIMENSION TYPE · 2.7

Description: When a fact record arrives before its corresponding dimension record, a
placeholder "inferred member" row is inserted into the dimension with a generated surrogate
key and NULLs for all descriptive attributes. The fact row is linked to this placeholder. When
the actual dimension data arrives, the placeholder is updated (backfilled) with real values.

⏱️
Use Case: New Employee Sales on Day 1A sales transaction arrives for new
employee EMP-9042, but HR hasn't processed the employee record into DimEmployee
yet. Rather than drop the fact or halt the pipeline, an inferred member row is created
and later backfilled.

DIMEMPLOYEE — LIFECYCLE OF AN INFERRED MEMBER

EMP_KEY NATURAL_KEY EMP_NAME DEPARTMENT HIRE_DATE IS_INFERRED

5099 EMP-9042 NULL NULL NULL TRUE ←

placeholder

5099 EMP-9042 Sarah Sales — 2024-03-01 FALSE ←

Chen West backfilled

✓ No fact data lost during pipeline delays ✓ Pipeline keeps running ✗ NULLs in
reports until backfill completes ✗ Requires backfill reconciliation logic

◈ REAL-WORLD PROJECT EXAMPLES

E-COMMERCE HEALTHCARE

Meesho / Snapdeal New Seller First AIIMS / Fortis New Patient Walk-In
Listing A patient walks in for emergency treatment
A new seller lists a product at 11:58 PM. At before their registration is complete in the
midnight, the nightly ETL loads hospital system. FactTreatmentEvent is
FactProductView events — including 200 views recorded with an inferred DimPatient
of the new listing. DimSeller doesn't yet have placeholder (age, name = NULL). Once the
the seller (onboarding ETL runs at 6 AM). An patient is registered and the record syncs to
inferred member SK is assigned; 200 fact rows the DW, the placeholder is backfilled.
are safely linked. At 6 AM, DimSeller backfills Treatment cost is never lost.
the placeholder row with real seller data.
Particularly critical for trauma/
is_inferred = TRUE rows appear as emergency cases where treatment starts
"Pending Seller" in dashboards; KPI before administrative registration
dashboards show total views correctly completes
even before backfill

⇗ Kimball Inferred Member

⇗ Kimball Inferred Member

⇗ FHIR Patient Resource Spec

⇗ Late-Arriving Dims (Medium)

⇗ GCP Healthcare API

⇗ dbt Snapshot Backfill

⇗ REFERENCE DOCUMENTATION & FURTHER READING

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 37 / 126

 ⇗ Kimball — Inferred Dimension Members ⇗ dbt — Handling Late-Arriving Dimensions

⇗ Medium — Late Arriving Dimensions

⬡ BENEFITS ✔ ADOPT WHEN

✦ Zero fact data loss — no transactions are held ✔ Source systems feeding dimensions are known to
back or discarded due to dimension lag lag behind transactional systems

✦ Pipeline remains operational and on-schedule ✔ Pipeline SLA requires zero fact data loss even
even when source systems are out of sync when dimension data is delayed

✦ Backfill is seamless — reports self-correct once ✔ A backfill/reconciliation process can reliably

dimension data arrives update placeholder rows when data arrives

✦ Referential integrity is preserved from day one ✔ The reporting team accepts a short window of
via the placeholder surrogate key NULL attributes in affected rows

✔ Examples: new employee records, new product

launches, newly onboarded customers

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 38 / 126

 Shrunken / Subset Dimension Aggregate Mart Support
DIMENSION TYPE · 2.8

Description: A conformed dimension that contains only a subset of rows or columns from the
full base dimension — scoped to a specific data mart, product line, or aggregation level. A
shrunken dimension must be a perfect subset of the full conformed dimension to maintain
drill-across compatibility. Used alongside aggregate fact tables.

🔬
Use Case: Monthly Product Summary MartA full DimProduct has 50,000 SKUs with
30 attributes. A monthly summary fact table only needs product at the brand level with
5 attributes. DimProductBrand is a shrunken subset — 400 rows, 5 columns —
conformed to the full DimProduct.

DIMPRODUCT (FULL — 50,000 ROWS) DIMPRODUCTBRAND (SHR

ROWS)
PRODUCT_KEY SKU NAME BRAND CATEGORY WEIGHT … 25 MORE
BRAND_KEY BRAND CATEGOR
P-001 WGT- Widget Acme Hardware 1.2kg …
PRO- Pro L B-10 Acme Hardware
L
B-11 TechCo Electron
P-002 WGT- Widget Acme Hardware 0.8kg …
PRO- Pro S
S

Conformance rule: Every brand_key in DimProductBrand must correspond exactly to

the same brand values in the full DimProduct. The shrunken dim is a rollup of the full
dim — never an independent creation.

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 39 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

RETAIL BANKING

Reliance Fresh Monthly Category ICICI Annual Segment Performance

Summary Full DimCustomer has 45M customers × 40
Full DimProduct has 120,000 SKUs × 35 attributes. An annual executive summary fact
attributes. A monthly sales summary fact uses DimCustomerSegment (shrunken): 8
table (FactSalesMonthlyCategorySummary) rows (Mass, Mass Affluent, Affluent, HNI, UHNI,
operates at brand × category grain. NRI, Corporate, SME). The shrunken dim
DimProductBrand is a shrunken subset: 1,200 makes board-level reports instant while the
rows × 6 attributes (brand_key, brand_name, full dim remains available for branch-level
category, division, is_private_label, analytics.
brand_tier). 100× smaller dimension for 80%
of management reports. Shrunken dims are always derived from the
full conformed dim via a GROUP BY /
brand_key values are a strict subset of DISTINCT on the rollup attribute
DimProduct brand values — any discrepancy
breaks drill-across conformance
⇗ Kimball Shrunken Dimension

⇗ Kimball Shrunken Dimension ⇗ Banking Segment Analytics

⇗ dbt Mart Models ⇗ dbt Aggregate Mart Design

⇗ BigQuery Aggregate Tables

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Shrunken Dimensions ⇗ Medium — Shrunken Dimensions for Aggregates

⇗ dbt — Aggregate Mart Models

⬡ BENEFITS ✔ ADOPT WHEN

✦ Enables aggregate fact tables at higher ✔ Building aggregate fact tables that operate at a
granularity without forcing the full base higher grain than the atomic base fact table
dimension
✔ A specific mart or report only needs a subset of
✦ Smaller dimension improves query performance the full dimension's rows or columns
in aggregate marts dramatically
✔ Performance optimisation is needed and the full
✦ Maintains drill-across compatibility because dimension contains far more detail than required
values are a strict subset of the conformed base
✔ Must always be derived from and stay conformed
dim
to the full base dimension — never designed
✦ Reduces the number of attributes BI users are independently
exposed to in summary-level reports

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 40 / 126

 Static / Fixed Dimension Pre-Built Reference Data
DIMENSION TYPE · 2.9

Description: A dimension that is pre-loaded at DW build time with a fixed set of known
values and never (or very rarely) sourced from an operational system. Contains reference
data like status codes, priority levels, day-of-week names, flag descriptions, or ISO codes. The
DW team owns and maintains it directly — not a downstream of any source system extract.

📖
Use Case: DimDayOfWeek7 rows, pre-loaded once, never changes: Monday through
Sunday with attributes like is_weekend, sort_order, abbreviation. No source system
feeds this — the DW team creates it at setup and it never needs ETL refreshes.

DIMDAYOFWEEK — PRE-BUILT, STATIC, 7 ROWS

DAY_KEY DAY_NAME ABBREVIATION SORT_ORDER IS_WEEKEND

1 Monday Mon 1 N

2 Tuesday Tue 2 N

6 Saturday Sat 6 Y

7 Sunday Sun 7 Y

OTHER COMMON STATIC DIMENSIONS

🏷️ 🌐 ⭐
DimOrderStatusNew, DimCurrencyISO 4217 DimPriorityLow /
Processing, Shipped, currency codes, names, Medium / High / Critical
Delivered, Cancelled — and symbols — stable — sort order + colour
fixed set of known reference table codes for dashboards
states

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 41 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

RETAIL / ANY INDUSTRY BANKING / FINTECH

Standard DimDayOfWeek & DimMonth DimTransactionStatus for NPCI / UPI

7-row DimDayOfWeek (Monday–Sunday with A static dimension with 6 rows: Pending,
is_weekend, sort_order) and 12-row DimMonth Authorized, Settled, Failed, Reversed,
(January–December with quarter, fiscal_half) Disputed — each with status_code,
are pre-loaded at DW setup and never change. display_label, is_final_state, and sort_order. No
No ETL refresh ever needed. BI dashboards source system feeds this. The DW team owns
sort weekdays correctly (Mon=1, not it and updates it (rarely) when a new status is
alphabetical) using sort_order. introduced by NPCI regulatory guidelines.

Pre-loaded via a seed file in dbt; stored is_final_state flag enables "open
in a "reference" schema; excluded from transactions" queries without hardcoding
daily ETL monitoring status codes in every SQL filter

⇗ dbt Seeds Documentation ⇗ NPCI UPI Product Overview

⇗ Kimball Static Dimensions ⇗ dbt Seeds for Reference Data

⇗ BigQuery Static Reference Tables ⇗ UPI Transaction Status Codes

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Static Dimensions ⇗ dbt — Seeds for Static Reference Data

⇗ BigQuery — Static Reference Tables

⬡ BENEFITS ✔ ADOPT WHEN

✦ No ETL pipeline needed — zero ongoing ✔ Reference values are small, well-known, and
operational cost once loaded stable (status codes, priority levels, day names)

✦ Adds descriptive labels and sort orders to coded ✔ No source system owns a clean, queryable
values from source systems version of the reference data

✦ Enables clean grouping and filtering without ✔ The DW needs to enrich coded values (e.g. status
relying on source system code tables = "3") with human-readable labels

✦ DW team has full control — no source system ✔ Add sort_order and display_name columns to
dependency or refresh scheduling required enable correct dashboard ordering beyond
alphabetical

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 42 / 126

 Null Handling in Dimensions — The Foundational Design Rule
"Unknown" Member
DESIGN PRINCIPLE · 2.10

Description: NULL foreign keys in fact tables cause rows to be silently excluded from GROUP
BY aggregations — a query joining FactSales to DimCustomer will drop all fact rows where
customer_key IS NULL. The Kimball solution is to never allow NULL FKs in fact tables. Instead,
dimension tables always contain a special "Unknown" or "N/A" row with surrogate key = 0 (or
-1). All unresolvable fact FKs point to this row.

⚠️
The Null DangerAn e-commerce site has 50,000 transactions. 3,000 are from
anonymous (guest) customers with no customer record. If customer_key = NULL for
these rows, all 3,000 transactions are silently dropped from every report that joins
DimCustomer. Revenue is understated by an unknown amount.

❌ WRONG — NULL FK IN FACT ✅ CORRECT — "UNKNOWN"

TABLE MEMBER ROW

SALE_KEY CUSTOMER_KEY REVENUE CUSTOMER_KEY CUSTOMER_NAME SEGMENT

S-001 C-8821 38.97 0 Unknown Unknown

S-002 NULL ← DANGER 45.00 C-8821 Alice Wang Premium

S-003 NULL ← DANGER 22.50

SALE_KEY CUSTOMER_KEY REVENUE
S-002 and S-003 silently excluded from
any JOIN to DimCustomer → revenue S-001 C-8821 38.97

understated by $67.50
S-002 0 → "Unknown" 45.00

S-003 0 → "Unknown" 22.50

All rows included. "Unknown" segment

visible in reports. Zero revenue loss.

Kimball Rule: Dimension tables must always contain a row for "Unknown", "N/A", "Not
Applicable", or "Not Yet Assigned" with a well-known surrogate key (0 or -1). Fact table
FK columns must be declared NOT NULL — the ETL must resolve every unmatched FK
to the Unknown row.

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 43 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

E-COMMERCE BANKING

Ajio / Myntra Guest Checkout Orders Kotak / IndusInd Legacy Account

40% of orders come from unregistered (guest) Migrations
users — customer_key would be NULL without During a core banking system migration,
the Unknown member pattern. DimCustomer 12,000 accounts had their branch codes not
has customer_key = 0, customer_name = yet mapped to DimBranch. Instead of NULL
"Guest Customer", segment = "Unknown". All branch_key on 850,000 fact rows, all
40% of guest orders point to customer_key = unmapped rows point to branch_key = -1
0, are included in all aggregations, and appear ("Branch Mapping Pending"). ETL monitoring
as "Guest Customer" in reports — never tracks the count of -1 assignments as a data
silently dropped. quality KPI that decreases to zero as mapping
completes.
Revenue from guest customers = ₹180 Cr/
month; without Unknown member, this is ETL quality dashboard: chart of "rows
invisible in customer-joined reports pointing to Unknown member" over time —
should trend to zero post-migration

⇗ Kimball Null FK Handling

⇗ Kimball Null FK Handling

⇗ Unknown Member Pattern (Medium)

⇗ Data Migration Patterns (Microsoft)

⇗ dbt NULL Handling Tests

⇗ DQ Monitoring with dbt

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Null Foreign Keys & Default Rows ⇗ dbt — Handling NULLs in Models

⇗ Microsoft — Handling Unknown Members ⇗ Medium — Unknown Member Pattern

⬡ BENEFITS ✔ ADOPT WHEN

✦ Eliminates silent data loss — every fact row is ✔ Always — every dimension table must have an
included in every aggregation Unknown member row; every fact FK must be NOT
NULL
✦ "Unknown" rows surface unresolved data as a
visible, quantifiable segment — not invisible gaps ✔ Source data has optional or late-populated FK
fields (optional customer on a transaction)
✦ NOT NULL FK constraint enforced at DB level
prevents bad data entering the DW ✔ Inferred member pattern is in use — the
placeholder and Unknown row serve different
✦ ETL quality is measurable — monitor how many
purposes (inferred = real entity, pending data;
rows route to the Unknown member over time
Unknown = genuinely unresolvable)

✔ ETL process must explicitly map unresolvable

NULLs to the Unknown key — never pass NULLs
through

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 44 / 126

 Date / Calendar Dimension Every DW Needs This
DIMENSION TYPE · 2.11 — THE UNIVERSAL CONFORMED DIMENSION

Description: The Date Dimension is the most important and universal conformed dimension
in any data warehouse. It has one row per calendar day and is pre-loaded for a range of years
(typically 10 years back, 5 forward). Unlike all other dimensions, it is never sourced from an
operational system — the DW team generates it entirely, adding rich calendar, fiscal, and
business attributes that no source system provides. Every fact table has at least one FK to
DimDate.

📅
Key Principle: Never join fact tables to a SQL date function at query time. Pre-
materialise every calendar attribute — fiscal year, week number, holiday flag, quarter
name — so BI tools and analysts can filter and group without writing complex date
arithmetic in every query.

DIMDATE — STANDARD ATTRIBUTE SET (ONE ROW PER DAY)

DATE_KEY FULL_DATE DAY_OF_WEEK DAY_NAME WEEK_NUM_ISO MONTH_NUM MONTH_NAME QUARTER YE

20240101 2024-01-01 2 Monday 1 1 January Q1 20

20240102 2024-01-02 3 Tuesday 1 1 January Q1 20

20240106 2024-01-06 7 Saturday 1 1 January Q1 20

FULL ATTRIBUTE TAXONOMY

CALENDAR ATTRIBUTES FISCAL ATTRIBUTES BUSINESS / REPORTING

date_key (YYYYMMDD integer) fiscal_year is_weekend (Y/N)

full_date (DATE type) fiscal_quarter is_weekday (Y/N)
day_of_week (1–7) fiscal_month_num is_holiday (Y/N)
day_name, day_abbreviation fiscal_period holiday_name
day_of_month (1–31) fiscal_week_num is_business_day
day_of_year (1–366) fiscal_year_start_date business_day_of_month
week_num_iso (ISO 8601) fiscal_year_end_date relative_day_label
week_num_calendar is_fiscal_year_end (Today / Yesterday / …)
month_num, month_name is_fiscal_quarter_end 4-4-5 period (retail)
month_abbreviation Note: fiscal calendar may not is_last_day_of_month
days_in_month align with calendar year — is_last_day_of_quarter
quarter_num, quarter_name set at DW build time per org season (Spring/Summer/…)
year, year_month (YYYYMM) quarter_offset_from_today
is_leap_year

Date key pattern: Use YYYYMMDD integer (e.g. 20240315) as the surrogate key —
not a DATE type. Integer keys are faster for joins, portable across databases, and
human-readable. Add a proper DATE column (full_date) separately for date arithmetic.
Reserve key value 0 = Unknown and 19000101 = Not Applicable.

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 45 / 126

 Multi-timezone DW: For global systems, store UTC date and local date as separate
FKs in the fact table. A transaction may have different calendar attributes depending
on whether you report in UTC or local time. Two FKs (role-playing dates) resolve this.

◈ REAL-WORLD PROJECT EXAMPLES

RETAIL GLOBAL TECH / GCP

Walmart India / DMart Fiscal Calendar Google / Infosys Multi-Timezone

DimDate DimDate
DMart's fiscal year starts April 1 (India A global analytics platform stores
standard). DimDate has fiscal_year (FY2024 = utc_date_key AND local_date_key as separate
April 2023 – March 2024), fiscal_quarter FKs to DimDate in every fact table. A
(FQ1=Apr–Jun), is_dussehra_sale, transaction at 11:30 PM IST on March 31 is
is_diwali_week, is_republic_day. All 15 fact April 1 UTC. utc_date_key drives SLA and
tables share one DimDate loaded with 15 technical reporting; local_date_key drives
years of data at project inception. Fiscal business reporting. Both FKs are role-playing
quarter reports just GROUP BY fiscal_quarter. aliases of the same DimDate table.

Loaded via a Python/dbt seed script once; Without dual date keys, a quarter-end
fiscal calendar attributes set per org transaction in IST appears in the wrong
policy; never sourced from any fiscal quarter in UTC-based reporting
operational system

⇗ Kimball Calendar Date Dimension

⇗ Kimball Calendar Date Dimension

⇗ BigQuery Timezone Functions

⇗ dbt Date Dimension Build

⇗ Multi-Timezone DW Design (Medium)

⇗ India Fiscal Calendar Design

E-COMMERCE

Flipkart Big Billion Days Holiday Flag

is_big_billion_day, is_sale_period,
is_pre_sale_day attributes added to DimDate
as static business flags. All FactSales rows
during the Big Billion Days period
automatically carry is_big_billion_day=Y via
the date_key join. YoY sale performance
analysis requires no complex date range filters
— just WHERE is_big_billion_day = Y.

Custom business event flags added to

DimDate are the cleanest way to handle
recurring promotional periods

⇗ Kimball Calendar Date Dimension

⇗ dbt Seeds for Holiday Flags

⇗ Retail Event Analytics (GCP)

⇗ REFERENCE DOCUMENTATION & FURTHER READING

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 46 / 126

 ⇗ Kimball — Date Dimension Techniques ⇗ dbt — Date Spine / Date Dimension

⇗ BigQuery — Date Dimension & Date Functions

⇗ Databricks — Date Dimension Best Practices

⬡ BENEFITS ✔ ADOPT WHEN

✦ Eliminates complex SQL date functions in every ✔ Always — every data warehouse must have a
query — all calendar attributes are pre-computed Date Dimension, no exceptions
join columns
✔ Build it first, before any fact table — every fact
✦ Adds fiscal, business, and holiday context that table will need it
no source system provides natively
✔ Include fiscal calendar attributes early —
✦ Single pre-loaded table — zero ETL refresh retrofitting a fiscal calendar later is painful
needed after the initial load (new future dates
✔ Pre-load 10 years back and 5 years forward; add
appended annually)
annual extension as part of year-end DW operations
✦ Conformed across all fact tables — every mart
✔ For global DW: add local_date_key alongside
uses the same date definitions, quarter names,
utc_date_key in fact tables (role-playing dates)
fiscal periods

✦ BI tools drill-down hierarchies (Year → Quarter →

Month → Day) work automatically

Slowly Changing Dimensions (SCD)

CHAPTER 03

— Types 0 through 7

How a data warehouse responds to changes in dimension attribute values. Each type
represents a different tradeoff between historical accuracy, storage, and query complexity.

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 47 / 126

 SCD Type 0 — Retain Original Immutable
SCD · TYPE 0

Description: The attribute value never changes. Once loaded, it is never updated regardless
of what changes in the source system. Used for truly immutable attributes that define the
original state of a record.

🔒
Use Case: Date of Birth, Original Credit Score, Account Open Date, SSN. These are facts
about an entity's origin that should never be overwritten — even if a source system
correction arrives.

DIMCUSTOMER — TYPE 0 COLUMNS HIGHLIGHTED

CUST_KEY DOB (TYPE 0) ORIGINAL_SCORE (TYPE 0) CITY (TYPE 1) SEGMENT (TYPE 2)

C-001 1985-04-12 720 New York Premium

Even if a data correction arrives saying DOB = 1985-04-13, a Type 0 field is never
updated. The original loaded value is the source of truth.

◈ REAL-WORLD PROJECT EXAMPLES

BANKING HEALTHCARE / INSURANCE

SBI / PNB Account Open Date Aadhaar-Linked Patient Date of Birth

customer_since_date in DimCustomer is SCD date_of_birth in DimPatient is Type 0. If a
Type 0. Even if a system migration assigns a clerical error is later "corrected" in the hospital
new record, the original account open date system, the DW does NOT accept the change
from the legacy system is preserved forever. — the Aadhaar-verified original DOB is the
"Customer tenure" calculations are always immutable truth. Insurance premium
accurate because the founding date never calculations and age-based drug dosage
gets overwritten. calculations always use the verified original
value.
SCD Type 0 enforced at ETL level: any
update to customer_since_date from source Immutability contract documented in
is logged as a data quality alert but Collibra/Dataplex data dictionary with
never applied justification: "Aadhaar-verified;
corrections require legal process"

⇗ Kimball SCD Type 0

⇗ Kimball SCD Type 0

⇗ RBI KYC Master Directions

⇗ Aadhaar API Documentation

⇗ Immutable Attributes in DW
⇗ Healthcare Data Immutability

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — SCD Type 0 ⇗ Medium — SCD Types Overview

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 48 / 126

 ⬡ BENEFITS ✔ ADOPT WHEN

✦ Guarantees certain attributes are immutable — ✔ The attribute represents an immutable fact about
protects original onboarding data from accidental the entity's origin (date of birth, SSN, account open
overwrites date)

✦ No ETL logic required for these columns — they ✔ Business rules explicitly state "never update this
are loaded once and never touched again field regardless of source changes"

✦ Establishes a clear contract: these values ✔ Regulatory or audit requirements mandate that
represent the entity's original state forever the original value be preserved permanently

✔ Any change to the source value would be

considered a data quality error, not a real-world
change

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 49 / 126

 SCD Type 1 — Overwrite No History
SCD · TYPE 1

Description: The old value is simply replaced with the new value. No historical record is kept.
Simple but lossy — you can only ever see the current state. Used when history has no
analytical value, or for correcting data quality errors retroactively across all history.

✏️
Use Case: Typo Correction in Customer Name"Jon Smith" was a data entry error;
the correct name is "John Smith". Type 1 overwrites the name in place. No historical
analysis depends on the old misspelled name, so no history is needed.

BEFORE → AFTER CHANGE

BEFORE AFTER (overwritten)

CUST_KEY NAME CITY CUST_KEY NAME CITY

C-001 Jon Smith New York C-001 John Smith New York

✓ Simple — no row explosion ✗ History permanently lost ✗ Fact rows retroactively

reattributed

◈ REAL-WORLD PROJECT EXAMPLES

RETAIL / FMCG E-COMMERCE

Reliance JioMart Customer Phone Myntra / Nykaa Product Name Typo

Number Update Fix
A customer updates their registered mobile A product was loaded as "Moisturising
number from +91-98XXXXXXXX to Cream 50ml" (typo) instead of "Moisturizing
+91-70XXXXXXXX. The old number was never Cream 50ml". Type 1 overwrite corrects it.
used for analysis — it's a contact field. SCD Type Since brand/category/price are unchanged,
1: overwrite in-place. All marketing there is no analytical value in knowing the
communications now use the new number. No old misspelled name. All historical fact rows
"which phone did they have during order X?" now correctly join to "Moisturizing Cream
question ever needs answering. 50ml".

Phone number, email address, and contact SCD Type 1 is correct for data quality
preference fields are almost always Type 1 — corrections — the old value was never
contact info, not analytical attributes the truth, just an error

⇗ Kimball SCD Type 1 ⇗ Kimball SCD Type 1

⇗ dbt Snapshots — Invalidate Hard Deletes ⇗ dbt Data Quality Tests

⇗ BigQuery MERGE for SCD1 ⇗ Delta Lake UPDATE (Databricks)

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — SCD Type 1 ⇗ dbt — Snapshots & SCD Handling

⇗ BigQuery — DML MERGE for SCD1 ⇗ Databricks — Delta Lake MERGE for SCD

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 50 / 126

 ⬡ BENEFITS ✔ ADOPT WHEN

✦ Simplest implementation — no surrogate key ✔ Historical value has zero analytical meaning —
versioning, no date-range logic only the current state matters

✦ No row growth — dimension table size stays ✔ Change is a correction of a data error, not a real-
constant regardless of update frequency world event (typo fix, phone format
standardisation)
✦ Retroactive correction propagates cleanly
across all historical fact rows automatically ✔ Business explicitly accepts that old facts will be
retroactively reassigned to new values
✦ Ideal for data quality fixes where the "old" value
was simply wrong ✔ Attribute changes infrequently and reporting
never compares old vs. new values

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 51 / 126

 SCD Type 2 — Add New Row Full History · Most Common
SCD · TYPE 2

Description: When an attribute changes, the existing row is closed (end-dated) and a new
row is inserted with the new value, a new surrogate key, and new effective dates. The fact
table always links to the surrogate key, enabling exact point-in-time reconstruction. The most
widely used SCD type.

📊
Use Case: Employee PromotionAlice was promoted from "Analyst" to "Senior
Analyst" on 2024-03-01. Past sales facts should still reflect her as "Analyst" during that
period. A new row is added; old row is end-dated. Sales from Feb still join to the Analyst
row; sales from March join to the Senior Analyst row.

DIMEMPLOYEE — SCD TYPE 2

EMP_KEY (SK) NATURAL_KEY EMP_NAME TITLE START_DATE END_DATE IS_CURRENT

E-301 EMP-001 Alice Analyst 2022-01-10 2024-02-29 N

Wang

E-402 EMP-001 Alice Senior 2024-03-01 9999-12-31 Y

Wang Analyst

Point-in-time join: WHERE [Link] BETWEEN dim.start_date AND dim.end_date — or

use is_current = Y for present-day queries.

✓ Full, accurate history ✓ Point-in-time reconstruction ✗ Row count grows with

every change ✗ Requires surrogate keys

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 52 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

BANKING RETAIL

HDFC / Axis Relationship Manager Tata Retail / Shoppers Stop Customer

Transfer Loyalty Tier
A customer's assigned relationship manager A customer upgrades from Silver to Gold tier
changes. The old RM managed the customer on August 15. SCD Type 2 creates a new row:
from 2020-01-01 to 2023-06-14. A new row is Silver tier expires August 14, Gold tier starts
inserted for the new RM from 2023-06-15. Fact August 15. Q2 revenue is correctly attributed
rows for 2021 join to the old RM row; 2024 to Silver tier; Q3 revenue to Gold tier. Tier-
fact rows join to the new RM row. Revenue based offers and analytics are always
attribution per RM is historically accurate. historically accurate.

Without SCD Type 2: all 3 years of Loyalty tier is one of the most important
revenue would be attributed to the SCD Type 2 attributes in retail — drives
current RM — incorrect performance promotion eligibility and retrospective
measurement analysis

⇗ Kimball SCD Type 2 ⇗ Kimball SCD Type 2

⇗ dbt Snapshots (SCD Type 2) ⇗ dbt Snapshots

⇗ BigQuery SCD2 Implementation ⇗ Retail Loyalty DW Design (Medium)

HR / CONSULTING

Infosys / Wipro Employee Department

Transfer
An employee moves from "Delivery — Java
Practice" to "AI/ML CoE" on March 1. SCD Type
2 creates a new DimEmployee row.
FactProjectRevenue rows for Jan–Feb join to
"Delivery — Java Practice"; March onwards
joins to "AI/ML CoE". Headcount and revenue
attribution per practice are always accurate.

SCD Type 2 is mandatory for any attribute

that determines commission, attribution,
or regulatory compliance

⇗ Kimball SCD Type 2

⇗ HR SCD2 Patterns (Medium)

⇗ SAP HR Analytics Reference

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — SCD Type 2 ⇗ dbt — Snapshots (SCD Type 2)

⇗ GCP — BigQuery SCD Type 2 Implementation ⇗ Databricks — Delta Lake SCD Type 2

⇗ Snowflake — Implementing SCD Type 2

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 53 / 126

 ⬡ BENEFITS ✔ ADOPT WHEN

✦ Complete, lossless history — every version of ✔ Historical analysis requires knowing what the
every attribute is permanently preserved attribute value was at the time of the fact — not
just today
✦ Point-in-time reconstruction is exact — join fact
to dim on date range to get the right version ✔ Attribute changes are meaningful business
events (promotions, address moves, price changes)
✦ Enables powerful "as-of" analysis: "what was
Alice's territory when she made that 2022 sale?" ✔ Change frequency is manageable — not millions
of updates per day (use RCD/mini-dim then)
✦ Industry default — all major DW platforms, dbt,
and BI tools are optimised for this pattern ✔ This is the default choice for most slowly
changing dimension attributes
✦ Surrogate key decouples the DW from source
system key changes ✔ Required by regulatory or compliance
frameworks that mandate point-in-time auditability

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 54 / 126

 SCD Type 3 — Add New Column Limited History
SCD · TYPE 3

Description: Instead of adding a new row, a new column is added to store the previous
value. The row remains a single row but now has both current_value and previous_value
columns. Limited to tracking only one previous state — useful when you only ever need to
compare "before vs. after" a known one-time change.

🔄
Use Case: Sales Territory RealignmentA company reorganizes its sales territories
once. Analysts need to compare performance under old vs. new territories. Since this is
a one-time structural change, Type 3 (two columns) is sufficient — no need for full row
versioning.

DIMSALESREP — BEFORE AND AFTER TERRITORY CHANGE

REP_KEY REP_NAME CURRENT_TERRITORY PREVIOUS_TERRITORY CHANGE_DATE

R-10 Tom Reyes North-West Central 2024-01-01

R-11 Priya Nair South-East South 2024-01-01

✓ Simple — no row growth ✓ Easy before/after comparison ✗ Only 1 prior value

tracked ✗ Schema changes needed for more history

◈ REAL-WORLD PROJECT EXAMPLES

RETAIL / FMCG BANKING

ITC / Marico Sales Territory Yes Bank / Bandhan Bank Branch

Realignment Cluster Reorg
A nationwide territory restructuring moves A bank reorganises 800 branches from 12
40% of districts from North to North-Central regions to 8 zones. DimBranch gets
zone. Rather than losing all history (Type 1) or prev_region and curr_zone columns.
creating double the rows (Type 2), "Performance under old regional
DimTerritory adds prev_zone and curr_zone structure" (for the annual review) uses
columns. "Revenue under old territory prev_region; "performance under new zone
structure" uses prev_zone; "revenue under structure" uses curr_zone. A third re-org would
new structure" uses curr_zone. Year-over-year require a second "prev" column — Type 3's
reports are meaningful. limit becomes visible.

Type 3 is ideal for planned, company- Use Type 3 only if this is a one-time
wide, one-time structural changes where event; if territory/region changes happen
only before/after comparison is needed regularly, use Type 2 instead

⇗ Kimball SCD Type 3 ⇗ Kimball SCD Type 3

⇗ SCD Type 3 Use Cases (Medium) ⇗ Banking Branch Analytics

⇗ dbt Snapshot Strategies ⇗ RBI Branch Rationalisation

⇗ REFERENCE DOCUMENTATION & FURTHER READING

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 55 / 126

 ⇗ Kimball — SCD Type 3 ⇗ Medium — SCD Type 3 Use Cases

⇗ dbt — SCD Strategies in Snapshots

⬡ BENEFITS ✔ ADOPT WHEN

✦ No row growth — single row per entity even ✔ The change is a known one-time structural event
after the change (territory realignment, system migration,
rebranding)
✦ Current and previous values are immediately
visible side-by-side in any query ✔ Only one prior value ever needs to be tracked —
not a multi-version history
✦ No date-range joins required — both values
always accessible in the same row ✔ Analysts need to compare before/after the
specific change in a simple side-by-side report
✦ Minimal ETL complexity — just update two
columns when a change is detected ✔ The attribute is unlikely to change more than
once, or multiple changes are analytically irrelevant

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 56 / 126

 SCD Type 4 — History Table Split Storage
SCD · TYPE 4

Description: Current data lives in the main dimension table (always one row per entity,
always current). All historical versions are stored in a separate history table. The fact table
joins to the current dimension for today's view; historical analysis requires joining to the
history table.

📁
Use Case: Product PricingThe main DimProduct always shows the current price.
DimProduct_History has one row per price change event. Operational dashboards join to
DimProduct for speed; historical price analysis joins to the history table.

DIMPRODUCT (CURRENT ONLY) DIMPRODUCT_HISTORY (ALL VERSIONS)

PRODUCT_KEY PRODUCT_NAME CURRENT_PRICE PRODUCT_KEY PRICE EFF_DATE EXP_DATE

P-042 Widget Pro 24.99 P-042 18.99 2023-01-01 2023-12-31

P-042 21.50 2024-01-01 2024-02-28

P-042 24.99 2024-03-01 9999-12-31

✓ Main dim stays lean and fast ✓ Full history separately preserved ✗ Two tables to
maintain ✗ History queries require extra join

◈ REAL-WORLD PROJECT EXAMPLES

RETAIL / PRICING E-COMMERCE / FINTECH

Bigbasket / Grofers Daily Price History Paytm / PhonePe Customer Profile —

Product prices change multiple times per Current vs History
week. SCD Type 2 on price would create A customer's KYC status, risk tier, and
millions of new rows. Type 4: DimProduct transaction limit change frequently (monthly
(main) stays compact with current price only; AML review). Type 4: DimCustomer (main,
DimProductPriceHistory has one row per price ~50M rows) has only current status for fast
change per product. Fast current-price queries operational queries;
hit DimProduct. Historical "price at time of DimCustomerStatusHistory archives each
order" queries hit the history table via date change. Fraud detection uses DimCustomer
range join. for real-time checks; compliance audit uses
the history table.
DimProduct stays at 80,000 rows;
DimProductPriceHistory grows to 2M rows — Separation of current-state (operational)
separate tables prevent main dim bloat from history (compliance) is the primary
motivation for Type 4 in fintech

⇗ Kimball SCD Type 4 (Mini-Dim)

⇗ Kimball SCD Type 4

⇗ Dynamic Pricing Analytics (GCP)

⇗ FinTech KYC & AML Analytics

⇗ SCD Type 4 Pattern (Medium)

⇗ Customer Profile DW (Databricks)

⇗ REFERENCE DOCUMENTATION & FURTHER READING

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 57 / 126

 ⇗ Kimball — SCD Type 4 (Mini-Dimension) ⇗ Medium — SCD Type 4 Pattern

⇗ Snowflake — Current vs Historical Dim Tables

⬡ BENEFITS ✔ ADOPT WHEN

✦ Main dimension stays compact and fast — ✔ Current-state queries dominate and need to be
operational dashboards never pay the cost of fast — historical queries are infrequent
historical rows
✔ An attribute changes frequently enough to bloat
✦ Complete history is still available in the history a Type 2 dimension but history must still be
table for compliance and audit queries retained

✦ Clear physical separation between current state ✔ Two separate access patterns exist: operational
and historical state simplifies BI tool configuration (current) and analytical (historical)

✦ Especially effective when rapidly-changing ✔ Storage or performance constraints make a large

attributes would bloat a Type 2 dimension Type 2 dimension unacceptable for production
queries

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 58 / 126

 SCD Type 5 — Mini-Dimension + Fast Attrs + Current Shortcut
Type 1 Outrigger
SCD · TYPE 5 (= 4 + 1)

Description: Type 5 = Type 4 (mini-dimension for rapidly changing attributes) + Type 1 (a FK

in the base dimension that always points to the current mini-dimension row, overwritten in
place). Fact table holds a historical FK to mini-dim. Base dim holds a current FK to mini-dim.
This eliminates the need to go through the fact table to find a customer's current profile.

⚡
Use Case: Customer Credit ProfileIncome band, credit score tier, and risk rating
change monthly. They're split into a mini-dim. The fact table's mini-dim FK captures
which profile was active at transaction time. DimCustomer.current_profile_key (Type 1)
always reflects today's profile for operational lookups.

◈ TYPE 5 STRUCTURE

DimCustomer (base) FactSales

cust_key PK cust_key FK
name, city profile_key FK (historical)
current_profile_key ← Type1 overwrite

DimCustProfile (mini-dim)

profile_key PK
income_band
credit_tier
risk_rating

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 59 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

BANKING / FINTECH HEALTHCARE / INSURANCE

Kotak / Standard Chartered Customer Apollo / Max Bupa Patient Health

Risk Profile Profile
DimCustomer has stable attributes (name, DimPatient has stable demographics + a
DOB, account type) and a current_health_profile_key to DimHealthProfile
current_risk_profile_key pointing to mini-dim (combinations of BMI_band ×
DimRiskProfile mini-dim (24 combinations of diabetes_status × hypertension_flag = 12
risk_tier × income_band × combinations). Each claim fact carries the
credit_score_range). FactTransaction carries health_profile_key at time of treatment.
both customer_key (stable) AND Actuarial "historical claims by health profile at
risk_profile_key (at-time-of-transaction). time of claim" is exact; "current patient risk
"Revenue from High Risk customers in Q3" distribution" uses
uses risk_profile_key on fact; "current High DimPatient.current_health_profile_key.
Risk portfolio" uses current_risk_profile_key on
DimCustomer. Health profiles change at each annual
checkup; mini-dim prevents 8M patient
Mini-dim avoids re-scoring 45M customer rows from being re-versioned monthly
rows monthly; only the 24-row
DimRiskProfile is static; profile FK on
⇗ Kimball SCD Type 5
fact captures point-in-time

⇗ Healthcare Analytics (GCP)

⇗ Kimball SCD Type 5

⇗ Actuarial DW Design Patterns

⇗ RBI Risk Classification Guidelines

⇗ SCD Types 4–7 (Medium)

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — SCD Type 5 ⇗ Medium — SCD Types 5, 6, 7 Explained

⬡ BENEFITS ✔ ADOPT WHEN

✦ Fast-changing attributes are isolated in a mini- ✔ A subset of dimension attributes changes far
dim, preventing row explosion in the base more frequently than the rest (monthly re-scoring)
dimension
✔ Both historical profile at transaction time AND
✦ Historical profile at transaction time is current profile for present-day reporting are needed
preserved via the fact table FK to the mini-dim
✔ Going through the fact table to find the current
✦ Current profile is always accessible directly from profile is too expensive for operational queries
the base dimension without touching the fact
✔ The fast-changing attributes have limited distinct
✦ Best of both worlds: performance (current view combinations (suitable for a mini-dim structure)
shortcut) + accuracy (historical point-in-time)

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 60 / 126

 SCD Type 6 — Hybrid (1 + 2 + 3) Gold Standard for Analytics
SCD · TYPE 6 (= 1 + 2 + 3)

Description: Combines all three core types: Type 2 (new row per change with surrogate key
+ effective dates), Type 3 (current_value column on every row, overwritten Type 1 style), and
a historical_value column. Every row has the point-in-time value AND the current value. This
lets analysts write queries without date-range joins for current analysis while still supporting
full historical reconstruction.

🏆
Use Case: Sales Rep Territory — Current + Historical in One RowManagement
wants both: "What region was this rep in when this sale was made?" AND "What region
are they in now?" Type 6 puts both answers in the dimension row itself.

DIMSALESREP — SCD TYPE 6

EMP_KEY (SK) NATURAL_KEY NAME CURRENT_TERRITORY (TYPE1) HIST_TERRITORY (TYPE3) START_DT

R-101 REP-5 Tom North-West Central 2022-01-01

Reyes

R-202 REP-5 Tom North-West North-West 2024-01-01

Reyes

Power of Type 6: Join on surrogate key for point-in-time accuracy. Join on

is_current=Y for present view — no date math required. current_territory is always up-
to-date on EVERY historical row, enabling "what's the current territory of the rep who
made this 2022 sale?" without a second lookup.

✓ Historical AND current in same row ✓ Most flexible for BI tools ✗ ETL must update
current_* on all prior rows ✗ More complex to implement

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 61 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

RETAIL HR / CONSULTING

Amazon / Reliance Retail Customer Deloitte / EY Employee Grade for

Segment Analysis Commission Calculation
DimCustomer has: historical FactProjectRevenue must attribute revenue to
segment_at_time_of_transaction (Type 2 the grade the employee held when they
versioned) AND current_segment (Type 1 worked on the project. Type 6 DimEmployee:
overwritten on all rows). FactSales joins to grade_at_time_of_project (historical) +
customer_key (pointing to the historically current_grade. Partners who were Directors in
correct row). BI report can toggle: "Revenue 2021 are correctly paid Director-rate
attributed to customer's segment when they commissions for 2021 work, while also
bought" vs "Revenue by current customer appearing as Partners in current headcount
segment" — both from the same fact table reports.
join, same dimension row.
Without Type 6: either commission is
Type 6 is the gold standard: one fact-to- wrong (Type 1 gives current grade to
dim join gives both historical accuracy historical work) or current headcount is
AND current context with zero extra SQL wrong (pure Type 2 requires extra filter)

⇗ Kimball SCD Type 6 Hybrid ⇗ Kimball SCD Type 6 Hybrid

⇗ SCD Type 6 Gold Standard (Medium) ⇗ Professional Services Analytics

⇗ dbt Snapshot Hybrid Strategy ⇗ Commission Attribution DW Design

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — SCD Type 6 (Hybrid) ⇗ dbt — Advanced Snapshot Strategies

⇗ Databricks — SCD Type 6 with Delta ⇗ Medium — SCD Type 6 Gold Standard

⬡ BENEFITS ✔ ADOPT WHEN

✦ Every historical row carries both its point-in- ✔ Analysts routinely ask both "what was X at the
time value AND the entity's current value — no time of the event?" AND "what is X today?" on the
second lookup needed same report

✦ BI tools can offer analysts a one-click toggle ✔ ETL team can handle the added complexity of
between "as-of" and "current" views updating current_* columns on all prior rows

✦ Eliminates complex date-range join logic for ✔ The dimension does not change at extremely
current-state reporting on historical fact sets high frequency (which would make bulk updates
expensive)
✦ The gold standard for analytics-heavy DW
where both historical accuracy and current ✔ Enterprise BI environment with power users who
context are critical need sophisticated temporal analysis

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 62 / 126

 SCD Type 7 — Dual-Key Hybrid Dual FK in Fact
SCD · TYPE 7

Description: The fact table carries TWO foreign keys for the same dimension: (1) a surrogate
key pointing to the historical Type 2 row, and (2) a durable natural key pointing to the current
row. The Type 2 dimension also has a current-view dim (Type 1 style). This achieves the same
result as Type 6 but through key relationships rather than physically overwriting current
columns.

🔑
Use Case: Dual-View Customer AnalysisAnalysts can toggle between "analyze by
customer's region at time of sale" (join via surrogate key) vs. "analyze by customer's
current region" (join via durable key) — both available from the same fact table row.

FACTSALES — DUAL FK

SALE_KEY CUST_KEY FK (HISTORICAL SK) CUST_NATURAL_KEY FK (CURRENT) REVENUE

S-001 C-301 (→ "Central" in 2022) EMP-5 (→ current row "North- 12,000

West")

✓ Equivalent power to Type 6 ✓ No physical overwriting required ✗ Two FKs per

dimension in fact table ✗ More complex query patterns

◈ REAL-WORLD PROJECT EXAMPLES

LARGE RETAIL DW BANKING / FINTECH

Walmart / Big Bazaar Avoiding Mass HDFC / ICICI Large Customer

Updates Dimension
A 500M-row DimProduct table with Type 6 A 80M-row DimCustomer. Type 6 would require
would require updating current_category on all updating current_segment on 80M rows when
500M rows whenever a product changes any customer changes segment. Type 7: fact
category. Type 7 avoids this: each fact row table has customer_hist_key (historical row
carries both the historical surrogate key FK) and customer_durable_key. Current-
(product_key_hist) AND the durable key segment query: JOIN DimCustomer on
(product_key_dur). The current-view durable_key WHERE is_current=1. Historical
dimension filters to is_current=1. No bulk query: JOIN on hist_key directly. Zero bulk
updates needed — just insert new row and updates.
update is_current on the old one.
Type 7 implemented as two views on the
Type 7 is preferred over Type 6 when the same DimCustomer table:
dimension is very large (>100M rows) v_dim_customer_current (is_current=1) and
making bulk current_* updates too the full table for historical joins
expensive

⇗ Kimball SCD Type 7

⇗ Kimball SCD Type 7

⇗ GCP Handling 100M+ Row Dimensions

⇗ SCD Type 7 Dual-Key (Medium)

⇗ Banking Customer Dim Design

⇗ BigQuery Large Dim Optimisation

⇗ REFERENCE DOCUMENTATION & FURTHER READING

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 63 / 126

 ⇗ Kimball — SCD Type 7 ⇗ Medium — SCD Type 7 Dual-Key Pattern

⬡ BENEFITS ✔ ADOPT WHEN

✦ Achieves Type 6 functionality without physically ✔ Type 6 is the target functionality but bulk UPDATE
overwriting current_* columns on all prior rows of prior Type 2 rows is too costly

✦ ETL update burden is eliminated — no mass ✔ The database engine or pipeline framework
update of historical rows required handles dual FK joins efficiently

✦ Dual FK pattern makes the dual-view intent ✔ The BI/analytics layer can abstract the dual FK
explicit and visible in the fact table schema complexity away from end users

✦ Works well in environments where UPDATE ✔ The team prefers a key-relationship solution over
operations on large dimension tables are a physical data overwrite approach
expensive

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 64 / 126

 SCD Type 2 — Row Versioning Production Engineering Standard
Implementation Pattern
SCD · IMPLEMENTATION DETAIL · 3.9

Description: SCD Type 2 is the most widely used change handling pattern, but the concept
card alone doesn't show how to implement the versioning metadata. Every SCD Type 2 table
must carry four standard engineering columns: effective_from_date , effective_to_date ,
is_current , and row_version . These enable point-in-time reconstruction without
recursive CTEs and allow BI tools to always retrieve the current row efficiently.

STANDARD VERSIONING COLUMNS — PURPOSE & RULES

COLUMN TYPE VALUE RULES PURPOSE

surrogate_key INTEGER PK Unique per row — new Physical row identity;

SK assigned for each used in fact table FK
new version

natural_key VARCHAR Same value across all Business identifier;

versions of the same used to join to source
real-world entity systems

durable_key INTEGER Same value across all Cross-version

versions (entity- grouping; used in SCD
level, not row-level) Type 6/7

effective_from_date DATE NOT Date this version Lower bound of version

NULL became the truth. validity window
First version =
earliest known date or
business open date

effective_to_date DATE NOT Current row: Upper bound of version

NULL 9999-12-31 (sentinel). validity; NOT NULL
Expired row: day avoids awkward IS NULL
before next version's checks
effective_from_date

is_current BOOLEAN / 1 for current row, 0 Fast current-row

TINYINT for all expired rows. retrieval without date
Must be maintained on comparison: WHERE
every UPDATE/INSERT is_current = 1

row_version INTEGER 1 for first version, Sequence/audit — how

increments per change many times has this
per entity entity changed?

load_timestamp TIMESTAMP When this row was ETL audit; differs

physically loaded into from
the DW effective_from_date
(transaction time vs
valid time)

row_hash VARCHAR(64) SHA-256 of all tracked Change detection in

attribute columns ETL — compare hash to
detect updates without
column-by-column
comparison

FULL SCD TYPE 2 TABLE EXAMPLE — DIMEMPLOYEE WITH ALL

VERSIONING COLUMNS

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 65 / 126

 EMP_KEY (SK) NATURAL_KEY DURABLE_KEY EMP_NAME DEPARTMENT SALARY_BAND EFF_FROM EFF_TO

1001 EMP-77 D-55 Alice Sales L3 2020-03-01 2023-06

Chen

1042 EMP-77 D-55 Alice Engineering L4 ← 2023-06-15 9999-12

Chen ← changed changed ←curren

POINT-IN-TIME QUERY PATTERN

-- Get dimension attributes AS OF a specific date (e.g. at time of a

sales transaction)
SELECT e.emp_name, [Link], e.salary_band
FROM FactSales f
JOIN DimEmployee e
ON f.emp_key = e.emp_key -- surrogate key; already points to
correct version row

-- OR: Reconstruct "as-of 2022-01-01" view from durable_key

SELECT * FROM DimEmployee
WHERE durable_key = 55
AND effective_from_date <= '2022-01-01'
AND effective_to_date >= '2022-01-01';

-- Fast current-row lookup (no date comparison)

SELECT * FROM DimEmployee WHERE is_current = 1;

9999-12-31 sentinel vs NULL: Always use 9999-12-31 for open-ended (current)

rows, never NULL. NULL in effective_to_date forces IS NULL checks everywhere and
makes BETWEEN date range queries impossible. The sentinel makes range queries
uniform: BETWEEN effective_from AND effective_to works for all rows.

ETL process in 4 steps: (1) Hash-compare incoming source row against current DW
row. (2) If changed: UPDATE current row → set effective_to_date = yesterday, is_current
= 0. (3) INSERT new row → effective_from = today, effective_to = 9999-12-31,
is_current = 1, row_version = prior + 1. (4) If new entity: INSERT with effective_from =
business open date or load date.

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 66 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

E-COMMERCE TELECOM

Flipkart / Amazon DimCustomer SCD2 Airtel / Jio DimSubscriber Production

Engineering Implementation
DimCustomer table: surrogate_key (auto- 600M subscriber dimension. Nightly job: (1)
increment), natural_key (customer_id from hash all 12 tracked attributes per subscriber,
source), durable_key, all business attributes, (2) compare against current row hash in
effective_from_date, effective_to_date DimSubscriber, (3) rows with changed hash:
(9999-12-31 for current), is_current (1/0), UPDATE old row effective_to_date=yesterday,
row_version, row_hash (SHA-256 of tracked is_current=0 → INSERT new row
columns), load_timestamp, source_system. effective_from=today, is_current=1,
dbt model uses the "snapshot" materialization row_version+=1. Full run on changed rows
strategy with unique_key=natural_key and only (~200K changes/night) completes in 8
strategy="check" with minutes in BigQuery.
check_cols=["address","segment","tier"].
Row hash reduces change detection from
dbt snapshot: 6-line YAML config 12-column comparison to 1 string
generates all 9 versioning columns comparison — 4× faster nightly ETL on
automatically; engineers never write SCD2 600M rows
ETL by hand

⇗ BigQuery Large-Scale DML

⇗ dbt Snapshots Full Docs

⇗ Telecom Analytics (GCP)

⇗ BigQuery SCD Type 2 Guide

⇗ Delta Lake MERGE at Scale

⇗ Delta Lake SCD2 MERGE

⇗ Snowflake SCD2 Streams

BANKING

SBI / Canara Bank Regulatory Audit

Query
Regulator asks: "What was customer
C-88421's risk classification on October 15,
2022?" Query: SELECT * FROM DimCustomer
WHERE natural_key='C-88421' AND
effective_from_date <= '2022-10-15' AND
effective_to_date >= '2022-10-15'. The
9999-12-31 sentinel makes this single
BETWEEN query work for both current and
historical rows identically.

9999-12-31 sentinel is non-negotiable for

regulatory audit queries — NULL
effective_to_date requires IS NULL
special-casing

⇗ BigQuery SCD2 Audit Patterns

⇗ RBI Audit Trail Requirements

⇗ 9999-12-31 Sentinel Pattern

⇗ REFERENCE DOCUMENTATION & FURTHER READING

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 67 / 126

 ⇗ dbt — Snapshots Full Documentation ⇗ BigQuery — SCD2 MERGE Statement

⇗ Databricks — Delta Lake MERGE SCD2 ⇗ Snowflake — Streams & SCD2 Tasks

⇗ Azure Synapse — SCD Type 2 Pattern

⬡ BENEFITS OF STANDARD VERSIONING ✔ ADOPT WHEN

COLUMNS
✔ Implementing any SCD Type 2 dimension — these
✦ is_current = 1 enables instant current-row columns are mandatory, not optional
retrieval without any date range logic
✔ Always use 9999-12-31 as the sentinel —
✦ 9999-12-31 sentinel makes BETWEEN range standardise across all dims in the DW
queries work uniformly for all rows
✔ Always maintain is_current as a redundant
✦ row_hash eliminates column-by-column change shortcut — the performance benefit is worth the
detection — one string comparison per row in ETL maintenance

✦ row_version provides an auditable sequence of ✔ Add row_hash to all Type 2 dims to simplify ETL
changes per entity change detection logic

✦ load_timestamp separates ETL processing time ✔ Include load_timestamp on every row to support
from business validity time pipeline auditing and late-data investigation

Rapidly Changing Dimensions

CHAPTER 04

(RCD)

When attributes change so frequently that SCD Type 2 would cause unmanageable row
explosion. RCD patterns isolate the volatile attributes into separate structures to maintain
performance.

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 68 / 126

 Pattern 1 — Mini-Dimension Core Pattern
RCD PATTERN · 4.1

Description: Split the fast-changing attributes out of the main dimension into a separate,
smaller "mini-dimension." The fact table carries a FK to both. The main dimension retains only
stable attributes and uses SCD Type 1 or Type 2 as appropriate. The mini-dimension grows by
appending new rows as attribute values change.

📈
Use Case: Customer Risk ProfileA financial services company re-scores 2M
customers monthly on income_band, credit_tier, and risk_rating. If kept in DimCustomer
as Type 2, this would add 2M rows/month. Instead, these 3 attributes go into
DimCustProfile — a mini-dim with only a few thousand distinct combinations.

DIMCUSTOMER (STABLE DIMCUSTPROFILE (MINI-DIM — FAST ATTRS)

ATTRIBUTES)
PROFILE_KEY INCOME_BAND CREDIT_TIER RISK_RATING
CUST_KEY NAME DOB CITY
P-10 Mid B Low
C-001 Alice 1985-04-12 Boston
Wang P-11 High A Low

P-12 Mid C Medium

FACTTRANSACTIONS — JOINS BOTH DIMS

TXN_KEY DATE_KEY CUST_KEY FK PROFILE_KEY FK AMOUNT

T-2001 20240301 C-001 P-10 450.00

T-2090 20240401 C-001 P-11 890.00

Alice's profile changed from P-10 (Mid/B/Low) to P-11 (High/A/Low) by April. The fact
table captures which profile was active at each transaction — without touching
DimCustomer at all.

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 69 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

BANKING / FINTECH HEALTHCARE

CIBIL / Experian Customer Credit Score AIIMS / Manipal Hospital Patient Risk
45M customers have credit scores re- Stratification
calculated monthly. Applying SCD Type 2 to Patients are re-stratified into risk bands (Low/
score_band, credit_tier, and income_band Medium/High/Critical) after each consultation
would add 45M×3 = 135M new rows/month. based on BMI, BP, and diabetes status. 8M
Mini-dim solution: DimCreditProfile (18 patients × quarterly re-stratification = 32M
combinations of score_band × credit_tier × SCD2 rows/year if in base dim. Mini-dim:
income_band = 18 rows, static). Each fact row DimPatientRisk (12 rows: 3 risk_levels × 2
carries credit_profile_key capturing the exact diabetes_status × 2 hypertension_status).
profile at transaction time. FactTreatmentClaim carries patient_key and
risk_profile_key independently.
DimCreditProfile: 18 rows, never grows.
DimCustomer: 45M rows, stable. Mini-dim is essential when re-
FactTransaction: credit_profile_key classification happens at high frequency
changes monthly per customer. across a large population — classic RCD
use case

⇗ Kimball Rapidly Changing Monster Dim

⇗ Kimball Rapidly Changing Monster Dim

⇗ CIBIL Score Methodology

⇗ Healthcare Risk Stratification (AWS)

⇗ Mini-Dimension Design (Medium)

⇗ Patient Analytics GCP Healthcare

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Rapidly Changing Monster Dimensions

⇗ Medium — Mini-Dimension Design Pattern ⇗ dbt — Modeling Rapidly Changing Attributes

⬡ BENEFITS ✔ ADOPT WHEN

✦ Eliminates the row explosion that SCD Type 2 ✔ A dimension has attributes that change very
would cause for high-frequency attribute changes frequently (monthly, weekly) for a large population

✦ Main dimension stays compact — only stable ✔ Applying SCD Type 2 to these attributes would
attributes, loaded once or rarely cause unacceptable row growth (e.g. 2M rows/
month)
✦ Point-in-time profile preserved in the fact table
FK — no analytical accuracy is lost ✔ The fast-changing attributes form meaningful
analytical groups (income band, risk tier)
✦ Mini-dim has far fewer rows than the base dim
— combinations, not per-entity versions ✔ Both historical accuracy (at transaction time) and
current state are required for analysis

✔ Examples: customer credit scoring, patient health

metrics, subscriber tier re-classification

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 70 / 126

 Pattern 2 — Push to Periodic Snapshot Misplaced Measure Fix
Fact
RCD PATTERN · 4.2

Description: Some attributes that appear to be dimension attributes are actually numeric
measures masquerading as dimensions. The solution is to move them out of the dimension
entirely and into a periodic snapshot fact table, where they belong as measures.

💹
Use Case: Product PriceProduct price changes daily. It feels like a product attribute
but it's actually a measure. Instead of DimProduct.current_price (which would require
daily Type 2 rows), create FactDailyProductPrice with one row per product per day.

DIMPRODUCT (STABLE — FACTDAILYPRODUCTPRICE (PERIODIC

NO PRICE) SNAPSHOT)

PRODUCT_KEY NAME CATEGORY DATE_KEY PRODUCT_KEY LIST_PRICE COST_PRICE

P-042 Widget Hardware 20240301 P-042 24.99 12.00

Pro
20240302 P-042 22.99 12.00

◈ REAL-WORLD PROJECT EXAMPLES

RETAIL / FMCG FINANCE / FOREX

Bigbasket / JioMart Daily Product NSE / BSE / Zerodha Exchange Rate

Pricing Snapshot
Product prices change daily (dynamic pricing, USD/INR, EUR/INR, and 20 other pairs are
competitor matching). list_price in DimProduct captured at market close daily. Storing
changes too frequently for SCD2. Push to fact: exchange_rate in DimCurrency makes no
FactDailyProductPrice (periodic snapshot) has sense — rates change continuously.
one row per product × day with list_price, FactCurrencyRate (periodic snapshot) captures
cost_price, and discount_band. "What was the each rate × date × currency pair.
price on March 15?" is a simple date filter. FactInternationalSales joins to
DimProduct stays stable with just category, FactCurrencyRate on date for correct INR
brand, and description. conversion at transaction date.

FactDailyProductPrice: 80,000 SKUs × 365 Exchange rates are the canonical example
days = 29M rows/year — manageable and of a numeric measure that superficially
analytically correct looks like a dimension attribute

⇗ Kimball Numeric Value Dimension ⇗ NSE Data APIs

⇗ Dynamic Pricing Analytics (GCP) ⇗ RBI Reference Rates

⇗ Price History Pattern (dbt) ⇗ Financial Data Warehouse (GCP)

⇗ REFERENCE DOCUMENTATION & FURTHER READING

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 71 / 126

 ⇗ Kimball — Numeric Value Dimensions ⇗ BigQuery — Periodic Snapshot Patterns

⇗ dbt — Snapshot to Periodic Fact

⬡ BENEFITS ✔ ADOPT WHEN

✦ Correctly reclassifies a numeric measure that ✔ An attribute is actually a numeric measure (price,
was wrongly modelled as a dimension attribute score, rate) masquerading as a dimension column

✦ DimProduct remains stable and compact — no ✔ The value changes continuously and every data
pricing churn pollutes it point at each time period has independent
analytical value
✦ Full price history is naturally available via
periodic snapshot fact table query ✔ The cadence of change maps naturally to a
periodic snapshot (daily price, monthly rate)
✦ Aligns with first-principles dimensional
modelling: measures belong in fact tables ✔ Examples: product pricing, exchange rates,
interest rates, benchmark scores

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 72 / 126

 Pattern 3 — Junk Dimension for Low- Pre-built Combinations
Cardinality Fast Flags
RCD PATTERN · 4.3

Description: When the rapidly changing attributes are low-cardinality flags or indicators (not
continuous values), pre-build all possible combinations into a junk dimension. Since every
possible combination already exists in the table, new "changes" simply point to a different
existing row in the junk dim — the junk dim itself never grows.

🏷️
Use Case: Subscription Status FlagsA SaaS platform customer's subscription_tier
(Free/Pro/Enterprise), is_trial (Y/N), and auto_renew (Y/N) change frequently. With
3×2×2=12 combinations, the junk dim has 12 static rows. Customer "changes" just
update which row they point to in the fact or a current-profile table.

DIMSUBFLAGS — 12 STATIC ROWS, PRE-BUILT

FLAG_KEY SUBSCRIPTION_TIER IS_TRIAL AUTO_RENEW

1 Free N N

2 Free Y N

5 Pro N Y

9 Enterprise N Y

… 12 total rows — never changes

✓ Junk dim is completely static ✓ Changes cost-free (just update FK) ✗ Only works
for low-cardinality combos

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 73 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

E-COMMERCE BANKING

Meesho / Snapdeal Order Attribute Paytm / Razorpay Payment Transaction

Flags Flags
Order processing flags change rapidly: Transaction flags that change rapidly:
is_flagged_for_review (Y/N), payment_channel (5 values), is_emv_chip (Y/
is_priority_fulfillment (Y/N), N), is_contactless (Y/N), is_international (Y/N).
manual_review_required (Y/N), 5×2×2×2 = 40 combinations.
payment_status (3 values). These change DimPaymentProfile pre-loaded with 40 rows. A
multiple times per day per order. 2×2×2×3 = chargeback changes is_disputed from N to Y
24 combinations in DimOrderFlags (static junk — just an FK update to the disputed
dim). Updating a flag = just updating the FK combination row. No SCD2 overhead.
on the fact row to a different pre-existing
combination row. Zero new dimension rows Fraud analytics team queries:
ever created. "contactless + international + disputed"
= single DimPaymentProfile.profile_key
Junk dim update = one FK update on fact filter
row. SCD Type 2 update = new dim row +
date range update. Junk dim is 100×
⇗ Kimball Junk Dimension
cheaper for high-frequency flag changes.

⇗ Razorpay API Docs

⇗ Kimball Junk Dimension

⇗ FinTech DW Architecture (GCP)

⇗ E-Commerce DW Flags Pattern

⇗ dbt Modeling Junk Dim

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Junk Dimensions for Flags

⇗ Medium — Junk Dimensions for Transactional Flags

⇗ dbt — Modeling Low-Cardinality Dimensions

⬡ BENEFITS ✔ ADOPT WHEN

✦ Junk dim is static — never needs inserts or ✔ The fast-changing attributes are categorical flags
updates as flags change for individual entities or indicators with very low cardinality each

✦ "Changes" are just FK updates pointing to a ✔ Total combination count across all flags is small
different pre-existing combination row and manageable (under ~1,000 rows)

✦ All flag combinations are filterable in a single ✔ The attribute set is stable — new flag types are
dimension join added infrequently

✦ No row explosion whatsoever — bounded by ✔ The junk dim pattern already exists in the model
total possible combinations — extending it is the natural fit

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 74 / 126

 Bi-Temporal
CHAPTER 05

Modeling

Two independent timelines per record. The most comprehensive approach to temporal data
management — supports both "what was true in reality" and "when did we know it"
independently.

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 75 / 126

 Bi-Temporal Model Audit-Grade History
TEMPORAL PATTERN · 5.1

Description: Every record carries two independent timelines: Valid Time (when the fact was
true in the real world) and Transaction Time (when the record was stored in the database).
This allows answering four types of questions that no single-timeline model can: current
reality, historical reality, what we knew now about the past, and what we knew then about the
past.

THE TWO TIMELINES EXPLAINED

Valid Time (VT) Transaction Time (TT)

When was this true in the real world? When did we record/know this in our
e.g. "Customer lived in Boston from system?
Jan 1 to Mar 31" e.g. "We recorded the Boston address
on Feb 5"

SAMPLE SCENARIO: CUSTOMER ADDRESS HISTORY

Alice moved from Boston to Chicago on Feb 1, but didn't update her profile until Feb 15. Then
on Mar 10, she corrects the record with a backdated Boston address (she was actually in
Boston until Jan 31, not Feb 1).

BI-TEMPORAL TABLE — DIMCUSTOMER_BT

CUST_ID CITY VT_FROM (VALID) VT_TO (VALID) TT_FROM (TRANSACTION) TT_TO (TRANSACTION)

C-001 Boston 2023-01-01 2024-02-01 2023-01-01 2024-03-10

C-001 Boston 2023-01-01 2024-01-31 2024-03-10 9999-12-31

C-001 Chicago 2024-02-01 9999-12-31 2024-02-15 9999-12-31

QUERY TYPES ENABLED

📍 ⏮️ 🕵️
"Current "Jan 15 "What we knew on
Reality"WHERE vt_to Reality"WHERE Feb 10"WHERE
= '9999' AND tt_to = '2024-01-15' BETWEEN '2024-02-10' BETWEEN
'9999' → Chicago vt_from AND vt_to AND tt_from AND tt_to → we
tt_to = '9999' → Boston thought: Boston until
Feb 1

Use only when needed: Bi-temporal is the most complex and storage-intensive
pattern. Use it in regulated industries (banking, healthcare, insurance) where audit
trails must prove "what did we believe, and when did we believe it."

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 76 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

BANKING / REGULATORY INSURANCE

RBI / SEBI Regulatory Compliance DW LIC / New India Assurance Policy

A bank records a customer's KYC risk Backdating
classification as "Low Risk" on Jan 1 An insurance policy is issued on March 15 but
(valid_from=Jan 1, transaction_time=Jan 1). backdated to March 1 per the customer's
On March 15, new AML guidelines reclassify request (valid_from = March 1). The system
the customer as "Medium Risk" retroactively recorded it on March 15 (transaction_time =
from Feb 1 (valid_from=Feb 1, March 15). A claim filed on March 10 requires
transaction_time=Mar 15). Bi-temporal proving the policy was valid on March 10
preserves both: "what we recorded in (VT=March 10) even though the system only
January" (TT=Jan) AND "what was actually knew about it from March 15 (TT=March 15).
true from Feb 1" (VT=Feb 1). Auditors can Bi-temporal handles this exactly.
query both independently.
IRDAI regulatory requirement: system must
RBI inspection question: "What did your prove what coverage existed (valid time)
system say about this customer's risk on vs when it was recorded (transaction
February 28?" — only bi-temporal can time)
answer accurately

⇗ Martin Fowler — Bi-Temporal History

⇗ Martin Fowler — Bi-Temporal History

⇗ IRDAI Policy Guidelines

⇗ SEBI Surveillance System

⇗ Insurance DW Bi-Temporal (Medium)

⇗ Temporal Tables SQL:2011

⇗ Snowflake Time Travel

HEALTHCARE

CGHS / ESIC Drug Price Regulatory

Reporting
Drug prices are retrospectively revised by the
National Pharmaceutical Pricing Authority. A
price valid from April 1 is officially notified on
May 10. Bi-temporal: valid_time = April 1
(when price was effective), transaction_time =
May 10 (when the DW received the data).
Hospitals can report "price as we knew it in
April" vs "retrospectively corrected price for
April" independently.

Retrospective price revisions are common

in regulated healthcare — bi-temporal is
the only correct model

⇗ Martin Fowler — Bi-Temporal History

⇗ NPPA Drug Price Portal

⇗ Delta Lake Time Travel

⇗ REFERENCE DOCUMENTATION & FURTHER READING

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 77 / 126

 ⇗ Martin Fowler — Bi-Temporal History Pattern ⇗ Wikipedia — Temporal Database

⇗ SQL:2011 — Temporal Tables Standard ⇗ Snowflake — Time Travel & Bi-Temporal

⇗ Databricks — Delta Lake Time Travel

⬡ BENEFITS ✔ ADOPT WHEN

✦ Answers all four temporal questions: current ✔ Regulated industry requires proving what the
reality, historical reality, what we knew now, what system believed at a specific recorded time
we knew then (banking, insurance, healthcare)

✦ Supports retroactive corrections without ✔ Source data corrections arrive after the fact and
destroying the prior recorded state — both must be recorded without overwriting the original
versions coexist recording

✦ Full audit trail: prove to regulators exactly what ✔ Auditors or compliance teams need to
the system believed at any point in time reconstruct both the real-world state and the
system's knowledge state independently
✦ Handles late-arriving corrections gracefully — a
new TT row is added without altering old TT rows ✔ The analytical team is mature enough to work
with two-timeline query patterns

✔ Do NOT use for general analytics — the added

complexity is only justified by genuine audit/
compliance need

Schema
CHAPTER 06

Patterns

How fact and dimension tables are physically arranged and connected. Schema choice
impacts query performance, storage efficiency, and ETL complexity.

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 78 / 126

 Star Schema Industry Standard
SCHEMA PATTERN · 6.1

Description: One central fact table surrounded by denormalized dimension tables. Each
dimension is a single table with all attributes fully denormalized (e.g., DimStore contains city,
state, country — not normalized into sub-tables). Named "star" because the ER diagram looks
like a star. The dominant pattern for data warehouses and BI workloads.

⭐
Use Case: Retail Analytics DWA retailer builds a star schema with FactSales at
center, surrounded by DimDate, DimProduct, DimStore, DimCustomer, DimPromotion.
All joins are single-hop — fact to dim. BI tools like Tableau, Power BI, Looker perform
best on star schemas.

◈ STAR SCHEMA — FACTSALES AT CENTER

DimDate

date_key PK
full_date, month
quarter, year

FactSales

date_key FK
DimProduct product_key FK
DimStore

product_key PK store_key FK store_key PK

name, category customer_key FK name, city
brand, subcategory quantity state, region
revenue
discount

DimCustomer

customer_key PK
name, segment
city, country

✓ Simple, fast queries (1 join per dim) ✓ BI tool friendly ✓ Easy for analysts to
understand ✗ Some data redundancy in dims

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 79 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

RETAIL HEALTHCARE

Reliance Retail / DMart Sales Data Apollo Hospitals Patient Billing Mart
Mart FactPatientBilling → DimPatient, DimDoctor,
FactSales (centre) with DimDate, DimProduct, DimProcedure, DimDate, DimInsurancePlan,
DimStore, DimCustomer, DimPromotion as DimWard. Hospital management uses Power
direct dimension spokes. Tableau/Power BI BI on this star schema. No joins beyond one
connects directly — all joins are single-hop. A hop — billing analysts without SQL expertise
store manager's "daily sales by category" can self-serve using drag-and-drop in the BI
report executes in <2 seconds on BigQuery tool.
because there are no snowflake join chains.
Healthcare billing mart: 6 dimensions,
Star schema is the universal default for all fully denormalised into star schema
BI-facing layers; all 6 major BI tools for non-technical analyst self-service
have native star schema optimisation

⇗ Kimball Star Schema

⇗ Kimball Star Schema

⇗ GCP Healthcare Analytics

⇗ Microsoft — Star Schema Guide

⇗ Power BI Healthcare Template

⇗ BigQuery Star Schema Best Practices

⇗ dbt Mart Structure

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Star Schema Design ⇗ Microsoft — Star Schema in Power BI

⇗ BigQuery — Star Schema Best Practices ⇗ dbt — Mart Structure (Star Schema)

⇗ Databricks — Star Schema on Lakehouse

⬡ BENEFITS ✔ ADOPT WHEN

✦ Single-hop joins from fact to every dimension — ✔ Building a presentation/reporting layer for BI
maximum query performance tools and business analysts

✦ All major BI tools (Tableau, Power BI, Looker, ✔ Query performance and simplicity are higher
Metabase) are natively optimised for star schema priorities than perfect storage normalisation

✦ Intuitive structure — business analysts can ✔ Dimensions are stable and not subject to
understand and self-serve without SQL expertise frequent hierarchical restructuring

✦ Denormalized dims reduce join complexity in ✔ This is the recommended default for all DW
queries and simplify ETL logic presentation layers — use it unless there is a
specific reason not to
✦ Industry default for DW presentation layers —
most tooling, best practices, and talent assume
star schema

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 80 / 126

 Snowflake Schema Normalized Dims
SCHEMA PATTERN · 6.2

Description: Dimension tables are normalized — their attributes are broken out into sub-
dimension tables. DimProduct might reference DimCategory, DimBrand. This reduces storage
redundancy but introduces multi-hop joins. Named "snowflake" because the branching ER
diagram resembles a snowflake.

❄️
Use Case: Product HierarchyProduct belongs to a subcategory, which belongs to a
category, which belongs to a department. In a snowflake, these are separate tables.
This is storage-efficient if the hierarchy changes frequently, but adds join complexity.

◈ SNOWFLAKE — DIMPRODUCT CHAIN

DimProduct
FactSales product_key PK
product_key FK
→ product_name
→
subcat_key FK →

DimSubCategory DimCategory

subcat_key PK cat_key PK
subcat_name
→ category_name
cat_key FK → dept_key FK →

✓ Reduced storage for large hierarchies ✓ Easier to maintain hierarchy changes ✗

Multi-hop joins hurt query performance ✗ BI tools work less smoothly

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 81 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

MANUFACTURING RETAIL

Tata Motors / Mahindra Bill of Global Retail Product Hierarchy

Materials DimProduct → DimSubcategory →
DimPart has a deep hierarchy: Part → DimCategory → DimDepartment. When "Home
SubAssembly → Assembly → Module → Vehicle. Decor" sub-category is restructured, only
Normalising into separate tables (DimPart, DimSubcategory needs updating — not all
DimSubAssembly, DimAssembly, DimModule) 50,000 product rows. Trade-off: BI tool joins
reduces storage by 40% for a 500,000-part require 3 hops instead of 1. Most teams
catalogue. Engineers querying BOM structures eventually flatten this into a star schema view
use the normalised tables; the BI reporting for analysts while keeping snowflake for ETL
layer uses a denormalised star view. efficiency.

Snowflake in manufacturing is justified A common pattern: snowflake schema for

by deep, frequently-changing hierarchy ETL/storage layer + materialised star
structures — rebuilding the snowflake is schema view for BI consumption
cheaper than updating millions of
denormalised rows
⇗ Kimball Snowflake Schema

⇗ Kimball Snowflake Schema

⇗ GS1 Product Hierarchy Standards

⇗ SAP BOM Analytics

⇗ dbt Staging → Intermediate → Mart

⇗ Manufacturing DW Design (Medium)

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Snowflake Schema Considerations ⇗ Microsoft — Snowflake vs Star Schema

⇗ Medium — Star vs Snowflake Schema ⇗ Snowflake — Schema Design Patterns

⬡ BENEFITS ✔ ADOPT WHEN

✦ Reduces storage footprint for very large ✔ Dimension hierarchies are very deep and the
dimension tables with deep hierarchies hierarchy structure changes frequently

✦ Hierarchy changes (e.g. category restructuring) ✔ Storage is a hard constraint and dimension
update only one sub-dimension table redundancy is measurably significant

✦ Enforces referential integrity at the database ✔ The BI tooling in use handles multi-hop joins well
level through FK constraints (e.g. direct SQL access rather than semantic model)

✦ Avoids denormalisation anomalies in ✔ Prefer star schema for analyst-facing layers —

environments where hierarchy is frequently snowflake adds friction for non-technical users
reorganised

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 82 / 126

 Galaxy Schema (Fact Constellation) Enterprise Multi-Mart
SCHEMA PATTERN · 6.3

Description: Multiple fact tables sharing conformed dimension tables. Each business process
gets its own fact table, and shared dimensions (DimDate, DimCustomer, DimProduct) are
reused across all of them. This is how enterprise data warehouses are built — multiple stars
connected through shared conformed dims.

🌌
Use Case: Retail Enterprise DWFactSales, FactInventory, FactReturns all share
DimDate, DimProduct, and DimStore. Analysts can "drill across" — compare sales
revenue vs. inventory levels vs. return rates on the same product and date axis.

◈ FACT CONSTELLATION — SHARED DIMDATE AND DIMPRODUCT

FactSales FactInventory

date_key FK date_key FK
product_key FK product_key FK

FactReturns

date_key FK
product_key FK

↓ shared conformed dimensions ↓

DimDate (CONFORMED)

date_key PK

DimProduct (CONFORMED)

product_key PK

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 83 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

ENTERPRISE RETAIL FINANCIAL SERVICES

Walmart India / Tesco Enterprise DW HDFC / Axis Bank Enterprise Analytics

Three fact tables share conformed dimensions: FactRetailTransactions, FactCorporateLoans,
FactSales (transaction), FactInventory (daily FactTreasuryTrades, FactHRPayroll — four subject
snapshot), FactMarketing (monthly). All three areas sharing DimDate, DimCustomer (shrunken
share DimDate, DimProduct, DimStore. A drill- subset for corporate), DimEmployee, DimBranch.
across query: "Compare March revenue, end-of- Executive dashboard combines metrics across all
March inventory, and March marketing spend by four fact tables using conformed dimensions. This
product category" joins all three fact tables on the is the galaxy/fact constellation architecture.
same conformed DimDate and DimProduct keys.
Key governance requirement: all four teams
Galaxy schema is the natural outcome of must agree on conformed DimCustomer
adding a second or third subject area to a definition — the Bus Matrix drives this
star schema DW

⇗ Kimball Galaxy / Fact Constellation

⇗ Kimball Galaxy / Fact Constellation

⇗ Banking Enterprise Analytics (GCP)

⇗ Enterprise DW Reference Arch (Microsoft)

⇗ HDFC Bank Tech Architecture (Case Study)

⇗ GCP Enterprise DW Reference

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Fact Constellation / Galaxy Schema ⇗ Medium — Galaxy Schema Explained

⇗ Microsoft — Multi-Fact Models in Power BI

⬡ BENEFITS ✔ ADOPT WHEN

✦ Enables drill-across queries — compare sales, ✔ Enterprise DW covering multiple business

inventory, and returns on the same date/product processes (sales, inventory, HR, finance) that share
axis common dimensions

✦ Conformed dimensions enforce consistent ✔ Cross-process analysis is a core requirement —

definitions across all subject areas management needs to compare metrics across
subject areas
✦ Each business process gets its own optimised
fact table without being forced into a single ✔ A data governance team can own and maintain
monolithic structure conformed dimensions as shared enterprise assets

✦ Scales naturally as new business processes are ✔ Multiple teams build separate data marts that
added — just add another fact table sharing need to interoperate through shared dimension
existing conformed dims definitions

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 84 / 126

 Data Vault 2.0 Ingestion Layer
SCHEMA PATTERN · 6.4

Description: A methodology for modeling the raw ingestion layer of a data warehouse. Uses
three table types: Hubs (business keys), Links (relationships between business keys), and
Satellites (descriptive attributes + history). Hash keys replace integer sequences. Highly
parallelizable, source-agnostic, and fully auditable. Typically sits beneath the Kimball
presentation layer.

🏗️
Use Case: Enterprise Data Integration LayerIntegrating Customer data from 5
source systems (CRM, billing, support, marketing, app). Each source feeds into the same
Hub_Customer (keyed by business key). Satellites per source capture source-specific
attributes. Links capture relationships (customer-to-order, customer-to-account).

DATA VAULT COMPONENTS

◈ HUB → LINK → SATELLITE ARCHITECTURE

Hub_Customer
business_key, hash_key, load_date, rec_src

Link_CustomerOrder Hub_Order
cust_hash_key + order_hash_key order_id, hash_key

Sat_Customer_CRM Sat_Customer_Billing Sat_Order_Details

name, email, segment billing_addr, payment_tier amount, status, channel

HASH_KEY CUST_ID (BIZ KEY) LOAD_DTS REC_SRC

HUB_CUSTOMER
SAMPLE a3f4e2... CUST-1001 2024-03-01 CRM

✓ Highly parallel loads — no contention ✓ Full audit trail by design ✓ Source-system

agnostic ✗ Complex — needs tooling (dbt, WhereScape) ✗ Not analyst-friendly —
needs a presentation layer

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 85 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

INSURANCE BANKING / REGULATORY

Bajaj Allianz / ICICI Lombard Multi- IndusInd / RBL Bank RBI CRILC
Source Integration Reporting
Claims data comes from 6 legacy systems: RBI's Central Repository of Information on
LegacyMotor, LegacyHealth, LegacyLife, Large Credits requires tracking every loan
AgentPortal, BancassurancePartner, relationship across multiple bank entities
ReinsuranceSystem. Each has different natural with full audit history. Data Vault:
keys for "customer". Data Vault: Hubs store Hub_Borrower, Hub_LoanFacility,
each system's natural key; Links resolve cross- Link_BorrowerFacility, Sat_BorrowerDetails
system customer relationships; Satellites store (versioned), Sat_FacilityTerms (versioned).
version-controlled attributes per source. New regulatory reporting requirements add
Presentation layer builds star schemas from new Satellites without touching existing
vault for BI. ones.

6 source systems with conflicting customer Data Vault additions for new regulatory
IDs — Data Vault's Hub-Link-Satellite requirements never require existing Sat
separation is the only clean integration tables to be altered — critical for live
architecture regulatory systems

⇗ Data Vault 2.0 — Dan Linstedt ⇗ Data Vault 2.0 — Dan Linstedt

⇗ dbt Data Vault Patterns ⇗ RBI CRILC Reporting Framework

⇗ Snowflake — Data Vault Guide ⇗ Banking Regulatory DW (Medium)

⇗ Insurance DW Architecture (Databricks)

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Databricks — Data Vault 2.0 ⇗ dbt — Data Vault Packages & Patterns

⇗ Medium — Data Vault 2.0 Concepts ⇗ Snowflake — Data Vault Architecture

⇗ Wikipedia — Data Vault Modeling

⬡ BENEFITS ✔ ADOPT WHEN

✦ Fully parallelisable loads — Hubs, Links, and ✔ Integrating data from many heterogeneous
Satellites load independently with no inter- source systems with different natural keys and
dependency contention schemas

✦ Every record has full audit metadata: source ✔ Full audit trail and data lineage are hard
system, load timestamp, hash key — traceability regulatory requirements (financial services,
is built-in government)

✦ Schema is source-agnostic — adding a new ✔ Load parallelism is critical — very high data
source system adds new Satellites, never alters volumes that sequential loading cannot handle
existing ones
✔ The team has tooling (dbt vault packages,
✦ Historical changes are preserved automatically WhereScape) to automate the boilerplate
via Satellite versioning — no SCD decisions
✔ A Kimball star schema presentation layer will sit
needed per attribute
above it for analyst consumption

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 86 / 126

 One Big Table (OBT) Modern Lakehouse
SCHEMA PATTERN · 6.5

Description: All facts and dimension attributes are pre-joined and denormalized into a single
wide table. Leverages columnar storage engines (BigQuery, Snowflake, Redshift) which
compress repeated values efficiently. Eliminates join overhead entirely at query time.
Common in the modern "semantic layer" and reverse-ETL era.

📊
Use Case: BigQuery Analytics LayerA startup with 10M rows pre-joins all dimension
attributes into one wide table. No joins at query time. BigQuery columnar compression
makes repeated strings (like "United States" in country column) storage-efficient. Tools
like Metabase, Preset, or Hex perform very fast on OBT.

ONE BIG TABLE — EVERYTHING IN ONE ROW

SALE_DATE PRODUCT_NAME CATEGORY BRAND STORE_CITY STORE_REGION CUST_SEGMENT QUANTITY

2024-03-01 Widget Pro Hardware Acme Boston North-East Premium 3

2024-03-01 Gadget Lite Electronics TechCo Chicago Mid-West Standard 1

✓ Zero join overhead ✓ Ideal for columnar engines ✓ BI tools love it ✗ Harder to
manage dimension changes ✗ Some redundancy

◈ REAL-WORLD PROJECT EXAMPLES

TECH STARTUP / ANALYTICS RETAIL / D2C

Razorpay / Zepto Early-Stage Analytics Mamaearth / Boat (D2C Brands)

A fintech startup's entire analytics is built on Shopify Analytics
one OBT in BigQuery: payment_obt with all A direct-to-consumer brand exports Shopify
transaction, merchant, customer, and date orders to BigQuery as a single orders_obt:
attributes in one 80-column table. No joins in order_id, customer_email, product_name,
Metabase/Looker — every question answered category, sku, quantity, price, discount,
with a simple SELECT+GROUP BY. As the shipping_city, state, date, channel. All Looker
company scales, the OBT is later split into a Studio reports run directly on this one table.
star schema, but early-stage iteration speed Columnar compression in BigQuery makes the
justifies the simplicity. redundant city/state columns essentially free.

OBT is the pragmatic choice for startups: BigQuery's columnar storage compresses
ship in 1 week vs star schema in 6 weeks; repeated strings like state names to
technical debt addressed at Series B near-zero overhead — OBT storage penalty
scale is <5% vs normalised star schema

⇗ OBT vs Star Schema (Medium) ⇗ BigQuery Shopify Connector

⇗ BigQuery Nested Fields (OBT) ⇗ Shopify Analytics Docs

⇗ dbt OBT Discussion ⇗ dbt Shopify Package

⇗ REFERENCE DOCUMENTATION & FURTHER READING

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 87 / 126

 ⇗ Databricks — One Big Table Pattern ⇗ dbt — OBT vs Star Schema Trade-offs

⇗ BigQuery — Nested & Repeated Fields (OBT) ⇗ Medium — OBT in Modern Analytics

⬡ BENEFITS ✔ ADOPT WHEN

✦ Zero join cost at query time — all attributes ✔ The analytics engine is a columnar cloud DW
already co-located in one table (BigQuery, Snowflake, Redshift, DuckDB)

✦ Columnar engines (BigQuery, Snowflake, ✔ The primary consumer is a BI tool or self-serve

Redshift) compress repeated string values analytics platform where joins create friction
efficiently — storage penalty is lower than it
✔ Data volume and change frequency are low
appears
enough that full rebuilds or incremental merges are
✦ Simplest possible model for self-serve BI — manageable
analysts never need to know about joins
✔ The team is in early-stage growth — model
✦ Ideal for the Gold/Presentation layer in a simplicity matters more than perfect normalisation
Medallion architecture on top of a more
✔ Used as the Gold/Serving layer in a Medallion
normalised raw layer
architecture, sitting above a normalised Silver layer

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 88 / 126

 Medallion Architecture (Bronze / Lakehouse / Cloud Native
Silver / Gold)
SCHEMA PATTERN · 6.6 — MODERN LAKEHOUSE STANDARD

Description: A multi-layer data organisation pattern native to modern cloud lakehouses

(Delta Lake, BigQuery Dataplex, Databricks, Azure Synapse, Snowflake). Raw data is ingested
into a Bronze layer, cleaned and standardised into Silver, and served as business-ready
dimensional models in Gold. Each layer has a distinct quality contract and access pattern.
Dimensional modelling (star schemas, conformed dims) lives in the Gold layer.

LAYER ARCHITECTURE

🥉 BRONZE — Raw / 🥈 SILVER — 🥇 GOLD —

Landing Cleaned / Business / Serving
Conformed
Purpose: Exact copy of Purpose: Business-ready
source data — nothing Purpose: Deduplicated, dimensional model,
filtered, nothing validated, type-cast, aggregates, KPIs
transformed standardised Format: Star schema fact
Format: As-is (JSON, CSV, Format: Delta / Parquet + dimension tables, OBT
Parquet, CDC events) with schema enforcement views
Guarantees: Append- Guarantees: No Guarantees: Business-
only, immutable, full duplicates, correct types, defined metrics,
history retained NULLs handled, referential conformed dimensions,
Access: Data engineers links established SLAs met
only — not for BI Access: Data engineers, Access: BI tools,
Examples: Raw API data scientists, advanced dashboards, self-serve
responses, CDC binlog analysts analysts, executive
events, uploaded CSVs Examples: Cleansed reports
customer records, Examples: FactSales,
Also called: Raw, normalised order events DimCustomer, DimDate,
Landing, Ingestion monthly KPI aggregates
layer Also called: Staging,
Refined, Standardised Also called: Curated,
layer Presentation, Serving
layer

DATA FLOW & TRANSFORMATION RESPONSIBILITIES

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 89 / 126

 CONCERN BRONZE SILVER GOLD

Data None enforced Dedup, validate, cast Business rules applied

Quality

Schema Source schema Standardised types, Dimensional model

as-is renamed columns (Kimball star)

History All versions Deduped — 1 record per SCD Type 2 for dim
retained entity state versioning

Grain Source event Logical entity grain Declared fact grain per
grain subject area

Who Engineers only Engineers + Data BI tools, Analysts,

reads? Scientists Executives

Tooling Kafka, Fivetran, Spark, dbt staging dbt marts, BigQuery

raw copy models materialised views

Relation to dimensional modelling: Kimball star schema design belongs entirely in

the Gold layer. Bronze and Silver are pre-dimensional — they prepare, clean and
normalise data ready for Gold. Many teams add a fourth layer: Platinum / Diamond
for aggregated summary tables and ML feature stores sitting above Gold.

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 90 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

RETAIL / GCP HEALTHCARE / AZURE

Reliance Digital / Tata CLiQ on GCP Max Healthcare / Fortis on Azure

Databricks Databricks
Bronze: raw Kafka event streams + Fivetran Bronze layer: FHIR-format patient records, HL7
extracts land in GCS as-is (JSON/Avro). Silver: lab results, medical imaging metadata land in
dbt models on BigQuery clean, deduplicate, ADLS Gen2. Silver: PySpark jobs standardise
and type-cast into normalised entity tables FHIR to internal schema, mask PII per PDPA
(customer, product, order). Gold: dbt mart compliance. Gold: Delta Live Tables build
models build star schema fact and dimension DimPatient, FactAdmission, FactClaim star
tables for Looker. Dataplex governs all three schema for Power BI. Separate Platinum layer
layers with lineage and data quality rules. for ML feature store (patient readmission risk
features).
GCP Dataplex enforces schema contracts at
each layer boundary; Bronze→Silver→Gold Medallion architecture with 4 layers
lineage visible in Dataplex Catalog (adding Platinum for ML) is becoming
standard in healthcare analytics
platforms
⇗ GCP Databricks Medallion

⇗ GCP Dataplex Governance ⇗ Azure Databricks Medallion

⇗ dbt Project Structure ⇗ Azure Health Data Services

⇗ BigQuery + Databricks Integration ⇗ Delta Live Tables Docs

⇗ FHIR on Azure

FINANCIAL SERVICES / AWS

Kotak / Yes Bank on AWS S3 + Redshift

Bronze: CDC from Oracle core banking via
AWS DMS lands in S3 as raw Parquet. Silver:
AWS Glue ETL deduplicates and conforms into
Silver S3 tables. Gold: Redshift Spectrum
serves dimensional model views to Tableau.
dbt Cloud runs transformations from Silver to
Gold. Each layer in a separate S3 prefix with
separate IAM policies.

Medallion architecture maps directly to

dbt project structure: staging models =
Silver; mart models = Gold

⇗ AWS Medallion Architecture

⇗ AWS Redshift Best Practices

⇗ AWS DMS for CDC

⇗ dbt Cloud on AWS

⇗ REFERENCE DOCUMENTATION & FURTHER READING

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 91 / 126

 ⇗ Databricks — Medallion Architecture ⇗ dbt — Project Structure (Bronze/Silver/Gold)

⇗ GCP Dataplex — Medallion Zones ⇗ Azure — Medallion Architecture

⇗ AWS — Data Lake Layered Architecture

⬡ BENEFITS ✔ ADOPT WHEN

✦ Separation of concerns — each layer has one ✔ Building on a cloud lakehouse platform
job; quality issues at one layer don't cascade (Databricks, BigQuery, Snowflake, Azure Synapse)
forward
✔ Multiple teams consume data at different quality
✦ Full data lineage preserved — Bronze is levels (engineers need raw; analysts need clean)
immutable so you can always replay from raw
✔ Data lineage, replay, and auditability are
✦ Incremental transformations are simpler — each requirements — Bronze immutability provides this
layer only needs to process changes from the
✔ dbt is the transformation tool — Medallion maps
layer below
directly to staging / intermediate / mart model
✦ Multiple consumers can access the appropriate layers
layer for their use case without competing on one
✔ Default recommendation for all new cloud DW /
table
lakehouse builds — prefer this over ad-hoc schema
✦ Aligns naturally with dbt project structure organisation
(staging → intermediate → marts)

Hierarchy
CHAPTER 07

Patterns

Dimensions almost always contain hierarchies — date has Year→Quarter→Month→Day;

product has Department→Category→SubCategory. How you model them affects rollup
performance and query complexity.

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 92 / 126

 Fixed-Depth Hierarchy Most Common
HIERARCHY · 7.1

Description: A hierarchy with a known, fixed number of levels. Each level is stored as a
separate column in a single denormalized dimension table. Simple, fast, and the default
approach for most hierarchies in a star schema.

📅
Use Case: DimDate — Year/Quarter/Month/DayEvery date dimension has exactly 4
levels. Store all four as columns in one row. Rollups are trivial GROUP BY queries.

DIMDATE — ALL HIERARCHY LEVELS AS COLUMNS

DATE_KEY FULL_DATE DAY_NUM MONTH_NAME QUARTER YEAR FISCAL_YEAR

20240301 2024-03-01 Friday March Q1 2024 FY2024

20240302 2024-03-02 Saturday March Q1 2024 FY2024

◈ FIXED-DEPTH TREE

Year: 2024

Quarter: Q1

Month: March

Day: 2024-03-01

Day: 2024-03-02

Day: …

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 93 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

RETAIL / UNIVERSAL RETAIL / FMCG

DimDate Calendar Hierarchy HUL / ITC Product Category Hierarchy

Year → Quarter → Month → Week → Day is the Department → Category → Subcategory →
universal fixed-depth date hierarchy. Every Brand → SKU — exactly 5 levels, all products
DW pre-loads all levels as columns in fill all 5. DimProduct stores dept_name,
DimDate. Power BI, Tableau, and Looker all category_name, subcategory_name,
create automatic drill-down hierarchies from brand_name, sku_name as direct columns.
these columns. "Year 2024 → Q3 2024 → July "Revenue by category" = GROUP BY
2024 → Week 28 → July 12" is a 0-config drill category_name. No hierarchy navigation
path. query needed — all levels are direct
attributes.
Fixed-depth hierarchies in BI tools: drag
year/quarter/month/day into a hierarchy Fixed-depth product hierarchy is the most
definition — drill-down is automatic common hierarchy pattern in retail —
design it as columns, not a recursive
table
⇗ Kimball Calendar Date Dimension

⇗ dbt — Date Dimension Build ⇗ Kimball Fixed-Depth Hierarchy

⇗ Power BI Date Hierarchy ⇗ GS1 Product Hierarchy Standard

⇗ Hierarchy Design in Power BI

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Fixed-Depth Hierarchies ⇗ Microsoft — Hierarchies in Tabular Models

⇗ dbt — Building Dimension Hierarchies ⇗ BigQuery — Date Hierarchy Columns

⬡ BENEFITS ✔ ADOPT WHEN

✦ All hierarchy levels available as direct columns ✔ Hierarchy has a known, stable number of levels
— no recursive queries or path traversal needed that never varies across members

✦ GROUP BY at any level is trivial — year, quarter, ✔ All members fill all levels consistently — no
month, day are all simple column references skipping or ragged depth

✦ BI tools handle fixed-depth hierarchies natively ✔ This is the default choice for Date, Geography
— drill-down just works out of the box (Country→Region→City), and Product hierarchies

✦ Simplest possible storage: one row per leaf- ✔ Use this pattern unless the hierarchy is genuinely
level member with all ancestor levels variable-depth or self-referencing
denormalised

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 94 / 126

 Ragged / Variable-Depth Hierarchy Variable Levels
HIERARCHY · 7.2

Description: A hierarchy where not all branches have the same depth. Some members skip
levels. For example, a geographic hierarchy where "United States → California → San
Francisco" has 3 levels, but "Vatican City" (a country with no regions or cities) has just 1 level.
NULL padding is the common solution.

🌍
Use Case: Global GeographyLarge countries have Country→Region→City. Small
countries may have Country→City (no region). Some territories skip directly from
Country to Outlet. NULL padding fills missing levels.

DIMGEOGRAPHY — RAGGED HIERARCHY WITH NULL PADDING

GEO_KEY COUNTRY REGION CITY DEPTH

G-01 USA California San Francisco 3

G-02 USA Texas Austin 3

G-10 Vatican City NULL NULL 1

G-11 Singapore NULL Singapore 2

NULL handling: Queries must handle NULL regions gracefully. COALESCE(region,

country) is a common trick to roll small countries directly to country-level in GROUP BY.

◈ REAL-WORLD PROJECT EXAMPLES

GLOBAL RETAIL TELECOM / GOVERNMENT

Walmart / Tesco Global Store Airtel / BSNL Network Hierarchy

Geography Circle → (optional Cluster) → District → Town →
Country → (optional Region) → State → City → Cell Tower. Metro circles (Mumbai, Delhi) have
Store. Small countries (Singapore, Cluster level; smaller circles don't. DimTower
Luxembourg) have no Region level — Region has NULL cluster_name for non-metro towers.
is NULL. Large countries (India: Country → "Coverage by cluster" uses WHERE
Region → State → City → Store) have all 5 cluster_name IS NOT NULL. Network
levels. NULL-padded hierarchy with performance reports handle the ragged depth
COALESCE(region, country) for rollup queries. with COALESCE and IS NULL filters.
GROUP BY COALESCE(region, country)
correctly handles both. Ragged telecom hierarchy reflects India's
uneven geography — metro vs. rural
Ragged geography is the most common real- network structures are fundamentally
world hierarchy — no country's different depths
administrative structure maps to a
perfect 4-level tree
⇗ Kimball Ragged Hierarchy

⇗ Kimball Ragged Hierarchy ⇗ Telecom Analytics (GCP)

⇗ BigQuery Geospatial Analytics ⇗ Network Topology Analytics (Medium)

⇗ SSAS Ragged Hierarchies

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 95 / 126

 ⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Ragged Hierarchies ⇗ Microsoft — Ragged Hierarchies in SSAS

⇗ Medium — Handling Ragged Hierarchies

⬡ BENEFITS ✔ ADOPT WHEN

✦ Accurately models real-world geographies and ✔ The real-world hierarchy genuinely has variable
org structures that don't fit neat uniform levels depth across members (global geography, complex
org charts)
✦ NULL padding keeps the schema consistent —
same column structure for all members ✔ The maximum depth is still known and bounded
— NULL padding is feasible
✦ COALESCE tricks allow rollup queries to still
work correctly across varying depths ✔ Analytical teams understand how to handle
NULLs in rollup queries

✔ The alternative (forcing all members to the same

depth with placeholder nodes) would be misleading
or unmanageable

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 96 / 126

 Recursive / Parent-Child Hierarchy Self-Referencing
HIERARCHY · 7.3

Description: The table references itself — each row has a parent_key pointing to another row
in the same table. Maximum flexibility for arbitrary-depth trees, but requires recursive CTEs or
materialized path structures for efficient querying. Used for org charts, bill of materials,
category trees.

🏢
Use Case: Organizational HierarchyAn org chart where every employee has a
manager. The CEO has no manager (NULL parent). The depth can vary across branches.

DIMEMPLOYEE — PARENT-CHILD RESULTING TREE

STRUCTURE
EMP_KEY NAME TITLE MANAGER_KEY (FK→SELF)

E-1 Maria CEO NULL

E-1: Maria Chen (CEO)
Chen
E-2: Tom Park (VP
E-2 Tom VP E-1
Park Sales Sales)

E-3 Alice Sales E-2 E-3: Alice

Wang Mgr Wang (Mgr)

E-4 Bob Sales E-3 E-4: Bob

Roy Rep
Roy (Rep)

BigQuery/Snowflake: Use recursive CTEs ( WITH RECURSIVE ) to walk the tree. Or

pre-materialize a closure table for faster rollups.

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 97 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

HR / CONSULTING MANUFACTURING

TCS / Cognizant Org Chart (100,000 Tata Steel / JSW Steel Bill of Materials
employees) A finished product (e.g. automotive steel coil)
DimEmployee has emp_key and manager_key has a BOM 8 levels deep: Finished Product →
(FK to same DimEmployee table). The org Sub-Assembly → Component → Sub-
chart is 10+ levels deep and restructures Component → Raw Material → Chemical
quarterly. WITH RECURSIVE CTE traverses the Composition. parent_part_key in DimPart
tree: "Find all employees under BU Head X" enables recursive BOM explosion: "total raw
walks the parent_key chain to unlimited depth. material cost for product X" traverses the full
Employee count by subtree, budget rollups, tree recursively to leaf nodes.
and reporting-line analysis all use recursive
CTE. BOM explosion via recursive CTE is the
canonical manufacturing use case —
Recursive CTEs on 100K-node trees hierarchy depth varies per product so
complete in <3 seconds in BigQuery/ recursive dim is the only option
Snowflake; pre-materialise closure table
for sub-second performance
⇗ Kimball Recursive Parent-Child

⇗ Kimball Recursive Parent-Child ⇗ SAP PP BOM Structure

⇗ BigQuery Recursive CTEs ⇗ BOM Explosion SQL (Medium)

⇗ Snowflake Hierarchical Queries

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Recursive Parent-Child Hierarchy ⇗ BigQuery — Recursive CTEs

⇗ Snowflake — Recursive CTEs for Hierarchies ⇗ SQL Server — Hierarchical Data & CTEs

⬡ BENEFITS ✔ ADOPT WHEN

✦ Handles truly arbitrary-depth hierarchies ✔ Hierarchy depth is genuinely unknown or

without any schema changes unlimited (org chart that can grow N levels deep)

✦ Compact storage — just two columns (member ✔ The hierarchy structure changes frequently —
key + parent key) per row regardless of tree parent reassignments, new levels added
depth
✔ The query engine supports recursive CTEs well
✦ Adding new hierarchy members is trivial — just (Snowflake, BigQuery, PostgreSQL, SQL Server)
insert a row with the correct parent key
✔ If query performance is critical, combine with a
✦ Naturally models real-world org charts, BOM closure table for fast tree traversal
structures, and folder hierarchies
✔ Examples: employee org chart, bill of materials,
product category tree, folder structures

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 98 / 126

 Closure Table / Pathstring Fast Tree Traversal
HIERARCHY · 7.4

Description: A materialized table that stores every ancestor-descendant pair for all nodes in
the hierarchy, along with the depth of the relationship. Pre-computes all paths so tree
traversal requires no recursion at query time — just a simple join.

🌳
Use Case: Product Category RollupPre-materialize all ancestor-descendant pairs so
you can answer "Give me sales for all products under the Electronics department" with
a simple JOIN — no recursive CTE needed.

DIMCATEGORY (SOURCE) BRIDGECATEGORYPATH (CLOSURE)

CAT_KEY NAME PARENT_KEY ANCESTOR DESCENDANT DEPTH

1 Electronics NULL 1 1 0

2 Phones 1 1 2 1

3 Smartphones 2 1 3 2

2 2 0

2 3 1

3 3 0

Query: "All products under Electronics (cat_key=1):" JOIN BridgeCategoryPath ON

ancestor=1 — returns Phones and Smartphones without any recursion.

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 99 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

E-COMMERCE HR / ORG ANALYTICS

Amazon / Flipkart Product Category Wipro / HCL Org Chart Subtotals for
Taxonomy Dashboards
Electronics → Computers → Laptops → Gaming Executive dashboard shows "total headcount
Laptops is 4 levels, but Electronics → and revenue under each VP" — recalculated
Accessories → Cables is 3 levels. Closure nightly for 500 VPs. Recursive CTE takes 45
table: one row per ancestor-descendant pair seconds. Closure table (pre-materialised
per level. "All products under Electronics" = nightly): "headcount under VP X" = simple
JOIN BridgeCategoryPath WHERE ancestor_key SUM with closure join, 0.3 seconds. Dashboard
= ELECTRONICS_KEY — no recursion, no CTE. becomes real-time interactive instead of 45-
Executes as a simple hash join in BigQuery. second batch refresh.

Closure table on 50,000 product Closure table is the performance

categories: ~500,000 closure rows. optimisation layer on top of a recursive
"Subtotal by category" queries: 10ms vs 2 dim — build recursive dim first, add
seconds with recursive CTE on same data closure table when query latency becomes
unacceptable

⇗ Martin Fowler — Closure Table

⇗ Martin Fowler — Closure Table

⇗ BigQuery Pre-Materialised Hierarchy

⇗ dbt Materialised Hierarchy Models

⇗ Closure Table vs CTE (Medium)

⇗ BigQuery Materialised Views

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Martin Fowler — Closure Table Pattern ⇗ Wikipedia — Closure Table

⇗ Medium — Closure Table vs Recursive CTE

⇗ BigQuery — Pre-Materialised Hierarchy Tables

⬡ BENEFITS ✔ ADOPT WHEN

✦ All ancestor-descendant relationships are pre- ✔ Tree traversal queries are frequent and
materialised — tree traversal is a simple JOIN, no performance is critical (product category rollup, org
recursion chart subtotals)

✦ Subtotal queries ("all sales under Electronics") ✔ The SQL engine does not support recursive CTEs,
are extremely fast even on large trees or recursive query performance is unacceptable

✦ Depth column enables level-specific filtering ✔ The hierarchy changes infrequently enough that
("show only direct children" vs "all descendants") pre-materialising the closure table is manageable

✦ Works on any SQL engine — no recursive CTE ✔ Combine with the parent-child recursive dim —
support required closure table is the performance layer on top of it

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 100 / 126

 Grain
CHAPTER 08

Concepts

Grain is the single most important design decision in dimensional modeling. "What does one
row in this fact table represent?" must be answered precisely before any other design
decisions are made.

Atomic Grain Always Recommended

GRAIN · 8.1

Description: The lowest possible level of detail that an operational process produces. One
row = one scan at checkout, one click, one payment line. Atomic grain tables can answer ANY
question at any level of rollup. The Kimball methodology strongly recommends always
building the atomic grain first.

⚛️
Example grain declaration: "One row represents one line item on one sales
transaction at one store on one day for one customer." — This is atomic. Any coarser
grain (one row per day per store) loses the ability to drill into individual transactions.

GRAIN STATEMENT → TABLE DESIGN

GRAIN ELEMENT DIMENSION FK COLUMN

one line item Degenerate (line_num) line_num (DD)

one sales transaction Degenerate txn_id (DD)

at one store DimStore store_key FK

on one day DimDate date_key FK

for one customer DimCustomer customer_key FK

for one product DimProduct product_key FK

Kimball's Rule: Declare the grain first. Then identify all dimensions that are
meaningful at that grain. Then identify all facts that exist at that grain. Any fact that
doesn't exist at atomic grain belongs in a different fact table.

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 101 / 126

 Aggregate Grain & Grain Conflict Handle With Care
GRAIN · 8.2

Aggregate Grain: Pre-summarized fact tables at a higher level of granularity — one row per
product per month instead of per transaction. Used for performance optimization when
common queries always roll up to a certain level. Aggregate tables should be in addition to,
not instead of, the atomic fact table.

Grain Conflict: The most common and dangerous mistake in dimensional modeling — mixing
rows of different granularity in the same fact table. This causes double-counting and incorrect
aggregations.

GRAIN CONFLICT EXAMPLE — BAD DESIGN

DATE_KEY PRODUCT_KEY STORE_KEY CUSTOMER_KEY REVENUE ⚠️ PROBLEM

20240301 P-042 ST-07 C-8821 38.97 ← individual

transaction

20240301 P-042 ST-07 NULL 152.40 ← daily store

total! WRONG GRAIN

Grain Conflict Danger: SUM(revenue) on this table would double-count P-042 sales
at ST-07 on 2024-03-01. The daily total (152.40) and individual transactions already
sum to more than actual sales. Always put different grains in separate fact tables.

CORRECT PATTERN — TWO SEPARATE FACT TABLES

FactSales_Atomic (transaction grain) FactSales_Daily (day × product × store grain)

DATE_KEY PRODUCT_KEY CUST_KEY REVENUE DATE_KEY PRODUCT_KEY STORE_KEY TOTAL_REV

20240301 P-042 C-8821 38.97 20240301 P-042 ST-07 152.40

20240301 P-042 C-4401 17.00

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 102 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

RETAIL HEALTHCARE

DMart / Big Bazaar POS Transaction NIMHANS / AIIMS Hospital Procedure

Grain Grain
Grain declared: "one row per scanned product Grain: "one row per clinical procedure
per customer receipt per store per day." performed per patient per admission episode."
Consequence: date, product, store, customer, Valid dimensions: date, patient, doctor,
promotion, cashier are all valid dimensions. procedure, ward, diagnosis. Valid facts:
Unit_price, quantity, discount, revenue are procedure_duration_minutes, cost,
valid facts. Attempting to add "monthly materials_used. "Total cost per admission" =
target" to this fact table is a grain conflict — SUM across procedure rows for that
monthly target belongs in a separate admission_key. Reporting at daily-patient level
FactSalesTarget. requires a separate aggregate fact.

Grain declaration is the first line of Declaring procedure-level grain prevents

the DW design document: written in plain the most common healthcare DW mistake:
English before any SQL is designed mixing admission-level and procedure-
level measures in one fact table

⇗ Kimball — Declaring the Grain

⇗ Kimball — Declaring the Grain

⇗ dbt — Grain & Granularity in Marts

⇗ FHIR Clinical Resource Grain

⇗ Grain Declaration (Medium)

⇗ Healthcare DW Grain Design

◈ REAL-WORLD PROJECT EXAMPLES

RETAIL FINANCE

Reliance Retail Monthly Store HDFC / ICICI Annual P&L Rollup

Performance CFO dashboard requires FY revenue and cost
Atomic FactSales: 500M rows/year. by business unit — 24 rows in FactAnnualPL.
Management asks for "monthly revenue by Without aggregate: scan 2B transaction rows
store by category" — same query runs 5,000 nightly for a 24-row result. With FactAnnualPL
times/day. Build FactSalesMonthlySummary (aggregate): 24-row scan. Grain conflict
(aggregate): month × store × category grain, example: accidentally including "average
2.4M rows. Looker's PDT routes all monthly interest rate" (non-additive) in the aggregate
category queries to the aggregate table — it should be computed from additive
automatically. Query time: 47s → 0.4s. components (total_interest / total_principal),
BigQuery cost: ₹8,000/day → ₹300/day. never stored as a pre-aggregated value.

Aggregate fact table ROI calculation: Grain conflict anti-pattern caught in

2.4M rows vs 500M rows scanned → 99.5% review: FactAnnualPL included
cost reduction on BigQuery bytes- avg_interest_rate as a pre-computed
processed billing column — removed and replaced with two
additive components

⇗ Kimball Aggregated Fact Tables

⇗ Kimball Aggregated Fact Tables

⇗ BigQuery Materialised Views

⇗ dbt Materialised Aggregate Models

⇗ Looker PDT Documentation

⇗ Grain Conflict Anti-Patterns

⇗ REFERENCE DOCUMENTATION & FURTHER READING

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 103 / 126

 ⇗ Kimball — Declaring the Grain ⇗ dbt — Grain and Granularity in Marts

⇗ Medium — Why Grain Declaration Matters

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Aggregated Fact Tables ⇗ BigQuery — Materialised Views for Aggregates

⇗ dbt — Materialised Models & Aggregates ⇗ Snowflake — Dynamic Tables for Aggregation

⬡ BENEFITS OF AGGREGATE GRAIN ✔ ADOPT WHEN — AND WHAT TO AVOID

(WHEN DONE RIGHT)
✔ Build aggregate fact tables only after the atomic
✦ Dramatically faster query performance for grain table exists and query patterns are
common rollup queries — no need to re- understood
aggregate millions of rows
✔ A specific rollup query (daily × product × store)
✦ Reduces compute cost on cloud DW platforms runs frequently and is a known performance
where queries are billed by bytes scanned bottleneck

✦ Transparent to BI tools — aggregate navigation ✔ Never mix grains in a single fact table — if rows
can be handled automatically by the semantic represent different things, separate them into
layer different tables

✔ Always document the grain declaration explicitly

in the table metadata or data dictionary

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 104 / 126

 Aggregate Awareness & Aggregate Query Performance Pattern
Navigation
GRAIN CONCEPT · 8.3

Description: Aggregate awareness is the ability of a BI tool or semantic layer to

transparently route a query to a pre-built aggregate fact table when the query's grain
matches the aggregate, instead of scanning the full atomic fact table. The end user writes one
query — the system automatically chooses the most efficient table. Requires maintaining
multiple fact tables at different grains, all consistent with the same atomic base.

⚡
Use Case: Monthly Sales ReportFactSales (atomic) has 500M rows at transaction
grain. A monthly summary report scans all 500M rows every time to GROUP BY month.
With aggregate awareness: FactSalesMonthlySummary (1.2M rows at
month×product×store grain) is used instead. Query time: 45s → 0.3s.

THE AGGREGATE TABLE HIERARCHY

FactSalesTransaction — one row per sale line item ·

MOST GRANULAR
500,000,000 rows · Used for drill-through only

FactSalesDaily — one row per day × product × store

DAILY AGGREGATE
· 18,000,000 rows · Used for daily trend reports

FactSalesMonthly — one row per month × product

MONTHLY AGGREGATE category × region · 1,200,000 rows · Used for
monthly dashboards

FactSalesAnnual — one row per year × business unit

ANNUAL AGGREGATE
· 24,000 rows · Used for executive YoY summary

HOW AGGREGATE NAVIGATION WORKS

USER QUERY DIMENSIONS REQUESTED SYSTEM ROUTES TO ROWS SCANNED

Order details for Transaction, Customer, FactSalesTransaction ~500M

customer #1234 Product (filtered)

Daily sales by Day, Store, Product FactSalesDaily ~500K

store this week

Monthly revenue Month, Product Category FactSalesMonthly ✓ ~50K

by product
category

Annual revenue by Year, Region FactSalesAnnual ✓ ~200

region

Semantic layer / BI tool support: Aggregate navigation is supported natively by

Looker (PDT + explore), dbt (materialised views + semantic layer), Power BI

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 105 / 126

 (aggregations), Tableau (extract acceleration), and BigQuery BI Engine. The end user
never sees the routing — they just get fast results.

Maintenance discipline: Aggregate tables must be kept consistent with the atomic
base. If the atomic fact is updated or backdated, all affected aggregate tables must be
reprocessed. Document dependencies explicitly in your data lineage tooling (Dataplex,
dbt docs, Collibra).

◈ REAL-WORLD PROJECT EXAMPLES

RETAIL / CLOUD FINANCE / BANKING

Tata Retail / Shoppers Stop on Looker Axis Bank / Yes Bank Regulatory
Three aggregate tables registered in the Reporting Aggregates
Looker semantic layer: FactSalesDaily (18M Daily regulatory reports (LCR, NSFR, CRR)
rows), FactSalesMonthly (1.2M rows), require specific aggregate grains:
FactSalesAnnual (50K rows). Looker's FactDailyLiquidityPosition (product × currency
aggregate awareness routes "YTD revenue by × maturity_band grain). Pre-materialised as a
region" to FactSalesAnnual automatically. BigQuery scheduled query from atomic
"This week's daily trend" goes to FactTransaction. RBI report generation: 0.8
FactSalesDaily. The analyst writes one Looker seconds from the aggregate vs 38 seconds
Explore query — routing is transparent. from atomic fact. Materialised view
invalidation triggers reprocessing when atomic
Looker aggregate awareness configuration: fact changes.
10 lines of YAML per aggregate table in
the LookML model; zero SQL changes BigQuery materialised views with
incremental refresh provide aggregate
awareness transparently for SQL-based
⇗ Kimball Aggregate Navigation
reporting tools

⇗ Looker Aggregate Awareness Docs

⇗ Kimball Aggregate Navigation

⇗ dbt Semantic Layer

⇗ RBI Liquidity Coverage Ratio

⇗ BigQuery Scheduled Queries

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Aggregate Navigation ⇗ BigQuery BI Engine — Aggregate Awareness

⇗ Databricks — Lakehouse Optimisation & Caching

⇗ dbt Semantic Layer — Aggregate Navigation ⇗ Power BI — Aggregation Tables

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 106 / 126

 ⬡ BENEFITS ✔ ADOPT WHEN

✦ Dramatic query performance improvement for ✔ Specific high-frequency report queries are
common rollup reports — orders of magnitude measurably slow on the atomic fact table
faster
✔ The rollup grain (daily, monthly, yearly) is stable
✦ Reduces cloud compute cost — smaller scans and well-understood — not ad-hoc
mean less bytes processed = lower bill on
✔ The BI or semantic layer supports aggregate
BigQuery/Snowflake
navigation (dbt semantic layer, Looker, Power BI
✦ Transparent to end users — they write one aggregations)
query; the semantic layer routes to the best table
✔ Always build on top of the atomic fact table —
✦ Atomic fact table remains the single source of never replace it with the aggregate
truth — aggregates are always derivable
✔ Monitor aggregate usage and drop any aggregate
that is never used (maintenance cost not justified)

Key
CHAPTER 09

Strategies

The technical plumbing of dimensional models. Key strategy choices affect SCD correctness,
join performance, pipeline complexity, and data quality.

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 107 / 126

 Surrogate Key vs. Natural Key Foundation
KEY STRATEGY · 9.1

SURROGATE KEY (SK) NATURAL KEY (NK) /

BUSINESS KEY
A system-generated, meaningless integer or
hash assigned by the DW. Has no business The identifier that exists in the source
meaning. Decoupled from source system operational system. Has real business
identifiers. Required for SCD Type 2 to work meaning. Cannot be used as the DW PK in
correctly — each version of a dimension row SCD Type 2 because multiple rows for the
gets a unique SK. same entity would share the same NK.

Example: emp_key = 5099 (auto- Example: emp_natural_key =

incremented by DW). Means nothing "EMP-001" (from HR system). Both the
to the business. Two rows for the same pre-promotion and post-promotion
employee (pre and post-promotion) rows share this NK. Use NK for joining
have different SKs: 5099, 5100. back to source; use SK for joining to
facts.

KEY COMPARISON IN DIMEMPLOYEE

EMP_KEY (SK — DW GENERATED) EMP_NATURAL_KEY (NK — FROM HR SYSTEM) NAME TITLE IS_CURRENT

E-301 EMP-001 Alice Analyst N

Wang

E-402 EMP-001 Alice Senior Y

Wang Analyst

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 108 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

E-COMMERCE HR

Flipkart / Amazon DimProduct Infosys / TCS Global Employee Master

Surrogate Keys An employee's "natural key" should be
Source system product IDs include: SAP employee ID. But TCS has 8 geographic
material number (12-digit), Marketplace SKU employee ID systems (India, USA, UK, etc.)
code (alphanumeric), and Seller-assigned that overlap. DimEmployee uses a synthetic
codes. All three can change over the product integer surrogate key. All fact tables join on
lifecycle. DimProduct assigns an integer emp_key. Natural keys from all 8 systems are
surrogate key (product_key) at first load. All stored as attributes. When an employee
fact tables use product_key. Natural keys from transfers from India to USA and gets a new
all three source systems are stored separately regional ID, only one DimEmployee row is
for reconciliation — never used as FKs. updated — all historical fact rows are
unaffected.
Real case: a product's SAP material
number was renumbered during a system Multi-source employee systems are the
migration; surrogate key protected all primary justification for surrogate keys
200M historical fact rows from breaking in HR dimensions

⇗ Kimball Surrogate Key Technique ⇗ Kimball Surrogate Key Technique

⇗ dbt Utils — generate_surrogate_key ⇗ Workday Global HCM

⇗ BigQuery Auto-Increment Keys ⇗ Multi-Source Key Resolution (Medium)

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Surrogate Key Technique ⇗ dbt — Surrogate Keys (dbt_utils)

⇗ BigQuery — Auto-Increment & Generated Columns

⇗ Microsoft — Surrogate Keys in Data Warehouses

⬡ BENEFITS OF SURROGATE KEYS ✔ ADOPT WHEN

✦ Enables SCD Type 2 — each version of a ✔ Always use surrogate keys as the PK for all
dimension row gets its own unique SK dimension tables — this is the Kimball standard

✦ Decouples the DW from source system key ✔ SCD Type 2 is in use — surrogate keys are
changes — if the source renames a key, the DW is mandatory to version dimension rows
unaffected
✔ Source system natural keys are unstable, reused,
✦ Integer PKs provide faster joins than string- or composite (multi-column)
based natural keys
✔ Retain natural keys as a separate column for
✦ Protects against null, duplicate, or reused joining back to source systems and for human
natural keys from source systems readability

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 109 / 126

 Durable Supernatural Key SCD Type 6 / 7
KEY STRATEGY · 9.2

Description: A DW-assigned surrogate key that is stable across all Type 2 versions of a
dimension row. Unlike the regular surrogate key (which changes per version), the durable key
stays constant across all rows for the same real-world entity. Used in SCD Type 7 as the
"current view" FK in the fact table.

THREE KEY TYPES FOR THE SAME EMPLOYEE

EMP_KEY (SK) DURABLE_KEY NATURAL_KEY TITLE IS_CURRENT

E-301 D-55 EMP-001 Analyst N

E-402 D-55 EMP-001 Senior Analyst Y

emp_key E-301 → point-in-time historical join (2022 sale)

durable_key D-55 → current-view join (always latest row)

◈ REAL-WORLD PROJECT EXAMPLES

BANKING HR / CONSULTING

ICICI / Kotak Customer Identity Across Wipro / Accenture Employee Re-hire

Mergers Pattern
Kotak acquires ING Vysya Bank. ING Vysya An employee leaves Wipro (emp_nat_key =
customers are merged into Kotak's "EMP-4421", row expires). Three years later
DimCustomer with SCD Type 2. Each customer they rejoin (new emp_nat_key = "EMP-8812"
gets a durable_key assigned at first — different ID from HR system). Durable key
appearance (regardless of which bank). When D-1042 is recognised from the old natural key
ING customers transfer, their SCD2 versioning in a matching table and reused. All historical
creates new rows — but the durable_key stays performance data from first tenure is linked.
the same. "Customer lifetime value across the "Total tenure" aggregates both tenures via
merger" groups all rows by durable_key. durable_key.

Durable key survives corporate mergers, Durable key is critical for SCD Type 6
system migrations, and natural key and Type 7 architectures — without it,
reassignments — assigned once, never re-hires or re-registered customers look
changed like new entities

⇗ Kimball Durable Supernatural Key ⇗ Kimball Durable Supernatural Key

⇗ M&A Data Integration Patterns ⇗ HR Rehire Pattern (Medium)

⇗ Durable Key in SCD 6 & 7 ⇗ dbt Persistent Surrogate Keys

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Durable Supernatural Key ⇗ Medium — Durable Key in SCD Type 6 & 7

⇗ dbt — Persistent Surrogate Keys

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 110 / 126

 ⬡ BENEFITS ✔ ADOPT WHEN

✦ Single stable key that identifies an entity across ✔ SCD Type 6 or Type 7 is in use and current-view
all its Type 2 historical versions access from historical fact rows is required

✦ Enables "current view" lookups without ✔ The natural key is unstable (can change over
scanning all historical rows or filtering on time) but entity continuity must still be tracked
is_current
✔ Analysts need to group all historical versions of
✦ Used in SCD Type 7 as the dual FK — fact table an entity under a single stable identifier
can access both historical and current views
✔ Always assign the durable key at the entity's first
simultaneously
appearance and never change it
✦ Simplifies "who is this entity today?" queries
without date-range logic

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 111 / 126

 Hash Key (SHA-256 / MD5) Data Vault + Delta Detection
KEY STRATEGY · 9.3

Description: A cryptographic hash of the business key (or a combination of columns for
change detection). Used in two contexts: (1) Data Vault — hash keys replace integer
sequences, enabling parallel loads without sequence contention. (2) Delta detection — a hash
of all tracked columns creates a single fingerprint; if it changes, the row has changed.

🔐
Use Case 1 — Data Vault Hub Key: SHA256(customer_id) → deterministic,
reproducible, parallelizable. Multiple pipelines can compute the same hash
independently without a central sequence generator.

🔍
Use Case 2 — SCD Delta Detection: Hash all tracked columns per row. Compare
incoming hash to stored hash. If different → change detected → SCD update triggered.
Used heavily in dbt and BigQuery MERGE patterns.

DELTA DETECTION PATTERN

EMP_KEY NAME TITLE CITY ROW_HASH (SHA256 OF ALL COLS) CHANGE?

EMP-001 Alice Analyst Boston a3f4e2b1… stored

Wang

EMP-001 Alice Senior Boston 9c71a043… CHANGED →

Wang Analyst SCD
trigger

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 112 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

GCP / BIGQUERY INSURANCE / DATA VAULT

GCP-Based Delta Ingestion Framework Bajaj Allianz / New India Data Vault 2.0
In a BigQuery delta ingestion pipeline, each In Data Vault, Hub_Policy has policy_hk =
row arriving from source has a row_hash = SHA-256(source_system || "||" ||
SHA-256(col1 || col2 || … || colN) computed in policy_natural_key). Multiple source systems
Dataflow. MERGE INTO target: WHEN can compute the same hash independently —
MATCHED AND row_hash != target.row_hash no coordination, no sequence generator, no
THEN UPDATE, WHEN NOT MATCHED THEN contention. 12 parallel Dataflow pipelines all
INSERT. 200M row comparison in 4 minutes compute and insert to Hub_Policy
using only hash column comparison — no 40- simultaneously; identical hashes naturally
column CASE WHEN logic. deduplicate via MERGE.

Hash-based change detection eliminates Data Vault hash key is the enabler of
column-by-column comparison: 1 string fully parallelised DW loads — it
comparison per row vs N column eliminates the global sequence generator
comparisons; 3× faster MERGE on BigQuery bottleneck that serialises pipeline
execution

⇗ BigQuery — SHA256 Functions

⇗ Data Vault 2.0 — Dan Linstedt

⇗ Dataflow — MERGE & CDC Patterns

⇗ dbt Data Vault Blog

⇗ dbt Utils — surrogate_key

⇗ Hash Keys in Data Vault (Medium)

⇗ GCP CDC Architecture Guide

⇗ AutomateDV dbt Package

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Databricks — Data Vault Hash Keys ⇗ dbt Utils — Hash Surrogate Key Macro

⇗ BigQuery — SHA256 & MD5 Functions ⇗ Medium — Hash Keys in Data Vault 2.0

⬡ BENEFITS ✔ ADOPT WHEN

✦ Deterministic and reproducible — the same ✔ Data Vault 2.0 architecture — hash keys are
natural key always produces the same hash, mandatory for Hub, Link, and Satellite design
enabling parallel loads
✔ High-volume parallel ingestion pipelines where
✦ Data Vault: eliminates sequence generator sequential surrogate key generation is a bottleneck
contention — multiple pipelines compute identical
✔ SCD change detection in dbt or BigQuery MERGE
hashes independently
patterns — hash all tracked columns into one
✦ Delta detection: hashing all tracked columns row_hash
into a single fingerprint makes change detection
✔ Use SHA-256 for collision resistance in large
a simple string comparison
datasets; MD5 is acceptable for smaller datasets
✦ Compact change indicator — one hash column with lower risk
replaces N individual column comparisons in
MERGE logic

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 113 / 126

 Foundational planning frameworks,
Architecture query patterns, production
CHAPTER 10 & Design engineering standards, and decision
aids that tie the entire discipline
Patterns together.

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 114 / 126

 Kimball Bus Architecture & Enterprise Enterprise DW Foundation
DW Bus Matrix
ARCHITECTURE PATTERN · 10.1

Description: The Kimball Bus Architecture is the enterprise-level planning framework that
defines how multiple data marts are designed to interoperate. Its central tool is the
Enterprise DW Bus Matrix — a planning document where rows are business processes
(subject areas) and columns are candidate dimensions. A cell is marked if that dimension is
used by that business process. Shared (conformed) dimensions appear in multiple rows,
forming the "bus" that connects all marts together.

🏗️
Why it matters: The Bus Matrix is built before any physical tables are created. It
reveals which dimensions must be conformed across marts, which subject areas share
grain, and what the incremental build order should be. It is the single most important
architectural planning artefact in Kimball methodology.

ENTERPRISE DW BUS MATRIX — EXAMPLE

BUSINESS PROCESS 📅 DATE 👤 CUSTOMER 📦 PRODUCT 🏪 STORE 🏭 VENDOR 💳 PROMO 👔

Retail Sales ✦ ✦ ✦ ✦ ✦

Inventory ✦ ✦ ✦ ✦

Procurement ✦ ✦ ✦

HR / Payroll ✦ ✦

Marketing Campaigns ✦ ✦ ✦ ✦

Conformed dims → 5 3 processes 4 4 2 proc 2 proc

processes processes processes

Reading the matrix: Date, Product, and Store appear across 4+ business processes
→ these must be conformed dimensions, built first, owned by the enterprise DW team.
Customer is shared by Sales and Marketing → must also be conformed. Vendor is only
in Inventory and Procurement → can be scoped to those marts initially.

Build order: Start with the subject area that has the most stakeholder value AND the
most shared dimensions. Typically: Retail Sales first (most conformed dims), then reuse
those conformed dims for Inventory. Each new mart should reuse existing conformed
dims wherever possible.

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 115 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

ENTERPRISE RETAIL FINANCIAL SERVICES

Reliance Industries / Future Group HDFC / Axis Bank DW Bus Matrix

Enterprise DW Subject areas: Retail Banking, Corporate
Bus Matrix rows: Retail Sales, Grocery, Banking, Wealth Management, Insurance, HR,
Fashion, Electronics, Loyalty Program, Supply Risk Management (6 rows). Shared dims: Date
Chain, HR (7 subject areas). Bus Matrix (6), Branch (5), Customer (4), Employee (3),
columns: Date, Customer, Product, Store, Product (3). Matrix decision: DimCustomer
Employee, Supplier, Promotion, Channel (8 needs conformance between Retail
candidate dims). Matrix reveals: Date and (individual) and Corporate (entity) — create
Product appear in 6 subject areas → build first, DimIndividualCustomer and
own centrally. Customer appears in 4 → DimCorporateEntity as separate conformed
conform across Retail, Grocery, Fashion, dims. Wealth Management shares
Loyalty. Supply Chain and HR share only Date DimIndividualCustomer with Retail but not
and Store → can be built independently. Corporate.

Bus Matrix review meeting: 3 hours with Financial services bus matrix: customer
business owners identified 3 candidate entity disambiguation is the hardest
"conformed" dims that were actually conformance problem — individual vs
different entities — saved 6 months of corporate vs SME are fundamentally
integration rework different entities

⇗ Kimball Enterprise Bus Matrix ⇗ Kimball Enterprise Bus Matrix

⇗ Bus Architecture Explained (Medium) ⇗ Banking Data Warehouse Architecture

⇗ GCP Enterprise DW Reference Arch ⇗ dbt Project Architecture

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Enterprise Bus Matrix ⇗ Medium — Kimball Bus Architecture Explained

⇗ dbt — Project Planning & Architecture

⇗ Microsoft — Enterprise DW Reference Architecture

⬡ BENEFITS ✔ ADOPT WHEN

✦ Forces dimension conformance decisions to be ✔ Always — build the bus matrix before designing
made explicitly before any physical development any physical tables in an enterprise DW
begins
✔ Multiple business processes or subject areas are
✦ Communicates the DW architecture to business in scope (not a single-subject analytics product)
stakeholders without technical jargon
✔ Multiple teams will own different subject areas —
✦ Reveals the incremental build sequence — the matrix aligns them on shared dimensions
which mart to build first based on shared dim
✔ Present the bus matrix to business stakeholders
reuse
for alignment before starting any engineering work
✦ Creates a living reference document — the bus
✔ Maintain it as a living document — add rows
matrix evolves as new subject areas are added
when new business processes are added to the DW
✦ Prevents siloed mart development that
produces incompatible, non-interoperable data
products

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 116 / 126

 Drill-Across & Drill-Through Query Conformed Dim Payoff
Patterns
QUERY PATTERN · 10.2

Description: Drill-across queries combine metrics from two or more separate fact tables on
a shared conformed dimension axis — e.g. compare sales revenue vs. inventory levels vs.
marketing spend by product and month. Drill-through navigates from a summary aggregate
down to the underlying atomic transaction rows. Both patterns only work correctly when
dimensions are conformed. They represent the primary analytical payoff of the Kimball
architecture.

DRILL-ACROSS — CROSS-MART COMPARISON VIA CONFORMED

DIMENSIONS

-- Drill-across: Compare Sales Revenue vs Marketing Spend by Month

and Product Category
-- Each subquery hits a SEPARATE fact table; joined on CONFORMED
dimension keys

WITH sales AS (
SELECT d.year_month, [Link], SUM([Link]) AS sales_revenue
FROM FactSales f
JOIN DimDate d ON f.date_key = d.date_key ← conformed dim
JOIN DimProduct p ON f.product_key = p.product_key ← conformed dim
GROUP BY 1,2
),
marketing AS (
SELECT d.year_month, [Link], SUM([Link]) AS mkt_spend
FROM FactMarketingSpend m
JOIN DimDate d ON m.date_key = d.date_key ← SAME conformed dim
JOIN DimProduct p ON m.product_key = p.product_key ← SAME conformed
dim
GROUP BY 1,2
)
SELECT s.year_month, [Link], s.sales_revenue, m.mkt_spend,
ROUND(mkt_spend / NULLIF(sales_revenue,0) * 100, 1) AS
mkt_pct_of_sales
FROM sales s
LEFT JOIN marketing m USING (year_month, category);

Non-conformed dim danger: If DimProduct in FactSales uses different category

values than DimProduct in FactMarketingSpend, the JOIN produces wrong cross-mart
results silently. Conformed dimensions are the prerequisite for valid drill-across.

DRILL-THROUGH — FROM SUMMARY TO ATOMIC DETAIL

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 117 / 126

 LEVEL TABLE USED GRAIN EXAMPLE QUESTION

L1 — FactSalesAnnual Year × Region "Total 2024 revenue by

Executive region?"

L2 — FactSalesMonthly Month × Product "Which product categories

Management Category × Store drove the Q3 shortfall?"

L3 — FactSalesDaily Day × Product × "Which days in August

Analyst Store showed the revenue drop?"

L4 — FactSalesTransaction Individual line "Show me every transaction

Auditor item in Store 7 on Aug 14th"

BI tool implementation: Modern BI tools (Tableau, Power BI, Looker) support drill-
through natively — a user clicks a bar on a monthly chart and sees the underlying
transaction rows. This requires a defined "detail" data source pointing to the atomic
fact table, linked to the same conformed dimensions as the summary.

◈ REAL-WORLD PROJECT EXAMPLES

RETAIL ANALYTICS PHARMA / HEALTHCARE

Tata Retail Promo ROI Drill-Across Sun Pharma / Cipla Sales vs

Business question: "For every product Prescription Drill-Across
category in Q3, compare actual sales revenue Sales team has FactSalesRepVisits; Medical
vs marketing spend vs inventory cost." Three team has FactPrescriptionData from IQVIA.
separate fact tables (FactSales, Drill-across on conformed DimDoctor and
FactMarketingSpend, FactInventoryCost) DimDrug: "Which doctors received >3 visits
joined via conformed DimDate.year_quarter last month but still haven't prescribed the new
and [Link] in a drill-across drug?" This cross-mart query is only possible
query. Without conformed dims: the JOIN because DimDoctor and DimDrug are
produces a cartesian product. With conformed conformed across both fact tables with
dims: the result is a clean, trusted comparison identical keys.
used in the board deck.
Pharma cross-mart analytics is one of the
Drill-across is the primary ROI proof of most high-value drill-across use cases —
a well-designed Kimball architecture — it direct link between sales investment and
justifies the months spent conforming prescription outcome
dimensions

⇗ Kimball Drilling Across Fact Tables

⇗ Kimball Drilling Across Fact Tables

⇗ Pharma Analytics — IQVIA

⇗ Looker Drill Fields

⇗ Drill-Across vs Drill-Down (Medium)

⇗ Power BI Drill-Across Pattern

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — Drilling Across Fact Tables ⇗ Medium — Drill-Across vs Drill-Down

⇗ Power BI — Drill-Through Configuration ⇗ Looker — Drill Fields and Drill Paths

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 118 / 126

 ⬡ BENEFITS ✔ ADOPT WHEN

✦ Drill-across unlocks enterprise-level analytics — ✔ Multiple fact tables exist that share conformed
compare KPIs across completely separate dimensions — drill-across becomes immediately
business processes available

✦ Drill-through provides transparency and audit ✔ Business stakeholders ask cross-mart questions:
capability — no more "trust the dashboard" "how does our marketing spend compare to
without validation revenue by category?"

✦ Both patterns are the direct return on ✔ Executives need summary views but auditors
investment from building conformed dimensions need transaction-level traceability
correctly
✔ Pre-requisite: all dimensions involved in the
✦ Reduces the need for ad-hoc data pulls — cross-mart join must be fully conformed
analysts can self-serve from summary to detail
without engineering

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 119 / 126

 Audit & Technical Columns — Every Table Needs These
Production Engineering Standard
DESIGN PRINCIPLE · 10.3

Description: Every production DW table — both fact and dimension — should carry a
standard set of audit and technical columns. These columns are not business attributes and
are never exposed to end users in BI tools, but they are essential for pipeline monitoring, data
quality investigation, reprocessing, lineage tracing, and change detection. Defining them as a
standard across the entire DW prevents inconsistency and ad-hoc firefighting.

STANDARD AUDIT COLUMN SET — DIMENSION TABLES

COLUMN TYPE VALUE PURPOSE

dw_insert_timestamp TIMESTAMP UTC timestamp when Row creation audit;

row was first differs from
inserted into DW effective_from_date

dw_update_timestamp TIMESTAMP UTC timestamp of Row modification audit;

most recent change NULL = never updated
to the row

source_system VARCHAR Originating system Lineage — which source

name: 'SAP', system produced this row?
'Salesforce', 'CDC-
Oracle'

source_natural_key VARCHAR Original PK from Join back to source for

source system reconciliation; never
used as DW FK

etl_batch_id VARCHAR / Unique ID of the Trace any row back to a

BIGINT ETL/ELT run that specific pipeline
loaded this row execution

row_hash CHAR(64) SHA-256 of all Change detection in ETL —

tracked business compare hash to find
attribute columns updates

is_inferred BOOLEAN TRUE if this is an Flag late-arriving dim

inferred/placeholder rows that haven't been
member row backfilled yet

STANDARD AUDIT COLUMN SET — FACT TABLES

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 120 / 126

 COLUMN TYPE VALUE PURPOSE

event_date_key INTEGER FK Business event date The when of the business

(YYYYMMDD) — joins event — drives all time
to DimDate analysis

load_date DATE Calendar date the Pipeline latency

row was physically monitoring; identifies
loaded into DW late-arriving facts

dw_insert_timestamp TIMESTAMP UTC timestamp of Precise load audit with

physical insert time-of-day granularity

source_system VARCHAR Source system name Multi-source fact tables

— identify origin of
each row

etl_batch_id VARCHAR / ETL/ELT run ID Reprocess a specific

BIGINT batch if an error is
found

is_late_arriving BOOLEAN TRUE if event_date < Flag rows where late

load_date by more arrival may affect
than N days period-end snapshots

is_correction BOOLEAN TRUE if this row is Enables audit queries to

a reversal or distinguish original vs
restated correction restated facts

Separation rule: Audit columns must never be exposed in BI tool data sources or
semantic layer definitions. They are for engineers and data stewards only. Create a
separate "technical view" of each table that excludes audit columns for BI
consumption.

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 121 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

GCP / BIGQUERY PRODUCTION DW BANKING / REGULATORY

Infosys / Wipro GCP DW Audit SBI / RBI-Regulated DW Audit Columns

Standard RBI inspection: "Show every row in the credit
Every BigQuery table (fact and dim) has: risk dimension that was modified in October
dw_insert_timestamp, source_system, 2023 and prove when the modification was
etl_batch_id (Cloud Composer DAG run ID), recorded." dw_update_timestamp answers the
row_hash (SHA-256), load_date. Cloud "when recorded" question;
Composer monitoring dashboard queries effective_from_date answers the "when
load_date vs event_date to detect late effective" question. Without these audit
arrivals. When a batch fails and reruns, columns, the bank cannot answer the RBI
etl_batch_id identifies exactly which rows inspector's query — a compliance failure.
came from the failed run vs the clean rerun.
RBI/SEBI regulations require DW tables to
Cloud Composer sets etl_batch_id = maintain creation and modification
DAG_RUN_ID on every task; every row timestamps; audit columns are a
inserted can be traced to the exact regulatory requirement, not optional
Airflow DAG execution

⇗ RBI IT Framework for Banks

⇗ GCP Dataplex Data Lineage

⇗ Snowflake Access History

⇗ Cloud Composer Audit Logs

⇗ Azure Purview Data Lineage

⇗ dbt Source Freshness & Audit

⇗ Databricks Audit Logging

⇗ BigQuery Data Lineage (Dataplex)

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ dbt — Sources, Freshness & Audit ⇗ GCP Dataplex — Data Lineage & Quality

⇗ Databricks — Delta Lake Audit Logging ⇗ Snowflake — Access History & Data Lineage

⇗ Microsoft Purview — Data Lineage

⬡ BENEFITS ✔ ADOPT WHEN

✦ Any row in the DW can be traced back to the ✔ Always — define audit columns as a DW-wide
specific source system, source record, and ETL standard at project inception, not retroactively
run that produced it
✔ Document which columns are "audit-only" vs
✦ Pipeline incidents can be diagnosed and "business attributes" in your data dictionary
reprocessed precisely — no "which batch had the
✔ Add etl_batch_id at every layer (Bronze, Silver,
bad data?" guessing
Gold in Medallion) for end-to-end lineage
✦ Data quality monitoring (late arrivals, inferred
✔ Use Dataplex, Collibra, or dbt docs to surface
member counts, correction volumes) is built-in
lineage built from these audit columns
✦ row_hash eliminates expensive column-by-
✔ Automate monitoring dashboards off load_date
column change detection in ETL — one
vs event_date to detect late-arriving data patterns
comparison per row

✦ Standardisation across all tables means the ops

team has consistent tooling for all monitoring

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 122 / 126

 SCD Selection Decision Framework Architect's Decision Guide
DESIGN PRINCIPLE · 10.4

Description: Choosing the correct SCD type for each dimension attribute is one of the most
consequential design decisions in a DW. The wrong choice either loses history that business
needs or creates unnecessary complexity. This decision framework walks through the key
questions per attribute to arrive at the appropriate SCD type, covering all real-world scenarios
including corrections, compliance, and rapidly changing data.

DECISION FRAMEWORK — ATTRIBUTE-LEVEL SCD SELECTION

For each dimension attribute, answer these questions in order:

Q1 Is the attribute completely immutable — can it never legitimately change? (SSN,

date of birth, account open date)

→ SCD Type 0. Load once, never update.

Q2 Is the change a data quality correction (typo fix, format standardisation) — not a

real-world event?

→ SCD Type 1. Overwrite. Old value was simply wrong. No history needed.

Q3 Is the change a real-world event AND does the business need historical analysis

("what was X at the time of this transaction?")?

→ SCD Type 2 (default). Full version history. The standard choice for most attributes.

Q4 Is this a known one-time structural change where only the immediately prior value

matters?

→ SCD Type 3. Add prev_value column. Use only for planned migrations/reorgs.

Q5 Does the attribute change very frequently (daily/weekly for millions of rows) —

causing SCD2 to bloat unacceptably?

→ Mini-Dimension (RCD). Split fast-changing attributes into a separate mini-dim.

Q6 Do you need both historical accuracy AND current state accessible simultaneously

from the same row (for BI tools)?

→ SCD Type 6 (Hybrid). Adds current_* columns to Type 2. Gold standard for analytics.

Q7 Is there a regulatory requirement to prove "what did the system believe at time T"

— independent of real-world truth?

→ Bi-Temporal. Two independent time axes. Use only when compliance genuinely requires

it.

QUICK REFERENCE MATRIX

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 123 / 126

 SCENARIO HISTORY NEEDED? CORRECTION OR REAL CHANGE? CHANGE FREQUENCY → USE

Date of N/A (immutable) Never changes Never Type 0

birth,
account
open date

Phone No Correction of bad data Occasional Type 1

format fix,
name typo

Customer Yes Real-world change Low–Medium Type 2 ★

address default
change

Territory Prior only Planned restructure Once Type 3

realignment
(one-time)

Sales rep Yes + current Real-world change Low–Medium Type 6 ★

address + best
current
needed

Credit Yes Real-world change Very High Mini-Dim

score (RCD)
(changes
monthly)

Financial Yes + system Both Any Bi-

regulatory state Temporal
audit

Important: SCD type is decided per attribute, not per dimension table. A single
DimCustomer may have: Type 0 for customer_since_date, Type 1 for phone_number,
Type 2 for address and segment, Type 6 for the primary analytical attributes. Always
document the SCD decision for each attribute in your data dictionary.

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 124 / 126

 ◈ REAL-WORLD PROJECT EXAMPLES

E-COMMERCE HEALTHCARE

Flipkart / Myntra DimCustomer AIIMS / Max Hospital DimPatient SCD

Attribute-Level Review Review
DimCustomer design review: DimPatient attributes audited: date_of_birth →
customer_since_date → Q1: immutable? YES → Type 0 (Aadhaar-verified, immutable).
Type 0. email_address → Q2: correction or real contact_phone → Type 1 (contact info, no
change? CORRECTION → Type 1. home_city → history needed). residential_address → Type 2
Q3: real change, history needed? YES → Type (impacts insurance zone, history needed for
2. loyalty_tier → Q6: need current AND historical claims). blood_group → Type 0
historical? YES → Type 6 (gold standard). (biological fact, immutable). insurance_tier →
credit_score_band → Q5: changes monthly for Type 6 (historical accuracy for past claims +
80M customers, SCD2 would add 80M rows/ current tier for new admissions). risk_band →
month → Mini-Dim (RCD). Result: one RCD Mini-Dim (changes quarterly for 2M
DimCustomer table with 5 different SCD patients).
strategies per attribute group.
Healthcare DimPatient has more Type 0
SCD decision per attribute documented in attributes than most dims — biological
Collibra with business owner sign-off; facts (blood group, DOB) are truly
this single review prevented a Type 2 on immutable and must be protected from
email (wrong) and Type 1 on loyalty tier accidental overwrites
(wrong)

⇗ Kimball SCD Type Selection

⇗ Kimball SCD Type Selection

⇗ Healthcare Data Governance (GCP)

⇗ dbt Snapshot Strategy Guide

⇗ FHIR Patient Resource

⇗ Choosing the Right SCD (Medium)

⇗ Dataplex Data Quality Rules

⇗ Collibra Data Dictionary

⇗ REFERENCE DOCUMENTATION & FURTHER READING

⇗ Kimball — SCD Type Selection Guide ⇗ dbt — Snapshot Strategy Selection

⇗ Medium — Choosing the Right SCD Type ⇗ Databricks — SCD Decision Framework

⬡ BENEFITS ✔ ADOPT WHEN

✦ Eliminates the most common DW design error: ✔ Designing any new dimension table — walk
applying Type 2 to every attribute regardless of through this framework for every attribute before
business need coding

✦ Documents the reasoning behind each SCD ✔ Reviewing an existing DW for technical debt —
choice — future engineers understand why, not audit each dim attribute's SCD type against this
just what framework

✦ Prevents under-engineering (losing history that ✔ Onboarding new data engineers — this
compliance needs) and over-engineering framework replaces "just use Type 2 for everything"
(bloating with unneeded versions) as default guidance

✦ Provides a consistent framework for cross-team ✔ Record the SCD decision and rationale per
discussion and review of dimension designs attribute in the data dictionary / data catalogue
(Collibra, Dataplex)

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 125 / 126

 Decision Cheat
QUICK REFERENCE

Sheet

SCD TYPE SELECTION SCHEMA SELECTION

SITUATION USE SITUATION USE

Attribute never changes Type 0 Standard BI/ Star Schema

analytics workload
History has no value / Type 1
typo fix Large hierarchies, Snowflake
storage concern
Need full point-in-time Type 2 ✓
history Default Multiple business Galaxy /
processes, Constellation
One-time structural Type 3 enterprise DW
change, compare before/
after Multi-source Data Vault 2.0
ingestion, full
Want clean current dim + Type 4 audit needed
separate full history
Columnar engine, OBT
Fast attrs + need current Type 5 simple queries,
shortcut from base dim startup

Need current AND Type 6 ✓

historical on same row Best FACT TYPE SELECTION

Dual-view via keys (no Type 7 SITUATION USE

physical overwrite)
Individual events Transaction
Attribute changes monthly RCD → (sales, clicks) Fact
for millions of rows Mini-Dim
Balances, levels at Periodic
fixed intervals Snapshot

Pipeline lifecycle Accumulating

(orders, tickets) Snapshot

Presence/coverage — Factless Fact

no measures

Dimensional Modeling Complete Reference · Based on Kimball Group methodology · Data Vault
2.0 · Modern Lakehouse patterns

Covers: Fact Types · Dimension Archetypes · SCD 0–7 · RCD · Bi-Temporal · Star/Snowflake/Galaxy/
Vault/OBT · Hierarchy Patterns · Grain · Key Strategies

Debajyoti Mukhopadhyay Dimensional Modeling — Complete Reference 126 / 126