DEA-C02 Data Transformation Practice Test 4 — 15 Questions

Question 1

A data engineer needs to transform a table by replacing NULL values in a numeric column with the average of the non-NULL values in that column. The table contains millions of rows. Which SQL approach is most efficient and correct?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

MERGE INTO table t USING (SELECT AVG(column) AS avg_val FROM table) s ON t.column IS NULL WHEN MATCHED THEN UPDATE SET t.column = s.avg_val;. The MERGE statement computes the average once in a subquery and efficiently updates all NULL rows in a single set-based operation. This is more performant than correlated subqueries or window functions that might cause errors. The other options either have syntax issues, potential inefficiencies, or logical flaws. Using MERGE is a best practice for such transformations in Snowflake.

Answer

UPDATE table SET column = avg_val FROM (SELECT AVG(column) OVER () AS avg_val FROM table) WHERE column IS NULL;

Answer

UPDATE table SET column = (SELECT AVG(column) FROM table WHERE column IS NOT NULL) WHERE column IS NULL;

Answer

UPDATE table SET column = (SELECT AVG(column) FROM table) WHERE column IS NULL;

Question 2

A data engineer needs to apply a transformation that replaces NULL values in a numeric column with the average of the non-NULL values in the same column, computed over a window partitioned by `region` and ordered by `sale_date`. Which expression correctly achieves this using a window function?

Accepted Answer

COALESCE(amount, AVG(amount) OVER (PARTITION BY region)). To replace NULLs with the regional average, the window function must compute the average over the entire partition without an ORDER BY that restricts the frame. `AVG(amount) OVER (PARTITION BY region)` does exactly that, and COALESCE supplies the original value when present. The other options either compute a running average, partition by date, or add unnecessary complexity.

Answer

COALESCE(amount, AVG(amount) OVER (PARTITION BY region ORDER BY sale_date))

Answer

COALESCE(amount, AVG(amount) OVER (PARTITION BY region, sale_date))

Answer

IFNULL(amount, AVG(amount) OVER (PARTITION BY region ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING))

Question 3

A data engineer needs to transform a table by replacing NULL values in a numeric column with the average of the non-NULL values in that column. The table contains millions of rows. Which SQL approach is most efficient and correct?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

COALESCE(column, AVG(column) OVER (PARTITION BY category)). The COALESCE function is designed to return the first non-NULL value from its arguments. When combined with a windowed AVG partitioned by category, it replaces NULLs in the column with the category-specific average. This achieves the transformation in a single SQL statement without subqueries. Other functions like NULLIF or ARRAY_TO_STRING do not provide this behavior.

Answer

IFNULL(column, AVG(column) OVER (PARTITION BY category))

Answer

NVL(column, AVG(column) OVER (PARTITION BY category))

Answer

NULLIF(column, AVG(column) OVER (PARTITION BY category))

Question 4

A data engineer is using a Snowflake stream to capture change data capture (CDC) records from a source table. The engineer needs to transform the stream data by joining it with a dimension table to enrich the records before loading into a target table. The stream has a column `METADATA$ACTION` that indicates INSERT, DELETE, or UPDATE. Which transformation approach correctly handles both inserts and deletes when joining with the dimension table?

Accepted Answer

Use a LEFT OUTER JOIN from the stream to the dimension table, and include all METADATA$ACTION values.. To correctly handle both inserts and deletes from a stream, the transformation should preserve all stream records. A LEFT OUTER JOIN from the stream to the dimension table ensures that every stream record is included, and dimension attributes are added when available. This allows downstream logic to process deletes by recognizing the DELETE action and acting accordingly.

Answer

Use a CROSS JOIN between the stream and the dimension table to ensure all combinations are considered.

Answer

Use an INNER JOIN between the stream and the dimension table, and filter for METADATA$ACTION = 'INSERT'.

Answer

Use a FULL OUTER JOIN between the stream and the dimension table to capture all changes.

Question 5

