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

1. You are tasked with creating a SQL UDF in Snowflake to mask sensitive customer data (email addresses) before it's used in a reporting dashboard. The masking should replace all characters before the '@' symbol with asterisks, preserving the domain part. For example, '[email protected]' should become ' @example.com'. Which of the following SQL UDF definitions correctly implements this masking logic, while minimizing the impact on Snowflake compute resources?

A) Option E
B) Option D
C) Option B
D) Option A
E) Option C

2. You are tasked with building a Snowpipe to ingest JSON data from an AWS S3 bucket into a Snowflake table named 'SALES DATA'. The data is landing in the bucket frequently, and you want to use Snowpipe's auto-ingest feature. However, you are observing significant latency in data appearing in your Snowflake table after it lands in S3, despite verifying that S3 event notifications are correctly configured and the Snowflake event queue is receiving them. You've already checked that the pipe is enabled and has the necessary permissions. The Snowflake Pipe definition is as follows:

What is the MOST LIKELY reason for this delay, and what steps can you take to further troubleshoot?

A) Snowflake's internal metadata cache is out of sync. Run 'ALTER PIPE SALES PIPE to refresh the cache.
B) Snowpipe auto-ingest only supports CSV files. Convert your JSON data to CSV format before loading.
C) There is a backlog of files in the internal Snowflake queue waiting to be processed. Monitor the 'SYSTEM$PIPE STATUS' function and consider increasing the 'MAX CONCURRENCY' parameter (if applicable, based on underlying infrastructure considerations) on the pipe definition.
D) The S3 bucket is not in the same region as the Snowflake account. Ensure the S3 bucket and Snowflake account are in the same region to reduce network latency.
E) The Snowflake virtual warehouse associated with the pipe is undersized. Increase the warehouse size to improve ingestion performance.

3. You are developing a data pipeline that extracts data from an on-premise PostgreSQL database, transforms it, and loads it into Snowflake. You want to use the Snowflake Python connector in conjunction with a secure method for accessing the PostgreSQL database. Which of the following approaches provides the MOST secure and manageable way to handle the PostgreSQL connection credentials in your Python script when deploying to a production environment?

A) Prompt the user for the PostgreSQL username and password each time the script is executed.
B) Store the PostgreSQL username and password in a configuration file (e.g., JSON or YAML) and load the file in the Python script.
C) Store the PostgreSQL username and password in environment variables and retrieve them in the Python script using 'os.environ'
D) Hardcode the PostgreSQL username and password directly into the Python script.
E) Store the PostgreSQL username and password in a dedicated secrets management service (e.g., AWS Secrets Manager, HashiCorp Vault, Azure Key Vault) and retrieve them in the Python script using the appropriate API.

4. You are tasked with implementing data masking on a 'CUSTOMER' table. The requirement is to mask the 'EMAIL' column for all users except those with the 'DATA ADMIN' role. You have the following code snippet. What is wrong with it?

A) There is no code provided, so there is nothing wrong with it.
B) Without masking poliy code, it's impossible to determine if there is anything wrong.
C) The masking policy is applied to the wrong column. It should be applied to the ID column, not the EMAIL column.
D) The masking policy syntax is incorrect. It should use 'CASE WHEN IS_ROLE_IN_SESSION('DATA_ADMIN') THEN EMAIL ELSE '[email protected]' END'.
E) The WITH clause is unneccessary.

5. You have configured a Kafka Connector to load JSON data into a Snowflake table named 'ORDERS. The JSON data contains nested structures. However, Snowflake is only receiving the top- level fields, and the nested fields are being ignored. Which configuration option within the Kafka Connector needs to be adjusted to correctly flatten and load the nested JSON data into Snowflake?

A) Use the 'transforms' configuration with the 'org.apache.kafka.connect.transforms.ExtractField$Value' transformation to extract specific fields.
B) Apply the 'org.apache.kafka.connect.transforms.Flatten' transformation to the 'transforms' configuration.
C) Set the 'value.converter.schemas.enable' property to 'true'.
D) Configure the 'snowflake.data.field.name' property to specify the column in the Snowflake table where the entire JSON should be loaded as a VARIANT.
E) Enable the 'snowflake.ingest.stage' property and set it to a Snowflake internal stage.

Solutions: