Dimensional Modeling and Schema Design Questions
Designing analytical schemas: star and snowflake schemas, fact and dimension tables, grain selection, slowly changing dimensions, and normalization vs denormalization for analytics. Covers modeling business requirements into queryable structures and schema design for large datasets. The data-modeling backbone of BI and warehouse work.
You need a star schema where the natural facts occur at two different grains at once, for example session-level and event-level activity. Describe your options for modeling this (separate fact tables at each grain, a single consolidated multi-grain fact table with nulls for the coarser grain's extra columns, or an event table with derived aggregates), and explain how you would support cross-grain analysis without double counting.
Sample Answer
Direct answer
For facts that occur at two different natural grains (session-level and event-level), the cleanest option is usually two separate fact tables, one per grain, sharing conformed dimensions; a single consolidated multi-grain table with nulls works but complicates every query with grain-awareness logic, and cross-grain analysis is best done by joining/aggregating the two separate tables at query time rather than forcing one physical table to serve both.
Structured elaboration
- Separate fact tables (recommended default):
session_fact(session_id, user_key, date_key, session_duration, page_count)andevent_fact(event_id, session_id, user_key, date_key, event_type), each internally consistent at its own grain, avoiding the aggregation-logic bugs that come from mixing grains in one table. - Consolidated multi-grain table with nulls: one table where session-level rows have event-specific columns NULL and vice versa. This avoids a second table but means every query MUST filter by a grain-indicator column first or risk silently mixing grains in an aggregation (summing a measure that only exists at one grain, inflated by NULL-padded rows from the other grain if the filter is forgotten).
- Event table with derived aggregates: store only the finest grain (events) and derive session-level metrics via a view or materialized aggregate on top, which avoids grain duplication entirely but pushes session-level query cost onto an aggregation over potentially many events per session.
- Cross-grain analysis without double counting: whichever approach is used, cross-grain analysis (comparing session count to event count) must never join the two grains directly on a shared key without an explicit aggregation step first (for example, joining event_fact to session_fact directly and summing a session-level measure would multiply it by the number of matching events).
Worked example
"Average events per session" needs event_fact aggregated to session grain first (COUNT(*) GROUP BY session_id), THEN averaged, not a direct join of session_fact to event_fact followed by a naive average, which would double count session-level attributes once per matching event row. With two separate fact tables, this two-step aggregate-then-analyze pattern is natural; with a single consolidated table, the same discipline is needed but is easier to accidentally skip since both grains superficially live in "one table."
Trade-offs and pitfalls
The consolidated multi-grain table's appeal (one table, seemingly simpler) is usually outweighed by the ongoing risk of a grain-unaware query silently mixing levels; two clean, single-grain fact tables cost a bit more extract, transform, load (ETL) and dimension-conformance discipline but make grain-mixing bugs much harder to write accidentally, since each table only ever means one thing.
You are designing a fact table for cart-abandonment analysis. Stakeholders want to measure abandonment by session, device, and marketing campaign. Propose at least two candidate grains for this fact table (for example, one row per session versus one row per cart event), list the trade-offs of each, and recommend which one to implement first for the fastest business value.
Sample Answer
Direct answer
Two candidate grains: session-level (one row per abandoned session, aggregating all cart activity within it) and event-level (one row per cart-modification event: add, remove, quantity change). Start with session-level: it directly answers "how many sessions abandoned a cart" with the least modeling and extract, transform, load (ETL) effort, and covers the stated dimensions (session, device, campaign) without needing event-sequence logic.
Structured elaboration
- Session-level grain:
cart_abandon_fact(session_id, device_key, campaign_key, date_key, cart_value, item_count), one row per session that had cart activity but no completed order. Cheap to build, directly supports "abandonment rate by device" and "abandonment rate by campaign" dashboards, the most commonly requested cuts. - Event-level grain:
cart_event_fact(event_id, session_id, device_key, campaign_key, date_key, event_type, product_key, quantity), one row per add/remove/update action. Supports deeper questions (which specific products get added then removed before abandonment, at what point in a multi-step checkout users typically drop off) but costs more storage and requires session-reconstruction logic (grouping events into sessions, detecting "no completed order" as an absence, which is trickier to get right than a single per-session flag). - Recommendation: implement session-level first. It answers the stated business question (abandonment by session, device, campaign) with a simple, well-understood grain, and ships faster. Event-level is the natural follow-up once the team wants product-level or funnel-step-level abandonment insight, and it can be built without re-doing the session-level table (both can coexist, one being a summary of the other).
Worked example
A user adds two products to their cart, removes one, then leaves without checking out. At session grain, this produces one row: (session_id=S1, device=mobile, campaign=summer_promo, cart_value=$45, item_count=1). At event grain, it produces three rows (add product A, add product B, remove product B), which is the only grain that could tell you "products get removed from this cart before abandonment more often than average," but is unnecessary if the immediate business question is just "abandonment rate by device and campaign."
Trade-offs and pitfalls
The risk with jumping straight to event-level is over-building for a question nobody asked yet, and under-delivering on the actual near-term ask (a clean abandonment-rate-by-device dashboard) while the team is busy handling event-sequencing edge cases (out-of-order events, session-boundary detection). The risk with staying at session-level forever is that the moment someone asks a funnel or product-level question, the fact table can't answer it and a rebuild is needed; keeping both grains as separate, coexisting fact tables (not trying to force one table to serve both purposes) avoids that trap.
For a B2B SaaS analytics product, compare three ways to power the BI dashboards: a classic star schema, a single wide denormalized table, and a lakehouse (raw events plus views). Recommend one approach and justify it in terms of migration effort, query performance, and long-term maintainability.
Sample Answer
Direct answer
For a B2B SaaS analytics product, a star schema is usually the right default: it gives predictable, fast joins for the tenant/user/feature-usage dimensions this kind of product almost always needs, without the migration risk of a lakehouse rebuild or the flexibility loss of a single wide table. Recommend star schema unless the product has a specific reason (extremely wide, sparse, frequently-changing event schemas) to prefer the alternatives.
Structured elaboration
- Star schema: predictable joins, mature business intelligence (BI)-tool support, and straightforward multi-tenant filtering (a
tenant_keyon the fact table plus row-level security). Migration effort from an existing normalized online transaction processing (OLTP)-backed reporting layer is moderate. Long-term maintainability is high because the schema is explicit and self-documenting. - Single wide denormalized table: fastest to query for a fixed, known set of dashboards (no joins at all), but brittle: every new metric or dimension requires a wide-table schema change, and it doesn't scale well to many different fact grains (usage events, billing events, support tickets) without collapsing them into one table with lots of nulls.
- Lakehouse (raw events plus views): the most flexible for teams whose event schema changes frequently or who need to support ad-hoc, exploratory analysis over raw data, but the highest migration effort (rebuilding ingestion, adopting new tooling) and the least predictable query performance without careful partitioning/clustering discipline.
- Recommendation logic: a B2B SaaS product's core dashboards (tenant usage, feature adoption, billing) are well-known in advance and benefit from the star schema's query predictability and BI-tool ergonomics; only lean toward a lakehouse if the product's analytics needs are genuinely exploratory and evolving faster than a star schema's dimensions can keep up.
Worked example
A usage-analytics dashboard needs "feature adoption by tenant, by plan tier, over time." In a star schema: usage_event_fact(tenant_key, feature_key, plan_key, date_key, event_count) joined to three small dimensions, a query any BI tool generates automatically. In a wide table, this dashboard is fast, but adding a new cut (adoption by industry vertical) means adding a column and backfilling it across the whole wide table. In a lakehouse, the same query requires a view or transformation layer over raw events, adding a layer of query-time complexity most BI users shouldn't need to reason about.
Trade-offs and pitfalls
Migration and maintainability matter as much as raw query performance here: a wide table is fast to build initially but accumulates technical debt fast as requirements grow; a lakehouse is the most future-proof for genuinely unpredictable analytics needs but the most expensive to stand up and keep performant. Choosing the star schema is a bet that the product's core analytics questions are knowable in advance, which is usually true for B2B SaaS dashboards even if it isn't true for every analytics product.
Design an analytics data model for an e-commerce company processing 1 million transactions a day that needs near-real-time dashboards for orders, revenue, inventory, and product performance. Name the fact and dimension tables (with key columns), your SCD handling for the changing dimensions, and how you would get data in near-real-time (CDC versus streaming). State any technology assumptions you make.
Sample Answer
Direct answer
Start by declaring the grain of each fact table before naming dimensions: an order-line fact at "one row per order line item" for revenue and inventory, and near-real-time ingestion via change-data-capture (CDC) from the operational database, landing in a staging layer that's transformed into the star schema on a short (minutes-scale) cadence. Handle the changing dimensions (customer, product, inventory location) with Slowly Changing Dimension Type 2 where historical accuracy matters.
Structured elaboration
- Fact tables:
order_line_fact(order_line_id, order_key, product_key, customer_key, store_key, date_key, quantity, unit_price)at order-line grain for revenue and product-performance dashboards;inventory_snapshot_fact(product_key, store_key, date_key, on_hand_qty)as a periodic (hourly or daily) snapshot for inventory-level reporting, since inventory level is a semi-additive measure that should not be summed across time. - Dimensions:
product_dim,customer_dim(Slowly Changing Dimension (SCD) Type 2 on attributes like segment or address, since historical order reports must show the customer state as of the order),store_dim,date_dim. - Near-real-time ingestion, CDC vs. streaming: CDC (reading the operational database's transaction log) is the natural fit here since the source of truth is already a relational online transaction processing (OLTP) system emitting inserts/updates; a lightweight CDC connector (e.g., a log-based CDC tool) streams change events into a staging area, and a short-interval transformation job (every 1-5 minutes) applies them into the warehouse's fact and dimension tables, including Slowly Changing Dimension Type 2 (SCD2) versioning for dimension changes. A pure streaming architecture (events published directly by the application) would be preferable only if the source weren't already a transactional database with a readable change log.
- Handling scale: at 1M transactions/day (roughly 12/second average, higher at peak), partition the fact tables by date and consider clustering by a high-selectivity column like
store_keyorproduct_keydepending on the dominant query pattern, so dashboards don't scan the full history on every query.
Worked example
A customer places an order at 2:00pm containing 3 line items. CDC captures the orders and order_items inserts within seconds, lands them in staging, and the 5-minute transform job resolves each line item's customer_key (via the customer's current SCD2 version as of order time), product_key, and store_key, then inserts 3 rows into order_line_fact. A revenue dashboard refreshing every 5 minutes sees the order reflected within one cycle; an inventory dashboard sees the corresponding stock decrement in the next inventory_snapshot_fact load.
Trade-offs and pitfalls
The main risk in near-real-time designs is dimension lag: if a new product or customer hasn't yet been captured by the dimension's own CDC/load cycle when its first fact row arrives, you get an orphaned foreign key. Handle this with an "unknown member" placeholder row inserted at fact-load time, backfilled with real attributes once the dimension catches up. Also be explicit about which dashboards genuinely need near-real-time freshness (revenue, inventory) versus which can tolerate hourly or daily batch (long-term trend reports), since near-real-time pipelines cost meaningfully more in operational complexity than they're worth for reports nobody checks intraday.
Tell me about a time you pushed back on a stakeholder who wanted to change the grain of a report, for example from user-level to session-level. Walk through how you approached the conversation, the technical arguments you made, and the outcome.
Sample Answer
Direct answer
A strong answer describes a concrete situation (a stakeholder asked to change a report's grain, for example from user-level to session-level), explains the specific technical consequences that change would have caused (double counting, loss of a metric the stakeholder didn't realize depended on the finer grain), how the conversation was framed around the STAKEHOLDER's actual goal rather than a rules-lawyering defense of the schema, and what was ultimately agreed and delivered.
Structured elaboration
- Situation: set up the concrete business context, who asked for the change and why, and what they believed the change would accomplish.
- Task: the specific technical risk in play, for example that dropping from user-level to session-level grain would silently break an existing "average sessions per user" metric several other dashboards depended on, or would require re-deriving a metric that couldn't be reconstructed once the finer detail was discarded.
- Action: how the conversation was handled, ideally not a flat "no" but an explanation in the stakeholder's own terms of the specific consequence (which metric breaks, which report becomes wrong), paired with an alternative that met their actual underlying need (a session-level SUMMARY VIEW built on top of the existing user-level grain, rather than changing the base table's grain itself).
- Result: the concrete outcome, for example the stakeholder got their session-level dashboard via a derived view while the base fact table's grain (and everything built on it) stayed intact, and ideally a lasting change like a documented grain-change review process for that table going forward.
Worked example
A product analytics stakeholder requested changing event_fact's grain from user-level daily aggregate to raw session-level, to power a new dashboard. Investigating revealed three existing dashboards computed metrics (7-day retention, average sessions per active day) directly from the user-level grain that would either break or need re-derivation under the proposed change. Rather than refusing outright, the conversation reframed around the stakeholder's actual need (session-level detail for their specific new dashboard) and proposed building a NEW session-level fact table alongside the existing user-level one, satisfying their requirement without touching the three dependent dashboards. The stakeholder got their dashboard within the same timeline they'd originally hoped for.
Trade-offs and pitfalls
The weak version of this story is either capitulating (changing the grain and quietly breaking downstream reports) or refusing without offering an alternative (protecting the schema at the cost of the stakeholder relationship and the business need going unmet). The strong version demonstrates both the technical judgment to see the consequence clearly and the communication skill to solve the stakeholder's actual problem without that consequence, which is what this behavioral question is really probing for.
Unlock Full Question Bank
Get access to all Dimensional Modeling and Schema Design interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.