DAA-C01 Question 209
Select 2Find reasons/causes of anomalies or patterns in historical dataA retail analytics team stores three years of daily order history in Snowflake. A dashboard shows that online revenue dropped sharply for one product category during two specific weeks last quarter, while total site traffic stayed normal. The business wants to identify likely drivers in the historical data before escalating to engineering. The analyst has access to fact tables for orders, returns, inventory snapshots, promotions, and web sessions, plus dimensions for product, channel, and geography. Which TWO actions are the most appropriate to help find the likely causes of the anomaly using Snowflake SQL analytics?
- A
Compare the affected weeks to prior comparable periods by joining orders to inventory snapshots and promotions, then use window functions to quantify week-over-week and year-over-year changes by category, SKU, and region.
- B
Run a clustering key on the revenue column of the orders table so Snowflake can automatically explain why the revenue anomaly occurred.
- C
Use time-series decomposition with SQL aggregates to separate trend and seasonal baselines, then identify whether the revenue drop aligns with changes in conversion rate, return rate, or stock availability.
- D
Replace all historical null values in the fact tables with zero before analysis so that any anomaly detection query returns more consistent results.
- E
Aggregate all order history to monthly totals only, because daily granularity introduces too much noise to determine root cause.
Show answer and explanation
Correct answers: A, C
Explanation
To find reasons for anomalies in historical data, the most effective Snowflake-based approach is to compare the anomalous period with appropriate baselines and then examine related business drivers across multiple fact tables. In this case, the analyst should retain enough time granularity to isolate the two-week drop, account for seasonality, and join revenue outcomes with explanatory factors such as inventory, promotions, returns, geography, and funnel metrics.
Option 1 is correct because comparative analysis across dimensions with SQL window functions is a standard and practical way to investigate anomalies. Functions such as LAG and moving averages help establish how unusual the drop is versus prior weeks or the same period last year.
Option 3 is also correct because anomaly investigation should separate expected pattern components like trend and seasonality from unexplained deviations. Snowflake SQL supports this style of analysis through time-based aggregation, conditional metrics, and window functions. Looking at conversion rate, return rate, and stock availability is directly aligned with finding likely causes rather than merely identifying the anomaly.
Options 2, 4, and 5 are incorrect because they confuse performance tuning, data cleansing, or over-aggregation with root-cause analysis. Snowflake documentation and best practices emphasize using SQL analytic functions, careful treatment of nulls, and business-context joins to investigate changes in historical data rather than expecting platform features like clustering to provide causal explanations automatically.
- A. Correct.
Correct. This is a practical root-cause workflow in Snowflake. Comparing the anomalous weeks against comparable periods helps distinguish a true anomaly from normal seasonality. Joining orders with inventory snapshots and promotions can reveal whether the drop aligns with stockouts, promotion changes, or regional effects. Window functions such as LAG, SUM OVER, AVG OVER, and ratio calculations are appropriate for quantifying week-over-week and year-over-year shifts across dimensions like SKU and region.
- B. Incorrect.
Incorrect. Clustering keys can improve pruning and query performance for large tables, but they do not explain anomalies or perform causal analysis. A clustering key on revenue specifically is unlikely to be useful for this scenario and would not automatically identify business drivers such as stockouts or promotion changes. This option reflects a common misconception that storage optimization features provide analytical explanations.
- C. Correct.
Correct. Establishing a baseline with trend and seasonality is a strong analytical approach when investigating historical anomalies. In Snowflake, analysts commonly use SQL aggregates, window functions, and time-based grouping to compare observed values against expected patterns. Then they can examine related metrics such as conversion rate from web sessions to orders, return rate from returns data, and stock availability from inventory snapshots to isolate likely causes of the revenue decline.
- D. Incorrect.
Incorrect. Replacing all nulls with zero across historical fact data is not a sound root-cause method and can distort meaning. In many datasets, null indicates missing, not applicable, or not yet recorded data, which is different from zero. Blanket imputation may create false signals or hide data-quality issues that themselves could explain the anomaly. Null handling should be metric-specific and intentional.
- E. Incorrect.
Incorrect. Aggregating only to monthly totals would likely hide the two-week anomaly and reduce the analyst's ability to correlate the drop with operational events such as inventory shortages or promotion timing. While smoothing can be useful later, the investigation should preserve sufficient granularity to identify when and where the change occurred.