Data Modeling Interview Questions

The exact meaning of one row (“one row per order line”). Every fact and dimension must be consistent with it; mixing grains causes double counting. Declaring it first forces clarity and is the most important modeling decision.

Facts are numeric measurements of business events at a declared grain (amount, quantity, duration). Dimensions provide descriptive context to filter and group facts (who, what, where, when).

Star: fact + denormalised dimensions, one join hop, simpler and faster for BI. Snowflake: dimensions normalised into sub-dimensions, less redundancy, more joins. Default to star; snowflake only for large shared hierarchies.

Required for SCD2 (one entity, many versions), decouple from source key changes, integrate multiple sources with clashing IDs, compact joins, and allow unknown members (-1).

Normalise OLTP for write integrity (no update anomalies). Denormalise OLAP for read simplicity and speed; storage is cheap and history is append-only.

Transaction: one row per event, insert-only. Periodic snapshot: one row per entity per period (balances, inventory). Accumulating snapshot: one row per process instance updated as milestones occur (order lifecycle).

Additive sum across all dimensions (revenue). Semi-additive sum across some but not time (balances, inventory: use period-end or average). Non-additive can’t be summed (ratios, unit prices, distinct counts): store components and compute after aggregation.

A fact table with only keys. Event tracking (attendance, logins) where COUNT(*) is the measure, and coverage (products on promotion per day) enabling “what didn’t happen” queries via anti-joins.

Keep an order-grain fact for header amounts and/or allocate them to lines with a documented rule (by value/weight) so line sums reconcile. Never repeat the full header amount on every line.

A dimension with identical keys and attribute meanings shared across fact tables (date, customer, product), enabling drill-across analysis and an integrated warehouse (columns of the bus matrix).

One dimension used in multiple roles in the same fact, e.g. dim_date as order date, ship date, delivery date; dim_airport as origin/destination. Implemented with aliases or views.

Junk: combination of low-cardinality flags in one small dimension. Degenerate: transaction identifier stored on the fact without a dimension table (order_number). Mini: rapidly changing attributes split from a large dimension into a small banded dimension. Outrigger: a dimension referenced by another dimension (use sparingly).

A bridge table (track_key, artist_key, allocation_factor). Joining through it multiplies rows, so use the allocation factor for additive metrics (revenue) or present metrics as non-additive across the many side.

Insert an inferred member (natural key + placeholder attributes, flagged) when the fact arrives, use its surrogate key, and update attributes when the real record arrives. Alternatively map to an unknown member and re-key later (more work).

Parent-child table plus a closure/bridge table of (ancestor, descendant, depth) for easy roll-ups, or recursive CTEs at query time. Fixed-depth hierarchies are simply flattened into dimension columns.

Type 1 overwrites (no history). Type 2 adds a new row with validity dates and a new surrogate key (full history). Type 3 adds a “previous value” column (one level of history).

Stage changed keys twice: once with the natural key as merge key (to close the current row when the hash differs) and once with a NULL merge key (never matches → inserts the new version). New keys insert via NOT MATCHED; unchanged keys do nothing.

Both give “as-was” and “as-is” views. Type 6 adds a current-value column (overwritten across all versions) to SCD2 rows. Type 7 stores both the version surrogate key and the durable natural key on facts, joining to the SCD2 dim (as-was) or a current view (as-is).

You must split an existing version: insert the backdated version, adjust valid_to of the preceding version, and potentially re-key facts that fall in the affected period. It’s expensive, so design processes to minimise backdating or accept as-loaded semantics.

Kimball: business-process stars with conformed dims, fast to deliver, analyst-friendly. Inmon: normalised enterprise DWH feeding marts, integrated but slow. Data Vault: hubs/links/satellites, insert-only, auditable, resilient to source change, but needs a dimensional layer for consumption.

No. OBTs are great serving artifacts for BI on columnar engines, but built from a governed star they stay consistent; without a core model they duplicate logic and drift. Dimension changes also force fact-wide rewrites.

Typed common columns (event_id, user_id, ts, event_type, session_id) plus a properties map/variant; promote hot properties to typed columns or per-type tables; govern with a tracking plan/schema registry.

Above gold tables: defines metrics once (measure, aggregation, filters, allowed dimensions) so every BI tool and API computes “revenue” identically. Prevents metric drift across dashboards.