DEA-C02 Data Transformation — Timed 10 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)

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