Data Architecture and Modeling
Data Architecture and Modeling tests how you translate business questions into structures that store data at the right level of abstraction. Expect questions on the conceptual-logical-physical chain, normalisation versus dimensional design, grain, keys and slowly changing dimensions, and on the architecture artifacts that let systems interoperate.
Levels of abstraction
- Conceptual: business entities and relationships only - Customer, Order, Product. No keys, no data types.
- Logical: attributes, primary and foreign keys, cardinality, normalised to at least third normal form. Technology-independent.
- Physical: indexes, partitions, data types, storage options, denormalisation for performance. Enterprise architecture frameworks provide viewpoints such as planner, owner, designer and builder, and ask who consumes which artifact. A data model without an owning business question is a diagram, not an architecture.
Normalisation and dimensional design
First normal form removes repeating groups; second normal form removes partial dependencies on a composite key; third normal form removes transitive dependencies. Normalised models protect integrity in transactional systems. For analytics, dimensional models trade redundancy for query simplicity: a fact table at a declared grain, surrounded by conformed dimensions. Star schemas keep dimensions single-table; snowflakes normalise them and add joins.
Worked example
Business question: monthly revenue by product category and sales region. Grain must be declared first: one row per order line per month, not per order. Amount, quantity and discount are additive measures; margin is derived and should not be stored as a fact. Product and region become dimensions with surrogate keys so history is preserved. Because product category changes over time, the Product dimension is Type 2 with effective-from, effective-to and current-flag columns; the fact row keeps the surrogate key valid at the time of sale. If the team instead keys the fact to the natural product code, category reassignments silently rewrite history.
Traps that cost marks
- Jumping to physical design before the business question and grain are fixed.
- Confusing second normal form (partial dependency) with third (transitive dependency).
- Using a natural key as the history key - surrogate keys are what make Type 2 work.
- Snowflaking a star by habit, adding joins with no integrity benefit.
- Omitting the grain declaration: mixed-grain facts cannot be aggregated consistently.