Try and practice the latest Snowflake : DEA-C02 real questions & answers

Snowflake DEA-C02 Exam Overview:

Snowflake DEA-C02 Exam Syllabus Topics:

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

1. You are tasked with creating a development environment from a production database named 'PROD DB'. This database contains sensitive data, and you need to mask the data in the development environment. You decide to use cloning and a transformation function during the cloning process. What is the MOST efficient approach to clone 'PROD DB' into a development database 'DEV DB' and mask sensitive data in the process?

A) Clone 'PROD to ' DEV DB'. Export the data from 'DEV DB', transform it using a scripting language (e.g., Python), and then load the transformed data back into replacing the original data.
B) Create a clone of 'PROD named 'DEV DB'. Create stored procedures on 'DEV DB' which apply masking at the query level. Cloning databases does not preserve masking policies from the Source database
C) Create a clone of 'PROD named 'DEV DB'. Define masking policies on the columns in 'PROD DB' before cloning. These policies will be automatically applied to the cloned tables in "DEV_DB' ensuring all data is masked during query time in the DEV environment.
D) Create a clone of 'PROD named 'DEV DB'. Create a warehouse for running masking policies. Then apply masking policies to the tables in 'DEV DB' Cloning masks the underlying data directly.
E) Create a clone of 'PROD named 'DEV DB', then create views on 'DEV DB' using masking policies. Cloning the Views from 'PROD will automatically copy the masking policies.

2. You are designing a data pipeline to ingest streaming data from Kafka into Snowflake. The data contains nested JSON structures representing customer orders. You need to transform this data and load it into a flattened Snowflake table named 'ORDERS FLAT'. Given the complexities of real-time data processing and the need for custom logic to handle certain edge cases within the JSON payload, which approach provides the MOST efficient and maintainable solution for transforming and loading this streaming data into Snowflake?

A) Utilize a third-party ETL tool (like Apache Spark) to consume the data from Kafka, perform the JSON flattening and transformation logic, and then use the Snowflake connector to load the data into the 'ORDERS FLAT' table in batch mode.
B) Create a Python UDF that calls 'json.loads()' to parse the JSON within Snowflake and then use SQL commands with 'LATERAL FLATTEN' to navigate and extract the desired fields into a staging table. Afterward, use a separate SQL script to insert from staging to the final table 'ORDERS FLAT
C) Implement a custom external function (UDF) written in Java to parse and transform the JSON data before loading it into Snowflake. Configure Snowpipe to call this UDF during the data ingestion process. This UDF will flatten the JSON structure and return a tabular format directly insertable into 'ORDERS FLAT.
D) Use Snowflake's built-in JSON parsing functions within a Snowpipe COPY INTO statement, combined with a 'CREATE VIEW' statement on top of the loaded data. The view will use 'LATERAL FLATTEN' to present the data in the desired flattened structure without physically transforming the underlying data.
E) Use Snowflake's Snowpipe with a COPY INTO statement that utilizes the 'STRIP OUTER ARRAY option to handle the JSON array, combined with a series of SQL queries with 'LATERAL FLATTEN' functions to extract the nested data after loading into a VARIANT column.

3. You are implementing a data share between two Snowflake accounts. The provider account wants to grant the consumer account access to a function that returns anonymized customer data based on a complex algorithm. The provider wants to ensure that the consumer cannot see the underlying implementation details of the anonymization algorithm. Which of the following approaches can achieve this goal? (Select TWO)

A) Create a secure UDF in the provider account and grant usage on the secure UDF to the share. Share the share with the consumer account.
B) Create a standard UDF in the provider account and grant usage on the UDF to the share. Share the share with the consumer account.
C) Share the underlying table and provide the consumer account with the anonymization algorithm separately.
D) Create a view that calls the secure UDF and share that view with the consumer account.
E) Create an external function in the provider account and grant usage to the share. Share the share with the consumer account.

4. A data engineer is tasked with creating a Listing to share a large dataset stored in Snowflake. The dataset contains sensitive Personally Identifiable Information (PII) that must be masked for certain consumer roles. The data engineer wants to use Snowflake's dynamic data masking policies within the Listing to achieve this. Which of the following approaches is the MOST secure and maintainable way to implement this requirement, assuming that the consumer roles are pre-defined and known?

A) Implement an external function that masks the data based on the consumer's role and share this function in the Listing. Use this external function in a view shared through the listing.
B) Create a view that applies conditional masking using 'CASE' statements based on the function and share the view in the Listing.
C) Apply dynamic data masking policies directly to the base tables containing the PII and share these tables in the Listing. Policies should use the function to determine when to mask the data.
D) Create multiple versions of the shared tables, each with different masking applied. The data engineer must manually manage which version each consumer can access.

5. You are working with a Snowflake table 'customer_data' which contains customer information stored in a VARIANT column named raw_info'. The 'raw_info' JSON structure includes nested addresses, and preferences. Your task is to extract the city from the first address in the 'addresses' array, and the customer's preferred communication method from the 'preferences' object. Some customers might not have addresses or preferences defined. Select the two SQL snippets that correctly and efficiently extract this data, handling missing fields gracefully and providing appropriate type casting. Address array is in the format 'addresses: [ { 'city': '...', 'state': ' '},

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

Solutions: