Data Modeling Patterns

SLIDE 64: Section Intro, Data Modeling Patterns

Section 6: Data Modeling Patterns

Data modeling is where data engineering intersects most directly with business value. A well-designed data model makes analytical queries fast, intuitive, and correct. A poorly designed model makes every downstream consumer struggle: analysts write convoluted SQL with dozens of joins, dashboards time out, and subtle errors in aggregation go undetected for months because the schema is too confusing to audit.

This section teaches the modeling patterns used in production analytical systems at companies like Netflix, Uber, Airbnb, and Spotify. You will learn how to design star schemas, choose fact table types, handle slowly changing dimensions, define grain correctly, and make the trade-off between normalization and denormalization for analytical workloads.

Recognition Signal:

When the interviewer asks you to design the schema for an analytics system, when they ask “how would you model this data for the warehouse?”, or when you need to decide how to structure data for analytical queries, you are in data modeling territory. This also applies when an interviewer asks you to design the output of a data pipeline: the data model IS the output.

What We’ll Cover:

1. Operational (OLTP) vs analytical (OLAP) data models
2. Normalization for transactional systems, denormalization for analytical systems
3. Star schema and snowflake schema
4. Fact table types: transaction, periodic snapshot, accumulating snapshot
5. Slowly Changing Dimensions (SCD Types 1, 2, and 3)
6. Data Vault modeling for enterprise-scale warehouses
7. Grain: the single most important modeling decision
8. Denormalization techniques and semi-structured data modeling

Connection to the Pipeline Mental Model:

Data modeling lives at the intersection of the Store, Transform, and Serve stages. The data model defines the structure of the Gold layer in the medallion architecture (Section 7) and determines how data is organized in the warehouse for consumption by dashboards, analysts, and ML models. The pipeline exists to produce this model. If the model is wrong, the pipeline delivers the wrong output, no matter how well-engineered the pipeline itself is.

SLIDE 65: Operational vs Analytical Data Models

The same data requires fundamentally different schema designs depending on whether it serves an application (OLTP) or serves analytics (OLAP). This distinction is the starting point for all data modeling decisions.

OLTP (Online Transaction Processing):

The application database. Optimized for high-frequency reads and writes of individual records. A user places an order, updates their profile, or sends a message. Each operation touches one or a few rows. The schema is normalized to eliminate redundancy and prevent update anomalies.

-- OLTP query: fetch one order with its items
SELECT o.order_id, o.order_date, oi.product_id, oi.quantity, oi.price
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_id = 12345;
-- Reads: 1 order + 3 items = 4 rows. Milliseconds.

OLAP (Online Analytical Processing):

The analytics warehouse. Optimized for complex queries that aggregate millions of rows. An analyst asks “what was total revenue by product category and region for Q4?” This query scans millions of rows across multiple tables. The schema is denormalized to minimize joins and maximize scan performance.

-- OLAP query: aggregate revenue across millions of orders
SELECT d.category, g.region, SUM(f.revenue) as total_revenue
FROM fact_orders f
JOIN dim_product d ON f.product_key = d.product_key
JOIN dim_geography g ON f.geo_key = g.geo_key
JOIN dim_date dt ON f.date_key = dt.date_key
WHERE dt.quarter = 'Q4-2024'
GROUP BY d.category, g.region;
-- Reads: millions of fact rows, joins to small dimension tables. Seconds.

Why You Cannot Use the Same Schema for Both:

A normalized OLTP schema requires many joins for analytical queries. An orders analysis might require joining orders, order_items, products, categories, customers, addresses, and payments. Seven-table joins on millions of rows are extremely slow. Conversely, a denormalized OLAP schema stores redundant data (product name repeated on every order line), which wastes storage and creates update anomalies in a transactional context (renaming a product requires updating millions of rows).

The data pipeline’s job is to transform OLTP-optimized data into OLAP-optimized data. This is the core of the Transform stage.

SLIDE 66: Normalization for OLTP: Why It Matters

Normalization organizes data to eliminate redundancy. Each fact is stored exactly once. This matters for OLTP systems because redundancy causes update anomalies: if a customer’s address is stored in 50 order rows and the customer moves, you must update all 50 rows. Miss one, and you have inconsistent data.

Normal Forms Through a Concrete Example:

Consider an order management system.

Unnormalized (all data in one table):

orders_flat:
| order_id | customer_name | customer_email | product_name | category | price | qty |
| 1001     | Alice Chen    | alice@x.com    | Laptop Pro   | Electronics | 1200 | 1  |
| 1001     | Alice Chen    | alice@x.com    | USB Cable    | Accessories | 15   | 2  |
| 1002     | Bob Smith     | bob@y.com      | Laptop Pro   | Electronics | 1200 | 1  |

