DAA-C01 Question 207
Single answer3.3 Perform diagnostic analyses.A retail analytics team uses Snowflake to investigate why online conversion rates dropped sharply over the last two weeks. They have a fact table of website sessions, a dimension table of marketing campaigns, and a table of checkout events. The analyst wants to perform a diagnostic analysis to determine which factors are most strongly associated with the drop and to compare current behavior against a stable historical baseline. Which approach would BEST support this diagnostic analysis in Snowflake?
- A
Build a dataset that joins sessions, campaigns, and checkout events; segment metrics by channel, device, geography, and campaign; then compare the affected two-week period to a prior baseline period using window functions and conditional aggregations.
- B
Train a forecasting model on historical conversion data and use its future predictions to explain why conversion rates declined in the most recent two weeks.
- C
Export the raw tables to a spreadsheet and manually inspect a sample of rows to identify the most likely reason for the decline.
- D
Create a dashboard that shows only the overall conversion rate for the last two weeks, without dimensional breakdowns, because diagnostic analysis should avoid slicing data too finely.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to create an integrated analytical dataset and compare the affected period with a historical baseline across relevant dimensions. In Snowflake, diagnostic analysis commonly relies on SQL-based investigation techniques such as joins, aggregations, CASE logic, window functions, and time-based comparisons to identify where performance changed and which segments are associated with the issue. This aligns with standard analytics practice: descriptive analysis tells you what happened, diagnostic analysis helps determine why it happened, predictive analysis estimates what may happen next, and prescriptive analysis suggests actions. Snowflake supports this workflow efficiently through scalable SQL processing over large fact and dimension tables. Best practice is to compare the current period against a baseline and drill into dimensions likely to explain variance rather than relying only on high-level KPIs or predictive methods.
- A. Correct.
Correct. Diagnostic analysis focuses on understanding why something happened by identifying relationships, patterns, and contributing factors. Joining the relevant session, campaign, and checkout data enables the analyst to examine where users are dropping off. Segmenting by dimensions such as channel, device, geography, and campaign is a standard diagnostic technique for isolating drivers. Comparing the problematic period to a stable historical baseline with SQL constructs like conditional aggregation, CASE expressions, and window functions is practical and aligned with how analysts investigate root causes in Snowflake.
- B. Incorrect.
Incorrect. Forecasting is primarily predictive analysis, not diagnostic analysis. A forecasting model can estimate expected future conversion rates, but it does not directly identify which dimensions or behavioral changes contributed to a recent decline. Someone might choose this because predictive tools feel advanced, but they answer a different business question.
- C. Incorrect.
Incorrect. Manual spreadsheet inspection does not scale well for realistic diagnostic analysis in Snowflake, especially across multiple large tables and dimensions. It also increases the risk of sampling bias and missed patterns. A candidate might pick this if they are thinking about ad hoc troubleshooting, but the scenario calls for structured, repeatable analysis over integrated data.
- D. Incorrect.
Incorrect. Looking only at the overall conversion rate is insufficient for diagnostic analysis because it hides potential variation across important dimensions. The purpose of diagnosis is to find contributing factors, and that requires breakdowns and comparisons. Someone might choose this if they confuse monitoring with diagnosis.