DEA-C02 Practice Test 16 — 15 Questions

Question 1

Which Snowflake edition is required to support a 90-day Time Travel retention period?

Accepted Answer

Enterprise Edition.. Snowflake's Time Travel feature scales its capability based on the edition. Standard Edition is limited to a 1-day retention period. Enterprise Edition and Business Critical Edition support up to 90 days. This tier-based differentiation is a fundamental concept in Snowflake's storage and data protection architecture, requiring architects to align their service level agreements with the appropriate edition to meet data recovery and compliance needs.

Answer

Standard Edition.

Answer

Any edition supports 90 days by default.

Answer

Only the Trial Edition.

Question 2

Refer to the exhibit. If the output shows TABLE_TYPE='BASE TABLE', IS_TRANSIENT='YES', and RETENTION_TIME='1', which statement correctly describes the storage behavior if this table is accidentally dropped?

Accepted Answer

The table can be recovered using UNDROP for exactly 24 hours after the drop.. The exhibit describes a transient table. Transient tables are permanent objects that persist across sessions but are specifically designed to have no Fail-safe period. They support Time Travel for up to 1 day. If dropped, the table can be recovered using UNDROP within that 24-hour window, but once that day passes, it is gone forever.

Answer

The table can be recovered by Snowflake Support for up to 7 days after the drop.

Answer

The table will stay in Time Travel for 1 day, then move to Fail-safe for 7 days.

Answer

The table is not recoverable because transient tables do not support the UNDROP command.

Question 3

An organization wants to classify their data to identify PII. Which feature should they use to automatically tag columns containing sensitive information?

Accepted Answer

Data Classification.. Object Tagging is the primary governance feature for metadata management. When combined with Snowflake's Data Classification capability, it can automatically scan and suggest tags for columns based on their content, such as PII or financial data. This automation reduces manual labor and ensures that governance policies are consistently applied across large, complex schemas, which is fundamental to maintaining a high-quality data catalog.

Answer

Row Access Policies.

Answer

Dynamic Data Masking.

Answer

Query Profile.

Question 4

Which of the following is the most cost-effective way to handle massive concurrent read-only queries?

Accepted Answer

Use a multi-cluster warehouse with economy scaling.. Multi-cluster warehouses are designed specifically to handle high concurrency. By setting the scaling policy to 'Economy', Snowflake adds clusters only when the queue grows, prioritizing cost over immediate startup. This allows the system to scale horizontally to meet demand without requiring manual intervention, effectively balancing user experience with credit consumption. It is the standard solution for environments where dashboard traffic spikes and performance must be maintained without over-provisioning compute resources.

Answer

Increase the warehouse size.

Answer

Enable query pruning.

Answer

Create materialized views for every user.

Question 5

Which feature allows a Data Engineer to define a transformation pipeline where the target table automatically updates when the source table changes, without needing to manually define a schedule?

Accepted Answer

Dynamic Tables. Dynamic Tables provide a declarative approach to data engineering. By defining the target table based on a SQL query, Snowflake manages the dependency graph and incrementally updates the data. This abstracts away the complexity of managing tasks, streams, and manual refresh schedules, making it the most modern and efficient way to handle continuous data transformation in a pipeline.

Answer

Tasks with Streams

Answer

Materialized Views

Answer

Stored Procedures

Question 6

Refer to the exhibit. A data engineer is analyzing a Query Profile for a long-running join operation. Based on the provided JSON statistics, what is the most effective action to improve the performance of this specific query?

Accepted Answer

Scale up the virtual warehouse to a larger size.. The exhibit shows significant spillage to both local and remote disk, with 1.2TB reaching remote storage. Remote spillage is a critical performance bottleneck because it involves network latency to S3/Azure Blob. Moving to a larger warehouse provides more memory and local storage per node, allowing the join to stay in-memory or on faster local SSDs.

Answer

Enable the Search Optimization Service on the join columns.

Answer

Apply a clustering key to the tables involved in the join.

Answer

Rewrite the query to use a Common Table Expression (CTE).

Question 7

Refer to the exhibit. A data engineer runs SYSTEM$CLUSTERING_INFORMATION on a table. Based on the output, what is the most accurate interpretation of the table's current state?

Accepted Answer

The clustering depth indicates that a significant number of micro-partitions overlap.. The clustering information provides metrics on how well data is grouped within micro-partitions. An average depth of 8.4 and high average overlaps (12.5) relative to the total partitions suggest that the table is not well-clustered for the specified keys. This indicates that queries filtering on C1 and C2 will likely scan more partitions than necessary.

Answer

The table is perfectly clustered because the average depth is less than 10.

Answer

The table does not have a clustering key defined, so the depth is irrelevant.

Answer

The constant partition count of 100 means the table is mostly read-only.

Question 8

A data engineer needs to move data from a Snowflake table in the 'US-WEST-2' region to another Snowflake table in the 'EU-CENTRAL-1' region. What is the most resilient and automated way to keep these tables synchronized?

Accepted Answer

Use the Snowflake Database Replication feature to sync the database containing the table to the target region.. Snowflake's Database Replication and Failover features are designed specifically for cross-region data movement. By setting up a secondary database in the target region and enabling replication, Snowflake handles the secure transfer of data and metadata automatically. This is more resilient than manual export/import processes and leverages Snowflake's global backbone for data transfer.

