Data Modeling
20 questionsNormalization, dimensional modeling, Data Vault, schema design patterns. The modeling questions that separate junior from senior data engineers.
Normal Forms: 1NF Through BCNF
Walk me through the normal forms from 1NF to BCNF. Give a concrete example of a table that violates 2NF and show how you'd fix it.
Denormalization Tradeoffs
When and why would you denormalize a data model? What are the risks?
Star Schema Design
Design a star schema for an e-commerce company that wants to analyze sales by product, customer, store, and time.
Snowflake Schema vs Star Schema
What is a snowflake schema and when would you choose it over a star schema?
Fact Table Types
Explain the three types of fact tables — transaction, periodic snapshot, and accumulating snapshot. When do you use each?
Slowly Changing Dimensions (SCD Types 1–3)
Explain SCD Types 1, 2, and 3. When would you choose each? Walk me through implementing SCD Type 2.
Surrogate Keys vs Natural Keys
Why do dimensional models use surrogate keys instead of natural (business) keys? Are there cases where natural keys are better?
Degenerate and Junk Dimensions
What are degenerate dimensions and junk dimensions? Give examples of each.
Bridge Tables for Multi-Valued Dimensions
A patient can have multiple diagnoses per hospital visit. How do you model this in a star schema?
Data Vault 2.0: Hubs, Links, and Satellites
Explain the core components of Data Vault 2.0. When would you choose it over a Kimball star schema?
Anchor Modeling
What is anchor modeling and how does it differ from Data Vault? When would you consider it?
One Big Table (OBT) Pattern
Some modern data teams advocate for the 'One Big Table' (OBT) approach. What is it, and when does it make sense?
Activity Schema Pattern
What is the Activity Schema pattern and how does it compare to traditional dimensional modeling?
Wide Tables vs Normalized for Analytics
Your team debates wide flat tables vs normalized tables for the analytics layer. What's your recommendation and why?
Modeling for Spark and Data Lakes
How does data modeling differ when your target is a Spark-based data lake (Delta/Iceberg) vs a traditional RDBMS warehouse?
Modeling Clickstream Data
Design a data model for clickstream/event data from a web application. Consider both raw storage and analytical queries.
Modeling Financial Transactions
Design a data model for financial transactions that supports regulatory reporting, fraud detection, and customer analytics. What special considerations apply?
SaaS Multi-Tenant Data Modeling
How would you model data for a multi-tenant SaaS analytics platform? Consider isolation, scalability, and query patterns.
Schema Evolution Strategies
Your data model needs to evolve — new columns, changed types, removed fields. How do you manage schema evolution without breaking downstream consumers?
SCD Types 4, 5, and 6
Beyond SCD Types 1–3, explain Types 4, 5, and 6. When would you use them in practice?