Snowflake

SnowPro Advanced: Data Engineer Complete Guide

2026-03-26
Updated: 2026-10-15
NicheeLab Editorial Team
Table of contents
  1. Exam Overview
  2. Exam Domains and Weighting
  3. Streams & Tasks
  4. Snowpipe & Snowpipe Streaming
  5. Dynamic Tables
  6. Semi-structured Data
  7. Performance Optimization Tips
  8. Study Strategy
  9. Data Protection Essentials
  10. Try a Sample Question
  11. Frequently Asked Questions

The SnowPro Advanced: Data Engineer Certification validates advanced expertise in building, optimizing, and operating data pipelines on Snowflake. It focuses on topics that map directly to day-to-day data engineering work, including Streams, Tasks, Snowpipe, Dynamic Tables, and semi-structured data. This article covers the exam overview, scoring domains, key study points for each topic, and a strategy to pass.

Exam Overview

ItemDetails
Exam codeDEA-C02
Questions65 (multiple choice, multiple select, and interactive)
Duration115 minutes
Passing score750 / 1000
Exam fee$375 USD
PrerequisitesValid SnowPro Core certification
DeliveryPearson VUE (test center or online proctored)
Certification validity2 years
Recommended experience2+ years of hands-on data engineering on Snowflake

Exam Domains and Weighting

These are the five domains on the English official exam page and Study Guide (DEA-C02, last updated March 6, 2026).

DomainWeightKey topics
1.0 Data Movement28%Loading and ingesting data (file formats, INFER_SCHEMA, stages and storage integrations), troubleshooting ingestion errors, continuous pipelines (Snowpipe, Snowpipe Streaming, streams, tasks, dynamic tables), connectors (Kafka, Spark, Python), data sharing and the Marketplace, external, Iceberg, and hybrid tables
2.0 Performance Optimization19%Troubleshooting underperforming queries; warehouse configuration (scale up vs. scale out, multi-cluster, Snowpark-optimized); clustering, materialized views, search optimization, query acceleration, and caching; ACCOUNT_USAGE and resource monitors; monitoring continuous pipelines (task history, alerts, data quality)
3.0 Storage and Data Protection14%Time Travel (including its impact on streams) and Fail-safe, cross-region and cross-cloud replication, analyzing micro-partitions with system functions (clustering depth, clustering keys), creating development environments with Time Travel and cloning
4.0 Data Governance14%Object tagging and data classification, data lineage, data quality monitoring, column-level security (Dynamic Data Masking, external tokenization), row access, projection, and aggregation policies, Data Clean Rooms
5.0 Data Transformation25%UDFs (Snowpark, SQL, JavaScript, UDTFs, UDAFs) and external functions, stored procedures and transactions, processing semi-structured and unstructured data (directory tables), Snowflake Cortex features, data transformations with Snowpark, development workflows with Notebooks, Git integration, and dbt Projects

As of October 2026, the Japanese exam page and Study Guide still show the earlier weights (Data Movement 26%, Performance Optimization 21%; the other three domains are the same).

Streams & Tasks

Streams are Snowflake's Change Data Capture (CDC) feature, tracking INSERT/UPDATE/DELETE operations against a table. Combined with Tasks, you can build schedule-based data pipelines.

Stream typeTracksUse case
StandardINSERT / UPDATE / DELETEGeneral-purpose CDC
Append-onlyINSERT onlyAppend-only tables (logs, events)
Insert-only (for External Tables)New file additionsDetecting new files on an External Table

Tasks execute SQL on a schedule (CRON expression or minute-based interval) or in response to a Stream containing data (SYSTEM$STREAM_HAS_DATA). You can define parent-child relationships as a task tree to build pipelines in a DAG (directed acyclic graph) structure.

Snowpipe & Snowpipe Streaming

Snowpipe is a serverless data ingestion feature that detects new files arriving in a stage and automatically runs COPY INTO.

  • Auto-ingest Snowpipe: Auto-ingest triggered by cloud event notifications (AWS SQS, Azure Event Grid, GCS Pub/Sub)
  • REST API Snowpipe: Manually triggered by calling the insertFiles REST API
  • Snowpipe Streaming: Row-level streaming ingestion using the Snowflake Ingest SDK (no files required, low latency)
ComparisonAuto-ingest SnowpipeSnowpipe Streaming
TriggerFile arrival eventSDK API call
Latency1 to several minutesSeconds
Requires files?Yes (files on a stage)No (row-level inserts)
Billing modelServerless creditsServerless credits

Dynamic Tables

Dynamic Tables let you define data pipelines declaratively on Snowflake. You define the target table with a SQL query, and it automatically refreshes as the source data changes.

  • Control refresh cadence with the TARGET_LAG parameter (e.g. 1 MINUTE / 1 HOUR / DOWNSTREAM)
  • Specifying DOWNSTREAM aligns the refresh with the lag of downstream Dynamic Tables
  • You can chain multiple Dynamic Tables to build a medallion architecture (Bronze → Silver → Gold)
  • Simpler to define and maintain pipelines compared to Streams + Tasks

Semi-structured Data

