DEA-C02 Data Transformation Practice Question

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✗

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

  Why it's wrong here

  Fetching data to an external client for processing is extremely inefficient due to network latency and the limitation of local compute resources. This approach bypasses Snowflake's parallel processing engine and increases the risk of data inconsistency and security issues during the data transfer process.

• ✗

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

  Why it's wrong here

  Row-by-row processing, or 'RBAR' (Row By Agonizing Row), is an anti-pattern in Snowflake. It prevents the engine from utilizing columnar storage and parallel execution capabilities, leading to slow performance and high cost. Set-based operations are always preferred for large-scale data transformations in any relational system.

• ✓

  Using set-based SQL transformations in a materialized view or dynamic table.

  Why this is correct

  Set-based transformations leverage Snowflake's query optimizer to execute complex logic in parallel across the cluster. This is the foundation of efficient ELT, as it pushes the transformation logic down to the data rather than moving data to the logic, maximizing performance and minimizing latency.

• ✗

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

  Why it's wrong here

  While UDFs are useful, calling them for every column in every row can introduce significant overhead compared to native SQL expressions. Complex window functions are best handled by native SQL operators, which are optimized by the engine, whereas UDFs may impede certain query optimizations.