Problems: Alice’s email is stored twice (redundancy). “Laptop Pro” and its category are repeated (redundancy). If the laptop price changes, you must update every row that references it.

First Normal Form (1NF): Each column holds atomic values, each row is unique. The table above is already in 1NF if we consider (order_id, product_name) as the composite key.

Second Normal Form (2NF): Every non-key column depends on the entire primary key, not just part of it. customer_name depends only on order_id, not on product_name. Fix: separate customers from order items.

Third Normal Form (3NF): No transitive dependencies. category depends on product_name, not on the primary key. Fix: separate products from order items.

Normalized result (3NF):

customers (customer_id, name, email)
products (product_id, name, category, price)
orders (order_id, customer_id, order_date)
order_items (order_id, product_id, quantity, line_price)

Now each fact is stored once. Updating a customer email or product price requires changing exactly one row. This is ideal for transactional systems.

The Trade-Off for Analytics:

The normalized schema requires a 4-table join to answer “what did Alice buy?” In OLTP, this is fine because the query touches a few rows. In OLAP, when the question becomes “what did 10 million customers buy last quarter?”, a 4-table join across hundreds of millions of rows becomes a serious performance problem. This is why analytical systems denormalize.

SLIDE 67: Denormalization for OLAP: Why It Matters

Denormalization is the deliberate introduction of redundancy to improve read performance. For analytical systems, the trade-off is clear: storage is cheap, but join performance on billions of rows is expensive. By pre-joining and pre-aggregating data, denormalization shifts the cost from query time (paid repeatedly by every analyst, every query) to pipeline time (paid once during the daily batch job).

What Denormalization Looks Like:

Instead of joining four tables at query time, the pipeline pre-joins them and stores the result as a wide, flat table:

-- Denormalized fact table (pipeline produces this during the Transform stage)
fact_orders_wide:
| order_id | order_date | customer_name | customer_email | customer_city |
| product_name | category | unit_price | quantity | line_total | region |

Now an analyst can answer “revenue by category and region” with a single-table scan and no joins:

SELECT category, region, SUM(line_total) as revenue
FROM fact_orders_wide
WHERE order_date BETWEEN '2024-10-01' AND '2024-12-31'
GROUP BY category, region;

The Cost of Denormalization:

Storage increases because data is duplicated. If “Laptop Pro” appears in 500,000 order rows, the product name and category are stored 500,000 times instead of once. At modern storage costs ($0.023/GB/month on S3), this is almost always acceptable.

Update consistency is harder. If a product is renamed, the denormalized table retains the old name in historical rows. In analytical systems, this is often desirable (you want to know what the product was called when the order was placed), but it must be managed explicitly through Slowly Changing Dimensions (covered on SLIDE 71).

The Rule:

Normalize for writes (OLTP). Denormalize for reads (OLAP). The data pipeline is the bridge: it reads from normalized sources and writes denormalized analytical models.

SLIDE 68: Star Schema: The Analytics Standard

Star schema is the most widely used data modeling pattern for analytical systems. It is the default choice for data warehouses at companies from startups to FAANG. The name comes from the shape of the schema diagram: a central fact table surrounded by dimension tables, forming a star.

Structure:

The fact table contains the measurable events of the business: orders placed, clicks recorded, rides completed, payments processed. Each row represents one event at a specific level of detail (the grain). Fact tables contain two types of columns: foreign keys to dimension tables and numeric measures (revenue, quantity, duration, cost).

Dimension tables contain the descriptive context for the events: who (customer), what (product), when (date), where (geography), how (payment method). Dimension tables are typically small (thousands to millions of rows) and wide (many descriptive columns).

Why Star Schema Works:

Star schema queries are intuitive. Analysts think in terms of “measures sliced by dimensions.” Revenue (measure) by category (dimension) by quarter (dimension) by region (dimension). The SQL maps directly to this mental model: SELECT dimensions, SUM(measures) FROM fact JOIN dimensions GROUP BY dimensions.

Query optimizers handle star schema efficiently. The joins are always fact-to-dimension (large to small), which are ideal for broadcast joins. Dimension tables fit in memory, so the join never requires shuffling the large fact table.

Star schema is flexible. Adding a new dimension (e.g., marketing channel) means creating a new dimension table and adding a foreign key to the fact table. Existing queries are unaffected. Adding a new measure (e.g., shipping cost) means adding a column to the fact table. No structural changes to dimensions.

SLIDE 68B: Star Schema: Worked Example

Scenario: Design the star schema for an e-commerce analytics warehouse that supports queries about orders, products, customers, and time-based trends.

Fact Table: fact_orders

