Data Modeling Interview Questions

Sample Answer

Set the grain to one row per play event, and use a surrogate event_id as the primary key. Natural keys like (user_id, session_id, event_ts) look tempting, but event_ts is not guaranteed unique and session_id can be missing or reused across devices. You keep user_id, device_id, content_id, session_id as foreign keys and attributes, but you do not rely on them for uniqueness. This makes joins to dimensions safe and makes play counts stable under late arriving events.

Sample Answer

You could key OrderItem by a natural composite (order_id, sku_id, line_number) or by a surrogate order_item_id. The surrogate wins here because refunds and adjustments need a stable target even if SKUs change, items split, or line numbers are reissued. Model Order (PK order_id) 1 to many OrderItem (PK order_item_id, FK order_id), and OrderItem 1 to many Refund (PK refund_id, FK order_item_id) with Refund carrying refunded_qty and refunded_amount. Compute order totals by aggregating from OrderItem minus Refund at query time or in a derived summary table, not by updating Order in place, so you avoid reconciliation drift.

Sample Answer

Start with entities Listing (PK listing_id) and Host (PK host_id). Because the relationship is many to many and changes over time, you add a bridge, ListingHostAssignment, with PK (listing_id, host_id, effective_start_ts) and attributes effective_end_ts and is_primary. Cardinality is Listing 1 to many assignments, Host 1 to many assignments, and time filters pick the row where $D \in [\text{start}, \text{end})$. To answer both questions, you filter assignments active on D, then either pick is_primary for the listing view or count distinct listing_id for the host view.

Sample Answer

You could denormalize product_name into order_items for fast reads, or normalize and always join to products. Denormalization wins here if the UI needs the name as of purchase time, because later edits should not change historical receipts, and it cuts join cost on the hottest path. If you denormalize, you treat product_name as an immutable snapshot field and keep product_id as the source of truth, plus you add backfills only for data fixes, not routine edits. If you normalize, you accept join latency and caching complexity, but you avoid duplication and drift.

Sample Answer

First, map the query pattern: analysts want latest profile values, so joining to a current user_profile dimension is naturally aligned. Next, look at update frequency and scale: denormalizing daily profile changes into a 5 TB per day fact explodes write and backfill cost for little benefit. Then evaluate correctness: duplicating mutable attributes causes drift across partitions when updates are partial or late, and you will get inconsistent results depending on scan range. So you keep the fact normalized, and you optimize the join with clustering, dictionary encoding, materialized views, or a curated wide table refreshed on a schedule.

Sample Answer

Start by listing measures and where they live: GMV and category are item level, active buyers is order or buyer activity level. If you put the fact grain at order item, you can sum item revenue for GMV by category, but you must compute active buyers using distinct buyer_id at the chosen time grain to avoid counting a buyer multiple times. For average items per order, you aggregate to order first, for example count items per order_id, then average those counts by week and category, or compute $$\text{avg items per order} = \frac{\#\text{order items}}{\#\text{distinct orders}}$$ at the reporting grain. Dimensions: date, buyer, product, category, fulfillment channel, and you keep order_id as a degenerate dimension for rollups.

Sample Answer

This question is checking whether you can pick a grain that matches the atomic event and still supports multiple aggregations without duplication. You set the fact grain to one row per playback session, or per playback interval if you need accurate watch time with pauses and seeks, then store watch_seconds and viewer_id. Dimensions include date, title, country, device, and member. To connect to billing later, you conform member, date, country, and plan dimensions across both marts so retention and revenue can be analyzed against viewing behavior without mismatched keys.

Sample Answer

The standard move is to keep the core transactional fact at the booking grain, one row per booking, with measures like booked_amount and dimensions like booking_date, guest, host, listing, and acquisition channel. But here, revenue recognition depends on stay nights, so you also build an allocation fact at one row per booking night, keyed by stay_date, with recognized_revenue per night. Growth uses the booking fact for booking counts and funnel metrics by booking_date, Finance uses the nightly fact for revenue by stay_date, and both share conformed listing, guest, host, and channel dimensions. You avoid confusing time semantics by naming role playing date dimensions explicitly, for example booking_date_key and stay_date_key.

Sample Answer

The standard move is: put the Order business key in a Hub, relationships like Order to Customer in Links, and all descriptive fields in Satellites tracked by load date and hashdiff. But here, snapshot backfills matter because you need idempotent loads and you cannot treat every snapshot row as a new version. You compute a hashdiff over the satellite attributes, only insert when the hashdiff changes, and keep Record Source so you can reconcile differences between the event system and the snapshot system. If backfills can correct old values, you either accept append-only with corrected versions and derive an as-of view downstream, or add effectivity satellites to represent validity windows.

Sample Answer

Get this wrong in production and your marts silently double count, because the same real-world product maps to multiple keys or the same key maps to different products over time. The right call is: pick a canonical business key for the Product Hub, or create separate Hubs for SKU and CatalogProduct, then relate them with a Link that captures mappings and changes over time. You store the mapping rules and provenance in a Satellite on that Link, including effective timestamps if reuse is possible. Downstream, you standardize on one conformed key by materializing a crosswalk view that resolves to the latest valid mapping for the reporting grain.