Crack Your Next Interview with These Data Modelling Interview Questions!

SCDs & History Tracking

21. What are Slowly Changing Dimensions (SCDs)? SCDs are dimension table design patterns that handle changes to attribute values over time. When a customer moves from New York to Chicago, do you overwrite the old address, keep both, or store a “previous” column? The answer depends on whether historical analysis needs the old value. The biggest mistake is defaulting to Type 1 (overwrite) everywhere because it is simpler — then six months later the business asks “what region was this customer in when they placed that order?” and the data is gone.

22. Explain SCD Type 1, Type 2, and Type 3. Type 1 overwrites the old value with the new one — no history is kept. Type 2 creates a new row for each change, using effective_date, end_date, and an is_current flag to track versions. Type 3 adds a “previous” column (e.g., previous_city) to store exactly one prior value. Type 2 is the default for most warehouses because it preserves full history, but it increases table size significantly — a customer who changes address 5 times generates 5 rows. In an interview, always mention the trade-off: Type 2 is more powerful but requires surrogate keys and careful JOIN logic using date ranges.

23. How do you implement SCD Type 2 in SQL? The standard approach uses a MERGE statement that compares incoming records against the current dimension rows. When a change is detected, you expire the old row (set end_date and is_current = false) and insert a new row with the updated values. Here is a simplified example:

In an interview, mention that production implementations also handle brand-new customers (not just changes) and use a hash of tracked columns to detect changes efficiently.

24. When would you use SCD Type 3 over Type 2? Type 3 is appropriate when you only need to track the most recent change for a specific attribute, not the full history. A classic example is an organizational restructuring where sales territories are reassigned — you want both the “current_territory” and “previous_territory” for a transition period comparison, but you do not need every territory the rep has ever belonged to. The limitation is that Type 3 scales poorly: if you need to track changes to 5 attributes, you suddenly have 10 extra columns. In an interview, say “Type 3 is rare in practice — I default to Type 2 unless storage or complexity is a hard constraint.”

25. What is a mini-dimension, and when do you use one? A mini-dimension extracts rapidly changing attributes from a large dimension into a separate, smaller table. If your dim_customer has 10 million rows and the loyalty_tier and credit_score_band columns change weekly, SCD Type 2 on the full dimension would explode row counts. Instead, you create a dim_customer_profile mini-dimension with just those volatile attributes and its own surrogate key. The fact table then has foreign keys to both dim_customer and dim_customer_profile. In an interview, this question separates candidates who have dealt with real-scale SCD problems from those who only know textbook definitions.

26. What is a bridge table, and how does it relate to SCDs? A bridge table resolves many-to-many relationships by sitting between a fact table and a dimension. In the context of SCDs, bridge tables become necessary when a dimension relationship itself changes over time. For example, a patient-to-doctor assignment is many-to-many and changes quarterly. The bridge table tracks which doctors are assigned to which patients at which point in time, using effective and end dates. In an interview, mention that bridge tables need a weighting factor column to prevent double-counting in aggregations.

27. How do you handle late-arriving dimensions? Late-arriving dimensions occur when a fact record arrives before the corresponding dimension record — for example, a transaction is recorded before the customer master data is loaded. The standard approach is to insert a placeholder row in the dimension table with a special surrogate key and default values, then update it when the real data arrives. If you are using SCD Type 2, the late-arriving data may need to be inserted with a backdated effective_date, not the current date. In an interview, this question tests whether you have dealt with real-world pipeline timing issues.

28. What is a Type 6 (hybrid) SCD? Type 6 combines Types 1, 2, and 3 (1+2+3=6). You maintain full history with Type 2 rows (effective/end dates), add a “current” column that is overwritten (Type 1) on every row for the same entity, and optionally include a “previous” column (Type 3). This lets analysts query with WHERE is_current = TRUE for the latest view or join on date ranges for the historical view, while the overwritten “current” column on all rows enables easy side-by-side comparison. In an interview, explaining Type 6 unprompted shows you have worked with complex analytical requirements.

29. What is a snowflake schema, and how does it differ from a star schema? A snowflake schema normalizes dimension tables into sub-dimensions. Instead of a flat dim_product with category_name and department_name baked in, you split those into dim_category and dim_department tables linked by foreign keys. This reduces storage by eliminating redundancy in dimensions but adds JOINs at query time. In most modern columnar warehouses (BigQuery, Snowflake, Redshift), storage is cheap and JOINs are expensive, so star schemas are preferred. In an interview, say “I default to star schema unless I have a specific reason to snowflake, such as a very large dimension with highly redundant hierarchical data.”

30. What is Data Vault modeling? Data Vault is a modeling methodology designed for auditability and flexibility. It uses three core table types: Hubs (business keys), Links (relationships between hubs), and Satellites (descriptive attributes with full history). Data Vault separates structure from content, making it highly resilient to source system changes. The trade-off is complexity — a simple star schema with 5 tables might become 15+ tables in Data Vault. In an interview, position Data Vault as ideal for the raw/vault layer of a warehouse, with star schemas built on top as the presentation layer.

31. What is a Hub, Link, and Satellite in Data Vault? A Hub stores the unique business key and its load metadata (load date, record source). A Link captures the relationship between two or more Hubs — it is essentially a many-to-many association table. A Satellite stores the descriptive attributes and change history for a Hub or Link, using effective dates. The key insight is that Hubs and Links rarely change (business keys and relationships are stable), while Satellites absorb all the volatility. In an interview, draw a quick example: Hub_Customer, Hub_Product, Link_Order (connecting them), Sat_Customer_Details, Sat_Order_Details.

32. What is a One Big Table (OBT) approach, and when is it appropriate? The OBT approach pre-joins all dimensions into a single wide, fully denormalized table. It is popular in modern analytics stacks where BI tools (Looker, Metabase) work best with a single source table and columnar storage makes wide tables cheap. The trade-off is update complexity — changing a customer attribute requires updating every row that references that customer. OBT works well for read-heavy dashboards with infrequent dimension changes. In an interview, say “OBT is a serving-layer optimization, not a replacement for proper modeling in the transformation layer.”

33. What is a junk dimension? A junk dimension collects miscellaneous low-cardinality flags and indicators that do not belong in any existing dimension. Instead of cluttering the fact table with columns like is_gift_wrapped, is_expedited, payment_method (3 possible values), you combine them into a single junk dimension with every possible combination. A junk dimension with 4 binary flags has at most 16 rows. In an interview, the key point is that junk dimensions keep the fact table clean and narrow, which matters at billions of rows.

34. What is a role-playing dimension? A role-playing dimension is a single physical dimension table used multiple times in the same fact table with different meanings. The classic example is dim_date appearing three times in a shipment fact: order_date_key, ship_date_key, and delivery_date_key. Each foreign key “plays a different role” but points to the same date dimension. In an interview, mention that you typically create views (dim_order_date, dim_ship_date) to make the model self-documenting for BI users.

35. What is a factless fact table used for coverage analysis? A coverage factless fact captures what could have happened rather than what did happen. For example, a retail chain loads a table of every product-store-date combination where a promotion was active. By left-joining this against the actual sales fact, you can find which promotions generated zero sales in certain stores — information you cannot derive from transaction data alone. In an interview, this is a strong answer because it shows you think about “absence of data” problems, which are common in business analysis.

36. How does the Activity Schema pattern work? The activity schema is a modern pattern where all user actions are stored in a single, narrow table with columns like entity_id, activity_type, timestamp, and a JSON feature column. Instead of building separate fact tables for clicks, signups, and purchases, everything goes into one stream. You then self-join this table to build customer journeys or funnel analyses. The trade-off is query complexity — self-joins on large activity tables can be expensive. In an interview, position it as useful for event-driven analytics products, not as a replacement for traditional dimensional modeling in a warehouse.

37. What is the difference between wide tables and normalized tables in a modern analytics stack? Wide tables pre-join data for read performance; normalized tables minimize redundancy for write integrity. In modern columnar engines, wide tables scan only the columns you request, so having 200 columns does not penalize a query that reads 5. However, wide tables are harder to maintain and reason about. The practical default: normalize in your transformation layer (dbt staging/intermediate models), then materialize wide tables as your final mart. In an interview, this framing shows you think in layers rather than absolutes.

38. How do you model semi-structured data (JSON, arrays) in a warehouse? Semi-structured data like JSON payloads should be flattened into typed columns during the transformation layer. Most warehouses (BigQuery, Snowflake) can query JSON natively, but relying on JSON access in production dashboards is fragile — schema changes in the source silently break downstream queries. The best practice is to extract known fields into columns and keep the raw JSON as a fallback. In an interview, mention that arrays should be unnested into separate rows with a cross join, and that this is where grain decisions become critical.

Core Concepts & Fundamentals

1. What is data modeling, and why does it matter for data engineers? Data modeling is the process of defining how data is structured, stored, and related across your system. Without it, you end up with a warehouse full of inconsistent tables where “revenue” means three different things depending on which team built the pipeline. In an interview, emphasize that modeling is not a one-time exercise — it evolves as business requirements change and new sources are onboarded. The best models balance query performance with maintainability. Read more in our guide on what is data modeling.

