DEA-C02 Practice Test 2 — 10 Questions

Question 1

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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 10

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