Snowflake DEA-C02 : SnowPro Advanced: Data Engineer (DEA-C02)

A Snowflake table 'ORDERS' contains billions of records and is frequently queried for reporting purposes. The reporting queries often filter on 'ORDER DATE and 'CUSTOMER ID'. The data engineering team is considering creating a clustering key to improve query performance. They are evaluating two options: (1) clustering on 'ORDER DATE' alone and (2) clustering on '(ORDER DATE, CUSTOMER ID)'. Which of the following statements best describes the trade-offs between these two options in the context of query performance and data maintenance?

• A. Neither clustering option will significantly improve performance, and the team should focus on optimizing the queries themselves through techniques like query rewriting and the use of appropriate indexes.
• B. Clustering on ' (ORDER DATE, CUSTOMER_ID)' will provide better performance for queries filtering on both columns, but may lead to increased reclustering costs if distribution is skewed within "ORDER_DATE' partitions.
• C. Clustering on '(ORDER DATE, CUSTOMER IDY is always the best option because it allows for more granular filtering and reduces the need to scan unnecessary micro-partitions, regardless of data distribution.
• D. Clustering on ORDER DATE alone is preferable because it eliminates the risk of data skewness associated with 'CUSTOMER ID, leading to more balanced micro-partitions and consistent query performance.
• E. Clustering on 'ORDER_DATE alone will result in better overall query performance because it's a single dimension, simplifying the clustering process and reducing the need for Snowflake to perform complex data scans.

You are implementing a data pipeline to load data from AWS S3 into Snowflake. The source data consists of CSV files with a header row. Some of the CSV files have inconsistent data types in a specific column (e.g., sometimes an integer, sometimes a string). You want to use the 'COPY' command to load the data and handle these data type inconsistencies gracefully. Which of the following 'COPY' command options, used in conjunction, would BEST address this issue and avoid load failures? Assume the file format is already defined to specify CSV type, header skip, and field delimiter.

• A. Option B
• B. Option C
• C. Option E
• D. Option D
• E. Option A

A large e-commerce company uses Snowflake to store website clickstream data in a table named 'WEB EVENTS'. This table is partitioned using the 'EVENT DATE column. The company needs to analyze user behavior across different devices. A common query involves joining 'WEB EVENTS' with a smaller 'USER DEVICES' table (containing user-to-device mappings) to determine the device type for each event. However, the performance of this join operation is poor, especially when filtering 'WEB EVENTS' by a specific date range. The 'USER DEVICES table is small enough to fit in memory. What is the most effective approach to optimize this query for performance?

• A. Create a materialized view that pre-joins 'WEB_EVENTS' and 'USER_DEVICES' tables without filtering
• B. Use a standard 'JOIN' operation between 'WEB_EVENTS' and USER_DEVICES' without any modifications.
• C. Convert the 'WEB EVENTS' table to use a VARIANT data type and query with JSON path expressions.
• D. Broadcast the 'USER DEVICES table to all compute nodes before performing the join. (Hint: Consider using 'BROADCAST hint)
• E. Use a 'LATERAL FLATTEN' function to process the data in parallel.

You have a Snowflake task that executes a complex stored procedure. This stored procedure performs several UPDATE statements on a large table. After enabling the 'QUERY TAG' parameter, you notice that the task history in Snowflake shows frequent suspensions due to exceeding warehouse resource limits. The warehouse is already scaled to the largest size. Which combination of the following strategies would BEST address this issue and minimize task suspensions, assuming you CANNOT further scale the warehouse?

• A. Set a lower 'SUSPEND TASK AFTER N FAILURES' value to proactively suspend the task before it consumes excessive resources.
• B. Increase the 'ERROR ON N parameter for the task to allow for more consecutive timeouts before the task is suspended.
• C. Implement a retry mechanism within the task's SQL code to automatically retry failed UPDATE statements after a short delay.
• D. Break down the stored procedure into smaller, more manageable transactions and commit changes more frequently. Consider utilizing batch processing techniques.
• E. Optimize the UPDATE statements in the stored procedure to reduce resource consumption by using techniques such as clustering keys, partitioning and avoiding full table scans.

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