2. What is an ER diagram, and when would you use one? An Entity-Relationship diagram maps entities (tables), their attributes (columns), and the relationships between them (one-to-many, many-to-many). The biggest mistake engineers make is skipping the ER diagram and jumping straight to DDL statements — then discovering six weeks later that a missing relationship forces a costly migration. Use ER diagrams during the design phase to get stakeholder alignment before writing any code. In an interview, mention that ER diagrams are most valuable for OLTP systems where referential integrity is critical. See our full walkthrough on ER diagrams.

3. Explain the difference between conceptual, logical, and physical data models. A conceptual model defines the high-level entities and relationships — think “Customer places Order.” A logical model adds attributes, data types, and keys without worrying about a specific database engine. A physical model is the actual implementation: column types, indexes, partitioning strategies, and storage format. The common interview trap is treating these as purely academic layers. In practice, jumping from conceptual straight to physical is how you end up with a schema that nobody besides the original author can understand.

4. What is normalization? Walk through 1NF, 2NF, and 3NF. Normalization is the process of organizing data to reduce redundancy and enforce integrity. 1NF requires atomic column values and no repeating groups. 2NF eliminates partial dependencies — every non-key column must depend on the entire primary key, not just part of it. 3NF removes transitive dependencies — if column C depends on column B, which depends on the primary key A, then C should live in a separate table. In an interview, always pair the definition with a real example: “In a sales table, storing customer_name alongside customer_id violates 3NF because name depends on customer_id, not on the sale.”

5. When should you denormalize, and what are the risks? Denormalization is deliberate redundancy added to reduce the number of JOINs at query time. You should denormalize when read performance is critical and write frequency is low — classic data warehouse territory. The risk is data inconsistency: if a customers name changes, you now need to update it in every denormalized table or accept stale data. A good rule of thumb is to normalize your source-of-truth layer (staging/raw) and denormalize your serving layer (mart/presentation). In an interview, naming this layered strategy shows you understand trade-offs, not just definitions.

6. What is a primary key, and how does it differ from a surrogate key? A primary key uniquely identifies each row in a table. A natural primary key uses business data (like email or order_number), while a surrogate key is a system-generated identifier with no business meaning, typically an auto-incrementing integer or UUID. Surrogate keys are preferred in warehouses because natural keys change — companies merge, email addresses update, product SKUs get reassigned. In an interview, point out that surrogate keys also improve JOIN performance since integer comparisons are faster than string comparisons. Learn more about keys in dimensional modeling.

7. What is a foreign key, and why do some warehouses skip enforcing them? A foreign key creates a reference from one table to the primary key of another, enforcing referential integrity. Many modern cloud warehouses (BigQuery, Redshift, Snowflake) allow you to declare foreign keys but do not enforce them at write time because enforcement adds overhead to every INSERT and UPDATE. The keys still serve as documentation and can help the query optimizer generate better plans. In an interview, saying “we declare them for documentation but enforce integrity upstream in the pipeline” is the practical answer interviewers want.

8. What is a composite key, and when is it necessary? A composite key uses two or more columns together to uniquely identify a row. It is necessary when no single column is unique on its own — for example, an order_line_items table might use (order_id, line_number) as its composite key. The common pitfall is creating composite keys with too many columns (4+), which makes JOINs verbose and error-prone. In an interview, mention that most dimensional models avoid composite keys in dimension tables by using surrogate keys, but they appear naturally in fact tables as combinations of foreign keys.

9. What is the difference between a candidate key and an alternate key? A candidate key is any column (or set of columns) that could serve as the primary key — it is unique and not null. Once you pick one candidate key as the primary key, the remaining candidate keys become alternate keys. For example, in an employees table, both employee_id and social_security_number are candidate keys, but you would pick employee_id as primary and SSN becomes an alternate key. In an interview, this question tests whether you understand that primary key selection is a design choice, not a given.

10. What does “cardinality” mean in data modeling? Cardinality describes the numerical relationship between two entities: one-to-one, one-to-many, or many-to-many. Getting cardinality wrong is one of the most expensive modeling mistakes because it directly impacts your grain. If you model a one-to-many relationship as one-to-one, you will silently drop rows. If you model a one-to-one as many-to-many, you will introduce duplicates that inflate every aggregate. In an interview, always ask clarifying questions about cardinality before proposing a schema — it shows you think before you code.

The Easiest Way to Ace the Data Modeling Interview: A 3-Step Guide

0