Data Architecture and Modeling
Data architecture translates business strategy into integrated data structures, flows, and standards. Modeling turns those structures into conceptual, logical, and physical designs. The exam tests model fidelity, key design, normalization versus dimensional trade-offs, and architecture-to-implementation traceability.
Core rules
- Three model levels: conceptual (business entities and relationships), logical (attributes, keys, normalization, business rules), physical (tables, indexes, partitions, platform constraints). Do not skip conceptual; it prevents premature table design.
- Resolve many-to-many relationships with associative entities; do not hide them in multi-valued columns.
- Choose keys deliberately: natural keys for business identity, surrogate keys for stable joins and history; enforce uniqueness and referential integrity.
- Normalize for operational integrity (3NF) and denormalize for analytical access (star schemas), documenting the reason.
- Architecture layers - source, integration, persistence, access, consumption - need defined ownership, interfaces, and lineage.
Worked example
An order-to-cash model starts with Customer, Order, Order Line, Product, Invoice. Order Line resolves the many-to-many between Order and Product and carries quantity and price. The logical model normalizes Customer Address into a separate entity with effective dates; the physical model adds a surrogate order-line key and a partitioned fact table. The reasoning: each design choice traces to a requirement - audit history, join stability, or query performance - rather than fashion.
Traps
- Jumping to physical tables before agreeing business entities.
- Using surrogate keys without unique natural-key constraints, allowing duplicates.
- Denormalizing without documenting the performance or usability reason.
- Treating a data model as a one-time deliverable rather than a controlled artifact.