A data engineer is designing a transformation that uses the PIVOT operator to convert rows into columns. The source table 'sales' has columns 'product', 'month', and 'revenue'. The engineer wants to pivot on 'month' and aggregate 'revenue' using SUM. Which TWO statements are true regarding the behavior of the PIVOT operator in Snowflake? (Choose two.)

Accepted Answer

The PIVOT operator automatically excludes the column used for aggregation from the output.. PIVOT in Snowflake consumes the aggregated value column, so it does not appear in the output. It also allows a dynamic list of pivot values via a subquery in the IN clause, enabling flexible transformations. Multiple aggregates are supported, and pivot columns can be non-numeric. The generated column names are derived from the pivot values and aggregates, not the original names.

Answer

The PIVOT operator can only be used with a single aggregate function per pivot.

Answer

The PIVOT operator preserves the original column names for the pivoted columns without any modification.

Answer

The PIVOT operator can only pivot on a numeric column.

Question 6

A data engineer is designing a transformation pipeline that uses a Snowflake stream on a table to capture changes. The stream will feed a task that merges changes into a target table. Which TWO statements about stream consumption and transformation are correct? (Choose two.)

Accepted Answer

The METADATA$ACTION column in a stream indicates whether the row was inserted or deleted.. Streams provide metadata columns that describe each change: METADATA$ACTION distinguishes inserts from deletes, and METADATA$ISUPDATE flags rows that are part of an update. These columns are essential for building correct merge transformations. The other statements misrepresent stream concurrency, view support, and retention behavior, making them incorrect for this pipeline design.

Answer

A stream can be consumed by multiple concurrent transactions without advancing the offset until each transaction commits.

Answer

A stream automatically purges change data after a fixed retention period regardless of consumption.

Answer

A stream can be created on a view that joins multiple tables, and it will capture changes from all underlying tables.

Question 7

A data engineer needs to filter the results of a complex analytical query based on the result of a window function. The query calculates a rolling average of sales per region and should only return rows where the current sale exceeds that average. Which SQL clause is most efficient for this transformation?

Accepted Answer

The QUALIFY clause. Filtering on window functions requires a mechanism that executes after the window calculations are performed. The QUALIFY clause allows engineers to filter results directly in the SELECT statement without nesting logic inside a subquery or a Common Table Expression. This significantly improves query readability and can lead to internal optimizations by the Snowflake query optimizer during the execution phase.

Answer

The WHERE clause

Answer

The HAVING clause

Answer

The GROUP BY clause

Question 8

A data engineer is implementing a Python User-Defined Function (UDF) to perform complex string manipulation. To optimize performance for a large-scale transformation, the engineer wants to ensure the UDF processes multiple rows in a single call. Which type of UDF should be implemented?

Accepted Answer

A Vectorized Python UDF. Snowflake's Vectorized Python UDFs allow for high-performance processing by passing batches of rows as Pandas DataFrames or Series. This reduces the overhead associated with calling the function for every individual row. For data transformations involving heavy computational logic or libraries like NumPy, vectorized UDFs are significantly more efficient than standard scalar UDFs which process data row-by-row.

Answer

A scalar Python UDF with a loop

Answer

A Python User-Defined Table Function (UDTF)

Answer

A JavaScript UDF using the 'async' keyword

Question 9

A data engineer is converting a column of strings into integers. Some rows contain non-numeric characters that would normally cause the query to fail. Which function should be used to return a NULL value instead of an error when a conversion is impossible?

Accepted Answer

TRY_CAST(). Data quality issues are common during transformation. Standard casting functions like CAST() or TO_NUMBER() are strict and will terminate a query if they encounter invalid input. To build resilient pipelines, engineers use 'try' variants of these functions, which gracefully handle errors by returning a NULL, allowing the rest of the dataset to be processed without interruption.

Answer

AS_INTEGER()

Answer

TO_DECIMAL()

Answer

COALESCE()

Question 10

Refer to the exhibit. A data engineer applies search optimization to a table to speed up point lookups. How does this transformation impact the storage and maintenance costs for the table?

Accepted Answer

