Data Architecture and Modeling
What this domain tests
Can you choose a model, declare a grain, and justify keys and history? Scenarios ask you to critique a schema, select a pattern, or explain why a report double-counts.
Core rules
- Architecture layers: business → data → application → technology. Data architecture defines data assets, structure, integration and lifecycle; it is not the same as database design.
- Model progression: conceptual (entities and relationships) → logical (attributes, keys, normalization, no DB specifics) → physical (types, indexes, partitioning, storage).
- Normalization removes redundancy and update anomalies: 1NF atomic values; 2NF no partial dependency on part of a composite key; 3NF no transitive dependency. Use 3NF for operational and ODS layers.
- Dimensional models serve analytics: fact tables hold measures at a declared grain; dimensions hold descriptive context. Star schemas keep dimensions denormalized; snowflaking normalizes them at the cost of more joins and poorer usability.
- Data Vault separates hubs (business keys), links (relationships) and satellites (descriptive history) for auditable, parallel-load integration.
- Slowly changing dimensions: Type 1 overwrites; Type 2 adds a row with effective dates and a current flag; Type 3 keeps a prior-value column. Type 2 is the default when history matters.
- Keys: natural/business keys identify; surrogate keys are warehouse-generated values that insulate the model from source changes.
Worked example
A retailer wants sales by product category and customer segment, and must restate history when a customer changes segment. Reasoning: declare the grain first - one row per order line. Measures: quantity, net sales and discount are additive; unit price is non-additive. Dimensions: Date, Product, Customer, Store, Promotion. Customer is Type 2, so a segment change creates a new version with effective dates and a current flag, and reports can show the segment at the time of sale. The order number becomes a degenerate dimension on the fact. Every dimension carries a surrogate key; the fact stores surrogates, never the source customer number. Everything downstream follows from the grain declaration.
Traps that cost marks
- Mixing grains in one fact table (order header and order line rows together).
- Snowflaking for purity and hurting BI usability.
- Type 2 dimensions without effective dates or a current flag, producing duplicate-looking rows.
- Using natural keys as warehouse primary keys, so source changes break history.
- Putting physical details such as indexes and data types into a conceptual model.