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.