Modeling Advanced

Fact additivity — the property that decides what you can SUM

Whether a measure can be summed across dimensions. Getting this wrong produces nonsense totals — a frequent interview probe.

• Additive: summable across all dimensions (revenue, gpu_seconds). The easy case.
• Semi-additive: summable across some dimensions but not time (account balance, active-instance count, inventory). Summing balances across days is meaningless — you average over time, sum over entities.
• Non-additive: can't be summed at all (ratios, percentages, utilization %). Store the numerator and denominator as additive facts and compute the ratio at query time, never pre-aggregate the ratio.

Bridge tables & many-to-many

When a fact relates to a dimension many-to-many (an instance with multiple tags; a customer in multiple segments), a direct FK fans out. A bridge table sits between them.

• Structure: fact → bridge(group_key, member_key, [weight]) → dimension.
• The double-counting trap: joining through a bridge multiplies the fact by group size. Either accept it and aggregate carefully, or use allocation weights that sum to 1 so totals stay correct.
• Time-varying M:N: add validity windows to the bridge for point-in-time membership (which segments a customer belonged to when).

Factless facts, junk & role-playing dimensions

• Factless fact: a fact with no measures — just the co-occurrence of dimensions. Models events ("a search happened for GPU type X in region Y") or coverage ("which machines were eligible but not rented"). You count rows.
• Junk dimension: collapse several low-cardinality flags (is_interruptible, is_verified, payment_method) into one small dimension instead of many boolean columns on the fact — keeps facts narrow.
• Role-playing dimension: one physical dim (dim_date) joined multiple times in different roles (created_date, started_date, ended_date) via views/aliases. Don't duplicate the table.
• Degenerate dimension: a meaningful ID on the fact with no attributes to warehouse (rental_id, invoice_number) — leave it on the fact.
• Multi-valued / hierarchical dims: handle with bridges (above) or, for fixed depth, flattened level columns.

Late-arriving facts & dimensions

• Late-arriving fact: an event lands after its event date. With idempotent partitioning + lookback (chapters 04/04b) it folds into the right period.
• Late-arriving dimension (inferred member): a fact references a dimension key you haven't loaded yet (usage for a customer the CRM hasn't synced). Insert a placeholder/inferred dim row with the natural key and unknown attributes, then enrich when the real record arrives — so the fact isn't dropped or orphaned.
• Early-arriving fact vs SCD2: when a fact arrives before the dimension version that should apply, decide whether to attach "unknown" and backfill, or hold the fact. Document the choice.

Surrogate key strategies

• Why surrogate keys: insulate facts from source-key changes, enable SCD2 (multiple rows per natural key), and unify keys across sources.
• Sequence/identity keys: simple but require a central generator and ordering — awkward in distributed/parallel loads.
• Hash keys (md5/sha of natural key + version): deterministic, parallel-friendly, no coordination — the modern ELT default (and what Data Vault uses). Watch collision risk (negligible with sha) and that inputs are normalized (trim/case) before hashing.
• For SCD2: surrogate = hash(natural_key + valid_from) so each version is unique and joins are stable.

SCD types 4 / 6 & mini-dimensions

• Type 4 (mini-dimension): split fast-changing attributes (e.g. usage-tier bands) into a separate small dimension referenced by the fact, so the main dim doesn't churn. The fix when SCD2 would explode row count.
• Type 6 (1+2+3 hybrid): SCD2 rows plus a "current value" column on every row, so you can report either point-in-time or current-attribute without re-joining. Common in practice.
• Choosing: map the decision to query patterns — "do consumers ask 'as it was then' or 'as it is now', or both?" Type 6 answers both at the cost of an extra column.

Data Vault (know it exists, know the tradeoff)

A modeling paradigm for the raw/integration layer: Hubs (business keys), Links (relationships), Satellites (descriptive, time-stamped attributes).

• Strengths: highly auditable, parallel-loadable, handles many sources and schema change gracefully, full history by design.
• Cost: many tables and lots of joins — not query-friendly, so you still build dimensional marts on top.
• Verdict for a first hire: overkill early; it shines in regulated enterprises with many sources. Mention you know it and why you'd not reach for it on day one — that's the judgement signal.

Activity schema / event-centric modeling

A single, narrow activity stream table — one row per (entity, activity, timestamp, features) — instead of many fact tables. Everything is an event on a customer/machine timeline.

• Strengths: uniform shape, easy to add new activities, natural for funnels/sequences/temporal joins ("first activity X after activity Y").
• Cost: heavy reliance on temporal/window logic; can be less ergonomic for classic slice-and-dice BI.
• Where it fits a marketplace: excellent for the user/machine journey (search → bid → run → stop → bill). Pairs well with keeping the event log as source of truth (chapter 06) and materializing dimensional marts for BI.

Metrics / semantic layer modeling

• Define metrics, not just tables: a metric = an aggregation + dimensions it can be sliced by + a single owned definition. Tools: dbt Semantic Layer / MetricFlow, Cube, LookML.
• Why it's a modeling concern: it decouples "what revenue means" from "which table," so consistency survives table refactors and every tool gets the same number.
• Metric trees: express derived metrics (gross margin = revenue − cost; utilization = rented ÷ available) so the lineage of a number is explicit.
• First-hire value: establishing this early is the single best defense against the "two dashboards, two numbers" problem (chapter 05b).

