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

88 practiced

A new business attribute 'customer_segment' must be added historically for reports. Outline the steps to backfill existing dimension rows and to expose the attribute to reporting with minimal disruption. Compare approaches using SCD Type 1 change (overwrite) vs Type 2 (history) and the implications for historical dashboards.

Sample Answer

Situation: We need to add a new business attribute customer_segment to an existing customer dimension and make it available to reports retroactively with minimal disruption.

Approach / Steps to backfill and expose:1. Clarify requirements with stakeholders: Do historical dashboards need the segment as it would have been at event time, or can they use the current segment? Define the business rules and the mapping logic (e.g., revenue bands, tenure, signup cohort).2. Create a reproducible backfill mapping: implement the segment logic in SQL/ETL so it can be applied to any historical snapshot or customer attributes at a given date.3. Sandbox & sample-run: run the mapping on a subset (month or cohort) and validate with business owners.4. Schema change plan: - Add customer_segment column to the customer dimension (nullable) and to any staging tables. - Add any SCD metadata fields needed (effective_from, effective_to, is_current) if choosing Type 2.5. Backfill execution: - For SCD Type 1 (overwrite): run an UPDATE that writes the computed customer_segment into the existing dim_customer rows (in a transaction or batched). Validate row counts, checksums, and a pre/post snapshot for audit. Deploy during low usage/ETL window; keep backups. - For SCD Type 2 (history): generate new versioned rows for each customer where segment changes over time. For each historical time boundary you need to model (e.g., daily/monthly), create new dim rows with appropriate surrogate keys and effective_from/effective_to, and update is_current for the latest row. Ensure surrogate keys don't collide.6. Expose to reporting with minimal disruption: - Create an abstraction/view layer rather than changing downstream dashboards directly. Provide: - dim_customer_current view (SCD1-style current segment) - dim_customer_as_of(date) function/view (SCD2-style join by fact.event_date between effective_from and effective_to) - Update ETL/semantic layer (LookML, Power BI dataset, tableau extracts) to include the new column/reference view. Publish a parallel dataset first if possible.7. Validation & rollout: - Run reconciliation tests comparing counts and key metrics before/after. - Provide example dashboards showing differences between SCD1 and SCD2 results. - Communicate changes, maintain read-only rollback snapshot for the original state.

Comparison: SCD Type 1 vs Type 2 and implications- SCD Type 1 (overwrite) - Pros: simplest; minimal storage; immediate exposure; easy to implement and maintain. - Cons: destroys historical truth — historical dashboards will reflect the new segment for past events (e.g., last year’s orders will show the customer's current segment), possibly misleading trend analysis. - Use when segment is a correction/normalization (not business-state over time) or when historical segmentation isn't meaningful.- SCD Type 2 (history) - Pros: preserves historical state; accurate for time-based analysis; enables "as-of" joins so dashboards reflect the segment valid at each event. - Cons: more complex ETL; larger storage; downstream queries must join by event_date; surrogate keys and versioning add complexity; may need retroactive versioning if fine-grained history required. - Use when the segment is business-state (e.g., VIP status) and historical reporting must reflect past statuses.

Recommendation: If business requires historical correctness for past dashboards choose SCD Type 2 with a date-lookup view (dim_as_of) and add the new attribute via a controlled backfill. If historical fidelity is not required, use Type 1 for speed. In either case, deliver a transitional view (current vs. as-of) so BI consumers can adopt without immediate dashboard changes, provide validation artifacts, and schedule the change during a maintenance window with rollback snapshots.

EasyTechnical

95 practiced

Explain 'grain' with respect to choosing between invoice-level and invoice-line-level sales facts. For BI reporting needs that include SKU-level performance and invoice-level commission splits, which grain would you choose and why? What are the consequences for storage and ETL?

EasyTechnical

76 practiced

Compare star schema and snowflake schema for analytical reporting. Describe the structural differences, how each impacts query performance, storage and maintainability, and give two concrete examples of when you'd pick star over snowflake and vice versa for a BI team's reporting needs.

Sample Answer

Definition & structure:- Star schema: one large fact table (denormalized measures) connected directly to flat dimension tables (each dimension is a single table containing attributes).- Snowflake schema: fact table connected to normalized dimensions split into multiple related tables (dimension hierarchies represented as separate tables).

Query performance:- Star: faster for typical BI queries because fewer joins and simpler execution plans; good for aggregations and ad-hoc exploration.- Snowflake: more joins can slow queries, especially on large cardinality attributes; may benefit from reduced I/O if only a subset of attributes is read and storage engines exploit joins efficiently.

Storage:- Star: uses more storage due to denormalized dimension attributes (redundancy).- Snowflake: saves space by normalizing repeated attributes across lookup tables.

Maintainability:- Star: simpler to understand and use for analysts and BI tools; easier to add calculated fields and build dashboards.- Snowflake: more normalized—better for enforcing consistency and maintaining slowly changing dimensions across many attributes, but more complex ETL and documentation.

When to pick star (2 examples):1) Executive dashboards with many fast ad-hoc slices (time, product, region) where query speed and simplicity matter.2) Self-service BI environment where non-technical analysts build reports in Tableau/Power BI and benefit from flat dimensions.

