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

You need to define a UDF in Snowflake that takes a date as input and returns the next business day (Monday-Friday). If the input date is a Friday, the UDF should return the following Monday. If the input date is a Saturday or Sunday, the function should return the following Monday as well. Which of the following UDF definitions correctly implements this logic?

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

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. Contact Snowflake support and request them to restore the table from a system-level backup.
• B. 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.
• C. 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.
• D. Create a new table 'ORDERS' and manually re-insert the data from the application's logs and backups.
• E. Restore the entire Snowflake account to a previous point in time before the table was truncated.

A healthcare provider wants to share patient data with a research organization, but must ensure that researchers only have access to records from a specific region ('REGION A') and only see anonymized data'. You have a 'patients' table with columns 'patient_id' , 'region', 'dob', 'medical history', and 'ssn'. Which of the following steps would be MOST effective and secure for implementing row- level filtering and data masking for this data sharing scenario, minimizing administrative overhead and maximizing query performance?

• A. Create a secure VIEW on the 'patients' table with a WHERE clause filtering by 'region = 'REGION_A" and use masking policies to redact sensitive fields. Grant access to the secure view to the research organization's role.
• B. Create a policy tag on the 'region' column restricting access to 'REGION_A' and apply masking policies directly on the 'patients' table. Grant access to the patientS table to the research organization's role.
• C. Create a VIEW on the 'patients' table with a WHERE clause filtering by 'region = and use masking policies to redact sensitive fields. Grant access to the view to the research organization's role.
• D. Create a stored procedure that filters the 'patients' table based on the region and applies masking policies. The stored procedure writes the filtered and masked data to a new table. Grant access to the new table to the research organization's role.
• E. Create a standard VIEW on the 'patients' table with a WHERE clause filtering by 'region = 'REGION_A". Create a separate table containing only the anonymized data for 'REGION_A' and grant access to this separate table to the research organization's role.

You are designing a data pipeline using Snowpipe to ingest data from multiple S3 buckets into a single Snowflake table. Each S3 bucket represents a different data source and contains files in JSON format. You want to use Snowpipe's auto-ingest feature and a single Snowpipe object for all buckets to simplify management and reduce overhead. However, each data source has a different JSON schem a. How can you best achieve this goal while ensuring data is loaded correctly and efficiently into the target table?

• A. Create a separate Snowpipe for each S3 bucket. Although this creates more Snowpipe objects, it allows you to specify a different FILE FORMAT and transformation logic for each data source.
• B. Use a single Snowpipe with a generic FILE FORMAT that can handle all possible JSON schemas. Implement a VIEW on top of the target table to transform and restructure the data based on the source bucket.
• C. Use a single Snowpipe and leverage Snowflake's VARIANT data type to store the raw JSON data. Create separate external tables, each pointing to a specific S3 bucket, and use SQL queries to transform and load the data into the target table.
• D. Since Snowpipe cannot handle multiple schemas with a single pipe, pre-process the data in S3 using an AWS Lambda function to transform all files into a common schema before they are ingested by the Snowpipe.
• E. Use a single Snowpipe and leverage Snowflake's ability to call a user-defined function (UDF) within the 'COPY INTO' statement to transform the data based on the S3 bucket path. The UDF can parse the bucket path and apply the appropriate JSON schema transformation.

A data pipeline ingests clickstream data from various sources into a raw Snowflake table CRAW CLICKS). A transformation job then processes this data and loads it into a more structured 'CLICK EVENTS table, performing filtering, cleaning, and data enrichment. The data engineering team notices significant performance bottlenecks during this transformation process, leading to data freshness issues.
The team wants to optimize this process, considering the following:

• A. Create a materialized view on top of 'RAW CLICKS' that pre-computes the necessary transformations and aggregations, allowing the 'CLICK EVENTS' table to be populated directly from the materialized view.
• B. Replace the transformation job with a series of smaller, more specialized jobs, each running on a separate virtual warehouse optimized for the specific task, and orchestrate these jobs using a data pipeline tool.
• C. Implement a change data capture (CDC) mechanism on the source systems to only ingest changed data into 'RAW CLICKS, reducing the overall data volume and the amount of data processed by the transformation job.
• D. Use a larger virtual warehouse for the transformation job and partition the 'RAW CLICKS table on the ingestion timestamp to improve data pruning and reduce the amount of data processed during the transformation.
• E. Optimize the transformation queries by identifying and rewriting inefficient SQL patterns, ensuring appropriate use of joins, filtering conditions, and data type conversions.