DAA-C01 Question 163
Single answer2.4 Use data modeling to manipulate the data to meet BI requirements.A retail company stores point-of-sale transactions in a Snowflake table that contains one row per line item, including TRANSACTION_ID, STORE_ID, PRODUCT_ID, CUSTOMER_ID, TRANSACTION_TS, QUANTITY, UNIT_PRICE, and DISCOUNT_AMOUNT. The BI team uses a dashboarding tool that performs best when querying a simple star schema and needs to analyze sales by day, store, product category, and customer segment. They also need a metric for net sales and want to avoid repeated complex joins and calculations in BI queries. Which approach BEST meets these requirements?
- A
Create a sales fact table at the line-item grain with surrogate keys to conformed date, store, product, and customer dimensions, and store a derived NET_SALES measure in the fact table or a semantic layer view.
- B
Create one wide dimension table by denormalizing transactions, stores, products, customers, and dates into a single table so the BI tool does not need joins.
- C
Keep the raw transactional table unchanged and require the BI tool to calculate net sales and join to lookup tables at query time for maximum flexibility.
- D
Model the data as a snowflake schema by splitting product and customer attributes into as many normalized sub-dimensions as possible to reduce storage and improve BI query simplicity.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to build a dimensional model centered on a sales fact table at the correct business grain and surrounding conformed dimensions. This is a core BI modeling best practice because it supports intuitive aggregation, reusable metrics, and simpler SQL generated by BI tools. In Snowflake, dimensional modeling remains relevant even though storage is inexpensive and raw data can be retained separately; the presentation layer should still be modeled to meet reporting requirements. A star schema is typically preferred for dashboarding because it minimizes join complexity compared with normalized models. For this scenario, the line-item grain is important because the source data is one row per transaction line and the reporting dimensions are day, store, product category, and customer segment. Net sales should be standardized in the modeled layer, whether persisted in the fact table when appropriate or exposed consistently through a semantic view, to prevent inconsistent definitions across reports. This aligns with common dimensional modeling guidance from Kimball-style BI best practices and Snowflake documentation patterns for building analytic presentation layers with facts, dimensions, and derived business metrics.
- A. Correct.
Correct. A star schema is the standard dimensional model for BI workloads because it simplifies queries and aligns with how dashboard tools typically aggregate measures by descriptive attributes. Using a fact table at the line-item grain preserves analytic flexibility, while date, store, product, and customer dimensions support slicing by the requested business perspectives. A derived NET_SALES measure, such as QUANTITY * UNIT_PRICE - DISCOUNT_AMOUNT where appropriate for the business definition, reduces repeated logic in downstream BI queries. This approach best satisfies the requirement for simple, performant, reusable analytics.
- B. Incorrect.
Incorrect. A single denormalized wide table may seem attractive because it avoids joins, but it usually introduces significant duplication of dimensional attributes across transactional rows, makes governance and conformance harder, and is not the preferred dimensional modeling approach for scalable BI. It also does not cleanly separate facts from dimensions, which makes maintaining shared business dimensions such as customer segment or product category more difficult over time.
- C. Incorrect.
Incorrect. Leaving all logic to the BI tool conflicts directly with the stated requirement to avoid repeated joins and calculations. Although raw transactional storage is important for detailed data retention, it is not the best presentation model for BI consumption. Recomputing metrics such as net sales in every dashboard or semantic model increases inconsistency risk and burdens BI users with data modeling complexity.
- D. Incorrect.
Incorrect. A highly normalized snowflake schema can reduce redundancy in some dimension structures, but it generally increases the number of joins and makes BI queries more complex. The scenario specifically states that the BI tool performs best with a simple star schema. For analytic reporting, star schemas are generally preferred over over-normalized snowflake designs unless there is a very specific reason to normalize dimensions further.