DAA-C01 Question 169
Single answerUse different modeling techniques for the consumption layer (e.g., dimensional, Data Vault)A retail company has built a Snowflake-based analytics platform that ingests point-of-sale, e-commerce, and loyalty-program data from multiple source systems. The raw and integrated layers are modeled using Data Vault 2.0 because source structures change frequently and the company must preserve historical lineage. Business analysts now need a consumption layer for Power BI dashboards that answer questions such as daily sales by product category, customer purchase trends, and promotion effectiveness. Query performance and ease of use for analysts are higher priorities than preserving the full source-system structure in this layer. Which modeling approach should the data analyst recommend for the consumption layer?
- A
Build dimensional star schemas with conformed dimensions and fact tables sourced from the Data Vault business vault or information marts
- B
Expose hubs, links, and satellites from the Data Vault directly to Power BI so analysts can retain full lineage and historical granularity
- C
Flatten all source tables into one wide denormalized table per source system and let BI users define relationships in Power BI
- D
Replace the Data Vault model with third normal form (3NF) operational tables because BI tools work best on normalized schemas
Show answer and explanation
Correct answer: A
Explanation
The best recommendation is to keep Data Vault in the integration layer and create a dimensional consumption layer for analytics. This matches a common modern warehouse pattern: use Data Vault for scalable ingestion, change resilience, and historical traceability, then publish dimensional marts for reporting and dashboarding. In Snowflake, this approach also aligns well with separating storage and compute, allowing marts to be optimized for BI workloads without changing the integrated history-preserving model. Best practices for analytics consumption generally favor star schemas because they reduce join complexity, support intuitive business semantics, and work well with BI tools such as Power BI. Kimball-style dimensional modeling remains the standard for consumption layers when the main goals are query performance and analyst usability, while Data Vault is better suited to raw and integrated layers where auditability, source alignment, and agility are key.
- A. Correct.
Correct. For a consumption layer intended for business reporting and self-service analytics, a dimensional model is typically the best fit. Star schemas organize data into fact tables and dimensions that align with common analytic patterns such as sales by product, customer, date, or promotion. This improves usability for analysts and usually reduces join complexity for BI tools. In many real-world Snowflake architectures, Data Vault is used in raw/integrated layers for auditability and agility, while dimensional marts are built downstream for reporting consumption.
- B. Incorrect.
Incorrect. Data Vault structures are excellent for integration, historization, and lineage, but they are usually not the most user-friendly choice for BI consumption. Exposing hubs, links, and satellites directly to analysts increases join complexity and makes it harder to answer standard business questions efficiently. A candidate might choose this option because lineage is important, but the scenario explicitly prioritizes query performance and ease of use in the consumption layer.
- C. Incorrect.
Incorrect. A single wide table per source system may seem to simplify reporting, but it does not create a coherent enterprise consumption model across multiple domains and source systems. It also often duplicates attributes, weakens governance, and makes conformed analysis harder, especially for measures like promotion effectiveness or customer trends that span systems. This reflects a common misconception that denormalization alone is equivalent to a well-designed analytic model.
- D. Incorrect.
Incorrect. Third normal form is generally more appropriate for operational or highly normalized integration use cases than for business-friendly reporting. Replacing Data Vault with 3NF would also undermine the stated requirement to preserve historical lineage in the integrated layers. BI tools typically perform better and are easier for users to navigate with dimensional models than with highly normalized operational schemas.