DEA-C02 · domain
Data Transformation
This domain covers transforming data inside Snowflake: SQL window functions, QUALIFY, Python and Java UDFs, stored procedures, Snowpark, and Change Tracking. Questions present a transformation goal and ask you to choose the most efficient, correct Snowflake-native construct, or to identify which features deliver the stated benefit.
Focused practice
Practice Data Transformation questions
Scored sessions drawing only from this domain — pick a length below.
Start 20-question practice test →What this domain covers
What to know about Data Transformation
Be able to pick the right Snowflake transformation primitive for a given workload and write it correctly. The single most important thing: use QUALIFY to filter window-function results, and reserve Snowpark or vectorized UDFs for complex, stateful, large-volume logic.
Filtering window-function output with QUALIFY instead of wrapping the query in a subquery
Choosing vectorized Python UDFs or Snowpark for large-scale string and row transformations
Using Change Tracking and streams to process only changed rows during incremental transforms
Selecting SQL window functions versus Snowpark or stored procedures for stateful processing
Watch out for
Common Data Transformation exam traps
- ▸Trying to reference a window function alias in WHERE; Snowflake requires QUALIFY or an outer query instead.
- ▸Assuming scalar Python UDFs are vectorized by default; without the vectorized decorator they run row by row and scale poorly.
- ▸Confusing Change Tracking metadata with actual change data, and forgetting streams must be consumed to advance the offset.
Question index
All Data Transformation questions (40)
Click any question to see the full explanation, or start a practice session above.
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?
Easy2A 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?
Medium3A 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?
Hard4A data engineer is building a transformation pipeline that must parse semi-structured log data stored in a VARIANT column named log_data. The JSON structure contains a nested array under the key 'events', and each element in the array has a 'timestamp' field. The engineer needs to produce one row per event with the timestamp extracted as a TIMESTAMP_NTZ value. Which SQL construct should be used to achieve this transformation efficiently?
Medium5In a data transformation pipeline, what is the primary benefit of using 'Zero-Copy Cloning' for creating a development environment from production data?
Medium6Which feature allows a Data Engineer to define a transformation pipeline where the target table automatically updates when the source table changes, without needing to manually define a schedule?
Easy7Refer 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?
Hard8A 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.)
Medium9A 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?
Hard10To 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?
Medium11Which Snowflake feature helps in managing data transformation pipelines by providing a visual and programmatic way to handle task dependencies and state management?
Medium12A 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.)
Medium13Which Snowflake feature is best suited for transforming data that is already loaded and requires periodic, complex SQL transformations without managing manual scheduling or streams?
Easy14An engineer needs to ensure that a transformation pipeline handles 'late-arriving' data in a streaming context. Which feature is most effective?
Medium15Which transformation technique should be used when you need to pivot data from a long-form format (rows) to a wide-form format (columns) for reporting?
Medium16A data engineer is building a transformation pipeline that processes semi-structured event logs stored in a VARIANT column named `event_data`. The logs contain a nested array under the key `items`. The engineer needs to produce one output row per element in the array, preserving all other columns from the source table. Which Snowflake construct should be used to achieve this transformation?
Medium17When performing a MERGE operation to update a large table, which factor most significantly impacts the performance of the transformation?
Hard18What is the primary benefit of using `CLONE` for data transformation and testing?
Easy19A Data Engineer needs to ensure that data in a target table is updated with changes from a source table while handling potential duplicate records. Which command should be used?
Medium20A 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?
Easy21A 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?
Medium22A 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?
Hard23You 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?
Hard24A 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.)
Medium25When migrating a large transformation from a legacy system to Snowflake, why is it recommended to prioritize ELT over ETL?
Medium26A 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?
Hard27Which function is best suited for converting a string representation of a JSON object into a VARIANT type within a transformation query?
Medium28A data engineer is transforming semi-structured event logs stored in a VARIANT column named event_data. The column contains an array of objects under the key 'items', where each object has a 'product_id' and a 'quantity'. The engineer needs to produce one row per product per event, with the event's timestamp and user ID repeated. Which SQL construct should be used to achieve this transformation?
Medium29A 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?
Hard30A 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?
Easy31A 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?
Medium32A 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?
Medium33A 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?
Medium34A 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?
Easy35A Data Engineer needs to transform semi-structured JSON data loaded into a VARIANT column named 'raw_data'. The goal is to flatten the 'items' array into individual rows while preserving the 'order_id' from the root level. Which function is the most efficient choice for this transformation?
Medium36Which TWO statements describe the benefits of using Snowflake's 'Change Tracking' for data transformation?
Medium37A data engineer is working with a table that has a VARIANT column containing JSON objects. Some objects have a key 'discount' with a numeric value, while others have it as a string, and some lack the key entirely. The engineer needs to produce a numeric column 'discount_amount' where missing keys are treated as 0 and string values are cast to numbers. Which expression correctly achieves this?
Hard38A 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?
Easy39Which approach is most efficient for transforming a large volume of data in Snowflake when the logic requires complex window functions and stateful processing?
Medium40A data engineer must transform semi-structured event data stored in a VARIANT column named 'event_payload'. The payload contains an array under the key 'tags', and each element of the array is an object with keys 'name' and 'score'. The engineer needs to produce one row per tag element, preserving the original event ID and extracting the 'name' and 'score' values. Which Snowflake construct should be used to achieve this transformation?
MediumOther domains
All DEA-C02 exam domains
Frequently asked questions
- What does the Data Transformation domain cover on the DEA-C02 exam?
- Be able to pick the right Snowflake transformation primitive for a given workload and write it correctly. The single most important thing: use QUALIFY to filter window-function results, and reserve Snowpark or vectorized UDFs for complex, stateful, large-volume logic.
- How many questions are in this domain?
- This page lists all 40 Data Transformation questions in the DEA-C02 question bank. The actual exam draws from this domain proportionally to its weighting in the official exam blueprint.
- What is the best way to practise this domain?
- Start with a short focused session (10 questions) to identify gaps, then work through explanations. Repeat with a longer session once the weak areas feel solid.
- Can I practise only Data Transformation questions?
- Yes — the session launcher on this page filters questions to this domain only. Choose any session length for inline explanations and scoring.