DEA-C02 Data Transformation Practice Test 5 — 15 Questions

Question 1

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

Accepted Answer

PARSE_JSON(). The PARSE_JSON function is the standard Snowflake tool for casting string data into the VARIANT format. Once in the VARIANT format, the data can be queried using path notation (e.g., col:field), enabling powerful transformations on nested or semi-structured data. This is a foundational operation in data ingestion pipelines where raw data often arrives as strings in CSV or Parquet files.

Answer

TO_VARIANT()

Answer

TRY_CAST(data AS VARIANT)

Answer

TO_JSON()

Question 2

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

Accepted Answer

ELT minimizes data movement and leverages Snowflake's compute scalability.. Snowflake's architecture is optimized for ELT (Extract, Load, Transform), where data is loaded in raw form and transformed using Snowflake's massive compute power. This leverages the cloud data warehouse's scalability, parallel processing, and columnar storage. By moving the transformation logic into the warehouse, you avoid the bottlenecks and data movement costs associated with traditional ETL, resulting in significantly faster and more scalable data pipelines.

Answer

ELT is required to use Snowflake's external stage integration.

Answer

Snowflake does not support traditional ETL transformation tools.

Answer

ETL is only possible using Snowflake's proprietary Java API.

Question 3

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?

Accepted Answer

Dynamic Tables. Dynamic Tables provide a declarative approach to data transformation. Users define the query that represents the transformation, and Snowflake manages the refresh frequency, dependencies, and incremental updates automatically. This reduces the administrative burden compared to managing manual Tasks and Streams. It is the modern standard for building ELT pipelines where the user describes the desired state, and the system ensures the target reflects the source over time.

Answer

Materialized Views

Answer

Stored Procedures

Answer

External Tables

Question 4

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?

Accepted Answer

MERGE INTO target USING source ON ... WHEN MATCHED THEN UPDATE.... The MERGE command is specifically designed for complex DML operations that combine insert, update, and delete actions in a single pass. By joining the source and target on a primary key, it ensures that new records are inserted while existing records are updated, preventing duplicates and ensuring data consistency. This is the standard method for slowly changing dimensions and incremental data synchronization in modern data warehousing pipelines.

Answer

INSERT INTO ... SELECT DISTINCT

Answer

UPDATE target SET ... FROM source

Answer

CREATE OR REPLACE TABLE target AS SELECT ...

Question 5

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

Accepted Answer

Using set-based SQL transformations in a materialized view or dynamic table.. For large-scale, complex transformations, using SQL within a View, Dynamic Table, or a CTAS (Create Table As Select) operation is highly efficient because it runs directly on the Snowflake compute engine. Snowflake's query optimizer is highly tuned for window functions and complex joins, allowing it to distribute these operations across all nodes in the warehouse, providing superior performance compared to row-by-row procedural processing.

Answer

Using a Python script to fetch data, process locally, and upload.

Answer

Using a single Stored Procedure with row-by-row cursor processing.

Answer

Using a User-Defined Function (UDF) for every column transformation.

Question 6

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

Accepted Answer

It provides a cost-effective way to create isolated development environments.. Cloning (Zero-Copy Cloning) allows an engineer to create a full copy of a table or database instantly without duplicating the underlying data. This is invaluable for testing transformations in a sandbox environment without incurring storage costs or taking up time for massive data movement. Any changes made in the cloned object are isolated from the original, ensuring that production pipelines remain unaffected during the development and validation of new transformation logic.

Answer

It automatically updates the source table with new transformations.

Answer

It allows for the conversion of Parquet files to internal tables.

Answer

It enables multi-region replication of data.

Question 7

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

Accepted Answer

Tasks with dependencies (DAG). A Directed Acyclic Graph (DAG) of tasks allows for complex, multi-step transformation sequences where each task can have dependencies on the success of prior tasks. This structure provides a clear, manageable workflow for production ELT. By centralizing the orchestration logic within Snowflake, engineers avoid the need for external workflow schedulers, simplifying the architecture and improving observability through standard Snowflake monitoring views and query history tools.

Answer

Streams

Answer

Stored Procedures

Answer

External Tables

Question 8

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

Accepted Answer

It allows for efficient incremental processing of only modified data.. Change Tracking is the underlying mechanism that enables Streams. It allows Snowflake to record which rows were inserted, updated, or deleted, making incremental transformations possible. Instead of scanning entire tables to identify changes, the engine can simply query the stream, which significantly reduces compute time and costs for ETL processes. This is critical for high-frequency data loading and incremental updates in modern data warehouses.

