DEA-C02 Data Transformation — Timed 50 Questions

Question 1

A data engineer is designing a pipeline using Snowflake Streams and Tasks to process CDC data. Which TWO characteristics correctly describe the behavior of a standard table stream when consumed by a DML statement? (Choose TWO)

50 minute time limit — choose an answer to begin.

Accepted Answer

The stream offset advances only when the DML transaction is successfully committed.. Understanding stream behavior is critical for building reliable data pipelines. A stream tracks changes to a source table by using an offset. When a DML statement consumes a stream within a transaction, the stream's offset advances only upon a successful commit. This ensures 'exactly-once' processing semantics, which prevents data loss or duplication during the transformation and ingestion phases.

Answer

The stream is automatically dropped after its content is read by a SELECT statement.

Answer

A stream can only be created on permanent tables, not on transient or temporary tables.

Answer

The stream will store a physical copy of the source data until it is consumed.

Question 2

Refer to the exhibit. A data engineer needs to produce a flattened result set where each row represents a single product item linked to its customer_id. Which approach using the FLATTEN function will correctly extract the 'prod' and 'qty' values for all orders?

Accepted Answer

Use two nested FLATTEN functions in the FROM clause, referencing the 'orders' then 'value:items'.. Nested arrays in JSON require multiple levels of flattening to reach the leaf nodes. The first FLATTEN call must target the 'orders' array, and a second subsequent FLATTEN call must target the 'items' array within each order. This recursive-style flattening allows the engineer to join the top-level customer_id with the deeply nested product details in a single relational view.

Answer

Use a single FLATTEN on the 'orders' path and access 'items' using bracket notation.

Answer

Apply the GET_PATH function recursively within a GROUP BY clause to aggregate products.

Answer

Utilize the STRIP_NULL_VALUE function before a single FLATTEN on the root object.

Question 3

A data engineering team is evaluating the use of Dynamic Tables for a new transformation pipeline. Which THREE statements accurately describe the behavior and limitations of Dynamic Tables? (Choose THREE)

Accepted Answer

Dynamic Tables can be refreshed incrementally or via a full refresh automatically.. Dynamic Tables simplify the engineering process by allowing declarative data modeling where Snowflake manages the refresh logic. They are designed for incremental updates based on a defined Target Lag. However, engineers must understand that they have specific limitations regarding the types of queries allowed and how they interact with other features like certain window functions or non-deterministic operations.

Answer

Dynamic Tables support the use of non-deterministic functions like CURRENT_TIMESTAMP().

Answer

Dynamic Tables are physically stored as a view and do not consume storage.

Question 4

A data engineer needs to transform a flat table containing organizational hierarchy (employee_id, manager_id) into a format that shows the depth of each employee from the CEO. Which SQL construct should be used to perform this recursive transformation?

Accepted Answer

A CONNECT BY clause. Hierarchical data transformations are common in HR and financial systems. Snowflake provides specific syntax to handle these recursive relationships efficiently. While recursive CTEs are a standard SQL approach, the CONNECT BY clause is a specialized construct designed specifically for navigating parent-child relationships, offering built-in pseudocolumns like LEVEL to track depth within the hierarchy during the transformation process.

Answer

A PIVOT function combined with a window function

Answer

A LATERAL JOIN with a FLATTEN function

Answer

A GROUP BY CUBE clause

Question 5

Refer to the exhibit. If the staging table contains a record with an ID that already exists in the target table and its status is 'DELETED', what is the final outcome for that record in the target table after the MERGE execution?

Accepted Answer

The record will be deleted from the target table.. The MERGE statement evaluates clauses in the order they are written. In this exhibit, the first 'WHEN MATCHED' clause checks for a specific condition ('DELETED'). Since this condition is met, the DELETE operation is prioritized and executed. This pattern is commonly used in CDC pipelines to handle upserts and deletes in a single, atomic DML operation.

Answer

The record will be updated with the value from the staging table.

Answer

The MERGE statement will fail with a 'Multiple matches' error.

Answer

The record will be updated and then immediately deleted.

Question 6

A data engineer needs to provide a transformed view of a massive dataset that requires complex joins and aggregations. The source tables are updated frequently, but the users require the highest possible query performance. Why might the engineer choose a Materialized View over a Dynamic Table in this scenario?

Accepted Answer

Materialized Views offer automatic background maintenance without a user-defined lag.. Choosing between Materialized Views and Dynamic Tables depends on the complexity of the query and the refresh requirements. Materialized Views are more restricted in Snowflake; they only support a single table and limited types of transformations. However, they are ideal for simple workloads where background maintenance is handled automatically and they provide performance benefits for specific types of data distribution.

Answer

Materialized Views support multi-table joins which are not available in Dynamic Tables.

Answer

Materialized Views are always cheaper to maintain than Dynamic Tables.

Answer

Materialized Views support all SQL functions including non-deterministic ones.

Question 7

When transforming data using Snowflake's External Functions, which THREE components are required to establish the connection between Snowflake and the remote service? (Choose THREE)

Accepted Answer

An API Integration object. External Functions allow Snowflake to extend its processing capabilities by calling code hosted outside the platform, such as on AWS Lambda or Azure Functions. To secure and manage this communication, Snowflake requires three distinct objects: an API Integration for authentication, a secure proxy (like an API Gateway), and the External Function definition itself which maps the SQL signature to the remote endpoint.

Answer

A Storage Integration object

Answer

A Snowflake Managed Stage

Question 8

A data engineer is optimizing a large table for better query performance. Which TWO actions are considered best practices for maintaining efficient micro-partitioning through clustering? (Choose TWO)

Accepted Answer

Monitor the clustering depth using the SYSTEM$CLUSTERING_INFORMATION function.. Snowflake automatically manages micro-partitioning, but for very large tables (multi-terabyte), natural clustering may not be sufficient. Manual clustering helps when queries regularly filter on specific columns. Understanding when to apply a clustering key and how to monitor its effectiveness is vital for maintaining performance as the dataset grows and the transformation logic evolves.

Answer

Select columns with high cardinality (e.g., UUIDs) as clustering keys.

Answer

Manually run the RECLUSTER command daily during off-peak hours.

Answer

Apply clustering keys to all tables regardless of their size to ensure consistency.

Question 9

A data engineer needs to calculate the cumulative sum of transactions over time, but the calculation must reset whenever a specific 'gap' of more than 24 hours occurs between consecutive transactions. Which advanced transformation technique is best suited for this?

Accepted Answer

A combination of LAG() and a rolling SUM() window function. This is a 'sessionization' or 'islands and gaps' problem. Standard window functions can calculate running totals, but resetting them based on a time threshold requires identifying groups (islands) of records. This is typically achieved by using the LAG function to compare the current row with the previous row, followed by a conditional sum to create a grouping identifier.

Answer

A recursive CTE (Common Table Expression)

Answer

The GROUP BY ROLLUP clause

Answer

The RATIO_TO_REPORT window function

Question 10

A data engineer is using a stream on a view to capture changes from multiple source tables. Which TWO constraints must be met for a stream to be created on a view? (Choose TWO)

Accepted Answer

The underlying tables must have change tracking enabled.. Streaming changes from a view (Change Data Capture on a View) is a powerful pattern but has strict requirements in Snowflake. The view must be a 'non-materialized' view and all underlying tables must be tracked correctly. This allows Snowflake to reconstruct the delta of the complex join or transformation represented by the view for downstream consumption.

Answer

The view must be a Secure View.

Answer

The view must only contain inner joins and no aggregations.

Answer

The stream must be created using the 'SHOW_INITIAL_ROWS' clause.

Question 11

An engineer is using Dynamic Tables to handle incremental transformations. Which TWO conditions must be met for the refresh to succeed?

Accepted Answer

The owning role must have the SELECT privilege on all source tables and views.. Dynamic Tables automate the pipeline by tracking changes via Change Data Capture. They require defined lag targets and privilege grants on underlying objects. Understanding these constraints is vital for maintaining reliable data pipelines, as failures often stem from privilege misconfigurations or incompatible SQL operations, like non-deterministic functions, that prevent the optimizer from correctly tracking the incremental state of the source tables.

Answer

The query must include a non-deterministic function like CURRENT_TIMESTAMP() in the SELECT clause.

Answer

The query definition must not contain any JOIN operations between tables.

Answer

The warehouse assigned to the dynamic table must be running continuously to trigger refreshes.

Question 12

Which THREE of the following are valid approaches to optimize data transformation pipelines using Streams?

Accepted Answer

Use a stream to capture updates and insert them directly into a target table using a scheduled Task.. Streams are essential for CDC in Snowflake. Optimizing them involves minimizing the data processed, using them with tasks, and avoiding expensive re-computations. Mastering these patterns allows engineers to build resilient, cost-effective pipelines that only process changes rather than entire datasets, which is vital for real-time reporting and ETL efficiency in a production environment where compute costs are a major concern.

Answer

Always set the stream on a view that contains non-deterministic functions to capture dynamic data.

Answer

Use the STREAM_GET_STALE_OFFSET function to force the stream to reset its position to the beginning of time.

Question 13

Which TWO of the following are true regarding the behavior of the 'IGNORE NULLS' clause in transformation functions?

Accepted Answer

It ensures that window functions like LEAD skip over NULL values to find the next non-null value.. The IGNORE NULLS clause is critical for analytic window functions like LAG and LEAD. Without it, null values would interrupt the calculation, leading to incorrect results. Understanding how nulls impact analytical transformations is essential for data engineers producing accurate metrics, as simple calculations can easily lead to misleading insights if null handling is ignored or incorrectly implemented in the transformation logic.

Answer

It can be applied to the SUM() aggregate function to treat NULLs as zero.

Answer

It is valid for use in all standard SQL aggregate functions.

Answer

It converts all NULL values to a default constant value specified by the user.

Question 14

