Data Architecture and Modeling: Structures, Normalization, and Design Trade-offs
What to master
Architecture questions test whether you can reason from requirements to structure. Know the layers (source, integration, storage, presentation) and patterns: normalized (3NF) for operational integrity, dimensional (star/snowflake) for analytics, Data Vault for auditable integration, and lakehouse hybrids. Understand grain as the foundational modeling decision-every fact table must declare its grain before measures. Distinguish fact (events/measures) from dimension (context), and slowly changing dimensions types (Type 1 overwrite, Type 2 versioned with effective dates, Type 3 prior-value column). Study normalization forms (1NF atomicity, 2NF no partial dependency, 3NF no transitive dependency) and when denormalization is justified for read performance.
Traps in questions
- Choosing a model before stating the grain; grain errors cascade into every downstream metric.
- Assuming 3NF is "better"-it optimizes writes/integrity, not analytical scans.
- Confusing SCD Type 2 (history rows) with Type 3 (limited prior-value columns).
- Overlooking conformed dimensions when integrating multiple marts.
Worked example
A sales fact table at grain "one row per order line" cannot also store customer lifetime value without violating grain. Solution: keep the fact at line grain and place lifetime value in a customer dimension snapshot, or build a separate aggregate fact at customer grain.
Rule of thumb: declare the grain in one sentence; if a measure does not fit that sentence, it belongs in a different table.