Data Warehouse and Dimensional Modeling Questions

Design and model scalable analytical data systems using dimensional modeling principles and data warehouse architecture patterns. Core concepts include fact and dimension tables, defining and enforcing grain, surrogate keys, degenerate and role playing dimensions, conformed dimensions, and handling slowly changing dimensions including Type One, Type Two, and Type Three. Understand schema choices and trade offs such as star schema versus snowflake schema, normalization versus denormalization, and fact table types including transactional, periodic snapshot, and accumulating snapshot. Apply design decisions to meet query patterns and performance goals by considering partitioning, indexing, compression, columnar storage, and aggregation strategies. Be able to design schemas for different business domains, reason about data integration and consistency, and optimize for common analytical workloads and reporting requirements.

MediumSystem Design

74 practiced

Design a star schema to support order analytics for an e-commerce system. Requirements: analyze order revenue by customer segment, product categories, sales channel and day. Provide the grain, list the fact table columns (including measures) and at least five dimensions with key attributes. Explain surrogate key choices and how you would model returns/refunds.

Sample Answer

Grain:- One row = one order line (one product on one order) at the time of order completion (order_line_level, captured once payment settled). This supports slicing by day, product, customer, channel.

Fact table: fact_order_line- surrogate_key (order_line_sk, INT, PK)- order_id (natural)- order_line_id (natural)- order_date_sk (FK to dim_date)- product_sk (FK to dim_product)- customer_sk (FK to dim_customer)- customer_segment_sk (FK to dim_customer_segment) — optional if segments change quickly- category_sk (FK to dim_category)- sales_channel_sk (FK to dim_sales_channel)- promotion_sk (FK to dim_promotion, nullable)- quantity_ordered (INT)- unit_price (DECIMAL)- line_gross_amount = quantity_ordered * unit_price (DECIMAL)- discounts_amount (DECIMAL)- tax_amount (DECIMAL)- shipping_amount (DECIMAL)- line_net_amount = line_gross_amount - discounts_amount + tax_amount + shipping_amount (DECIMAL) — measure- cost_amount (DECIMAL) — for margin- line_profit = line_net_amount - cost_amount (DECIMAL)- refund_flag (BOOLEAN) or refunded_amount (DECIMAL)- created_at, last_update_at

Dimensions (each with surrogate PK and slowly changing dimension type):1) dim_date: date_sk (PK), date, day, week, month, quarter, year, day_of_week2) dim_customer: customer_sk (PK), customer_id, name, email, signup_date, country, state, city, lifetime_value, current_segment, SCD type 2 fields (effective_from, effective_to, is_current)3) dim_customer_segment: customer_segment_sk, segment_code, segment_name, description4) dim_product: product_sk, product_id, sku, name, brand, category_sk, subcategory, price, current_cost, attributes; model SCD type 2 if product attributes change5) dim_category: category_sk, category_id, category_name, parent_category6) dim_sales_channel: sales_channel_sk, channel_code (web/mobile/marketplace/phone), channel_name, partner_id7) dim_promotion (optional): promotion_sk, promo_code, promo_type, start_date_sk, end_date_sk

Surrogate key choices:- Use integer surrogate keys for all dimensions for join performance and to support SCDs. Natural keys (order_id, product_id, customer_id) retained in facts for traceability. SCD type 2 on customer and product so historical analyses reflect state at order time via effective dates or by snapshotting FK at order time.

Modeling returns/refunds:- Two options (recommended combined): 1) Capture refunds as negative fact rows: when a return occurs, insert a new order_line fact with same natural order_line_id or return_id, negative quantity and negative amounts, linked to a return_reason_dim and refund_date_sk. This keeps revenue rollups simple. 2) Maintain a separate fact_refund table for detailed returns (refund_id, order_line_sk, refund_date_sk, refunded_quantity, refunded_amount, restocking_fee, reason_sk). Then ETL joins to compute net revenue by subtracting refunds from orders in reporting layers.- Include refunded_amount and refund_flag on fact_order_line to support quick filters. Use reason dimension for analyses by cause.

