Online Access Free DEA-C02 Exam Questions

A data engineer accidentally truncated a critical table 'ORDERS' in the 'SALES DB' database. The table contained important historical order data, and the data retention period is set to the default. Which of the following options represents the MOST efficient and reliable way to recover the truncated table and its data, minimizing downtime and potential data loss?

• A.Create a new table 'ORDERS' and manually re-insert the data from the application's logs and backups.
• B.Use the UNDROP TABLE command to restore the table. If UNDROP fails, clone the entire SALES_DB database to a point in time before the truncation using Time Travel.
• C.Use Time Travel to create a clone of the truncated table from a point in time before the truncation. Then, swap the original table with the cloned table.
• D.Contact Snowflake support and request them to restore the table from a system-level backup.
• E.Restore the entire Snowflake account to a previous point in time before the table was truncated.

You have an external table named in Snowflake that points to a set of CSV files in an AWS S3 bucket. The CSV files have a header row, and the data is comma-separated. However, some of the files in the S3 bucket are gzipped. You need to define the external table to correctly read both compressed and uncompressed files. Which of the following SQL statements BEST achieves this?

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

You are designing a Snowpipe pipeline to ingest data from an AWS SQS queue. The queue contains notifications about new files arriving in an S3 bucket. However, due to network issues, some notifications are delayed, causing Snowpipe to potentially miss files. Which of the following strategies, when combined, will BEST address the problem of delayed notifications and ensure data completeness?

• A.Set 'MAX FILE_AGE to 'DEFAULT' and utilize the 'SYSTEM$PIPE FORCE RESUME' procedure in conjunction with a separate process that lists the S3 bucket and compares it to the files already loaded in Snowflake, loading any missing files.
• B.Increase the 'MAX FILE AGE parameter in the Snowpipe definition and implement a periodic 'ALTER PIPE ... REFRESH' command.
• C.Implement a Lambda function that triggers the 'SYSTEM$PIPE FORCE RESUME procedure after a certain delay.
• D.Use 'VALIDATE()' function periodically to identify files that have not been loaded and trigger manual data loads for missing data.
• E.Configure the SQS queue with a longer retention period and implement an event bridge rule with a retry policy to resend notifications.

You are tasked with creating a UDTF in Snowflake to perform a complex data transformation that requires external libraries (e.g., for advanced string manipulation or data analysis). The transformation involves cleaning and standardizing addresses from a table containing millions of customer records. Which language and approach would be most appropriate and efficient for this scenario?

• A.Scala UDTF leveraging sbt to manage dependencies to achieve address parsing and standardization.
• B.SQL UDF with nested CASE statements for address standardization.
• C.JavaScript UDF utilizing regular expressions for simple string replacements.
• D.Python UDTF leveraging Anaconda packages (e.g., 'addressparser' , 'pandas') for advanced address parsing and standardization, utilizing Snowflake's optimized execution environment for Python.
• E.Java UDTF with necessary JAR files uploaded to Snowflake's internal stage, leveraging external libraries for address parsing and standardization.

You are developing a data pipeline in Snowflake that processes sensitive customer data'. You need to implement robust data governance controls, including column-level security and data masking. Which of the following combinations of Snowflake features, when used together, provides the MOST comprehensive solution for achieving this?

• A.Data masking policies and network policies.
• B.Object tagging, column-level security policies (using views), and masking policies.
• C.Row access policies and data masking policies on base tables, supplemented with object tagging and column-level security policies on views that grant limited access to specific user roles.
• D.Dynamic tables and masking policies.
• E.Row-level security policies and data masking policies.