To find information about query activity from 90 days ago, a Snowflake user should use the account_usage.query_history_archive view. This view is designed to provide access to historical query data beyond the default 14-day retention period found in the standard query_history view. It allows users to analyze and audit past query activities for up to 365 days after the date of execution, which includes the 90-day period mentioned.

To return results from the results cache in Snowflake, certain conditions must be met:

Privileges: The user must have the appropriate privileges on the objects associated with the query. This ensures that only authorized users can access cached data.

Time Frame: The query must have been run within 24 hours of the previously-run query. Snowflake’s results cache is designed to store the results of queries for a short period, typically 24 hours, to improve performance for repeated queries.

When a Snowflake virtual warehouse is resized, the data cached in the warehouse is not lost. This is because the cache is maintained independently of the warehouse size. Resizing a warehouse, whether scaling up or down, does not affect the cached data, ensuring that query performance is not impacted by such changes.

 External functions in Snowflake are a special type of User-Defined Function (UDF) that call code executed outside of Snowflake, typically through a remote service. Unlike traditional UDFs, external functions do not contain SQL code within Snowflake; instead, they interact with external services to process data2.

https://docs.snowflake.com/en/sql-reference/external-functions.html#:~:text=External%20functions%20are%20user%2Ddefined,code%20running%20outside%20of%20Snowflake.

 External stages can be altered and dropped in Snowflake. An external stage points to an external location, such as an S3 bucket, where data files are stored. Users can modify the stage’s definition or drop it entirely if it’s no longer needed. This is in contrast to table stages, which are tied to specific tables and cannot be altered or dropped independently.

The correct answer is D. Increase the value of the MAX_CLUSTER_COUNT .

Queries wait in a queue when a warehouse does not have enough available compute capacity to handle the current concurrency. For a multi-cluster warehouse, increasing MAX_CLUSTER_COUNT allows Snowflake to start more clusters when concurrency increases.

Why D is correct:

Increasing MAX_CLUSTER_COUNT allows a multi-cluster warehouse in Auto-scale mode to add more clusters, up to the configured maximum. This helps reduce query queuing for concurrent workloads.

Why the other options are incorrect:

A. Increasing WAREHOUSE_SIZE gives each cluster more compute power, which can help individual queries run faster, but it is not the best direct solution for reducing queued concurrent queries.

B. Increasing AUTO_SUSPEND keeps the warehouse running longer but does not increase concurrency capacity.

C. WAIT_FOR_COMPLETION = FALSE is used with warehouse resize behavior and does not reduce query queuing.

Official Snowflake documentation reference:

Snowflake documentation explains that multi-cluster warehouses improve concurrency by starting additional clusters as query demand increases. The MAX_CLUSTER_COUNT property defines the maximum number of clusters that can run.

The Search Optimization Service can improve highly selective searches against supported values stored in semi-structured columns such as VARIANT, OBJECT, and ARRAY. It maintains a search-access structure that helps Snowflake locate qualifying micro-partitions without scanning all candidate data.

A materialized view can precompute frequently used expressions or extracted values from semi-structured data. Repeated queries can then access the materialized representation instead of repeatedly performing expensive extraction or transformation operations against the full source table.

A clustering key cannot simply be defined directly on a VARIANT, OBJECT, or ARRAY value. In some designs, a supported expression that extracts and converts a scalar value can be used for clustering, but option B as written is not generally correct.

Query Acceleration Service can help certain eligible scan-intensive queries, but it is not the semi-structured-data-specific optimization represented by this question. Multi-cluster warehouses improve concurrency rather than the execution speed of one query.

If a user needs to connect to Snowflake with a tool or technology that is not listed in Snowflake ' s partner ecosystem, Snowflake recommends using its JDBC or ODBC drivers. These drivers provide a standard method of connecting from various tools and programming languages to Snowflake, offering wide compatibility and flexibility. By using these drivers, users can establishconnections to Snowflake from their applications, ensuring they can leverage the capabilities of Snowflake regardless of the specific tools or technologies they are using.