CREATE TABLE fact_orders (
    order_key       BIGINT PRIMARY KEY,
    order_id        VARCHAR(50),        -- natural key from source system
    product_key     BIGINT REFERENCES dim_product,
    customer_key    BIGINT REFERENCES dim_customer,
    date_key        INT REFERENCES dim_date,
    geo_key         INT REFERENCES dim_geography,
    promo_key       INT REFERENCES dim_promotion,
    quantity        INT,
    unit_price      DECIMAL(10,2),
    discount_amount DECIMAL(10,2),
    tax_amount      DECIMAL(10,2),
    shipping_cost   DECIMAL(10,2),
    gross_revenue   DECIMAL(12,2),
    net_revenue     DECIMAL(12,2)       -- gross - discount
);

-- Grain: one row per order line item
-- At 50M orders/day with avg 3 items each: ~150M rows/day, ~55B rows/year

Dimension Tables:

CREATE TABLE dim_product (
    product_key     BIGINT PRIMARY KEY,   -- surrogate key
    product_id      VARCHAR(50),          -- natural key from source
    name            VARCHAR(200),
    category        VARCHAR(100),
    subcategory     VARCHAR(100),
    brand           VARCHAR(100),
    supplier        VARCHAR(200),
    unit_cost       DECIMAL(10,2),
    is_active       BOOLEAN,
    effective_date  DATE,                 -- SCD Type 2 fields
    end_date        DATE,
    is_current      BOOLEAN
);
-- ~2M products, ~10M rows with SCD Type 2 history

CREATE TABLE dim_customer (
    customer_key    BIGINT PRIMARY KEY,
    customer_id     VARCHAR(50),
    name            VARCHAR(200),
    email           VARCHAR(200),
    segment         VARCHAR(50),          -- e.g., "Premium", "Standard", "New"
    acquisition_channel VARCHAR(100),
    city            VARCHAR(100),
    state           VARCHAR(100),
    country         VARCHAR(50),
    registration_date DATE,
    effective_date  DATE,
    end_date        DATE,
    is_current      BOOLEAN
);
-- ~10M customers, ~15M rows with SCD Type 2 history

CREATE TABLE dim_date (
    date_key        INT PRIMARY KEY,      -- format: YYYYMMDD (e.g., 20240115)
    full_date       DATE,
    day_of_week     VARCHAR(10),
    day_of_month    INT,
    week_of_year    INT,
    month           INT,
    month_name      VARCHAR(10),
    quarter         VARCHAR(6),           -- e.g., "Q1-2024"
    year            INT,
    is_weekend      BOOLEAN,
    is_holiday      BOOLEAN,
    fiscal_quarter  VARCHAR(6),
    fiscal_year     INT
);
-- Pre-populated for 20 years: ~7,300 rows (tiny table)

CREATE TABLE dim_geography (
    geo_key         INT PRIMARY KEY,
    city            VARCHAR(100),
    state           VARCHAR(100),
    country         VARCHAR(50),
    region          VARCHAR(50),          -- e.g., "North America", "EMEA"
    timezone        VARCHAR(50)
);

Example Queries This Schema Supports:

-- Revenue by category and region for Q4 2024
SELECT p.category, g.region, SUM(f.net_revenue) as revenue
FROM fact_orders f
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_geography g ON f.geo_key = g.geo_key
JOIN dim_date d ON f.date_key = d.date_key
WHERE d.quarter = 'Q4-2024' AND p.is_current = TRUE
GROUP BY p.category, g.region
ORDER BY revenue DESC;

-- Top 10 customers by spending in the last 30 days
SELECT c.name, c.segment, SUM(f.net_revenue) as total_spend
FROM fact_orders f
JOIN dim_customer c ON f.customer_key = c.customer_key
JOIN dim_date d ON f.date_key = d.date_key
WHERE d.full_date >= CURRENT_DATE - INTERVAL '30 days'
  AND c.is_current = TRUE
GROUP BY c.name, c.segment
ORDER BY total_spend DESC
LIMIT 10;

-- Weekend vs weekday revenue comparison
SELECT d.is_weekend, SUM(f.net_revenue) as revenue, COUNT(*) as order_count
FROM fact_orders f
JOIN dim_date d ON f.date_key = d.date_key
WHERE d.year = 2024
GROUP BY d.is_weekend;

Notice how every query follows the same pattern: join fact to dimensions, filter on dimension attributes, aggregate fact measures. This consistency is why star schema is the standard.

SLIDE 69: Snowflake Schema: When to Normalize Dimensions

Snowflake schema is a variation of star schema where dimension tables are further normalized into sub-dimension tables. The “snowflake” name comes from the branching shape of these normalized dimensions.

Instead of storing category, subcategory, and department as columns in dim_product, each is broken into its own table with a foreign key relationship.

When Snowflake Schema Makes Sense:

