Dimensional Modeling and Star Schema Concepts Questions

Understand fact and dimension tables, surrogate keys, and slowly changing dimensions. Be able to write queries that efficiently query dimensional data structures. Understand grain of fact tables and how to aggregate appropriately.

HardSystem Design

27 practiced

Design a real-time enrichment layer to attach slowly changing dimension attributes (e.g., user tier, experiment bucket) to streaming events before landing them into a facts store for near-real-time analytics. Discuss cache design (read-through vs write-through), TTL and stale data handling, consistency guarantees, fallback behavior if enrichment fails, and how to persist enriched vs raw events.

Sample Answer

Requirements & constraints:- Enrich streaming events (ms–s latency) with slowly changing dimension (SCD) attributes for near-real-time analytics.- Low added latency, high availability, eventual consistency acceptable for many BI use-cases, ability to backfill/correct historical facts.

High-level design:- Streaming ingestion (Kafka) → Enrichment service (stateless microservice) → Output topic(s) → Facts store (columnar OLAP / event table) and raw events lake.- SCD store (authoritative): transactional DB for source-of-truth (e.g., PostgreSQL, Spanner) + materialized views into a fast key-value store (Redis/ElastiCache or DynamoDB with DAX) used as cache.

Cache design:- Read-through cache is preferred: enrichment service queries cache; on miss cache loads from SCD store and returns to caller. Benefits: simple cache coherence for readers, avoids double writes and keeps SCD authoritative.- Write-through used only if enrichment service were the single writer to SCD (rare); otherwise avoid due to complexity.- Use versioned keys (entity_id#valid_from or entity_id#version) so multiple SCD versions can be looked up, and to support point-in-time joins.

TTL & stale data handling:- TTL: relatively long (minutes–hours) because SCD changes are infrequent. But combine TTL with change-notification invalidation: when SCD updates occur, publish invalidation events to a cache-invalidation topic to proactively evict/update entries.- Staleness window: record cache metadata: last_refreshed and source_version. Track acceptable staleness per attribute; if stale beyond SLA, flag event.

Consistency guarantees:- Provide at-least-once enrichment: enrichment service can retry on transient failures; the facts store deduplicates by event_id to handle duplicates.- Strong consistency for critical attributes: if strict recency required, enrichment service can read-through to SCD store (no cache) or use conditional read from a strongly-consistent store (Spanner/strongly-consistent DynamoDB).- Default: eventual consistency acceptable; surface freshness metadata to BI consumers so dashboards can filter.

Fallback behavior:- If enrichment fails (cache & SCD unavailable, or timeout), fallback strategies: - Emit raw event to raw-events topic with enrichment_needed flag and minimal placeholder attributes (null or “unknown” + reason code). - Emit best-effort partial enrichment (attributes available) and set enrichment_quality metadata. - Push failed enrichment events to a retry queue and a dead-letter queue for operator intervention and backfill.

Persisting enriched vs raw events:- Always persist raw event (immutable) into a data lake (S3) and a raw-events topic. This allows re-enrichment/backfill if SCD logic changes.- Persist enriched events to the facts store for near-real-time analytics. Include: - Enriched attributes - enrichment_version and source_version (SCD snapshot id or timestamp) - enrichment_quality/freshness metadata and link to raw_event_id- Backfill pipeline: periodic batch rejoin raw events with latest or historical SCD (using versioned keys) and upsert into facts store to correct earlier approximations.

Operational & BI considerations:- Expose freshness/failure metrics and attribute-level confidence on dashboards.- Provide tools for backfill re-enrichment, and maintain lineage so analysts can compare analyses using “near-real-time” vs “fully backfilled” datasets.- For ad-hoc queries needing absolute correctness, recommend query against batch-fact tables built from fully re-enriched data.

Trade-offs:- Read-through + cache invalidation balances latency and correctness for BI; strong consistency increases latency and load on SCD store.- Keeping raw events ensures reproducibility and allows fixing enrichment logic without data loss.

HardSystem Design

25 practiced

Architect a multi-region retail data warehouse to support both near-real-time dashboards for local store managers (latency < 5 minutes) and heavy historical analytics for global business teams. Include: star schema guidelines (fact grain choices), replication and data locality strategies, CDC for source systems, handling SCDs across regions, partitioning/sharding, and choices for data storage engines. Discuss consistency/latency trade-offs and cost considerations.

Sample Answer

Requirements & constraints:- Functional: near-real-time (<5 min) dashboards per region; heavy historical/global analytics.- Non-functional: multi-region latency, eventual cross-region consistency, cost-conscious, scalable.

High-level architecture:- Local region cluster per geography (edge): ingest CDC, OLAP store for low-latency dashboards.- Central/global data warehouse: consolidated long-term store for heavy analytics.- Streaming layer between: Kafka (regional topics + geo-replication via MirrorMaker / Confluent Replicator) or cloud-native streaming (Pub/Sub, MSK + Mirror).- CDC: Debezium or cloud CDC pushing change events into regional topics.

Star schema & fact grain:- Use conformed dimensions (Product, Store, Time, Customer) with stable surrogate keys.- Facts: - Receipt/Event-level fact_sales_grain = one row per transaction-line (recommended for flexibility). - Aggregated hourly_store_sales for dashboard fast reads (pre-aggregate materialized views).- Grain decisions: raw event fact for historical/ML; aggregated facts for dashboard performance.

Replication & data locality:- Keep hot dashboards and recent partitions in-region (local OLAP like ClickHouse, Snowflake reader accounts in-region, or Redshift RA3 with datasharing).- Asynchronously replicate CDC streams to central warehouse for global analytics; use multi-master metadata (UIDs) for dedupe.

SCD handling:- Dimensions: SCD Type 2 for slowly changing attributes with surrogate keys and effective_from/effective_to, plus current_flag.- Implement SCD updates at CDC consumer: apply merge/upsert with versioning in regional store; propagate full history to central warehouse.- Resolve conflicts centrally by last-writer-wins plus source-priority rules; include change metadata (source_region, txn_ts).

Partitioning / sharding:- Facts partitioned by date (daily) and region for efficient pruning; sub-partition by store_id for hot stores.- Shard streaming topics by store_id; consumer groups aligned to shards for parallelism.- Use cluster-local shards to keep reads/writes low-latency.

Storage engines:- Regional low-latency OLAP: ClickHouse, Pinot, or cloud Druid for minute-level dashboards.- Central warehouse for analytics: Snowflake / BigQuery / Redshift Spectrum for scalability and cost-managed storage (cold data on object storage: S3/GCS).- Use materialized views in central warehouse for common heavy queries.

Consistency / latency trade-offs:- Prefer eventual consistency across regions to meet <5 min dashboard SLA locally. Local CDC -> local store ASAP (<1-2 min); central consolidation asynchronously (minutes to hours).- For cross-region OLTP-like consistency, implement read-after-write only locally; global reconciliation via nightly consistency checks and compensating merges.- If stricter cross-region consistency required, add synchronous replication at higher cost and latency.

Cost considerations:- Keep hot data in expensive low-latency stores (regional) for rolling window (e.g., 7–30 days); archive older data to cheaper object storage and query via external tables.- Use streaming + incremental updates to avoid full table copies.- Use auto-scaling / pause features in central warehouses; pre-aggregate to reduce compute.- Monitor egress costs for cross-region replication; favor cloud provider regional services or compress/compact CDC payloads.

Operational notes:- Observability: end-to-end lag metrics (CDC → topic → consumer → store), data quality checks, and automated backfills.- Security: encryption in transit/rest, IAM per region, GDPR-aware data locality.- Testing: simulate region failover, conflict scenarios, and backfill scale.

This design balances fast local dashboards with a scalable central warehouse, uses event-driven CDC for near-real-time updates, SCD Type 2 for history, partitioning for performance, and cost controls via data tiering and pre-aggregation.

EasyTechnical

19 practiced

Describe surrogate keys and natural keys in the context of dimensional modeling. Explain why BI solutions often prefer surrogate keys for dimension tables. Provide at least three reasons and one scenario where using the natural key is acceptable.

HardTechnical

35 practiced

A product attribute historically lived in product_dim but now logically belongs in supplier_dim after an organizational change. You must refactor the dimensional model without losing history and while keeping facts joinable. Describe migration steps to move the attribute, preserve historical correctness, update existing SCD2 rows, and ensure existing reports don't break during transition. Include a rollback plan.

Sample Answer

Goal: move one attribute (e.g., preferred_packaging) from product_dim to supplier_dim, preserve history (SCD2), keep fact tables joinable during/after migration, and allow rollback.

Migration plan (staged, reversible):1. Analysis & design- Identify attribute owner change, business key(s) for product_dim and supplier_dim, impacted ETL, reports and downstream consumers.- Decide canonical location: supplier_dim holds attribute; product_dim will reference supplier via surrogate_key(s) or late-binding view.- Define SCD2 semantics on supplier_dim (effective_from, effective_to, is_current, surrogate_key).

2. Non‑disruptive dual-write staging- Create supplier_dim_staging and supplier_dim_new (SCD2) without deleting product_dim attribute.- Update upstream ETL to populate supplier_dim_staging from sources (source-of-truth) and also continue writing the attribute to product_dim (dual-write) for N days/versions to compare.

3. Populate supplier_dim_new and preserve history- For each source change event, insert new supplier_dim row (new surrogate_key) with proper effective_from/to.- For historical mapping where supplier attribute historically changed but product_dim captured it, backfill supplier_dim_new using product_dim historical SCD2 rows joined by business key + timestamp to generate supplier SCD2 rows that reflect the historical attribute values at the appropriate periods.

4. Keep facts joinable (two-layer join approach)- Add supplier_surrogate_key to facts where possible: create a mapping table product_to_supplier_key(product_surrogate, supplier_surrogate, valid_from, valid_to).- Build a mapping process that for each fact row timestamp finds the supplier_surrogate valid at that time (lookup by product identity + fact_date between effective_from/to).- Expose a view facts_with_supplier that joins facts → product_dim → product_to_supplier_key → supplier_dim_new, ensuring existing reports using product_dim continue to work.

5. Transition layer & compatibility views- Create compatibility views: - product_dim_view: returns product_dim attributes plus supplier attribute (if reporting still expects it) by LEFT JOIN to supplier_dim_new based on mapping/temporal lookup. - supplier_dim_view: canonical supplier view.- Point BI reports to views, not base tables. Keep synonyms for names if required.

6. Testing & validation- Reconcile counts and values between product_dim attribute and supplier_dim_new across time windows.- Run parallel reports and compare metrics (row-level diffs, aggregation parity).- Performance test joins and add indexes on mapping and effective dates.

7. Cutover- Once validated, stop dual-write to product_dim for that attribute; remove column population (keep column but mark deprecated).- Update views so product_dim_view sources attribute only from supplier_dim_new.- Update ETL documentation and communicate change.

8. Rollback plan- Keep product_dim populated and unchanged until final sign-off.- If issues, revert BI views to original product_dim-only view (no change to base ETL), disable use of supplier_dim_new for reporting, and drop mapping consumers. Since product_dim still contains historical values, reports will return original values.- Maintain backups: snapshot supplier_dim_new, product_dim prior to cutover, and versioned ETL code in CI so you can restore previous behavior quickly.

Edge cases & governance- If multiple suppliers per product over time, mapping must be many-to-many with effective ranges.- If attribute was NULL historically, ensure backfill preserves NULLs.- Ensure surrogate keys immutable; never reuse.- Document lineage, owners, and update data contracts.

This approach preserves SCD2 history by building supplier SCD2 rows from historical product records, keeps facts joinable via a temporal mapping, minimizes downstream breakage via compatibility views and dual-write, and includes a clean rollback path.

MediumTechnical

26 practiced

Given a staging table source_customers and a customer_dim SCD2 table, write SQL that detects which source rows need a new SCD2 insertion because one or more tracked attributes changed. Schemas:

sql

source_customers(customer_id int, email text, name text, updated_at timestamp)
customer_dim(surrogate_id int, customer_id int, email text, name text, effective_from timestamp, effective_to timestamp, is_current boolean)

Return the source rows that require inserts and the columns that changed. Use SQL techniques suitable for large tables.

Sample Answer

Approach: join each source row to the current SCD2 row for that customer (is_current = true). For large tables use indexed join on customer_id and avoid expensive per-row subqueries; to detect changes compare tracked attributes directly or with a checksum. Return only source rows where at least one tracked attribute differs and list which columns changed.

sql

-- 1) Using explicit column comparisons (preferred for clarity)
SELECT
  s.customer_id,
  s.email,
  s.name,
  s.updated_at,
  -- list changed columns
  CONCAT_WS(',',
    CASE WHEN COALESCE(s.email,'') <> COALESCE(d.email,'') THEN 'email' END,
    CASE WHEN COALESCE(s.name,'')  <> COALESCE(d.name,'')  THEN 'name'  END
  ) AS changed_columns
