SnowPro Advanced: Data Engineer (DEA-C02) Practice Exam

What the SnowPro Advanced exam covers

• Data Movement192 questions
• Performance Optimization155 questions
• Storage and Data Protection104 questions
• Data Governance104 questions
• Data Transformation185 questions

Free SnowPro Advanced sample questions

A sample of 10 questions with answers and explanations. Sign up free to practice all 740.

1. Question 1Data Movement

   A data engineer created an external stage named s3_stage backed by a storage integration for an S3 bucket. Which statement bulk loads all new files from that stage into the table SALES_RAW?

   • AINSERT INTO sales_raw SELECT * FROM @s3_stage
   • BCOPY INTO sales_raw FROM @s3_stageCorrect
   • CPUT @s3_stage INTO sales_raw
   • DMERGE INTO sales_raw USING @s3_stage
   ✓ Correct answer: B

   COPY INTO <table> is the Snowflake command for bulk-loading data from an internal or external stage into a table; with no FILES or PATTERN clause it loads every file not already recorded in the table's load history. INSERT ... SELECT cannot read raw staged files this way, and MERGE performs row-level upserts between existing tables, not stage files.

   Why the other options are wrong

   • AINSERT ... SELECT FROM @stage is not valid syntax for bulk-loading raw files; COPY INTO is required.
   • CPUT copies files from a local file system to a stage; it does not load data into a table.
   • DMERGE performs conditional inserts and updates between two tables and cannot read directly from a stage.
2. Question 2Data Movement

   Which metadata column, automatically available during a stage query or COPY INTO load, lets a data engineer trace a loaded row back to its source file?

   • AMETADATA$FILENAMECorrect
   • BMETADATA$ROW_ID
   • CMETADATA$ACTION
   • DMETADATA$ISUPDATE
   ✓ Correct answer: A

   When querying a stage or referencing pseudo-columns during a load, METADATA$FILENAME returns the name and path of the source file for each row, letting engineers trace records back to the exact file they were loaded from for debugging or lineage.

   Why the other options are wrong

   • BMETADATA$ROW_ID is not a Snowflake pseudo-column used in COPY loads.
   • CMETADATA$ACTION is a stream pseudo-column indicating INSERT or DELETE, not a load-time file reference.
   • DMETADATA$ISUPDATE is a stream pseudo-column flagging update-related row pairs, unrelated to file loads.
3. Question 3Data MovementSelect all that apply

   Which TWO statements accurately describe performance and capability tradeoffs of external tables compared to native Snowflake tables? (Choose TWO)

   • AExternal tables automatically get the same micro-partition clustering
   • BExternal tables are read-only and cannot accept direct DML statementsCorrect
   • CExternal tables always outperform native tables on join-heavy queries
   • DExternal tables support fully transactional multi-statement updates
   • EQuery performance is generally slower since files are scanned externallyCorrect
   ✓ Correct answer: B, E

   External tables present a metadata layer over files that remain in external storage, so queries scan those files directly rather than Snowflake's optimized native micro-partitions, generally costing more time; they also do not support INSERT, UPDATE, DELETE, or MERGE since the underlying files are managed outside Snowflake.

   Why the other options are wrong

   • ANative micro-partition clustering is a Snowflake storage feature that external tables, backed by external files, do not get automatically.
   • CExternal tables typically perform worse, not better, on join-heavy workloads compared to natively stored tables.
   • DExternal tables cannot be updated transactionally at all since they are read-only metadata overlays.
4. Question 4Data Governance

   A masking policy fully masks a SALARY column for most roles. A BI role needs to compute AVG(SALARY) without ever seeing individual masked or clear values. What is the most appropriate governance feature instead of adjusting the masking policy?

   • AA row access policy that hides every row
   • BA projection policy that hides the column from SELECT lists
   • CAn aggregation policy that restricts the role to aggregate-only queriesCorrect
   • DA stricter masking policy that returns a random number
   ✓ Correct answer: C

   Aggregation policies (CREATE AGGREGATION POLICY) enforce that a governed column can only be used inside aggregate functions with a minimum group size, blocking any query that would expose individual row-level values while still permitting legitimate summary statistics like AVG(SALARY).

   Why the other options are wrong

   • AHiding every row would also prevent the legitimate aggregate calculation the BI role needs to perform.
   • BA projection policy would block SELECT of the column entirely, preventing even the aggregate calculation the role needs.
   • DA per-row randomized value could still be selected and analyzed row by row; it does not guarantee aggregate-only access.
5. Question 5Performance Optimization

   A data engineer reviews a Query Profile and finds a single operator consuming 85% of total execution time, with its output row count far larger than its input row count. What is the most effective first step to diagnose this?

   • AIncrease DATA_RETENTION_TIME_IN_DAYS on the table
   • BExamine that operator for a cartesian or many-to-many joinCorrect
   • CEnable AUTO_SUSPEND to free up warehouse memory
   • DAdd a RESULT_SCAN clause to the query
   ✓ Correct answer: B

   In Query Profile, the operator with the highest percentage of execution time is the best starting point. When its output row count is far greater than its input, it typically indicates an unintended cartesian join or a join condition matching many rows on both sides, causing row multiplication.

   Why the other options are wrong

   • ADATA_RETENTION_TIME_IN_DAYS controls Time Travel retention, unrelated to query execution cost.
   • CAUTO_SUSPEND controls warehouse idle billing, not query diagnostics.
   • DRESULT_SCAN retrieves a prior result set and does not help diagnose a currently running query's join behavior.
6. Question 6Performance Optimization

   QUERY_HISTORY shows consistently high QUEUED_OVERLOAD_TIME across many different queries and users on one warehouse during business hours, while each query's own EXECUTION_TIME is reasonable. What is the most appropriate fix?

   • ARewrite each query to reduce row counts
   • BRecluster every table during business hours
   • CIncrease DATA_RETENTION_TIME_IN_DAYS
   • DConvert to multi-cluster to absorb loadCorrect
   ✓ Correct answer: D

   High QUEUED_OVERLOAD_TIME with normal per-query EXECUTION_TIME indicates the bottleneck is too many concurrent queries competing for one warehouse's capacity, not any single query's efficiency; enabling multi-cluster, with an appropriate MAX_CLUSTER_COUNT, lets Snowflake spin up additional clusters to absorb concurrent demand and reduce queuing.

   Why the other options are wrong

   • AIndividual queries already run efficiently per their EXECUTION_TIME; rewriting them would not address queue-related delay.
   • BReclustering affects scan efficiency for individual queries, not concurrency-driven queuing across many queries.
   • CTime Travel retention has no relationship to warehouse queuing or concurrency.
7. Question 7Performance Optimization

   STATEMENT_TIMEOUT_IN_SECONDS is set to 1800 on a warehouse. What happens to a query that has already been executing for 1800 seconds?

   • AIt is paused and automatically resubmitted at a smaller size
   • BSnowflake automatically cancels the query once the limit is reachedCorrect
   • CIt is moved to a queue and retried after the warehouse resumes
   • DThe query continues but its results are excluded from billing
   ✓ Correct answer: B

   This parameter acts as a safety limit on how long any single statement may actively run on the warehouse; when a query's execution time reaches the configured threshold, in this case 1800 seconds, Snowflake terminates the statement to prevent it from consuming resources indefinitely.

   Why the other options are wrong

   • AThere is no automatic pause-and-resubmit behavior tied to this parameter; the statement is simply canceled.
   • CA running query that hits the timeout is canceled, not re-queued for a later retry.
   • DBilling is not selectively excluded for timed-out queries; the timeout results in cancellation, not a billing exemption.
8. Question 8Storage and Data ProtectionSelect all that apply

   Which two of the following are true about zero-copy cloning in Snowflake? (Choose TWO)

   • AIt is only available for external tables
   • BIt is a metadata-only operation at creation timeCorrect
   • CThe resulting clone is a fully independent objectCorrect
   • DIt permanently links the clone's storage billing to the source forever
   • EIt requires copying every micro-partition to new physical storage immediately
   ✓ Correct answer: B, C

   Cloning creates a new object by copying only metadata, referencing the same underlying micro-partitions as the source. The clone then behaves as a completely independent object going forward, and storage is only duplicated as changes cause divergence.

   Why the other options are wrong

   • AZero-copy cloning works for standard tables, schemas, and databases, not only external tables.
   • DStorage billing only diverges as data changes; the two objects are not permanently tied together.
   • ENo immediate physical copy occurs; micro-partitions are shared until data diverges.
9. Question 9Data Transformation

   A transformation only needs to parse a JSON string already stored in a VARIANT column using functions built into Snowflake. What is the most efficient approach?

   • AAn external function calling a microservice
   • BExport to storage, reprocess externally
   • CUse native functions like PARSE_JSON, FLATTENCorrect
   • DRoute the value through an API Gateway
   ✓ Correct answer: C

   Since the data is already in Snowflake as VARIANT, built-in semi-structured functions like PARSE_JSON, GET, and FLATTEN can transform it in-warehouse with no network round trip.

   Why the other options are wrong

   • ACalling an external microservice adds network latency for logic Snowflake already performs natively.
   • BExporting and reprocessing externally is unnecessary round-tripping for data already in Snowflake.
   • DRouting through an API Gateway is the external function pattern, which is not needed for built-in JSON parsing.
10. Question 10Data TransformationSelect all that apply

    Which TWO of the following Snowpark DataFrame method calls are actions that immediately trigger query execution against Snowflake? (Choose TWO)

    • Adf.select(...)
    • Bdf.with_column(...)
    • Cdf.filter(...)
    • Ddf.count()Correct
    • Edf.collect()Correct
    ✓ Correct answer: D, E

    Actions force Snowpark to compile and run the SQL built from all preceding transformations. collect() returns the rows, and count() returns the row count, both requiring a round trip to Snowflake right away.

    Why the other options are wrong

    • Aselect() only narrows the projected columns in the logical plan; it is a transformation, not an action.
    • Bwith_column() adds a derived column definition to the plan without executing any query.
    • Cfilter() only adds a predicate to the logical plan; it does not run anything by itself.

Who this SnowPro Advanced practice exam is for

This practice set is for anyone preparing for the SnowPro Advanced: Data Engineer (DEA-C02) exam at the advanced level - from first-time candidates building a foundation to experienced Data practitioners doing a final review before test day. If you learn best by working through realistic questions and reading why each answer is right or wrong, it is built for you.

How to use this SnowPro Advanced practice exam

1. Start with the free sample questions above to gauge your current baseline.
2. Read the full explanation on every question, including why each wrong option is wrong.
3. Track your weak domains and focus your study where you are losing the most marks.
4. Once you are scoring consistently well, take a timed, full-length mock exam.
5. Use your readiness score to decide when you are ready to book the real SnowPro Advanced exam.

Related Data resources

• SnowPro Advanced study guideKey concepts
• Data practice examsAll Data
• Certification pathWhere this fits
• Certification exam guides & tipsBlog
• Plans & pricingFree & paid
• Databricks Certified Data Engineer Associate practice examRelated
• Databricks Certified Machine Learning Associate practice examRelated
• SnowPro Core (COF-C02) practice examRelated

SnowPro Advanced practice exam FAQ