Snowflake schema saves storage when dimensions are very large and contain highly repeated hierarchical data. If dim_product has 10 million rows and each row stores the full category hierarchy (category name, category description, department name, department head), that hierarchy data is repeated thousands of times. Normalizing it into a separate dim_category table (a few hundred rows) eliminates the redundancy.

It also helps when dimension hierarchies are independently queryable. If analysts frequently query “revenue by department” without needing product-level detail, a separate dim_department table makes that query simpler and faster.

When to Stick with Star Schema:

In most cases, the storage savings of snowflake schema are negligible compared to the complexity cost. A dim_product table with 2 million rows and a few repeated category names uses minimal extra storage. The additional joins (fact → product → category → department) add query complexity and can confuse analysts who expect the simple star join pattern.

Modern columnar warehouses (Snowflake, BigQuery, Redshift) compress repeated values extremely well. A category column with 50 unique values across 2 million product rows compresses to almost nothing. The storage argument for snowflake schema has weakened significantly in the cloud era.

The Rule:

Default to star schema. Use snowflake schema only when dimensions are genuinely large (tens of millions of rows), contain deep hierarchies with significant repeated data, and the team has the SQL sophistication to handle multi-level dimension joins. In interviews, stating this trade-off demonstrates modeling maturity.

SLIDE 70: Fact Table Types

Not all fact tables record the same kind of information. There are three types of fact tables, each designed for a different analytical pattern. Choosing the wrong type leads to either missing data or an awkward schema that forces complex workarounds.

Transaction Fact Table

One row per discrete event. The most common type. Each row captures a specific occurrence with its measures.

fact_orders:
| order_key | date_key | product_key | customer_key | quantity | revenue |
| 1         | 20240115 | 501         | 1001         | 2        | 2400.00 |
| 2         | 20240115 | 302         | 1002         | 1        | 15.00   |
| 3         | 20240116 | 501         | 1003         | 1        | 1200.00 |

Grain: one row per order line item. Rows are inserted, never updated. The table grows continuously. This is the right type for orders, clicks, payments, rides, messages, and any discrete event.

Periodic Snapshot Fact Table

One row per entity per time period. Captures the state of something at regular intervals rather than individual events.

fact_daily_inventory:
| date_key | product_key | warehouse_key | quantity_on_hand | quantity_reserved | reorder_point |
| 20240115 | 501         | 1             | 1250             | 80                | 200           |
| 20240115 | 501         | 2             | 830              | 45                | 150           |
| 20240116 | 501         | 1             | 1170             | 92                | 200           |

Grain: one row per product per warehouse per day. A new row is inserted at the end of each period (daily, weekly, monthly). This is the right type for balances, inventory levels, subscription counts, and any measure that represents a state at a point in time rather than an event.

Accumulating Snapshot Fact Table

One row per entity lifecycle. Tracks an entity through milestones with multiple date columns. Rows are updated as the entity progresses through its lifecycle.

fact_order_lifecycle:
| order_key | order_date | payment_date | ship_date | delivery_date | return_date |
|           | (created)  | (paid)       | (shipped) | (delivered)   | (returned)  |
| 1         | 2024-01-15 | 2024-01-15   | 2024-01-16| 2024-01-19    | NULL        |
| 2         | 2024-01-15 | 2024-01-15   | NULL      | NULL          | NULL        |

Grain: one row per order (the entire lifecycle). The row is inserted when the entity is created and updated as it progresses through each milestone. This is the right type for orders (created → paid → shipped → delivered → returned), loans (applied → approved → disbursed → repaid), and any process with defined stages.

Choosing the Right Type:

The decision comes from the analytical question. “How many orders were placed yesterday?” needs a transaction fact. “What was our inventory level at end of day?” needs a periodic snapshot. “What is the average time from order to delivery?” needs an accumulating snapshot.

In interviews, explicitly stating the fact table type and why you chose it demonstrates modeling depth: “I’d use a transaction fact table at the grain of one row per order line item, because the analytics team needs to slice and aggregate individual transactions by product, customer, and time.”

SLIDE 71: Slowly Changing Dimensions (SCD)

Dimension attributes change over time. A customer moves to a new city. A product is reclassified into a different category. An employee transfers to a different department. How the dimension table handles these changes determines whether historical analysis is accurate or misleading.

The Problem:

Customer Alice Chen lives in Toronto. In March 2024, she moves to Vancouver.

If we simply overwrite her city to "Vancouver":
  Query: "Revenue by city for January 2024"
  Result: Alice's January orders are attributed to Vancouver
  Reality: She lived in Toronto in January
  Consequence: Toronto's January revenue is understated, Vancouver's is overstated

SCD Type 1: Overwrite

Replace the old value with the new value. History is lost.

