Instantly download DEA-C02 updated real questions

Snowflake DEA-C02 Exam Overview:

Snowflake DEA-C02 Exam Syllabus Topics:

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

1. 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) 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.
B) 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.
C) Optimize the transformation queries by identifying and rewriting inefficient SQL patterns, ensuring appropriate use of joins, filtering conditions, and data type conversions.
D) 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.
E) 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.

2. You have a Python UDF in Snowflake designed to enrich customer data by calling an external API to retrieve additional information based on the customer ID. Due to API rate limits, you need to implement a mechanism to cache API responses within the UDF to avoid exceeding the limits. The UDF is defined as follows:

Which caching mechanism can be implemented MOST effectively WITHIN the Python UDF to minimize API calls while adhering to Snowflake's UDF limitations?

A) Persist the API responses in a temporary table within Snowflake. The UDF will first query the temporary table for the customer ID; if found, return the cached data. Otherwise, call the API and store the response in the temporary table for future use.
B) Leverage external caching services like Redis by making API calls to Redis from the UDF to store and retrieve cached API responses. This would require configuring Snowflake to connect with external systems.
C) Create a global dictionary within the UDF to store the API responses, using the customer ID as the key. Before calling the API, check if the customer ID exists in the dictionary; if it does, return the cached response. This approach will keep cached values during the session.
D) Utilize Snowflake's built-in caching mechanisms (result caching) by ensuring the UDF is deterministic and only depends on its input parameters. Snowflake will automatically cache the results of the UDF for subsequent calls with the same input.
E) Use the 'functools.lru_cache' decorator to cache the results of the 'get_customer details' function within the UDF's scope. This will automatically cache the most recently used API responses.

3. You are building a data pipeline that utilizes a Snowflake stage to store intermediate results. You need to ensure data security and compliance. Which of the following methods offer the BEST approach for securing data stored in a Snowflake stage?

A) Apply masking policies to the columns in the tables that are loaded from the stage. This ensures sensitive data is masked before it reaches the target tables.
B) Utilize network policies to restrict access to the stage based on IP address or network identifier. Only authorized IP addresses should be able to interact with the stage.
C) Configure the stage to use temporary storage, which automatically deletes the data after a specified retention period.
D) Encrypt the data at rest on the storage layer using Snowflake's built-in encryption features. Snowflake automatically encrypts all data at rest.
E) Encrypt the data client-side before uploading it to the stage and decrypt it after loading it into Snowflake. This provides an additional layer of security.

4. You have a table 'SALES DATA' in your production environment. You want to create a development environment using cloning, but only want to include data up to a specific point in time to minimize storage costs and potential exposure of recent, sensitive data'. You know there were significant changes to the 'SALES DATA' table structure on '2024-01-15'. Your goal is to create a clone that only includes the structure as of '2024-01-14'. Which Snowflake command is MOST appropriate for this scenario?

A)

B)

C)

D)

E)

5. Consider a scenario where you have a Snowflake table named 'CUSTOMER DATA' containing customer IDs (INTEGER) and encrypted credit card numbers (VARCHAR). You need to create a secure JavaScript UDF to decrypt these credit card numbers using a custom encryption key stored securely within Snowflake's internal stage, and then mask all but the last four digits of the decrypted number for data protection. Which of the following actions are necessary to ensure both functionality and security while adhering to Snowflake's best practices for UDF development and security?

A) Pass the encryption key as an argument to the UDF each time it is called.
B) Encrypt the key using a weaker encryption algorithm before storing it in an internal stage to balance security and performance.
C) Store the encryption key directly within the JavaScript UDF code as a string variable.
D) Store the encryption key in a separate file on an internal stage accessible only by the UDF's service account and load the key from the file within the UDF at runtime.
E) Use Snowflake's Secure Vault (Secret) feature to store the encryption key and retrieve it securely within the UDF.

Solutions: