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.

HardTechnical

27 practiced

You operate in an ELT-first stack where the analytic DB has limited upsert performance. Describe a pattern to implement SCD Type 2 using ELT primitives (staging tables, MERGE tasks, streams, or staging+swap) and explain how you'd minimize downtime and expensive full-table operations.

Sample Answer

Solution overview: implement SCD Type 2 with incremental, append-friendly operations using a staging table + change stream + targeted MERGE (or staging+swap) to avoid full-table updates.

Pattern (steps):1. Ingest raw changes to a staging table (stg_src) each batch with source keys and all attributes plus metadata (batch_id, load_ts).2. Use a change data capture stream (Snowflake STREAM or equivalent) on stg_src to capture only new/updated rows.3. Left-join stream to target dimension (dim_table) to detect: - new keys => insert new active record - existing key but changed attributes => expire current record (set end_date, active_flag=0) and insert new active record (start_date, end_date=NULL, active_flag=1)4. Implement MERGE at partitioned / micro-batch level: MERGE only on rows identified by the stream (not full table). Example:

sql

MERGE INTO dim_table t
USING changes c
  ON t.business_key = c.business_key AND t.active_flag = 1
WHEN MATCHED AND (
     (t.attr1 <> c.attr1) OR (t.attr2 <> c.attr2)
) THEN
  UPDATE SET t.end_date = c.load_ts, t.active_flag = 0
WHEN NOT MATCHED THEN
  INSERT (business_key, attr1, attr2, start_date, end_date, active_flag)
  VALUES (c.business_key, c.attr1, c.attr2, c.load_ts, NULL, 1);

Then insert new active records from changes where no active exists or after expiry.

Optimizations to minimize downtime and cost:- Micro-batches: run stream-driven jobs frequently (minutes/hours) to keep change set small.- Predicate pushdown: filter changes to only keys that differ using hash checksums (compare hash in source vs active).- Partition / clustering: cluster dim_table on business_key or date ranges to make MERGE localized.- Staging+swap for very large dims: build a new partition (or copy affected partitions) and atomically swap metadata or pointer (in Snowflake, use zero-copy clone + rename if needed).- Avoid full-table scans: use stream offsets / watermarking to process only recent changes.

Result: SCD Type 2 preserved, minimal locking and compute by restricting MERGE scope to changed keys and using append-friendly inserts for new versions.

MediumTechnical

20 practiced

Explain what a conformed dimension is and why it's important when multiple fact tables (sales, returns, shipments) are used. How would you enforce conformance and handle schema changes across teams?

EasyTechnical

20 practiced

Define the 'grain' of a fact table and explain why choosing the correct grain is critical. Provide two examples of fact table grains (one transaction-level, one snapshot-level) and describe how queries and aggregation strategies differ between them.

HardTechnical

24 practiced

Multiple business teams disagree on the definition of 'active user'. As a data analyst responsible for dimensional models and metrics, design a process and schema approach to resolve conflicting definitions, implement versioned metrics, and provide lineage so teams can choose the appropriate metric for their dashboards.

Sample Answer

Situation: Multiple business teams (marketing, product, finance) disagreed on "active user" definitions, causing conflicting dashboards and decisions.

Task: As the data analyst owning dimensional models and metrics, I needed to create a repeatable process and schema so definitions are authoritative, versioned, and traceable — letting teams pick the appropriate metric while ensuring consistency and auditability.

Action:1. Convene stakeholders & clarify requirements- Ran workshop to capture each team's use-case, business rules, time-window (e.g., 7-day rolling, monthly unique), and downstream decisions tied to the metric.- Documented acceptance criteria and priority use-cases.

2. Governance process- Established a Metrics Working Group (rep from each team + data engineering + analytics) and SLAs for proposal, review, and approval.- Introduced a change request template: motivation, proposed SQL, comparison to current, and impact analysis.

3. Schema & implementation (versioned metrics)- Built canonical dimensional model for users and events: dim_user, dim_date, fact_event with standardized columns (user_id, event_type, event_time).- Created a metrics metadata layer: - metric_definitions (metric_id, name, description, primary_use_case) - metric_versions (metric_id, version, sql_definition, time_window, effective_from, effective_to, owner, created_at) - metric_snapshot (materialized results for heavy metrics keyed by version+date)- Example SQL stored as parametrized query in metric_versions; CI pipeline validates and runs backfill when a new version is approved.

