DAA-C01 SnowPro Advanced: Data Analyst Exam Questions and Answers

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.

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.

In the Snowflake ecosystem, Parquet is treated as a semi-structured data format. When you stage a Parquet file, Snowflake does not automatically parse it into multiple columns like it might with a flat CSV file. Instead, the entire content of a single row or record is loaded into a single VARIANT column, which is referenced in SQL using the positional notation $1.

The fundamental mistake often made—and represented in Option A—is treating Parquet as a delimited format where $1, $2, and $3 refer to different columns. In Parquet ingestion, columns $2 and beyond will return NULL because the schema is contained within the object in $1.

To successfully "shred" or flatten this semi-structured data into a relational table with separate columns, an analyst must use path notation. This involves referencing the root object ($1), followed by a colon (:), and then the specific element key (e.g., $1:o_custkey). Furthermore, because the values extracted from a Variant are technically still Variants, they must be explicitly cast to the correct data type using the double-colon syntax (e.g., ::number, ::date) to ensure they land in the target table with the correct data types.

Evaluating the Options:

Option A is incorrect because it uses positional references ($2, $3, etc.) which are only valid for structured files like CSVs.

Option B is incorrect because it attempts to reference keys directly without the required stage variable ($1) and colon separator.

Option D is incorrect as it uses a non-standard parse() function that does not exist for this purpose in Snowflake SQL.

Option C is the 100% correct syntax. It correctly identifies that the Parquet data resides in $1, utilizes the colon to access internal keys, and applies the necessary type casting. This specific method is known as "Transformation During Ingestion" and is a core competency for any SnowPro Advanced Data Analyst.

To determine the correct result of the query, a Data Analyst must evaluate the nested functions according to standard SQL order of operations while correctly identifying how Snowflake handles NULL values in aggregate calculations.

Step 1: Evaluating AVG(GRADE)

The AVG() function calculates the arithmetic mean of the non-null values in a column. Based on the provided table data, the grades are: 4, 1, 2, 1, and 2. The sixth row contains a NULL grade. In Snowflake, as per ANSI SQL standards, aggregate functions ignore NULL values entirely. They are not treated as zeros, nor do they cause the function to return NULL (unless the entire column is null).

Sum of non-null values: $4 + 1 + 2 + 1 + 2 = 10$

Count of non-null values: $5$

Result: $10 / 5 = 2.0$

Step 2: Evaluating CEIL(..., 2)

The CEIL (or CEILING) function is typically used to return the smallest integer value that is greater than or equal to the input. However, Snowflake's CEIL function also supports an optional second argument for scale (precision).

The input is the result from the previous step: 2.0.

The scale argument is 2.

The function CEIL(2.0, 2) attempts to round the value up to the nearest value with two decimal places. Since 2.0 is already a whole number and has no fractional component beyond the specified scale, the ceiling remains 2.0.

Evaluating the Options:

Option A is incorrect as it represents a massive scaling error.

Option C and D are incorrect because they result from mathematical errors or incorrectly including the NULL row in the denominator (e.g., $10 / 6 = 1.66$, then rounded up).

Option B is the 100% correct answer. It accurately reflects the result of the AVG function ($2.0$) followed by the CEIL operation, which preserves the value as it is already at the target ceiling. This question tests precision in both mathematical logic and the technical nuances of Snowflake-specific SQL functions.

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.

In Snowsight, Snowflake's modern web interface, interactivity is driven by Dashboard Filters. When an analyst defines a filter, it serves as a dynamic variable that users can manipulate to update visualizations. To incorporate these filters into the SQL logic of a dashboard tile, Snowflake utilizes the colon prefix (:) syntax.

Referencing the filter as :branch_region tells the Snowflake query engine to replace that placeholder with the value(s) selected in the dashboard UI at runtime. This is consistent with how other system filters, such as :daterange, are implemented to ensure all tiles on a dashboard remain synchronized. Using this syntax allows the same query to be repurposed for different regions without manual code changes, significantly reducing the maintenance burden for the Data Analyst.

In Snowsight (Snowflake's web interface), Data Analysts can create interactive dashboards and worksheets using Filters. When you define a filter (such as a Date Range or a list of Regions), you must assign it a SQL Keyword.

This "SQL Keyword" acts as a variable placeholder within your SQL code. For example, if you create a filter for "Customer Region" and set the SQL keyword to :my_region, you can then write a query like: SELECT * FROM sales WHERE region = :my_region. When a user interacts with the UI and selects "North America" from the dropdown, Snowsight automatically injects "North America" into every instance where :my_region appears in the worksheet's SQL.

Evaluating the Options:

Option A is incorrect because the keyword is a label/variable, not the underlying function code.

Option C and Option D are incorrect as they confuse the data source of the filter values with the reference name used in the SQL code.

Option B is the correct answer. The SQL Keyword is specifically the identifier (prefixed with a colon in the code) that allows the analyst to link the UI element (the filter) to the execution logic of the query. This is a fundamental skill for the Data Presentation and Data Visualization domain, ensuring reports are dynamic and user-friendly.

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.

The SAMPLE clause (or TABLESAMPLE) is used in Snowflake to return a subset of rows from a table. When performing analysis on massive datasets, sampling allows for faster query execution and reduced credit consumption while still providing a statistically representative view of the data.

There are two primary methods of sampling in Snowflake: BERNOULLI (row-based) and BLOCK (partition-based). The query in this question uses BLOCK sampling, which selects a specific percentage of micro-partitions (blocks) rather than individual rows. This method is significantly faster for very large tables because it avoids the overhead of scanning every single row within a block; it either includes the entire block or skips it entirely.

Evaluating the Syntax:

Probability: The value inside the parentheses (0.012) represents the probability percentage for inclusion. Unlike some systems that might use decimals (where 1.0 = 100%), Snowflake treats this number as a direct percentage. Therefore, 0.012 is exactly 0.012%, not 1.2%.

Repeatable/Seed: The REPEATABLE clause (or SEED) followed by a number (99992) ensures that the sampling is deterministic. If the underlying data does not change, running this same query multiple times with the same seed will return the exact same "random" subset of blocks.

Evaluating the Options:

Options A and C are incorrect because they misinterpret the probability 0.012 as 1.2%.

Option D is incorrect because it mistakenly identifies the seed number 99992 as a target row count.

Option B is the 100% correct answer as it accurately identifies the sampling method (BLOCK), the correct percentage probability (0.012%), and the role of the seed (99992).

Snowflake utilizes a unique architecture known as Zero-Copy Cloning, which allows users to create a replica of a table, schema, or entire database without physically duplicating the underlying data files (micro-partitions). When you execute the CLONE command, Snowflake simply creates new metadata that points to the existing micro-partitions of the source object.

Because the data is not physically copied, the clone operation is nearly instantaneous and, crucially, incurs no additional storage costs at the moment of creation. Storage costs only begin to accumulate for the clone when the data in the source or the clone diverges—for example, if rows are updated or deleted in the clone, Snowflake creates new micro-partitions to store the changed data while preserving the original state in the source.

Evaluating the Options:

Option A (LIKE) only copies the column definitions and structure of the table. It does not copy the data itself, so while it doesn't incur storage, it also doesn't provide the "data" requested by the prompt.

Option B (CTAS - Create Table As Select) performs a full deep copy of the data. This creates entirely new micro-partitions, which immediately increases the storage footprint and associated costs.

Option D is a filtered CTAS operation. While it may result in less data than the full table, it still involves creating new physical storage for the 2019 records, thus incurring additional costs.

Option C is the 100% correct answer. It uses the CLONE keyword, which is the specific Snowflake feature designed to provide a full dataset for dev/test environments with zero initial storage impact. This is a core competency in the Data Transformation and Data Modeling domain.

Secure Views in Snowflake are specifically designed for data privacy and security, particularly when data is being shared across different business units or external accounts. The primary benefit of a secure view is that it prevents users from seeing the internal definitions (the underlying SQL) and protects against "trial-and-error" data discovery.

In a standard view, a savvy user might be able to deduce information about data they are not authorized to see by observing the query optimizer's behavior or by looking at the query profile. For example, by using specific filters or functions, a user might observe how long a query takes to execute or check the statistics to guess the distribution of values in hidden columns. Secure views prevent this by ensuring that Snowflake does not expose the internal metadata or the specific row/column counts that would allow a user to make observations about the quantity or nature of the underlying data that falls outside their access privileges.

Evaluating the Options:

Option B and C are incorrect because Snowflake does not merely obfuscate bytes or micro-partitions in a way that suggests a simple "hiding" of metrics; it fundamentally changes the query plan generation to ensure security.

Option D is a common misconception. In fact, secure views can sometimes be less performant than regular views. This is because the Snowflake optimizer is restricted from using certain optimizations (like predicate pushdown) that might inadvertently reveal data patterns to an unauthorized user.

Option A is the 100% correct answer. The "secure" nature of the view ensures that the user cannot use statistical side-channels or metadata observations to infer the existence or volume of data they are restricted from seeing.

To achieve the desired result, an analyst must understand the fundamental behavior of different JOIN types within Snowflake and how they affect the retention of records from the "left" or primary table. The goal here is to list all brokers, even those who have zero customers (like "Drew") or customers with zero orders (like "Debby").

In SQL, an INNER JOIN only returns rows when there is a match in both tables. If we were to use an INNER JOIN between BROKER and CUSTOMER, Drew would be excluded from the results because he has no associated records in the CUSTOMER table. Similarly, an INNER JOIN with the ORDERS table would exclude any broker whose customers haven't placed an order.

Evaluating the Join Logic:

Option C is the correct solution because it utilizes a chain of LEFT JOINs. A LEFT JOIN (or LEFT OUTER JOIN) ensures that every record from the left table (BROKER) is preserved in the result set. If no matching record exists in the joined table (CUSTOMER or ORDERS), Snowflake populates the columns with NULL. This is why "Drew" appears with a CUST_COUNT of 0 and "Debby" appears with a NULL for the total order amount.

Option A fails because it uses an INNER JOIN for the CUSTOMER table, which would immediately filter out "Drew."

Option B and Option D fail because they use INNER JOINs at different stages of the query, which would strip away brokers or customers that do not have matching order activity.

Additionally, the query correctly uses COUNT(DISTINCT c.customer_id) to ensure that customers are not double-counted if they have multiple orders, and GROUP BY 1 (referencing b.broker_name) to aggregate the data at the broker level. This pattern is essential for accurate Data Analysis in Snowflake when dealing with "optional" relationships in a star or snowflake schema.

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, 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.

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.