DEA-C02 Data Transformation Practice Test 6 — 15 Questions

Question 1

A data engineer is building a transformation pipeline that must read semi-structured events from a stage, parse them, and write the output to a target table. The pipeline will run on a schedule and must be version-controlled and testable like other software artifacts. The team wants to avoid writing SQL scripts that are hard to unit test. Which Snowflake feature should the engineer use to implement this transformation?

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

Accepted Answer

Snowpark with a Python stored procedure. Snowpark is the appropriate choice because it allows developers to write transformation logic in Python, which can be unit tested and version controlled. It also integrates with Snowflake's compute and handles semi-structured data. The other options either lack testability, are not designed for complex transformations, or are technically invalid for the described use case.

Answer

A materialized view over the stage

Answer

A series of SQL scripts executed by a task

Answer

Snowpipe with a transformation function

Question 2

A data engineer is building a transformation pipeline that must read semi-structured events from a stage, parse them, and write the output to a target table. The pipeline will run on a schedule and must be version-controlled and testable like other software artifacts. The team wants to avoid writing SQL scripts that are hard to unit test. Which Snowflake feature should the engineer use to implement this transformation?

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

Accepted Answer

Use the FLATTEN function with the LATERAL keyword to explode the 'events' array, and access the 'timestamp' field using the colon notation, casting it to TIMESTAMP_NTZ.. The FLATTEN function with LATERAL is the standard Snowflake construct for exploding nested arrays in VARIANT columns. It produces one row per array element, and the colon notation allows direct access to nested fields. Casting the extracted value to TIMESTAMP_NTZ ensures the correct data type for downstream processing. This method is efficient and leverages Snowflake's native semi-structured data handling.

Answer

Use the GET_PATH function to directly extract the 'timestamp' field from the array without exploding it, relying on implicit casting to TIMESTAMP_NTZ.

Answer

Use the OBJECT_CONSTRUCT function to rebuild the JSON, then use a JavaScript UDF to iterate over the array and return each timestamp.

Answer

Use the PARSE_JSON function to convert the VARIANT to a string, then use SPLIT_TO_TABLE to separate the array elements, and finally extract the timestamp with regular expressions.

Question 3

A data engineer is transforming a large table with a VARIANT column that contains nested arrays. The goal is to produce one row per element in the array, preserving all other columns. The engineer uses the FLATTEN function with the LATERAL keyword. Which behavior should the engineer expect when the VARIANT column contains an empty array?

Accepted Answer

The row is omitted from the result set because FLATTEN produces no rows for an empty array.. FLATTEN with LATERAL produces one row per element in the array. When the array is empty, there are no elements, so no rows are generated, and the outer row is excluded from the result. This is consistent with inner join semantics. To retain such rows, an outer join with LATERAL FLATTEN is needed. The correct expectation is that the row is omitted.

Answer

The row is retained with NULL values for the flattened columns.

Answer

The query fails with an error because FLATTEN cannot process empty arrays.

Answer

The row is retained with an empty array in the flattened column.

Question 4

A data engineer is transforming a large fact table using a complex SQL query that includes multiple window functions and joins. The query is executed frequently and must return results with minimal latency. The engineer notices that the query spends significant time on repartitioning data for window functions. Which Snowflake feature should be used to improve performance by pre-organizing the data to avoid repartitioning?

Accepted Answer

Define a clustering key on the columns used in the PARTITION BY clause of the window functions.. Clustering keys physically order data by the specified columns, which can align with the PARTITION BY clause of window functions. This reduces the need for Snowflake to repartition data at query runtime, leading to faster execution. For large fact tables with frequent window function queries, clustering is the recommended approach to optimize performance by minimizing data movement.

Answer

Use the RESULT_SCAN function to cache the query results and reuse them.

Answer

Apply search optimization on the columns used in the window functions.

Answer

Create a materialized view that pre-computes the window functions.

Question 5

A data engineer needs to transform a large fact table by adding a column that contains the previous row's `sale_amount` partitioned by `customer_id` and ordered by `sale_date`. The table has billions of rows, and the transformation must run efficiently without shuffling data unnecessarily. Which Snowflake feature should be used to achieve this?

Accepted Answer

Use the `LAG` window function with a `PARTITION BY customer_id ORDER BY sale_date` clause.. The LAG window function is specifically designed to return a value from a preceding row within a partition. Partitioning by customer_id and ordering by sale_date ensures the immediate previous sale for each customer is correctly identified. Snowflake's optimizer can handle large datasets efficiently with window functions, avoiding the costly shuffles typical of self-joins or correlated subqueries.