When to pick snowflake (2 examples):1) Very wide dimensions with many hierarchical lookup tables (e.g., geo → country/state/city and taxonomy hierarchies) where storage savings and clear hierarchy modeling matter.2) Regulatory or master-data-driven contexts where normalization enforces consistency across many downstream datasets and ETL processes manage joins centrally.

Trade-offs summary:Choose star for analyst productivity and performance; choose snowflake for storage efficiency, normalized master data, and strict data governance. Hybrid approaches (denormalized reporting marts with normalized master storage) are common.

MediumTechnical

100 practiced

A product team requests a reliable, conformed product dimension combining three source systems (ERP, ecommerce, PIM) with different product codes and attribute sets. Describe your approach to design the conformed dimension: key mapping, attribute survivorship, attribute harmonization, lineage metadata, and how you'd validate the conformed dimension before release.

Sample Answer

Situation: Product stakeholders need a single, reliable product dimension that combines ERP, ecommerce, and PIM systems, each with different product codes and attributes.

Approach (overview): I’d design a conformed product dimension through a deterministic & probabilistic key mapping layer, clear attribute survivorship rules, harmonization standards, embedded lineage metadata, and a thorough validation regimen before release.

Key mapping:- Identify natural keys from each source (SKU, UPC/EAN, vendor SKU, internal product_id).- Build a master reference table linking source keys to a new conformed_product_id. Start with deterministic matches (UPC, manufacturer part number) then apply fuzzy matching (name similarity, normalized attributes) with match scores and manual review for uncertain pairs.- Record match type and confidence; keep source keys for traceability.

Attribute survivorship:- Define authoritative source by attribute type (e.g., PIM authoritative for descriptive attributes, ERP for cost and inventory, ecommerce for pricing/promo).- Implement rule precedence + recency: take authoritative source unless null, otherwise newest non-null value.- Store provenance for every attribute (source system, timestamp).

Attribute harmonization:- Standardize units, taxonomies, and naming (e.g., weight in kg, category mapping to master taxonomy). Use lookup tables for category mapping and transformations (e.g., color synonyms).- Normalize free-text (strip HTML, case fold, remove stop words) before matching.

Lineage metadata:- Capture and store: source_system, source_key, load_timestamp, transformation_version, match_confidence, and change history. Expose this via a lineage table and surface in BI tool tooltips for analysts.

Validation before release:- Automated tests: row counts, referential integrity, uniqueness of conformed_product_id, distribution checks (category, brand), null-rate thresholds for critical attributes.- Reconciliation: compare aggregates back to source systems (e.g., count of SKUs per brand) and sample-record audits (stratified by confidence band).- Business acceptance: produce sample extracts and dashboard previews for product managers to verify key products and attributes; resolve mismatches.- Regression checks: monitor downstream reports for metric deltas.

Outcome: This produces a trusted, auditable conformed product dimension suitable for reporting, with clear provenance and measurable confidence so BI dashboards remain accurate and explainable.

MediumBehavioral

96 practiced

Describe a time when you had to convince a stakeholder to accept a different grain or modeling approach for a report. Use the STAR format: Situation, Task, Action, Result. If you don't have a personal example, describe a hypothetical scenario and how you'd handle it.

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.