Modeling a two-sided marketplace (worked)

Bring it together for a compute marketplace — the exact shape the next chapter applies to the domain:

• Conformed dimensions: dim_customer (renter), dim_host, dim_machine (SCD2 on price/specs), dim_gpu_type (with DLPerf), dim_region, dim_date.
• Demand facts: fct_search (factless), fct_bid (transaction; bid_price, won flag), fct_usage (instance-hour grain; gpu_seconds, cost components).
• Supply facts: fct_machine_availability (periodic snapshot — listed/available capacity per machine per hour), fct_host_payout.
• Lifecycle: fct_instance_lifecycle (accumulating snapshot: requested → running → stopped → deleted) to measure provision latency and interruptions.
• Semi-additive watch: available capacity and active-instance counts are semi-additive (don't sum over time).

Modeling for incrementality & idempotency

• Model with a partition/event timestamp so incremental builds and delete-insert work cleanly (ties modeling to chapter 04).
• Insert-only, derive state: prefer append-only facts + derived current-state views over in-place mutation — reprocessable and auditable.
• Deterministic surrogate keys (hashes) so re-runs produce identical keys.
• Snapshots for history you can't reconstruct: if a source mutates in place with no history, snapshot it (SCD2) on a schedule before the past is lost.

Tradeoff scenarios to rehearse

• "Customer can have many active instances — how model usage?" Fact at instance-hour grain; customer is a dimension; never put a customer-level measure on it (semi-additive trap).
• "Pricing changes hourly — how do you bill historically correctly?" SCD2 / price_history + as-of join (02b); don't overwrite price.
• "They want both 'revenue by current plan' and 'revenue by plan at time of use'." Type 6 dimension answers both.
• "Tag/segment membership is many-to-many and changes." Time-bounded bridge with weights.
• "Analysts keep getting different active-user counts." Not a modeling-table fix — a single definition in the semantic layer.

✓ Check yourself

Answer from memory before opening each one.

1. A table stores each account's balance every day. A report sums balance over the month to show "total balance". Why is that figure meaningless, and what do you report?

   Show answer

   Balance is semi-additive: you may sum it across accounts at one point in time but not across days, because the same money counts once per day. Report the end-of-period balance, or the average over time. See fact additivity.

2. Monday had 100 GPU-hours of capacity at 90% utilization. Tuesday had 900 GPU-hours at 50%. What is the correct combined utilization, and what does averaging the two percentages give?

   Show answer

   Correct: (90 + 450) / (100 + 900) = 54%. Averaging the percentages gives 70%, which weights both days equally despite the ninefold difference in capacity. Store rented and available hours as additive facts and divide last. See fact additivity.

3. Each instance can carry several tags. A report of revenue by tag adds up to more than company revenue. Why, and how do you fix it?

   Show answer

   Joining through the bridge repeats each fact row once per tag, so the same revenue counts several times. Either accept overlap and do not sum across tags, or add allocation weights that sum to one per instance so totals reconcile. See bridge tables.

4. Usage rows arrive for a customer the CRM has not synced yet. Loading them would orphan the foreign key. What are your options?

   Show answer

   Insert an inferred member into the dimension with the natural key and unknown attributes, load the fact against it, and fill in the real attributes when the record arrives. Dropping the fact loses revenue, and holding it delays reporting. See late-arriving facts and dimensions.

5. One team wants revenue by the customer's current plan, and another wants revenue by the plan at the time of use. Do you build two dimensions?

   Show answer

   No. A Type 6 dimension keeps Type 2 rows and adds a current-value column on each row, so one table answers both. The cost is one extra column that must be refreshed on every row when the plan changes. See SCD types 4 and 6.

6. A new colleague suggests Data Vault for your six-source warehouse. Would you adopt it, and what would change your mind?

   Show answer

   Not on day one. Hubs, links and satellites add many tables and joins, and you would still build dimensional marts on top. It pays off with many changing sources and strict audit needs. Name those triggers. See Data Vault.

Takeaway

• Additivity decides what you can sum: store the numerator and denominator of a ratio and divide last, and never sum balances across time.
• Joining many-to-many through a bridge multiplies facts; use weights that sum to one, or do not sum across the group.
• An inferred member keeps a fact from being dropped when its dimension row arrives late.
• Hash surrogate keys are deterministic and parallel-friendly, but normalize the inputs before hashing.
• Type 6 answers both as-was and as-is questions; a mini-dimension stops fast-changing attributes from churning the main one.
• Data Vault and activity schemas have real costs; a first hire adopts neither without a need that pulls them in.
• One metric definition in a semantic layer beats fixing tables when two dashboards disagree.

With the patterns in hand, apply them to the usage marketplace the examples have used throughout. → Marketplace / Usage-Data Domain

Further reading

• Dimensional Modeling Techniques — Kimball Group, 2026. Additivity, bridge tables, junk, role-playing and factless facts in one index.
• Snapshots — dbt Labs docs, 2026. Recording history for sources that overwrite in place.
• About MetricFlow — dbt Labs docs, 2026. How metrics are defined once and reused.
• Activity Schema specification — ActivitySchema project, 2026. The definition of a single activity stream table.