Answer

Use a correlated subquery that selects the maximum `sale_amount` from the same table where `sale_date` is less than the current row's `sale_date`.

Answer

Use the `LEAD` window function with a `PARTITION BY customer_id ORDER BY sale_date` clause and then reverse the order.

Answer

Use a self-join on `customer_id` where the right table's `sale_date` is less than the left table's `sale_date`, then filter to the maximum right `sale_date`.

Question 6

A data engineer needs to join a large fact table to a small dimension table that changes slowly. The dimension has a `valid_from` and `valid_to` timestamp, and the fact rows have an `event_ts`. The engineer wants to avoid scanning the entire dimension for every fact row and must ensure the join uses the correct validity window. Which approach is most appropriate?

Accepted Answer

Use an ASOF JOIN with `MATCH_CONDITION (event_ts >= valid_from)` and equality on the dimension key.. ASOF JOIN is purpose-built for joining time-series data to slowly changing dimensions by matching the closest preceding record. It uses MATCH_CONDITION to compare timestamps and equality predicates for the key, avoiding full scans and Cartesian products. The other options either ignore historical validity or rely on inefficient join types that do not scale for large fact tables.

Answer

Use a LEFT OUTER JOIN on the dimension key and filter with `valid_to IS NULL` to get the current record.

Answer

Use a non-equi join with `event_ts BETWEEN valid_from AND valid_to` and rely on the optimizer to prune partitions.

Answer

Use a CROSS JOIN with a WHERE clause that filters on the dimension key and timestamp range.

Question 7

A data engineer is using Snowpark for Python to transform data. The engineer needs to perform a join between two DataFrames and then apply a filter based on a column from the joined result. Which TWO methods are appropriate for this transformation? (Choose two.)

Accepted Answer

Use `DataFrame.join()` to combine the DataFrames, then `DataFrame.where()` to apply the condition.. In Snowpark, join() combines two DataFrames, and filter() or its alias where() applies a row-wise condition. These methods build a lazy query plan that executes on Snowflake. Other methods like merge(), union(), or select() do not perform a join followed by a row filter, so they are not appropriate for this transformation.

Answer

Use `DataFrame.union()` to combine the DataFrames, then `DataFrame.filter()` to apply the condition.

Answer

Use `DataFrame.join()` to combine the DataFrames, then `DataFrame.select()` to apply the condition.

Answer

Use `DataFrame.merge()` to combine the DataFrames, then `DataFrame.filter()` to apply the condition.

Question 8

A data engineer is working with a table that has a column `order_date` of type DATE. The engineer needs to create a new column that contains the year and month in the format 'YYYY-MM' (e.g., '2023-01') for each order. Which Snowflake function should be used to produce this formatted string?

Accepted Answer

TO_CHAR(order_date, 'YYYY-MM'). TO_CHAR with a format string is the correct function to convert a date into a custom string format. It directly produces the 'YYYY-MM' representation, including leading zeros for months. Other options either return a date, invalid syntax, or lack proper formatting.

Answer

DATE_TRUNC('month', order_date)

Answer

CONCAT(YEAR(order_date), '-', MONTH(order_date))

Answer

EXTRACT(YEAR_MONTH FROM order_date)

Question 9

A data engineer is working with a table that stores customer orders in a VARIANT column named order_details. The order_details column contains an array of line items under the key 'items'. Each line item is an object with keys 'product_id', 'quantity', and 'price'. The engineer needs to produce a flattened result set where each row represents a single line item with its associated order ID. Which Snowflake function should be used to achieve this transformation?

Accepted Answer

LATERAL FLATTEN(input => order_details:items). To transform an array of line items into individual rows, the LATERAL FLATTEN function is the correct choice. It takes a VARIANT array and outputs one row per element, allowing each line item to be processed separately. The other functions either aggregate, extract keys, or parse JSON but do not explode arrays into rows.

Answer

OBJECT_KEYS(order_details:items)

Answer

PARSE_JSON(order_details:items)

Answer

ARRAY_TO_STRING(order_details:items, ',')

Question 10

A data engineer is transforming a table with a column `tags` stored as a VARIANT array of strings. The goal is to produce one row per tag for downstream aggregation. Which function should be used to expand the array into rows?

Accepted Answer

