An enterprise data team needs a schema that tracks the full change history of business entities over time, handles data from multiple source systems with different keys for the same real-world entity, and supports auditable lineage. Which schema best meets these requirements?
Answer & explanation
Correct answer: D
WHY D: The data vault schema is purpose-built for enterprise environments with multiple source systems. Hubs hold business keys (one per real-world entity regardless of source), Links model relationships between hubs, and Satellites store the descriptive attributes plus full change history with load timestamps. This directly satisfies auditable lineage, multi-source key integration, and historical tracking.
WHY NOT A: A staging-plus-mart architecture describes an ETL pipeline pattern, not a schema design that natively addresses multi-source key integration or change history tracking at the entity level.
WHY NOT B: SCD Type 2 on a star schema does preserve history, but it does not address multi-source key integration across systems or provide the structured auditability and decoupled loading that data vault offers.
WHY NOT C: A flat wide denormalized table collapses the attributes into a single record and does not preserve historical changes or support multi-source business key reconciliation.
WHY NOT E: A third-normal-form snowflake schema with in-place updates destroys history. It also requires complex JOINs and lacks the built-in multi-source business key isolation that data vault hubs provide.