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.

MediumTechnical

69 practiced

You have sales, marketing, and finance teams that each define products and customers slightly differently. Describe an approach to design conformed dimensions across these domains: governance processes, schema versioning, a core enterprise model with extensions, and technical patterns to surface team-specific attributes while preserving a single canonical key.

EasyTechnical

82 practiced

Given these two tables:

orders(order_id bigint PK, customer_id bigint, order_date date, total_amount numeric)customers(customer_id bigint PK, country varchar, signup_date date)

Write ANSI SQL (Postgres/BigQuery compatible) to compute monthly revenue by country for 2024. Output columns: month (YYYY-MM), country, revenue, number_of_customers. Treat null customer_id as country = 'UNKNOWN'. Use a date_trunc or equivalent for month bucketing.

MediumTechnical

96 practiced

Two source systems provide customer records with overlapping updates: System A is authoritative for contact info, System B for billing info. Propose a dimensional modeling and ETL strategy to merge these into a single customer dimension while preserving history and attribute provenance. Include column-level origin tracking, audit fields, and how to resolve conflicting updates during merges.

MediumTechnical

84 practiced

For a customer dimension where some attributes (preferences) change very frequently and others (date_of_birth) rarely change, propose a storage and modeling strategy that balances storage, query performance, and analytic needs. Consider using mixed SCD types, separate history tables, event tables, or pivoted attribute stores, and explain which queries are optimized or degraded by each choice.

MediumTechnical

97 practiced

An ETL job accidentally overwrote address fields for 5M customer rows with bad data. Design an emergency rollback and remediation plan: how to identify affected rows, restore previous values (e.g., from audit/history tables, backups), validate the restoration, communicate with stakeholders, and add safeguards to prevent recurrence.

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.