Snowflake SnowPro Advanced: Data Engineer (DEA-C02) · 25% practice allocation

Snowflake DEA-C02: Data Transformation Practice Questions

5 free sample questions from the Data Transformation domain, with answers and explanations. The full DEA-C02 bank has 262 questions across 5 domains. Questions are scenario-based and written from the official Snowflake exam guide.

1.

A Snowpark Python UDF must use a third-party library (e.g., 'scikit-learn') during transformation. Which TWO are valid ways to make the dependency available to the UDF? (Select TWO)

  • A. Specify the package in the packages= argument when registering the UDF, drawing from the Snowflake Anaconda channel✓ Correct
  • B. Grant the UDF the EXTERNAL ACCESS privilege on the public PyPI endpoint by default
  • C. Upload a .zip/.whl of the library to a stage and reference it with imports= when registering the UDF✓ Correct
  • D. Add the library name in a COMMENT on the UDF so Snowflake resolves it automatically
  • E. Run 'pip install scikit-learn' inside the UDF handler at execution time
Explanation

Snowflake resolves declared packages from the curated Snowflake Anaconda channel via packages=, and custom or unavailable libraries can be staged and referenced through imports=. UDF sandboxes have no outbound internet by default, so pip install at runtime fails. External access integrations must be explicitly created and granted and are not enabled by default, and they are for network calls rather than dependency resolution. COMMENT is purely metadata.

2.

A data engineer loads a VARIANT column RAW_EVENT that contains a JSON array under the key "items". Each element has "sku" and "qty". Which query correctly produces one row per item with the SKU as a VARCHAR and the quantity as a NUMBER?

  • A. SELECT PARSE_JSON(RAW_EVENT):items[0]:sku::STRING AS sku, PARSE_JSON(RAW_EVENT):items[0]:qty::NUMBER AS qty FROM events;
  • B. SELECT RAW_EVENT:items:sku::STRING AS sku, RAW_EVENT:items:qty::NUMBER AS qty FROM events;
  • C. SELECT f.value:sku::STRING AS sku, f.value:qty::NUMBER AS qty FROM events, LATERAL FLATTEN(input => RAW_EVENT:items) f;✓ Correct
  • D. SELECT f.key::STRING AS sku, f.value::NUMBER AS qty FROM events, LATERAL FLATTEN(input => RAW_EVENT) f;
Explanation

LATERAL FLATTEN over the array path RAW_EVENT:items emits one row per array element, and f.value holds each element, so f.value:sku and f.value:qty extract the fields. Option 2 is wrong because you cannot dot-path into an array to get scalar fields; it returns NULL. Option 3 flattens the top-level object, so key/value are the object's attribute names and values, not SKU/qty. Option 4 uses PARSE_JSON on an already-VARIANT column (unnecessary and an error if not a string) and only ever returns the first array element rather than one row per item.

3.

A MERGE statement on a target table produces the error "Duplicate row detected during DML action". Which TWO approaches will resolve the issue while keeping the merge semantics correct? (Select TWO)

  • A. Set the session parameter ERROR_ON_NONDETERMINISTIC_MERGE = FALSE to allow an arbitrary matching row to be used✓ Correct
  • B. Wrap the MERGE inside BEGIN TRANSACTION / COMMIT
  • C. Add a cluster key on the merge join column of the target table
  • D. Deduplicate the source so at most one source row matches each target row, e.g. with QUALIFY ROW_NUMBER() = 1✓ Correct
  • E. Change the target table to a transient table
Explanation

The error is raised because multiple source rows match a single target row, making the update nondeterministic. Deduplicating the source (option 1) fixes it properly. Setting ERROR_ON_NONDETERMINISTIC_MERGE = FALSE (option 2) suppresses the error and lets Snowflake pick one matching row arbitrarily — a valid but less rigorous resolution. Clustering affects pruning/performance only, not duplicate detection. A transaction wrapper changes atomicity, not matching semantics. Table type (transient vs permanent) is irrelevant to MERGE determinism.

4.

Which TWO statements accurately describe Snowflake secure views used as a transformation/sharing layer? (Select TWO)

  • A. Secure views cannot be shared through a Snowflake share
  • B. A secure view's definition is hidden from users who do not own it, including via GET_DDL and INFORMATION_SCHEMA✓ Correct
  • C. Secure views automatically apply row access policies to the base tables they reference
  • D. Secure views are always materialized to prevent repeated base-table scans
  • E. Certain query optimizations that could expose underlying data are bypassed, so secure views can be slower than regular views✓ Correct
Explanation

Secure views hide their SQL text from non-owners and disable optimizations (such as pushing user expressions ahead of security predicates) that could leak data, which can reduce performance. They do not create or apply row access policies automatically — those are separate objects attached to tables or views. Secure views are in fact required for sharing views in a Snowflake share, so 'cannot be shared' is false. Secure views are not materialized; that is a separate object type.

5.

A dbt project runs on Snowflake with a warehouse that is also used by BI users. The team notices long-running incremental models block dashboards. Which TWO dbt/Snowflake configurations best address this without changing model logic? (Select TWO)

  • A. Enable multi-cluster (auto-scale) mode on the shared warehouse so concurrent BI queries get their own cluster✓ Correct
  • B. Set query_tag in the dbt profile so Snowflake deprioritizes dbt queries
  • C. Increase dbt's threads setting to 32 on the shared warehouse
  • D. Set materialized='ephemeral' on all incremental models
  • E. Configure a separate, dedicated virtual warehouse for dbt runs using the snowflake_warehouse model/profile config✓ Correct
Explanation

Isolating dbt on its own warehouse eliminates resource contention entirely, and enabling multi-cluster scaling on the shared warehouse adds clusters so queued BI queries run concurrently. Ephemeral materialization inlines model SQL as CTEs, which would break incremental logic and can make downstream queries heavier. Raising thread count increases concurrent dbt queries and worsens contention. Query tags are metadata for monitoring/attribution and do not influence scheduling priority.

Test yourself across every DEA-C02 domain

The free DEA-C02 practice test mixes about 20 questions weighted like the exam. The full 262-question bank adds instant grading, flashcards and a timed mock exam.

Take the free DEA-C02 practice testPractice the full bank

Study this domain

Data Transformation study guide (DEA-C02) →

Other DEA-C02 domains

← All Snowflake SnowPro Advanced: Data Engineer study resources