DEA-C02 Practice Test 5 — 10 Questions

Question 1

A data engineer needs to unload a large table to an external stage in Parquet format. The table contains a column with sensitive data that must be masked in the unloaded files. The engineer wants to use a secure view that applies a masking policy, and then unload from that view. Which statement accurately describes the behavior when unloading from a secure view?

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

Accepted Answer

Unloading from a secure view is allowed, and the masking policies defined in the view are applied to the unloaded data.. COPY INTO <location> supports unloading from secure views, and the secure view's definition, including masking and row access policies, is enforced during the unload. This means sensitive columns can be masked in the output files. The other options either deny support, claim masking is bypassed, or impose unnecessary role requirements. The correct behavior is that policies are applied, ensuring data security.

Answer

Unloading from a secure view is allowed, but the masking policies are not applied; the underlying data is unloaded unmasked.

Answer

Unloading from a secure view is not supported; COPY INTO <location> requires a base table or a regular view.

Answer

Unloading from a secure view requires the ACCOUNTADMIN role and the masking policies are ignored.

Question 2

A data engineer needs to protect a set of permanent tables in a Snowflake Enterprise Edition account. The requirement is to allow querying of historical versions for up to 30 days and to allow recovery of dropped tables within that same window. Which two actions should the engineer take to meet these requirements? (Choose two.)

Accepted Answer

Confirm the account is on Enterprise Edition, which supports retention windows longer than the Standard Edition one-day maximum.. Meeting a 30-day queryable-history requirement on permanent tables requires Enterprise Edition, which raises the maximum retention from one day to ninety days, and an explicit DATA_RETENTION_TIME_IN_DAYS setting of 30 on each table. Transient tables cap retention at one day, Fail-safe is non-queryable and fixed at seven days, and there is no configurable FAILSAFE_DAYS parameter.

Answer

Convert the permanent tables to transient tables to increase the maximum allowed retention window.

Answer

Rely on Fail-safe to provide the 30-day queryable history window for dropped tables.

Answer

Enable Fail-safe on the tables by setting FAILSAFE_DAYS to 30 using ALTER TABLE.

Question 3

A data engineer manages a transient table named STG_EVENTS in a Snowflake Enterprise Edition account. The table has a DATA_RETENTION_TIME_IN_DAYS setting of 1. The engineer wants Time Travel to retain data for 30 days for this table. What will happen if the engineer executes ALTER TABLE STG_EVENTS SET DATA_RETENTION_TIME_IN_DAYS = 30?

Accepted Answer

The statement will fail with an error because transient tables support a maximum Time Travel retention of 1 day.. Transient tables are limited to a maximum Time Travel retention of 1 day. Attempting to set a higher value such as 30 days causes Snowflake to reject the ALTER TABLE statement with an error. To achieve 30 days of Time Travel, the table must be a permanent table in an account with Enterprise Edition or higher. The table type is the deciding factor.

Answer

The retention period will be set to 30 days, but only if the table has not been cloned previously.

Answer

The retention period will be set to 30 days but Fail-safe will still be disabled for the table.

Answer

The retention period will be set to 30 days, and Time Travel will retain data for 30 days.

Question 4

A data engineer is implementing a data classification process using Snowflake's Data Classification feature. The engineer wants to automatically classify columns containing sensitive data and then use the results to apply masking policies. After running the classification, the engineer notices that some columns that should be classified as 'EMAIL' are not being tagged. The engineer has verified that the data contains valid email addresses. What is the most likely reason for the missing classification?

Accepted Answer

The Data Classification feature only samples a subset of rows, and the sample did not include enough email addresses to meet the threshold.. Data Classification uses sampling to analyze column data. If the sample does not contain a sufficient number of email addresses, the column may not be classified as EMAIL. This is a known limitation. The other options are incorrect because classification does not depend on column names, can handle NULLs, and is not restricted to a specific schema.

Answer

The Data Classification feature requires that the column name contains the word 'EMAIL' to classify it as EMAIL.

Answer

The Data Classification feature cannot classify columns that contain NULL values.

Answer

The Data Classification feature is only available for columns in the PUBLIC schema.

Question 5

A data engineer is auditing storage consumption in a Snowflake account. A large permanent table named SALES_FACT has DATA_RETENTION_TIME_IN_DAYS set to 60. Over the past month, ETL jobs have performed several full-table UPDATE operations that rewrote most micro-partitions. The engineer notices that the table's Time Travel storage has grown significantly even though the visible row count is unchanged. What is the most likely cause of the increased Time Travel storage?

Accepted Answer

Time Travel storage grows because each UPDATE creates new micro-partitions while prior versions are retained for the 60-day retention window.. Time Travel storage reflects historical micro-partition versions retained for the configured window. Each UPDATE that rewrites micro-partitions creates new versions, and the prior versions are stored for 60 days. Repeated full-table updates therefore accumulate substantial historical data, increasing Time Travel storage even when the visible row count is stable.

Answer

Time Travel storage grows because the 60-day retention forces Snowflake to store a full compressed copy of the table in Fail-safe.

Answer

Time Travel storage grows because Snowflake copies the entire table into a separate Time Travel schema on each UPDATE.

Answer

Time Travel storage grows because the account has exceeded its storage quota, causing Snowflake to duplicate micro-partitions as a redundancy measure.

Question 6

A data engineer is designing a transformation that requires unpivoting a wide table with columns `q1_sales`, `q2_sales`, `q3_sales`, and `q4_sales` into a long format with columns `quarter` and `sales`. The table has millions of rows. Which Snowflake construct should be used to achieve this efficiently?

Accepted Answer

Use the `UNPIVOT` clause in a SELECT statement.. The UNPIVOT clause is specifically designed to transform columns into rows. It is a native Snowflake feature that is optimized for performance on large tables. Using UNION ALL or LATERAL FLATTEN can achieve similar results but with more complexity and potentially less efficiency. PIVOT does the reverse and is not appropriate here.

Answer

Use the `PIVOT` clause in a SELECT statement.

Answer

Use a `LATERAL FLATTEN` on an array constructed from the quarter columns.

Answer

Use a series of `UNION ALL` SELECT statements, one for each quarter column.

Question 7

A data engineer needs to continuously ingest JSON event files landing in an Amazon S3 bucket. The ingestion must be automatic, near real-time, and must reuse the same transformation logic already validated in a COPY INTO statement. The team wants the least administrative overhead. Which Snowflake feature should they configure?

Accepted Answer

A Snowpipe configured with COPY INTO from the S3 stage. Snowpipe is the Snowflake service built for continuous, event-driven loading. It runs COPY INTO statements automatically when new files arrive and can embed transformation logic, so the validated COPY logic is reused. Because it uses serverless compute triggered by cloud event notifications, it provides near real-time ingestion with very little operational management, fitting the stated constraints.

Answer

A scheduled task that runs COPY INTO every five minutes against the S3 stage

Answer

An external table defined over the S3 stage with automatic refresh enabled

Answer

A materialized view created over an external table pointing at the S3 stage

Question 8

A data engineer is using Snowpark for Python to transform data. The engineer needs to perform a join between two DataFrames and then apply a filter based on a column from the joined result. Which TWO methods are appropriate for this transformation? (Choose two.)

Accepted Answer

Use `DataFrame.join()` to combine the DataFrames, then `DataFrame.where()` to apply the condition.. In Snowpark, join() combines two DataFrames, and filter() or its alias where() applies a row-wise condition. These methods build a lazy query plan that executes on Snowflake. Other methods like merge(), union(), or select() do not perform a join followed by a row filter, so they are not appropriate for this transformation.

Answer

Use `DataFrame.union()` to combine the DataFrames, then `DataFrame.filter()` to apply the condition.

Answer

Use `DataFrame.join()` to combine the DataFrames, then `DataFrame.select()` to apply the condition.

Answer

Use `DataFrame.merge()` to combine the DataFrames, then `DataFrame.filter()` to apply the condition.

Question 9

A data engineer is building a transformation pipeline that must parse semi-structured log data stored in a VARIANT column named log_data. The JSON structure contains a nested array under the key 'events', and each element in the array has a 'timestamp' field. The engineer needs to produce one row per event with the timestamp extracted as a TIMESTAMP_NTZ value. Which SQL construct should be used to achieve this transformation efficiently?

Accepted Answer

Use the FLATTEN function with the LATERAL keyword to explode the 'events' array, and access the 'timestamp' field using the colon notation, casting it to TIMESTAMP_NTZ.. The FLATTEN function with LATERAL is the standard Snowflake construct for exploding nested arrays in VARIANT columns. It produces one row per array element, and the colon notation allows direct access to nested fields. Casting the extracted value to TIMESTAMP_NTZ ensures the correct data type for downstream processing. This method is efficient and leverages Snowflake's native semi-structured data handling.

Answer

Use the GET_PATH function to directly extract the 'timestamp' field from the array without exploding it, relying on implicit casting to TIMESTAMP_NTZ.

Answer

Use the OBJECT_CONSTRUCT function to rebuild the JSON, then use a JavaScript UDF to iterate over the array and return each timestamp.

Answer

Use the PARSE_JSON function to convert the VARIANT to a string, then use SPLIT_TO_TABLE to separate the array elements, and finally extract the timestamp with regular expressions.

Question 10

A data engineer needs to create a development copy of a 5 TB production table named FACT_SALES for query testing. The team wants the copy to share the same underlying micro-partitions as the source so no additional storage is consumed at creation time. Which command should the engineer use?

Accepted Answer

CREATE TABLE dev_db.public.fact_sales_clone CLONE prod_db.public.fact_sales;. Zero-copy cloning with CREATE TABLE ... CLONE creates a new table that references the same micro-partitions as the source, so no storage is duplicated at creation. CTAS would physically copy the data, CREATE TABLE ... LIKE would only copy the schema, and ALTER TABLE ... SET CLONE is not valid syntax. Only the CLONE form meets the no-extra-storage requirement.

Answer

CREATE TABLE dev_db.public.fact_sales_clone AS SELECT * FROM prod_db.public.fact_sales;

Answer

ALTER TABLE prod_db.public.fact_sales SET CLONE dev_db.public.fact_sales_clone;

Answer

CREATE TABLE dev_db.public.fact_sales_clone LIKE prod_db.public.fact_sales;