FROM source_customers s
LEFT JOIN customer_dim d
  ON d.customer_id = s.customer_id
  AND d.is_current = true
WHERE
  -- no current row (new customer) OR any tracked attr changed
  d.surrogate_id IS NULL
  OR COALESCE(s.email,'') <> COALESCE(d.email,'')
  OR COALESCE(s.name,'')  <> COALESCE(d.name,'');

-- 2) Alternative: checksum approach for many tracked columns
SELECT
  s.*,
  CASE WHEN d.surrogate_id IS NULL OR md5(CONCAT_WS('||', COALESCE(s.email,''), COALESCE(s.name,'')))
       <> md5(CONCAT_WS('||', COALESCE(d.email,''), COALESCE(d.name,'')))
       THEN true ELSE false END AS needs_insert
FROM source_customers s
LEFT JOIN customer_dim d
  ON d.customer_id = s.customer_id AND d.is_current = true
WHERE d.surrogate_id IS NULL
   OR md5(CONCAT_WS('||', COALESCE(s.email,''), COALESCE(s.name,'')))
      <> md5(CONCAT_WS('||', COALESCE(d.email,''), COALESCE(d.name,'')));

Key points:- Use COALESCE to handle NULLs consistently.- CONCAT_WS/CASE gives readable changed_columns for downstream audit.- Ensure indexes on customer_dim(customer_id, is_current) and source_customers(customer_id).Complexity: single scan of source table + index lookup/join to current rows — O(N) on source with efficient join. Edge cases: NULLs, whitespace differences (consider TRIM/LOWER), type mismatches, and timezone handling for timestamps. For very wide schemas use checksum to reduce SQL verbosity but be aware of rare hash collisions and reduced explainability.

Unlock Full Question Bank

Get access to hundreds of Dimensional Modeling and Star Schema Concepts interview questions and detailed answers.

Join thousands of developers preparing for their dream job.