Answer

It eliminates the need for primary keys on tables.

Answer

It automatically cleans up stale data in the target table.

Answer

It is only available for tables in the Enterprise edition.

Question 9

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?

Accepted Answer

Using the PIVOT clause.. The `PIVOT` clause is designed specifically to rotate data from a row-based structure into a column-based format. By specifying the column to aggregate and the values to pivot, Snowflake transforms the data set, making it easier for BI tools to consume metrics directly. This is a common requirement when generating reports that represent time-series data or categorical breakdowns where metrics are required side-by-side rather than in long, narrow tables.

Answer

Using a series of CASE statements within a GROUP BY clause.

Answer

Using a self-join to correlate rows.

Answer

Using a stored procedure to iterate through rows.

Question 10

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

Accepted Answer

It provides a metadata-only copy that does not incur additional storage costs until the data is modified.. Zero-copy cloning is a unique Snowflake feature that allows for the creation of identical copies of tables, schemas, or databases without duplicating the underlying data. This is a game-changer for data engineers, as it enables safe, isolated testing of transformations using real-world data volumes without the time or cost associated with traditional data movement.

Answer

It ensures that any changes made in the clone are immediately reflected in the production source.

Answer

It automatically transforms the data into a more efficient format for testing.

Answer

It encrypts the data using a different key to ensure developer privacy.

Question 11

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?

Accepted Answer

FLATTEN. The FLATTEN function is specifically designed to transform semi-structured data into a relational format by producing a lateral view of array elements. By using it in a LATERAL join, the engineer can correlate the parent 'order_id' with each exploded array element effectively. This is a critical pattern in Snowflake for normalizing JSON structures before downstream analytics, ensuring that hierarchical data becomes queryable by standard SQL BI tools.

Answer

JSON_EXTRACT_PATH_TEXT

Answer

OBJECT_CONSTRUCT

Answer

ARRAY_TO_STRING

Question 12

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?

Accepted Answer

Dynamic Tables. Dynamic Tables provide a declarative approach to data engineering. By defining the target table based on a SQL query, Snowflake manages the dependency graph and incrementally updates the data. This abstracts away the complexity of managing tasks, streams, and manual refresh schedules, making it the most modern and efficient way to handle continuous data transformation in a pipeline.

Answer

Tasks with Streams

Answer

Materialized Views

Answer

Stored Procedures

Question 13

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?

Accepted Answer

Use the LATERAL FLATTEN function on event_data:items to expand the array into separate rows.. LATERAL FLATTEN is the idiomatic Snowflake construct for expanding semi-structured arrays into rows. When applied to event_data:items, it produces one row per array element, allowing direct access to nested attributes like product_id and quantity. Other options either aggregate, parse, or manually iterate, none of which efficiently achieve the required row-level expansion while preserving parent context.

Answer

Employ a recursive common table expression (CTE) to iterate over the array elements and output one row per element.

Answer

Use the PARSE_JSON function to convert event_data:items into a relational table with columns for product_id and quantity.

Answer

Apply the ARRAY_AGG function to event_data:items to concatenate the array elements into a single string.

Question 14

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?

Accepted Answer

LATERAL FLATTEN(input => event_data:items). To expand a nested array into multiple rows while keeping the original columns, a lateral join with FLATTEN is required. LATERAL FLATTEN allows the FLATTEN function to access the VARIANT column from the preceding table and returns one row per array element, which is exactly the needed behavior. The other functions either aggregate, construct objects, or parse strings, none of which achieve row expansion.

Answer

ARRAY_AGG(event_data:items)

Answer

OBJECT_CONSTRUCT('items', event_data:items)

Answer

PARSE_JSON(event_data:items)

Question 15

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?

Accepted Answer

Use the FLATTEN function in the FROM clause with the INPUT argument set to event_payload:tags.. Flattening a semi-structured array into rows is a core transformation in Snowflake. The FLATTEN table function directly expands array elements, producing one row per element and enabling easy extraction of nested fields. It preserves the parent row context, so the event ID remains available. Other functions like PARSE_JSON or SPLIT_TO_TABLE do not achieve the same row expansion for VARIANT arrays.

Answer

Use the SPLIT_TO_TABLE function with a delimiter of comma on the string representation of the tags array.

Answer

Use the PARSE_JSON function on event_payload:tags and then apply a lateral join with a VALUES clause.

Answer

Use the GET_PATH function to extract the array, then use a recursive CTE to iterate over its elements.