Dimensional & Analytical Modeling

Why this matters for interviews

Analytics-heavy companies (Meta, Airbnb, Netflix, Uber) often ask Data Engineers to design a warehouse mart — not just run Spark jobs. You need vocabulary for star schemas, grain, and surrogate keys, separate from OLTP normalization (covered in Databases).

OLTP vs dimensional (OLAP) modeling

OLTP (operational)	OLAP (analytical)
Normalize to reduce write anomalies	Denormalize for read performance
Many small tables, joins at query time	Few wide tables, pre-joined for BI
Optimized for point lookups & updates	Optimized for scans & aggregations
Natural keys common	Surrogate keys common

Databases covers B-trees, columnar storage, and lake formats — here we cover how to shape tables for warehouses and gold marts.

Star vs snowflake

Star schema: one fact table surrounded by denormalized dimension tables.

dim_date │ dim_customer ── fct_orders ── dim_product │ dim_store

Snowflake schema: dimensions are normalized into sub-dimensions (e.g. dim_product → dim_category → dim_department).

	Star	Snowflake
Query simplicity	Fewer joins	More joins
Storage	Some redundancy	Less redundancy
ETL complexity	Simpler loads	More tables to maintain
Typical at FAANG lakehouse	Default for gold marts	When hierarchy is large or shared

Modern teams usually star at the mart layer; snowflake only when dimension hierarchies are huge or reused across many facts.

Fact tables

A fact stores measurements at a defined grain — the finest level of detail one row represents.

Grain examples:

Fact	Grain (one row =)
fct_orders	One line item on an order
fct_daily_revenue	One store × one product × one day
fct_subscription_snapshot	One subscriber × one month-end

Wrong grain is a common interview trap: mixing order-level and line-item-level in one fact breaks aggregations.

Measure types

Type	Example	Aggregation
Additive	revenue, quantity	Sum across all dimensions
Semi-additive	account_balance, inventory	Sum across some dims, not time
Non-additive	margin_pct, unit_price	Never sum — recalculate from components

Store additive facts as numbers; store ratios in dimensions or recompute in BI.

Degenerate dimensions

Attributes that belong to the fact grain but aren't worth a separate dimension — e.g. order_id, invoice_number as columns on fct_orders with no dim_order.

Dimension tables

Dimensions describe who, what, where, when context for facts.

Conventions:

• Surrogate key — warehouse-generated integer (customer_sk) as the join key
• Natural key — source-system ID (customer_id from CRM) stored as an attribute, not the primary join key
• SCD attributes — type-1 overwrite vs type-2 history (implementation in Medallion & Incremental Processing)

Why surrogate keys?

Benefit	Explanation
Source independence	Merge CRM + billing customers without key collision
SCD2 support	Multiple rows per natural key over time
Performance	Narrow integer joins vs wide strings
Late-arriving data	Placeholder row until dimension arrives

Common dimension types

Type	Example
Conformed	Same dim_date used by sales and support marts
Role-playing	One physical dim_date joined twice (order date vs ship date)
Junk	Low-cardinality flags (is_gift, is_express) bundled into one dimension
Mini	Small reference table embedded as dimension (status codes)

Star-schema design checklist

1. Declare grain in one sentence before drawing tables
2. List dimensions required to describe that grain
3. Choose measures — additive facts only at this grain
4. Assign surrogate keys on every dimension
5. Document unknown/null handling (Unknown Customer row)

Link to medallion gold

Layer	Modeling role
Bronze	Source fidelity — no dimensional modeling
Silver	Conformed entities, cleaned natural keys
Gold	Star-schema marts for BI and downstream ML features

Interview answer template

"Order line-item grain: fct_order_items with order_item_sk surrogate, FKs to dim_customer_sk, dim_product_sk, dim_date_sk (order date). Measures: quantity, extended_price (additive). order_id is a degenerate dimension. Customer dimension uses surrogate keys with SCD2 for address changes — implemented in silver merge, exposed in gold."