DEA-C02 Data Transformation Practice Test 3 — 15 Questions

Question 1

A data engineer is building a transformation pipeline that processes semi-structured event data stored in a VARIANT column named `event_payload`. The pipeline must extract the value of the key `session_id` from the top-level JSON object. The `session_id` key is always present and contains a string. Which expression should the engineer use to reliably return `session_id` as a VARCHAR?

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

Accepted Answer

event_payload:session_id::VARCHAR. The colon operator is the standard way to extract a top-level key from a VARIANT object in Snowflake. It returns a VARIANT, which can then be cast to the desired data type. Since the key is always present and contains a string, casting to VARCHAR yields the correct result. Other notations, such as dot or bracket, are not valid for this purpose and would cause errors or return NULL.

Answer

event_payload.session_id::VARCHAR

Answer

event_payload['session_id']::VARCHAR

Answer

GET(event_payload, 'session_id')::VARCHAR

Question 2

A data engineer is building a transformation pipeline that processes semi-structured event data stored in a VARIANT column named `event_payload`. The pipeline must extract the value of the key `session_id` from the top-level JSON object. The `session_id` key is always present and contains a string. Which expression should the engineer use to reliably return `session_id` as a VARCHAR?

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

Accepted Answer

Use a WHERE clause with IS_VARCHAR(event_payload:session_id) AND event_payload:session_id IS NOT NULL.. The requirement is to include only events where session_id exists and is a non-null string. The IS_VARCHAR function is designed specifically to test if a VARIANT value is a VARCHAR, returning FALSE for missing keys or other types. Combining it with IS NOT NULL ensures null strings are excluded. This dual check guarantees the correct data is included.

Answer

Use a WHERE clause with event_payload:session_id IS NOT NULL.

Answer

Use a WHERE clause with event_payload:session_id::STRING IS NOT NULL.

Answer

Use a WHERE clause with GET_PATH(event_payload, 'session_id') IS NOT NULL.

Question 3

A data engineer is building a transformation pipeline that processes semi-structured event data stored in a VARIANT column named `event_payload`. The pipeline must extract the value of the key `session_id` from the top-level JSON object. The `session_id` key is always present and contains a string. Which expression should the engineer use to reliably return `session_id` as a VARCHAR?

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

Accepted Answer

CONVERT_TIMEZONE('America/New_York', event_time)::DATE AS event_date, EXTRACT(HOUR FROM CONVERT_TIMEZONE('America/New_York', event_time)) AS event_hour. The two-argument CONVERT_TIMEZONE interprets the input timestamp as UTC and converts it to the specified target timezone. This is the correct approach when the stored TIMESTAMP_NTZ represents UTC. Casting to DATE yields the local date, and EXTRACT(HOUR ...) yields the local hour. Using three arguments with 'UTC' as source is redundant, while reversing the arguments converts in the wrong direction. Omitting conversion entirely fails the timezone requirement.

Answer

CONVERT_TIMEZONE('UTC', 'America/New_York', event_time)::DATE AS event_date, EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', event_time)) AS event_hour

Answer

CONVERT_TIMEZONE('America/New_York', 'UTC', event_time)::DATE AS event_date, EXTRACT(HOUR FROM CONVERT_TIMEZONE('America/New_York', 'UTC', event_time)) AS event_hour

Answer

DATE_TRUNC('DAY', event_time)::DATE AS event_date, EXTRACT(HOUR FROM event_time) AS event_hour

Question 4

A data engineer is transforming a large fact table that contains billions of rows. The transformation requires calculating a moving average over a 7-day window for each product, and the result must be stored in a new table. The engineer wants to minimize the amount of data processed and avoid repeated scans of the base table. Which approach is most efficient in Snowflake?

Accepted Answer

Use a single INSERT INTO ... SELECT statement with a window function that computes the moving average and writes directly to the target table.. The most efficient method is to perform the transformation in a single SQL statement using a window function. This minimizes data movement and leverages Snowflake's parallel processing. Other approaches introduce extra steps, data movement, or limitations that make them less efficient for large-scale data.

Answer

Create a temporary table with the base data, then run a separate UPDATE statement to compute the moving average and store it in a new column.

Answer

Use a Snowpark Python stored procedure that reads the data into a DataFrame, computes the moving average using pandas, and writes the result back to Snowflake.

Answer

Create a materialized view that includes the moving average calculation, then query the materialized view to populate the target table.

Question 5

A data engineer stores events in a table with a VARIANT column `payload`. The payload may contain `device.os` as a string at the top level, or it may be missing, or it may have a nested `device` object with an `os` key. The engineer wants to return the OS value when present and a fallback string `'unknown'` otherwise, without erroring when the path is absent. Which expression should be used?

Accepted Answer