-- Before: customer_key=1001, city="Toronto"
UPDATE dim_customer SET city = 'Vancouver' WHERE customer_key = 1001;
-- After: customer_key=1001, city="Vancouver"
-- All historical orders now appear as "Vancouver"

Use Type 1 when history does not matter for the attribute. Fixing a typo in a customer name, correcting a data entry error, or updating a phone number. These are corrections, not changes.

SCD Type 2: Add New Row (Versioned History)

Create a new row for the new value. The old row is marked with an end date. Both versions coexist in the dimension table with different surrogate keys.

-- Before the move:
| customer_key | customer_id | name       | city    | effective_date | end_date   | is_current |
| 1001         | C-5001      | Alice Chen | Toronto | 2020-01-01     | 9999-12-31 | TRUE       |

-- After the move (March 15, 2024):
| customer_key | customer_id | name       | city      | effective_date | end_date   | is_current |
| 1001         | C-5001      | Alice Chen | Toronto   | 2020-01-01     | 2024-03-14 | FALSE      |
| 1002         | C-5001      | Alice Chen | Vancouver | 2024-03-15     | 9999-12-31 | TRUE       |

Now January orders reference customer_key=1001 (Toronto) and April orders reference customer_key=1002 (Vancouver). Historical analysis is correct.

Type 2 is the most common production approach for attributes where historical accuracy matters: customer location, product category, employee department, account status.

SCD Type 3: Add Previous Value Column

Add a column to store the previous value. Only one level of history is preserved.

| customer_key | name       | current_city | previous_city | city_change_date |
| 1001         | Alice Chen | Vancouver    | Toronto       | 2024-03-15       |

Type 3 is rarely used in practice because it only captures one change. If Alice moves again, the Toronto record is lost. Use Type 3 only when the business explicitly needs to compare “current vs previous” for a single attribute and deeper history is not required.

Which Type to Use:

Type 1 for corrections and attributes where history is irrelevant (phone number, typo fixes). Type 2 for attributes where historical accuracy in analytics matters (location, segment, category). This is the default for production systems. Type 3 is niche; in most cases where you would consider Type 3, Type 2 is the better investment.

SLIDE 71B: SCD Type 2: Worked Example

Scenario: A customer changes their segment from “Standard” to “Premium” on June 1, 2024 after spending over $10,000 in the last 12 months.

Step 1: Close the old record

UPDATE dim_customer
SET end_date = '2024-05-31',
    is_current = FALSE
WHERE customer_id = 'C-5001' AND is_current = TRUE;

Step 2: Insert the new record with a new surrogate key

INSERT INTO dim_customer (customer_key, customer_id, name, email, segment,
                          city, effective_date, end_date, is_current)
VALUES (next_surrogate_key(), 'C-5001', 'Alice Chen', 'alice@x.com', 'Premium',
        'Vancouver', '2024-06-01', '9999-12-31', TRUE);

Result:

| customer_key | customer_id | name       | segment  | city      | effective_date | end_date   | is_current |
| 1001         | C-5001      | Alice Chen | Standard | Toronto   | 2020-01-01     | 2024-03-14 | FALSE      |
| 1002         | C-5001      | Alice Chen | Standard | Vancouver | 2024-03-15     | 2024-05-31 | FALSE      |
| 1003         | C-5001      | Alice Chen | Premium  | Vancouver | 2024-06-01     | 9999-12-31 | TRUE       |

Three versions of Alice now exist. Fact rows from 2020-2024/03/14 reference key 1001 (Standard, Toronto). Fact rows from March-May 2024 reference key 1002 (Standard, Vancouver). Fact rows from June 2024 onward reference key 1003 (Premium, Vancouver).

Querying Current vs Historical:

-- Current state: "How many Premium customers do we have?"
SELECT segment, COUNT(DISTINCT customer_id) as customer_count
FROM dim_customer
WHERE is_current = TRUE
GROUP BY segment;

-- Historical analysis: "Revenue by customer segment for Q1 2024"
-- (automatically correct because fact rows point to the right version)
SELECT c.segment, SUM(f.net_revenue) as revenue
FROM fact_orders f
JOIN dim_customer c ON f.customer_key = c.customer_key
JOIN dim_date d ON f.date_key = d.date_key
WHERE d.quarter = 'Q1-2024'
GROUP BY c.segment;
-- Alice's Q1 orders correctly attributed to "Standard" (key 1001 and 1002)

-- Point-in-time lookup: "What segment was Alice in on April 1, 2024?"
SELECT segment FROM dim_customer
WHERE customer_id = 'C-5001'
  AND '2024-04-01' BETWEEN effective_date AND end_date;
-- Returns: "Standard" (key 1002)

Key Implementation Detail:

The fact table must reference the dimension’s surrogate key (customer_key), not the natural key (customer_id). If the fact table used customer_id, joining to the dimension would return all three versions of Alice, and the query would need additional date-range logic to pick the correct version. With surrogate keys, the join is simple and always returns exactly the right version.

SLIDE 72: Data Vault: Hubs, Links, and Satellites

Data Vault is an alternative modeling approach designed for enterprise-scale data warehouses with many source systems, frequent schema changes, and strict auditability requirements. It is more complex than star schema but solves problems that star schema struggles with at very large scale.

Three Building Blocks:

Hubs store unique business keys. A hub has a surrogate key, the business key, the load timestamp, and the record source. Hubs answer: “What entities exist in our system?”

hub_customer:
| hub_customer_key | customer_bk | load_date  | record_source |
| 1                | C-5001      | 2020-01-01 | CRM_System    |
| 2                | C-5002      | 2020-01-15 | Web_App       |

Links store relationships between hubs. A link connects two or more hubs and represents a business event or association.

link_order:
| link_order_key | hub_customer_key | hub_product_key | order_bk | load_date  |
| 1              | 1                | 501             | ORD-1001 | 2024-01-15 |

Satellites store descriptive attributes with temporal tracking. Each satellite belongs to a hub or link and contains the attributes that change over time (similar to SCD Type 2).

sat_customer_details:
| hub_customer_key | name       | email       | city      | load_date  | end_date   |
| 1                | Alice Chen | alice@x.com | Toronto   | 2020-01-01 | 2024-03-14 |
| 1                | Alice Chen | alice@x.com | Vancouver | 2024-03-15 | 9999-12-31 |

When Data Vault Makes Sense:

Data Vault is designed for environments where many source systems feed the warehouse, schemas change frequently, full auditability and lineage are required (financial services, healthcare, government), and the warehouse team needs to integrate new sources quickly without redesigning the entire model.

When Star Schema Is Better:

For most data engineering interviews and most production systems, star schema is the right answer. Star schema is simpler to query, easier for analysts to understand, and performs better for standard analytical workloads. Data Vault is appropriate when you have 20+ source systems feeding a central warehouse with strict compliance requirements. If the interviewer does not specifically mention these conditions, default to star schema.

Interview Tip:

Mention Data Vault to demonstrate breadth: “For this use case, I’d use a star schema because the query patterns are standard analytical aggregations and we have a manageable number of sources. If this were an enterprise with 50+ source systems and regulatory audit requirements, I’d consider Data Vault for the raw/staging layer and build star schema views on top for analyst consumption.”

SLIDE 73: Grain: The Most Important Modeling Decision

Grain is the definition of what a single row in a fact table represents. Getting grain wrong makes the entire data model wrong. Every measure in the fact table must be consistent with the grain. Every dimension join must make sense at the grain level.

Grain Defined:

The grain is stated as: “One row represents one [event/snapshot] at the level of [entity] per [time period].”

Grain examples:
  fact_orders:          "One row per order line item"
  fact_daily_revenue:   "One row per product category per region per day"
  fact_page_views:      "One row per page view event"
  fact_daily_inventory: "One row per product per warehouse per day"
  fact_monthly_balance: "One row per customer account per month"

Why Grain Matters:

The grain determines what questions the model can answer. A fact table at the grain of “one row per order” cannot tell you which products were in that order (product-level detail is lost). A fact table at the grain of “one row per order line item” can answer both order-level and product-level questions (aggregate up from line items to orders).

The Cardinal Rule: Never Mix Grains

Every row in a fact table must be at the same grain. Mixing grains (some rows represent individual transactions, other rows represent daily summaries) produces incorrect results when aggregated.

Choosing the Right Grain:

Start with the lowest grain that the business questions require. You can always aggregate up from a fine grain (order line items → orders → daily totals), but you cannot disaggregate from a coarse grain (daily totals → individual orders).

If unsure, go with the finest grain available. Disk is cheap. Storing one row per click event (billions of rows) is better than storing one row per daily user summary and later discovering that the business needs session-level analysis.

How to State Grain in an Interview:

“Each row in fact_orders represents one order line item. This means a single order with 3 products generates 3 rows in the fact table. The measures (quantity, unit_price, line_total) are all consistent with this grain. I chose line-item grain rather than order grain because the analytics team needs to analyze revenue by product category, which requires product-level detail.”

SLIDE 74: Denormalization Techniques for Performance

Beyond star schema, several additional denormalization techniques trade storage and freshness for query speed. These are tools for specific performance problems, not default approaches.

Pre-Aggregated Summary Tables (Rollups)

Compute aggregations at multiple levels of granularity during the pipeline and store each as a separate table.

