Data Transformation

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?

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?

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?

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?

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?

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

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?

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

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?

Which function is best suited for converting a string representation of a JSON object into a VARIANT type within a transformation query?

When migrating a large transformation from a legacy system to Snowflake, why is it recommended to prioritize ELT over ETL?

Which Snowflake feature is best suited for transforming data that is already loaded and requires periodic, complex SQL transformations without managing manual scheduling or streams?

A 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?

Which approach is most efficient for transforming a large volume of data in Snowflake when the logic requires complex window functions and stateful processing?

What is the primary benefit of using `CLONE` for data transformation and testing?

Which Snowflake feature helps in managing data transformation pipelines by providing a visual and programmatic way to handle task dependencies and state management?

Which TWO statements describe the benefits of using Snowflake's 'Change Tracking' for data transformation?

Which 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?

In a data transformation pipeline, what is the primary benefit of using 'Zero-Copy Cloning' for creating a development environment from production data?

A 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?

Which 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?

A 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?

A 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?

A 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?

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?

A 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?

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?

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?

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?

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?

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

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?

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?

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?

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?

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?

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?

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

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

A 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?