Online Access Free DEA-C02 Practice Test

You have a base table 'ORDERS' with columns 'ORDER ID, 'CUSTOMER D', 'ORDER DATE, and 'ORDER AMOUNT'. You need to create a view that aggregates the total order amount per customer per month. However, for data governance purposes, you need to ensure that the view only shows data for the last 3 months. What is the MOST efficient and secure way to create this view in Snowflake?

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

A retail company wants to store product data in a Snowflake VARIANT column. The product data is currently in a relational table called 'PRODUCTS' with columns 'PRODUCT ID', 'PRODUCT NAME, 'CATEGORY, 'PRICE, and 'DISCOUNT. They want to create a JSON structure where each product is represented as a JSON object, and the entire result set is a JSON array. Which of the following SQL statements will achieve this transformation most efficiently?

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

Your company is implementing data governance policies in Snowflake and wants to automatically classify data to track Personally Identifiable Information (PII). You have defined a classification policy with a tag 'PII' and associated tag values like 'Email', 'CreditCard', and 'SSN'. You want to monitor the usage of PII data'. Which of the following approaches is the MOST efficient way to track access and modifications to columns tagged with the 'PII' tag and any of its tag values?

• A.Implement a custom UDF that intercepts all queries. Inside the UDF, check if any of the accessed tables or columns are tagged with 'PII', and log those queries into a separate logging table for monitoring.
• B.Create a scheduled task to query the INFORMATION SCHEMCOLUMNS view and filter based on the TAG DATABASE, TAG SCHEMA, and TAG NAME columns to identify PII columns, then query the QUERY_HISTORY view and filter based on the identified column names.
• C.Use Snowflake's native data governance capabilities in conjunction with Snowflake Horizon to actively monitor data quality and access patterns, specifically focusing on PII-tagged columns.
• D.Create a masking policy that redacts all PII data. Monitor access attempts via the ACCESS HISTORY view. Although it hinders data usability, this approach guarantees no PII data leakage and allows easy monitoring.
• E.Enable Snowflake's data governance features, including object tagging and data classification. Then, leverage the ACCESS HISTORY view, filtering based on POLICY _ TAGS. This will show all accesses to data tagged as PII.

You are building a data pipeline using Snowflake Tasks to orchestrate a series of transformations. One of the tasks, 'task _ transform data', depends on the successful completion of another task, 'task extract_data'. However, occasionally fails due to transient network issues. You want to implement a retry mechanism for 'task_extract data' without impacting the overall pipeline execution time significantly. Which of the following approaches is the most appropriate and efficient way to achieve this within the Snowflake Task framework?

• A.Modify the task definition to call a stored procedure. The stored procedure implements a loop with a retry counter. Inside the loop, execute the data extraction logic. If an error occurs, catch the exception, wait for a few seconds, and retry the extraction. After a specified number of retries, raise an exception to signal task failure.
• B.Create a new root-level task that checks the status of 'task_extract_data'. If it failed, the root-level task will execute a copy of the 'task_extract data' task. After this, it updates the 'task_transform_data"s 'AFTER' condition to depend on the new task that retries extraction.
• C.Implement a TRY...CATCH block within the task definition to catch any exceptions. Inside the CATCH block, use SYSTEM$WAIT to pause for a few seconds, then re- execute the core logic of the task. Repeat this process a limited number of times before failing the task permanently.
• D.Use the 'AFTER keyword in the 'CREATE TASK' statement for 'task_transform_data' to only execute if succeeds on its first attempt. If fails, the entire pipeline will stop, ensuring data consistency.
• E.Configure the task with an error notification integration that sends alerts upon failure. Manually monitor these alerts and manually resume the task if it fails. Use 'ALTER TASK task extract data RESUME;'

You are developing a Snowpark Python application that transforms data from a source table "ORDERS RAW' into a target table 'ORDERS CLEANED'. The transformation involves multiple steps, including data validation, cleansing, and aggregation. You need to ensure that either all steps succeed, or none of them do, to maintain data integrity. You are considering different approaches to transaction management. Which of the following strategies offer the MOST comprehensive and reliable approach to manage transactions in this scenario, especially considering potential network interruptions and session timeouts?

• A.Explicitly start a transaction using 'session.autocommit = False' and manually commit the transaction using 'session.commit()' at the end of the process. Catch any exceptions and call 'session.rollback()' in case of an error. Implement retry logic for network interruptions.
• B.Rely on Snowpark's auto-commit mode and assume that each operation is atomic. If an error occurs, manually revert the changes by deleting any partially processed data.
• C.Disable autocommit using 'session.autocommit = False' , perform all transformations, and then use 'session.commit()' at the end. If a network interruption occurs, the transaction will automatically be rolled back by Snowflake.
• D.Implement a custom transaction management system using temporary tables. Write intermediate results to temporary tables and only copy the data to the target table if all steps succeed. Drop the temporary tables if an error occurs.
• E.Leverage the 'with session.transaction_context() as transaction:' statement in Python (Snowpark 2.x or later). Inside the block perform all data transformations. If an exception is raised the context manager will automatically rollback the transaction, otherwise it will commit.