DAA-C01 Question 210
Single answerFind reasons/causes of anomalies or patterns in historical dataA retail analytics team stores three years of daily order history in Snowflake. During quarterly review, analysts notice an unusual spike in revenue for one product category during two specific weeks last year. They need to determine what likely caused the anomaly and explain it to business stakeholders. The data model includes a fact table with order metrics and dimension tables for product, region, channel, and promotion campaign. Which approach would BEST help the team identify the drivers of the anomaly using Snowflake SQL analytics?
- A
Compare the anomalous weeks to comparable prior and subsequent periods by segmenting revenue with window functions and GROUP BY across category, region, channel, and campaign dimensions to isolate which dimension combinations changed most.
- B
Create a larger virtual warehouse and rerun the same total-revenue query so the result is returned faster, then use the faster runtime as evidence that the spike is valid.
- C
Use only a single overall AVG(revenue) across the entire three-year period and compare the anomalous weeks to that baseline without breaking down any dimensions, because detailed segmentation can bias the analysis.
- D
Replace the historical fact table with a dynamic table that stores only the anomalous weeks, then use the reduced dataset to infer the root cause from the remaining records.
Show answer and explanation
Correct answer: A
Explanation
The best answer is Option 1 because finding reasons for anomalies in historical data requires comparative and dimensional analysis, not just detecting that an anomaly exists. In Snowflake, this is typically done with SQL patterns such as aggregations by business dimensions, window functions for period-over-period comparisons, ranking, moving averages, and contribution analysis across slices like campaign, region, and channel. Analysts should compare the anomalous period to adjacent periods or equivalent periods from prior years to account for seasonality and recurring patterns. Snowflake documentation on window functions and analytic SQL supports these techniques for trend and comparative analysis. Options 2 and 4 focus on platform objects or performance features that do not explain causation, while Option 3 ignores the dimensional context required to isolate the real driver.
- A. Correct.
Correct. To explain an anomaly in historical data, the most effective approach is to compare the anomalous period against relevant baselines and then decompose the result by meaningful business dimensions. In Snowflake, analysts commonly use GROUP BY with dimensions such as region, channel, and campaign, along with window functions for period-over-period comparisons, ranking, moving averages, or contribution analysis. This helps identify whether the spike was concentrated in a specific promotion, geography, or sales channel rather than treating revenue as a single aggregate. This is aligned with best practices for root-cause analysis in analytical workloads.
- B. Incorrect.
Incorrect. Increasing warehouse size may improve performance, but it does not help explain why the anomaly occurred. Compute scaling addresses query runtime, not analytical reasoning. A faster execution of the same high-level aggregation still will not reveal whether the spike was driven by a campaign, region, pricing issue, or channel mix change. This option reflects the misconception that performance tuning is equivalent to analytical investigation.
- C. Incorrect.
Incorrect. A single global average over three years is usually too coarse for anomaly diagnosis. It can hide seasonality, promotions, product lifecycle effects, and channel or regional differences. Without dimensional breakdowns, the analyst cannot determine what caused the spike. This option represents a common mistake of using an oversimplified baseline that is statistically weak for root-cause analysis.
- D. Incorrect.
Incorrect. Dynamic tables are intended to maintain the results of a query pipeline automatically; they are not a method for preserving only anomalous rows to determine causation. Replacing the historical fact table with a reduced dataset would remove critical comparison context needed to evaluate what changed. Root-cause analysis depends on retaining both anomalous and normal periods for comparison.