Gowtham Potureddi

Posted on Aug 15

Data Modeling Interview Questions: Dimensional, Normalization & Case Studies

#python #sql #interview #dataengineering

Most data modeling interview questions are not really asking you to recite the definition of third normal form — they are asking whether you can turn a fuzzy business sentence into a set of tables that answer it, defend the grain you chose, and keep the model honest as the data changes underneath it. That is the whole skill, and it is the reason strong SQL writers still stumble here: writing a query against tables that already exist is a different muscle from deciding what the tables should be. The interviewer hands you a domain — orders, subscriptions, ad impressions — and watches how you decompose it, which keys you reach for, whether you know when to normalize and when to build a star, and how you handle a customer who moves house without silently rewriting history.

This guide walks the modeling round the way it is actually conducted, end to end. It starts with a repeatable framework — grain, entities, keys, then the normalize-versus-dimensionalize decision — so you never stare at a blank whiteboard again. From there it goes deep on the four topics that come up in almost every loop: normalization in sql (1NF through 3NF, with real DDL and the anomalies each form removes), dimensional modeling with the trade-off between a star schema vs snowflake schema, fact and dimension tables and the three fact-table flavours, and slowly changing dimensions with surrogate keys and a working SQL MERGE. It closes with a full case study — modeling a subscriptions and events domain from business questions down to physical tables — because the senior-level version of this interview is one long case study, and knowing the vocabulary is not the same as being able to run the play.

When you want hands-on reps alongside the reading, drill schema design on the SQL practice library →, rehearse warehouse modeling on the dimensional modeling practice library →, and pressure-test whole schemas on the data system design practice library →.

On this page

How data modeling is interviewed — a framework
Normalization — 1NF, 2NF, 3NF, and when to denormalize
Dimensional modeling — star vs snowflake, facts and dimensions
Slowly changing dimensions — Type 1, 2, 3 and surrogate keys
Case study — modeling a subscriptions and events domain
Cheat sheet — data modeling interview recipes
Frequently asked questions
Practice on PipeCode

1. How data modeling is interviewed — a framework

The modeling round tests judgment, not memorised DDL — run one repeatable loop every time

The framing that settles your nerves before the whiteboard: a data modeling interview is a structured design conversation where the interviewer gives you a business domain and evaluates whether you can pick a grain, identify entities and keys, choose between a normalized and a dimensional design for the workload, and handle change over time — not whether you can recite Codd's normal forms verbatim. The candidates who pass are not the ones with the most memorised definitions; they are the ones who narrate a consistent loop out loud so the interviewer can follow their reasoning and interrupt with constraints.

The five-step loop — say it out loud, in order. Every good modeling answer walks the same path, and naming the steps as you go is what makes you sound senior.

Grain first. Declare, in one sentence, what a single row of your central table represents ("one row per order line item," "one row per subscription per day"). Everything downstream depends on this, and getting it wrong is the single most common failure.
Entities and attributes. Identify the nouns (customer, product, order, payment) and the facts you measure (quantity, amount, duration). Nouns become dimensions or reference tables; measurements become facts.
Keys. Choose natural keys (business identifiers) and, in a warehouse, surrogate keys (system-generated integers) so the model survives source-system changes and lets you version history.
Normalize vs dimensionalize. Decide the shape based on the workload: normalized (3NF) for transactional write-heavy systems, dimensional (star) for analytical read-heavy systems. This is the fork the interview keeps returning to.
Slowly changing dimensions. Decide how attributes that change over time (a customer's address, a product's price tier) are recorded — overwrite, version, or keep a prior value — because "how do you handle history?" is the follow-up in nearly every loop.

Three layers the interviewer expects you to separate. Conflating them is a classic tell that you have only ever queried, never modeled.

Conceptual model. The entities and their relationships in business language, no columns or types — "a customer places many orders; an order has many line items."
Logical model. Tables, columns, keys, and relationships, still independent of any specific database — this is where normal forms and star schemas live.
Physical model. The actual DDL: data types, partitioning, clustering, indexes, and storage choices tuned to the target engine.

OLTP vs OLAP — the axis behind every choice. The reason the same domain gets two different models is the workload.

OLTP (transactional). Many small concurrent writes, row-level lookups, strict integrity — favours normalization to avoid redundancy and update anomalies.
OLAP (analytical). Large scans, aggregations, few writers, read latency matters — favours dimensional denormalization (star schemas) so a query touches one fact and a few dimensions instead of a dozen joined tables.

What separates a pass from a fail in this round.

Can you state the grain in one sentence before drawing a single table? — the highest-leverage habit.
Do you choose the shape from the workload (OLTP → normalize, OLAP → star) instead of defaulting to whatever you use at work? — the judgment signal.
Can you handle change over time with the right SCD type without hand-waving? — the follow-up that separates mid from senior.

Worked example — applying the grain-first framework to an orders domain

Detailed explanation. The most reliable way to open any modeling question is to run the loop on a simple, familiar domain so the interviewer sees your process, then let them add constraints. Take "model an e-commerce orders domain for analytics." Do not start drawing tables; start by declaring the grain, because the grain decides how every measure aggregates and whether your later joins even make sense.

Grain. "One row per order line item" — finer than "one row per order," because a customer buys several products per order and you want per-product analysis.
Entities. customer, product, order, and the measurement order_line (quantity, unit price, discount).
Keys. Natural keys order_id, product_id, customer_id; surrogate integer keys on each dimension for the warehouse.
Shape. Analytics workload → a star: one fact_order_line surrounded by dim_customer, dim_product, dim_date.

Question. For an analytical orders model, what grain do you declare, and what does a single fact row contain?

Input.

Business question to answer	Implication for the model
Revenue by product by month	need product + date + a revenue measure
Units per order	need a quantity measure at line grain
Repeat-purchase rate by customer	need a customer key on every fact row
Discount impact on margin	need discount as an additive measure

Code.

Grain decision:
  candidate A: one row per ORDER            -> loses per-product detail (too coarse)
  candidate B: one row per ORDER LINE ITEM  -> supports product-level revenue  [CHOOSE]

fact_order_line (grain = one order line item)
  order_line_sk        surrogate key
  date_sk    -> dim_date          (order date)
  customer_sk-> dim_customer
  product_sk -> dim_product
  quantity             additive
  gross_amount         additive
  discount_amount      additive
  net_amount           additive   (gross - discount)

Step-by-step trace.

Read the business questions; the finest one ("revenue by product") forces a per-product grain, so "one row per order line item" wins over "one row per order."
Split nouns from measures: customer/product/date are dimensions; quantity/gross/discount/net are additive measures on the fact.
Attach a surrogate key to each dimension so the fact stores small integers and dimensions can hold history later.
Confirm the grain answers all four questions: it does, because every measure is stored at line grain and can be summed up to order, customer, product, or month.

Output:

Question	Answered by
Revenue by product by month	`SUM(net_amount)` grouped by `product`, `month`
Units per order	`SUM(quantity)` grouped by `order_id`
Repeat-purchase rate	distinct orders per `customer_sk` over time
Discount impact	`SUM(discount_amount)` vs `SUM(gross_amount)`

Why this works — concept by concept:

Grain first — declaring "one row per line item" before drawing anything fixes the single decision every downstream join and aggregation depends on; a wrong grain silently double-counts or loses detail.
Additive measures — storing quantity and amounts as additive facts means every business question is a SUM with a GROUP BY, never a special case.
Surrogate keys on dimensions — small integer keys keep the fact narrow and let dimensions version history later without touching the fact.
Workload-driven shape — because the workload is analytical, a star (one fact, a few dimensions) beats a fully normalized order/customer/product web of joins for read speed.
Cost — a narrow fact keyed by integers scans fewer bytes and joins fewer, smaller dimensions, so the analytical queries are cheap and fast at warehouse scale.

Worked example — choosing normalized (3NF) vs dimensional (star) by workload

Detailed explanation. The fork the interview keeps circling back to is "normalize or denormalize?", and the honest answer is it depends on the workload — but you must be able to defend which way you would go and why. The mental model: normalization optimises for correct, non-redundant writes; dimensional modeling optimises for fast, simple reads. When the interviewer changes the scenario from "the app that takes the order" to "the dashboard that reports on orders," the right model changes with it.

Normalize (3NF) when the system is transactional: many concurrent writers, integrity is paramount, and you must avoid storing the same fact twice.
Dimensionalize (star) when the system is analytical: reads dominate, joins should be few and shallow, and some controlled redundancy in dimensions is a feature, not a bug.
Both, in sequence is the realistic answer: a normalized OLTP source feeds an ETL/ELT job that reshapes it into a dimensional warehouse.

Question. The same "orders" domain is asked about twice — first as the checkout system, then as the analytics warehouse. Which shape for each, and why?

Input.

System	Dominant workload	Integrity need	Right shape
Checkout app (OLTP)	many small writes	strict, no redundancy	normalized 3NF
Analytics warehouse (OLAP)	large read scans	reporting consistency	dimensional star
Reporting API cache	repeated identical reads	derived, refreshable	denormalized / MV

Code.

OLTP (checkout)  -> normalized:
  customers(customer_id PK, name, email)
  addresses(address_id PK, customer_id FK, line1, city, ...)
  orders(order_id PK, customer_id FK, order_ts, status)
  order_items(order_id FK, product_id FK, qty, price)   -- no repeated customer data

OLAP (warehouse) -> star (denormalized dims):
  fact_order_line(date_sk, customer_sk, product_sk, qty, net_amount)
  dim_customer(customer_sk, customer_id, name, city, segment)  -- attributes folded in
  dim_product(product_sk, product_id, name, brand, category)
  dim_date(date_sk, date, month, quarter, year)

Step-by-step trace.

For the checkout system, writes dominate and the same customer places many orders, so you normalize: customer attributes live once in customers, referenced by foreign key, preventing update anomalies.
For the warehouse, reads dominate and analysts join the fact to a handful of dimensions, so you denormalize product brand/category into dim_product rather than snowflaking them out.
The bridge between them is ETL: the normalized source is transformed into the star, resolving keys to surrogates and folding reference attributes into dimensions.
If a specific dashboard reruns the same aggregation constantly, you add a further-denormalized materialized view on top of the star.

Output:

Layer	Shape	Why
Source (OLTP)	3NF normalized	correct, non-redundant writes
Warehouse (OLAP)	star schema	few shallow joins, fast reads
Serving	materialized view	repeated aggregation reuse

Rule of thumb. Match the shape to the workload: normalize where you write, denormalize into a star where you read, and let ETL move data between the two — "normalize for integrity, denormalize for speed" is the sentence to say out loud.

2. Normalization — 1NF, 2NF, 3NF, and when to denormalize

Normalization removes redundancy so writes cannot create contradictions — and you must know exactly which anomaly each normal form kills

The invariant to burn in: normalization is the process of organising columns and tables so that each fact is stored exactly once, eliminating the update, insertion, and deletion anomalies that redundancy causes — and each normal form removes a specific kind of dependency, so an interviewer wants you to name the dependency, not just the number. If you can point at a table and say "this violates 2NF because product_name depends on only part of the composite key," you have already passed the normalization portion.

The vocabulary the interviewer is listening for.

Functional dependency. A → B means a value of A determines exactly one value of B (order_id → order_date). Normalization is the art of making every non-key column depend on "the key, the whole key, and nothing but the key."
Candidate key / primary key. The minimal set of columns that uniquely identifies a row. A composite key is made of more than one column.
Partial dependency. A non-key column depends on only part of a composite key — the thing 2NF forbids.
Transitive dependency. A non-key column depends on another non-key column (zip → city), not directly on the key — the thing 3NF forbids.

The three anomalies redundancy causes — the "why" behind normalization.

