DAA-C01 Sample Questions Answers

The GENERATOR table function is used in Snowflake to produce a synthetic result set, typically for testing or creating dummy data. However, the GENERATOR function requires at least one of two parameters to be explicitly set to a positive value to produce any rows: ROWCOUNT or TIMELIMIT.

In the provided query, the user has set TIMELIMIT => NULL. In Snowflake's SQL implementation for the GENERATOR function, NULL is treated as a zero or an undefined limit. Furthermore, since the ROWCOUNT parameter is omitted, it defaults to zero. Because neither a positive duration nor a positive row count has been specified, the generator engine has no instruction on how many rows to build.

Evaluating the Results:

Option A is a distractor; while one might think NULL means "no limit" (infinity), the GENERATOR function is designed to be finite and safe.

Option B is incorrect; the number 12 inside the RANDOM(12) function is merely a seed value for the random number generator, ensuring the output is reproducible. It has no impact on the number of rows produced by the FROM clause.

Option C is incorrect; 10 is just a boundary for the UNIFORM function.

Option D is the 100% correct answer. Because the query specifies TIMELIMIT => NULL and fails to provide a ROWCOUNT, Snowflake will execute the query successfully but return a result set containing 0 rows. This is a common "trick" question in the Data Analysis domain to test an analyst's familiarity with table function parameters.

The provided image shows a specific section of a Snowflake Query Profile, which is a visual representation of the execution plan and the actual performance of a query. Analyzing this profile is a critical skill for a Data Analyst to identify performance bottlenecks.

1. Identifying the Exploding Join: The most striking evidence in this profile is the relationship between the input and output row counts of the Join [5] operator.

Input Row Counts: The join receives approximately 95.26k rows from one branch and 150k rows from the other.

Output Row Count: The join produces a staggering 571.6M rows.

When a join operation produces a significantly larger number of rows than the sum of its inputs, it is known as an exploding join (or a "Cartesian product-like" behavior). This typically occurs when the join condition is not restrictive enough or when there are many-to-many relationships with duplicate keys in both joining tables. In this specific case, joining on C1.C_NATIONKEY = C2.C_NATIONKEY has caused the row count to balloon from thousands to over half a billion.

2. Impact on Performance: Exploding joins consume excessive CPU and memory resources to process the massive intermediate result set. This often leads to secondary problems like spilling (Option A), where the data exceeds the virtual warehouse's memory and must be written to disk. However, the profile clearly identifies the join itself as the root cause.

Evaluating the Options:

Option A is a symptom, but the visual evidence of row expansion directly points to the join logic itself.

Option C (inefficient pruning) would be identified by a high percentage of partitions scanned in the Table Scan nodes, not by a row count explosion after a join.

Option D is irrelevant; while foreign keys can help the optimizer, their absence doesn't cause this specific visual profile on its own.

Option B is the 100% correct answer. The "exploded" arrow indicating 571.6M rows leaving a join fed by significantly smaller inputs is the textbook definition of an exploding join.

One of the most powerful features of the Snowsight interface for a Data Analyst is the automatic data profiling provided in the results pane. Snowflake automatically calculates statistics and visual distributions for the result set of any query executed in a worksheet, provided the result set is not excessively large.

When the Analyst views the query results, they can simply click on the column header for the "total amount" column. This action opens a summary pane that displays key descriptive statistics such as the mean, sum, and a histogram showing the frequency distribution of the values in that specific column. This allows for immediate visual analysis of data skew, outliers, or common ranges without requiring the analyst to write additional SQL or move the data to an external visualization tool.

Evaluating the Options:

Option A is the Correct answer. This is the fastest, built-in way to perform "exploratory data analysis" (EDA) on a result set within the UI.

Option B and C are incorrect because while Snowsight does have a "Chart" tab, creating a chart requires manual configuration and is a separate step from the automatic profiling features found in the column headers.

Option D is incorrect because calling the WIDTH_BUCKET function would require the Analyst to run the query again with modified SQL logic, which explicitly contradicts the requirements of the question.

This feature significantly enhances the Data Analysis workflow by providing "at-a-glance" insights into data quality and distribution directly within the development environment.

Managing complex pipelines involving multiple dependent tasks requires an understanding of Task Graphs (DAGs). In Snowflake, a DAG is a series of tasks with parent-child relationships. If a parent task (like STEP10) fails, Snowflake automatically cancels the execution of all child tasks (like STEP11 and STEP12) to prevent data corruption or inconsistent states.

