Data Modeling

Grain — the first thing you ask, always

Before designing anything, before writing any SQL, before any modeling discussion: what is the grain of this table?

Grain = what does one row represent. "One row per order." "One row per user per day." "One row per GPU per minute." "One row per inference request."

If you can't state the grain of a table in one sentence, the model isn't right yet.

Atomic grain vs aggregated grain

Always model facts at the atomic grain (the lowest possible), then aggregate on top. "One row per order_item" is more atomic than "one row per order". Atomic grain is harder to recreate from aggregated grain; the reverse is easy.

Facts and dimensions — the Kimball vocabulary

Fact tables (fct_*)

• Hold measures — things you sum/count/average (revenue, count, duration).
• Tall and narrow — many rows, relatively few columns.
• Time-anchored — almost always have an event timestamp.
• Foreign keys to dimensions — user_id, product_id, date_key.
• One row per atomic event — order_placed, inference_request, gpu_minute.

Dimension tables (dim_*)

• Hold attributes / descriptions — name, status, country, tier.
• Short and wide — fewer rows, many columns.
• One row per entity — one row per user, one row per product, one row per date.
• Updated when entities change (slowly — hence "slowly changing dimensions").

Three types of fact tables

Measure types in facts

• Additive — sum across any dimension makes sense. Revenue, units sold. Easiest to model.
• Semi-additive — sums across some dimensions but not time. Account balance — summable across customers, not across time.
• Non-additive — ratios, percentages. Conversion rate. Margin %. Store the numerator and denominator separately; compute the ratio at query time.

Star schema — the default

A central fact table surrounded by dimension tables, each connected by a foreign key. Visually it looks like a star.

Why star schema wins

• Query-friendly — joins are simple and consistent (one hop from fact to dim).
• Performance — modern warehouses optimize star schema joins explicitly.
• BI-tool-friendly — Looker, Tableau, Power BI all model around it.
• Easy to extend — add a new fact or dim without breaking existing ones.
• Self-documenting — the structure tells you what the business cares about.

Surrogate keys

Each dimension has a synthetic primary key (a hash or sequence) called a surrogate key. The fact references that, not the natural key.

Why surrogate keys:

• SCD Type 2 versions need their own keys (a customer can have multiple rows in dim_customer across time).
• Natural keys can change. Surrogate keys don't.
• Joins are faster on small integer/hash keys than on long strings.

In dbt: {{ dbt_utils.generate_surrogate_key(['col1', 'col2']) }} generates a hash.

Snowflake schema (the technique, not the warehouse)

Same as star schema, but dimensions are normalized — a dimension can reference other dimension tables.

Example: dim_product → dim_category → dim_department. Three tables instead of one wide product dim.

Trade-off:

• Pro: saves storage, reduces update anomalies.
• Con: more joins, harder for BI users, slower queries.

Modern best practice: denormalize dimensions (single wide dim_product table) unless you have a specific reason to snowflake. Storage is cheap; query simplicity is valuable.

One Big Table (OBT) — the modern alternative

Instead of fact + dimensions joined at query time, materialize a single wide table that pre-joins everything. Each fact row already contains all dimension attributes.

Pros

• No joins for analysts — point and click in BI.
• Faster query latency on flat data.
• Simpler mental model.

Cons

• Storage cost — denormalized data balloons.
• Maintenance — when a dim attribute changes, every fact row needs updating.
• SCD handling is messier.
• Less flexible — you've baked in specific joins.

Slowly Changing Dimensions — the time-travel problem

Dimensions change. A customer's plan changes from Basic to Pro. A product's price changes. A GPU provider's location changes. How do you model this?

The question is: when looking at a historical fact, do you want to see the dimension attributes as they were then, or as they are now?

SCD types — know all of them by name

SCD Type 2 — the shape

user_id  | user_key | plan      | valid_from  | valid_to    | is_current
─────────┼──────────┼───────────┼─────────────┼─────────────┼───────────
123      | abc      | basic     | 2024-01-01  | 2024-06-15  | false
123      | def      | pro       | 2024-06-15  | 2025-02-01  | false
123      | ghi      | enterprise| 2025-02-01  | 9999-12-31  | true
456      | jkl      | basic     | 2024-03-10  | 9999-12-31  | true

Joining a fact to an SCD Type 2 dim

