Modeling Judgement

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

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.

-- 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;

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.

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.

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"):

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