SELECT COALESCE(payload:device.os::STRING, payload:device:os::STRING, 'unknown') AS os. The requirement is to extract a value from either a flat key or a nested object and substitute a default when neither exists. COALESCE evaluates its arguments in order and returns the first non-NULL result, which naturally handles missing keys and multiple possible payload shapes. The other expressions either use incorrect path syntax or omit the fallback string, leaving NULLs where a default is needed.

Answer

SELECT TRY_CAST(payload:device.os AS STRING) AS os

Answer

SELECT payload:device.os::STRING AS os

Answer

SELECT IFNULL(payload:device.os::STRING, payload:device:os::STRING) AS os

Question 6

A data engineer needs to transform semi-structured event data stored in a VARIANT column named event_data. The data contains an array of objects under the key 'items', and each object has a 'price' attribute stored as a string (e.g., "19.99"). The engineer must produce a single row per event where the total price of all items is calculated as a numeric value. Which SQL statement achieves this?

Accepted Answer

SELECT event_id, SUM(TO_NUMBER(item.value:price::STRING)) AS total_price FROM events, LATERAL FLATTEN(input => event_data:items) AS item GROUP BY event_id;. The correct approach uses LATERAL FLATTEN to expand the array of items, then TO_NUMBER to convert each price string to a numeric value, and SUM to aggregate per event. This ensures accurate monetary calculations. The other options either omit necessary conversion, use less precise casting, or lack explicit scale specification, which can lead to incorrect results.

Answer

SELECT event_id, SUM(CAST(item.value:price AS FLOAT)) AS total_price FROM events, LATERAL FLATTEN(input => event_data:items) AS item GROUP BY event_id;

Answer

SELECT event_id, SUM(item.value:price) AS total_price FROM events, LATERAL FLATTEN(input => event_data:items) AS item GROUP BY event_id;

Answer

SELECT event_id, SUM(item.value:price::NUMBER) AS total_price FROM events, LATERAL FLATTEN(input => event_data:items) AS item GROUP BY event_id;

Question 7

A data engineer is building a transformation pipeline that processes semi-structured event data stored in a VARIANT column named `event_payload`. The pipeline must extract the value of the key `session_id` from the top-level JSON object. The `session_id` key is always present and contains a string. Which expression should the engineer use to reliably return `session_id` as a VARCHAR?

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

Accepted Answer

Use `event_payload:device_type::STRING` in a WHERE clause with `IS NOT NULL`, and group by that expression.. Extracting with the colon operator and casting to STRING converts both missing keys and explicit JSON nulls to SQL NULL. Filtering with IS NOT NULL then removes both cases, ensuring the count by device_type is accurate and excludes incomplete events.

Answer

Use `event_payload:device_type` in a WHERE clause with `IS DISTINCT FROM NULL`, and group by that expression.

Answer

Use `GET_PATH(event_payload, 'device_type')` in a WHERE clause with `IS NOT NULL`, and group by that expression.

Answer

Use `event_payload:device_type` in a WHERE clause with `IS NOT NULL`, and group by that expression.

Question 8

A data engineer needs to transform a large dataset by applying a complex JavaScript user-defined function (UDF) to each row. The UDF is computationally expensive and the dataset is several terabytes. The engineer wants to minimize cost and execution time. Which Snowflake feature should be used to process the data efficiently?

Accepted Answer

Use a vectorized Python UDF (via the Snowpark API) on a Snowpark-optimized warehouse.. Vectorized Python UDFs allow processing data in batches, which is much faster than row-by-row processing for large datasets. When paired with a Snowpark-optimized warehouse, which offers higher memory and compute per node, the transformation can be executed efficiently, reducing both time and cost. This is the recommended approach for complex, expensive UDFs on large data volumes.

Answer

Use a JavaScript UDF within a SELECT statement on a virtual warehouse.

Answer

Use a Java UDF with vectorized execution on a standard warehouse.

Answer

Use a Snowpark Python user-defined function (UDF) executed on a Snowpark-optimized warehouse.

Question 9

A data engineer is using Snowflake's PIVOT function to transform a table from long to wide format. The source table has columns sale_date, product, and revenue. The engineer wants to create a pivot where each product becomes a column, and the values are the sum of revenue per sale_date. Which two statements are true about the PIVOT operation in this scenario? (Choose two.)

Accepted Answer

The PIVOT operation can be combined with a GROUP BY clause on the non-pivoted columns.. PIVOT requires an aggregate function to combine multiple rows into a single cell, and it implicitly groups by the non-pivoted columns. While an explicit GROUP BY is not necessary, the operation does perform grouping. The other statements are false: pivoted columns cannot be filtered in the same query level, values must be explicitly listed, and PIVOT is not limited to numeric columns.

Answer

The PIVOT function automatically includes all distinct values from the pivot column as new columns.

Answer

The PIVOT function can only be applied to numeric columns.

Answer

Columns generated by PIVOT can be referenced directly in the same query's WHERE clause without a subquery.

Question 10

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, and also ensure that any negative values are replaced with zero. Which TWO Snowflake functions or clauses can be used together to achieve this transformation in a single SELECT statement? (Choose two.)

Accepted Answer

GREATEST(column, 0). To replace NULLs with the column average and negative values with zero, the engineer can use COALESCE to fill NULLs with the average computed via AVG OVER (), and GREATEST to replace negatives with zero. These two functions can be nested or used in sequence to achieve the desired result in a single SELECT statement.

Answer

ZEROIFNULL(column)

Answer

IFNULL(column, AVG(column) OVER ())

Answer

NULLIF(column, 0)

Question 11

A data engineer needs to transform a table by replacing all NULL values in the 'discount' column with 0. The table contains millions of rows, and the transformation will be applied frequently. Which Snowflake function should be used to achieve this efficiently?

Accepted Answer

ZEROIFNULL(discount). ZEROIFNULL is a specialized Snowflake function that replaces NULL with 0 for numeric expressions. It is more efficient and self-documenting than general functions like COALESCE or IFNULL, which are designed for broader use cases. When transforming large tables, using the most specific function reduces computational overhead and improves readability. ZEROIFNULL is the intended function for this exact scenario.

Answer

IFNULL(discount, 0)

Answer

NVL(discount, 0)

Answer

COALESCE(discount, 0)

Question 12

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 transformation must be performed in a single SQL statement and should be efficient for a large table. Which function or approach should be used?

Accepted Answer

Use the AVG window function with the IGNORE NULLS clause and then apply COALESCE to replace NULLs.. The AVG window function with IGNORE NULLS computes the average of non-NULL values over a specified window. When used with an empty OVER clause, it calculates the overall average across all rows. Then, COALESCE replaces NULLs with that average. This approach is efficient because it processes the data in a single pass, avoiding multiple table scans, and leverages Snowflake's optimized window function execution.

Answer

Use the NVL function with a subquery that calculates the average.

Answer

Use the IFNULL function with a scalar subquery that computes the average.

Answer

Use the COALESCE function with a subquery that calculates the average.

Question 13

A data engineer is building a transformation pipeline that processes semi-structured event data stored in a VARIANT column named `event_payload`. The pipeline must extract the value of the key `session_id` from the top-level JSON object. The `session_id` key is always present and contains a string. Which expression should the engineer use to reliably return `session_id` as a VARCHAR?

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

Accepted Answer

Use Snowpipe with a transformation function to apply filtering and aggregation during ingestion.. Snowpipe's transformation capabilities allow filtering and aggregation during ingestion, which is ideal for streaming data pipelines requiring low latency. This approach eliminates the need for separate transformation steps, reducing latency and cost. Other options introduce delays or are not designed for streaming data, making them unsuitable for this scenario.

Answer

Create a stream on the target table and use a task with a WHEN clause to process only new data.

Answer

Use a task that runs every minute to transform the data using a stored procedure.

Answer

Create a materialized view that joins the stream with the dimension table and pre-aggregates the results.

Question 14

A data engineer is designing a transformation that must apply a sequence of operations to a stream of data: first filter out invalid records, then enrich with a lookup, and finally aggregate. The engineer wants to ensure that the transformation is applied only to new data since the last run and that the operations are performed in the correct order. Which Snowflake feature should the engineer use to achieve this?

Accepted Answer

A task that executes a stored procedure with a stream as input. A task combined with a stored procedure and a stream enables incremental processing of new data with ordered transformations. The stream captures changes, the task triggers the procedure, and the procedure applies filter, enrich, and aggregate steps in sequence. Other options either do not support streams or do not provide the required ordering and custom logic.

Answer

A Snowpipe that continuously loads data and applies transformations

Answer

A materialized view that joins the stream to a lookup table

Answer

A dynamic table that references the stream and applies transformations

Question 15

A data engineer is transforming a table that contains a column 'log_message' of type VARCHAR. The engineer needs to extract the substring that appears after the first occurrence of the delimiter 'ERROR: ' and before the next newline character. The log messages can contain multiple lines. Which Snowflake function combination should be used to reliably extract this substring?

Accepted Answer

REGEXP_SUBSTR(log_message, 'ERROR: ([^
]*)', 1, 1, 'e', 1). Extracting a substring between a delimiter and a newline requires a pattern that excludes newline characters. The regular expression 'ERROR: ([^
]*)' captures the desired text by matching any character except newline after the delimiter. Using REGEXP_SUBSTR with the 'e' parameter returns the first capture group. Other methods like SPLIT_PART or a greedy dot pattern would include subsequent lines or fail when no newline is present.

Answer

SPLIT_PART(log_message, 'ERROR: ', 2)

Answer

SUBSTR(log_message, CHARINDEX('ERROR: ', log_message) + LEN('ERROR: '), CHARINDEX('
', log_message, CHARINDEX('ERROR: ', log_message)) - CHARINDEX('ERROR: ', log_message) - LEN('ERROR: '))

Answer

REGEXP_SUBSTR(log_message, 'ERROR: (.*)', 1, 1, 'e', 1)