DEA-C02 Practice Questions

Question 1

Refer to the exhibit. A security administrator executes this query to audit access to a sensitive table. What specific information is captured in the 'base_objects_accessed' column regarding the data lineage of this query?

Accepted Answer

It identifies the underlying tables that provided the data, even if the user queried a view.. The ACCESS_HISTORY view is a powerful tool for tracking data movement and consumption. The 'base_objects_accessed' field specifically records the underlying source tables (the 'base' objects) that provided the data, even if the user queried a view or a series of nested views. This is vital for accurate compliance reporting and understanding the ultimate source of truth.

Answer

It lists only the immediate view name that the user referenced in their SELECT statement.

Answer

It displays the metadata of the warehouse used to execute the query for the table.

Answer

It identifies the specific columns and rows that were filtered out by the query optimizer.

Question 2

A data engineer is tasked with migrating small, frequent batches of data into Snowflake. Which feature is most appropriate to keep costs low while ensuring the data is processed continuously?

Accepted Answer

Using Snowpipe for serverless continuous ingestion.. Snowpipe is highly efficient for continuous ingestion because it only charges for the actual compute used during the load. For small, frequent batches, it avoids the cost of keeping a warehouse running 24/7. This cost-effectiveness makes it the ideal choice for real-time ingestion patterns, ensuring that the organization pays only for the resources consumed during data movement, rather than paying for idle compute cycles in a traditional, scheduled warehouse setup.

Answer

A virtual warehouse running 24/7 with a scheduled task.

Answer

Triggering a stored procedure every minute to check the storage stage.

Answer

Using a large multi-cluster warehouse to process the batches in parallel.

Question 3

A data engineering team needs to create a set of tables for an ETL process that involves massive intermediate data transformations. These tables should persist across multiple sessions for 24 hours but do not require long-term disaster recovery protection. Which table type should be used to minimize storage costs while meeting these requirements?

Accepted Answer

Transient Tables. Transient tables are designed specifically for data that needs to persist beyond a single session but does not require the 7-day Fail-safe protection offered by permanent tables. By excluding Fail-safe, Snowflake reduces the storage footprint and associated costs, making them ideal for intermediate ETL stages where data can be easily re-created if lost.

Answer

Temporary Tables

Answer

External Tables

Answer

Permanent Tables

Question 4

What is the primary benefit of using Snowflake's Object Tagging for cost attribution?

Accepted Answer

It allows costs to be associated with specific business projects.. Object Tagging allows organizations to assign costs to specific business units, projects, or applications by labeling the tables and schemas they use. This is critical for internal showback/chargeback models. By tracking resource usage at the tagged object level, finance teams can accurately map Snowflake costs to specific departments, encouraging better resource management and accountability across the organization's data footprint.

Answer

It automatically reduces the storage costs by compressing data.

Answer

It enables query results to be cached more efficiently.

Answer

It replaces the need for Resource Monitors.

Question 5

A data engineer needs to filter the results of a complex analytical query based on the result of a window function. The query calculates a rolling average of sales per region and should only return rows where the current sale exceeds that average. Which SQL clause is most efficient for this transformation?

Accepted Answer

The QUALIFY clause. Filtering on window functions requires a mechanism that executes after the window calculations are performed. The QUALIFY clause allows engineers to filter results directly in the SELECT statement without nesting logic inside a subquery or a Common Table Expression. This significantly improves query readability and can lead to internal optimizations by the Snowflake query optimizer during the execution phase.

Answer

The WHERE clause

Answer

The HAVING clause

Answer

The GROUP BY clause

Question 6

If a permanent table in a Snowflake Enterprise edition account is dropped, and it had a 10-day Time Travel retention period, what is the total duration until the data is permanently unrecoverable by any means?

Accepted Answer

17 days, including 10 days of Time Travel and 7 days of Fail-safe.. For permanent tables, the data protection lifecycle consists of the Time Travel period followed by the Fail-safe period. After the table is dropped, it remains in Time Travel for 10 days (allowing UNDROP). Then, it automatically moves into Fail-safe for another 7 days. Once both periods expire (17 days total), the data is purged.

Answer

10 days, after which the data is purged from the storage layer.

Answer

7 days, as the Fail-safe period overrides the Time Travel period upon dropping.

Answer

90 days, which is the maximum Time Travel limit for Enterprise accounts.

Question 7

A data engineer is implementing a Python User-Defined Function (UDF) to perform complex string manipulation. To optimize performance for a large-scale transformation, the engineer wants to ensure the UDF processes multiple rows in a single call. Which type of UDF should be implemented?

Accepted Answer

A Vectorized Python UDF. Snowflake's Vectorized Python UDFs allow for high-performance processing by passing batches of rows as Pandas DataFrames or Series. This reduces the overhead associated with calling the function for every individual row. For data transformations involving heavy computational logic or libraries like NumPy, vectorized UDFs are significantly more efficient than standard scalar UDFs which process data row-by-row.

Answer

A scalar Python UDF with a loop

Answer

A Python User-Defined Table Function (UDTF)

Answer

A JavaScript UDF using the 'async' keyword

Question 8

A data engineer changes the DATA_RETENTION_TIME_IN_DAYS parameter for a schema from 1 to 30. How does this change affect the existing permanent tables within that schema?

Accepted Answer

Tables without an explicit retention setting will inherit the new 30-day period.. Parameters in Snowflake follow a hierarchy where object-level settings override schema-level settings, which override account-level settings. If a table does not have its own specific retention period set, it inherits the new value from the schema. However, if a table was explicitly created with its own retention period, that setting remains unchanged.

Answer

All existing tables in the schema are immediately updated to 30 days of retention.

Answer

