Snowflake DEA-C02 Exam Syllabus Topics:

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

1. You are configuring a Snowflake Data Clean Room for two healthcare providers, 'ProviderA' and 'ProviderB', to analyze patient overlap without revealing Personally Identifiable Information (PII). Both providers have patient data in their respective Snowflake accounts, including a 'PATIENT ID' column that uniquely identifies each patient. You need to create a secure join that allows the providers to determine the number of shared patients while protecting the raw 'PATIENT ID' values. Which of the following approaches is the most secure and efficient way to achieve this using Snowflake features? Select TWO options.

A) Utilize Snowflake's Secure Aggregate functions (e.g., APPROX_COUNT_DISTINCT) on the 'PATIENT_ID' column without sharing the underlying data. Each provider calculates the approximate distinct count of patient IDs, and the results are compared to estimate the overlap.
B) Implement tokenization of the 'PATIENT_ID' column in both ProviderA's and ProviderB's accounts. Share the tokenized values through a secure view and perform a JOIN operation on the tokens. Use a third party to deanonymize the tokens afterwards.
C) Create a hash of the 'PATIENT_ID' column in both ProviderA's and ProviderB's accounts using a consistent hashing algorithm (e.g., SHA256) and a secret salt known only to both providers. Share the hashed values through a secure view and perform a JOIN operation on the hashed values.
D) Share the raw 'PATIENT_ID' columns between ProviderA and ProviderB using secure data sharing, and then perform a JOIN operation in either ProviderA's or ProviderB's account.
E) Leverage Snowflake's differential privacy features to add noise to the patient ID data, share the modified dataset and perform a JOIN.

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

3. You have a table named 'EMPLOYEES with a retention period of 1 day. You accidentally deleted several important rows from this table, but you need to recover the data'. You know the deletion occurred 25 hours ago. What actions should be taken to attempt to recover the deleted data, and what outcome can you expect? Assume you are working in an Enterprise edition of Snowflake account.

A) Attempt to use Time Travel to query the table before the deletion and re-insert the deleted rows. Expect the recovery to be successful as long as the deletion occurred within the data retention period.
B) Since its Enterprise edition of Snowflake account, the Time travel and cloning will work with 7 days retention period, hence attemtp clone table using Time Travel and recover data successfully
C) Attempt to clone the table using Time Travel to a point in time before the deletion, then extract the deleted rows. Expect the recovery to be successful as long as the deletion occurred within the data retention period.
D) Attempt to use UNDROP TABLE command if the table was dropped. Expect the recovery to be successful as long as the deletion occurred within the data retention period.
E) Attempt to use Time Travel or cloning to recover the data. Expect the recovery to fail because the deletion occurred outside the I-day data retention period.

4. You have created a Snowflake Iceberg table that points to data in an AWS S3 bucket. After some initial data ingestion, you realize that the schema in the Iceberg table does not perfectly match the schema of the underlying Parquet files in S3. Specifically, one of the columns in the Iceberg table is defined as 'VARCHAR , while the corresponding column in the Parquet files is stored as 'INT. What will be the most likely behavior when you query this Iceberg table in Snowflake?

A) The query will succeed, but the 'VARCHAR column will contain 'NULL' values for all rows where the underlying Parquet files contain 'INT' values.
B) The query will fail with an error indicating a data type mismatch between the Iceberg table schema and the underlying Parquet file schema.
C) Snowflake will automatically cast the SINT' data in the Parquet files to 'VARCHAR during query execution, and the query will succeed without any errors or warnings.
D) The query will succeed, but the result will be unpredictable and may vary depending on the specific data values in the Parquet files.
E) Snowflake will attempt to cast the data, and if a cast fails (e.g., 'INT' value is too large to fit in 'VARCHAR), the query will return an error only for those specific rows. Other rows will be processed correctly.

5. You are working on a Snowpark Python application that needs to process a stream of data from Kafka, perform real-time aggregations, and store the results in a Snowflake table. The data stream is highly variable, with occasional spikes in traffic that overwhelm your current Snowpark setup, leading to significant latency in processing. Which of the following strategies, either individually or in combination, would be MOST effective to handle these traffic spikes and ensure near real-time processing?

A) Configure the Snowflake warehouse used by your Snowpark application to use auto-suspend and auto-resume with a short auto-suspend time to minimize costs during periods of low traffic.
B) Use Snowpark's async actions (e.g., to offload data processing to separate threads or processes, allowing your main Snowpark application to continue receiving data.
C) Implement a message queuing system (e.g., RabbitMQ, Kafka) between Kafka and your Snowpark application to buffer incoming data during traffic spikes.
D) Use 'CACHE RESULT for all queries in snowpark that use Kafka
E) Implement dynamic warehouse scaling. Utilize Snowflake's Resource Monitors and the ability to programmatically resize warehouses through Snowpark. Monitor the queue depth or latency of your Snowpark application, and dynamically scale up the warehouse size when thresholds are exceeded. Then, scale it back down when traffic subsides.

Solutions: