DEA-C02 Data Transformation Practice Test 2 — 15 Questions

Question 1

Which TWO statements are true regarding the use of Streams and Tasks for data transformation in Snowflake?

Accepted Answer

A task can be configured to execute only when a stream contains data.. Streams provide a mechanism to track changes (CDC) in a table, capturing inserts, updates, and deletes. Tasks allow for the execution of SQL commands on a schedule or triggered by a stream. Together, they form a robust framework for incremental transformation. A task can be triggered by a stream containing data to ensure compute resources are only consumed when there is new data to process, optimizing cost and throughput.

Answer

Streams can track changes on views if the view is a secure view.

Answer

Streams automatically purge data once a task consumes it.

Answer

Streams are global objects and can be shared across accounts.

Question 2

Which THREE features are provided by Snowpark for data transformation?

Accepted Answer

Support for Python, Java, and Scala APIs.. Snowpark allows developers to write transformations using idiomatic code in Java, Scala, or Python. It translates this code into optimized SQL executed on Snowflake's compute engine. This provides the flexibility of procedural programming, the scalability of the Snowflake engine, and the ability to include custom libraries for complex data manipulation that would be difficult or impossible to achieve using standard SQL alone.

Answer

Client-side execution on the local machine.

Answer

Native support for training machine learning models on external GPUs.

Question 3

A Data Engineer is using the `FLATTEN` table function to parse a JSON array. If the array contains empty objects that must be ignored, which parameter should be set?

Accepted Answer

Set OUTER => FALSE. When using `FLATTEN`, the `RECURSIVE` and `MODE` parameters control how the function traverses the JSON structure. Specifically, to exclude null or empty nodes, the engine's behavior can be refined using the `OUTER` parameter. Setting `OUTER => FALSE` ensures that rows with no matches in the path are excluded from the output. Mastering these parameters is vital for cleaning messy semi-structured data before it is loaded into downstream relational tables.

Answer

Set RECURSIVE => FALSE

Answer

Set MODE => 'OBJECT'

Answer

Set PATH => 'NULL'

Question 4

When transforming data using User-Defined Functions (UDFs), which language choice offers the highest level of performance for complex, compute-intensive operations?

Accepted Answer

Java UDFs. Snowflake's Java and Python UDFs run in a highly optimized sandbox environment. However, Java UDFs are generally faster for CPU-bound, compute-intensive tasks because of the maturity of the Java Virtual Machine (JVM) in handling multithreading and garbage collection compared to Python's interpreter. For complex, repetitive calculations that occur millions of times, Java can offer significant speed advantages, though Python is often chosen for its ecosystem of data science libraries.

Answer

SQL UDFs

Answer

Python UDFs

Answer

JavaScript UDFs

Question 5

A data engineer needs to extract values from a VARIANT column named 'raw_data' which contains a JSON structure. One of the keys, 'user_id', is sometimes missing from the JSON. Which function or syntax should be used to safely handle missing keys and return a SQL NULL instead of an error or a literal 'null' string?

Accepted Answer

raw_data:user_id::string. Extracting data from VARIANT columns using the colon notation is the standard approach in Snowflake for semi-structured data transformation. This syntax automatically handles missing keys by returning a SQL NULL value, which is crucial for maintaining data integrity during ELT processes. Understanding the difference between a SQL NULL and a JSON null is fundamental for accurate data downstream.

Answer

JSON_EXTRACT_PATH_TEXT(raw_data, 'user_id')

Answer

GET(raw_data, 'user_id')

Answer

FLATTEN(raw_data:user_id)

Question 6

A data engineer is implementing a Change Data Capture (CDC) pipeline using Snowflake Streams and Tasks. The requirement is to process only new records from a source table into a transformed target table. Which THREE statements accurately describe the behavior or requirements of this transformation pattern?

Accepted Answer

A stream's offset is only advanced when it is used in a DML statement that successfully commits.. Snowflake Streams provide a powerful mechanism for tracking DML changes, and Tasks provide the scheduling. Together, they enable robust CDC pipelines. Engineers must understand how stream offsets are managed and how task execution interacts with stream data to ensure that data is processed exactly once and that the pipeline remains resilient to failures or idle periods.

Answer

Multiple streams on the same table will share the same offset and consumer position.

Answer

The stream captures the state of the row at the time the stream was created.

Question 7

A transformation pipeline requires calculating a running total of 'sales_amount' for each 'region' ordered by 'transaction_date'. Which window function configuration will achieve this result?

Accepted Answer

SUM(sales_amount) OVER (PARTITION BY region ORDER BY transaction_date). Window functions are a cornerstone of data transformation in Snowflake, allowing for complex analytical calculations across groups of rows. Correctly defining the partition and order clauses is essential for accurate reporting. In this scenario, the running total must be isolated by region and calculated chronologically to provide meaningful business insights over time.

Answer

SUM(sales_amount) OVER (ORDER BY transaction_date)

Answer

SUM(sales_amount) GROUP BY region, transaction_date

Answer

RUNNING_SUM(sales_amount) OVER (PARTITION BY region)

Question 8

A data engineer needs to handle 'late-arriving' data in a fact table. Which TWO strategies are most effective for managing this within Snowflake? (Choose two.)

Accepted Answer

