DAA-C01 Question 262
Single answerIdentify correlations among variablesA retail analytics team stores daily metrics in a Snowflake table named DAILY_STORE_METRICS with the columns STORE_ID, SALES_AMT, AD_SPEND, SITE_VISITS, and DAY_DT. The team wants to quickly identify whether higher advertising spend and website traffic are linearly associated with higher sales before building a forecasting model. They need a solution that can be run directly in Snowflake with SQL and returns a numeric measure of pairwise linear relationship strength. Which approach should they use?
- A
Use the CORR aggregate function in SQL, for example: SELECT CORR(AD_SPEND, SALES_AMT), CORR(SITE_VISITS, SALES_AMT) FROM DAILY_STORE_METRICS;
- B
Use the COUNT function on each column and compare the counts; matching counts indicate stronger correlation between the variables.
- C
Use a window function such as ROW_NUMBER() over DAY_DT for each variable, then compare the row numbers to infer correlation strength.
- D
Use AVG and STDDEV only; if two columns have similar averages and standard deviations, they are strongly correlated.
Show answer and explanation
Correct answer: A
Explanation
For identifying correlations among variables in Snowflake, the most direct SQL-based approach is to use the CORR function, which computes the Pearson correlation coefficient for paired numeric data. This is well suited for exploratory analysis when a team wants to understand whether variables such as AD_SPEND or SITE_VISITS are linearly associated with SALES_AMT. In practice, analysts often compute several pairwise correlations first to determine which predictors may be useful in later modeling steps. Best practice is also to remember that correlation does not imply causation and that Pearson correlation specifically measures linear association and can be sensitive to outliers. Snowflake documentation for statistical aggregate functions includes CORR for this purpose.
- A. Correct.
Correct. Snowflake supports the CORR function to calculate the Pearson correlation coefficient between two numeric expressions. This is the appropriate SQL-based method for quantifying pairwise linear association directly in Snowflake. A value near 1 indicates strong positive linear relationship, near -1 indicates strong negative linear relationship, and near 0 indicates little to no linear relationship.
- B. Incorrect.
Incorrect. COUNT only shows the number of non-NULL values and says nothing about how two variables move together. Candidates may choose this if they confuse data completeness with statistical relationship, but equal counts do not imply correlation.
- C. Incorrect.
Incorrect. ROW_NUMBER() assigns sequence numbers based on ordering and is not a statistical measure of association. While rankings can be used in some nonparametric analyses, simply comparing row numbers does not produce a valid correlation metric and does not address the requirement for a numeric measure of linear relationship.
- D. Incorrect.
Incorrect. Similar averages and standard deviations do not mean two variables are correlated. Correlation measures co-movement between paired observations, not similarity in distribution summaries. Two variables can have nearly identical means and variability while being completely unrelated.