To recover from such a failure, Snowflake provides the RETRY LAST clause within the EXECUTE TASK command. The critical nuance here is that you do not target the individual failed task; instead, you target the root task of the graph (in this case, ETLDAILY). When you run EXECUTE TASK RETRY LAST;, Snowflake identifies the most recent failed run of that graph and intelligently resumes execution starting from the failed task (STEP10) and continues through all subsequent dependent tasks that were previously skipped.

Evaluating the Options:

Option A is incorrect because attempting to execute just STEP10 would treat it as a standalone run and would not automatically trigger the dependent logic of the ETLDAILY graph structure.

Options C and D are incorrect because RESUME is a DDL command used to change a task's state from SUSPENDED to STARTED (enabled). It does not trigger an immediate execution or a retry of a failed run; it merely allows the task to run on its next scheduled interval.

Option B is the 100% correct answer. It utilizes the dedicated Snowflake mechanism for graph recovery, ensuring that the entire pipeline is completed starting from the point of failure without re-running successfully completed earlier steps.

Calculating a moving average (or rolling average) is a standard time-series analysis technique used to smooth out short-term fluctuations and highlight longer-term trends. In Snowflake, this is accomplished using Window Functions and the ROWS framing clause.

To calculate a 7-day moving average when you have one record per day, the "window" or "frame" must encompass exactly 7 rows. In SQL windowing syntax, the CURRENT ROW counts as one of those days. Therefore, to reach a total of 7, you need to look back at the 6 preceding rows ($6 + 1 = 7$).

Evaluating the Options:

Options A and B use the SUM() function. While the sum is part of an average, the question specifically asks for the average itself.

Option D is incorrect because 7 PRECEDING AND CURRENT ROW actually creates an 8-day window (the current day plus the seven days before it).

Option C is the 100% correct answer. It uses the AVG() aggregate function and correctly defines the frame as 6 PRECEDING AND CURRENT ROW, ensuring the calculation reflects exactly one week of data including the current day.

