DEA-C02 Practice Test 10 — 10 Questions

Question 1

A data engineer is using Snowflake's Time Travel feature to recover a table that was dropped 2 days ago. The table was a permanent table with DATA_RETENTION_TIME_IN_DAYS set to 1. The engineer attempts to use UNDROP TABLE but receives an error. What is the most likely reason for the failure?

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

Accepted Answer

The table's Time Travel retention period has expired, and the table is now in Fail-safe.. A permanent table with 1-day Time Travel retention is recoverable via UNDROP only within that 1-day window. After 2 days, the table has moved to Fail-safe, which does not support UNDROP. Therefore, the UNDROP fails because the Time Travel period has expired. Other options like transient table type, privileges, or name reuse are not applicable in this scenario.

Answer

The user lacks the necessary privileges to perform UNDROP on the table.

Answer

The table name was reused by another table, so UNDROP cannot restore the original.

Answer

The table was created as a transient table, so UNDROP is not supported.

Question 2

A data engineer is setting up a Snowpipe to automatically ingest data from an external stage (Google Cloud Storage) into a Snowflake table. The engineer wants to minimize latency and ensure that files are loaded as soon as they are available. Which Snowpipe configuration should be used?

Accepted Answer

Create a pipe with AUTO_INGEST = TRUE and configure a cloud notification integration for Google Cloud Pub/Sub.. For automatic, low-latency ingestion from Google Cloud Storage, Snowpipe must be configured with AUTO_INGEST = TRUE and a notification integration that uses Google Cloud Pub/Sub. This allows Snowflake to receive event notifications when new files arrive, triggering immediate ingestion. Storage integrations alone do not provide event notifications, and polling introduces delay and unnecessary compute.

Answer

Create a pipe with AUTO_INGEST = TRUE and rely on Snowflake's internal polling mechanism to detect new files.

Answer

Create a pipe with AUTO_INGEST = TRUE and configure the stage to use a storage integration with Google Cloud Storage.

Answer

Create a pipe with AUTO_INGEST = FALSE and schedule it to run every minute using a task.

Question 3

A data engineer is implementing a data governance strategy and needs to ensure that all tables containing sensitive data are automatically identified and tagged. The engineer wants to use Snowflake's native classification capabilities and then apply masking policies based on those tags. Which sequence of steps should the engineer follow?

Accepted Answer

Run Data Classification, review results, then create and attach masking policies to tagged columns.. Data Classification is the native feature that automatically scans and tags sensitive columns. Once tagged, the engineer can review the tags and then create masking policies attached to those columns. This ensures that policies are applied to the correct columns without manual discovery.

Answer

Manually apply tags, then create masking policies that reference those tags.

Answer

Create masking policies first, then run Data Classification to tag columns.

Answer

Use Access History to identify sensitive columns, then manually tag them.

Question 4

A data engineer is using Snowpipe to load data from an external stage. The pipe is configured with AUTO_INGEST = TRUE. Which two statements are true regarding the behavior of Snowpipe in this configuration? (Choose two.)

Accepted Answer

Snowpipe relies on cloud storage event notifications to trigger the load process.. Snowpipe with AUTO_INGEST uses cloud storage event notifications to trigger loads and maintains a load history to avoid duplicates. It does not poll the stage, does not auto-create integrations, and is not compatible with internal stages.

Answer

Snowpipe periodically polls the external stage for new files based on a defined schedule.

Answer

AUTO_INGEST = TRUE requires the stage to be an internal stage.

Answer

Snowpipe automatically creates a notification integration if one does not exist.

Question 5

A data engineer is optimizing a Snowflake environment for a data warehouse that experiences high concurrency during business hours. The engineer observes that many queries are small and frequent, and the warehouse is often queued. The engineer wants to reduce queueing and improve throughput without increasing cost significantly. Which two actions should the engineer take? (Choose two.)

Accepted Answer

Implement a separate warehouse for small, frequent queries and another for large, long-running queries.. High concurrency with many small queries is best addressed by increasing parallelism through multi-cluster warehouses and by isolating workloads to prevent resource contention. Multi-cluster warehouses dynamically add clusters to handle queued queries, while workload separation ensures that small queries are not blocked by large ones. Scaling up a warehouse or tweaking timeout parameters does not increase concurrency, and result caching is ineffective for unique queries. Therefore, the two correct actions are enabling multi-cluster and separating workloads.

Answer

Increase the warehouse size from Medium to Large.

Answer

