DEA-C02 Practice Questions 1 — 50 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.

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.

Question 16

Refer to the exhibit. What is the DATA_RETENTION_TIME_IN_DAYS setting for the table after the UNDROP operation?

Accepted Answer

It remains 5.. When a table is dropped, its metadata and parameter settings are preserved. When the UNDROP command restores the table, it retrieves the original configuration, including the previously set DATA_RETENTION_TIME_IN_DAYS value of 5. This ensures that object-level policies remain consistent after recovery, allowing the Data Engineer to maintain control over historical data retention settings without needing to reconfigure them after accidental deletions.

Answer

It reverts to the database default.

Answer

It reverts to the account default.

Answer

It is reset to 1.

Question 17

A healthcare company implements a Row Access Policy (RAP) on a PATIENTS table to restrict doctor access to only their assigned patients. The RAP references a mapping table. What is the most critical performance consideration when designing this policy for a table with billions of rows?

Accepted Answer

Ensuring the mapping table is small and uses columns that allow for effective pruning.. When Row Access Policies involve joins to mapping tables, Snowflake's optimizer must execute these checks efficiently to avoid full table scans. Using a memoizable function or ensuring the mapping table is small and clustered correctly helps the pruning process. Governance at scale requires balancing strict security logic with the underlying query performance to ensure user experience is maintained.

Answer

Applying the policy to the mapping table itself to prevent circular references.

Answer

Using a CASE statement instead of a WHERE clause within the policy definition.

Answer

Granting the OWNERSHIP privilege of the mapping table to the PUBLIC role.

Question 18

A financial institution uses Snowflake to store customer transactions. A data engineer needs to implement a policy that restricts access to rows in the TRANSACTIONS table based on the department of the user. The department information is stored in a lookup table named USER_DEPARTMENT. The policy must be applied dynamically without modifying the TRANSACTIONS table. Which Snowflake feature should the engineer use?

Accepted Answer

Implement a Row Access Policy that uses a mapping table to determine the user's department and filters rows accordingly.. Row Access Policies are designed to filter rows based on user attributes or mapping tables. They attach to tables and enforce filtering at query time, making them ideal for dynamic row-level security. Secure views can be bypassed if base table access is granted, and masking policies only affect column values, not row visibility. Thus, a Row Access Policy is the correct solution.

Answer

Create a secure view that joins TRANSACTIONS with USER_DEPARTMENT and filters rows based on the current user's department.

Answer

Use a Column-level Security policy with a masking policy that returns NULL for rows not belonging to the user's department.

Answer

Create a dynamic data masking policy that checks the user's department and masks the entire row if the department does not match.

Question 19

A financial services firm stores customer records in a table called TRANSACTIONS. The compliance team requires that a specific column, CREDIT_CARD_NUMBER, be transformed so that only the last four digits are visible to all users except members of the role PAYMENT_ADMIN. Additionally, the transformation must occur at query time without modifying the stored data. Which Snowflake feature should the data engineer use to meet this requirement?

Accepted Answer

A masking policy applied to the CREDIT_CARD_NUMBER column.. The requirement is to dynamically mask a column based on the user's role while preserving the original data. A masking policy attached to the column evaluates at query time and can return different values depending on the role. It does not alter stored data, and it can show only the last four digits for non-privileged roles while revealing the full value for PAYMENT_ADMIN. This is the standard Snowflake method for column-level dynamic data masking.

Answer

A tag-based masking policy using the TAG_STRING system function.

Answer

A row access policy applied to the TRANSACTIONS table.

Answer

A secure view that selects only the last four digits of the column.

Question 20

A data engineer is unloading a large fact table to an external stage pointing at an Amazon S3 bucket. The downstream consumer requires many small files for parallel processing, and each file must be no larger than 64 MB. Which COPY INTO location options should the engineer use?

Accepted Answer

MAX_FILE_SIZE = 67108864 and SINGLE = FALSE. MAX_FILE_SIZE sets an upper bound on each unloaded file and takes a byte value, so 64 MB must be written as 67108864. SINGLE = FALSE lets the unload split output across multiple files. Used together, they produce many files each capped at the requested size, which is what the downstream parallel consumer requires.

Answer

MAX_FILE_SIZE = 64 and SINGLE = FALSE

Answer

MAX_FILE_SIZE = 67108864 and SINGLE = TRUE

Answer

FILE_FORMAT = (TYPE = CSV COMPRESSION = GZIP) and SINGLE = FALSE