Use a MERGE statement to update existing rows or insert new ones based on a business key.. Late-arriving data occurs when transactions are processed long after they occurred, potentially disrupting time-series transformations. Snowflake provides mechanisms like MERGE and Dynamic Tables that can handle these updates gracefully. Choosing the right strategy ensures that historical reports remain accurate and that the data warehouse reflects the true state of the business.

Answer

Use the OVERWRITE clause in the COPY command to refresh the entire table.

Answer

Disable micro-partitioning on the fact table to allow for easier updates.

Answer

Use a Secure View to filter out any data older than 24 hours.

Question 9

When transforming data using a 'CTAS' (Create Table As Select) statement, which characteristic of the new table is NOT inherited from the source table?

Accepted Answer

The clustering keys of the source table.. CTAS is a common pattern for creating intermediate transformation layers. While it copies data and basic structure, it does not duplicate the entire metadata environment of the source. Understanding these limitations is vital for data engineers to ensure that the new table has the necessary constraints, security, and performance optimizations required for production use.

Answer

The data types of the columns.

Answer

The names of the columns.

Answer

The actual data rows residing in the source.

Question 10

A data engineer needs to apply a masking policy to a column during a transformation. Which TWO statements about Snowflake Data Masking are correct? (Choose two.)

Accepted Answer

Masking policies are applied at query time, not when the data is stored.. Data masking is a key part of the transformation process when handling sensitive PII data. It allows engineers to protect data without physically altering the underlying storage. Understanding how masking policies interact with roles and transformations is essential for maintaining compliance while still allowing data scientists and analysts to perform their work.

Answer

Masking policies automatically encrypt the data on the physical disk.

Answer

Only the ACCOUNTADMIN role can create masking policies.

Answer

Once a masking policy is applied, the data can never be unmasked.

Question 11

When using the FLATTEN function in a transformation, what is the purpose of the 'LATERAL' keyword?

Accepted Answer

It allows the FLATTEN function to reference columns from preceding tables in the FROM clause.. The LATERAL keyword is a fundamental part of working with semi-structured data in Snowflake. It allows the FLATTEN function to be applied to each row of the source table individually. This 'row-wise' processing is what enables the transformation of nested arrays into a flat, relational structure while preserving the context of the parent record.

Answer

It improves the performance of the flatten operation by using parallel processing.

Answer

It limits the number of rows returned by the FLATTEN function to one per input row.

Answer

It ensures that only unique values are returned from the nested array.

Question 12

A data engineer needs to create a view that simplifies access to a complex transformation involving multiple joins and aggregations. Which type of view is most appropriate if the underlying data changes frequently and query performance is the top priority?

Accepted Answer

A Dynamic Table.. Choosing between standard views, materialized views, and dynamic tables is a core skill for Snowflake data engineers. In scenarios with frequent changes and high performance needs, the trade-offs between storage costs, compute costs for refreshes, and query latency must be carefully balanced to provide the best experience for downstream data consumers.

Answer

A standard Secure View.

Answer

A Materialized View.

Answer

An External Table.

Question 13

A Data Engineer is optimizing a large-scale transformation task. Which TWO of the following best practices should be implemented to minimize resource consumption and improve performance when performing heavy data transformations?

Accepted Answer

Utilize Snowflake Streams to capture only delta changes for incremental processing.. Optimizing transformations in Snowflake involves minimizing unnecessary data movement and leveraging the engine's strengths. Using incremental processing with Streams and Tasks prevents full table scans, while using Materialized Views for complex aggregations reduces compute overhead. These patterns are essential for maintaining performance as data volume grows, ensuring that the warehouse is not over-utilized by re-processing unchanged records or performing redundant, computationally expensive calculations on static datasets.

Answer

Perform complex transformations using Python UDFs instead of SQL expressions.

Answer

Avoid using Materialized Views to prevent unnecessary storage costs.

Answer

Increase warehouse size to '3XL' to ensure all transformations finish instantly.

Question 14

A Data Engineer is loading raw CSV files into Snowflake. The files occasionally contain malformed rows that cause the COPY INTO command to fail. Which THREE strategies should the engineer consider to ensure data transformation reliability?

Accepted Answer

Set the ON_ERROR option to 'CONTINUE' to skip only the bad rows.. Handling raw data requires defensive loading practices. Using the 'ON_ERROR' option allows control over the loading process, while validation functions like VALIDATE() provide deep insight into specific parsing errors. Finally, loading into a staging table first ensures that transformation logic is applied to clean data, preventing downstream pipeline failures. These strategies together provide a robust architecture that handles dirty data gracefully without halting the entire system.

Answer

Always load directly into final production tables for maximum speed.

Answer

Convert all files to Parquet before loading to avoid schema mismatches.

Question 15

A data engineer is building a transformation pipeline that must replace sensitive values in a VARCHAR column with a consistent pseudonym across multiple tables. The pseudonym must be deterministic for the same input value and must not be reversible without a secret. Which Snowflake function should be used to generate the pseudonym?

Accepted Answer

SHA2 with a salt concatenated to the input value. SHA2 with a salt provides a deterministic, one-way transformation suitable for pseudonymization. The salt ensures that identical input values yield identical pseudonyms, enabling joins across tables, while the cryptographic hash makes reversal infeasible without the salt. Other options either lack determinism, are reversible, or are not designed for deriving pseudonyms from input values.

Answer

MD5 with a randomly generated initialization vector

Answer

ENCRYPT with a symmetric key and the 'AES-256' algorithm

Answer

RANDOM with a fixed seed value for reproducibility