Data Architecture and Modeling
Data Architecture and Modeling tests whether you can connect enterprise architecture to concrete models: choosing abstraction levels, declaring grain, normalizing for integrity, and dimensionalizing for analytics. Questions often present a model defect and ask for the smallest correct fix.
Core rules and concepts
- Architecture holds the decisions that are expensive to reverse: platforms, integration patterns, standards, and the sequenced roadmap. It spans enterprise, solution, and data levels.
- Three model levels: conceptual (business entities and relationships, no attributes or keys), logical (attributes, keys, cardinality, normalized, technology-independent), physical (tables, data types, indexes, partitions, vendor specifics).
- Normalization: 1NF removes repeating groups and enforces atomic values; 2NF removes partial dependencies on part of a composite key; 3NF removes transitive dependencies on non-key attributes. 3NF protects OLTP integrity; dimensional models serve query performance.
- Dimensional design: a fact table at a declared grain, conformed dimensions shared across facts, star versus snowflake, surrogate keys that insulate the warehouse from source key changes.
- Slowly changing dimensions: Type 1 overwrites (corrections), Type 2 adds a version row with effective dates and a current flag (history), Type 3 keeps a prior-value column (limited history).
Worked example
An orders fact table stores both the order header amount and the order line amount. Revenue doubles when a report sums the amount column. Reasoning: the grain is ambiguous. State the grain as one row per order line; keep the line extended amount as the additive measure; move header-level amounts such as tax or freight to a separate fact at order grain, or derive them from lines. Conformed Date, Customer, and Product dimensions let the two facts be queried together. The fix is a grain declaration plus measure placement, not a DISTINCT in the report.
Traps that cost marks
- Jumping to physical tables before the conceptual and logical levels are agreed.
- Using Type 2 history to fix a data-entry error that Type 1 should overwrite.
- Snowflaking by habit, adding joins without a reason.
- Mixing grains in one fact table, or storing derived totals that then drift.
- Omitting cardinality and optionality, which later break referential integrity.