Domain 1: Data Movement
- Load data with COPY INTO <table> from internal or external stages; use storage integrations for S3, Azure, and GCS, and named, user (@~), or table (@%) stages as appropriate.
- Control loads with COPY options: ON_ERROR (CONTINUE, SKIP_FILE, ABORT_STATEMENT), VALIDATION_MODE to test without loading, MATCH_BY_COLUMN_NAME for semi-structured, PURGE, FORCE, and SIZE_LIMIT.
- Load metadata prevents reloading the same file; that metadata expires after 64 days, after which files can reload unless you use FORCE deliberately - inspect with COPY_HISTORY and LOAD_HISTORY.
- Use INFER_SCHEMA and CREATE TABLE ... USING TEMPLATE to derive columns from Parquet or CSV, and apply column transformations during COPY by selecting from the stage.
- Choose the right ingestion pattern: bulk COPY for batches, Snowpipe for continuous file ingestion (auto-ingest via cloud notifications or the REST API), and Snowpipe Streaming for low-latency row-level ingest via the Ingest SDK.
- Build pipelines with streams and tasks: a stream tracks table changes (standard, append-only, insert-only) with an offset, and the CHANGES clause reads change data without a stream.
- Schedule tasks on a CRON or interval, chain them into task graphs with AFTER, gate them with WHEN SYSTEM$STREAM_HAS_DATA, and remember the default USER_TASK_TIMEOUT_MS is 60 minutes.
- Prefer dynamic tables for declarative pipelines: set a TARGET_LAG and let Snowflake refresh incrementally based on dependencies, instead of hand-coding streams plus tasks.
- Move data across accounts with Secure Data Sharing (shares, reader accounts, listings, Marketplace); cross-region or cross-cloud sharing first requires database replication.
- Query lake data in place with external tables or Apache Iceberg tables, and ingest from event streams using the Snowflake Connector for Kafka.
Domain 2: Performance Optimization
- Read the Query Profile to find the most expensive operator, exploding or cartesian joins, and how many bytes each TableScan reads.
- Judge pruning by comparing partitions scanned with partitions total; functions on filter columns and poor natural clustering defeat pruning and force full scans.
- Spilling to local or remote storage means the warehouse ran out of memory - scale the warehouse up or reduce the data the query sorts, joins, or aggregates.
- Know the caching layers: the result cache reuses identical query results for 24 hours, the metadata cache answers min/max and count queries, and the warehouse data cache is lost on suspend.
- Scale UP (a larger warehouse) for a single slow, complex query; scale OUT (a multi-cluster warehouse) for concurrency and queuing, tuning MIN/MAX clusters and the STANDARD or ECONOMY scaling policy.
- Define a clustering key on a large table when queries filter or join on a column that is not the load order; use SYSTEM$CLUSTERING_INFORMATION and SYSTEM$CLUSTERING_DEPTH to decide whether to cluster or recluster.
- Add the Search Optimization Service for selective point lookups, and use materialized views or dynamic tables to pre-compute expensive results - each with its own maintenance cost.
- Enable the Query Acceleration Service (QAS) to offload scan-heavy portions of eligible queries, and set resource monitors to cap credit spend with NOTIFY or SUSPEND triggers.
- Separate warehouses by workload (ETL, BI, data science) so one heavy job does not starve another, and set auto-suspend and auto-resume to control cost.
- Diagnose with QUERY_HISTORY, GET_QUERY_OPERATOR_STATS, and EXPLAIN, and distinguish queuing (QUEUED_OVERLOAD_TIME) from execution time.
Domain 3: Storage and Data Protection
- Snowflake stores data in immutable micro-partitions (about 16 MB compressed, columnar) whose per-partition min/max metadata drives pruning and natural clustering.
- Time Travel keeps historical versions for DATA_RETENTION_TIME_IN_DAYS (default 1; Standard edition max 1; Enterprise and above up to 90); query the past with AT and BEFORE, and recover objects with UNDROP.
- Fail-safe adds a fixed, non-configurable 7-day window after Time Travel, recoverable only by Snowflake support - so set retention high enough before an incident, not after.
- Choose table types for cost: permanent tables have Time Travel plus Fail-safe, transient tables have limited Time Travel and no Fail-safe, and temporary tables live only for the session.
- Zero-copy cloning (CREATE ... CLONE) is a metadata-only copy that consumes no extra storage until data diverges; you can clone at a past point using Time Travel.
- Understand what a clone does not copy - load history is not carried over, and clones of pipes, streams, and tasks have specific behaviors to remember.
- Protect against region or account loss with database replication and failover groups: secondary databases are read-only until failover, then you fail over and back.
- Analyze storage with TABLE_STORAGE_METRICS (ACTIVE_BYTES, TIME_TRAVEL_BYTES, FAILSAFE_BYTES, RETAINED_FOR_CLONE_BYTES) and DATABASE_STORAGE_USAGE_HISTORY.
- Interpret SYSTEM$CLUSTERING_DEPTH and SYSTEM$CLUSTERING_INFORMATION to see how well-clustered a table is and whether reclustering is worthwhile.
- Design retention deliberately: a pipeline failure discovered nine days later needs at least nine days of Time Travel that was already in effect when the data changed.
Domain 4: Data Governance
- Control access with role-based access control: grant privileges to roles, build a role hierarchy under system roles (ACCOUNTADMIN, SECURITYADMIN, USERADMIN, SYSADMIN), and use future grants for objects created later.
- Apply Dynamic Data Masking with masking policies that return clear or masked values based on the querying role, using CURRENT_ROLE or IS_ROLE_IN_SESSION for conditional logic.
- Protect columns at scale with tag-based masking policies: attach a masking policy to a tag, then tag the sensitive columns so the policy applies automatically.
- Filter rows with row access policies backed by a mapping or entitlement table, and combine them with masking for column-and-row protection.
- Restrict how columns are used with projection policies (block direct projection) and aggregation policies (allow only aggregate access).
- Classify and label data: object tagging with CREATE TAG and tag lineage, plus automatic classification (SYSTEM$CLASSIFY) that assigns semantic and privacy tags.
- Monitor usage with ACCESS_HISTORY for query access and column lineage, OBJECT_DEPENDENCIES for lineage, and ACCOUNT_USAGE views such as QUERY_HISTORY, LOGIN_HISTORY, and GRANTS_TO_ROLES.
- Use secure views and secure UDFs when sharing data so the definition is hidden and the optimizer cannot leak underlying rows.
- Monitor data quality with Data Metric Functions - system DMFs like NULL_COUNT, DUPLICATE_COUNT, and FRESHNESS, or custom DMFs scheduled on a table.
- Enforce perimeter and session controls with network policies and account security parameters.
Domain 5: Data Transformation
- Write UDFs in SQL, JavaScript, Python, or Java/Scala; choose scalar UDFs or tabular UDTFs, mark them secure when needed, and use memoizable UDFs for expensive constant lookups.
- Call out to remote services with external functions through an API integration when logic must run outside Snowflake.
- Build stored procedures with Snowflake Scripting (cursors, RESULTSET, exception handling, EXECUTE IMMEDIATE) or in Python/Snowpark, and understand owner's rights versus caller's rights.
- Transform semi-structured data in VARIANT, OBJECT, and ARRAY columns using colon paths, casting with ::, PARSE_JSON, and OBJECT_CONSTRUCT.
- Explode nested arrays into rows with the FLATTEN table function and LATERAL FLATTEN, using OUTER, RECURSIVE, and the KEY, VALUE, and INDEX columns.
- Handle unstructured files with directory tables and file URL functions - BUILD_SCOPED_FILE_URL, BUILD_STAGE_FILE_URL, and GET_PRESIGNED_URL - and process them with Python or Java UDFs.
- Use Snowpark (Python DataFrames) for programmatic transformation; remember it is lazy, so transformations build a plan and only actions like collect, show, or save_as_table execute it.
- Register Snowpark UDFs and stored procedures, use vectorized (pandas) UDFs for batch work, and pick a Snowpark-optimized warehouse for memory-intensive jobs.
- Apply set-based SQL transforms: MERGE for upserts (often from a stream), multi-table INSERT, QUALIFY with window functions, and PIVOT and UNPIVOT.
- Apply Snowflake Cortex LLM functions such as COMPLETE, SUMMARIZE, SENTIMENT, and TRANSLATE to transform or enrich text columns.
SnowPro Advanced Data Engineer (DEA-C02) exam tips
- Weight your prep by the blueprint: Data Movement (26%) and Data Transformation (25%) are half the exam, so be fluent with COPY options, Snowpipe versus Snowpipe Streaming, streams and tasks, dynamic tables, UDFs, stored procedures, FLATTEN, and Snowpark before the smaller domains.
- Master the performance decision tree: scale UP (a bigger warehouse) fixes a single slow complex query and spilling; scale OUT (a multi-cluster warehouse) fixes concurrency and queuing; clustering and Search Optimization fix pruning and point lookups. Expect Query Profile scenarios.
- Know the data-protection numbers cold: Time Travel default 1 day (Enterprise up to 90), Fail-safe fixed at 7 days and non-configurable, and the 64-day load-metadata expiry. Retention must be set before an incident to recover from it.
- Be precise about pipeline objects: a stream tracks changes with an offset, a task runs on a schedule with a default 60-minute USER_TASK_TIMEOUT_MS, and a dynamic table refreshes declaratively to a TARGET_LAG. Know when to pick a dynamic table over streams plus tasks.
- For governance, distinguish the policy types - masking (column values), row access (which rows), projection and aggregation (how a column may be used), and tags plus classification (labeling) - and use ACCESS_HISTORY for lineage. Prefer tag-based masking to scale.
Study guide FAQ
What is the format of the SnowPro Advanced: Data Engineer exam?
The DEA-C02 exam has 65 questions - multiple-choice, multiple-select, and interactive items such as drag-and-drop - to be answered in 115 minutes, with a scaled passing score of 750 out of 1000. It is delivered online-proctored or at a test center.
Do I need SnowPro Core first?
Yes, and the word that matters is active. Snowflake's certification FAQ states it directly: "To be eligible for an Advanced exam, you must have an active SnowPro Core certification. However, it is not a prerequisite for taking a Speciality exam." So Core is eligibility for this exam rather than a recommendation, and an expired Core does not carry you - if it lapses you are back to starting with SnowPro Core or a Specialty certification. Note that this applies to the Advanced exams only; the Specialty exams have no such requirement. Alongside it Snowflake states the candidate profile as 2 or more years of hands-on experience as a Data Engineer in a production environment.
How is DEA-C02 different from the older DEA-C01?
DEA-C02 keeps the same five content domains but reorganizes and modernizes topics. The Security domain became Data Governance, weightings shifted (Data Movement 26%, Performance 21%, Storage and Data Protection 14%, Data Governance 14%, Data Transformation 25%), and newer features such as dynamic tables, Snowpipe Streaming, Iceberg tables, and Snowpark feature more prominently.
How much Snowpark and Python do I need?
You should be comfortable with Snowpark DataFrames and lazy evaluation, registering Snowpark UDFs and stored procedures, and Python UDFs, alongside SQL. The transformation domain expects you to choose between SQL, UDFs, stored procedures, external functions, and Snowpark for a given task.
Is CertGrid's practice official Snowflake material?
No. CertGrid is an independent practice platform and is not affiliated with or endorsed by Snowflake. These questions are original and written to mirror the current DEA-C02 exam guide's five domains and scenario style. Always confirm the current exam guide on the official Snowflake certification page before your exam.
Official exam sources
The domain names and weightings on this page follow the published exam blueprint. Each source below records what it confirmed and when it was read, so the split can be checked rather than taken on trust.
- Snowflake - SnowPro Advanced: Data Engineer (DEA-C02)link and content verified 8 September 2026Exam code DEA-C02, which matches our catalogue. Candidate profile: "2 or more years of hands-on experience as a Data Engineer in a production environment". The page contains no occurrence of the word prerequisite, and the SnowPro program-policies page names none either.
- Snowflake - SnowPro Certification Program Policieslink and content verified 8 September 2026Checked specifically for a SnowPro Core prerequisite on Advanced certifications, because our guides asserted one. The policies page covers terms, renewal, continuing education, extended time and scoring, and states no such prerequisite.
- Snowflake - certification FAQlink and content verified 8 September 2026RESOLVES the prerequisite question, in Snowflake's current words: "To be eligible for an Advanced exam, you must have an active SnowPro Core certification. However, it is not a prerequisite for taking a Speciality exam." So an active Core IS required for this exam, eligibility rather than a recommendation, and the Advanced/Specialty distinction is Snowflake's own. The FAQ is a React accordion that ships every question in the DOM and fetches no answer until a row is opened, which is exactly why this sat unresolved - the question was always readable and the answer never was. Read by opening that one row in a headless browser over the DevTools protocol: a public page, no sign-in, nothing gated by authentication.
Settled, and recorded here. Settled on 8 September 2026, and recorded here because the route to it matters. An active SnowPro Core certification IS required to be eligible for an Advanced exam, and is not required for a Specialty exam. Snowflake's certification FAQ answers this directly, and both guides state it. Where the answer is NOT is worth knowing. Neither the current exam page nor the SnowPro program policies restate the prerequisite - Snowflake's own certification-team article said so back in 2023 and the current pages simply do not mention it either way. Reading one of those pages alone and concluding from its silence that the requirement had been dropped would have been wrong, which is why the FAQ is cited here rather than the more obvious pages.
Related Data resources
- SnowPro Advanced Data Engineer (DEA-C02) practice exam
- Data practice exams
- Certification path
- CompTIA Data+ (DA0-002) study guide
- Confluent Certified Developer for Apache Kafka (CCDAK) study guide
- Databricks Certified Data Analyst Associate study guide
- Certification exam guides & tips
- Pricing & plans
- FAQ