Notes:- Precompute aggregates (daily/channel/category) in aggregate fact tables or materialized views for large volumes.- Ensure currency handling (currency_code, exchange_rate) if multi-currency.

MediumTechnical

140 practiced

What is a factless fact table? Provide two real-world examples (e.g., student_attendance, ad_impression) and explain how factless facts support analytical queries such as coverage analysis, enforcement checks, or funnel steps. Explain how you choose the grain for a factless fact.

MediumTechnical

98 practiced

You have streaming event facts and daily account_state snapshots. Explain how to reconcile both data sources to avoid double-counting (for example, an event that is reflected in both the event stream and snapshot). Propose an architecture and processes (deduplication windows, event_apply_window, reconciliation jobs, canonical keys) to combine events and snapshots into a single analytic layer consistently.

Sample Answer

High-level approach:- Treat snapshots as point-in-time canonical state and events as deltas. Build an analytic layer that merges them deterministically so any entity/state is counted once.

Architecture & components:1. Ingest - Stream events into a messaging system (Kafka) with event time and ingestion time. - Load daily account_state snapshots into a staging table (S3/DB) with snapshot_date.

2. Canonical key - Define a stable primary key for accounts (account_id) + optional namespace (region, product) and versioning (source, source_id). - Normalize identifiers during ingestion (ID mapping table).

3. Deduplication & event_apply_window - For streaming events: dedupe by (canonical_key, event_id) in a sliding deduplication window (e.g., 24–72 hours) using a compacted store (Kafka compacted topic or Redis). - Define event_apply_window per snapshot frequency: events earlier than snapshot_date - grace_period (e.g., 1 day) are already materialized in that snapshot; events arriving within [snapshot_date - grace_period, snapshot_date + apply_window] must be reconciled. - Apply_window example: if snapshots taken at 00:00 UTC daily and late-arriving events may be up to 48h late, set apply_window = 48h.

4. Reconciliation job (daily) - For each snapshot_date: - Load snapshot as base state. - Pull all events for canonical_keys in snapshot with event_time > snapshot_date - grace_period and <= snapshot_date + apply_window. - Apply events in event_time order to mutate snapshot to “as-of” consistent state. - If an event duplicates a change already present in snapshot (compare event_id or change hash), skip (de-dup). - Persist merged records into analytic layer (e.g., analytics table partitioned by date) and mark provenance (snapshot_version, applied_event_ids).

5. Ongoing streaming merge - Maintain a change table that applies deduped events in near real-time to analytic view between snapshots. At snapshot reconciliation, compute a delta between snapshot-merged state and current change table; reconcile conflicts using last-write-wins by event_time + source_priority.

6. Monitoring & metrics - Track counts: events ingested vs applied vs deduped vs ignored by snapshot. - Alert when late-event rate > threshold or reconciliation diff > expected bounds.

Edge cases & practices:- Late arrival: keep tombstones and idempotent apply logic.- Reconciliation audits: store hashes and small sample export of diffs for manual inspection.- Backfill: re-run reconciliation for historical snapshots when schema changes.

Example:- Snapshot taken 2025-11-20 00:00. apply_window=48h.- Event at 2025-11-19 23:50 with event_id E1 — included in reconciliation for 2025-11-20.- Event at 2025-11-20 02:00 with same event_id E1 — deduped by event_id store; not double-counted.

This design balances realtime needs with daily authoritative snapshots, uses canonical keys and idempotent operations to avoid double-counting, and provides auditability for analysts.

EasyTechnical

76 practiced

Explain what a degenerate dimension is in a star schema and provide two concrete examples (e.g., invoice_number, transaction_id). Discuss why you might keep such attributes in the fact table rather than creating a separate dimension and any downsides of keeping them in the fact.

MediumTechnical

99 practiced

Explain additive, semi-additive, and non-additive measures with examples (e.g., sales_amount is additive, account_balance is semi-additive, conversion_rate is non-additive). For each type describe how aggregations across time or other dimensions should be handled and modeling strategies to report them correctly.

Unlock Full Question Bank

Get access to hundreds of Data Warehouse and Dimensional Modeling interview questions and detailed answers.

Join thousands of developers preparing for their dream job.