Question bank
SnowPro Advanced: Data Analyst
42 SnowPro Advanced: Data Analyst questions with the correct answer and a written explanation, organised across 4 exam domains.
- 42 questions
- 4 domains
- Foundation level
certilumQuestion index
Jump straight to any question. Answers and explanations are inline.
Data Analysis
13 questions in this domain
- Question 1Single Choice
How do aggregate functions differ from window statistical functions in Snowflake?
- AAggregate functions collapse multiple rows into a single summary row, while window functions retain the original row identity.Correct answer
- BWindow functions collapse multiple rows into a single summary row, while aggregate functions retain the original row identity.
- CBoth return exactly one row for each input group and hide row-level details.
- DBoth preserve every input row exactly as it appears in the source table.
Why this is the answer
Aggregate functions such as AVG reduce rows to one summary row. Window functions compute across rows related to the current row and preserve granularity.
- Question 2Multiple Select
Which of the following are descriptive statistics that quantify central tendency and dispersion? Select all that apply.
- AMeanCorrect answer
- BMedianCorrect answer
- CVarianceCorrect answer
- DClustering depth
Why this is the answer
Mean and median describe central tendency, while variance measures dispersion. Clustering depth is a performance and optimization metric, not a descriptive statistic.
- Question 3Interactive
The SQL statement in the code block calculates a percentile. What kind of result does it return?
- AA continuously interpolated value that may not be an actual value in the dataset.Correct answer
- BA discrete value guaranteed to exist in the dataset.
- CThe row number of the median record.
- DA boolean value that states whether interpolation was required.
Why this is the answer
PERCENTILE_CONT returns a continuous or interpolated percentile value, which may not exist as an actual row value. PERCENTILE_DISC is used when an actual existing value is required.
- Question 4Single Choice
What is returned by the CORR function in Snowflake?
- AThe correlation coefficient between two numeric variables.Correct answer
- BThe covariance of two numeric variables.
- CThe variance of one numeric variable.
- DThe median of two numeric variables.
Why this is the answer
CORR returns the correlation coefficient, which measures the strength and direction of the linear relationship between two numeric variables.
- Question 5Multiple Select
When should an analyst choose VAR_SAMP instead of VAR_POP? Select all that apply.
- AWhen the data is a random sample of a larger population.Correct answer
- BWhen the data contains every member of the entire population.
- CWhen the goal is to compute an unbiased estimate of the population variance.Correct answer
- DWhen the exact variance of the collected population is required.
Why this is the answer
VAR_SAMP is used for sample data and produces an unbiased estimate of the population variance. VAR_POP is used when the dataset represents the entire population.
- Question 6Interactive
The code block uses RANK() as a window function. How are tied sales values handled?
- ATied rows receive the same rank, and the next rank contains a gap.Correct answer
- BTied rows receive the same rank, and the next rank has no gap.
- CEach tied row receives a unique sequential row number.
- DTied rows are skipped and do not receive a rank.
Why this is the answer
RANK() assigns the same rank to tied rows and leaves gaps in the following rank values. DENSE_RANK() would not leave gaps.
- Question 7Single Choice
What is the main purpose of micro-partition pruning in Snowflake?
- ATo skip reading irrelevant micro-partitions using selective filters on well-chosen columns.Correct answer
- BTo merge all micro-partitions into one large partition after every query.
- CTo create materialized views automatically for all tables.
- DTo increase warehouse size before running a query.
Why this is the answer
Micro-partition pruning allows the Cloud Services layer to skip unnecessary partitions when queries use highly selective filters on columns with good cardinality or clustering.
- Question 8Multiple Select
Which conditions suggest that a Clustering Key might be beneficial for a large Snowflake table? Select all that apply.
- AThe table has a high clustering depth.Correct answer
- BFrequent queries filter or join on a column with poor data distribution.Correct answer
- CThe table's data distribution has become poor over time.Correct answer
- DThe table is only used for long-term archival and is rarely queried.
Why this is the answer
Clustering keys are useful when large tables have high clustering depth, poor data distribution, or frequent queries on specific filter or join columns.
- Question 9Interactive
The query in the code block performs a highly selective point lookup on a massive orders table. Which Snowflake feature is designed to optimize this pattern?
- ASearch Optimization ServiceCorrect answer
- BClustering Key
- CMaterialized View
- DLarger warehouse
Why this is the answer
Search Optimization Service accelerates highly selective and non-contiguous point lookups, while clustering is better suited for range scans.
- Question 10Single Choice
What does data spilling to local or remote disk indicate in a Query Profile?
- AIntermediate query results exceeded the available memory and were written to disk.Correct answer
- BThe query did not use any micro-partitions.
- CThe query result was too large to return to the user.
- DThe table is not compressed.
Why this is the answer
Data spilling occurs when intermediate results exceed the memory available to the warehouse. It can be caused by insufficient warehouse size or poorly written SQL.
- Question 11Multiple Select
Which statements about the Search Optimization Service are correct? Select all that apply.
- AIt is a background service that accelerates point lookup queries.Correct answer
- BIt is well suited for highly selective and non-contiguous value lookups.Correct answer
- CIt can speed up searches for specific values in massive datasets.Correct answer
- DIt replaces the need for clustering on all range-based queries.
Why this is the answer
Search Optimization Service accelerates point lookups and highly selective non-contiguous searches. Clustering remains appropriate for range scans and is not replaced by SOS.
- Question 12Interactive
The code block creates a materialized view. What tradeoff should an analyst consider when using it?
- AFaster queries at the cost of incremental maintenance compute.Correct answer
- BSlower queries with no ongoing maintenance cost.
- CNo storage or compute cost after creation.
- DThe view must be manually refreshed after every query.
Why this is the answer
Materialized views provide faster query responses for heavy aggregations but require ongoing compute for incremental maintenance.
- Question 13Single Choice
A warehouse is experiencing high user concurrency and queries are queued. Which solution should be chosen?
- AScale Out by adding more clusters to the warehouse.Correct answer
- BScale Up by changing to a larger warehouse size.
- CCreate a clustering key on every table.
- DDisable the query profile to reduce overhead.
Why this is the answer
High user concurrency is solved by scaling out, which adds more clusters. Scaling up increases compute size but does not directly address concurrent workload queuing.
Data Ingestion and Data Preparation
7 questions in this domain
- Question 14Single Choice
What is the primary result of using an aggregate function such as AVG or STDDEV in a query?
- AIt collapses multiple rows into a single summary row.Correct answer
- BIt preserves the original row identity and granularity.
- CIt adds a new column to every row without reducing row count.
- DIt sorts all rows by the computed value.
Why this is the answer
Aggregate functions summarize many rows into one result. Window functions, by contrast, preserve row-level detail.
- Question 15Multiple Select
Which statements about sample-based and population-based variance functions are correct?
- AVAR_POP should be used when analyzing a complete population.Correct answer
- BVAR_SAMP should be used when the dataset is a sample.Correct answer
- CVAR_SAMP always returns a smaller value than VAR_POP.
- DVAR_POP and VAR_SAMP are interchangeable.
Why this is the answer
Use VAR_SAMP for sample data and VAR_POP for complete populations. Choosing the wrong variant can lead to biased statistical inference.
- Question 16Interactive
Examine the SQL query in the code block. What does the output reveal about the table being profiled?
- ACOUNT(column) returns the number of rows where that column is not NULL.Correct answer
- BCOUNT(*) returns the number of rows where the column is not NULL.
- CAVG(column) treats NULL values as zero.
- DCOUNT(column) returns the same result as COUNT(*) regardless of NULLs.
Why this is the answer
COUNT(*) counts all rows, while COUNT(column) ignores NULLs. AVG also ignores NULLs and does not treat them as zero.
- Question 17Single Choice
Which function should an analyst use to return a percentile value that actually exists in the dataset?
- APERCENTILE_DISCCorrect answer
- BPERCENTILE_CONT
- CCUME_DIST
- DPERCENT_RANK
Why this is the answer
PERCENTILE_DISC returns a non-interpolated value from the input set, while PERCENTILE_CONT returns an interpolated value.
- Question 18Multiple Select
Which Snowflake functions can be used to measure the strength and direction of a linear relationship between two numeric variables?
- ACORRCorrect answer
- BCOVAR_POPCorrect answer
- CCOVAR_SAMPCorrect answer
- DSTDDEV
Why this is the answer
CORR returns the correlation coefficient, and COVAR_POP/COVAR_SAMP measure covariance. STDDEV measures the spread of one variable.
- Question 19Interactive
Review the SQL statement in the code block. What kind of join operation is being performed?
- ACartesian productCorrect answer
- BInner equi-join
- CLeft outer join
- DSelf join
Why this is the answer
CROSS JOIN returns a Cartesian product, which combines every row from the first table with every row from the second table. This can cause exploding joins.
- Question 20Single Choice
What is the primary diagnostic tool for identifying performance bottlenecks such as data spilling and exploding joins in Snowflake?
- AQuery ProfileCorrect answer
- BSearch Optimization Service
- CMaterialized View
- DClustering Key
Why this is the answer
The Query Profile is the main diagnostic tool for identifying bottlenecks, including exploding joins, data spilling, and high partition scans.
Data Presentation and Data Visualization
12 questions in this domain
- Question 21Single Choice
Which type of function collapses multiple input rows into a single summary result?
- AAggregate functionCorrect answer
- BWindow function
- CRanking function
- DScaling function
Why this is the answer
Aggregate functions like AVG and STDDEV collapse multiple rows into a single summary row, unlike window functions which preserve row identity.
- Question 22Multiple Select
Which statements about using VAR_POP versus VAR_SAMP are correct?
- AUse VAR_SAMP when the data represents a sample.Correct answer
- BUse VAR_POP when analyzing a complete population.Correct answer
- CVAR_POP on sample data can cause biased inference.Correct answer
- DVAR_POP is always preferred over VAR_SAMP.
Why this is the answer
VAR_SAMP is for sample data and VAR_POP is for a full population. Choosing VAR_POP for a sample will lead to biased statistical inference.
- Question 23Interactive
Refer to the code block. Which function in the query returns an interpolated percentile value?
- APERCENTILE_CONTCorrect answer
- BPERCENTILE_DISC
- CMEDIAN
- DAVG
Why this is the answer
PERCENTILE_CONT returns a continuous interpolated result, while PERCENTILE_DISC returns an actual value from the dataset.
- Question 24Single Choice
Which statistical metric identifies the most frequently occurring value in a dataset?
- AModeCorrect answer
- BMean
- CMedian
- DVariance
Why this is the answer
The mode is a descriptive statistic that quantifies the central tendency by identifying the most frequent value in a dataset.
- Question 25Multiple Select
Which statements accurately describe NULL handling by most Snowflake statistical functions?
- AMost statistical functions ignore NULLs.Correct answer
- BCOALESCE can be used to handle NULLs explicitly.Correct answer
- CIgnoring NULLs can skew the perceived distribution.Correct answer
- DNULLs are treated as zero in most statistical functions.
Why this is the answer
Most statistical functions ignore NULLs, so analysts should use COALESCE or similar logic to avoid an inaccurate representation of data distribution.
- Question 26Interactive
Refer to the code block. Which window frame clause is used in the running total?
- AROWSCorrect answer
- BRANGE
- CGROUPS
- DDEFAULT
Why this is the answer
The query uses ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which defines a cumulative window frame with the ROWS clause.
- Question 27Single Choice
Which Snowflake function returns the correlation coefficient between two numeric variables?
- ACORRCorrect answer
- BCOVAR_POP
- CCOVAR_SAMP
- DSTDDEV
Why this is the answer
CORR returns the correlation coefficient, which measures the strength and direction of the linear relationship between two numeric variables.
- Question 28Multiple Select
Which statements about clustering keys in Snowflake are correct?
- AThey physically reorder data in a table.Correct answer
- BThey should align with common filter or join predicates.Correct answer
- CA high clustering depth indicates poor data distribution.Correct answer
- DThey are the preferred optimization for single-value point lookups.
Why this is the answer
Clustering keys physically reorder data, align with frequent predicates, and a high clustering depth suggests poor distribution. Point lookups are better handled by Search Optimization Service.
- Question 29Interactive
Refer to the code block. Which tool should an analyst use to inspect this query for bottlenecks?
- AQuery ProfileCorrect answer
- BSearch Optimization Service
- CMaterialized View
- DClustering Key
Why this is the answer
The Query Profile is the primary diagnostic tool for identifying bottlenecks such as exploding joins, spilling, and excessive partition scans.
- Question 30Single Choice
Which Snowflake service accelerates highly selective point lookup queries on massive tables?
- ASearch Optimization ServiceCorrect answer
- BClustering key
- CMaterialized view
- DScaling up
Why this is the answer
Search Optimization Service accelerates selective point lookup queries. Clustering is better for range scans, not non-contiguous point lookups.
- Question 31Multiple Select
Which statements about materialized views are correct?
- AThey are pre-computed result sets.Correct answer
- BThey can optimize heavy aggregation workloads.Correct answer
- CThey incur incremental maintenance costs.Correct answer
- DThey are always free of storage overhead.
Why this is the answer
Materialized views are pre-computed result sets that optimize heavy aggregations but require compute resources for incremental maintenance and consume storage.
- Question 32Interactive
Refer to the code block. What is the strategy being used to handle high user concurrency?
- AScaling upCorrect answer
- BScaling out
- CMulti-clustering
- DWarehouse suspension
Why this is the answer
Increasing WAREHOUSE_SIZE is scaling up. High concurrency is better solved by scaling out, which adds more clusters instead of enlarging one warehouse.
Data Transformation and Data Modeling
10 questions in this domain
- Question 33Single Choice
What is the primary difference between aggregate functions and window statistical functions in Snowflake?
- AAggregate functions collapse multiple rows into a single summary row, while window functions preserve original row granularity.Correct answer
- BWindow functions collapse multiple rows into a single summary row, while aggregate functions preserve original row granularity.
- CBoth aggregate and window functions preserve the original row identity in all cases.
- DAggregate functions are used only for ranking, while window functions are used only for averaging.
Why this is the answer
Aggregate functions like AVG and STDDEV reduce a set of rows to one summary result. Window statistical functions calculate values across rows related to the current row and retain the original row-level detail.
- Question 34Multiple Select
Which of the following Snowflake functions can be classified as descriptive statistics for quantifying central tendency or dispersion? (Select all that apply.)
- AAVGCorrect answer
- BMEDIANCorrect answer
- CVAR_SAMPCorrect answer
- DCORR
Why this is the answer
AVG and MEDIAN are central tendency metrics, while VAR_SAMP measures dispersion around the mean. CORR measures the linear relationship between two variables, not univariate descriptive statistics.
- Question 35Interactive
Review the SQL snippet. What does the function used in this query return for the requested percentile?
- AIt returns an interpolated value between the two closest rows when the percentile falls between them.Correct answer
- BIt returns the nearest actual existing value from the dataset.
- CIt returns NULL if the percentile is not an exact row match.
- DIt returns the sum of all values divided by the number of rows.
Why this is the answer
PERCENTILE_CONT is a continuous percentile function. It interpolates between values, so the result may not exist in the dataset.
- Question 36Single Choice
An analyst is working with a random sample of customer transactions rather than the full population. Which variance function should be used to avoid biased statistical inference?
- AVAR_POP
- BVAR_SAMPCorrect answer
- CSTDDEV_POP
- DCOVAR_POP
Why this is the answer
VAR_SAMP computes sample variance, which is appropriate when the data represents a subset of a larger population. Using VAR_POP on a sample would underestimate variance and produce biased inference.
- Question 37Multiple Select
Which statements about PERCENTILE_CONT and PERCENTILE_DISC are true? (Select all that apply.)
- APERCENTILE_CONT can return an interpolated value that does not exist in the dataset.Correct answer
- BPERCENTILE_DISC always returns an actual value from the dataset.Correct answer
- CPERCENTILE_DISC interpolates between two rows when the percentile falls between them.
- DPERCENTILE_CONT returns only existing values from the dataset.
Why this is the answer
PERCENTILE_CONT interpolates and may return values not in the dataset; PERCENTILE_DISC returns one of the actual existing values.
- Question 38Interactive
Review the SQL snippet. When two employees have the same sales value, how do the two ranking columns differ?
- AThe RANK column leaves gaps after ties, while DENSE_RANK does not leave gaps.Correct answer
- BThe DENSE_RANK column leaves gaps after ties, while RANK does not.
- CBoth columns produce identical values for all rows, including ties.
- DRANK ignores ties and assigns a unique number to every row.
Why this is the answer
RANK() skips the next rank after tied rows, creating gaps. DENSE_RANK() assigns consecutive ranks without gaps.
- Question 39Single Choice
How does Snowflake's micro-partition pruning reduce the amount of data scanned by a query?
- AUsing highly selective filters in the WHERE clause so the Cloud Services layer can prune micro-partitions.Correct answer
- BAlways declaring every column as a clustering key.
- CDisabling the Cloud Services layer for large scans.
- DApplying a materialized view to every query automatically.
Why this is the answer
Snowflake stores metadata for micro-partitions. Highly selective filters allow the Cloud Services layer to prune partitions and scan only relevant data.
- Question 40Multiple Select
Which conditions indicate that a table may benefit from creating a clustering key? (Select all that apply.)
- AThe table has a high clustering depth because data distribution is poor.Correct answer
- BQueries frequently filter or join on a specific column that is not well ordered.Correct answer
- CThe table is extremely small and queried only once per month.
- DEvery large table should always have a clustering key regardless of query patterns.
Why this is the answer
High clustering depth and frequent filters or joins on a column are indicators for clustering. Clustering is not automatic or universally beneficial; small tables and infrequent queries do not need it.
- Question 41Interactive
Review the SQL snippet. Which query profile bottleneck is most likely to be observed for this statement?
- AAn exploding join caused by a Cartesian product.Correct answer
- BData spilling caused by an undersized warehouse.
- CExcessive partition scans due to missing clustering.
- DA blocking operation caused by a materialized view.
Why this is the answer
The query joins every row in orders with every row in customers due to the always-true condition, producing a Cartesian product. This is an exploding join.
- Question 42Single Choice
When is the Search Optimization Service most appropriate in Snowflake?
- AFor range scans on large contiguous datasets
- BFor highly selective, non-contiguous point lookups on massive tablesCorrect answer
- CFor replacing all clustering keys automatically
- DFor aggregating data in a materialized view
Why this is the answer
The Search Optimization Service is designed to accelerate point lookups with highly selective, non-contiguous predicates. Clustering is better for range scans.
Frequently asked questions
Everything about the SnowPro Advanced: Data Analyst question bank and how to use it.