20+ practice questions focused on Data Transformation — one of the most tested topics on the SnowPro Advanced: Data Engineer exam. Each question includes a detailed explanation so you learn why the right answer is correct.
Start Data Transformation PracticeA data engineer is designing a pipeline using Snowflake Streams and Tasks to process CDC data. Which TWO characteristics correctly describe the behavior of a standard table stream when consumed by a DML statement? (Choose TWO)
Explanation: Understanding stream behavior is critical for building reliable data pipelines. A stream tracks changes to a source table by using an offset. When a DML statement consumes a stream within a transaction, the stream's offset advances only upon a successful commit. This ensures 'exactly-once' processing semantics, which prevents data loss or duplication during the transformation and ingestion phases.
Refer to the exhibit. A data engineer needs to produce a flattened result set where each row represents a single product item linked to its customer_id. Which approach using the FLATTEN function will correctly extract the 'prod' and 'qty' values for all orders?
Explanation: Nested arrays in JSON require multiple levels of flattening to reach the leaf nodes. The first FLATTEN call must target the 'orders' array, and a second subsequent FLATTEN call must target the 'items' array within each order. This recursive-style flattening allows the engineer to join the top-level customer_id with the deeply nested product details in a single relational view.
A data engineering team is evaluating the use of Dynamic Tables for a new transformation pipeline. Which THREE statements accurately describe the behavior and limitations of Dynamic Tables? (Choose THREE)
Explanation: Dynamic Tables simplify the engineering process by allowing declarative data modeling where Snowflake manages the refresh logic. They are designed for incremental updates based on a defined Target Lag. However, engineers must understand that they have specific limitations regarding the types of queries allowed and how they interact with other features like certain window functions or non-deterministic operations.
A data engineer needs to transform a flat table containing organizational hierarchy (employee_id, manager_id) into a format that shows the depth of each employee from the CEO. Which SQL construct should be used to perform this recursive transformation?
Explanation: Hierarchical data transformations are common in HR and financial systems. Snowflake provides specific syntax to handle these recursive relationships efficiently. While recursive CTEs are a standard SQL approach, the CONNECT BY clause is a specialized construct designed specifically for navigating parent-child relationships, offering built-in pseudocolumns like LEVEL to track depth within the hierarchy during the transformation process.
Refer to the exhibit. If the staging table contains a record with an ID that already exists in the target table and its status is 'DELETED', what is the final outcome for that record in the target table after the MERGE execution?
Explanation: The MERGE statement evaluates clauses in the order they are written. In this exhibit, the first 'WHEN MATCHED' clause checks for a specific condition ('DELETED'). Since this condition is met, the DELETE operation is prioritized and executed. This pattern is commonly used in CDC pipelines to handle upserts and deletes in a single, atomic DML operation.
+15 more Data Transformation questions available
Practice all Data Transformation questions1. Baseline your knowledge
Start with 10 questions to gauge your current understanding of Data Transformation. This tells you whether you need a concept refresher or just practice.
2. Review every explanation
For each question — right or wrong — read the full explanation. Understanding why an answer is correct is more valuable than knowing the answer itself.
3. Focus on exam traps
Data Transformation questions on the DEA-C02 frequently use trap wording. Look for subtle differences in answers that test your precision, not just general knowledge.
4. Reach 80% consistently
Do repeated sessions until you score 80%+ three times in a row. Then move to mixed-mode practice to test cross-topic recall under realistic conditions.
The exact number varies per candidate. Data Transformation is tested as part of the SnowPro Advanced: Data Engineer blueprint. Practicing with targeted Data Transformation questions ensures you can handle any format or difficulty that appears.
Yes. Courseiva provides free DEA-C02 practice questions across all exam topics and domains. The platform includes topic-based practice, mock exams, missed-question review, bookmarked questions, and readiness tracking — no account required.
Difficulty is subjective, but Data Transformation is a high-priority exam concept tested in multiple ways — direct recall, scenario analysis, and command-output interpretation. Consistent practice is the best way to build confidence.
Launch a full Data Transformation practice session with instant scoring and detailed explanations.
Start Data Transformation Practice →