Active Snowflake advanced certification
SnowPro Advanced Data Engineer DEA-C02 Roadmap
Design, operate, secure, tune, and recover production-style Snowflake pipelines across data movement, transformation, performance, storage protection, and governance.
Exact 50-question study allocation
The practice set contains Data Movement 14; Performance Optimization 9; Storage and Data Protection 7; Data Governance 7; and Data Transformation 13. The distribution emphasizes complete pipelines: moving data reliably, transforming changes transactionally, measuring performance, selecting recovery guarantees, and enforcing governed access.
Stages, COPY, Snowpipe, streaming, connectors, unload, files, schema change, retries.
Profiles, queueing, spill, warehouses, pruning, clustering, caches, acceleration.
Table types, Time Travel, Fail-safe, clones, storage metrics, replication.
RBAC, managed access, masking, row policy, tags, sharing, audit coverage.
Streams, tasks, graphs, MERGE, dynamic tables, semi-structured data, transactions.
Phase 1 — Data movement and ingestion contracts
Start with volume, latency, file, schema, ownership, and replay requirements. Use warehouse-based COPY for batches, Snowpipe for continuous file micro-batches, and Snowpipe Streaming for direct low-latency rows. The feature name matters less than matching its semantics to the source.
- Configure internal and external stages, file formats, storage integrations, and least-privilege roles.
- Prepare reasonably sized files and use validation before committing regulated batches.
- Reconcile file identity, load history, rows, rejects, latency, and cost.
- Test duplicate submission, malformed data, schema drift, cloud-notification loss, and streaming retries.
- Unload parallel files and use partitioned paths only with reviewed cardinality and data sensitivity.
Phase 2 — Incremental and declarative transformation
Learn that a stream stores an offset, not a copy. A plain query does not advance it; committed DML consumption does. Give each consumer its own stream and monitor STALE_AFTER. Combine streams with triggered tasks and MERGE when you need explicit CDC behavior.
- Use transactions so related writes and stream advancement succeed or roll back together.
- Model task graphs, parallel branches, finalization, retry, suspension, timeout, and owner privileges.
- Compare dynamic tables for SELECT-expressible transformations and target-lag-driven pipelines.
- Use TARGET_LAG = DOWNSTREAM deliberately on intermediate dynamic tables.
- Build deterministic Type 2 dimension handling for late, duplicate, delete, and conflicting changes.
Phase 3 — Evidence-based performance optimization
Query history and the query profile come before tuning. Queueing points toward concurrency and workload isolation. Remote spill points toward more memory per query or a smaller working set. Excess scans point toward pruning, predicate shape, clustering, search optimization, materialization, or query acceleration.
- Separate unlike ETL and BI workloads into appropriate warehouses.
- Scale up for per-query resources and scale out primarily for concurrency.
- Control for persisted result reuse and warehouse cache when benchmarking.
- Add clustering only when stable workload evidence justifies maintenance cost.
- Measure elapsed time, credits, storage, and serverless cost for every retained optimization.
Phase 4 — Storage protection and data governance
Select table type from lifespan and recovery need. Temporary tables are session-scoped. Transient tables persist but lack Fail-safe. Permanent tables support the full continuous-data-protection lifecycle. Time Travel is user-accessible history within retention; Fail-safe is a separate Snowflake-assisted recovery period.
- Use historical queries and zero-copy clones for validation and controlled recovery.
- Read TABLE_STORAGE_METRICS for active, Time Travel, Fail-safe, and retained-for-clone bytes.
- Build custom role hierarchies and managed access schemas rather than giving broad system roles to pipelines.
- Apply masking, row access, tags, and policy-coverage monitoring.
- Publish secure read-only data products through sharing and test consumer-context behavior and revocation.
Phase 5 — Integrated reliability and readiness
Complete full failure loops: detect, diagnose, contain, restore, reconcile, and prevent recurrence. Practice stage denial, duplicate files, malformed events, stale streams, failed task children, dynamic refresh errors, warehouse queues, policy conflicts, consumer revocation, replication lag, and failover.
- Complete all three DEA-C02 projects with teardown evidence.
- Retrieve all 40 DEA-C02 flashcards using spaced repetition.
- Run all 50 DEA-C02 practice questions under 115 minutes.
- Explain why each distractor violates latency, consistency, access, recovery, performance, or cost requirements.
- Recheck the official exam page and current Snowflake documentation during the final week.
Official-source study set
All DEA-C02 learning surfaces
Frequently asked questions
What is the DEA-C02 exam format?
The official certification page identifies 65 mixed-format questions and 115 minutes. Verify delivery, fees, scoring, and policies on the current page.
Is SnowPro Core a prerequisite?
Do not infer that it is mandatory. Snowflake describes an experienced candidate; treat Core as strong preparation unless current official policy explicitly requires it.
How much experience is expected?
The official page describes a candidate with two or more years of hands-on production data-engineering experience.
How are the 50 questions allocated?
Data Movement 14, Performance Optimization 9, Storage and Data Protection 7, Data Governance 7, and Data Transformation 13.
Should I memorize SQL syntax?
Know important semantics and recognize patterns, but prioritize architectural tradeoffs, transaction behavior, security context, performance evidence, failure recovery, and cost.
Are these questions from the live exam?
No. They are original scenarios derived from public objectives and current official Snowflake documentation.
Build advanced Snowflake operating skill
Read the guide · Start questions · Review cards · Build projects