A Data Engineer needs to transform semi-structured JSON data into a relational table. Which transformation strategy offers the best performance for frequently queried nested attributes while minimizing storage costs?

Accepted Answer

Create a view that flattens the VARIANT column into typed, relational columns.. Extracting nested fields into separate columns using a view or a materialized view is the most efficient approach. By flattening the JSON structure, Snowflake's columnar engine can prune data effectively and apply micro-partitioning optimizations. This reduces compute overhead during query execution compared to repeated lateral flattening in standard SQL queries. Proper schema evolution practices ensure that downstream BI tools can consume the data without needing to parse JSON strings at runtime.

Answer

Keep all data in a single VARIANT column and use lateral flatten in every query.

Answer

Convert JSON to a CSV file and load it into a standard table using COPY INTO.

Answer

Store the JSON in a temporary table and use a stored procedure to cast it to STRING.

Question 15

A Data Engineer is using Dynamic Tables to handle incremental transformations. Which condition is required for the Dynamic Table to automatically maintain its data?

Accepted Answer

The defining query must include a specific warehouse to trigger the refresh.. Dynamic Tables require a target warehouse to process updates. The background process, known as the refresh mechanism, evaluates the query definition and incrementally updates the target table based on changes in the source tables. If the warehouse is suspended or insufficient privileges are assigned, the refresh process will fail. Monitoring the state of the Dynamic Table through metadata views is essential for ensuring pipeline reliability in complex multi-stage architectures.

Answer

The source tables must be updated using only the INSERT command.

Answer

The user must manually trigger a refresh every hour for consistency.

Answer

The source data must be stored in a flat file format like Parquet.

Question 16

Which TWO statements are true regarding the use of Streams and Tasks for data transformation in Snowflake?

Accepted Answer

A task can be configured to execute only when a stream contains data.. Streams provide a mechanism to track changes (CDC) in a table, capturing inserts, updates, and deletes. Tasks allow for the execution of SQL commands on a schedule or triggered by a stream. Together, they form a robust framework for incremental transformation. A task can be triggered by a stream containing data to ensure compute resources are only consumed when there is new data to process, optimizing cost and throughput.

Answer

Streams can track changes on views if the view is a secure view.

Answer

Streams automatically purge data once a task consumes it.

Answer

Streams are global objects and can be shared across accounts.

Question 17

Which THREE features are provided by Snowpark for data transformation?

Accepted Answer

Support for Python, Java, and Scala APIs.. Snowpark allows developers to write transformations using idiomatic code in Java, Scala, or Python. It translates this code into optimized SQL executed on Snowflake's compute engine. This provides the flexibility of procedural programming, the scalability of the Snowflake engine, and the ability to include custom libraries for complex data manipulation that would be difficult or impossible to achieve using standard SQL alone.

Answer

Client-side execution on the local machine.

Answer

Native support for training machine learning models on external GPUs.

Question 18

A Data Engineer is using the `FLATTEN` table function to parse a JSON array. If the array contains empty objects that must be ignored, which parameter should be set?

Accepted Answer

Set OUTER => FALSE. When using `FLATTEN`, the `RECURSIVE` and `MODE` parameters control how the function traverses the JSON structure. Specifically, to exclude null or empty nodes, the engine's behavior can be refined using the `OUTER` parameter. Setting `OUTER => FALSE` ensures that rows with no matches in the path are excluded from the output. Mastering these parameters is vital for cleaning messy semi-structured data before it is loaded into downstream relational tables.

Answer

Set RECURSIVE => FALSE

Answer

Set MODE => 'OBJECT'

Answer

Set PATH => 'NULL'

Question 19

When transforming data using User-Defined Functions (UDFs), which language choice offers the highest level of performance for complex, compute-intensive operations?

Accepted Answer

Java UDFs. Snowflake's Java and Python UDFs run in a highly optimized sandbox environment. However, Java UDFs are generally faster for CPU-bound, compute-intensive tasks because of the maturity of the Java Virtual Machine (JVM) in handling multithreading and garbage collection compared to Python's interpreter. For complex, repetitive calculations that occur millions of times, Java can offer significant speed advantages, though Python is often chosen for its ecosystem of data science libraries.

Answer

SQL UDFs

Answer

Python UDFs

Answer

JavaScript UDFs

Question 20

A data engineer needs to extract values from a VARIANT column named 'raw_data' which contains a JSON structure. One of the keys, 'user_id', is sometimes missing from the JSON. Which function or syntax should be used to safely handle missing keys and return a SQL NULL instead of an error or a literal 'null' string?

Accepted Answer

raw_data:user_id::string. Extracting data from VARIANT columns using the colon notation is the standard approach in Snowflake for semi-structured data transformation. This syntax automatically handles missing keys by returning a SQL NULL value, which is crucial for maintaining data integrity during ELT processes. Understanding the difference between a SQL NULL and a JSON null is fundamental for accurate data downstream.

Answer

JSON_EXTRACT_PATH_TEXT(raw_data, 'user_id')

Answer

GET(raw_data, 'user_id')

Answer

FLATTEN(raw_data:user_id)