The privilege required on a virtual warehouse to abort any existing executing queries isOPERATE. TheOPERATEprivilege on a virtual warehouse allows a user to perform operational tasks on the warehouse, including starting, stopping, and restarting the warehouse, as well as aborting running queries. This level of control is essential for managing resource utilization and ensuring that the virtual warehouse operates efficiently.

The correct answer is B. Copilot provides assistance when creating and interpreting queries .

Snowflake Copilot is an AI-assisted feature that helps users work with Snowflake more efficiently. It can assist with writing SQL, understanding queries, and interpreting query-related information.

Why B is correct:

Copilot is designed to help users create, refine, and understand SQL queries using natural language assistance.

Why the other options are incorrect:

A. Query optimization is primarily handled by Snowflake’s optimizer and performance features, not Copilot.

C. Copilot does not automatically rewrite table schemas based on inferred data patterns.

D. Copilot is not primarily a feature for executing stored procedures from prompts.

Official Snowflake documentation reference:

Snowflake documentation describes Snowflake Copilot as an AI assistant for helping users generate, understand, and work with SQL and Snowflake data workflows.

The correct answer is C. Clustering .

Materialized views store precomputed results and can improve query performance. Snowflake also supports clustering with materialized views in applicable scenarios, which can further improve pruning and query performance.

Why C is correct:

A materialized view can be clustered to help optimize access patterns. Clustering can improve performance when queries filter or join on specific columns.

Why the other options are incorrect:

A. Time Travel is not the main supported feature associated with standard materialized views in this question.

B. Fail-safe applies to protected table data storage, not as a feature used directly with standard materialized views for query optimization.

D. Data sharing generally requires secure objects for sharing views. A standard materialized view is not the best answer for this question.

Official Snowflake documentation reference:

Snowflake documentation explains materialized view behavior and supports clustering considerations for materialized views.

The correct answers are A. Increase the size of the virtual warehouse and B. Edit the queries to process the data in smaller batches .

Memory spilling occurs when a query requires more memory than is available in the virtual warehouse. Snowflake may spill intermediate data to local or remote storage, which can slow query performance.

Why A is correct:

Increasing the warehouse size provides more compute and memory resources. This can reduce or eliminate spilling for memory-intensive queries.

Why B is correct:

Rewriting queries to process data in smaller batches reduces the amount of intermediate data held in memory at one time. This can reduce spilling and improve performance.

Important note about option C:

Option C is misleading because a virtual warehouse cluster’s size is changed by increasing the warehouse size . Since option A already says this correctly, option C is not the best answer.

Why the other options are incorrect:

C. “Increase the size of the virtual warehouse clusters” is essentially a less precise duplicate of increasing warehouse size. The exam-style answer is A.

D. Suspending and resuming a warehouse may clear cache, but it does not increase available memory and does not solve memory spilling.

E. Clustering can improve pruning and reduce scanned data for some queries, but it is not the direct general solution for queries that are spilling because they are too large for memory.

Official Snowflake documentation reference:

Snowflake Query Profile documentation explains that spilling to local or remote storage can occur when operations do not fit in memory. Snowflake performance guidance recommends scaling up the warehouse or rewriting queries to reduce the amount of data processed.

The correct answer is C. GET_PATH .

GET_PATH extracts a value from semi-structured data using a path expression. It can be used to access values inside an object, including nested values.

Why C is correct:

For an object stored in a VARIANT or OBJECT column, GET_PATH can retrieve a value by specifying the key path.

Example:

SELECT GET_PATH(src, ' customer.name ' )

FROM my_table;

This accesses the name value inside the customer object.

Why the other options are incorrect:

A. OBJECT_KEYS returns the keys from an object, not the values for a specific key path.

B. OBJECT_PICK returns a new object containing selected key-value pairs, but it is not the general function for accessing a value by path.

D. XMLGET is used for XML data, not general JSON-style object access.

Official Snowflake documentation reference:

Snowflake documentation describes GET_PATH as a function that extracts a value from semi-structured data using a path name.