-- Finest grain (pipeline produces daily)
fact_orders:       1 row per order line item     (~150M rows/day)

-- Daily rollup (pipeline produces daily)
agg_daily_revenue: 1 row per category × region × day  (~50K rows/day)

-- Monthly rollup (pipeline produces monthly)
agg_monthly_revenue: 1 row per category × region × month  (~1,500 rows/month)

The dashboard that shows “monthly revenue by region” queries the monthly rollup (1,500 rows) instead of scanning the fact table (4.5B rows for a month). The speedup is massive: milliseconds instead of minutes.

Materialized Views

A materialized view is a precomputed query result stored as a table and refreshed on a schedule. Snowflake, BigQuery, and Redshift all support materialized views natively.

CREATE MATERIALIZED VIEW mv_daily_category_revenue AS
SELECT d.full_date, p.category, SUM(f.net_revenue) as revenue, COUNT(*) as orders
FROM fact_orders f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
GROUP BY d.full_date, p.category;

-- Refresh nightly after the pipeline completes
REFRESH MATERIALIZED VIEW mv_daily_category_revenue;

The trade-off: materialized views use storage and require refresh management, but they make complex queries instantaneous for dashboard consumers.

Wide (Fully Flattened) Tables

Join all dimensions into the fact table, eliminating all joins at query time.

CREATE TABLE fact_orders_wide AS
SELECT f.*, p.name as product_name, p.category, p.brand,
       c.name as customer_name, c.segment, c.city,
       d.full_date, d.quarter, d.year, d.is_weekend,
       g.region, g.country
FROM fact_orders f
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_customer c ON f.customer_key = c.customer_key
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_geography g ON f.geo_key = g.geo_key;

This eliminates all joins for analysts, but the table is very wide and stores significant redundant data. Use wide tables when the primary consumers are non-technical analysts using drag-and-drop BI tools that struggle with joins, or when query latency requirements are extremely tight.

SLIDE 75: Data Modeling for Semi-Structured Data

Modern data systems increasingly deal with semi-structured data: JSON payloads from APIs, nested event data from mobile apps, and complex objects from NoSQL databases. Modeling semi-structured data for analytics requires deciding when to flatten nested structures into relational columns and when to keep them nested.

When to Flatten:

Flatten when the nested fields are frequently queried, filtered, or aggregated. Flattening makes these operations simple and fast.

// Raw event (semi-structured)
{
  "event_id": "e-12345",
  "user_id": "u-1001",
  "event_type": "purchase",
  "properties": {
    "product_id": "p-501",
    "amount": 1200.0,
    "currency": "USD",
    "payment_method": "credit_card"
  },
  "device": {
    "type": "mobile",
    "os": "iOS",
    "version": "17.2"
  }
}

-- Flattened table (pipeline extracts nested fields during Transform stage)
events_flat:
| event_id | user_id | event_type | product_id | amount | currency | payment_method | device_type | device_os |
| e-12345  | u-1001  | purchase   | p-501      | 1200   | USD      | credit_card    | mobile      | iOS       |

Flattened columns are indexable, filterable, and joinable using standard SQL. This is the default approach for fields that appear in WHERE clauses or GROUP BY expressions.

When to Keep Nested:

Keep nested when the structure is highly variable (different event types have different properties), rarely queried, or when flattening would create dozens of mostly-NULL columns.

-- Semi-structured column approach (BigQuery, Snowflake VARIANT, Spark StructType)
events:
| event_id | user_id | event_type | properties (JSON/VARIANT) |
| e-12345  | u-1001  | purchase   | {"product_id":"p-501","amount":1200,...} |
| e-12346  | u-1001  | page_view  | {"page_url":"/home","duration_ms":3500}  |

Different event types have completely different property schemas. Flattening would create a table with hundreds of columns, most of which are NULL for any given row. Keeping properties as a JSON/VARIANT column and querying with JSON path expressions is more practical.

-- Querying nested data in BigQuery
SELECT event_id, JSON_VALUE(properties, '$.amount') as amount
FROM events
WHERE event_type = 'purchase'
  AND CAST(JSON_VALUE(properties, '$.amount') AS FLOAT64) > 1000;

Schema Evolution Challenges:

Semi-structured data schemas change without warning. A mobile app update adds a new field to the event payload. A third-party API changes its response format. Pipelines that flatten semi-structured data must handle schema evolution gracefully: new fields should be captured (not silently dropped), removed fields should not break the pipeline, and type changes (a field that was a string becomes an integer) must be detected and handled.

This is why the ELT pattern (Section 2) is particularly valuable for semi-structured data: store the raw JSON in the Bronze layer, and apply flattening transformations in the Silver layer. When the schema changes, only the Silver transformation needs updating, and the raw data is preserved for backfilling.

