The Dimensional Modeling Hub serves as the central index and architectural guide for designing analytical data warehouses and business intelligence systems. Established by Ralph Kimball, dimensional modeling structures data around business processes to maximize query readability, analytical navigation, and computational performance across columnar database engines.
This hub details core modeling primitives: Fact Tables, Dimension Tables, Slowly Changing Dimensions (SCD Types 0–6), and the design trade-offs between Star Schemas, Snowflake Schemas, and One Big Table (OBT) architectures.
Analytical query processing demands fundamentally different schema structures than Online Transaction Processing (OLTP):
+-------------------------------------------------------------------------------+
| OLTP (3NF / Inmon) vs. OLAP (Dimensional / Kimball) |
+-------------------------------------------------------------------------------+
| Characteristic | 3rd Normal Form (3NF) | Dimensional Model |
+---------------------+------------------------------+--------------------------+
| Primary Goal | Minimize data redundancy | Maximize query speed and |
| | and eliminate update anomalies| business understandability|
| Target Workload | Thousands of small writes/sec| Complex analytical scans |
| Schema Complexity | Hundreds of joined tables | Compact Star Schemas |
| Query Performance | Expensive multi-table joins | Highly optimized joins, |
| | | fast columnar aggregations|
| Optimization Target | Write-optimized (OLTP) | Read-optimized (OLAP/BI) |
+---------------------+------------------------------+--------------------------+
A dimensional schema represents a business process through two primary constructs:
Star Schema Topology:
+----------------------+
| Dim_Date |
+----------------------+
| (Date_Key)
v
+------------------+ (Cust_Key)+----------------------+ (Prod_Key)+------------------+
| Dim_Customer | ---------> | Fact_SalesOrders | <-------- | Dim_Product |
+------------------+ +----------------------+ +------------------+
| (Store_Key)
v
+----------------------+
| Dim_StoreLocation |
+----------------------+
Fact tables contain quantitative numerical measurements (facts) generated by a business event, alongside foreign keys pointing to surrounding dimension tables:
Sales_Amount, Quantity_Sold).Account_Balance—summing across 30 days is meaningless; average or end-of-period value is required).Unit_Price, Profit_Margin_Percentage).Dimension tables contain descriptive textual attributes that provide filtering, grouping, and labeling context for facts.
+-------------------------------------------------------------------------------+
| SPECIALIZED DIMENSION PATTERNS |
+-------------------------------------------------------------------------------+
| Pattern | Description and Architectural Purpose |
+---------------------+---------------------------------------------------------+
| Conformed Dimension | Shared across multiple fact tables (e.g., Dim_Date, |
| | Dim_Customer) ensuring consistent enterprise metrics. |
| Degenerate Dimension| Dimensional identifier stored directly in fact table |
| | without an associated lookup table (e.g., Invoice_ID). |
| Junk Dimension | Combines low-cardinality flags and codes into a single |
| | table, avoiding dozens of micro-dimension foreign keys. |
| Role-Playing Dim | Single physical dimension referenced multiple times in |
| | one fact table (e.g., Dim_Date as OrderDate, ShipDate). |
| Outrigger Dimension | A dimension table referenced by another dimension table |
| | (used sparingly to prevent excessive snowflake nesting).|
+---------------------+---------------------------------------------------------+
Every dimension table should use an artificial integer Surrogate Key (e.g., Customer_SK) as its primary key rather than the source system operational Natural Key (Customer_ID):
VARCHAR).Customer addresses, product categories, and employee departmental assignments change over time. Slowly Changing Dimension (SCD) techniques manage historical changes:
Slowly Changing Dimension (SCD) Types Summary:
+----------+--------------------+-----------------------------------------------+
| SCD Type | Name | Historical Treatment |
+----------+--------------------+-----------------------------------------------+
| Type 0 | Retain Original | Attribute values never change (e.g., DoB). |
| Type 1 | Overwrite | Overwrites old value with new; zero history. |
| Type 2 | Add New Row | Adds a new row with valid date range |
| | (Canonical) | (effective_date, end_date, is_current=True). |
| Type 3 | Add Attribute | Preserves previous value in a dedicated col |
| | | (e.g., current_region, previous_region). |
| Type 4 | History Table | Base table stores current value; separate |
| | | historical log table records past mutations. |
| Type 6 | Hybrid (1 + 2 + 3) | Combines Type 1, 2, and 3: Type 2 rows with a |
| | | Type 1 overwritten current_attribute column. |
+----------+--------------------+-----------------------------------------------+
-- SCD Type 2 Implementation Pattern
CREATE TABLE dim_customer (
customer_sk BIGINT PRIMARY KEY,
customer_id VARCHAR(64) NOT NULL,
customer_name VARCHAR(255) NOT NULL,
customer_tier VARCHAR(50) NOT NULL,
city VARCHAR(100) NOT NULL,
effective_date TIMESTAMP NOT NULL,
end_date TIMESTAMP NOT NULL,
is_current BOOLEAN NOT NULL
);
+---------------------------+-------------------+--------------------+------------------------+
| Architecture Model | Normalization | Storage Efficiency | Query Speed (Columnar) |
+---------------------------+-------------------+--------------------+------------------------+
| Star Schema | Denormalized | Moderate (Small | Very Fast (Single-hop |
| | Dimensions | dimension tables) | foreign key joins) |
| Snowflake Schema | Normalized | High (No redundant | Slower (Requires multi-|
| | Dimensions | dimension strings) | table dimension joins) |
| One Big Table (OBT) | Fully Flattened | Low (Redundant | Fastest (Zero joins; |
| | Single Table | compressed storage)| direct vectorized scan)|
+---------------------------+-------------------+--------------------+------------------------+
In modern cloud columnar warehouses (BigQuery, Snowflake, ClickHouse), lightweight compression algorithms (Dictionary Encoding, Run-Length Encoding, ZSTD) compress repeated strings in Star Schemas and OBTs to near-zero storage footprint, rendering normalized Snowflake schemas largely obsolete.