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

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.

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.

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.

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

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.

Snowsight provides special System Keywords that allow analysts to create dynamic, interactive dashboards without hard-coding dates. Two of the most critical keywords are :daterange and :datebucket.

The :daterange keyword is designed to filter the volume of data based on a time period selected by the user in the dashboard UI (e.g., "Last 30 days" or "Current Year"). Because it acts as a filter on the underlying data, it must be placed in the WHERE clause of the SQL statement (e.g., WHERE created_at = :daterange). This ensures that only the records within the selected timeframe are processed by the query.

The :datebucket keyword is used for time-series aggregation. It allows the user to dynamically change the granularity of the data—for example, switching a chart from "Daily" to "Monthly" views without rewriting the query. To achieve this, :datebucket is used inside a date-truncation function in the SELECT list and, crucially, must be included in the GROUP BY clause to correctly aggregate the metrics (e.g., GROUP BY 1 or GROUP BY :datebucket).

Evaluating the Options:

Option A is incorrect because :datebucket is for grouping/truncation, not for filtering in a WHERE clause.

Option B is incorrect because :daterange is a filter (returning a range), not a scalar value suitable for a SELECT list.

Option C is incorrect for the same reasons as A and B.

Option D is the 100% correct answer. It follows the standard Snowsight design pattern: :daterange restricts the data rows in the WHERE clause, while :datebucket defines the temporal aggregation level in the GROUP BY clause.

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.

This error occurs due to a misunderstanding of how Snowflake resolves object names and the location of the Shared Snowflake Database. In Snowflake, the ACCOUNT_USAGE schema is a collection of views that provide comprehensive historical data about account activities, including credit consumption, storage, and query history. These views are stored within the system-defined database named SNOWFLAKE.

When the Data Analyst executes the query shown in the image, they are operating within the database context of DB1 and the schema context of PUBLIC. Because the query references ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY without a fully qualified name, the Snowflake compiler attempts to resolve the object starting from the current database context. Consequently, it looks for a schema named ACCOUNT_USAGE inside DB1. As the error message correctly indicates, Schema 'DB1.ACCOUNT_USAGE' does not exist.

To fix this, the Analyst has two options:

Fully Qualify the Object Name: Modify the query to reference the full path: FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY. This is the most common best practice as it allows the query to run regardless of the user's current session context.

Change the Context: Use the command USE DATABASE SNOWFLAKE; before running the query. This changes the session's database context so that ACCOUNT_USAGE can be found.

It is a common misconception (represented in Option A) that only an ACCOUNTADMIN can access these views. While access is restricted by default, the ACCOUNTADMIN can grant IMPORTED PRIVILEGES on the SNOWFLAKE database to other roles, such as a Data Analyst role. However, the specific error shown is a namespace resolution error, not a permission-denied error (which would typically say "Insufficient privileges"). Therefore, changing the context or fully qualifying the name is the direct solution to this specific failure.

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.

Snowsight, the modern web interface for Snowflake, introduces robust collaboration features through shared worksheets. Understanding the synchronization and versioning logic is critical for the Data Presentation and Data Visualization domain.

When a worksheet is shared with collaborators, Snowflake enables real-time collaboration (Option D). This means that as one user types or modifies SQL code, those changes are immediately visible to all other users who have the worksheet open. This functionality is similar to co-authoring in modern document editors, allowing data teams to debug or develop complex queries synchronously without the need for manual copy-pasting or constant screen sharing.

Furthermore, Snowflake manages the execution history of shared worksheets through a structured versioning system (Option C). If multiple users are interacting with the same worksheet, Snowflake does not overwrite a single "master" state during execution. Instead, every time a user executes the worksheet (or a portion of it), Snowflake creates a new version of the worksheet content associated with that specific run. This ensures that the code used to generate a particular result set is preserved and tied to that specific Query ID in the history.

Evaluating the Options:

Option A is incorrect because while users can see current edits, viewing a full, navigable history of "past versions" is not a standard feature in the same way it is in dedicated version control systems like Git.

Option B is incorrect as worksheet sharing is restricted to users within the same Snowflake account. Sharing across accounts requires the use of Data Shares or Private Listings.

Option E is incorrect because Snowflake preserves the history rather than overwriting it, allowing analysts to audit who ran what code and when.

Options C and D are the 100% correct answers. They define the synchronous and persistent nature of Snowsight collaboration, ensuring that teams can work together effectively while maintaining an immutable trail of executions.

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.

Understanding how Snowflake aggregate functions like MIN() and MAX() handle numerical data and NULL values is fundamental for accurate Data Analysis. In this scenario, we have a table with five records distributed across two departments.

The MIN() function returns the smallest non-null value in the specified column across all rows in the group (or the entire table, if no GROUP BY is present). Looking at the salary column in the employees table, the values are: 10000, 9000, 8000, 15000, and NULL. The NULL value is ignored by the calculation. Among the remaining numerical values, 8000 is the smallest. Therefore, MIN_VAL will be 8000.

The MAX() function operates similarly, returning the largest non-null value from the set. Comparing the same list of numerical values—10000, 9000, 8000, and 15000—the largest value is clearly 15000. Consequently, MAX_VAL will be 15000.

Evaluating the Options based on the exhibit (image_8ca0df.png):

Option A incorrectly identifies the minimum and maximum based on the employee_id column (where 2000 is max and 900 is min), rather than the requested salary column.

Option B is incorrect as it seems to mix values from different columns or specific rows.

Option C is incorrect because it mistakenly suggests that MIN() returns NULL if a NULL value is present in the column. In Snowflake, standard aggregate functions (except for specialized ones like ARRAY_AGG or certain window functions with specific clauses) skip NULL values entirely.

Option D is the 100% correct output. It displays MIN_VAL as 8000 and MAX_VAL as 15000, which accurately reflects the mathematical minimum and maximum of the non-null entries in the salary column.

This behavior is consistent across all relational databases adhering to ANSI SQL standards, which Snowflake follows for these core aggregate operations.

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.

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.