This is the interview gotcha. Don't join on user_id alone — you'll fan out.

SELECT
  o.order_id,
  o.ordered_at,
  o.revenue,
  u.plan AS plan_at_order_time
FROM fct_orders o
JOIN dim_users u
  ON o.user_id = u.user_id
  AND o.ordered_at >= u.valid_from
  AND o.ordered_at <  u.valid_to

This gives the plan the user was on when they placed the order. That's the whole point of SCD2.

For "what plan are they on now?" — filter on is_current and skip the time-range join.

The date dimension

Every warehouse should have a dim_date table. One row per date, with columns for year, quarter, month, week, day-of-week, fiscal-year, holiday flags, business-day flags.

Why bother

• BI tools can pivot/filter on rich date attributes (fiscal quarter, week-over-week).
• Avoids re-deriving date attributes in every query.
• Holiday and business-day logic lives in one place.
• Outer-joining dim_date to fact_daily_x guarantees a row for every day (handles gaps).

date_day, year, quarter, month, month_name, week, day_of_week, day_name,
fiscal_year, fiscal_quarter, is_weekend, is_business_day, is_holiday,
days_in_month, is_first_of_month, is_last_of_month, ...

dbt's dbt_utils.date_spine macro is the standard way to generate it.

Periodic snapshot fact tables

Some questions don't have a natural transaction grain. "What's the GPU utilization right now?" — there's no single event. You take a snapshot at regular intervals.

Example: fct_gpu_hourly_utilization — one row per GPU per hour. Captures utilization_pct, memory_used_gb, requests_served, customer_id_currently_assigned, etc.

Different from a transaction fact (one row per event) — same shape, different semantics. You can sum measures across GPUs but be careful summing across hours (semi-additive).

Bridge tables — many-to-many

When a fact has a many-to-many relationship to a dim, you can't join directly. Example: an order can have multiple coupons applied; a coupon can apply to many orders.

Bridge table: fct_order_coupons with (order_id, coupon_id). The bridge sits between the fact and the dim.

Warning: joining a fact to a bridge to a dim causes fanout. Aggregate first or use grouping aggregations.

Degenerate dimensions

A dimension that has no attributes — just an ID. Example: order_id on a fact_order_items table. You'd never look up attributes about an order_id from a dim table; the ID itself is the dim.

Live them in the fact table; don't create a 1-column dim_order for them.

Conformed dimensions

The same dim used by multiple fact tables. dim_users joined by fct_orders, fct_logins, and fct_support_tickets all uses the same dim.

The principle: a dim is conformed when its meaning and grain is identical across uses. This is what enables drill-across analysis: "Show me revenue (from orders) and ticket count (from support) by user segment."

Lack of conformed dimensions = "we have three different definitions of customer and the numbers don't tie." Common mid-size company problem.

How to answer "design a data model for X"

The framework that makes you look senior. They'll give you a business — design the warehouse for them.

1. Ask about the business questions. "Before I model — what are the top three questions the business needs to answer? Revenue by segment? Retention? Unit economics?"
2. Identify the core entities. Users, products, orders, GPUs, inference requests — whatever's central. These become dimensions.
3. Identify the core events. What happens in the world that we want to count or measure? Each event becomes a fact table.
4. State the grain of each fact. "fct_orders is one row per order. fct_inference_requests is one row per inference request."
5. Identify measures vs attributes. Measures go in facts (numeric, additive where possible). Attributes go in dims (descriptive).
6. Decide on SCD strategy for each dim. Most are SCD1 (overwrite) by default; SCD2 where point-in-time accuracy matters.
7. Sketch the diagram. Draw it. Star schema. Foreign keys. Talk through one query path.
8. Mention edge cases. Late-arriving facts. Backfills. Time zones. Soft deletes. NULL handling.
9. Discuss the layer strategy. Staging → marts. Where dbt fits.

Worked example: design for an AI inference platform

FACTS
─────
fct_inference_requests       — one row per API request
  request_id, customer_id, model_id, gpu_id, region_id, date_key,
  ts, latency_ms, input_tokens, output_tokens, cost_usd, status

fct_gpu_hourly_utilization   — one row per GPU per hour
  gpu_id, hour_ts, utilization_pct, memory_used_gb, requests_served,
  revenue_generated_usd, current_customer_id