Snowflake provides native support for geospatial analysis through the GEOGRAPHY and GEOMETRY data types, along with a suite of Standardized Spatial Functions. To calculate the "least distance" between two geographic points (such as an employee's home and a potential office site) on the Earth's surface, the most efficient and direct function is ST_DISTANCE.

The ST_DISTANCE function takes two GEOGRAPHY objects as input and returns the minimum geodesic distance between them. A key benefit of using this native function for Data Analysis is that it automatically returns the result in meters by default when used with the GEOGRAPHY type, which models the Earth as a spheroid. This eliminates the need for manual mathematical conversions or complex custom logic, satisfying the "least operational overhead" requirement.

Evaluating the Options:

Option A (ST_HAUSDORFFDISTANCE) is used to measure the similarity between two shapes (geometries), not the simple distance between two points.

Option B (HAVERSINE) is a mathematical formula that can be implemented manually in SQL, but it requires significantly more code and "operational overhead" compared to a single built-in function.

Option C (ST_LENGTH) is used to measure the total length of a LineString or the perimeter of a Polygon, rather than the distance between two distinct objects.

Option D is the 100% correct answer. It is the optimized, native Snowflake function for point-to-point distance calculations in the Data Cloud.

?

Snowflake provides several "approximate" functions designed to handle massive scale with high efficiency. While HyperLogLog (HLL) is the standard for estimating cardinality (unique counts), and APPROX_TOP_K is used for frequency estimation, the specific task of determining the similarity between two sets relies on a different probabilistic algorithm.

The MINHASH function is Snowflake's implementation for estimating the Jaccard similarity coefficient between two or more sets. Jaccard similarity is defined as the size of the intersection divided by the size of the union of the sample sets. Calculating an exact Jaccard similarity on billions of rows would be computationally expensive. MINHASH solves this by creating a "signature"—a small, fixed-size binary representation of the data. By comparing these signatures rather than the raw data, Snowflake can efficiently estimate how similar the original datasets are.

Evaluating the Options:

Option B (APPROX_PERCENTILE) is used to estimate the value at a specific percentile (e.g., the 95th percentile of latency).

Option C (HyperLogLog) is used for estimating cardinality (the number of unique elements), not the similarity between sets.

Option D (APPROX_TOP_K) identifies the most frequent elements in a dataset.

Option A is the 100% correct answer. It is the specific function built into Snowflake for similarity estimation using the MinHash scheme.

Snowflake Cortex ML functions, such as the Forecasting and Anomaly Detection models, are designed to be "black boxes" that provide automated machine learning capabilities directly within SQL. However, for a Data Analyst to trust and validate these models, Snowflake provides specific Object Methods (invoked with the ! operator) to inspect the model's internal logic and performance.

The !EXPLAIN_FEATURE_IMPORTANCE() method is specifically designed to provide transparency into how the model reached its conclusions. When invoked on a trained forecast model, it returns a result set showing which features (such as exogenous variables or time-based components like seasonality and trend) had the most significant impact on the predicted values. This is a critical step in the Data Analysis workflow to ensure that the model is not relying on "noise" or irrelevant data points.

Evaluating the Options:

Option A (SHOW_EVALUATION_METRICS) is used to retrieve accuracy statistics like MSE (Mean Squared Error) or MAPE (Mean Absolute Percentage Error) from the training phase, but it does not explain the contribution of specific features.

Option B (SHOW_TRAINING_LOGS) is not a standard Cortex ML method; logging details are typically handled internally or through different system views.

Option D (FORECAST) is the primary method used to actually generate the future predictions once the model is trained; it outputs the forecast itself, not the underlying component importance.

Option C is the correct answer as it is the dedicated method for model interpretability, allowing analysts to see the "why" behind the forecast by quantifying the influence of each input variable. This aligns with Snowflake's focus on "Explainable AI" within the Data Cloud.

The PERCENTILE_DISC (discrete percentile) function is an inverse distribution function that assumes a discrete distribution model. It takes a percentile value and a sort specification and returns the value from the set that corresponds to that percentile. Unlike PERCENTILE_CONT, which interpolates between values to find a continuous result, PERCENTILE_DISC always returns an actual value from the input set.

In this scenario, we have a set of four values: $\{1, 2, 3, 4\}$. The query specifies a descending order (order by v desc), so the ordered set for the calculation is $\{4, 3, 2, 1\}$.

To find the discrete percentile, Snowflake calculates the cumulative distribution. For a set of $N$ elements, each element represents a percentile rank of $1/N$. With 4 elements, each covers 25% ($0.25$) of the distribution:

Value 4: Cumulative Percentile $0.25$

Value 3: Cumulative Percentile $0.50$

Value 2: Cumulative Percentile $0.75$

Value 1: Cumulative Percentile $1.00$

The PERCENTILE_DISC(0.60) function looks for the first value whose cumulative distribution is greater than or equal to the specified percentile ($0.60$).

$0.25$ (Value 4) is not $\ge 0.60$.

$0.50$ (Value 3) is not $\ge 0.60$.

$0.75$ (Value 2) is the first value where the cumulative distribution is $\ge 0.60$.

Therefore, the result is 2. If the order had been ascending (ASC), the cumulative distribution would have been $\{1: 0.25, 2: 0.50, 3: 0.75, 4: 1.00\}$, and the result for $0.60$ would have been 3. Understanding the impact of the ORDER BY clause within the WITHIN GROUP syntax is a critical skill for the Data Analysis domain of the SnowPro Advanced: Data Analyst exam.

In Snowflake, the performance of a metadata-based query (like MAX, MIN, or COUNT) is typically near-instantaneous because Snowflake maintains constant-time statistics in its Cloud Services layer. For a standard table, SELECT MAX(num) does not even require a virtual warehouse to be active; it simply reads the value from the table's metadata.

However, when a Row Access Policy (RAP) is applied to a table, the query's behavior changes fundamentally. A row access policy is a security feature that restricts which rows are visible to a user based on their role or other attributes. To enforce this policy, Snowflake can no longer rely on the high-level metadata of the entire table because it must first determine which specific rows the user is authorized to see. Consequently, the query engine must scan the individual micro-partitions and evaluate the policy logic for every row (or block of rows) to filter out unauthorized data before calculating the maximum value. This turns a "metadata-only" operation into a data-scanning operation, which requires a running warehouse and significantly more time.

Evaluating the Options:

Option A is incorrect because the prompt states the tables are "similarly-sized." Even if it were slightly larger, a metadata lookup for a standard table would still be nearly instant.

Option C is incorrect because a multi-cluster warehouse helps with concurrency (multiple users), not the raw execution speed of a single simple aggregate query.

Option D is incorrect because USE_CACHED_RESULT refers to the Query Result Cache. While turning it off would prevent a "0ms" response from a previous run, it wouldn't explain a "significant" slowdown compared to a standard metadata fetch.

Option B is the 100% correct answer. The presence of a Row Access Policy forces a full data scan and policy evaluation, which is the most common reason for performance degradation in otherwise simple metadata queries.

To determine the correct output, a Data Analyst must understand the behavior of the STRTOK_SPLIT_TO_TABLE table function and how it interacts with different delimiters.

1. Functionality of STRTOK_SPLIT_TO_TABLE:

The STRTOK_SPLIT_TO_TABLE function tokenizes a string based on a set of specified delimiters. Unlike simple SPLIT functions that often look for a literal string as a delimiter, STRTOK treats the delimiter string as a set of individual characters. Any character present in the delimiter string will trigger a split. Crucially, it skips empty tokens—if multiple delimiters appear consecutively, it does not produce a row for the space between them.

2. Evaluating the Data and Delimiters ('*-'):

'JOHN*DOE': The * character is a delimiter. This results in two tokens: JOHN and DOE.

'ALICE-SMITH': The - character is a delimiter. This results in two tokens: ALICE and SMITH.

'BOB*-JOHNSON': Both * and - are delimiters. Because they appear consecutively (*-), the function splits at each but ignores the empty space between them. This results in two tokens: BOB and JOHNSON.

3. Analyzing the Resulting Rows:

The LATERAL join produces a row for every token generated. The columns SEQ (identifying the source row sequence) and INDEX (position of the token within that sequence) are used for sorting.

Student 1: 2 tokens (JOHN, DOE) $\rightarrow$ 2 rows.

Student 2: 2 tokens (ALICE, SMITH) $\rightarrow$ 2 rows.

Student 3: 2 tokens (BOB, JOHNSON) $\rightarrow$ 2 rows.

Evaluating the Options (image_8233d8.png):

Option A incorrectly only shows results for Student 3.

Option C incorrectly implies Student 1 and 2 produce three tokens each.

Option D incorrectly interprets the consecutive delimiters in Student 3's name as creating a different sequence count.

Option B is the 100% correct result set. It accurately shows that each student’s name is split into exactly two parts, properly handling the varying delimiters and the consecutive characters in the third record.

In Snowsight, managing dashboard layouts requires an understanding of how tiles (queries or visualizations) are stored versus how they are displayed. When an analyst wants to remove a tile from the visible dashboard grid without destroying the underlying query logic or historical configuration, the Unplace action is the correct functional choice.

When a tile is unplaced, it is removed from the dashboard's active layout but remains part of the dashboard's "library" of available content. This is a critical distinction from the Delete action (Option C), which permanently removes the tile and its associated SQL code from the dashboard object. Unplacing allows the analyst to "archive" the work temporarily. Because the tile still technically exists within the dashboard's metadata, any new data added to the underlying tables will still be processed by the query whenever the tile is eventually placed back onto the grid.

Evaluating the Options:

Option A (Show/Hide) is not a standard standalone command for dashboard tile management in Snowsight; visibility is typically managed through placement on the grid.

Option B (Duplicate) creates a second copy of the tile. While this preserves the data, it does not satisfy the requirement to "hide" the current tile; it actually adds more clutter to the dashboard.

Option C (Delete) is incorrect because the prompt specifies that the data and tile will need to be available in the future. Deleting would require the analyst to rewrite the SQL and reconfigure the visualization from scratch.

Option D is the 100% correct answer. Unplacing is the "soft-remove" feature of Snowsight. It preserves the tile in the "Unplaced Tiles" sidebar, allowing for quick restoration at a later date. This feature is essential for analysts who need to manage evolving reporting requirements where certain metrics may only be relevant seasonally or during specific business cycles.

This scenario requires a specific combination of Snowflake's extensibility features: the ability to run Python (pandas) code, the ability to return multiple rows for a single input (tabular output), and high performance through vectorization.

A User-Defined Table Function (UDTF) is the correct architectural choice when an input value needs to be expanded into a set of rows. While a standard UDF returns exactly one value per input row, a UDTF can return zero, one, or many rows, making it ideal for "parsing" or "splitting" tasks. By using a Vectorized UDTF (specifically utilizing the Python handler with pandas), the Analyst can process blocks of rows as pandas DataFrames or Series. This is significantly more efficient than processing one row at a time because it reduces the overhead of the Snowflake-to-Python execution engine.

Evaluating the Options:

Option A is incorrect because a standard UDF can only return a single scalar value per input row, making it impossible to "return multiple rows of parsed output."

Option C is incorrect because External Functions are used to call code hosted outside of Snowflake (like an AWS Lambda). While they can process data, they introduce network latency and are not the native way to run pandas code for internal joins.

Option D is incorrect because Stored Procedures are generally used for administrative tasks and procedural logic. While they can return tables, they cannot be directly "joined" with other tables in a standard SELECT statement in the same way a UDTF can.

Option B is the correct choice. It satisfies the requirement for pandas integration, provides the necessary multi-row output via the process and end_partition methods, and allows the analyst to use the LATERAL keyword to join the parsed results directly with existing tables in a single SQL statement.

To predict future values based on historical data, an analyst must determine the mathematical relationship between two variables—typically time (independent variable) and revenue (dependent variable). This is the foundation of linear regression.

The REGR_SLOPE function is a linear regression function that calculates the slope of the "least squares" regression line for non-null pairs in a group. In the context of sales forecasting, the "slope" represents the rate of change in revenue over time. By calculating the slope, an analyst can project that trend forward to estimate what the revenue will be at a future date (the end of the year).

Evaluating the Options:

Option A (CORR) measures the correlation coefficient, which tells you how strongly two variables are related (between -1 and 1), but it does not provide a mathematical formula to predict a specific future value.

Option C (COVAR_SAMP) calculates the sample covariance, which indicates the direction of a linear relationship but not the magnitude or slope required for prediction.

Option D (VARIANCE) is a descriptive statistic that measures data spread (how far numbers are from the mean) and is not used for trend projection or prediction.

Option B is the 100% correct answer. Along with REGR_INTERCEPT, REGR_SLOPE allows the analyst to build the linear equation $y = mx + b$ to perform predictive analytics.

Working with semi-structured data types like Arrays is a core competency for a Snowflake Data Analyst. In Snowflake, arrays are zero-indexed, meaning the first element is at position 0. Consequently, the index of the last element is always the total number of elements minus one ($Size - 1$).

To retrieve an element from a specific index, Snowflake provides the GET() function. This function takes the array column and the calculated index as arguments. When combined with ARRAY_SIZE(), which returns the total count of elements in the array, the formula ARRAY_SIZE(COL_B)-1 accurately targets the final index regardless of the array's length.

Evaluating the Options:

Option B is incorrect because using ARRAY_SIZE as the index directly (without subtracting 1) results in an "out-of-bounds" error or returns NULL, because the index equals the length (e.g., in an array of 3 items, the max index is 2).

Option C is incorrect. While some programming languages (like Python) allow negative indexing to start from the end, Snowflake SQL does not support this shorthand for arrays; it would simply return NULL.

Option D is incorrect because LAST_VALUE is an Analytic/Window function used to find the last row in a sorted result set, not the last element within a single array cell.

Option A is the 100% correct approach. It uses the standard, robust method for dynamic index calculation. This ensures that even if different rows have arrays of different lengths, the query will always successfully "pluck" the final item from each. This skill is vital for Data Transformation tasks, such as extracting the most recent status from a history array.

When dealing with massive datasets containing billions of rows, efficiency is paramount. Snowflake provides two primary ways to retrieve a small subset of data: Sampling and Result Set Limiting.

The SAMPLE clause is used to return a subset of rows based on a specific count or percentage. The correct syntax for specifying a fixed number of rows is SAMPLE ( ROWS). This is highly effective for exploratory data analysis where you need to see the shape of the data without the overhead of scanning the entire table. It is important to note that SAMPLE does not guarantee a truly random distribution unless specified, but it is much faster than a full table scan.

The TOP <n> or LIMIT <n> clauses are used to restrict the number of rows returned in the output. The TOP syntax is commonly used in many SQL dialects and is fully supported in Snowflake to return the first "n" rows the query engine encounters. While TOP is often used with an ORDER BY clause to get the highest or lowest values, using it alone on a massive table like FCT_SALES is a standard way to quickly peak at the data.

Evaluating the Options:

Option A is incorrect syntax; the ROWS keyword and parentheses are required for a fixed count.

Option B is Correct; it uses the standard Snowflake SAMPLE syntax for a specific row count.

Option C and Option E are incorrect because ROWS is not a valid keyword within a TOP clause in Snowflake.

Option D is Correct; it uses the standard TOP syntax to limit the result set.

By using these methods, a Data Analyst ensures they are not wasting compute resources (Credits) by pulling billions of unnecessary rows into the UI or a local environment.