DAA-C01 Snowflake SnowPro Advanced: Data Analyst Exam Free Practice Exam Questions (2026 Updated)

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.

In Snowflake, a Stored Procedure is the primary object used for procedural logic and the encapsulation of multiple SQL statements. Unlike standard functions that are typically restricted to returning a single value or a table based on an input, Stored Procedures are designed to perform administrative tasks, wrap complex business logic, and execute a sequence of operations—such as DDL (Data Definition Language) and DML (Data Manipulation Language) commands—within a single callable object.

Modular code development is facilitated by Stored Procedures because they allow analysts and developers to write logic once and reuse it across various workflows. For example, a procedure can be written to truncate a staging table, call a COPY INTO command, and then execute several INSERT statements into a final fact table. This "wrapper" approach ensures consistency, as any change to the logic only needs to be updated in the procedure definition rather than in every script or task that uses it.

Evaluating the Options:

Option B is incorrect because a Materialized View is a pre-computed result set used to improve query performance; it does not "encapsulate a series of statements" for procedural execution.

Option C is incorrect because while User-Defined Functions (UDFs) provide reusability, they are generally intended for calculations or data transformations that return a result. They are much more restricted than procedures; for instance, a UDF cannot execute DDL commands like CREATE TABLE.

Option D is incorrect because a Common Table Expression (CTE) is a temporary named result set defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. It is not a persistent database object and cannot be reused across different sessions or scripts.

Option A is the correct answer. Snowflake Stored Procedures can be written in multiple languages (JavaScript, Snowflake Scripting/SQL, Python, Java, or Scala), providing the flexibility needed for sophisticated, modular automation in a data pipeline.

In Snowsight, the Scorecard chart type is specifically designed to highlight a single key metric (the "Value") and optionally compare it against a static or dynamic benchmark (the "Comparison"). This visualization is a core component of the Data Presentation and Data Visualization domain, as it provides an immediate "at-a-glance" status for high-level KPIs.

1. Interpreting the Scorecard Components:

Primary Value: The large, bold number (38) represents the actual value calculated by the underlying query. In this scenario, it is the company's average employee age.

Secondary Metric (The Percentage): When a scorecard is configured with a comparison value, Snowsight automatically calculates the percentage difference between the primary value and that comparison.

Visual Indicators: The green upward arrow indicates that the primary value is higher than the comparison value. If the value were lower, the arrow would point downward and typically appear in red.

2. The Mathematical Calculation:

The 12% shown in the exhibit is the result of the percentage change formula:

Evaluating the Options:

Options A and D are incorrect because they misidentify the large number (38) as the "comparison." In Snowflake's UI, the large number is always the primary metric being tracked, not the target it is being measured against.

Option B is incorrect because the percentage in a scorecard represents relative difference, not a statistical measure like standard deviation.

Option C is the 100% correct interpretation. It correctly identifies 38 as the current company value and 12% as the calculated difference from the industry benchmark of 34. This level of visual literacy is expected of a SnowPro Advanced: Data Analyst to ensure dashboard insights are communicated accurately to stakeholders.

In Snowflake, table relationships established via constraints are managed at the metadata level. When a table that is referenced by a foreign key is renamed, Snowflake automatically maintains the integrity of that relationship by updating the internal metadata pointers.

Unlike some legacy database systems where a rename might break hardcoded references, Snowflake's architecture ensures that the Foreign Key in the son table remains valid. After executing ALTER TABLE mother RENAME TO father;, the constraint son_fk will be automatically updated by the system to reference the new table name, father. If you were to run a SHOW TRANSACTIONS or GET_DDL command on the son table after the rename, you would see that the REFERENCES clause now points to father(id).

Evaluating the Options:

Options A and B are incorrect because Snowflake is designed to prevent "orphaned" constraints during standard DDL operations like renaming. The relationship is preserved.

Option C is incorrect because Snowflake's query optimizer can still utilize these constraints for query rewrite and join elimination optimizations, provided the constraints are defined, regardless of the table's current name.