fct_billing_events           — one row per billing event
  event_id, customer_id, date_key, ts, event_type, amount_usd

DIMENSIONS
──────────
dim_customers (SCD2)         — one row per customer per attribute version
  customer_key, customer_id, plan, region, signup_date, valid_from, valid_to

dim_models                   — one row per model
  model_id, model_name, family, parameter_count, hosted_since

dim_gpus (SCD2)              — one row per GPU per location change
  gpu_key, gpu_id, gpu_type, provider_id, region_id, valid_from, valid_to

dim_providers                — one row per GPU provider
dim_regions                  — one row per region
dim_date                     — one row per calendar day

With this model you can answer:

• "Revenue per customer per month" — sum cost_usd from fct_inference_requests, point-in-time joined to dim_customers.
• "GPU utilization by region" — fct_gpu_hourly_utilization joined to dim_gpus (current version) joined to dim_regions.
• "Provider payout this month" — fct_gpu_hourly_utilization × pricing logic per provider.
• "Average latency per model" — fct_inference_requests grouped by model_id.

Interview talking points

✓ Check yourself

Answer from memory before opening each one.

1. A colleague describes a new table as "orders joined to customers, with their region". What do you ask first, and why does the answer matter more than the column list?

   Show answer

   Ask for the grain: what does one row represent? Without a one-sentence answer, nobody knows what a join or a count means, and fanout bugs follow. Everything else, including keys and measures, depends on it. See grain.

2. A daily account-balance fact has one row per account per day. Marketing asks for "total balance in 2024" and a query sums the rows. What is wrong, and what are two valid answers?

   Show answer

   Balances are semi-additive: they sum across accounts on one day but not across time. Report the balance at period end, or the average daily balance. See measure types.

3. The customer dimension is SCD Type 2. A fact table joined on customer_id alone now double-counts orders for customers who changed plan. Why, and what join do you write?

   Show answer

   Each customer has several dimension rows, and joining on the natural key matches all of them. Join on the key plus a time range: the order time is at or after valid_from and before valid_to. See SCD Type 2.

4. When do you choose SCD Type 2 over Type 1 for a customer's plan, and what does the choice cost you?

   Show answer

   Default to Type 1 because most questions ask about the world now. Choose Type 2 when the business needs point-in-time accuracy, such as financial reporting by the plan a customer was on at purchase. The cost is a range join, larger dimensions, and a standing risk of fanout. See SCD types.

5. Support wants "average resolution time" by team and month in a ticket fact. What do you store, and why not the average itself?

   Show answer

   Store the sum of resolution minutes and the ticket count, and compute the ratio at query time. An averaged column is non-additive: averaging averages across teams or months gives the wrong number. See measure types.

6. Orders and support tickets both join to a customer dimension, yet customer counts by segment differ between the two reports. What do you suspect?

   Show answer

   The dimensions are not conformed: the two facts use different customer tables, or different grain or segment definitions. A conformed dimension has identical meaning and grain everywhere, which is what lets you compare facts side by side. See conformed dimensions.

Takeaway

• State the grain of every table in one sentence before anything else, and model facts at the atomic grain so you can aggregate up but never down.
• Facts hold measures and dimensions hold attributes; transaction, periodic snapshot and accumulating snapshot facts answer different questions.
• Measures differ: additive ones sum everywhere, semi-additive ones such as balances do not sum over time, and ratios are stored as numerator and denominator.
• The star schema is the canonical layer; build wide tables on top for specific consumers rather than in place of the star.
• Default to SCD Type 1; use Type 2 when the business needs point-in-time accuracy, and join with a valid-from and valid-to range to avoid fanout.
• Conformed dimensions are what let two facts be compared; without them the numbers do not tie.

A model is trustworthy only if the pipeline feeding it is, which is the subject of the next chapter. → Data Pipelines

Further reading

• Dimensional Modeling Techniques — Kimball Group, accessed 2026. The primary catalogue of grain, fact types, SCD types and conformed dimensions.
• The Data Warehouse Toolkit, 3rd Edition — Kimball Group, accessed 2026. The book behind the vocabulary used throughout this chapter.
• Add snapshots to your DAG — dbt Labs docs, accessed 2026. How dbt implements Type 2 history.
• dbt-utils — dbt Labs, accessed 2026. Surrogate key and date spine macros referenced in the chapter.