SLIDE 76: Worked Example: Model an E-Commerce Analytics Warehouse

Requirements:

An e-commerce platform needs an analytics warehouse to answer questions about orders, products, customers, and business performance. The key analytical questions are:

• Revenue by product category, region, and time period (daily, monthly, quarterly)
• Customer lifetime value by acquisition channel and segment
• Product return rates by category and brand
• Average time from order to delivery by shipping method

Step 1: Identify the Facts

The business events that generate measurable data:

• Orders: a customer buys products (measures: quantity, revenue, discount, tax)
• Returns: a customer returns products (measures: return quantity, refund amount)

These are the two fact tables.

Step 2: Identify the Dimensions

The descriptive context for those events:

• Product: name, category, subcategory, brand, supplier
• Customer: name, segment, acquisition channel, city, country
• Date: full date, day of week, month, quarter, year, is_holiday
• Geography: city, state, country, region
• Shipping: method, carrier, estimated_days

Step 3: Define the Grain

fact_orders:  one row per order line item
  Why: product-level revenue analysis requires line-item detail

fact_returns: one row per return line item
  Why: product-level return rate analysis requires line-item detail

Step 4: Choose SCD Strategy

Product category and customer segment change over time and historical accuracy matters for trend analysis. Use SCD Type 2 for dim_product (category, subcategory) and dim_customer (segment, city). Use SCD Type 1 for corrections (typos in names, data entry errors).

Step 5: Add an Accumulating Snapshot for Order Lifecycle

The “average time from order to delivery” question requires tracking each order through multiple milestones:

fact_order_lifecycle:
| order_key | order_date | payment_date | ship_date | delivery_date | return_date |
| days_to_ship | days_to_deliver | shipping_key |

Grain: one row per order. Updated as the order progresses through each milestone.

Step 6: Summary

Fact tables:
  fact_orders          (transaction fact, grain: order line item)
  fact_returns         (transaction fact, grain: return line item)
  fact_order_lifecycle (accumulating snapshot, grain: one order)

Dimension tables:
  dim_product    (SCD Type 2 on category, subcategory)
  dim_customer   (SCD Type 2 on segment, city)
  dim_date       (static, pre-populated for 20 years)
  dim_geography  (static, rarely changes)
  dim_shipping   (static, rarely changes)

This model supports all four analytical questions from the requirements. Revenue analysis uses fact_orders joined to dim_product, dim_customer, dim_date, and dim_geography. Customer lifetime value uses fact_orders aggregated by customer_key. Return rate uses fact_returns joined to dim_product. Order-to-delivery time uses fact_order_lifecycle joined to dim_shipping.

SLIDE 77: Wrong vs Right: Data Modeling Anti-Patterns

SLIDE 78: Interview Practice and Common Mistakes

Common Interview Mistakes:

Practice Questions:

1. “Design the data model for a ride-sharing analytics platform. The business needs: revenue by city and time, driver performance metrics, average ride duration by distance band, and surge pricing effectiveness analysis.” (Key decisions: fact_rides at ride-level grain, dim_driver with SCD Type 2 for rating tier, dim_city, dim_date, periodic snapshot for daily driver metrics)
2. “An analyst complains that the revenue numbers in the dashboard don’t match the numbers from a direct database query. Walk me through how you’d investigate.” (Key areas: grain mismatch, SCD issues attributing revenue to wrong dimension values, mixed grain in fact table, incorrect join producing duplicates)
3. “The product team wants to add a new ‘subscription tier’ attribute to customer analytics. How would you modify the existing data model?” (Key approach: add subscription_tier to dim_customer as an SCD Type 2 attribute, backfill historical fact rows with the correct customer_key versions)

SLIDE 79: Section Cheat Sheet

Concepts Covered:

Common Interview Mistakes:

Key Numbers to Remember:

Typical Dimension Sizes:
  dim_date:       ~7,300 rows (20 years)
  dim_geography:  ~50,000 rows (cities worldwide)
  dim_product:    1-10M rows (varies by business)
  dim_customer:   1-100M rows (varies by business)
  Dimensions with SCD Type 2: multiply by ~1.5x for version history

Typical Fact Table Sizes:
  E-commerce (50M orders/day × 3 items): ~150M rows/day, ~55B rows/year
  Clickstream (100M users × 20 events):  ~2B rows/day, ~730B rows/year
  Ride-sharing (50M rides/day):           ~50M rows/day, ~18B rows/year

Star Schema Query Performance:
  Fact-to-dimension join (fact: 1B rows, dim: 1M rows): seconds (broadcast join)
  Fact scan without partitioning: minutes (full table scan)
  Fact scan with date partitioning: seconds (partition pruning)

How This Connects to Other Sections: