Free Snowflake DAA-C01 Practice Exam with Questions & Answers | Set: 2

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.

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.

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.

To determine the correct output, a Data Analyst must understand the behavior of the ILIKE operator, wildcard characters, and the ESCAPE clause in Snowflake.

1. Pattern Matching with ILIKE: The ILIKE operator performs case-insensitive pattern matching. The provided pattern is 'p%^_j%'.

p: The string must start with the letter 'P' or 'p'.

%: This wildcard matches any sequence of zero or more characters.

^_: The ESCAPE '^' clause identifies the caret symbol as an escape character. This means the underscore (_) immediately following it is treated as a literal character rather than its usual wildcard function (which matches any single character).

j: The letter 'J' or 'j' must follow the literal underscore.

%: Matches any trailing sequence of characters.

2. Evaluating the Data: Applying this logic to the values in the cert_dem table:

'Peter*John': While it starts with 'P' and contains 'J', it lacks the required literal underscore. No match.

'Peter John': Contains a space instead of an underscore. No match.

'Peter_John': Starts with 'P', contains a literal underscore, and is followed by 'J'. Match.

'Peter_john': Because the operator is ILIKE (case-insensitive), the lowercase 'j' is accepted. Match.

null: Comparisons with NULL using LIKE or ILIKE always return NULL (unknown), so it is excluded from the results.

3. Ordering the Results: The query includes ORDER BY 1, which sorts the results alphabetically based on the first column. Between 'Peter_John' and 'Peter_john', the standard sort order typically places uppercase before lowercase in most Snowflake collations.

Evaluating the Options (image_829269.png):

Option A incorrectly excludes the case-insensitive match.

Option B incorrectly identifies values that lack the literal underscore.

Option D incorrectly includes values that do not meet the escaped underscore requirement.

Option C is the 100% correct result set, displaying both versions of 'Peter_John' that contain the literal underscore.

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.

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.

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.

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.

This scenario highlights the rules governing Shared Databases and Cross-Database Joins in Snowflake. When a user acquires data from the Snowflake Marketplace, it arrives in their account as a "Shared Database."

A critical restriction of shared databases is that they are read-only. Users (including Data Analysts and even AccountAdmins) cannot create new objects—such as tables, schemas, or views—directly inside a database created from a share. Therefore, Options A, C, and D are architecturally impossible because they all involve trying to write a new view into the ADDRESS_DATA database.

To combine the shared data with internal data (like the ORDERS table in GLOBAL_DWH), the Analyst must create the view in a database where they have CREATE VIEW privileges and that is writeable. Since GLOBAL_DWH is an internal database owned by the organization, it is the appropriate location to host the logic that joins local data with external enrichment data.

Evaluating the Options:

Option A and C are incorrect because you cannot create objects in a database created from a share.

Option D is incorrect because while IMPORTED_PRIVILEGES are necessary to view the data, they do not grant the ability to create objects within the share.

Option B is the 100% correct answer. By creating the view in the GLOBAL_DWH database (or any other local, non-shared database), the Analyst can successfully reference ADDRESS_DATA.OPENADDRESS and GLOBAL_DWH.PUBLIC.ORDERS in a single SQL statement, effectively creating an enriched data layer for the organization.