You are troubleshooting a slowly performing query in Snowflake that aggregates data from a large ORDERS table (10 billion rows) partitioned by ORDER DATE. The query execution plan shows significant 'Remote Spill to Disk'. Which of the following actions would be MOST effective in reducing the spill and improving query performance? Assume all statistics are up-to-date and the data is properly clustered by ORDER_DATE.

• A. Optimize the query to leverage data pruning based on ORDER DATE by ensuring the query filters on a specific or limited range of ORDER DATE values.
• B. Rewrite the query to use window functions instead of aggregate functions.
• C. Increase the value of the parameter. This allows the warehouse to scale up further if needed.
• D. Reduce the number of columns selected in the query, only selecting those that are essential for the aggregation.
• E. Increase the virtual warehouse size. This will provide more memory for the query to execute.

Explanation: Only visible for PassTorrent members. You can sign-up / login (it's free).

You have a table named 'EVENT LOGS with columns including 'EVENT ID, 'EVENT TIMESTAMP', 'USER ID, 'EVENT_TYPE, and 'EVENT DATA (which stores JSON data). Users frequently query the table filtering by specific key-value pairs within the 'EVENT DATA column. Which of the following approaches will BEST improve query performance when filtering on values inside the JSON column, considering the use of search optimization?

• A. Extract the frequently queried key-value pairs from the 'EVENT_DATR JSON into separate virtual columns and enable search optimization on these virtual columns.
• B. Enable search optimization directly on the 'EVENT DATA' column.
• C. Increase the warehouse size.
• D. Convert the ' EVENT_DATX column to a VARCHAR column and enable search optimization on it.
• E. Create a materialized view that extracts the key-value pairs from the ' EVENT_DATX column and enable search optimization on the materialized view's columns.

Explanation: Only visible for PassTorrent members. You can sign-up / login (it's free).

You have a Snowflake stage pointing to an external cloud storage location containing numerous Parquet files. A directory table is created on top of it. Over time, some files are deleted or moved from the external location. You notice discrepancies between the directory table's metadata and the actual files present in the storage location. Choose the option that best describes how Snowflake handles these discrepancies and the actions you should take.

• A. Snowflake does not track file deletions. If a file is deleted from cloud storage after being added to a directory table, Snowflake continues to reference the deleted file, potentially causing errors during data loading. Run 'VALIDATE on the directory table.
• B. Snowflake requires you to drop and recreate the directory table periodically to synchronize the metadata with the external storage. Using 'ALTER DIRECTORY TABLE REFRESH' will not remove deleted files from the directory table's metadata. However, these invalid files wont be shown in select unless explicitly used.
• C. Snowflake does not automatically detect these changes. You must manually refresh the directory table using 'ALTER DIRECTORY TABLE ... REFRESH' to synchronize the metadata. Snowflake does not provide an automated cleanup of metadata associated with removed files.
• D. Snowflake automatically updates the directory table in real-time, reflecting the changes immediately. No action is needed.
• E. Snowflake automatically detects deleted files and marks them as 'invalid' in the directory table. Queries will automatically exclude these invalid files.

Explanation: Only visible for PassTorrent members. You can sign-up / login (it's free).

You are designing a data pipeline that requires applying a complex scoring algorithm to customer data in Snowflake. This algorithm involves multiple steps, including feature engineering, model loading, and prediction. You want to encapsulate this logic within a reusable component and apply it to incoming data streams efficiently. Which of the following approaches is most suitable and scalable for implementing this scoring logic as a UDF/UDTF, considering real-time data processing and low latency requirements?

• A. A Python UDF that loads a pre-trained machine learning model (e.g., using scikit-learn) and performs predictions on the input data.
• B. A JavaScript UDF that uses basic JavaScript functions to perform the entire scoring algorithm without external dependencies.
• C. A SQL UDF containing a series of nested CASE statements to implement the entire scoring algorithm.
• D. A Python UDTF using Snowpark, leveraging external libraries like 'torch' for accelerated calculations and ML model inference by GPU.
• E. A Java UDTF that leverages a custom Java library for feature engineering and model prediction, deployed as a JAR file to Snowflake's internal stage.

Explanation: Only visible for PassTorrent members. You can sign-up / login (it's free).

A financial services company, 'Acme Finance', wants to share aggregated, anonymized transaction data with a research firm, 'Data Insights', through a Snowflake Data Clean Room. Acme Finance needs to ensure that Data Insights can only analyze the data using pre- defined aggregate functions and cannot access the raw, underlying transactional details. Acme Finance has already created a secure view to share the aggregated data'. Which of the following steps are necessary to grant Data Insights access to the data securely while enforcing the required restrictions?

• A. Create a share object and grant USAGE privilege on the database containing the secure view to the share. Then, grant SELECT privilege on the secure view to the share. Finally, share the share with Data Insights' Snowflake account using their account identifier.
• B. Create a row access policy that restricts the rows returned based on the role used by Data Insights. Then, grant SELECT privilege on the secure view directly to the role used by Data Insights' Snowflake account.
• C. Create an external function that Data Insights can call to execute pre-approved aggregate functions on the underlying data. Grant USAGE on the function to Data Insights' role and create a secure view that uses that function.
• D. Grant SELECT privilege on the secure view directly to the role used by Data Insights' Snowflake account.
• E. Create a masking policy that only allows aggregate functions to be executed by Data Insights' role and apply it to the relevant columns in the underlying table. Then, grant SELECT privilege on the secure view directly to the role used by Data Insights' Snowflake account.

Explanation: Only visible for PassTorrent members. You can sign-up / login (it's free).