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) 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.
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) 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.

2. A healthcare provider stores patient data in Snowflake, including 'PATIENT ID', 'NAME, 'MEDICAL HISTORY , and 'INSURANCE ID. They need to comply with HIPAA regulations. As a data engineer, you need to ensure that PHI (Protected Health Information) is masked appropriately based on user roles. Which of the following steps are NECESSARY to achieve this using Snowflake's data masking features and RBAC? (Select all that apply)

A) Grant the 'OWNERSHIP privilege on the 'PATIENT table to the 'ACCOUNTADMIN' role, ensuring complete control and management of the data by the administrator.
B) Enforce multi-factor authentication (MFA) for all users accessing the Snowflake environment to enhance security and prevent unauthorized access to sensitive data.
C) Create custom roles representing different user groups within the organization (e.g., 'DOCTOR, 'NURSE, 'ADMIN') and grant them the necessary privileges to access the data, including 'SELECT on the tables and views containing patient data.
D) Identify the columns containing PHI and create appropriate masking policies for each column (e.g., masking 'NAME, 'MEDICAL HISTORY, INSURANCE_ID).
E) Apply the created masking policies to the corresponding columns in the patient data tables, ensuring that the masking policies are designed to reveal only the necessary information based on the user's role (e.g., doctors see full medical history, nurses see limited medical history, admins see de-identified data).

3. You are developing a data pipeline that uses Snowpipe Streaming to ingest JSON data into a Snowflake table. Some JSON documents contain nested arrays and complex structures. You need to flatten the JSON structure during ingestion to simplify querying. Consider the following JSON document: { "order id": 123, "customer": { "id": "cust123", "name": "John Doe", "address": { "street": "123 Main St", "city": "Anytown" } }, "items": [ {"product_id": "prodl", "quantity": 2}, {"product_id": "prod2", "quantity": 1} ] } Which approach would you use within the 'COPY INTO' statement of your Snowpipe to flatten this JSON structure during ingestion?

A) Create a separate transformation pipeline using Snowflake Tasks to flatten the data after it is ingested into the table.
B) Snowpipe and the 'COPY INTO' command automatically flattens JSON data during ingestion; no additional steps are required.
C) Pre-process the JSON documents before loading them into the stage using a custom script to flatten the structure.
D) Use the ' FLATTEN()' table function directly within the 'COPY INTO' statement to expand the 'items' array and extract nested fields. For nested objects, use dot notation directly in the SELECT list (e.g., 'customer.name').
E) Use JavaScript UDFs within the 'COPY INTO' statement to recursively flatten the JSON structure.

4. A retail company wants to store product data in a Snowflake VARIANT column. The product data is currently in a relational table called 'PRODUCTS' with columns 'PRODUCT ID', 'PRODUCT NAME, 'CATEGORY, 'PRICE, and 'DISCOUNT. They want to create a JSON structure where each product is represented as a JSON object, and the entire result set is a JSON array. Which of the following SQL statements will achieve this transformation most efficiently?

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

5. You have a Snowflake table, 'CUSTOMER ORDERS', with columns like 'CUSTOMER ID', 'ORDER DATE', 'ORDER AMOUNT', and 'REGION'. A Bl dashboard relies on a query that aggregates data from this table, but the query performance is consistently slow. The query frequently filters by 'ORDER DATE and groups by 'REGION'. Based on the following 'EXPLAIN' output, which combination of techniques should be considered to improve the performance the most?

A) Redesign the dashboard to minimize the data being displayed at once to the user.
B) Increase the virtual warehouse size to 'LARGE or 'XLARGE.
C) Create an index on the 'ORDER DATE column.
D) Cluster the 'CUSTOMER ORDERS table on 'ORDER DATE' and 'REGION'.
E) Create a materialized view that pre-aggregates the data by 'ORDER DATE and 'REGION'

Solutions: