DEA-C02 Practice Test 1 — 10 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?

Choose an answer to begin — your selection is scored in the full session.

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.