Answer

Unload the data to an S3 bucket in US-WEST-2 and use Snowpipe to load it into EU-CENTRAL-1.

Answer

Create an External Function that calls a Python script to copy data via the Snowflake Connector.

Answer

Set up a Secure Data Share between the two regions and use a CREATE TABLE AS SELECT statement.

Question 9

What is the primary purpose of a 'Secure View' in Snowflake from a governance perspective?

Accepted Answer

To hide the underlying view definition and schema metadata.. A Secure View is designed to prevent users from seeing the underlying logic or source data of the view, even if they have been granted select access. By hiding the view definition and internal metadata, organizations can expose specific data subsets to external partners or internal teams without risking the disclosure of proprietary business rules or underlying table structures that might contain sensitive information.

Answer

To increase the performance of complex joins.

Answer

To automatically mask PII in the output.

Answer

To allow users to modify the base tables.

Question 10

A data engineer wants to optimize a query that performs a point lookup on a value nested deep within a VARIANT column in a 50TB table. Which approach is the most effective for optimizing this lookup?

Accepted Answer

Enable Search Optimization on the VARIANT column's specific paths.. The Search Optimization Service in Snowflake supports semi-structured data, including fields within VARIANT, OBJECT, and ARRAY types. By enabling search optimization on these specific paths, Snowflake creates a specialized index that allows for efficient point lookups of nested values, significantly reducing the amount of data scanned from the 50TB table.

Answer

Flatten the VARIANT column into a Materialized View.

Answer

Cluster the table based on the extracted value from the VARIANT column.

Answer

Use a standard VIEW to pre-parse the VARIANT data for all users.

Question 11

A table experiences performance degradation over time due to frequent DML operations (INSERT/UPDATE/DELETE). What is the most likely cause?

Accepted Answer

Micro-partition fragmentation.. Frequent DML operations lead to 'micro-partition fragmentation'. When rows are updated or deleted, the micro-partitions become sub-optimally filled or 'dirty'. This forces the query engine to scan more partitions than necessary because the data is no longer organized linearly. This fragmentation is a common performance bottleneck in write-heavy environments, and it requires periodic table maintenance or clustering to restore the efficiency of the partition pruning process for subsequent reads.

Answer

The metadata cache is exceeding its capacity.

Answer

The warehouse cache is becoming corrupted.

Answer

The result cache is being constantly invalidated.

Question 12

What is the primary benefit of using `CLONE` for data transformation and testing?

Accepted Answer

It provides a cost-effective way to create isolated development environments.. Cloning (Zero-Copy Cloning) allows an engineer to create a full copy of a table or database instantly without duplicating the underlying data. This is invaluable for testing transformations in a sandbox environment without incurring storage costs or taking up time for massive data movement. Any changes made in the cloned object are isolated from the original, ensuring that production pipelines remain unaffected during the development and validation of new transformation logic.

Answer

It automatically updates the source table with new transformations.

Answer

It allows for the conversion of Parquet files to internal tables.

Answer

It enables multi-region replication of data.

Question 13

Which TWO of the following are considered 'anti-patterns' for performance in Snowflake?

Accepted Answer

Using one large warehouse for all user workloads.. Using a single massive warehouse for all workloads leads to poor resource isolation and contention, as simple queries compete with heavy ones. Additionally, using SELECT * in production code is an anti-pattern because it retrieves unnecessary columns, forcing the system to read more data blocks than required. Both practices degrade performance and increase costs by wasting I/O and compute resources on operations that could be avoided.

Answer

Specifying column names instead of SELECT *.

Answer

Utilizing multi-cluster warehouses for concurrency.

Answer

Implementing materialized views for performance.

Question 14

A data engineer is loading 1TB of data from an S3 bucket into Snowflake using the COPY command. The data consists of 10,000 small files (approx. 100KB each). How will this file size affect the loading performance?

Accepted Answer

Loading will be slow due to the high overhead of processing many small files.. Snowflake performance is optimized when file sizes are between 100MB and 250MB compressed. Having 10,000 tiny files creates excessive overhead because each file requires a separate metadata operation and a separate request to the cloud storage. This prevents the warehouse from effectively parallelizing the load and saturating its compute nodes.

Answer

Performance will be optimal because many files allow for maximum parallelism.

Answer

The warehouse will automatically group the files into larger chunks before loading.

Answer

Snowflake will only use a single thread to maintain data integrity for small files.

Question 15

Refer to the exhibit. Based on the Query Profile, what is the most likely bottleneck for this query?

Accepted Answer

Inefficient data filtering and large data scans.. In this profile, the Table Scan accounts for the vast majority (85%) of the total query time. This indicates that the query is spending most of its duration reading data from storage rather than performing complex joins or aggregations. The lack of remote disk spilling suggests that memory is sufficient. Therefore, improving the scan performance through clustering or partition pruning is the most logical step to reduce execution time.

Answer

Insufficient warehouse memory.

Answer

Network congestion between nodes.

Answer

Excessive joins causing data shuffling.