Data Architecture and Modeling
Data Architecture and Modeling tests whether you can choose a structure that matches how the data will be queried, then defend it. Questions compare normalised and dimensional designs, ask which key a fact table should carry, or present a slowly changing dimension scenario and ask what history survives.
Core rules and concepts
- Three model levels: conceptual (entities and relationships in business language), logical (attributes, keys, relationships, normalised, technology-independent), physical (tables, data types, partitions, indexes, distribution).
- Normalisation removes redundancy: 1NF atomic values, 2NF no partial dependency on part of a composite key, 3NF no transitive dependency on non-key attributes. Normalised models suit OLTP and write integrity.
- Dimensional models suit analytics: a fact table at a declared grain holding numeric measures and foreign keys, surrounded by conformed dimensions. Star keeps dimensions denormalised; snowflake splits them and adds joins.
- Declare the grain first. "One row per order line per day" prevents double counting later. Never mix grains in one fact table; use separate facts and conformed dimensions.
- Keys: natural/business keys identify records in the source; surrogate keys are warehouse-generated integers that isolate the warehouse from source changes and let one natural key carry several versions.
- SCD types: Type 1 overwrite (no history), Type 2 new row with new surrogate key plus effective/expiry dates and a current flag, Type 3 previous-value column (limited history).
- Layers commonly include a landing/ODS zone, an integrated warehouse layer, subject marts or aggregates, and a serving/semantic layer. Data Vault (hubs, links, satellites) fits cases where auditability and source agility dominate.
Worked example
A retailer needs revenue by the customer's region as it was when the order was placed. The customer dimension uses SCD2: customer 42 moves from North to South in March, so a new version row gets a new surrogate key with start date 2025-03-01 while the old row is closed. Each order fact stores the surrogate key valid at order time. Reporting joins on that key and reproduces history; a Type 1 overwrite would silently rewrite the past.
Traps
Storing natural keys in facts and joining on mutable attributes; snowflaking every dimension; adding a "total" column that breaks the grain; using Type 2 without a current-record flag so queries double count; modelling before agreeing on the questions.