FLATTEN(input => tags). FLATTEN is the Snowflake table function that unnests semi-structured arrays and objects into relational rows. When applied to a VARIANT array, it returns one row per element, making it the correct choice for expanding `tags` into individual rows. The other functions either concatenate, parse, or extract object keys, none of which achieve the required row-level expansion.

Answer

OBJECT_KEYS(tags)

Answer

ARRAY_TO_STRING(tags, ',')

Answer

PARSE_JSON(tags)

Question 11

A data engineer is designing a transformation that requires unpivoting a wide table with columns `q1_sales`, `q2_sales`, `q3_sales`, and `q4_sales` into a long format with columns `quarter` and `sales`. The table has millions of rows. Which Snowflake construct should be used to achieve this efficiently?

Accepted Answer

Use the `UNPIVOT` clause in a SELECT statement.. The UNPIVOT clause is specifically designed to transform columns into rows. It is a native Snowflake feature that is optimized for performance on large tables. Using UNION ALL or LATERAL FLATTEN can achieve similar results but with more complexity and potentially less efficiency. PIVOT does the reverse and is not appropriate here.

Answer

Use the `PIVOT` clause in a SELECT statement.

Answer

Use a `LATERAL FLATTEN` on an array constructed from the quarter columns.

Answer

Use a series of `UNION ALL` SELECT statements, one for each quarter column.

Question 12

A data engineer is working with a table that contains a column of type VARCHAR storing JSON strings. The engineer needs to transform this column into a VARIANT type to leverage Snowflake's semi-structured data functions. Which function should be used to convert the VARCHAR column to VARIANT?

Accepted Answer

PARSE_JSON. PARSE_JSON is the dedicated function for converting a JSON-formatted string into a VARIANT. It parses the string and creates a structured object that can be queried using Snowflake's semi-structured data functions. This transformation is essential for enabling access to nested JSON elements and is the recommended approach for converting VARCHAR JSON to VARIANT.

Answer

CAST

Answer

TO_VARIANT

Answer

TRY_PARSE_JSON

Question 13

A data engineer needs to transform a string column containing dates in the format 'YYYY-MM-DD' into a DATE type. The column may contain NULL values and occasionally invalid date strings. The engineer wants the transformation to return NULL for invalid strings without causing the query to fail. Which function should the engineer use?

Accepted Answer

TRY_TO_DATE. TRY_TO_DATE is specifically designed to return NULL instead of failing when a string cannot be converted to a date. This makes it the correct choice for transforming a column that may contain invalid date strings. TO_DATE and CAST would raise errors, and DATE is not a function. The engineer should use TRY_TO_DATE to ensure the transformation completes successfully.

Answer

DATE

Answer

TO_DATE

Answer

CAST

Question 14

A data engineer is designing a transformation pipeline that uses Snowpark to process data. The pipeline must perform complex data cleaning and feature engineering. Which TWO capabilities of Snowpark are specifically designed to support these transformations? (Choose two.)

Accepted Answer

The ability to execute user-defined functions (UDFs) written in Python, Java, or Scala within Snowflake.. Snowpark's DataFrame API allows developers to write transformations in Python, Java, or Scala, providing a familiar and expressive way to perform complex data cleaning and feature engineering. Additionally, Snowpark supports UDFs in these languages, enabling custom logic to be executed within Snowflake. These two capabilities directly empower the development of sophisticated transformation pipelines.

Answer

The ability to automatically materialize transformation results into a new table without explicit DDL.

Answer

The ability to execute transformations on data without moving it out of Snowflake's storage layer.

Answer

The ability to use the Snowpark ML library for building and deploying machine learning models directly in Snowflake.

Question 15

A data engineer is using Snowpark for Python to perform transformations on a Snowflake table. The engineer wants to leverage Snowpark's capabilities for data transformation. Which two statements accurately describe Snowpark's features for transformation? (Choose two.)

Accepted Answer

Snowpark allows you to write transformations using a DataFrame API that is lazily evaluated and executed on the Snowflake compute engine.. Snowpark's DataFrame API enables lazy evaluation and pushdown to Snowflake, and it supports UDFs in multiple languages. These two features are fundamental for performing transformations efficiently. The other options incorrectly describe Snowpark as caching client-side, requiring data extraction, or providing a visual interface, which are not accurate.

Answer

Snowpark automatically caches all intermediate transformation results in memory on the client side to speed up iterative development.

Answer

Snowpark requires data to be extracted to a local Python environment for any transformation that uses third-party libraries.

Answer

Snowpark provides a built-in visual interface for designing transformation pipelines without writing code.