The change only applies to new tables created after the parameter was modified.

Answer

The schema-level change is ignored unless the account-level retention is also 30.

Question 9

Which of the following describes the purpose of the 'Result Cache' in Snowflake?

Accepted Answer

It provides instant access to query results without compute costs.. The Result Cache is a managed feature that stores the output of a query for a period (typically 24 hours). If an identical query is submitted later, Snowflake returns the cached results immediately without using any compute resources. This is a critical performance and cost optimization tool, as it eliminates the need to re-process large datasets for common, repeated queries, providing near-zero latency and zero credit consumption.

Answer

It stores data blocks to improve subsequent table scans.

Answer

It allows users to manually clear cache for specific tables.

Answer

It persists query results permanently in the user's stage.

Question 10

A data engineer wants to share a subset of data with a third party while ensuring sensitive columns are masked. Which governance combination is best?

Accepted Answer

Create a secure view and apply masking policies to the base table columns.. The best practice is to combine a Secure View with a Dynamic Data Masking policy. The Secure View provides a clean abstraction layer, while the Masking Policy ensures that the data itself remains protected regardless of how the view is queried. This combination allows for precise data sharing while adhering to strict compliance standards, protecting the organization from data leaks during the sharing process.

Answer

Create a standard view with CASE statements for masking.

Answer

Grant direct access to the base table.

Answer

Physically clone the table and remove sensitive rows.

Question 11

When unloading data from Snowflake to an external stage, which TWO of the following are supported file formats?

Accepted Answer

CSV. Unloading data is just as important as loading. Snowflake supports CSV, JSON, and Parquet for unloading, providing flexibility for downstream systems. Understanding which formats are natively supported allows engineers to choose the right format for compatibility with external tools (like BI platforms or data lakes) without needing additional transformation steps, making the data movement process more efficient and standardized.

Answer

XLSX

Answer

SQL

Answer

PDF

Question 12

During a data loading process using the COPY command, the data engineer notices that the same files are being processed multiple times, leading to duplicate records. Which Snowflake feature is likely being bypassed or misconfigured?

Accepted Answer

The Load History metadata maintained by the COPY command.. Snowflake's COPY command and Snowpipe both utilize 'load history' metadata to ensure that each unique file is only loaded once into a specific table. This history is maintained for 64 days. If duplicates are appearing, it is often because the files were modified (changing their checksum) or the engineer is using a method that doesn't check the history, such as using a FORCE=TRUE parameter.

Answer

The Change Data Capture (CDC) mechanism in the Snowflake Stream.

Answer

The transactional integrity of the Snowflake Virtual Warehouse.

Answer

The automatic deduplication feature of the VARIANT data type.

Question 13

A data engineer creates a table in a Snowflake database that resides on a standard (non-replicated) storage. The engineer wants to confirm that Fail-safe will protect the table after Time Travel expires. Which table type should the engineer use?

Accepted Answer

Permanent table. Fail-safe is exclusive to permanent tables. It provides a non-configurable 7-day window after Time Travel expires, during which Snowflake Support can recover data. Transient, temporary, and external tables do not receive Fail-safe coverage, so only a permanent table satisfies the requirement described in the scenario.

Answer

External table

Answer

Transient table

Answer

Temporary table

Question 14

A data engineer manages a Snowflake account with a database named PROD_DB. The database contains a schema SALES with a table ORDERS. The engineer wants to clone the entire PROD_DB database to a new database named DEV_DB for testing. The PROD_DB database is large, but the engineer needs the clone to be available immediately. Which statement accurately describes the storage consumption and availability of the cloned database?

Accepted Answer

The clone consumes no additional storage initially, and data changes in DEV_DB will consume additional storage only for the changed micro-partitions.. Zero-copy cloning creates a new database that shares the original micro-partitions without duplicating data. The clone is immediately available and consumes no additional storage at creation. Subsequent changes in either the source or the clone create new micro-partitions, and storage is billed only for those new partitions. This approach is efficient for creating development or testing environments.

Answer

The clone is not available immediately; it must complete a background copy process before it can be queried.

Answer

The clone shares storage with the source, and any changes to the source will also affect the clone, consuming additional storage in both databases.

Answer

The clone consumes storage equal to the size of the source database because it copies all data physically.

Question 15

A data engineer is designing a data protection strategy for a set of permanent tables in a Snowflake Enterprise Edition account. The engineer needs to ensure that accidental data modifications can be reversed and that dropped tables can be restored by the team without opening a support ticket. Which two configuration choices support these goals? (Choose two.)

Accepted Answer

Create zero-copy clones of critical tables on a schedule to maintain independent point-in-time copies.. Extending Time Travel retention beyond the default gives the team a longer window to query historical states and run UNDROP without support involvement. Scheduled zero-copy clones add independent recovery points that share storage until data diverges. Together these provide self-service reversal of modifications and restoration of dropped tables, whereas Fail-safe, transient tables, and disabling Time Travel all fail to meet the stated recovery objectives.

Answer

Disable Time Travel on critical tables to avoid storage charges and rely solely on external backups.

Answer

Rely on Fail-safe to restore dropped tables without involving Snowflake Support.

Answer

Use transient tables for all critical data to reduce storage costs while keeping recovery options.

SnowPro Advanced: Data Engineer DEA-C02 practice test

Three ways to study

Study DEA-C02 by domain

Study DEA-C02 by topic

Data Transformation practice questions

Performance Optimization practice questions

Storage and Data Protection practice questions

Data Governance practice questions

Data Movement practice questions

DEA-C02 fundamentals practice questions

DEA-C02 scenario practice questions

DEA-C02 troubleshooting practice questions

SnowPro Advanced: Data Engineer practice questions