Section D · Critical

Modeling Judgement

The word is judgement on purpose. Modeling questions rarely have one right answer — they test whether you reason from the question and the grain, weigh tradeoffs, and know which decisions are expensive to reverse. As the first hire, your models become the company's conventions.

What they're listening for

Not "do you know what a star schema is" — but "can you defend why you'd choose one design over another for this question, and do you know the cost of being wrong?" Always reason out loud from grain → question → tradeoff. A confident "it depends, and here's what it depends on" beats a memorized pattern.

Grain first, always

The grain of a table is what one row represents. Declaring it is the first move in every modeling answer, because everything else (keys, joins, aggregations, tests) follows from it.

Pick the finest grain you'll need for a fact table (e.g. one row per usage event, or per instance-hour), then aggregate up in marts. You can always roll up; you can't recover detail you didn't store.
A mixed-grain table is a bug — if some rows are per-order and some per-line, every aggregate is wrong. Interviewers plant this.
State the grain in one sentence: "fct_instance_hours is one row per instance per hour it was running." If you can't say it cleanly, the model isn't right yet.

Dimensional modeling (Kimball)

The default mental model for analytics: split the world into facts (measurements, events — the verbs) and dimensions (the context — the nouns: who, what, where, when).

Fact table: numeric, additive measures at a declared grain, plus foreign keys to dimensions. Long and narrow, grows forever. E.g. fct_usage with gpu_seconds, cost, keys to customer/instance/date.
Dimension table: descriptive attributes, one row per entity. Wide and short. E.g. dim_customer, dim_instance_type, dim_date.
Star schema: one fact surrounded by dimensions joined by keys. Snowflake schema = dimensions further normalized into sub-tables (usually not worth it in a columnar warehouse).
Why it works for analytics: intuitive for slicing ("revenue by region by month"), efficient for BI tools, and stable as the business grows.

Fact & dimension types worth naming

Type	What it is	Marketplace example
Transaction fact	One row per event	One row per usage event / charge
Periodic snapshot	State sampled at intervals	Daily balance / active instances per customer per day
Accumulating snapshot	One row per process, updated as it progresses	An instance lifecycle: requested → provisioned → running → stopped
Conformed dimension	Shared across facts	One `dim_customer` used by usage, billing, support facts
Degenerate dimension	An ID on the fact with no dim table	`invoice_number` stored directly on the fact

Knowing periodic vs accumulating snapshots is a strong signal — it shows you've modeled real lifecycles, not just events.

Slowly changing dimensions (SCD)

How you handle a dimension attribute that changes over time (a customer changes plan tier or region). This is a classic interview question — know the types and when to use each.

Type	Behavior	Use when
Type 1	Overwrite — keep only current value	History doesn't matter (fix a typo)
Type 2	New row per change with valid-from/valid-to + is_current flag	You need point-in-time truth (what tier were they on when billed?)
Type 3	Add a "previous value" column	Only the prior value matters; rare

scd2_shape.sql

-- dim_customer (Type 2): point-in-time joins use the validity window
-- customer_id | plan_tier | valid_from | valid_to   | is_current
-- 42          | free      | 2026-01-01 | 2026-03-15 | false
-- 42          | pro       | 2026-03-15 | 9999-12-31 | true

-- Bill each usage row at the tier that was active at the time:
SELECT u.*, d.plan_tier
FROM fct_usage u
JOIN dim_customer d
  ON d.customer_id = u.customer_id
 AND u.event_ts >= d.valid_from
 AND u.event_ts <  d.valid_to;

Judgement, not recitation

The interesting answer is which to use: "I'd make plan_tier SCD2 because billing must reflect the tier at time-of-use, but customer email I'd keep Type 1 — nobody needs the history, and SCD2 everywhere bloats the dim and complicates every join." dbt snapshots implement SCD2 for you — mention it.

Event vs state modeling

Two ways to represent something like an instance's life:

Event/log model: append-only rows for every state change (started, stopped). Immutable, full history, but you must derive current state with window functions.
State/snapshot model: current state per entity, updated in place — or a daily snapshot of state. Easy to query "now," loses fine history unless snapshotted.

Strong answer: keep the event log as the source of truth (you can always rebuild state from events, never the reverse), and materialize current-state and daily-snapshot tables on top for fast querying. Event-sourcing intuition reads as senior.

Normalization tradeoffs

Be able to argue both sides — that is the judgement they're testing.

	Normalized (3NF)	Denormalized
Optimized for	Writes, integrity, no duplication	Reads, query simplicity, speed
Home turf	OLTP / app databases	Analytics warehouses
Cost	Many joins to answer a question	Duplicated data, update anomalies

The principle: source systems are normalized; analytics layers denormalize for query speed and clarity. Columnar warehouses make wide denormalized tables cheap to scan, which shifts the default toward denormalization in marts. But dimensions stay conformed so logic isn't duplicated.

OBT vs star schema

A live debate; have a position. One Big Table (OBT) pre-joins everything into a single wide table per business process.

OBT pros: dead-simple for analysts/BI (no joins), fast scans on columnar engines, no fan-out mistakes downstream.
OBT cons: attribute changes must be reprocessed across the whole table, duplication, can hide grain, harder to reuse logic.
Star pros: reusable conformed dimensions, smaller storage, clear grain, change a dimension once.