Set the STATEMENT_QUEUED_TIMEOUT_IN_SECONDS parameter to a low value.

Answer

Enable the 'USE_CACHED_RESULT' parameter and increase the result cache size.

Question 6

A data engineer is transforming a large table with a VARIANT column that contains nested arrays. The goal is to produce one row per element in the array, preserving all other columns. The engineer uses the FLATTEN function with the LATERAL keyword. Which behavior should the engineer expect when the VARIANT column contains an empty array?

Accepted Answer

The row is omitted from the result set because FLATTEN produces no rows for an empty array.. FLATTEN with LATERAL produces one row per element in the array. When the array is empty, there are no elements, so no rows are generated, and the outer row is excluded from the result. This is consistent with inner join semantics. To retain such rows, an outer join with LATERAL FLATTEN is needed. The correct expectation is that the row is omitted.

Answer

The row is retained with NULL values for the flattened columns.

Answer

The query fails with an error because FLATTEN cannot process empty arrays.

Answer

The row is retained with an empty array in the flattened column.

Question 7

A data engineer is working with a table that contains a column of type VARCHAR storing JSON strings. The engineer needs to transform this column into a VARIANT type to leverage Snowflake's semi-structured data functions. Which function should be used to convert the VARCHAR column to VARIANT?

Accepted Answer

PARSE_JSON. PARSE_JSON is the dedicated function for converting a JSON-formatted string into a VARIANT. It parses the string and creates a structured object that can be queried using Snowflake's semi-structured data functions. This transformation is essential for enabling access to nested JSON elements and is the recommended approach for converting VARCHAR JSON to VARIANT.

Answer

CAST

Answer

TO_VARIANT

Answer

TRY_PARSE_JSON

Question 8

A data engineer is transforming semi-structured event logs stored in a VARIANT column named event_data. The column contains an array of objects under the key 'items', where each object has a 'product_id' and a 'quantity'. The engineer needs to produce one row per product per event, with the event's timestamp and user ID repeated. Which SQL construct should be used to achieve this transformation?

Accepted Answer

Use the LATERAL FLATTEN function on event_data:items to expand the array into separate rows.. LATERAL FLATTEN is the idiomatic Snowflake construct for expanding semi-structured arrays into rows. When applied to event_data:items, it produces one row per array element, allowing direct access to nested attributes like product_id and quantity. Other options either aggregate, parse, or manually iterate, none of which efficiently achieve the required row-level expansion while preserving parent context.

Answer

Employ a recursive common table expression (CTE) to iterate over the array elements and output one row per element.

Answer

Use the PARSE_JSON function to convert event_data:items into a relational table with columns for product_id and quantity.

Answer

Apply the ARRAY_AGG function to event_data:items to concatenate the array elements into a single string.

Question 9

A data engineer notices that a recurring daily query that joins a large fact table with a date dimension is taking longer than expected. The query filters on a date range and joins on a date key. The fact table is clustered by date_key, and the date dimension is small. The query profile shows a Cartesian join warning. Which action should the engineer take to resolve the Cartesian join and improve performance?

Accepted Answer

Ensure the join condition uses the correct columns and is not accidentally omitted.. A Cartesian join warning in the query profile signals that the join is producing a Cartesian product, usually due to a missing or incorrect join condition. The most direct fix is to ensure the join predicate correctly matches the fact table's date_key to the date dimension's key. This eliminates the Cartesian product, reducing the result set and improving performance. Other options like filtering or scaling up do not address the root cause and may lead to incorrect results or inefficiency.

Answer

Add a WHERE clause to filter the date dimension to the same range as the fact table.

Answer

Add a clustering key on the date dimension's date key.

Answer

Increase the warehouse size to handle the larger result set.

Question 10

A data engineer is investigating a slow query that scans a large table. The Query Profile shows that the table scan is reading a very high number of micro-partitions compared to the total number of partitions in the table. The query filters on a column that is not the clustering key. What is the most likely explanation for the high number of partitions read?

Accepted Answer

The table is not clustered on the filter column, so pruning is ineffective.. Pruning efficiency depends on how well data is clustered on the filter column. Without clustering, micro-partitions contain overlapping values, so the optimizer cannot skip many partitions. This results in a high number of partitions read. Warehouse size, indexing, and column count do not affect the number of partitions scanned in Snowflake.

Answer

The query is using a full table scan because the filter column is not indexed.

Answer

The table has too many columns, causing the scan to read all partitions.

Answer

The warehouse is too small, causing the scan to read partitions multiple times.