How to Use This Exam Blueprint Use this checklist as an independent study map for the Snowflake SnowPro Advanced: Data Engineer (DEA-C02) exam from Snowflake . It translates common Snowflake data engineering responsibilities into practical readiness checks: what you should recognize, design, troubleshoot, and choose under scenario pressure.
Do not treat this as a list of exact official exam weights. Instead, use it to find gaps before final practice.
For each topic area, mark yourself:
Ready : you can explain the concept, choose the right Snowflake feature, and troubleshoot a realistic scenario.Review : you understand the idea but need syntax, edge cases, or decision practice.Practice : you would likely miss scenario questions without hands-on repetition.Topic-Area Readiness Table Readiness area What to review Ready when you can… Status Snowflake architecture for data engineering Warehouses, storage/compute separation, databases, schemas, stages, micro-partitions, metadata, caching Explain how compute, storage, metadata, and services interact in pipeline design ☐ Ready ☐ Review ☐ Practice Data loading and unloading COPY INTO, stages, file formats, load history, validation, error handling, schema evolutionDesign repeatable batch loads and diagnose failed or duplicated loads ☐ Ready ☐ Review ☐ Practice Continuous ingestion Snowpipe, cloud notifications, pipes, streaming patterns, connector-based ingestion Choose between batch, Snowpipe, streaming, and external table approaches ☐ Ready ☐ Review ☐ Practice Transformations and ELT SQL transformations, CTAS, MERGE, views, stored procedures, Snowpark, UDFs Build reliable transformation logic with idempotency and observability ☐ Ready ☐ Review ☐ Practice Streams and tasks CDC streams, task schedules, task graphs, stream staleness, task history Implement and troubleshoot incremental pipelines ☐ Ready ☐ Review ☐ Practice Dynamic tables and materialized results Dynamic tables, target lag, materialized views, refresh tradeoffs Select the right managed refresh pattern for freshness and cost requirements ☐ Ready ☐ Review ☐ Practice Semi-structured data VARIANT, OBJECT, ARRAY, FLATTEN, JSON pathing, schema inferenceParse, flatten, model, and optimize semi-structured workloads ☐ Ready ☐ Review ☐ Practice Performance tuning Query Profile, warehouse sizing, clustering, pruning, search optimization, materialized views Identify the bottleneck and choose a proportional optimization ☐ Ready ☐ Review ☐ Practice Security and access control RBAC, grants, managed access schemas, masking, row access, tags, stages, integrations Grant least privilege for pipelines without overexposing data or credentials ☐ Ready ☐ Review ☐ Practice Governance and observability Account usage, information schema, query history, copy history, task history, access history Trace pipeline behavior, audit access, and explain lineage-relevant metadata ☐ Ready ☐ Review ☐ Practice Data sharing and collaboration Secure views, shares, reader/consumer patterns, policy-aware sharing Protect sensitive data while enabling controlled downstream consumption ☐ Ready ☐ Review ☐ Practice Resilience and lifecycle operations Cloning, Time Travel concepts, replication/failover concepts, environment promotion Design recoverable, testable, and promotable data engineering workflows ☐ Ready ☐ Review ☐ Practice
Core Snowflake Architecture Checks Can you explain these without notes? Architecture decision prompts Scenario cue Likely design consideration ETL jobs compete with BI users Separate warehouses or workload-specific warehouse strategy Queries scan too much data Check pruning, clustering, filtering, data model, and Query Profile Need isolated test data quickly Consider cloning and environment promotion controls Need multiple teams to build pipelines Use role hierarchy, schemas, ownership model, and naming standards Need cost attribution Use warehouse separation, tags where appropriate, and query/history metadata Need repeatable deployments Use scripted DDL, versioned artifacts, and controlled grants
Data Loading and Unloading Checklist Batch loading readiness You should be able to read, modify, and troubleshoot common load patterns like:
CREATE OR REPLACE FILE FORMAT ff_orders_csv
TYPE = CSV
FIELD_OPTIONALLY_ENCLOSED_BY = '"'
SKIP_HEADER = 1
NULL_IF = ( 'NULL' , '' );
CREATE OR REPLACE STAGE stg_orders
FILE_FORMAT = ff_orders_csv ;
COPY INTO raw . orders
FROM @ stg_orders
ON_ERROR = 'CONTINUE' ;
Be ready to explain:
Common load failure checks Symptom What to inspect Rows rejected File format, delimiter, quote handling, null handling, data types Load succeeds but data is shifted Column order, header handling, delimiter, embedded separators Files are not found Stage path, cloud storage path, pattern, integration, permissions Files load twice Load history, FORCE, renamed files, pipeline idempotency Load is slow File sizing pattern, warehouse size, concurrency, compression, cloud storage layout Numeric/date fields fail Format settings, regional formats, invalid source values, TRY_ functions Semi-structured load fails JSON validity, outer arrays, compression, file format type External stage access denied Storage integration, cloud IAM/policy, encryption configuration, object path
Unloading readiness Continuous Ingestion and Pipeline Selection Choose the ingestion pattern Requirement Pattern to consider Watch for Scheduled large file batches COPY INTO with orchestrationIdempotency, file organization, retries Near-real-time file arrival in cloud storage Snowpipe with notifications Cloud events, pipe definition, latency expectations High-frequency row/event ingestion Streaming-oriented pattern or connector Ordering, deduplication, schema drift, cost Query files without immediately loading all data External tables or staged-file query pattern Performance, metadata refresh, governance Kafka or event-platform source Snowflake connector or streaming integration pattern Offset handling, error topics, schema registry assumptions Cross-cloud or multi-system ingest Storage integrations and controlled landing zones Credentials, encryption, network path, auditability
Snowpipe readiness Example artifact to recognize:
CREATE OR REPLACE PIPE p_orders_auto
AS
COPY INTO raw . orders
FROM @ stg_orders_auto
FILE_FORMAT = ff_orders_json ;
Ingestion decision traps Trap Better exam-ready thinking “Near real time” always means streaming API Check whether the source is files, events, Kafka, or application rows Use one large warehouse for everything Separate load, transform, and consumption workloads when isolation matters Put all transformation inside COPY Keep loads simple when debuggability, replay, and lineage matter Ignore rejected records Capture, inspect, and design a remediation path Assume schema never changes Plan for schema drift, validation, and versioned raw zones
Be comfortable with:
Example MERGE pattern to understand:
MERGE INTO dim . customer tgt
USING stg . customer_updates src
ON tgt . customer_id = src . customer_id
WHEN MATCHED THEN UPDATE SET
tgt . email = src . email ,
tgt . updated_at = src . updated_at
WHEN NOT MATCHED THEN INSERT (
customer_id ,
email ,
updated_at
) VALUES (
src . customer_id ,
src . email ,
src . updated_at
);
Modeling checks Model/design topic Ready when you can… Raw, curated, and consumption layers Explain why each layer exists and what transformations belong there Star schema Identify facts, dimensions, grain, conformed dimensions, and surrogate keys Snowflake schema Recognize normalized dimensions and tradeoffs Data Vault-style patterns Recognize hubs, links, satellites at a conceptual level if used in your environment SCD Type 1 Overwrite old values safely SCD Type 2 Preserve history using effective dates, current flags, or similar design Deduplication Use keys, timestamps, hashes, and window functions appropriately Late-arriving data Decide whether to restate facts, update dimensions, or hold exceptions Schema evolution Separate raw capture from curated contract changes Data quality Add checks for nulls, uniqueness, referential consistency, ranges, and freshness
Scenario cue What to consider Need exact replay from source Preserve raw immutable data and load metadata Need latest customer state only Type 1 or current-state table may be enough Need historical reporting Type 2 dimension or historized model Need reusable business metric Curated table, view, or governed semantic layer pattern Need complex procedural logic Stored procedure or Snowpark may fit better than a single SQL statement Need simple dependency-based SQL refresh Dynamic table or task graph may be appropriate
Streams, Tasks, and Incremental Processing Streams readiness Example pattern:
CREATE OR REPLACE STREAM str_orders
ON TABLE raw . orders ;
MERGE INTO curated . orders tgt
USING str_orders src
ON tgt . order_id = src . order_id
WHEN MATCHED THEN UPDATE SET
tgt . status = src . status ,
tgt . updated_at = src . updated_at
WHEN NOT MATCHED THEN INSERT (
order_id ,
status ,
updated_at
) VALUES (
src . order_id ,
src . status ,
src . updated_at
);
Tasks readiness Example task pattern:
CREATE OR REPLACE TASK t_curate_orders
WAREHOUSE = wh_transform
SCHEDULE = 'USING CRON 0 * * * * UTC'
AS
CALL sp_curate_orders ();
Streams and tasks traps Trap Why it matters Treating a stream as a durable audit log Streams are for change consumption, not long-term retention Forgetting task state A valid task definition does not help if it is not running Consuming stream data without transaction awareness Failed or partial logic can create inconsistent downstream state Combining too much work in one task Harder to retry, debug, and monitor Ignoring empty-stream checks Wastes compute and complicates monitoring No rerun strategy Failed incremental jobs can create duplicates or missed changes
Dynamic Tables, Materialized Views, and Managed Refresh Feature selection checklist Need Consider Key tradeoff Declarative pipeline with freshness target Dynamic table Less custom orchestration, but understand refresh behavior Repeated query acceleration over stable logic Materialized view Maintenance overhead and eligibility considerations Full control over incremental logic Streams and tasks More orchestration responsibility Simple virtual abstraction View No stored result by default Physical curated table CTAS, INSERT, MERGE, task-driven ELT More control, more operations Low-latency point lookups Search optimization or data model change Cost and workload fit
Dynamic table readiness Example to recognize:
CREATE OR REPLACE DYNAMIC TABLE curated . daily_order_summary
TARGET_LAG = '1 hour'
WAREHOUSE = wh_transform
AS
SELECT
order_date ,
COUNT ( * ) AS order_count ,
SUM ( order_amount ) AS total_amount
FROM curated . orders
GROUP BY order_date ;
Semi-Structured Data Readiness Core skills Example to understand:
SELECT
src : value : customer : id :: STRING AS customer_id ,
item . value : sku :: STRING AS sku ,
item . value : quantity :: NUMBER AS quantity
FROM raw . events ,
LATERAL FLATTEN ( input => src : value : items ) item ;
Semi-structured scenario checks Scenario Good response JSON structure changes often Land raw as VARIANT, curate stable columns later BI filters repeatedly on nested field Consider extracting the field or using an optimization feature Arrays create duplicated rows Check FLATTEN logic and output grain Type conversions fail Use safe casts and data quality exception handling Source sends multiple event versions Store version metadata and branch parsing logic
Query tuning readiness You should be able to use Query Profile and history views to answer:
Tool or design choice Best fit Caution Warehouse scaling up Individual queries need more compute May not fix poor pruning or bad joins Multi-cluster/concurrency scaling pattern Many simultaneous queries Helps concurrency more than bad SQL Auto-suspend/auto-resume Cost control for intermittent workloads Too aggressive settings may add latency Clustering key Large tables filtered by common selective patterns Maintenance cost must be justified Materialized view Repeated expensive query pattern Not every query is eligible or worth materializing Dynamic table Managed refreshed transformation Understand freshness and refresh behavior Search optimization Selective point lookups or specific access patterns Not a general replacement for modeling Query rewrite Bad joins, missing predicates, overuse of SELECT * Often cheaper than adding compute Table redesign Wrong grain, huge semi-structured scans, poor join keys Requires downstream coordination
Trap Exam-ready correction “Make the warehouse bigger” for every slow query Inspect Query Profile first Assume clustering is always needed Check pruning and workload patterns Use materialized views for all dashboards Validate repetition, freshness, and maintenance tradeoff Ignore join cardinality Row explosion can dominate cost Filter after flattening everything Push filters as early as possible Benchmark with cached results only Test in a way that reflects real workload behavior Optimize one query while harming pipelines Balance workload-level performance and cost
Security, RBAC, and Protected Data Engineering RBAC readiness Data protection readiness Control Use case Masking policy Hide or transform sensitive column values based on role or context Row access policy Restrict visible rows by user, role, tenant, region, or business rule Tags Classify data and support governance automation Secure view Share or expose controlled logic with protection considerations Storage integration Access cloud storage without embedding long-lived credentials directly in SQL Network policy/private connectivity concepts Restrict or control access paths where required Access history Investigate who accessed what data, subject to available metadata Object dependencies Understand impact before changing governed objects
Example masking policy pattern to recognize:
CREATE OR REPLACE MASKING POLICY mp_email AS
( val STRING ) RETURNS STRING ->
CASE
WHEN CURRENT_ROLE () IN ( 'DATA_PRIVILEGED_ROLE' ) THEN val
ELSE '***MASKED***'
END ;
Security scenario checks Scenario cue Decision point Pipeline loads from cloud storage Use storage integration and grant only required stage/table privileges Analysts need data but not PII Use views, masking policies, row access policies, or curated tables Multiple teams create objects in shared schema Review ownership, managed access, future grants, and naming rules Data shared with external consumer Use secure sharing patterns and policy-aware design Service account runs tasks Assign a dedicated role with minimal required privileges Access error occurs after deployment Check active role, database/schema usage, object privileges, ownership, and future grants
Monitoring artifacts to know Artifact Use it to answer… Query history What ran, when, by whom, on which warehouse, and how long? Copy/load history Which files loaded, failed, or were skipped? Task history Did scheduled work run successfully? Pipe status/history Is continuous ingestion working? Stream metadata/checks Is there change data waiting? Access history Who accessed sensitive data or governed objects? Object dependencies What breaks if this table, view, or policy changes? Warehouse metrics Is the issue concurrency, size, queueing, or workload mix? Tags/classification metadata Which objects contain sensitive or governed data?
Pipeline observability checklist Data Sharing, Collaboration, and Lifecycle Operations Sharing readiness Lifecycle and resilience checks Need Snowflake capability to review Create test environment quickly Zero-copy clone concepts Recover from accidental change Time Travel concepts and recovery-oriented design Promote code across environments Versioned DDL, deployment scripts, controlled grants Validate production change safely Clone-based testing, representative data, rollback plan Reduce blast radius Separate roles, warehouses, schemas, and environments Cross-region or continuity planning Replication/failover concepts and dependency validation Preserve pipeline history Audit tables, metadata capture, and raw immutable zones
Snowpark, UDFs, and Stored Procedure Readiness When procedural or programmatic logic appears Be ready to identify when SQL alone is enough and when another tool is justified.
Need Possible approach Simple relational transformation SQL view, CTAS, MERGE, dynamic table, or task Multi-step orchestration inside Snowflake Stored procedure called by task Custom scalar logic UDF, if governance and performance fit DataFrame-style transformations Snowpark pattern External library or complex logic Validate runtime, packaging, security, and maintainability assumptions Reusable validation framework Procedure, table-driven checks, or orchestration tool
Readiness checks Scenario and Decision-Point Drill Use this table as a rapid final-review drill. For each row, cover the answer column and decide what you would choose.
Scenario What a strong answer should include Files arrive every few minutes in cloud object storage Snowpipe or event-driven ingestion; verify stage, pipe, notifications, load history Daily full extract must replace curated table Batch load plus swap/transactional pattern; validate row counts and rollback Source sends changed customer records Stream plus MERGE, or source CDC pattern; handle updates/deletes and idempotency Dashboard query is slow on a large table Query Profile, pruning, warehouse, clustering/materialization/search optimization only if justified Analysts need masked PII Masking policy, row access if needed, roles, secure views or curated tables Semi-structured events have unpredictable attributes Land raw VARIANT, curate stable fields, track schema versions Task graph stops running Check task state, history, owner role, warehouse, dependencies, errors Stream has no data but source changed Check source table, stream offset, transaction consumption, staleness, role/object COPY loads zero rows Stage path, pattern, file format, load history, previous load state, privileges Need low-cost development copy Clone concepts, environment isolation, grants, and lifecycle cleanup Need freshness target without custom orchestration Dynamic table if query pattern and refresh behavior fit Need repeated precomputed query result Materialized view or dynamic table depending transformation and freshness need Need to share governed data externally Secure sharing design, masking/row policies, provider/consumer dependency review Cost spike after pipeline change Query history, warehouse usage, task frequency, feature usage, data volume, query plan
Common Weak Areas for DEA-C02 Candidates Technical weak spots Scenario-reading traps Wording in question Be careful “Most cost-effective” Do not automatically choose maximum warehouse size or always-on compute “Near real time” Identify whether the source is file arrival, message stream, or application event “Least privilege” Grants and ownership matter; avoid administrator-style answers “Repeated query” Consider materialization, but verify freshness and maintenance needs “Changing schema” Separate raw ingestion from curated contracts “Failed after deployment” Check roles, grants, object ownership, task state, and integrations “Large table” Large alone does not require clustering; access pattern matters “Securely load from cloud storage” Prefer integration-based patterns over embedded credentials “Incremental” Decide whether streams, dynamic tables, MERGE, or source CDC fits best
Final-Week Checklist 7 to 5 days out 4 to 2 days out Last day Practical Next Step Pick the three weakest readiness areas from the table at the top of this page. For each one, do a short hands-on rebuild or scenario drill, then answer: Which Snowflake feature would I choose, why, what could fail, and how would I prove it is working?