It increases storage costs and uses serverless compute for maintenance.. The Search Optimization Service is a background process that creates a persistent search access path (index-like structure). It significantly improves performance for point lookups but comes at a cost. The service consumes both storage for the search access path and serverless compute for the background maintenance as data in the base table is modified over time.

Answer

It has no impact on storage as it uses the existing metadata of the micro-partitions.

Answer

It only increases compute costs during the initial build phase, with no ongoing costs.

Answer

It reduces storage costs by compressing the base table micro-partitions more efficiently.

Question 11

To comply with privacy regulations, a data engineer must ensure that certain columns in a table are masked for unauthorized users during transformation. Which Snowflake feature provides a way to define re-usable masking logic that is automatically applied at query time?

Accepted Answer

Dynamic Data Masking Policies. Dynamic Data Masking is a security-focused transformation feature that allows engineers to protect sensitive data without changing the underlying stored values. By creating Masking Policies and applying them to columns, Snowflake ensures that the transformation (masking) happens dynamically based on the role and context of the user executing the query.

Answer

Row Access Policies

Answer

External Tables with encrypted files

Answer

Secure Views with hardcoded filters

Question 12

When performing a MERGE operation to update a large table, which factor most significantly impacts the performance of the transformation?

Accepted Answer

The clustering of the target table on the join column used in the MERGE statement.. The performance of a MERGE operation is primarily constrained by the join condition, specifically if the join column is not clustered or indexed. Because Snowflake does not use traditional indexes, clustering by the join key allows for effective partition pruning. Ensuring that the join criteria align with the table's clustering key is the most effective way to minimize data scanning and optimize the merge process.

Answer

The number of columns included in the SELECT list of the source query.

Answer

The size of the virtual warehouse, as larger warehouses always make MERGE operations faster.

Answer

The use of an explicit transaction block around the MERGE statement.

Question 13

You are debugging a transformation pipeline where a task is failing with an 'Insufficient Privileges' error during a MERGE operation. The task is owned by a service account role. What is the most likely cause?

Accepted Answer

The task owner lacks the required DML privileges on the target table being merged into.. Tasks execute with the privileges of their owner. If the owner role lacks USAGE on the database, schema, or warehouse, or lacks the required DML permissions (INSERT/UPDATE/DELETE) on the target table, the task will fail. This is a common security best practice in Snowflake to ensure the principle of least privilege, requiring careful validation of all object-level permissions during the deployment phase.

Answer

The task owner does not have the EXECUTE TASK privilege on the current schema.

Answer

The warehouse used by the task is currently suspended by another process.

Answer

The task is missing a dependency definition for the source table.

Question 14

An engineer needs to ensure that a transformation pipeline handles 'late-arriving' data in a streaming context. Which feature is most effective?

Accepted Answer

Using Dynamic Tables with a configured lag target.. Snowflake's Dynamic Tables allow for declarative data transformation pipelines that automatically handle data state, including updates and late arrivals. By defining the lag, the engine manages the re-processing required to integrate new data into the final result set. This eliminates the need for manual handling of late data, which is historically a significant pain point in traditional ETL pipeline design.

Answer

Using a manual stored procedure to scan for missing data based on timestamps.

Answer

Implementing a complex Python task to buffer all data in a queue before processing.

Answer

Overwriting the entire target table with every execution to ensure data integrity.

Question 15

A data engineer needs to perform a complex transformation that involves reading from a source table and writing to multiple target tables in a single transaction. What is the recommended strategy?

Accepted Answer

Wrap the multiple INSERT statements in a single BEGIN...COMMIT transaction block.. Using an explicit transaction block (BEGIN...COMMIT) ensures that multiple DML statements are treated as a single atomic unit. This is critical for data consistency, particularly when updating related tables simultaneously. If any part of the transformation fails, the transaction can be rolled back, ensuring the system remains in a valid, predictable state, which is a core requirement for reliable enterprise data pipelines.

Answer

Use an asynchronous task for each target table to run in parallel.

Answer

Create a separate stored procedure for each insert to reduce complexity.

Answer

Use a series of independent views to union the data instead of writing to tables.