First-hire answer

"I'd model the core as a star — conformed dimensions, clean facts — and then materialize OBT-style wide marts on top for the specific dashboards analysts hit. Best of both: a maintainable core, an ergonomic serving layer. I wouldn't pick a religion; I'd pick per consumer."

Reversible vs one-way-door decisions

The most senior modeling instinct: spend judgement proportional to reversibility.

Expensive to reverse (decide carefully): the grain of a core fact; whether a key dimension is SCD2; primary-key/identity strategy; what raw data you retain. Getting grain wrong means rebuilding everything downstream.
Cheap to change (don't agonize): adding a derived column, a new mart, renaming in a non-breaking way, an OBT for one dashboard.

Saying "this is a one-way door, so I'd capture the finest grain and full history even if we don't need it yet; this other part is cheap to iterate, so I'd ship the simple version" is exactly the founding-hire judgement they're hiring for.

The conventions you'll set (taste matters here)

As employee-zero, your choices become standards. Mention you'd establish, lightly and early:

Naming: stg_ / int_ / fct_ / dim_ prefixes; consistent _id, _at (timestamp), _date suffixes; snake_case throughout.
A metrics layer / definitions: one canonical definition of "active customer," "revenue," "utilization" — so the same word means the same number everywhere. This alone prevents most trust disputes.
Surrogate vs natural keys: stable surrogate keys for dimensions so SCD2 and source-key changes don't break facts.
Documentation as part of done: a model isn't done until its grain and columns are described (dbt docs). For a solo hire, docs are how the company isn't hostage to your memory.

The modeling whiteboard (interview script)

When asked "model X" (e.g. "model usage and billing so we can report revenue and utilization"):

Ask what questions it must answer and who consumes it. Model to the question.
Identify the business process → that's your fact (usage events / charges).
Declare the grain in one sentence.
Name the dimensions (customer, instance type, date, region) and flag which need SCD2 and why.
Decide event-log vs state, and what you'll materialize on top.
Call the tradeoffs: star vs OBT for serving, normalization, reversibility — and what would change your mind.
Add the tests the model needs (grain uniqueness, FK to dims, reconciliation) — tie back to chapter 05.

Go deeper on bridges, advanced SCDs, and paradigms in 06b — Modeling Advanced →

✓ Check yourself

Answer from memory before opening each one.

A fact table holds one row per order for some orders and one row per order line for others. Total revenue is overstated. What is the diagnosis, and what do you do?

Show answer
The table mixes grains, so any sum counts some amounts more than once. Pick one grain, usually the order line, and give order-level amounts their own table or allocate them to lines. State the grain in one sentence before you build. See grain first.
A customer moves from the free to the pro tier on March 15. Finance wants revenue by the tier in force when usage happened. Which dimension type, and what is the join condition? How do you treat the customer's email address?

Show answer
Type 2 on plan tier, with valid-from and valid-to dates. Join where the usage timestamp is at or after valid_from and before valid_to. Keep email as Type 1, because nobody needs its history and Type 2 everywhere bloats the dimension. See slowly changing dimensions.
You must measure time from request to running for every instance, where an instance passes through requested, provisioned, running and stopped. Which fact table type fits, and why not a transaction fact?

Show answer
An accumulating snapshot: one row per instance, updated as each milestone is reached, with a timestamp per stage. A transaction fact gives one row per event and forces window logic every time you want a lifecycle duration. See fact and dimension types.
A source system keeps only the current status of each order and overwrites it. Nobody needs history yet. What do you do today, and why?

Show answer
Capture history now, through CDC or scheduled snapshots, before the past is lost. You can rebuild state from events later but never the reverse. This is a one-way door, so decide it early. See event versus state modelling.
Analysts want one wide table for a dashboard, and you prefer a star schema. Make the call.

Show answer
Build the star as the core, with conformed dimensions and clean facts, then materialize a wide table on top for that dashboard. The core keeps logic reusable, and the wide table gives analysts a join-free surface. Choose per consumer rather than one design for all. See OBT versus star schema.
Rank these by how costly each is to reverse later: the grain of the core usage fact, adding a margin column to a mart, and whether the customer dimension is Type 2.

Show answer
Grain is the costliest, since a wrong grain rebuilds everything downstream. The Type 2 decision comes next: history you never captured cannot be recovered. A new mart column is cheap. Spend your care on the first two. See reversible and one-way decisions.

Takeaway

State the grain in one sentence first, and choose the finest grain you need, because you can roll up but not down.
Facts hold additive measures at a declared grain and dimensions describe context; know transaction, periodic snapshot and accumulating snapshot facts.
Use Type 2 where point-in-time truth matters, such as the plan at billing time, and Type 1 elsewhere.
Keep the event log as the source of truth and materialize current state and snapshots on top.
Build a star core and wide marts on top, and choose per consumer.
Spend your judgement on one-way doors: grain, key strategy, history and the raw data you retain.
Set naming, metric definitions and documentation early, because they become the company's standards.

The basics decide most answers; bridges, additivity and alternative paradigms come up in a deep modeling round. → Modeling Advanced