3.1.2. Dimensional Model Preparation

💡 First Principle: Dimensional models (star schemas) optimize data for analytical queries by pre-joining and denormalizing—trading storage efficiency for query speed. Think of it like meal prep: you could cook each ingredient separately every time, or you could pre-combine common combinations so dinner is ready faster.

Scenario: An OLTP database stores orders in normalized tables (Orders, OrderItems, Customers, Products). Analytical queries must join all tables, causing slow performance. A dimensional model pre-joins and denormalizes for fast queries.

Star Schema Components

Visual: Star Schema Structure

Data Transformation for Dimensional Models

1. Extract from source systems into a staging area (a recovery point that also isolates the source), orchestrated by a Data Factory pipeline
2. Clean data quality issues
3. Conform dimensions (standardize attributes)
4. Generate surrogate keys
5. Load dimensions first, then facts. If a schedule change lets the fact load run first, fact rows that reference new customers or products find no dimension row yet and fail referential-integrity checks.

Modeling rules that exams test:

• Attributes live in dimensions, measures in facts. A fact row holds only foreign keys plus measures; a customer's address belongs in DimCustomer, not FactSales. Storing each attribute once is also why a star beats one flat table: a name change updates one dimension row, and the narrow fact table scans fast in a columnar engine.
• One date dimension serves every date. Calendar and fiscal attributes (FiscalYear, FiscalQuarter) both go in DimDate. A fact with several dates carries one key per role (OrderDateKey, ShipDateKey), each joining the same DimDate: a role-playing dimension.

⚠️ Exam Trap: Using natural keys in fact tables creates problems when source keys change (customer ID format updates, product SKU restructuring). Natural keys can change; surrogate keys provide stable references. Always generate surrogate keys for dimension tables.