Data Modeling and Architecture
What to know
Data modeling covers conceptual (business concepts), logical (entities, attributes, relationships, normalized), and physical (tables, indexes, storage) levels. Know normalization (1NF–3NF: atomic values, no partial dependency, no transitive dependency) and when denormalization is appropriate (performance, reporting). Understand entity-relationship constructs: cardinality, optionality, primary/foreign keys, and the difference between a natural and surrogate key.
Architecture covers how data is organized across the enterprise: the data warehouse (schema-on-write, structured, governed), data lake (schema-on-read, raw, flexible), data lakehouse (hybrid), and data mart (subject-specific subset). Know architectural patterns: layered (staging → integration → presentation), dimensional (star vs. snowflake schema, fact vs. dimension tables), and the concept of a canonical/virtual model. Understand the role of reference architecture, data flow diagrams, and the trade-off between centralization and federation.
Traps
- Confusing conceptual (business terms) with logical (structure, still technology-agnostic).
- Assuming a star schema is always better - snowflake reduces redundancy at the cost of joins.
- Mixing schema-on-read (lake) with schema-on-write (warehouse).
- Forgetting that surrogate keys decouple from business keys and support history.
Worked rule of thumb
For normalization questions, apply the dependency test: does a non-key attribute depend on the whole key (2NF) and only the key (3NF)? If it depends on part of the key or another non-key attribute, normalize further. For star vs. snowflake, ask: Is query simplicity or storage efficiency the priority? Star favors simplicity.
Dimensional modeling: facts are measurable events; dimensions are descriptive context.