Option D is the 100% correct answer. This automatic update behavior is a core feature of Snowflake's metadata-driven approach to Data Transformation and Data Modeling, allowing for flexible schema evolution without breaking existing relational structures.

When working with "Big Data" at the scale of trillions of rows, calculating an exact count of unique values using COUNT(DISTINCT column) is extremely resource-intensive. This is because Snowflake must keep track of every unique value encountered to ensure no duplicates are counted, leading to high memory usage and long execution times (often referred to as "spilling to disk").

To solve this, Snowflake provides HyperLogLog (HLL) functions. HLL(*) (or specifically HLL_ACCUMULATE and HLL_ESTIMATE) allows an analyst to estimate the cardinality (the number of unique elements) with a very small, known margin of error (typically around 1%). This is significantly faster and uses far fewer credits than an exact count because it uses a probabilistic algorithm rather than a state-heavy tracking mechanism.

Evaluating the Options:

Option A is technically correct for small datasets but is highly inefficient for trillions of rows, directly contradicting the "efficiently" requirement of the question.

Option C is a distractor; while Snowflake has various SYSTEM$ functions, SYSTEM$ESTIMATE is not a standard function for cardinality.

Option D is a formula that doesn't target cardinality but rather a ratio (density).

Option B is the correct answer. The HLL family of functions is the industry standard within Snowflake for high-performance cardinality estimation on massive datasets.

In Snowsight, worksheets and dashboards are tightly integrated to facilitate a smooth transition from data exploration to formal reporting. When a Data Analyst has already done the "heavy lifting"—cleaning and preparing data within standalone worksheets—Snowflake provides a direct way to convert those worksheets into dashboard tiles.

The "least operational overhead" method is to Move the existing worksheets into the dashboard. When you are in the dashboard view, you can select the option to add a tile and then choose an existing worksheet from your folders. This "Move" or "Add from Worksheet" action preserves the entire SQL statement, any formatting applied to the results, and the visualization settings (charts) that were already configured in the worksheet.

Evaluating the Options:

Option B and C are technically possible but require significantly more effort (manual copy-pasting, switching windows, re-naming tiles). This increases the risk of human error and is not considered a high-efficiency workflow in Snowsight.

Option D is the most complex and overhead-intensive option, requiring the analyst to sift through system logs to recover code they already have access to in their UI.

Option A is the 100% correct answer. By moving or importing the worksheet directly into the dashboard as a tile, the analyst maintains the link between the development work and the final presentation with just a few clicks. Note that once a worksheet is moved into a dashboard, it essentially becomes a "tile" and is managed as part of the dashboard object.

To determine the output of the final statement, a Data Analyst must perform a step-by-step trace of the code execution, following standard order of operations (inner-to-outer) and procedural logic within the Snowflake Scripting environment.

Step 1: Evaluating the innermost function, func2(10)

The definition for func2 takes a number p and returns p + 10.

Input: 10

Calculation: $10 + 10 = 20$

Result: 20

Step 2: Evaluating the next function, func1(20)

The definition for func1 takes a number v and returns v + 10.

Input: 20 (the result from Step 1)

Calculation: $20 + 10 = 30$

Result: 30

Step 3: Evaluating the procedure call, proc1(30)

The procedure proc1 takes an input variable named result. It then executes a conditional IF statement:

Conditional Check: IF (result >= 30)

Since the input is 30, the condition evaluates to True.

Action: The procedure executes RETURN result - 10.

Calculation: $30 - 10 = 20$

Summary of Logic:

The nested calls flow as follows: call proc1(30) $\rightarrow$ proc1 evaluates $30 \ge 30$ $\rightarrow$ proc1 returns $30 - 10$, which is 20. If the initial input had been lower, such that the final result passed to the procedure was less than 30, it would have followed the ELSE branch and returned the original value. This question evaluates the analyst's ability to interpret modular code and procedural control flow, which are key components of the Data Transformation and Data Modeling domain in the SnowPro Advanced: Data Analyst exam.

In the field of data warehousing and business intelligence (BI), choosing the right data model is crucial for long-term maintainability and user accessibility. While a single flat table might seem simple initially, dimensional modeling (typically using Star or Snowflake schemas) provides distinct advantages for enterprise analytics.

1. Scalability and Flexibility (Option C)

Combining all attributes into a single flat table creates a highly rigid structure. Every time a new attribute is added to a dimension (e.g., adding a "Promotion Category" to a product), the entire flat table must be rewritten or altered, which is inefficient for large datasets. Furthermore, flat tables often contain redundant data, leading to "update anomalies" where a change in a dimension attribute must be propagated across millions of rows. A dimensional model separates changing business processes (Facts) from the context of those processes (Dimensions), allowing the schema to scale and evolve independently.

2. Ad-hoc Analysis for Power Users (Option D)

Dimensional models are specifically designed to be intuitive for business users and BI tools. By organizing data into Facts (measurable metrics) and Dimensions (descriptive attributes), power users can easily "slice and dice" data across different hierarchies. For example, a user can quickly run an ad-hoc query to compare "Total Sales" (Fact) by "Store Region" (Dimension) and "Calendar Month" (Dimension). This structure provides a predictable and standardized "language" for the data, making it easier for users to build their own reports without needing a Data Analyst to create a custom flat table for every specific request.

Evaluating the Distractors:

Option A and E: These are common misconceptions. Modern cloud data warehouses like Snowflake are often highly optimized for wide "flat" tables due to columnar storage and sophisticated pruning. In many cases, a flat table may actually outperform a multi-table join (dimensional model) because it avoids the computational overhead of the join itself.

Option B: This is factually incorrect. Flat tables are denormalized (repeating data), which generally takes more storage space. Dimensional modeling is a form of normalization that saves space by storing descriptive strings once in a dimension table rather than repeating them for every transaction in a fact table.

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 data modeling and analysis, "rotating" data is a common task used to normalize datasets for reporting or visualization. The operation of taking multiple columns (like the individual months in the source image) and turning them into values within a single column (like the "MONTH" column in the target image) is specifically known as unpivoting.

The UNPIVOT relational operator in Snowflake allows an analyst to transform a "wide" table format into a "narrow" (or "long") table format. In the wide format shown in the first image, data is distributed across columns named JAN, FEB, MAR, and APRIL. While this is often easier for humans to read in a spreadsheet, it is difficult to query for trends. By applying UNPIVOT, Snowflake collapses these columns into two new ones: one for the name of the original column (the attribute, such as "MONTH") and one for the value that was stored in that column (the metric, such as "SALES").

Evaluating the Options:

Option A (PIVOT) is the opposite of the required action. It is used to turn unique values from one column into multiple separate columns (narrow to wide), which is not what is happening in the exhibit.

Option C (INTERSECT) is a set operator that returns only the distinct rows that are present in both the first and second query results. It does not perform data rotation.

Option D (EXCEPT) is a set operator that returns rows from the first query that are not present in the second.

Option B is the 100% correct answer. It is the dedicated relational operator for converting column headers into row values, which is exactly the transformation required to move from the first image to the second. Mastering this operator is a critical skill for any SnowPro Advanced: Data Analyst when preparing messy source data for high-performance analytics.

Manually defining table structures for complex semi-structured files like Parquet can be error-prone and time-consuming. Snowflake provides a specific automation workflow to handle this, involving the detection of the file's internal schema and the dynamic creation of a matching table.

The process starts with the INFER_SCHEMA function. Because Parquet files are self-describing, they contain metadata about their columns and data types. INFER_SCHEMA reads this metadata from files in a stage and returns a list of column names and types. To turn this list into an actual table, the analyst uses the CREATE TABLE ... USING TEMPLATE syntax. This command takes the output of INFER_SCHEMA as an input and automatically builds a table with the corresponding definition.

Evaluating the Options:

Option A is incorrect because CREATE TABLE LIKE is used to copy the structure of an existing table, not to build a new one from file metadata.

Option C and D are incorrect because GENERATE_COLUMN_DESCRIPTION is a helper function used to create a formatted string of column definitions, but it is not the primary command used with USING TEMPLATE for automated table creation.

