Snowflake · DEA-C02
SnowPro Advanced: Data Engineer (DEA-C02) Practice Tests
Validates advanced expertise in designing, building, optimizing, and managing complex data pipelines within the Snowflake platform. Covers data movement, transformation, performance optimization, storage, and data governance at a production-level depth.
Exam Details
Practice Questions
597
≈ 5 practice exams
Duration
115 minutes
Passing Score
750/1000
Difficulty
ProfessionalLast Updated
Jun 2026
Topics Covered
Data MovementData TransformationPerformance OptimizationStorage & Data ProtectionData GovernanceSnowpark & UDFsContinuous Data Pipelines
SnowPro Advanced: Data Engineer (DEA-C02) Practice Exam Preparation
Use this DEA-C02 practice exam to prepare for SnowPro Advanced: Data Engineer (DEA-C02) with realistic questions, detailed explanations, and focused study modes. The practice bank includes 597 questions for Snowflake DEA-C02, so you can review the exam steadily instead of relying on one long cram session.
As you practice, pay extra attention to recurring topics such as Data Movement, Data Transformation, Performance Optimization, Storage & Data Protection, and Data Governance. Start with short sessions to identify weak areas, then move into timed quizzes once your accuracy is consistent.
The explanations are especially useful when you want to connect exam wording to the responsibilities and scenarios described in the official certification guidance. Use the free preview first, then unlock the full question bank when you are ready to build a complete study routine.
Exam Domain Breakdown
Data Movement26%
Data Transformation25%
Performance Optimization21%
Storage and Data Protection14%
Data Governance14%
Exam Overview
The SnowPro Advanced: Data Engineer (DEA-C02) is Snowflake's role-based advanced certification that validates production-level expertise in designing, building, optimizing, and managing complex data engineering workflows on the Snowflake platform. Released on February 18, 2025 as the successor to DEA-C01, this exam tests a candidate's ability to source data from data lakes, APIs, and on-premises systems; build near real-time streaming pipelines; execute ELT/ETL transformations at scale; and apply advanced performance tuning across Snowflake's compute and storage architecture. The 2025 revision places greater emphasis on streaming technologies, automation, data observability, and multi-cloud interoperability compared to its predecessor.
The certification spans five weighted content domains: Data Movement (26%), Data Transformation (25%), Performance Optimization (21%), Storage and Data Protection (14%), and Data Governance (14%). It covers advanced Snowflake-native capabilities including Snowpark for Python/Java/Scala-based transformations, User-Defined Functions (UDFs) and UDTFs, continuous data pipelines using Streams and Tasks, dynamic data masking, row-level security policies, and Snowflake's data sharing and replication features. Achieving this credential demonstrates that a practitioner can operate confidently in enterprise-grade production environments.
Who Should Take This Exam
This certification is designed for experienced data engineers who work with Snowflake in production environments and wish to formally validate their advanced expertise. The ideal candidate has at least two years of hands-on Snowflake data engineering experience and is comfortable designing end-to-end pipelines, troubleshooting performance bottlenecks, and implementing governance frameworks at scale.
Relevant job titles include Data Engineer, Senior Data Engineer, Cloud Data Engineer, Data Platform Engineer, and Cloud Data Architect. Professionals working in data-intensive industries such as financial services, retail, healthcare, media, and technology who are responsible for building and maintaining Snowflake-based data infrastructure will find this credential directly applicable to their day-to-day work.
Prerequisites
Snowflake does not list any hard prerequisites that must be completed before registering for DEA-C02, but strongly recommends holding an active SnowPro Core certification (COF-C02) as a foundational baseline. The SnowPro Core validates general Snowflake platform knowledge, and candidates who lack it will likely find the advanced-level content significantly more challenging.
Beyond the Core certification, Snowflake recommends a minimum of two years of hands-on, production-level experience in a data engineering role using Snowflake. Candidates should be proficient in SQL and at least one of Python, Java, or Scala for Snowpark-based transformations, and should have practical experience with cloud platforms (AWS, Azure, or GCP), data ingestion tools such as Snowpipe, and Snowflake's native pipeline constructs including Streams, Tasks, and Dynamic Tables.
Exam Format
The DEA-C02 exam consists of 65 scored questions delivered in 115 minutes, yielding a time allocation of approximately 1 minute 46 seconds per question. Question types include multiple choice (single correct answer), multiple select (two or more correct answers), and interactive question types such as drag-and-drop and matching scenarios that simulate real-world data engineering decisions. There is no announced beta or unscored survey question pool; Snowflake states that results are available immediately upon exam completion.
The exam is delivered online via remote proctoring through Pearson VUE, or at an authorized Pearson VUE testing center. The cost is $375 USD per attempt (approximately $300 USD in India with a regional discount). Scoring uses a scaled score from 100 to 1000, with a passing threshold of 750. Candidates who do not pass must wait a minimum of seven days before retaking the exam. The certification is valid for two years, after which holders can renew via the dedicated recertification exam (DEA-R02).
Skills Measured
- 1.Data Movement (26%): Covers ingestion patterns including bulk loading with COPY INTO, continuous micro-batch ingestion via Snowpipe, Kafka connector configurations, external stages (S3, Azure Blob, GCS), and change data capture (CDC) strategies. Also includes data replication across accounts and regions using Snowflake Replication and Failover Groups.
- 2.Data Transformation (25%): Validates the ability to design and implement ELT pipelines using SQL and Snowpark (Python, Java, Scala), write and deploy User-Defined Functions (UDFs) and User-Defined Table Functions (UDTFs), leverage Dynamic Tables for declarative pipeline definitions, and orchestrate transformations using Streams and Tasks, including DAG-based task graph management.
- 3.Performance Optimization (21%): Tests knowledge of virtual warehouse sizing and multi-cluster configurations, query profiling with the Query Profile UI and EXPLAIN plans, clustering keys and Automatic Clustering, result caching and query acceleration service, partition pruning strategies, and identifying and resolving performance anti-patterns such as data spilling and inefficient joins.
- 4.Storage and Data Protection (14%): Covers Snowflake's storage architecture including micro-partitions and columnar storage, Time Travel configuration and usage (up to 90 days for Enterprise+), Fail-Safe for disaster recovery, data cloning (zero-copy cloning), table and schema types (permanent, transient, temporary), and managing storage costs through lifecycle policies.
- 5.Data Governance (14%): Assesses implementation of column-level security via Dynamic Data Masking and External Tokenization, row-level security using Row Access Policies, object tagging and data classification, Snowflake Access History and Data Lineage for auditing, account usage and information schema views for governance reporting, and designing compliant data sharing arrangements using Secure Views and the Snowflake Marketplace.
Study Tips
- Enroll in the official DEA-C02 Study Guide on learn.snowflake.com (requires a free Snowflake Community account). This guide provides the complete domain-by-domain objectives, links to relevant Snowflake documentation pages, and curated video resources — it is the single most authoritative preparation resource.
- Take the official DEA-P02 practice exam available through the Snowflake learning portal. This Snowflake-authored practice test mirrors the format and difficulty of the real exam, including interactive question types, and is the most reliable signal of exam readiness.
- Build hands-on pipelines in a free Snowflake trial account focusing on the two highest-weighted domains (Data Movement and Data Transformation). Practice loading data with Snowpipe and the Kafka connector, then transform it using both SQL Tasks/Streams and Snowpark DataFrames in Python to internalize both approaches.
- Study the Query Profile tool deeply for the Performance Optimization domain. Run intentionally inefficient queries (missing clustering keys, large cross-joins, queries that spill to disk), capture the Query Profile output, and practice diagnosing the bottleneck — this mirrors the diagnostic scenarios the exam presents.
- Review Snowflake's governance features end-to-end by implementing a complete governance setup in your trial account: apply Dynamic Data Masking policies to PII columns, create a Row Access Policy, tag objects using object tagging, and query ACCOUNT_USAGE.ACCESS_HISTORY to verify audit trails. Hands-on configuration of these features is consistently tested.
- Read the official Snowflake documentation changelog and release notes for features introduced or updated in 2024–2025, particularly around Dynamic Tables, Apache Iceberg table support, and Snowpark Container Services. The DEA-C02 refresh specifically incorporates newer platform capabilities not covered in DEA-C01.
- After studying each domain, attempt targeted questions from the VMExam DEA-C02 question bank or similar third-party practice sets to identify weak areas. Cross-reference every incorrect answer against the official Snowflake documentation rather than relying solely on third-party explanations.
Career Benefits
Earning the DEA-C02 credential signals to employers that a candidate can independently own and operate complex, production-scale Snowflake data platforms — a skill set in high demand as enterprises increasingly consolidate their data infrastructure on cloud-native platforms. Certified Snowflake data engineers in the United States report average annual salaries of approximately $125,000, with the range spanning roughly $101,000 at the 25th percentile to $156,000 at the 75th percentile, and senior roles reaching $190,000 or more. Industry analyses suggest that Snowflake-specific certification correlates with 20–30% higher compensation compared to equivalent roles without platform credentials.
The DEA-C02 positions holders for senior individual contributor and lead engineer roles across finance, retail, healthcare, and technology sectors where Snowflake is a primary data platform. Compared to broader cloud certifications such as AWS Certified Data Engineer or Google Professional Data Engineer, the SnowPro Advanced: Data Engineer is narrower in scope but commands a premium in organizations already standardized on Snowflake. It complements rather than competes with cloud-provider certifications and is frequently listed alongside them in job postings for Senior Data Engineer, Cloud Data Architect, and Data Platform Lead roles.
Sample Questions
5 sample questions with answers and explanations. The full bank has 597 questions, enough for 5 full-length practice exams.
Preview — answers shown1. A data engineer at Fabrikam implements a Python UDAF to calculate a cumulative difference metric across session events. The handler class is registered in Snowflake and the merge method is implemented as follows: def merge(self, other_state): self._total = self._total - other_state._total During parallel execution on a multi-cluster virtual warehouse, the UDAF returns inconsistent results across repeated runs on identical datasets. What is the root cause of this behavior? (Select one!)
AThe aggregate_state property is not returning a pickle-serializable object, causing Snowflake to silently discard partial states before the merge method is invoked
BPython UDAF merge methods must call self.accumulate() internally to process each incoming state value rather than directly modifying self._total
CSnowflake multi-cluster warehouses serialize UDAF execution across cluster nodes, so the inconsistency indicates a warehouse configuration error rather than a code defect
DThe merge method uses subtraction, which is neither commutative nor associative — Snowflake merges partial aggregate states from different compute nodes in a non-deterministic order, producing different results depending on which partial states are combined first
Explanation
Snowflake distributes UDAF computation by partitioning input data across multiple nodes and calling accumulate on each partition independently, then calling merge to combine the resulting partial states. The order in which partial states are merged is non-deterministic — Snowflake does not guarantee a fixed merge sequence. For correct distributed aggregation, the merge operation must be both commutative (order of operands does not matter) and associative (grouping of operands does not matter). Subtraction fails both properties: subtracting state B from state A produces a different result than subtracting state A from state B. When node counts or data partition boundaries change between runs, different merge orderings produce different final values. The accumulate method is called per input row within a partition, not during merge. The aggregate_state property must be pickle-serializable for Snowflake to serialize and transfer partial states between nodes, but this is a separate requirement from merge correctness.
2. A data engineering team at Contoso manages a 50 TB SALES_TRANSACTIONS table that is currently unclustered. Query profiling reveals that 95% of queries filter on both REGION and TRANSACTION_DATE, and 60% of queries additionally filter on PRODUCT_CATEGORY. Compilation output shows a very high ratio of micro-partitions scanned to total micro-partitions. The team wants to define a clustering key that maximizes partition pruning across all common query patterns while minimizing unnecessary reclustering overhead. Which clustering key definition should they apply? (Select one!)
ACLUSTER BY (TRANSACTION_ID) to cluster on the primary unique identifier for deterministic ordering
BCLUSTER BY (REGION, TRANSACTION_DATE, PRODUCT_CATEGORY) to align with all three dominant filter columns
CCLUSTER BY (PRODUCT_CATEGORY) to cluster on the highest-selectivity categorical column
DCLUSTER BY (TRANSACTION_DATE) to cluster on the single most universally used filter column
Explanation
Defining the clustering key as (REGION, TRANSACTION_DATE, PRODUCT_CATEGORY) directly aligns with all three query filter patterns identified in profiling, providing the maximum pruning benefit across queries using one, two, or all three of those columns. Snowflake's automatic clustering continuously maintains the physical ordering of micro-partitions on the defined key columns so that qualifying partitions are co-located and irrelevant ones are skipped at scan time. Clustering by TRANSACTION_ID yields no query benefit because it is a high-cardinality unique identifier — each micro-partition would contain nearly unique values with no range overlap between partitions, eliminating any pruning advantage. Clustering only on PRODUCT_CATEGORY ignores the two most frequently used filter columns and would prune poorly for the dominant REGION and TRANSACTION_DATE query pattern. Clustering only on TRANSACTION_DATE misses the equally pervasive REGION filter and leaves most queries without multi-column pruning.
3. A data engineer at Northwind Insurance accidentally dropped a critical permanent production table 10 days ago. The table had the default TIME_TRAVEL retention of 1 day. The engineer attempts to restore the table using Time Travel commands but receives an error indicating the data is outside the Time Travel window. Which two statements accurately describe the remaining data protection behavior and recovery options? (Select two!)
Multiple correct answersAPermanent tables include a 7-day Fail-safe period that begins immediately after the Time Travel retention window expires
BThe engineer can query the Fail-safe data directly using the AT or BEFORE clause to retrieve rows from before the table was dropped
CFail-safe data recovery is performed exclusively by Snowflake Support and cannot be self-initiated by the customer
DTransient and temporary tables have the same 7-day Fail-safe period as permanent tables
EFail-safe recovery is guaranteed to complete within 24 hours once a Snowflake Support case is opened
Explanation
Permanent tables in Snowflake receive a 7-day Fail-safe recovery window that begins immediately after the Time Travel retention period expires. With a 1-day Time Travel window, the table entered Fail-safe on day 1 after the drop — and since 10 days have elapsed, both the Time Travel and Fail-safe windows are exhausted for this scenario. Fail-safe data cannot be accessed directly by customers — the AT and BEFORE clauses are exclusively for the Time Travel window and have no visibility into Fail-safe storage. Recovery of Fail-safe data is performed solely by Snowflake Support; customers must open a support case to request recovery and have no self-service mechanism. Transient and temporary tables have a Fail-safe period of 0 days, providing no Fail-safe protection at all, which makes them unsuitable for data that requires disaster recovery guarantees. Snowflake does not publish or guarantee a specific completion timeframe for Fail-safe recovery cases.
4. A data engineer at Litware Inc. created a standard stream on the ORDERS table three days ago to capture changes for a downstream ETL process. The ORDERS table has DATA_RETENTION_TIME_IN_DAYS set to 1. The stream has not yet been consumed. When the engineer attempts to query the stream, they receive an error indicating the stream cannot be used. Which condition explains the stream's current state? (Select one!)
AThe stream is stale because its offset has fallen behind the source table's data retention period, making it unusable
BThe stream was automatically dropped because it was not consumed within 72 hours of creation
CThe stream is invalid because standard streams require the source table to have at least 7 days of data retention
DThe stream expired because Snowflake streams have a fixed 24-hour time-to-live independent of table retention settings
Explanation
A Snowflake stream becomes stale when its internal offset falls behind the source table's DATA_RETENTION_TIME_IN_DAYS boundary. Since the ORDERS table retains only 1 day of historical data and the stream has not been consumed for 3 days, the stream's offset now points to a historical position that no longer exists within the active retention window, rendering the stream stale (STALE=TRUE) and unusable. Streams do not have a fixed 24-hour time-to-live independent of the source table's retention setting — staleness is entirely determined by whether the stream's offset falls outside the source table's retention period. There is no minimum retention requirement to create a standard stream. Snowflake does not automatically drop streams due to inactivity; a stale stream must be manually recreated with a new offset.
5. A data engineer at Adatum is designing a cost-efficient storage strategy for two Snowflake tables. The TRANSACTIONS table is a production table that must support Time Travel queries up to 90 days in the past. The ETL_STAGING table is truncated and reloaded every day and requires no historical data access or disaster recovery protection. Which two table configurations meet these requirements while minimizing storage costs? (Select two!)
Multiple correct answersACreate TRANSACTIONS as a permanent table with DATA_RETENTION_TIME_IN_DAYS = 90
BCreate TRANSACTIONS as a transient table with DATA_RETENTION_TIME_IN_DAYS = 90
CCreate ETL_STAGING as a permanent table with DATA_RETENTION_TIME_IN_DAYS = 0
DCreate ETL_STAGING as a transient table with DATA_RETENTION_TIME_IN_DAYS = 0
ECreate ETL_STAGING as a temporary table with DATA_RETENTION_TIME_IN_DAYS = 0
Explanation
A permanent table is required for 90-day Time Travel because transient and temporary tables support a maximum of only 1 day of Time Travel retention. Only permanent tables on Enterprise edition or higher can be configured with DATA_RETENTION_TIME_IN_DAYS up to 90. A transient table for ETL_STAGING with DATA_RETENTION_TIME_IN_DAYS = 0 eliminates both Time Travel storage overhead and the mandatory 7-day Fail-safe period that Snowflake automatically applies to all permanent tables. Since ETL_STAGING is reloaded daily with no recovery requirements, removing both Time Travel and Fail-safe storage eliminates avoidable costs entirely. Creating ETL_STAGING as a permanent table with 0-day retention removes Time Travel overhead but still incurs 7 days of Fail-safe storage for all data changes, adding unnecessary cost. Creating ETL_STAGING as a temporary table would eliminate storage overhead but temporary tables are session-scoped and are automatically dropped when the session ends, making them unsuitable for a staging table shared across multiple pipeline sessions and scheduled executions.
More Snowflake Practice Exams
SnowPro Advanced: Administrator (ADA-C02)
ADA-C02 · 600 questions
SnowPro Advanced: Architect (ARA-C01)
ARA-C01 · 592 questions
SnowPro Advanced: Data Analyst (DAA-C01)
DAA-C01 · 600 questions
SnowPro® Advanced: Data Scientist (DSA-C03)
DSA-C03 · 600 questions
SnowPro Advanced: Security Engineer (SEA-C01)
SEA-C01 · 550 questions
SnowPro Core Certification (COF-C03)
COF-C03 · 592 questions
$17.99
One-time access to this exam
597 questions (5 practice exams' worth)
Unlimited timed exam simulations
Yours forever — no subscription
Detailed explanations
Free preview stays available