Home Certifications Editorials

Exam overview and study plan

SnowPro Data Engineer DEA-C02 Practice Questions

SnowPro Advanced: Data Engineer

Read questions, check verified answers and discuss the reasoning. Use reading options to shape your revision, or switch to the simulator for timed practice.

View 57 free questions

57 questions in this view

Page 1 of 10

Question 1

A company is using Snowflake Enterprise edition. A Data Engineer needs to optimize storage costs for a high-churn table that is recreated daily.

The Engineer changes the table type from permanent to transient and sets the DATA_RETENTION_TIME_IN_DAYS = 1. The Engineer also creates a clone of the table before the daily recreation process and sets the DATA_RETENTION_TIME_IN_DAYS = 1 for the clone.

Four hours later, the table is accidentally dropped.

Which methods can be used to retrieve the data? (Select TWO).

Use the UNDROP TABLE command to restore the table.
Use a query with the AT(OFFSET => -14400) syntax, then recreate the table using a CREATE TABLE AS SELECT statement.
Restore the data from the clone created before the table was dropped using the CREATE TABLE ... CLONE command.
Run a SELECT * FROM TABLE(RESULT_SCAN('<query_id>')) query using the query ID from the last query run on the table.
Extend Time Travel using ALTER TABLE SET DATA_RETENTION_TIME_IN_DAYS = 7 on the dropped table, then use the TIMESTAMP identifier to recover it.

Select an answer above, then submit your vote. Reading or practicing does not cast a vote.

Question 2

A Data Engineer needs to optimize a very large fact table in a database that is frequently joined on the customer_id column and filtered by the transaction_time column.

The customer_id column has nearly 50,000 unique values and the transaction_time data is a TIMESTAMP with millions of unique, discrete values with nanosecond precision.

Which clustering key configuration will OPTIMIZE pruning and MINIMIZE credit consumption?

CLUSTER BY (customer_id, transaction_time)
CLUSTER BY (date_trunc('month', transaction_time), customer_id)
CLUSTER BY (to_date(transaction_time), customer_id)
CLUSTER BY (customer_id, to_date(transaction_time))

Select an answer above, then submit your vote. Reading or practicing does not cast a vote.

Question 3

A Data Engineer stores JSON data in a VARIANT column.

Which factors will prevent JSON elements from being extracted into columnar storage, impacting query performance? (Select TWO).

The elements contain an explicit JSON null value in one or more rows.
The elements contain keys that include special characters, spaces, or Unicode characters.
An element is missing from a subset of rows and is represented as a SQL NULL.
The elements contain string-based ISO 8601 timestamps that are stored as native VARCHAR.
The elements contain multiple data types across different rows.

Select an answer above, then submit your vote. Reading or practicing does not cast a vote.

Question 4

A Data Engineer needs to protect the ACCOUNT_NAME column in the dynamic table, ACCT_DIM. The table is sourced from the base table, ACCOUNT.

How should the data in the dynamic table be protected?

ALTER DYNAMIC TABLE ACCT_DIM DROP ROW ACCESS POLICY <policy_name>, ADD ROW ACCESS POLICY <policy_name> ON ACCOUNT_NAME;
ALTER DYNAMIC TABLE ACCT_DIM MODIFY COLUMN ACCOUNT_NAME SET MASKING POLICY <policy_name>;
ALTER TABLE ACCOUNT MODIFY COLUMN ACCOUNT_NAME SET MASKING POLICY <policy_name>;
ALTER DYNAMIC TABLE ACCT_DIM ADD ROW ACCESS POLICY <policy_name> ON ACCOUNT_NAME;

Select an answer above, then submit your vote. Reading or practicing does not cast a vote.

Question 5

A Data Engineer is using a Snowpark Python User-Defined Function (UDF) that calls a third‑party fraud‑scoring REST API with the requests library. The API key is not hard‑coded in the handler.

The UDF is registered as a permanent function and works in local testing, but when executed in Snowflake, it consistently fails with name‑resolution and network errors. The Engineer wants to avoid embedding the API key in code, running everything within Snowflake.

How should these requirements be met?

Deploy an external Python service that uses the Snowflake Connector for Python to read unscored transactions. Call the fraud ‑ scoring API with requests, and write the scores back to Snowflake tables. Then schedule this process by having Snowflake tasks insert work into a control table.
Store the API key in a secure internal table and have the Python UDF read the key with a SELECT statement at runtime. Then configure a network policy on the account to allow outbound HTTPS traffic so the UDF can call the fraud ‑ scoring API directly over the public internet.
Rewrite the fraud ‑ scoring logic as a Snowpark stored procedure that uses the same request code and calls the procedure from SQL when scoring is needed. Then configure stored procedures to perform outbound HTTP calls to the external API.
Create an external access integration with a network rule that allows outbound HTTPS to the fraud ‑ scoring API host. Store the API key in a SECRET , and recreate the Python UDF specifying EXTERNAL_ACCESS_INTEGRATIONS and SECRETS , so the handler uses the Snowflake APIs to read the secret and call the external service.

Select an answer above, then submit your vote. Reading or practicing does not cast a vote.

Question 6

A Data Engineer needs to create an abstraction layer for an AI application that defines business metrics and relationships between the relevant data tables.

Which solution options will meet the requirements? (Select TWO).

Use the Snowflake REST API to define the business metrics.
Use Cortex Search to infer the relationships.
Convert JSON files into a semantic layer.
Use SQL commands to create a semantic layer.
Convert YAML files into a semantic layer.

Select an answer above, then submit your vote. Reading or practicing does not cast a vote.

Page 1 of 10

Build confidence for exam day with expert-reviewed questions and realistic practice. Your next certification starts here.

Company

About Contact Our Authors FAQ

Trust and policies

Methodology Editorial policy Terms and conditions Privacy policy

Contact

ExamPrepare LLC5203 Juan Tabo Blvd STE 2BAlbuquerque, NM 87111[email protected]

© 2026 ExamPrepare LLC. All rights reserved. All certification names, exam codes and logos are the property of their respective owners. All associated trademark and copyright rights belong to those owners. ExamPrepare is not affiliated with, endorsed by, or sponsored by any certification vendor.