4. Lineage & discoverability- Integrated with data catalog (e.g., Amundsen/Atlas) registering: - upstream tables (fact_event → metric) - fields used in SQL (user_id, event_time) - version links and impact graphs- Exposed metrics via a Metrics API/Catalog UI where users can: - See available versions, semantic differences, example values, and lineage graph - Choose a version for dashboarding; dashboards store metric_id+version to guarantee reproducibility

5. Validation & rollout- Ran parallel runs comparing old vs new versions, surfaced deltas to owners, and required sign-off.- Automated tests: row counts, cardinality, and guardrails (e.g., negative rates).

Result:- Reduced cross-team confusion; dashboards referenced explicit metric_id+version, eliminating silent drift.- Faster audits: trace from dashboard value → metric_version → SQL → source fields.- Stakeholders reported higher trust; time-to-resolution for metric disputes dropped from weeks to 2 days.

Learnings:- The technical solution must be paired with governance and clear owner accountability.- Versioning metrics and storing lineage prevents silent changes and supports reproducible analysis.

MediumTechnical

27 practiced

A product category hierarchy changes (a product moves from category A to category B). As an analyst you must produce reports showing sales by category as of the sale date. How would you model the category dimension and write queries to ensure historical correctness?

Sample Answer

Approach (summary): model the category dimension as a Type 2 Slowly Changing Dimension (SCD2) so every time a product’s category changes you insert a new category-row version with effective and end timestamps. In the sales fact store the fact should reference the product (or product SKU) and the sale_date; you can either store the category surrogate at ETL time (denormalized) or resolve category via date-range join at query time. This guarantees historical correctness.

Dimension model (fields):- category_sk (surrogate key)- category_id (business key)- name, parent_category_sk (for hierarchy), ... (attributes)- effective_from (timestamp/date)- effective_to (timestamp/date) — use a high value like '9999-12-31' for current- is_current (boolean)- version

Example queries:

1) Resolve category by date (recommended if fact stores product_sk and sale_date only):

sql

SELECT c.category_sk, c.name, SUM(s.amount) total_sales
FROM sales s
JOIN product p ON s.product_id = p.product_id
JOIN category_dim c
  ON p.category_id = c.category_id
  AND s.sale_date >= c.effective_from
  AND s.sale_date < c.effective_to
GROUP BY c.category_sk, c.name;

2) If ETL populated sales.category_sk at load time (fast reads), simple aggregation:

sql

SELECT c.name, SUM(s.amount) total_sales
FROM sales s
JOIN category_dim c ON s.category_sk = c.category_sk
GROUP BY c.name;

Hierarchy roll-up: use parent_category_sk and recursive CTE to aggregate up the tree for a given sale_date:

sql

WITH category_at_date AS (
  SELECT * FROM category_dim WHERE effective_from <= '2025-10-01' AND effective_to > '2025-10-01'
),
rec AS (
  SELECT category_sk, name, parent_category_sk FROM category_at_date
  UNION ALL
  SELECT p.category_sk, p.name, p.parent_category_sk
  FROM category_at_date p
  JOIN rec r ON r.parent_category_sk = p.category_sk
)
SELECT r.name, SUM(s.amount) total_sales
FROM sales s
JOIN category_at_date c ON s.category_id = c.category_id
JOIN rec r ON c.category_sk = r.category_sk
WHERE s.sale_date = '2025-10-01'
GROUP BY r.name;

Key reasoning and best practices:- SCD2 preserves full history and supports date-accurate joins.- Use surrogate keys for joins and to avoid business-key volatility.- If queries are frequent and performance-sensitive, populate category_sk into sales at ETL (denormalize) — ensures immutable historical snapshots and faster queries.- Maintain strict ETL rules: when a product moves categories, insert new category_dim row with new effective_from and update prior row’s effective_to.- Test edge cases: backdated changes, daylight/timezone, overlapping ranges, missing effective dates.

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.