Option B is the Correct answer. The combination of INFER_SCHEMA (to find the columns) and USING TEMPLATE (to build the table) is the standard Snowflake pattern for schema-on-read automation in Data Ingestion workflows.

In the Snowflake Snowsight interface, it is critical to distinguish between UI-level formatting and engine-level processing. Snowsight provides several client-side features that allow an analyst to change how data is displayed without re-executing the underlying SQL query or utilizing virtual warehouse credits.

Client-Side (No Compute Cost):

Formatting options such as adding thousand separators (Option A), adjusting the visible decimal precision (Option C), or changing the display format of dates and timestamps (Option D) are typically handled by the Snowsight web interface itself. These transformations are applied to the data that has already been retrieved into the browser's local result cache. Because they do not require the virtual warehouse to scan micro-partitions or perform new calculations, they do not incur additional compute costs.

Engine-Level (Incurs Compute Cost):

Sorting a column (Option B) is fundamentally different. While Snowsight allows you to click a column header to sort, this action frequently triggers a re-query or a secondary processing step if the entire result set is not already fully cached in the browser's memory. When you use "column options" to perform operations like sorting, filtering, or grouping on large datasets, Snowflake often has to leverage the virtual warehouse to reorganize the data. In the context of the Snowflake Data Analyst exam, sorting is identified as a transformation that requires active compute resources because the engine must evaluate the entire dataset to determine the new order of records.

Furthermore, even if a small result set is cached, complex sorting across large volumes of data necessitates warehouse involvement to ensure accuracy and handle "spilling" to local or remote storage if the sort operation exceeds available memory. Therefore, while visual "masks" are free, structural data reorganization like sorting is a compute-intensive task.

In Snowflake, "spilling" occurs when the intermediate data required to process a query exceeds the available memory (RAM) of the virtual warehouse. When this happens, Snowflake "spills" the data first to the warehouse's local SSD and, if that fills up, to remote storage (e.g., S3 or Azure Blob). This significantly degrades performance due to increased I/O latency.

To resolve this without increasing costs (i.e., without scaling up to a larger warehouse size), an analyst must reduce the memory footprint of the query.

CTEs vs. Temporary Tables: While Common Table Expressions (CTEs) are excellent for readability, they are often treated as logical entities that the optimizer may re-evaluate multiple times or struggle to keep in memory during massive joins.

Batch Processing: By splitting a single monolithic query into multiple steps and storing intermediate results in temporary tables, the analyst forces the engine to clear its memory between steps. This process acts as a "checkpoint," effectively processing the data in smaller, more manageable batches that fit within the current warehouse's memory limits.

Evaluating the Options:

Option A is incorrect because spilling is a compute-resource issue, not a storage-lock or contention issue.

Option B is incorrect because materialized views and clustering incur background credit costs for maintenance, violating the "without increasing costs" constraint.

Option C is incorrect because external tables are generally slower than native Snowflake tables due to the lack of proprietary metadata and micro-partitioning.

Snowflake Cortex ML functions, such as FORECAST, return a tabular result set when called using the instance method syntax (e.g., CALL modelX!FORECAST(...)). While this output is visible in the Snowsight results pane, the CALL statement itself cannot be used directly as a subquery within a standard INSERT INTO or CREATE TABLE AS SELECT (CTAS) statement.

To persist the results of a model's prediction into a permanent table (tableX), the Data Analyst must utilize the RESULT_SCAN table function. Snowflake stores the results of every query and function call in a temporary cache for 24 hours. The RESULT_SCAN function allows you to treat that cache as a queryable table.

The standard workflow is:

Execute the forecast: CALL modelX!FORECAST(FORECASTING_PERIODS => 12);

Immediately after, use the LAST_QUERY_ID() function to identify the query that generated the forecast results.

Create the table by querying that result set: CREATE TABLE tableX AS SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Evaluating the Options:

Option A is incorrect because the CALL syntax does not support a direct INTO clause for table creation.

Option B is incorrect because passing a table as an argument is part of the training or input phase, not the output persistence phase.

Option D is overly complex and contains non-standard terminology ("List the cache content").

Option C is the 100% correct answer. It reflects the required "post-processing" step in the Snowflake Data Cloud to bridge the gap between procedural model calls and relational table storage.

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.

When working with very large tables, calculating the exact number of unique combinations of columns (cardinality) using COUNT(DISTINCT ...) is a resource-intensive operation. It requires the Snowflake query engine to keep an exhaustive list of every unique pair encountered in memory, which can lead to high credit consumption and performance bottlenecks.

To provide a "very quick estimate," Snowflake utilizes the HyperLogLog (HLL) algorithm. The function HLL(column1, column2, ...) returns an HLL state (a binary representation) that can be used to estimate the number of distinct values with a high degree of accuracy and minimal computational overhead. This is part of Snowflake's suite of approximate aggregation functions, which are essential for Data Analysis on massive datasets where a 1% margin of error is acceptable in exchange for significantly faster results.

Evaluating the Options:

Option A is syntactically incorrect; DISTINCT cannot be used in that position with COUNT.

Option C is a valid query but will be significantly slower and more expensive than an HLL estimate on a "very large table".

Option D simply counts the total number of non-null rows, which does not represent the "number of different values" (cardinality).

Option B is the 100% correct answer. It specifically addresses the requirement for a "quick estimate" using the industry-standard probabilistic counting method built into Snowflake.

The ANY_VALUE function is an aggregate function used in Snowflake to bypass the requirement that all non-aggregated columns in a SELECT list must appear in the GROUP BY clause. In the provided query, the data is grouped by customer.id. Standard SQL would require customer.name to also be in the GROUP BY clause, even if every ID only has one name associated with it.

By using ANY_VALUE(customer.name), the analyst tells Snowflake to simply pick a value from the group. The core characteristic of this function is that it is non-deterministic. This means that if there are multiple different names for a single customer.id, Snowflake does not guarantee which one will be returned; it returns "any" value it finds most efficient to retrieve during processing. In most data modeling scenarios where a 1:1 relationship exists between an ID and a Name, ANY_VALUE is a performance-optimized alternative to using MIN() or MAX(), as it requires less computational overhead to identify a single representative value.

Evaluating the Options:

Option A is incorrect because ANY_VALUE only returns NULL if all values in the group are NULL.

Option C is incorrect because it describes the MIN() function, which is deterministic.

Option D is incorrect as it describes a median calculation (like MEDIAN()), which is a specific mathematical operation, not a "pick any" operation.

Option B is the 100% correct answer. It accurately defines the function's purpose: returning an arbitrary value from the group, acknowledging that the specific result is non-deterministic.

In Snowsight, the modern web interface for Snowflake, System Filters are specialized keywords that provide out-of-the-box interactivity for dashboards and worksheets. These filters allow non-technical users to manipulate the timeframes of visualizations (e.g., switching from "Last 7 days" to "Last 12 months") without requiring an analyst to manually rewrite the underlying SQL code.

The most critical keyword for temporal filtering is :daterange. When a dashboard contains a date filter widget (as shown in the provided exhibit), the :daterange keyword acts as a dynamic placeholder for the range selected by the user. Unlike a standard variable that might represent a single date, :daterange is specifically designed to handle the start and end boundaries of a period. When injected into a WHERE clause, Snowflake automatically expands this keyword into the appropriate logic to filter records between those two points in time.

Evaluating the Options:

Options A and B are incorrect because they use hard-coded logic (-7 days). While these would return data for the last week, they are static. Changing the filter in the dashboard UI would have no effect on these queries, failing the requirement to "easily change the time period" via the filter selection.

Option C is incorrect because :date_filter is not a reserved system keyword in Snowsight. While an analyst could create a custom filter with that name, it would not automatically link to the standard system date widget shown in the image.

Option D is the 100% correct answer. Using WHERE = :daterange is the documented standard for linking a query to the Snowsight system date filter. This ensures that every tile in the dashboard remains synchronized with the user's global date selection, providing a cohesive and interactive reporting experience.