DAA-C01 Question 213
Single answerIdentify demographics and relationshipsA retail analytics team stores customer profiles in a CUSTOMER_DIM table and household relationship data in a HOUSEHOLD_MEMBER table. The business wants a reliable Power BI dashboard that shows customer demographics by age band, household composition, and relationship type (for example, spouse, child, parent) without duplicating customers across visuals. Data volumes are large, and analysts frequently reuse these groupings across many reports. Which approach should the team implement in Snowflake to best support this requirement?
- A
Create a semantic model in Snowflake that defines reusable dimensions for age band and relationship type, and model the household bridge so BI tools can query consistent demographic groupings.
- B
Store all household members as a comma-separated list in CUSTOMER_DIM and let Power BI split the values during report rendering to derive relationship counts.
- C
Create a dynamic table that flattens CUSTOMER_DIM and HOUSEHOLD_MEMBER into a single wide table with one row per customer-household combination, and use that table directly for all demographic visuals without additional modeling.
- D
Build demographic groupings only in each Power BI report as calculated fields so business users can customize age bands and relationship logic independently.
Show answer and explanation
Correct answer: A
Explanation
The key requirement is consistent, reusable demographic and relationship-based analysis at scale. In Snowflake, the strongest approach is to centralize shared business logic in a semantic model so dimensions such as age band and relationship type are defined once and reused broadly. For relationship-heavy analysis, especially household composition, proper modeling is important to avoid double counting caused by one-to-many joins. A semantic layer is more appropriate than embedding logic in every BI report or storing denormalized string lists. Dynamic tables can support data preparation pipelines, but they are not themselves the best answer to the governance and reuse problem. This reflects Snowflake best practices around governed analytics, reusable definitions, and modeling relationships for BI consumption.
- A. Correct.
Correct. For repeated analytics such as age bands, household composition, and relationship categories, defining these concepts in a governed semantic layer in Snowflake is the best fit. A semantic model centralizes metric and dimension logic so multiple BI tools and analysts use the same definitions. Modeling the household relationship as a bridge or relationship-aware structure helps avoid double counting when customers can be linked to households and roles. This aligns with best practices for reusable business logic and consistent reporting.
- B. Incorrect.
Incorrect. Storing multiple household members in a comma-separated field breaks relational modeling and makes demographic analysis harder, not easier. It prevents clean joins, complicates filtering, introduces parsing overhead, and increases the risk of inconsistent counts across tools. This is a common anti-pattern when dealing with one-to-many relationships.
- C. Incorrect.
Incorrect. A flattened table can be useful for some workloads, but using one row per customer-household combination as the primary source for all visuals increases the likelihood of duplicated customer counts unless every downstream query carefully de-duplicates. It also hardcodes one analytical shape and does not provide reusable governed business definitions as effectively as a semantic model. Dynamic tables help materialize transformations, but they do not replace dimensional or semantic modeling.
- D. Incorrect.
Incorrect. Defining age bands and relationship logic separately in each Power BI report leads to inconsistent business definitions, especially when many analysts reuse the same concepts. Although report-level calculations are easy to create, they are not ideal for governed enterprise analytics. This option addresses flexibility but sacrifices consistency and maintainability.