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

1. A critical database, 'PRODUCTION DB', in your Snowflake account was accidentally dropped. You need to restore it as quickly as possible, but you're unsure if Time Travel retention is sufficient. Which method guarantees restoration of the database even if it falls outside the Time Travel window?

A) Utilize the data cloning feature: 'CREATE DATABASE CLONE PRODUCTION_DB BEFORE (STATEMENT 'DROP DATABASE PRODUCTION_DB');'
B) Restore from a Snowflake-managed backup using the 'CREATE DATABASE ... FROM BACKUP' command. Specify the timestamp before the drop occurred.
C) Fail-safe cannot be directly accessed by the user for restoration purposes; it is only used by Snowflake Support in extreme disaster recovery scenarios.
D) Use the 'UNDROP DATABASE PRODUCTION command.
E) Contact Snowflake Support and request restoration from Fail-safe.

2. You are setting up a Kafka connector to load data from a Kafka topic into a Snowflake table. You want to use Snowflake's automatic schema evolution feature to handle potential schema changes in the Kafka topic. Which of the following is the correct approach to enable and configure automatic schema evolution using the Kafka Connector for Snowflake?

A) Set the 'snowflake.data.field.name' property to the name of the column in the Snowflake table where the JSON data will be stored as a VARIANT, and set 'snowflake.enable.schematization' to 'true'.
B) Set the 'value.converter.schemas.enable' to 'true' and provide Avro schemas and also, configure the Snowflake table with appropriate data types for each field. Schema Evolution is not supported by the Kafka Connector for Snowflake.
C) Set the property to 'true' and the 'snowflake.ingest.stage' to an existing stage.
D) Automatic schema evolution is not directly supported by the Kafka Connector for Snowflake. You must manually manage schema changes in Snowflake.
E) Set 'snowflake.ingest.file.name' to an existing file in a stage.

3. You have a directory table 'my_directory_table' pointing to a stage containing CSV files with headers. You need to query the directory table to find all files modified in the last 24 hours and load those CSV files using COPY INTO into a target table Assume the target table exists and has appropriate schema'. Which of the following SQL statements, or set of statements, will accomplish this efficiently? Note: Consider efficient file loading.

A)

B)

C)

D)

E)

4. 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 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.
C) 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.
D) 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.
E) 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.

5. You're designing a data masking solution for a 'CUSTOMER' table with columns like 'CUSTOMER ID', 'NAME', 'EMAIL', and 'PHONE NUMBER. You want to implement the following requirements: 1. The 'SUPPORT' role should be able to see the last four digits of the 'PHONE NUMBER and a hashed version of the 'EMAIL'. 2. The 'MARKETING' role should be able to see the full 'NAME' and a domain-only version of the 'EMAIL' (everything after the '@' symbol). 3. All other roles should see masked values for 'EMAIL' and 'PHONE NUMBER. Which of the following masking policy definitions BEST achieves these requirements using Snowflake's built-in functions and RBAC?

A)

B)

C)

D)

E)

Solutions: