Snowflake DEA-C02 Practice Exam

SnowPro Advanced: Data Engineer DEA-C02

The SnowPro Advanced: Data Engineer certification validates advanced knowledge and skills used to apply comprehensive data engineering principles using Snowflake: sourcing data, building near real-time streams, scaling compute and evaluating performance. The DEA-C02 exam covers five domains: Data Movement (28%), Performance Optimization (19%), Storage and Data Protection (14%), Data Governance (14%), and Data Transformation (25%). This practice exam mirrors its scenario style with single-answer, multiple-select and drag-and-drop questions.

709 questions in the bank

Question bank updated : questions, answers, explanations and chapters.

Certification Overview

  • Exam name: SnowPro Advanced Data Engineer DEA-C02
  • Vendor: Snowflake
  • Exam code: DEA-C02
  • Duration: 115 minutes
  • Total questions: about 65
  • Official passing score: 750 on a scale of 0-1000 (source)
  • Our practice exam pass mark: 75%

Who Should Take This Exam?

Data Engineer, Senior Data Engineer, Analytics Engineer, Software Engineer, Technical Data Lead, Lead Systems Engineer, Cloud Data Engineer, Data Platform Engineer

Prerequisites

Active SnowPro Core certification. Snowflake recommends 2 or more years of hands-on experience as a Data Engineer in a production environment.

Topics Covered

  • Data Movement
  • Performance Optimization
  • Storage and Data Protection
  • Data Governance
  • Data Transformation

Question Types

  • Multiple Choice (Single Answer)
  • Multiple Choice (Multiple Answers)
  • Drag and Drop

SnowPro Advanced Data Engineer DEA-C02 Practice Questions

Our question bank contains 709+ practice questions for this certification. Sample questions from each exam chapter. Expand a question to see the answer choices. With full access, you get unlimited practice exams with randomized questions from our full question bank.

Data Movement

A data engineer wants Snowpipe to load new files from an external stage soon after they land, without any client application calling Snowflake. Which setup does this?
  • A. A Snowpipe Streaming client that writes the rows through the Ingest SDK
  • B. A client application that calls the insertFiles endpoint of the Snowpipe REST API
  • C. AUTO_INGEST = TRUE on the pipe, with bucket event notifications sent to its queue Correct answer
  • D. A scheduled task that runs a COPY INTO statement against the stage each minute

Option C is correct because AUTO_INGEST = TRUE makes the pipe listen to the bucket's event notifications, so Snowflake loads new files soon after they land with no client calling in. The REST endpoint needs a client application, Snowpipe Streaming writes rows rather than files, and a scheduled COPY task runs on a warehouse and polls instead of reacting to events.

Which command exports the result of a query to Parquet files in an external stage?
  • A. COPY INTO <table> with TYPE = PARQUET in the file format
  • B. PUT with the external stage as the destination
  • C. GET with the query result as the source
  • D. COPY INTO <location> with a SELECT query as the source Correct answer

Option D is correct because COPY INTO <location> unloads a table or the result of a SELECT query to files in a stage, and Parquet is a supported unload format. COPY INTO <table> loads data in the other direction, while PUT and GET only move files between a client machine and an internal stage.

Parquet files arrive in an internal stage, and the data engineer wants a table whose columns and types match the files without writing DDL by hand. What should they do?
  • A. CREATE TABLE ... USING TEMPLATE over INFER_SCHEMA on the staged files Correct answer
  • B. Query the stage's METADATA$FILENAME column and derive the columns from the output
  • C. Create an external table with AUTO_REFRESH enabled and read its column list
  • D. Run COPY INTO with MATCH_BY_COLUMN_NAME so Snowflake creates the missing table

Option A is correct because INFER_SCHEMA reads the column names and types from the staged Parquet files, and CREATE TABLE ... USING TEMPLATE builds the table from that output in one statement. METADATA$FILENAME returns only the file name, an external table exposes a VALUE column, and COPY INTO does not create a missing table.

An order-entry service needs single-row lookups and updates from many concurrent sessions, with unique keys and referential integrity enforced by the database. Which capabilities of hybrid tables meet these needs? (Select TWO).
  • A. Enforced PRIMARY KEY and FOREIGN KEY constraints Correct answer
  • B. Row-level locking for concurrent writes to different rows Correct answer
  • C. Search optimization service enabled on every column to index lookups
  • D. Materialized views that precompute each lookup result
  • E. Micro-partition pruning on a clustering key for point lookups

Options A and B are correct because hybrid tables enforce PRIMARY KEY and FOREIGN KEY constraints and use row-level locking, which suits many concurrent single-row updates. Search optimization on every column is not how hybrid tables are indexed, materialized views are not supported on them, and they are not stored in micro-partitions, so clustering-based pruning does not apply.

Performance Optimization

A data engineer must enable search optimization on a 6 TB events table in a shared ANALYTICS.RAW schema by running ALTER TABLE ... ADD SEARCH OPTIMIZATION. Which privileges does the role running the statement need?
  • A. OWNERSHIP on the table and the ADD SEARCH OPTIMIZATION privilege on the schema Correct answer
  • B. MODIFY on the table and USAGE on the warehouse that serves the lookup queries
  • C. OWNERSHIP on the schema and the MONITOR USAGE privilege on the account
  • D. OWNERSHIP on the table and the CREATE MATERIALIZED VIEW privilege on the schema

Option A is correct because ALTER TABLE ... ADD SEARCH OPTIMIZATION needs OWNERSHIP on the table plus the ADD SEARCH OPTIMIZATION privilege on the schema. Warehouse USAGE, MONITOR USAGE and CREATE MATERIALIZED VIEW are unrelated to enabling the service.

Which query against a 3 TB ORDERS table that is clustered by order_date gains the most from search optimization on the table?
  • A. SELECT SUM(amount) FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31'
  • B. SELECT * FROM orders WHERE order_uuid = 'f3a9-7c21-44d0' returning a single row Correct answer
  • C. SELECT * FROM orders WHERE status <> 'CANCELLED' ORDER BY order_date DESC LIMIT 1000
  • D. SELECT region, COUNT(*) FROM orders GROUP BY region ORDER BY 2 DESC

Option B is correct because search optimization speeds up highly selective point lookups that return a few rows, such as an equality filter on a unique identifier. The date range, the broad filter with a sort and the full aggregation all touch many partitions, so they gain little.

Before turning on search optimization for a 40 TB table that receives heavy daily MERGE traffic, an engineer wants a projection of the build and maintenance credits. What should the engineer run?
  • A. SELECT SYSTEM$ESTIMATE_QUERY_ACCELERATION('<query_id>') for a representative lookup
  • B. SELECT SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS('sales.public.events') Correct answer
  • C. SELECT SYSTEM$CLUSTERING_INFORMATION('sales.public.events', '(event_id)')
  • D. EXPLAIN USING TEXT on a representative lookup query against the table

Option B is correct because SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS projects the build and maintenance credits for a table before the service is enabled. The query acceleration estimate, clustering information and EXPLAIN do not estimate search optimization cost.

Finance wants last month's credits for the serverless compute behind scheduled jobs and continuous file loads, per object. Which TWO ACCOUNT_USAGE views report those credits? (Select TWO).
  • A. SERVERLESS_TASK_HISTORY Correct answer
  • B. TASK_HISTORY
  • C. WAREHOUSE_METERING_HISTORY
  • D. COPY_HISTORY
  • E. PIPE_USAGE_HISTORY Correct answer

Options A and E are correct because SERVERLESS_TASK_HISTORY reports credits for serverless tasks and PIPE_USAGE_HISTORY reports credits for Snowpipe loading, each per object. TASK_HISTORY and COPY_HISTORY describe runs and loads rather than credits, and WAREHOUSE_METERING_HISTORY covers warehouses only.

Storage and Data Protection

Which statement restores a table that a developer dropped by mistake two hours ago, assuming its default Time Travel period?
  • A. UNDROP TABLE orders Correct answer
  • B. CREATE TABLE orders CLONE orders AT(OFFSET => -7200)
  • C. ALTER TABLE orders SWAP WITH orders_backup
  • D. COPY INTO orders FROM @%orders

Option A is correct because UNDROP TABLE restores a dropped table in place while it is still inside its Time Travel period, and two hours is well within the default day. Option B creates a clone of a table that no longer exists, so it fails. Option C needs a second table to swap with, and option D reloads nothing from an empty stage.

After CREATE OR REPLACE TABLE customers AS SELECT ... ran with a faulty filter, the original customers table, which keeps 7 days of Time Travel, is needed back in full under its own name. What restores it?
  • A. Run CREATE TABLE customers CLONE customers AT(OFFSET => -3600)
  • B. Run UNDROP TABLE customers while the replacement keeps its name
  • C. Ask Snowflake Support to recover the replaced version from Fail-safe
  • D. Rename the replacement table and then run UNDROP TABLE customers Correct answer

Option D is correct because CREATE OR REPLACE dropped the original version, and UNDROP refuses to run while another table holds the name customers. Renaming the replacement frees the name, and then UNDROP brings back the original with its full 7 days of history. Option B fails on the name clash, option A clones the new table (the original is gone from the name), and option C is unnecessary because the data is still in Time Travel.

Analysts in an Enterprise edition account must be able to restore any table in the REPORTING schema to its state at any chosen moment within the past 14 days, without opening support cases. Which setup meets this?
  • A. Keep a nightly clone of the schema for each of the past 14 days
  • B. Set DATA_RETENTION_TIME_IN_DAYS to 14 on the REPORTING schema Correct answer
  • C. Set MAX_DATA_EXTENSION_TIME_IN_DAYS to 14 on the REPORTING schema
  • D. Rely on Fail-safe, which holds changed data for 14 days

Option B is correct because DATA_RETENTION_TIME_IN_DAYS set to 14 on the schema gives every table in it 14 days of Time Travel, which Enterprise edition allows (up to 90). Option A costs far more storage and only gives daily points, not any chosen moment. Option C controls how long the retention of a table can be extended so streams do not go stale, not the Time Travel period. Option D is wrong because Fail-safe lasts 7 days and is only reachable through Snowflake Support.

Developers dropped several objects in a test account and want to know which of them an UNDROP statement can bring back during the Time Travel period. Which TWO objects qualify? (Select TWO).
  • A. A database, together with all of its schemas Correct answer
  • B. A standard view in a schema that still exists
  • C. A virtual warehouse and its size setting
  • D. A named internal stage and the files staged in it
  • E. A schema, together with the tables it contained Correct answer

Options A and E are correct because UNDROP works on databases, schemas and tables, and a dropped database or schema comes back with the objects it contained. A view, a warehouse and a stage are not restored by UNDROP, and a stage's files are not kept in Time Travel.

Data Governance

Auditors ask for a list of every column in the account that currently has a masking policy attached, with the policy name beside each column. Which source supplies this directly?
  • A. SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY, filtered on the policies referenced by queries
  • B. SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES, filtered on the masking policy kind Correct answer
  • C. SNOWFLAKE.ACCOUNT_USAGE.MASKING_POLICIES, which lists each policy with the columns it protects
  • D. INFORMATION_SCHEMA.COLUMNS, which carries a policy name for each protected column

Option B is correct because POLICY_REFERENCES lists every object and column with a policy attached, with the policy name and kind, so filtering on the masking policy kind gives the audit list directly. ACCESS_HISTORY (option A) shows policies applied by queries that ran, not what is attached now, and ACCOUNT_USAGE has no MASKING_POLICIES view that maps policies to columns (option C). INFORMATION_SCHEMA.COLUMNS (option D) has no policy name column.

Tag COST_CENTER is set to 'FINANCE' on database SALES and to 'RETAIL' on schema SALES.ORDERS. Table SALES.ORDERS.HEADERS has no tag of its own. Which value does the table report for COST_CENTER?
  • A. Both values, listed in the order of the object hierarchy
  • B. No value, because a table reports the tags set directly on it, not inherited ones
  • C. FINANCE, because a tag set on the database outranks tags set on objects below it
  • D. RETAIL, because the closest value overrides the one inherited from the database Correct answer

Option D is correct because tags are inherited down the object hierarchy and a value set on a lower object overrides the inherited one. The table has no tag of its own, so it inherits RETAIL from the schema, not FINANCE from the database (option C). A table does report inherited tags (option B) and reports one value per tag, not both (option A).

Columns across the account carry the tag PII. Some are VARCHAR and some are NUMBER. All of them, including columns tagged later, must be masked for roles without privileges. Which design meets this?
  • A. Create one VARCHAR masking policy and set it on the tag, so that numbers are cast to text when it runs
  • B. Create one masking policy with a VARIANT argument and set it on the PII tag
  • C. Set a masking policy on each tagged column and add a task that finds columns tagged later
  • D. Create one VARCHAR masking policy and one NUMBER masking policy, and set both on the PII tag Correct answer

Option D is correct because a tag-based masking policy must match the data type of the columns it protects, so VARCHAR and NUMBER columns need one policy each, both set on the PII tag. Columns tagged later are then masked automatically. A single VARCHAR policy (option A) or a VARIANT argument (option B) does not match NUMBER columns, and per-column policies with a task (option C) leave a gap until the task runs.

A steward is checking which policies protect the columns of one table, SALES.PUBLIC.CUSTOMERS. Which TWO sources return that information? (Select TWO).
  • A. SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES view Correct answer
  • B. SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY view
  • C. SHOW GRANTS ON TABLE SALES.PUBLIC.CUSTOMERS
  • D. INFORMATION_SCHEMA.POLICY_REFERENCES table function Correct answer
  • E. SHOW MASKING POLICIES IN SCHEMA SALES.PUBLIC

Options A and D are correct because the ACCOUNT_USAGE POLICY_REFERENCES view and the INFORMATION_SCHEMA POLICY_REFERENCES table function both list the policies attached to a given object. LOGIN_HISTORY records sign-ins, SHOW GRANTS lists privileges, and SHOW MASKING POLICIES lists policy definitions in a schema, not what is attached to one table.

Data Transformation

Which clause of CREATE FUNCTION fixes the data type that a scalar UDF hands back to the query calling it?
  • A. RETURNS Correct answer
  • B. RUNTIME_VERSION
  • C. HANDLER
  • D. LANGUAGE

Option A is correct because the RETURNS clause of CREATE FUNCTION declares the data type of the value (or the table shape) the function hands back to the calling query. RUNTIME_VERSION picks the language runtime, HANDLER names the code entry point, and LANGUAGE selects the language, so none of them set the return type.

The schema holds two functions named CLEAN_TEXT: one declared with a single VARCHAR argument and one declared with VARCHAR and NUMBER arguments. How does Snowflake decide which one SELECT CLEAN_TEXT(name) runs?
  • A. It runs the function owned by the caller's current role
  • B. It runs the function that was created or replaced most recently
  • C. It runs the function whose argument list is shortest in characters
  • D. It matches the call to the signature by the number and types of the arguments Correct answer

Option D is correct because Snowflake supports overloading and resolves a call by the number and types of the arguments, so CLEAN_TEXT(name) with one VARCHAR matches the one-argument signature. Ownership, creation time and the length of the argument list play no part in resolution.

The owner of a SQL UDF named NORMALIZE_PHONE with one VARCHAR argument wants to read its definition text without recreating anything. Which TWO statements return it? (Select TWO).
  • A. SHOW GRANTS ON FUNCTION NORMALIZE_PHONE(VARCHAR)
  • B. DESCRIBE FUNCTION NORMALIZE_PHONE(VARCHAR) Correct answer
  • C. EXPLAIN USING TEXT SELECT NORMALIZE_PHONE('555-0100')
  • D. SELECT GET_DDL('FUNCTION', 'NORMALIZE_PHONE(VARCHAR)') Correct answer
  • E. SHOW USER FUNCTIONS LIKE 'NORMALIZE_PHONE'

Options B and D are correct because DESCRIBE FUNCTION returns the function's properties including its body, and GET_DDL returns the full CREATE statement. SHOW GRANTS lists privileges, EXPLAIN shows a query plan, and SHOW USER FUNCTIONS lists metadata without the definition text.

Lead-scoring rules live in lead_rules.py, which sits in the internal stage @code_stage, and the Python UDF SCORE_LEAD must import that module. Which TWO changes make the module available to the handler? (Select TWO).
  • A. Add PACKAGES = ('@code_stage/lead_rules.py') to the CREATE FUNCTION statement
  • B. Call session.add_import('@code_stage/lead_rules.py') in Snowpark before registering the UDF Correct answer
  • C. Add IMPORTS = ('@code_stage/lead_rules.py') to the CREATE FUNCTION statement Correct answer
  • D. Set the HANDLER clause to '@code_stage/lead_rules.score_lead'
  • E. Attach an external access integration whose network rule allows the stage location

Options B and C are correct because a staged module is made importable either by the IMPORTS clause of CREATE FUNCTION or by session.add_import in Snowpark. PACKAGES takes Anaconda package names, HANDLER names a function, and an external access integration only opens network egress.

Free Study Guide

Read the free Snowflake DEA-C02 study guide: every exam topic explained in plain English, with diagrams, exam tips and the mistakes to avoid.

More Snowflake Practice Exams

All Snowflake practice exams →

Practice with realistic mock exams to prepare for your Snowflake certification.