Snowflake stores semi-structured data such as JSON, XML, Avro, Parquet, and ORC in the VARIANT type, and you can query it with SQL.

  • Storage: Convert with PARSE_JSON and store in VARIANT / OBJECT / ARRAY types
  • Access: Dot notation (col:key) or bracket notation (col['key'])
  • Flattening: Expand nested arrays into rows with the FLATTEN function combined with LATERAL JOIN
  • Schema detection: Auto-infer the schema of files on a stage with the INFER_SCHEMA function
  • Type casting: Cast values inside a VARIANT to a specific type with the :: operator (e.g. col:price::NUMBER)

Performance Optimization Tips

TechniqueWhen to applyEffect
Clustering keyTables of 1 TB or larger with frequent filters on specific columnsBetter pruning rate
Materialized viewFrequently executed aggregation queriesReuse of pre-computed results
Search Optimization ServiceEquality lookups on high-cardinality columnsFaster point lookups
Warehouse sizingLarge queries with significant scan volumeIncreased parallel processing capacity

Study Strategy

The Data Engineer exam tests more practical knowledge than the Core exam, so hands-on study is essential.

  • Build an actual Streams + Tasks pipeline and verify how SYSTEM$STREAM_HAS_DATA behaves
  • Create Dynamic Tables and experiment with TARGET_LAG behavior
  • Set up Snowpipe auto-ingest with S3 event notifications and check pipe status
  • Store JSON in the VARIANT type and write queries that expand it with FLATTEN + LATERAL JOIN
  • Learn how to check spilling and pruning rates in Query Profile

Data Protection Essentials

  • Time Travel: Query historical data for up to 90 days on Enterprise Edition. Restore or reference via UNDROP TABLE or SELECT ... AT(TIMESTAMP =>) syntax
  • Fail-safe: A 7-day Snowflake-managed backup after the Time Travel period ends. Not accessible to users
  • Zero-Copy Clone: Copy only metadata via CREATE TABLE ... CLONE syntax. Storage costs are incurred only for changed data
  • Data encryption: Transparent AES-256 encryption that is always on with no user action required

Try a Sample Question

SnowPro Advanced: Data Engineer

Question 1

If you want to track only INSERT operations on a source table and ignore UPDATE/DELETE changes, which Stream type should you use?

  1. Standard Stream
  2. Append-only Stream
  3. Insert-only Stream
  4. Delta Stream

Correct answer: B

An Append-only Stream tracks INSERT operations only and does not record UPDATE or DELETE changes. It is ideal for append-only workloads like log or event tables. A Standard Stream tracks all DML operations. The Insert-only Stream is for External Tables only. There is no Stream type called Delta Stream.

Frequently Asked Questions

Is the SnowPro Core certification required for the SnowPro Advanced Data Engineer exam?

Yes. An active SnowPro Core certification is a prerequisite for the exam. It does not have to be COF-C03: a certification earned on COF-C02 also qualifies while it is still active. If your Core certification has expired, you must pass the Core exam again first. While you can take the Advanced exam immediately after passing Core, the Advanced exam builds on Core knowledge and demands a deep understanding of Streams, Tasks, Snowpipe, Dynamic Tables, and more, so we recommend allowing an additional 4-8 weeks of study time.

How does the exam test when to use Dynamic Tables versus Streams + Tasks?

The exam uses scenario-based questions to test the judgment call: choose Dynamic Tables when you want to declaratively define the pipeline, and choose Streams + Tasks when you need fine-grained control over CDC. Dynamic Tables shine for their simplicity — you declare the target table with a SQL query and data refreshes happen automatically. Streams + Tasks, on the other hand, offer the flexibility to handle intermediate change-data processing and error handling in detail.

How much of the exam covers semi-structured data?

There is no separate semi-structured data domain. In the English official Study Guide (DEA-C02, last updated March 6, 2026), objective 1.2 in Domain 1.0, Data Movement (28%), covers ingesting structured, semi-structured, and unstructured data and schema detection with INFER_SCHEMA, and objective 5.4 in Domain 5.0, Data Transformation (25%), covers transforming semi-structured data to structured data and back. Make sure you know how to store JSON/XML in the VARIANT type, access data via dot or bracket notation, expand nested arrays with the FLATTEN function and LATERAL JOIN, and query Parquet/ORC/Avro files (SELECT $1 FROM @stage syntax).

Check what you learned with practice questions

Practice with certification-focused question sets

Try free questions
Author

NicheeLab Editorial Team

NicheeLab editorial team focused on data engineering and cloud certification learning. Content is structured around practical study needs and official exam domains.


Related articles
Snowflake

Snowflake Certifications: All 11 Exams Explained (2026)

Every SnowPro certification on Snowflake's official list — C...

Snowflake

Snowflake Exam Difficulty Ranking: All 11 Certs Compared (2026)

All 11 SnowPro exams compared by difficulty in a ranking and...

Snowflake

Snowflake Study Guide: Fastest Pass Route by Exam (2026)

How to pass SnowPro certifications efficiently — official ma...

Snowflake

SnowPro Core (COF-C03): Complete Exam Guide (2026)

Pass the SnowPro Core exam — five domains, scope, sample que...

Snowflake

SnowPro Associate Platform (SOL-C01): Current Status & What to Take Next (2026)

As of October 2026, SnowPro Associate: Platform (SOL-C01) is...

Browse all Snowflake articles (103)
© 2026 NicheeLab All rights reserved.