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

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 C
• B. Option E
• C. Option B
• D. Option A
• E. Option D

You are managing a Snowflake environment where data retention is set to the default 1 day for all databases and tables. You need to clone a production table, 'CUSTOMER DATA, to a development environment to test some complex transformations. However, after cloning, you realize that the original 'CUSTOMER DATA' table in production was accidentally dropped 2 days ago. Which of the following statements accurately describe the situation and what can be done, if anything?

• A. The cloned 'CUSTOMER DATA' table in the development environment will inherit the data retention period from the original 'CUSTOMER DATA' table, so it will also be inaccessible after 1 day if dropped in development.
• B. The cloned 'CUSTOMER DATA' table in the development environment will remain accessible because the clone operation created a point-in-time snapshot.
• C. The cloned 'CUSTOMER DATA' table will only remain accessible if the source database was also cloned at the same time. If only the table was cloned, the cloned table is unusable after 1 day.
• D. While the source 'CUSTOMER DATA' is dropped and outside the retention period, the clone created will remain available and fully functional for querying. It is an independent copy. The clone is usable indefinitely, within the limits of your Snowflake account storage.
• E. The cloned 'CUSTOMER DATA' table in the development environment will no longer be accessible because the source table has been dropped and the data retention period has expired. The clone is effectively useless.

You are tasked with implementing a data loading process for a table 'CUSTOMER DATA' in Snowflake. The source data is in Parquet format on Azure Blob Storage and contains personally identifiable information (PII). You must ensure that the data is loaded securely, masked during the loading process, and that only authorized users can access the unmasked data after the load. Assume you have already created a stage pointing to the Azure Blob Storage. Which of the following steps should you take to achieve this?

• A. Load the data directly into a 'VARIANT column. Use a SQL transformation with 'FLATTEN' and masking policies on the extracted columns.
• B. Use a 'COPY command with 'ON ERROR = SKIP FILE'. Use a Task to monitor load failures and trigger alerts.
• C. Use a 'COPY command with the 'ENCRYPTION = (TYPE = 'AZURE CSE', KEY = option to encrypt the data during load. Implement role-based access control to restrict access to the table.
• D. Load the data without masking. Implement dynamic data masking policies on the table's PII columns using Snowflake's Enterprise edition features. Use a 'COPY' command with ERROR = CONTINUE
• E. Use a 'COPY command with the 'TRANSFORM' clause and JavaScript UDFs to mask the PII data during the load process. Implement masking policies on the 'CUSTOMER DATA' table to restrict access to the unmasked data.

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. Increase the value of the parameter. This allows the warehouse to scale up further if needed.
• B. Reduce the number of columns selected in the query, only selecting those that are essential for the aggregation.
• C. Increase the virtual warehouse size. This will provide more memory for the query to execute.
• D. 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.
• E. Rewrite the query to use window functions instead of aggregate functions.

You are designing a system to monitor data access patterns in Snowflake. You want to capture detailed information about all queries executed, including the user, query text, execution time, and any potential data access violations based on security policies. Which of the following approaches, used in combination, would provide the MOST comprehensive and scalable solution for this monitoring requirement? (Select TWO)

• A. Implement Snowflake's Event Tables and configure them to capture security-related events, such as data access policy violations.
• B. Enable query tagging and insert custom tags into each SQL statement indicating sensitive data access. Then, query 'QUERY HISTORY filtering on these tags.
• C. Enable the 'QUERY_HISTORY view in the 'ACCOUNT_USAGE' schema and periodically query it using a scheduled task.
• D. Create a stored procedure to intercept all SQL commands before execution, log them, and then execute them using 'EXECUTE IMMEDIAT
• E. Configure the 'SNOWFLAKE database's audit logs and stream them to an external security information and event management (SIEM) system.