Update anomaly. The same fact stored in many rows (a customer's city on every order) must be changed in every copy; miss one and the data contradicts itself.
Insertion anomaly. You cannot record a fact because unrelated data is missing (you cannot add a new product until someone orders it, if products only live in the orders table).
Deletion anomaly. Deleting one row destroys an unrelated fact (removing the last order for a product erases the product's existence).

The normal forms, each as a one-line test.

1NF — atomic values, no repeating groups. Every cell holds a single value; no comma-separated lists, no item1/item2/item3 columns. Each row is unique.
2NF — 1NF plus no partial dependencies. Every non-key column depends on the whole composite key, not part of it. (Tables with a single-column key are automatically in 2NF.)
3NF — 2NF plus no transitive dependencies. No non-key column depends on another non-key column; reference data (city from zip, category from product) moves to its own table.
BCNF — a stricter 3NF. For every dependency A → B, A must be a candidate key. It matters only in edge cases with overlapping candidate keys, and mentioning it signals depth.

When to stop normalizing — the denormalization trade-off. More normalization means fewer anomalies but more joins; the interview rewards knowing the balance.

Normalize for OLTP write paths and anywhere an anomaly would corrupt data.
Denormalize deliberately for read-heavy analytics — a star schema is a controlled denormalization where redundancy lives in dimensions and is rebuilt by ETL, so there is no update anomaly (nobody hand-edits a dimension; the pipeline rebuilds it).
Never denormalize an OLTP write path "for speed" without a measured reason — you are trading correctness for a micro-optimisation.

Decomposing a wide orders table to 3NF — a worked teaching example

Detailed explanation. The classic normalization question hands you a single spreadsheet-shaped table with everything jammed in and asks you to normalize it step by step, naming each violation. The key skill is to move one normal form at a time and say which dependency you are fixing, because "just make it 3NF in one leap" hides your reasoning.

Start: one orders_flat table with repeating item columns and customer/product attributes duplicated on every row.
1NF: split the repeating item group into one row per item.
2NF: move columns that depend on only part of the (order_id, product_id) key into their own tables.
3NF: move transitively-dependent reference data (product category, customer city) into reference tables.

Question. Normalize orders_flat(order_id, order_date, customer_id, customer_name, customer_city, product_id, product_name, category, qty, unit_price) to 3NF, naming each violation.

Input.

Column	Depends on	Violation it causes
`customer_name`, `customer_city`	`customer_id` (not the key)	duplicated on every order row
`product_name`, `category`	`product_id` (part of key)	partial dependency (2NF)
`category`	`product_id` via `product_name`	reference data mixed in (3NF)
`order_date`	`order_id` (part of key)	partial dependency (2NF)

Code.

-- 1NF: atomic rows — one row per (order, product). (Assume the repeating
--      item group has already been split so each row is a single line item.)

-- 2NF: remove partial dependencies on the composite key (order_id, product_id).
--      order_date depends on order_id only; product_name depends on product_id only.
CREATE TABLE orders (
  order_id     BIGINT PRIMARY KEY,
  order_date   DATE        NOT NULL,
  customer_id  BIGINT      NOT NULL REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
  order_id     BIGINT NOT NULL REFERENCES orders(order_id),
  product_id   BIGINT NOT NULL REFERENCES products(product_id),
  qty          INT    NOT NULL,
  unit_price   NUMERIC(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id)          -- composite key, no partials left
);

-- 3NF: remove transitive dependencies. category depends on product, not on the
--      order; city depends on customer, not on the order.
CREATE TABLE products (
  product_id   BIGINT PRIMARY KEY,
  product_name TEXT   NOT NULL,
  category_id  INT    NOT NULL REFERENCES categories(category_id)
);
CREATE TABLE categories (
  category_id  INT  PRIMARY KEY,
  category     TEXT NOT NULL
);
CREATE TABLE customers (
  customer_id  BIGINT PRIMARY KEY,
  customer_name TEXT  NOT NULL,
  city         TEXT   NOT NULL
);

Step-by-step trace.

1NF: ensure each row holds atomic values and one line item — no product1, product2 columns and no comma-separated lists.
2NF: the key is (order_id, product_id); order_date depends on order_id alone and product_name on product_id alone — both partial — so they move to orders and products respectively.
3NF: category depends on product_id transitively (through the product), and city depends on customer_id, so each moves to its own reference table.
The result stores each fact once: a customer's city is updated in exactly one row, a product's category in exactly one row, and the fact of an order line in exactly one place.

Output:

Table	Grain	Stores each fact once?
`orders`	one order	order_date, customer link
`order_items`	one line item	qty, price
`products` / `categories`	one product / category	product + category reference
`customers`	one customer	name, city

Rule of thumb. Normalize one form at a time and name the dependency you are removing — partial dependency → 2NF, transitive dependency → 3NF — because the interviewer scores the reasoning, not just the final tables.

A controlled denormalization for read performance — a worked teaching example

Detailed explanation. The mirror-image question is "when would you undo normalization?" — and the strong answer is a controlled denormalization for a read-heavy path, rebuilt by a pipeline so it cannot drift. The canonical case is a reporting table that would otherwise join five normalized tables on every dashboard load; you precompute the joined, denormalized shape and refresh it, accepting redundancy because nothing hand-edits it.

Trigger: a read path repeats the same multi-table join thousands of times a day.
Move: fold the reference attributes into a wide reporting table (or a star dimension) built by ETL.
Guardrail: the redundancy is safe only because the pipeline is the single writer — there is no update anomaly when no human updates the copy.

Question. A dashboard joins order_items → orders → customers → products → categories on every load and is slow. Denormalize safely.

Input.

Fact	Value
Read pattern	daily revenue by category + customer city
Joins per query	5 normalized tables
Write pattern	none (report only)
Refresh tolerance	hourly is fine

Code.

-- Controlled denormalization: one wide, pipeline-owned reporting table.
-- Redundant category/city are safe because ONLY the refresh job writes here.
CREATE TABLE rpt_order_line AS
SELECT
  oi.order_id,
  o.order_date,
  c.city              AS customer_city,     -- folded in (redundant on purpose)
  cat.category        AS product_category,  -- folded in
  oi.qty,
  oi.qty * oi.unit_price AS line_revenue
FROM order_items oi
JOIN orders     o   ON o.order_id    = oi.order_id
JOIN customers  c   ON c.customer_id = o.customer_id
JOIN products   p   ON p.product_id  = oi.product_id
JOIN categories cat ON cat.category_id = p.category_id;

-- Dashboard now reads ONE table, no joins:
SELECT order_date, product_category, customer_city, SUM(line_revenue) AS revenue
FROM rpt_order_line
WHERE order_date >= DATE '2026-08-01'
GROUP BY order_date, product_category, customer_city;

Step-by-step trace.

The normalized model is correct for writes but forces a 5-table join on every dashboard hit — the read cost is the problem, not the write cost.
A refresh job precomputes the join once per hour into rpt_order_line, folding city and category in as redundant columns.
Because only the refresh job writes the table, the redundant columns cannot go stale mid-flight or be edited inconsistently — the update anomaly that normalization prevents cannot occur here.
The dashboard query drops from five joins to a single scan with a GROUP BY.

Output:

Design	Joins per query	Anomaly risk
Fully normalized	5	none, but slow reads
Controlled denormalized (pipeline-owned)	0	none (single writer)

Rule of thumb. Denormalize only on read paths and only when a pipeline owns the copy — redundancy is safe precisely when no human ever updates it by hand.

Interview scenario on normalization

You are shown a student_courses(student_id, student_name, student_major, course_id, course_title, instructor, instructor_email, grade) table and asked: it has update and insertion anomalies — normalize it to 3NF and explain what each step fixes. The interviewer wants the decomposition and the dependency names.

Solution Using stepwise decomposition to 3NF

Answer choices (as the interviewer might frame "which decomposition is correct?").

A. Leave it as one table but add indexes to speed updates.
B. Split into students, courses, enrollments — but keep instructor_email in courses.
C. Split into students, courses, instructors, and an enrollments fact holding (student_id, course_id, grade).
D. Split by column type (all text in one table, all ids in another).

Code.

-- Correct 3NF decomposition (choice C)
CREATE TABLE students (
  student_id   BIGINT PRIMARY KEY,
  student_name TEXT NOT NULL,
  major        TEXT NOT NULL
);
CREATE TABLE instructors (
  instructor_id   BIGINT PRIMARY KEY,
  instructor_name TEXT NOT NULL,
  instructor_email TEXT NOT NULL          -- email depends on instructor, not course
);
CREATE TABLE courses (
  course_id     BIGINT PRIMARY KEY,
  course_title  TEXT NOT NULL,
  instructor_id BIGINT NOT NULL REFERENCES instructors(instructor_id)
);
CREATE TABLE enrollments (
  student_id BIGINT NOT NULL REFERENCES students(student_id),
  course_id  BIGINT NOT NULL REFERENCES courses(course_id),
  grade      TEXT,
  PRIMARY KEY (student_id, course_id)      -- the enrollment fact, at its grain
);

Step-by-step trace.

The composite key of the original is (student_id, course_id). student_name/major depend on student_id only, course_title/instructor on course_id only — partial dependencies, so 2NF splits students and courses out.
instructor_email depends on the instructor, which depends on the course — a transitive dependency — so 3NF moves instructors into their own table, referenced by courses.
What remains at the (student_id, course_id) grain is the enrollment fact plus its grade; that becomes the enrollments table.
Choice B is rejected because keeping instructor_email in courses leaves the transitive dependency (email duplicated for every course an instructor teaches) — still not 3NF.

Output:

Table	Removes which anomaly
`students`	update anomaly on student major
`instructors`	update anomaly on instructor email; insertion anomaly (add an instructor before any course)
`courses`	deletion anomaly (a course survives losing its last enrollment)
`enrollments`	isolates the grade fact at its true grain

Why this works — concept by concept:

Partial-dependency removal (2NF) — splitting attributes that depend on only part of (student_id, course_id) into students and courses is exactly what kills the per-row duplication of names and titles.
Transitive-dependency removal (3NF) — moving instructor_email behind an instructors table means the email is stored once, so an email change is a one-row update instead of a scatter-write.
Enrollment as its own grain — the surviving (student_id, course_id, grade) table is the true many-to-many fact; recognising it is the modeling insight the question rewards.
Anomaly framing — naming each fixed anomaly (update/insertion/deletion) is what turns a mechanical split into a defensible design answer.
Cost — 3NF trades a few extra joins on read for single-copy writes; for a transactional enrollment system that is the correct trade, and a warehouse would later denormalize it back into a star for reporting.

SQL
Topic — sql
Schema design and normalization problems (SQL)

Practice →

Design Topic — design Relational schema and data model design problems

Practice →

3. Dimensional modeling — star vs snowflake, facts and dimensions

A star schema puts one fact table at the centre and denormalized dimensions around it — and the interview tests whether you can pick the fact type and the right level of dimension normalization

The invariant: dimensional modeling (Kimball) organises analytical data as fact tables — the measurements, at a declared grain — surrounded by dimension tables that hold the descriptive context you filter and group by; the interview probes whether you can choose the fact-table type, keep dimensions conformed across facts, and decide between a star (denormalized dimensions) and a snowflake (normalized dimensions). Get the grain and the fact type right and the rest of the star follows almost mechanically.

Fact tables — the measurements. Know the three types cold.

Transaction fact. One row per event/transaction at the finest grain (one sale line, one click, one payment). The most common and most flexible; you aggregate up from it.
Periodic snapshot fact. One row per entity per regular period (account balance per day, inventory per store per day, MRR per day). Answers "what was the state at each period."
Accumulating snapshot fact. One row per process instance, with multiple date columns updated as the instance moves through milestones (order placed → shipped → delivered; signup → activated → churned). Answers "how long between steps."

Measures — how a number aggregates decides how you store it.

Additive. Sums across every dimension (revenue, quantity). The easy, preferred case.
Semi-additive. Sums across some dimensions but not time (account balance, headcount) — you average or take end-of-period over time.
Non-additive. Cannot be summed at all (ratios, percentages, unit price) — store the additive components and compute the ratio at query time.

Dimension tables — the context.

Denormalized by design. A dimension folds a hierarchy (brand → category → department) into one wide table; the redundancy is intentional and rebuilt by ETL.
Surrogate keys. Each dimension has a system-generated integer primary key; the fact stores that key, not the natural business key, which lets dimensions version history (SCD).
Degenerate dimension. A dimension attribute with no other columns (an order_id on the fact used for grouping) stays on the fact itself — no separate table.
Conformed dimension. The same dim_date or dim_customer shared by multiple facts, so "revenue" and "support tickets" can be sliced by the identical customer segments — this is what makes a warehouse a warehouse, not a pile of marts.
The bus matrix. A grid of business processes (rows) vs dimensions (columns); it is how you plan which conformed dimensions serve which facts, and naming it in an interview signals Kimball fluency.

Star vs snowflake — the trade-off, stated plainly.

Star schema. Dimensions are denormalized (flat, wide). Fewer joins, simpler SQL, faster reads, easier for BI tools — the default for analytics.
Snowflake schema. Dimensions are normalized into sub-tables (product → brand → category as separate tables). Less storage redundancy and easier dimension maintenance, but more joins and more complex queries.
When snowflake earns its keep. Only when a dimension hierarchy is large, changes often, or is shared in a way that makes the redundancy genuinely costly; otherwise the star's simplicity wins. "Default to star; snowflake a dimension only when a big, volatile hierarchy justifies the extra joins."

Designing a retail sales star schema — a worked teaching example

Detailed explanation. The bread-and-butter dimensional question is "design a star schema for retail sales." Run the loop: declare the grain, put the additive measures on the fact, surround it with conformed dimensions keyed by surrogates. The measures are additive (quantity, amount), which is the easy, ideal case.

Grain: one row per product per sales transaction (a line on a receipt).
Measures: quantity, unit_price (non-additive, so also store sales_amount additive), discount_amount, sales_amount.
Dimensions: dim_date, dim_product, dim_store, dim_customer — each with a surrogate key.

Question. Design the fact and dimension tables for a retail point-of-sale star at line-item grain.

Input.

Element	Choice
Grain	one product per sales transaction
Additive measures	quantity, sales_amount, discount_amount
Dimensions	date, product, store, customer
Degenerate dimension	`receipt_id` (kept on the fact)

Code.

CREATE TABLE dim_date (
  date_sk   INT  PRIMARY KEY,     -- surrogate (e.g. 20260814)
  date      DATE NOT NULL,
  day_name  TEXT, month INT, quarter INT, year INT, is_weekend BOOLEAN
);
CREATE TABLE dim_product (
  product_sk INT PRIMARY KEY,     -- surrogate
  product_id TEXT NOT NULL,       -- natural key
  name TEXT, brand TEXT, category TEXT, department TEXT   -- hierarchy folded in (star)
);
CREATE TABLE dim_store (
  store_sk INT PRIMARY KEY, store_id TEXT, name TEXT, city TEXT, region TEXT
);
CREATE TABLE dim_customer (
  customer_sk INT PRIMARY KEY, customer_id TEXT, name TEXT, segment TEXT
);

CREATE TABLE fact_sales (
  date_sk      INT NOT NULL REFERENCES dim_date(date_sk),
  product_sk   INT NOT NULL REFERENCES dim_product(product_sk),
  store_sk     INT NOT NULL REFERENCES dim_store(store_sk),
  customer_sk  INT NOT NULL REFERENCES dim_customer(customer_sk),
  receipt_id   BIGINT NOT NULL,          -- degenerate dimension (no table)
  quantity     INT NOT NULL,             -- additive
  sales_amount NUMERIC(12,2) NOT NULL,   -- additive
  discount_amount NUMERIC(12,2) NOT NULL -- additive
);

Step-by-step trace.

Declare the grain — one row per product per transaction — so every measure is stored at line-item level and aggregates cleanly upward.
Put only additive measures on the fact; unit_price is non-additive, so it is derived at query time from sales_amount / quantity rather than stored as a summable column.
Give each dimension a surrogate key and fold the product hierarchy (brand/category/department) directly into dim_product — a star, not a snowflake.
Keep receipt_id on the fact as a degenerate dimension because it groups line items into a basket but has no descriptive attributes of its own.

Output:

Query	SQL shape
Revenue by category by month	join `dim_product`, `dim_date`; `SUM(sales_amount)`
Basket size	`COUNT(*)` per `receipt_id`
Revenue by region	join `dim_store`; `SUM(sales_amount)`

Rule of thumb. Declare the grain, store only additive measures on the fact, fold hierarchies into denormalized dimensions (star), and keep transaction identifiers as degenerate dimensions on the fact.

Star vs snowflake for a product hierarchy — a worked teaching example

Detailed explanation. The follow-up is almost always "would you snowflake the product dimension?" The teaching point: snowflaking normalizes a dimension's hierarchy into child tables, which reduces redundancy but adds joins — so you snowflake only when the hierarchy is large and volatile enough that the maintenance win beats the query-complexity cost.

Star product dim: dim_product(product_sk, name, brand, category, department) — brand/category repeated across products.
Snowflake product dim: dim_product → dim_brand → dim_category → dim_department as separate joined tables.
Decision: star unless the hierarchy is huge and frequently restructured.

Question. For a 50-attribute product dimension with a rarely-changing 3-level hierarchy, star or snowflake? Show both and pick.

Input.

Factor	Star	Snowflake
Joins to filter by category	1 (fact→dim)	3 (fact→dim→brand→category)
Storage redundancy	higher	lower
Hierarchy change frequency	rare here	—
BI-tool friendliness	high	lower

Code.

-- STAR: one wide, denormalized dimension (chosen here)
CREATE TABLE dim_product (
  product_sk INT PRIMARY KEY,
  product_id TEXT, name TEXT,
  brand TEXT, category TEXT, department TEXT   -- hierarchy folded in
);
-- SELECT category, SUM(sales_amount) FROM fact_sales f
--   JOIN dim_product p ON p.product_sk = f.product_sk GROUP BY category;  -- 1 join

-- SNOWFLAKE: normalized hierarchy (only if the hierarchy is large + volatile)
CREATE TABLE dim_department (department_sk INT PRIMARY KEY, department TEXT);
CREATE TABLE dim_category (category_sk INT PRIMARY KEY, category TEXT,
                           department_sk INT REFERENCES dim_department(department_sk));
CREATE TABLE dim_brand (brand_sk INT PRIMARY KEY, brand TEXT,
                        category_sk INT REFERENCES dim_category(category_sk));
CREATE TABLE dim_product_sf (product_sk INT PRIMARY KEY, name TEXT,
                             brand_sk INT REFERENCES dim_brand(brand_sk));
-- category filter now traverses product->brand->category: 3 joins

Step-by-step trace.

In the star, filtering by category is a single fact-to-dimension join, and BI tools flatten it trivially — the cost is brand/category values repeating across product rows.
In the snowflake, category lives once in dim_category, saving storage and easing a mass hierarchy rename — but every category query now traverses three joins.
Because the hierarchy here is only 3 levels and rarely restructured, the redundancy is cheap and the join simplicity of the star wins.
You would flip to snowflake only if the hierarchy were deep, huge, and frequently reorganised, where maintaining one normalized copy outweighs the extra joins.

Output:

Criterion	Winner here
Query simplicity / speed	star
Storage redundancy	snowflake
Overall for this dimension	star (small, stable hierarchy)

Rule of thumb. Default to a star; reach for a snowflake only when a dimension's hierarchy is large and changes often enough that removing the redundancy is worth the extra joins.

Interview scenario on dimensional modeling

Design an analytics mart for a retail chain that must answer "revenue by product category by month" and "same-store sales year over year," while sharing a single date and store definition with the separate support_tickets mart. Minimise joins for analysts and keep the two marts comparable.

Solution Using a star schema with conformed date and store dimensions

Answer choices.

A. One fully-normalized 3NF schema shared by reporting and support.
B. Two independent marts, each with its own private date and store tables.
C. A star fact_sales plus conformed dim_date and dim_store shared with the support mart.
D. A single giant denormalized table with every column in one place.

Code.

Elimination:
A  3NF for analytics -> many joins per query, slow reads, not the pattern   [reject]
B  private dims per mart -> "same store"/"same month" differ across marts   [reject: not comparable]
D  one wide table -> no reuse, no conformed dims, unmanageable history       [reject]
C  star + conformed dim_date/dim_store shared across marts                   [ACCEPT]

Step-by-step trace.

Constraints: "minimise joins for analysts" → star (denormalized dims); "share one date/store definition with the support mart" → conformed dimensions.
A is normalized, forcing many joins on every analytical query — wrong workload shape — eliminate.
B gives each mart its own date and store tables, so "same-store" and "same month" are defined twice and the two marts stop being comparable — eliminate on the conformance requirement.
D collapses everything into one table, losing dimension reuse and making changing-attribute history unmanageable — eliminate.
C builds fact_sales at line grain with additive measures, surrounded by dim_date, dim_product, dim_store; dim_date and dim_store are conformed — the identical tables the support mart also joins — so both marts slice by the same months and stores.

Output:

Requirement	Mechanism
Few joins for analysts	star schema
Comparable across marts	conformed `dim_date`, `dim_store`
Revenue by category	`dim_product` hierarchy folded into the star
YoY same-store sales	`dim_date` year + `dim_store` on one fact

Why this works — concept by concept:

Star for read speed — one fact plus a few denormalized dimensions means each analytical query is a shallow join, which is the entire point of dimensional modeling for OLAP.
Conformed dimensions — sharing the exact dim_date and dim_store across the sales and support marts is what makes cross-process comparison valid; private dimensions would silently define "store" two ways.
Additive measures at line grain — storing revenue additively at the finest grain lets "by category," "by month," and "same-store YoY" all be simple SUM ... GROUP BY rollups.
Bus matrix thinking — planning which facts share which conformed dimensions is the Kimball discipline that keeps a warehouse coherent instead of a pile of incompatible marts.
Cost — a narrow surrogate-keyed fact scans and joins cheaply, and conformed dimensions are built once and reused, so both storage and query cost stay low as marts multiply.

Design
Topic — dimensional-modeling
Star schema and dimensional modeling problems

Practice →

SQL Topic — sql Fact-and-dimension join and aggregation problems (SQL)

Practice →

4. Slowly changing dimensions — Type 1, 2, 3 and surrogate keys

When a dimension attribute changes, your model must decide whether to forget, remember, or keep one prior value — and surrogate keys are what make "remember" possible

The invariant: a slowly changing dimension (SCD) is a dimension whose attributes change occasionally over time, and the SCD "type" is your policy for what happens to history when a value changes — Type 1 overwrites (no history), Type 2 adds a new versioned row (full history), Type 3 keeps a single prior value in an extra column — and Type 2, the one interviewers care about most, is only possible because the fact joins on a surrogate key, not the natural key. If you can explain why Type 2 needs a surrogate key, you have understood the mechanism, not just the label.

Surrogate keys vs natural keys — the enabling idea.

Natural key. The business identifier (customer_id = C-1001). It stays constant across versions of the same customer.
Surrogate key. A system-generated integer (customer_sk = 88213) that identifies one version of the row. When the customer's address changes under Type 2, you insert a new row with a new surrogate key but the same natural key.
Why it matters. The fact stores customer_sk, so a sale made before the move points at the old version (old address) and a sale after the move points at the new version — history is preserved automatically, without ever editing a fact row.

The SCD types — each as a one-line policy.

Type 0 — retain original. The attribute never changes (date of birth, original signup source); ignore any updates.
Type 1 — overwrite. Replace the old value in place. No history; the dimension always shows the current value. Cheap; correct when history is irrelevant (fixing a typo).
Type 2 — add new row. Insert a new versioned row with a new surrogate key and effective-dating columns (effective_from, effective_to, is_current). Full history; facts join to the version that was current at the time. The default for anything you must report "as it was."
Type 3 — add new column. Keep current_value and previous_value columns. Only the single most recent prior value is retained; useful for "compare to previous" without full history.
Type 4 / Type 6 (mention for depth). Type 4 keeps current values in the main dim and history in a separate history table; Type 6 combines 1+2+3 (a current column and versioned rows). Naming these signals seniority.

Late-arriving data — the senior follow-up.

Late-arriving dimension. A fact references a dimension member that has not loaded yet (an order for a customer the CRM has not synced). Insert a placeholder/inferred dimension row with the natural key and unknown attributes, so the fact can load; backfill the attributes when the real record arrives.
Late-arriving fact. A fact arrives with an old event date and must be attached to the dimension version that was current on that event date, not the version current today — so you look up the surrogate key by natural key and effective-date range.

Implementing SCD Type 2 with a MERGE — a worked teaching example

Detailed explanation. The single most-asked SCD question is "implement Type 2 for a customer dimension." The mechanics: keep effective_from, effective_to, and is_current on the dimension; when a tracked attribute changes, expire the current row (set effective_to and is_current = false) and insert a new current row with a fresh surrogate key. A MERGE (or an expire-then-insert pair) does this cleanly.

Detect change: compare incoming attributes to the current row for that natural key.
Expire: stamp the old row's effective_to and flip is_current to false.
Insert: add a new row, new surrogate key, effective_from = today, is_current = true.

Question. A customer moves city. Apply an SCD Type 2 update to dim_customer so both the old and new addresses are queryable by date.

Input.

customer_sk	customer_id	city	effective_from	effective_to	is_current
88213	C-1001	Austin	2025-01-10	9999-12-31	true

Incoming: C-1001 now in Denver as of 2026-08-14.

Code.

-- Expire the current version, then insert the new version (SCD Type 2).
-- Step 1: expire the row whose tracked attribute changed.
UPDATE dim_customer
SET effective_to = DATE '2026-08-13',
    is_current   = FALSE
WHERE customer_id = 'C-1001'
  AND is_current  = TRUE
  AND city <> 'Denver';                 -- only if the value actually changed

-- Step 2: insert the new current version with a fresh surrogate key.
INSERT INTO dim_customer
  (customer_sk, customer_id, city, effective_from, effective_to, is_current)
VALUES
  (88240, 'C-1001', 'Denver', DATE '2026-08-14', DATE '9999-12-31', TRUE);

-- Facts join to the version current on the event date:
-- SELECT ... FROM fact_sales f
--   JOIN dim_customer d
--     ON d.customer_id = f.customer_id
--    AND f.event_date BETWEEN d.effective_from AND d.effective_to;

Step-by-step trace.

Compare the incoming city = Denver to the current row's city = Austin; they differ, so a new version is warranted (the city <> 'Denver' guard prevents a no-op update).
Expire the old version: set its effective_to to the day before the change and is_current = false, closing its validity window at 2025-01-10 … 2026-08-13.
Insert a new row with a new surrogate key 88240, the same natural key C-1001, city = Denver, and an open validity window from 2026-08-14.
Any fact dated before the move joins (by natural key + date range) to sk 88213 (Austin); any fact after joins to sk 88240 (Denver) — history is intact with no fact ever rewritten.

Output:

customer_sk	city	effective_from	effective_to	is_current
88213	Austin	2025-01-10	2026-08-13	false
88240	Denver	2026-08-14	9999-12-31	true

Rule of thumb. Type 2 = expire the current row (effective_to, is_current=false) and insert a new row with a new surrogate key; the surrogate key is what lets facts point at the version that was true when the event happened.

SCD Type 1 vs Type 3, and a late-arriving dimension — a worked teaching example

Detailed explanation. Not every attribute deserves full Type 2 history, so the interview checks that you can pick the cheapest sufficient policy: Type 1 when history is irrelevant, Type 3 when you only need "compare to the immediately previous value," and a placeholder row when a fact references a dimension member that has not arrived yet.

Type 1: overwrite — for corrections and attributes nobody reports historically.
Type 3: keep previous_* alongside current_* — for "current vs prior tier" comparisons without versioning.
Late-arriving dimension: insert an inferred placeholder keyed by the natural key so the fact loads; backfill later.

Question. A customer's segment should support "current vs previous segment" comparison (Type 3), their email is just corrected in place (Type 1), and an order arrives for a customer not yet in dim_customer. Handle all three.

Input.

Attribute / event	Policy
`email` typo fix	Type 1 overwrite
`segment` change (compare to prior)	Type 3 previous-value column
order for unknown `customer_id`	late-arriving → placeholder row

Code.

-- Type 1: overwrite in place (no history kept)
UPDATE dim_customer SET email = 'ada@new.example'
WHERE customer_id = 'C-1001' AND is_current = TRUE;

-- Type 3: keep exactly one prior value in a dedicated column
UPDATE dim_customer
SET previous_segment = current_segment,       -- shift current -> previous
    current_segment  = 'Enterprise'           -- set new current
WHERE customer_id = 'C-1001' AND is_current = TRUE;

-- Late-arriving dimension: insert an inferred placeholder so the fact can load
INSERT INTO dim_customer (customer_sk, customer_id, name, current_segment, is_inferred, is_current)
VALUES (90001, 'C-2002', 'UNKNOWN', 'UNKNOWN', TRUE, TRUE)
ON CONFLICT (customer_id) DO NOTHING;         -- backfill attributes when CRM syncs

Step-by-step trace.

The email is a data-quality fix nobody reports on historically, so Type 1 overwrites it — cheapest policy, no new row or column.
The segment needs "current vs previous" only, so Type 3 shifts the old current_segment into previous_segment and writes the new value — one prior value retained, no full versioning.
An order references C-2002, which is not in the dimension yet; inserting an inferred placeholder (marked is_inferred) lets the fact load now and preserves referential integrity.
When the CRM later syncs C-2002, the placeholder's attributes are backfilled in place (a Type 1 update on the inferred row).

Output:

Case	Result
email	overwritten, no history (Type 1)
segment	`previous_segment` + `current_segment` (Type 3)
unknown customer	inferred placeholder row, fact loads, backfill later

Rule of thumb. Pick the cheapest sufficient SCD policy: Type 1 for corrections, Type 3 for "compare to previous," Type 2 for full history — and use inferred placeholder rows so late-arriving dimensions never block a fact load.

Interview scenario on slowly changing dimensions

The finance team must report revenue by the customer's billing region as it was on the invoice date, and customers occasionally change region. A prior engineer overwrote the region in place, so all historical revenue now maps to customers' current regions. Fix the model and the load.

Solution Using SCD Type 2 on the customer dimension

Answer choices.

A. Keep overwriting region (Type 1) but add an audit log table of changes.
B. Convert dim_customer to SCD Type 2 (surrogate key + effective dating) and join facts by event-date range.
C. Store region on the fact table directly at load time.
D. Add a previous_region column (Type 3) and call it done.

Code.

Elimination:
A  Type 1 + audit log -> current row still wrong; reporting can't join by date  [reject]
D  Type 3 -> only ONE prior region; multi-move history is lost                  [reject]
C  region on the fact -> works but duplicates dim logic, no reuse across facts  [reject: partial]
B  SCD Type 2 dim + date-range join -> full history, correct as-of reporting    [ACCEPT]

Step-by-step trace.

The requirement "as it was on the invoice date" is the textbook trigger for full history, which is SCD Type 2 — Type 1 and Type 3 cannot reconstruct arbitrary past states.
A keeps overwriting, so the current dimension value is still wrong for old invoices; an audit log does not help the reporting join — eliminate.
D retains only one prior region, so a customer who moved twice loses the middle state — eliminate for multi-change history.
C could work by stamping region on the fact, but it bypasses the dimension, so every other fact needing region reinvents the logic and conformance is lost — reject as a partial hack.
B rebuilds dim_customer as Type 2 with effective_from/effective_to/is_current and a surrogate key; facts store the surrogate (or join by natural key + event_date BETWEEN effective_from AND effective_to), so each invoice maps to the region that was current on its date.

Output:

Requirement	Mechanism
Revenue by region as-of invoice date	Type 2 version valid on that date
Multiple region changes over time	one versioned row per change
Reuse across facts	conformed Type 2 `dim_customer`
No fact rewrites on change	surrogate-key join

Why this works — concept by concept:

Type 2 preserves history — a new versioned row per change means every past state is queryable, which is the only design that satisfies "as it was on the invoice date."
Surrogate key + effective dating — the fact points at the version valid on the event date, so revenue never silently re-maps when a customer moves.
Conformance — fixing it in the shared dim_customer (not on one fact) means every fact that slices by region gets correct history for free.
Cheapest sufficient policy rejected for a reason — Type 1/3 are cheaper but cannot answer arbitrary as-of questions, so the requirement forces Type 2; naming that trade-off is the senior signal.
Cost — Type 2 grows the dimension by one row per change (tiny relative to facts) and adds a date-range join, a small price for correct historical reporting.

Design
Topic — slowly-changing-data
Slowly changing dimension (SCD) modeling problems

Practice →

Design Course — data modeling Data modeling for data engineering interviews

Practice →

5. Case study — modeling a subscriptions and events domain

The senior modeling round is one long case study — start from the business questions, pick a grain and a fact type per process, and conform the shared dimensions

The invariant: an end-to-end data modeling case study is graded on process — you start from the business questions, draw a bus matrix of business processes against shared dimensions, then for each process declare a grain and choose the fact type (transaction, periodic snapshot, or accumulating snapshot) and conform the dimensions across them — so the same customer, plan, and date definitions serve every fact. The candidate who narrates that flow beats the candidate who immediately starts typing CREATE TABLE.

Step 1 — the business questions drive everything. For a subscriptions product, the questions might be:

How many signups, upgrades, and churns happened per day/segment? (event counts)
What is monthly recurring revenue (MRR) over time, and how does it move? (state over time)
How long does a subscription take to go signup → activated → churned, and what fraction reach each stage? (process durations)

Step 2 — the bus matrix. List the business processes as rows and the conformed dimensions as columns; ticks show which dimensions each process uses.

Processes: events (signup/upgrade/churn), subscription lifecycle, daily MRR.
Conformed dimensions: dim_date, dim_customer, dim_plan.
The matrix is your plan: three facts, three shared dimensions, one consistent definition of "customer," "plan," and "day" across all of them.

Step 3 — grain and fact type per process. This is where the fact-type knowledge from section 3 pays off.

fact_events — transaction fact. Grain: one row per lifecycle event. Answers the "how many signups/upgrades/churns" questions; you aggregate up from it.
fact_subscription — accumulating snapshot. Grain: one row per subscription, with milestone date columns (signup_date, activated_date, churned_date) updated as the subscription progresses. Answers "duration between stages" and "funnel to each stage."
fact_mrr_daily — periodic snapshot. Grain: one row per active subscription per day, carrying the day's recurring revenue (a semi-additive measure — sum across customers, but average or take end-of-period across time). Answers "MRR over time."

Step 4 — dimensions and change handling. dim_customer and dim_plan are SCD Type 2 (plan price tiers and customer segments change and you must report as-of); dim_date is a static conformed calendar. Surrogate keys everywhere so the facts stay narrow and history is preserved.

Interview signals — what a strong case-study answer sounds like.

You declare the grain of each fact in one sentence before drawing columns.
You justify each fact type by the question it answers (counts → transaction, state-over-time → periodic, durations → accumulating).
You name the conformed dimensions and say why sharing them matters ("so churn and revenue slice by the identical customer segments").
You handle change explicitly with SCD Type 2 on the volatile dimensions and note semi-additive MRR.

The subscription lifecycle as an accumulating snapshot — a worked teaching example

Detailed explanation. "How long from signup to activation, and what fraction churn within 90 days?" is a process-duration question, and the fact type built for it is the accumulating snapshot: one row per subscription, with a column per milestone that is filled in (updated) as the subscription reaches each stage. Durations become date subtractions; funnel counts become COUNT of non-null milestone columns.

Grain: one row per subscription (updated in place as milestones occur).
Milestone dates: signup_date, activated_date, churned_date.
Derived measures: days_to_activate, days_active.

Question. Model the subscription lifecycle so you can report median days-to-activation and 90-day churn.

Input.

subscription_id	customer_sk	plan_sk	signup_date	activated_date	churned_date
S-501	88213	12	2026-05-01	2026-05-03	(null)
S-502	88240	12	2026-05-04	(null)	2026-06-10

Code.

CREATE TABLE fact_subscription (         -- accumulating snapshot: one row per subscription
  subscription_sk BIGINT PRIMARY KEY,
  subscription_id TEXT   NOT NULL,       -- degenerate dimension
  customer_sk     INT    NOT NULL REFERENCES dim_customer(customer_sk),
  plan_sk         INT    NOT NULL REFERENCES dim_plan(plan_sk),
  signup_date_sk    INT REFERENCES dim_date(date_sk),
  activated_date_sk INT REFERENCES dim_date(date_sk),
  churned_date_sk   INT REFERENCES dim_date(date_sk),
  days_to_activate  INT,                 -- filled when activated
  days_active       INT                  -- filled when churned
);

-- Median days-to-activation:
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_to_activate) AS median_days
FROM fact_subscription
WHERE activated_date_sk IS NOT NULL;

-- 90-day churn rate:
SELECT AVG(CASE WHEN churned_date_sk IS NOT NULL
                 AND days_active <= 90 THEN 1.0 ELSE 0 END) AS churn_90d
FROM fact_subscription;

Step-by-step trace.

Declare the grain: one row per subscription, updated in place — this is what makes it an accumulating snapshot rather than a transaction fact.
As S-501 activates, the load fills activated_date_sk and computes days_to_activate = 2; the row is updated, not appended.
days_to_activate across activated rows feeds a median; the funnel "what fraction activated" is COUNT(activated_date_sk) / COUNT(*).
90-day churn is the share of rows with a non-null churned_date within 90 days of signup — a single scan of the one-row-per-subscription fact.

Output:

Metric	Query result
Median days-to-activation	2 days
Activation rate	1 of 2 activated
90-day churn	S-502 churned within 90d

Rule of thumb. Process-duration and funnel questions → accumulating snapshot: one row per process instance with milestone date columns updated in place; durations are date subtractions, funnels are non-null counts.

A daily periodic snapshot for MRR from an events stream — a worked teaching example

Detailed explanation. "Show MRR over time" is a state-over-time question, and the fact built for it is the periodic snapshot: one row per active subscription per day carrying that day's recurring revenue. MRR is semi-additive — you sum it across customers on a given day, but you must not sum it across days (that would add the same recurring dollar many times); across time you take the end-of-period or average.

Grain: one row per active subscription per day.
Measure: mrr_amount (semi-additive).
Build: derive daily active state from the lifecycle/events and the plan price.

Question. Build a daily MRR snapshot and compute total MRR on a date and the month-end MRR trend.

Input.

Fact	Value
Source	`fact_events` (signup/upgrade/churn) + `dim_plan.price`
Grain	one active subscription per day
Measure	`mrr_amount` per subscription per day
Aggregation rule	additive across customers, semi-additive across time

Code.

CREATE TABLE fact_mrr_daily (            -- periodic snapshot: one row per sub per day
  date_sk        INT NOT NULL REFERENCES dim_date(date_sk),
  subscription_sk BIGINT NOT NULL,
  customer_sk    INT NOT NULL REFERENCES dim_customer(customer_sk),
  plan_sk        INT NOT NULL REFERENCES dim_plan(plan_sk),
  mrr_amount     NUMERIC(12,2) NOT NULL, -- semi-additive
  PRIMARY KEY (date_sk, subscription_sk)
);

-- Total MRR on a given day: additive across subscriptions (safe within one day)
SELECT SUM(mrr_amount) AS mrr
FROM fact_mrr_daily
WHERE date_sk = 20260814;

-- Month-end MRR trend: take the LAST day of each month (semi-additive over time)
SELECT d.year, d.month, SUM(f.mrr_amount) AS month_end_mrr
FROM fact_mrr_daily f
JOIN dim_date d ON d.date_sk = f.date_sk
WHERE d.date = (DATE_TRUNC('month', d.date) + INTERVAL '1 month - 1 day')
GROUP BY d.year, d.month
ORDER BY d.year, d.month;

Step-by-step trace.

Declare the grain: one row per active subscription per day, so "MRR on any date" is a same-day sum.
Derive each day's active subscriptions and their plan price from the events fact and dim_plan, writing one mrr_amount row per subscription per day.
Total MRR on a date sums mrr_amount across subscriptions — additive within a day, which is valid.
The month-end trend takes the snapshot on each month's last day (never sums across days), respecting the semi-additive rule so recurring dollars are not double-counted.

Output:

date	total MRR
2026-08-14	42,300.00
2026-08-31 (month-end)	43,100.00

Rule of thumb. State-over-time questions → periodic snapshot: one row per entity per period; treat balances/MRR as semi-additive — sum across entities, but take end-of-period or average across time, never a cross-day sum.

Interview scenario on the end-to-end case study

Model a subscriptions-and-events domain so the business can answer three questions: (1) daily counts of signups/upgrades/churns by plan, (2) MRR over time, and (3) median time from signup to activation. Keep customer and plan definitions consistent across every answer, and report correctly even when a customer changes segment or a plan changes price.

Solution Using a bus matrix with three fact types and conformed Type 2 dimensions

Answer choices.

A. One transaction fact_events table and compute everything from it, including MRR and durations, at query time.
B. Three facts — fact_events (transaction), fact_mrr_daily (periodic snapshot), fact_subscription (accumulating snapshot) — sharing conformed dim_date, dim_customer (Type 2), dim_plan (Type 2).
C. One giant denormalized table with a row per event and every attribute inlined.
D. Separate marts, each with its own private customer and plan tables.

Code.

Elimination:
A  events-only -> MRR (state) and durations (process) are awkward/expensive at query time  [reject: wrong grain]
C  one wide table -> no fact-type separation, no as-of history, unmanageable                [reject]
D  private dims per mart -> "customer"/"plan" defined differently, answers not comparable   [reject: not conformed]
B  3 fact types + conformed Type 2 dims -> each question at its natural grain, consistent    [ACCEPT]

Step-by-step trace.

Map each question to a fact type: counts → transaction (fact_events), MRR over time → periodic snapshot (fact_mrr_daily), signup-to-activation duration → accumulating snapshot (fact_subscription).
A forces state-over-time and process-duration answers out of an event stream, which is expensive and error-prone (reconstructing MRR per day from events on every query) — reject on grain mismatch.
C loses the ability to answer at three different grains and cannot cleanly hold as-of history — reject.
D gives each mart its own customer and plan tables, so segment/plan definitions diverge and the three answers stop being comparable — reject on conformance.
B builds the three facts at their natural grains, all sharing conformed dim_date, dim_customer, and dim_plan; the customer and plan dimensions are Type 2 so segment and price changes are reported as-of, satisfying the "report correctly even when things change" clause.

Output:

Question	Fact (grain)	Dimensions
Daily signup/upgrade/churn counts	`fact_events` (one event)	date, customer, plan
MRR over time	`fact_mrr_daily` (sub × day)	date, customer, plan
Median signup→activation	`fact_subscription` (one sub)	date, customer, plan

Why this works — concept by concept:

One fact type per question grain — matching transaction/periodic/accumulating facts to counts/state/duration questions means each answer is a simple aggregation at its natural grain instead of a query-time reconstruction.
Conformed dimensions — sharing dim_date, dim_customer, and dim_plan across all three facts guarantees churn, revenue, and activation slice by identical customers and plans.
Type 2 on volatile dimensions — versioning customer segment and plan price is what lets every metric be reported "as it was," satisfying the correctness-under-change requirement.
Semi-additive MRR handled explicitly — modeling MRR as a periodic snapshot with an end-of-period rule prevents the classic double-count across days.
Cost — three narrow surrogate-keyed facts plus three small conformed dimensions scan and join cheaply; the accumulating snapshot updates in place (bounded row count) and the periodic snapshot grows predictably by active-subs-per-day.

Analytics
Topic — cumulative-snapshots
Accumulating and periodic snapshot modeling problems

Practice →

Design
Topic — design
End-to-end warehouse case-study design problems

Practice →

Cheat sheet — data modeling interview recipes

Normal-form quick test (say the dependency, not just the number).

Normal form	One-line test	Fixes
1NF	atomic values, no repeating groups	multi-valued cells
2NF	1NF + no partial dependency on a composite key	attributes tied to part of the key
3NF	2NF + no transitive dependency	non-key → non-key (reference data)
BCNF	every determinant is a candidate key	overlapping-candidate-key edge cases

Fact-table-type chooser.

Question shape	Fact type	Grain
"How many events / how much per event?"	transaction	one event
"What was the state each period?" (balance, MRR, inventory)	periodic snapshot	one entity per period
"How long between milestones / funnel to each stage?"	accumulating snapshot	one process instance

Star vs snowflake decision line. Default to a star (denormalized dimensions, fewer joins, BI-friendly). Snowflake a dimension only when its hierarchy is large and volatile enough that removing redundancy beats the extra joins.

SCD type chooser.

Need	SCD type
Attribute never changes	Type 0
Only current value matters (fix typos)	Type 1 (overwrite)
Full "as it was on date X" history	Type 2 (versioned rows + surrogate key)
Compare current vs one prior value	Type 3 (previous-value column)
Current in dim, history in a side table	Type 4
Current column and full versioned history	Type 6 (1+2+3)

Grain & surrogate-key checklist.

State the grain of every fact in one sentence before drawing columns.
Store only additive measures on the fact; derive ratios at query time; treat balances/MRR as semi-additive.
Give every dimension a surrogate key; the fact stores the surrogate, never the natural key.
Conform shared dimensions (dim_date, dim_customer) across facts so marts stay comparable.
Use inferred placeholder rows for late-arriving dimensions; join late-arriving facts by natural key + effective-date range.

Whiteboard modeling script (the order to say things in). Business questions → bus matrix (processes × dimensions) → grain per process → fact type per process → additive/semi-additive measures → conformed dimensions with surrogate keys → SCD policy per changing attribute → physical tuning (partition/cluster/index).

Frequently asked questions

What are the most common data modeling interview questions?

The recurring data modeling interview questions cluster into four buckets: normalization ("normalize this table to 3NF and name each anomaly"), dimensional design ("design a star schema for X" and "star vs snowflake"), slowly changing dimensions ("how do you track a customer's changing address?"), and an end-to-end case study ("model this domain to answer these business questions"). Underneath all of them is one skill — declare a grain, choose keys, and match the shape to the workload — so preparing that framework covers most of what any loop will throw at you.

Star schema vs snowflake schema — which should I use?

Default to a star schema: denormalized dimensions mean fewer joins, simpler SQL, and better BI-tool performance, which is what analytical workloads want. Choose a snowflake only when a dimension's hierarchy is large and changes often enough that storing it once (normalized) is worth the extra joins on every query. In an interview, state the trade-off explicitly — "star for read simplicity, snowflake to reduce redundancy in a big volatile hierarchy" — rather than picking one dogmatically.

Do I need to normalize to 3NF or denormalize for a warehouse?

Both, in the right place. Transactional (OLTP) systems normalize to 3NF so concurrent writes cannot create update, insertion, or deletion anomalies; analytical warehouses deliberately denormalize into star schemas so reads touch one fact and a few dimensions instead of a dozen joined tables. The bridge is ETL/ELT: a normalized source is reshaped into a dimensional model, so "normalize for integrity, denormalize for speed" is the sentence to say — the workload decides which side you are on.

How do I explain slowly changing dimensions in an interview?

Define an SCD as a dimension whose attributes change occasionally, then give the policy per type: Type 1 overwrites (no history), Type 2 adds a new versioned row with a fresh surrogate key and effective_from/effective_to/is_current (full history), and Type 3 keeps a single prior value in an extra column. The key insight to volunteer is why Type 2 needs a surrogate key: because the fact joins to the version that was current on the event date, so history is preserved without ever rewriting a fact row.

What is grain and why do interviewers obsess over it?

Grain is what a single row of a fact table represents ("one row per order line item," "one row per subscription per day"), and it is the first thing to declare because every measure, join, and aggregation depends on it. A wrong grain silently double-counts (too coarse loses detail; mixing grains inflates sums), which is the most common and most expensive modeling mistake. Stating the grain in one sentence before drawing any columns is the single highest-signal habit in a data modeling interview.

How do I prepare for a data modeling interview in a few weeks?

Drill the four buckets in order: normalize a wide table to 3NF and name every anomaly; design two or three star schemas and defend the grain and fact type; implement SCD Type 2 with a MERGE; then run a full case study out loud from business questions to physical tables. Practise narrating the framework — grain, entities, keys, normalize-vs-dimensionalize, SCD — because interviewers score the reasoning you say aloud, and building that reflex on real sql data modeling problems is far more effective than re-reading definitions.

Practice on PipeCode

Turn data modeling theory into whiteboard reflex

Definitions explain normal forms and star schemas; drills build the reflex the interview actually tests — declaring a grain, choosing a fact type, and defending star-vs-snowflake and an SCD policy under a clock. Pipecode.ai is Leetcode for Data Engineering — scenario-first practice on SQL, dimensional modeling, and schema design tuned to the trade-offs the data modeling round rewards.

Practice dimensional modeling →
Practice SQL problems →