DEA-C02 Practice Test 12 — 15 Questions

Question 1

A data engineer is building a transformation pipeline that processes semi-structured event logs stored in a VARIANT column named `event_data`. The logs contain a nested array under the key `items`. The engineer needs to produce one output row per element in the array, preserving all other columns from the source table. Which Snowflake construct should be used to achieve this transformation?

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

Accepted Answer

LATERAL FLATTEN(input => event_data:items). To expand a nested array into multiple rows while keeping the original columns, a lateral join with FLATTEN is required. LATERAL FLATTEN allows the FLATTEN function to access the VARIANT column from the preceding table and returns one row per array element, which is exactly the needed behavior. The other functions either aggregate, construct objects, or parse strings, none of which achieve row expansion.

Answer

ARRAY_AGG(event_data:items)

Answer

OBJECT_CONSTRUCT('items', event_data:items)

Answer

PARSE_JSON(event_data:items)

Question 2

A data engineer is transforming a large fact table using a complex SQL query that includes multiple window functions and joins. The query is executed frequently and must return results with minimal latency. The engineer notices that the query spends significant time on repartitioning data for window functions. Which Snowflake feature should be used to improve performance by pre-organizing the data to avoid repartitioning?

Accepted Answer

Define a clustering key on the columns used in the PARTITION BY clause of the window functions.. Clustering keys physically order data by the specified columns, which can align with the PARTITION BY clause of window functions. This reduces the need for Snowflake to repartition data at query runtime, leading to faster execution. For large fact tables with frequent window function queries, clustering is the recommended approach to optimize performance by minimizing data movement.

Answer

Use the RESULT_SCAN function to cache the query results and reuse them.

Answer

Apply search optimization on the columns used in the window functions.

Answer

Create a materialized view that pre-computes the window functions.

Question 3

A data engineer runs a batch job on a transient table named STG_EVENTS that is loaded from an external stage. The job truncates and reloads the table every night. After a faulty deployment, the engineer needs to restore the table contents from 30 hours ago. The engineer attempts Time Travel but the query returns no historical data. What is the most likely cause?

Accepted Answer

The table is transient, which has a maximum Time Travel retention of 1 day, so data from 30 hours ago may be outside the retention window.. Transient tables support Time Travel but with a maximum retention of 1 day. Because the requested restore point is 30 hours old, it falls outside that window, so the historical data is unavailable. Permanent tables can be configured with longer DATA_RETENTION_TIME_IN_DAYS values, which would be required for a 30-hour recovery need.

Answer

Time Travel queries only work for permanent tables, and transient tables require Fail-safe recovery instead.

Answer

The TRUNCATE operation removes all historical versions immediately, making Time Travel impossible.

Answer

The table is loaded from an external stage, so Time Travel is not supported for tables that use external stages.

Question 4

A data engineer creates a zero-copy clone of a 5 TB permanent table to provide a development team with a copy of production data. Immediately after the clone is created, how is storage billed for the clone?

Accepted Answer

The clone consumes no additional storage until either the source or the clone is modified, at which point only the changed micro-partitions add storage.. Zero-copy cloning shares micro-partitions between the source and the clone, so no storage is consumed at creation. Storage is billed only when changes cause new micro-partitions to be written on either side. This is why cloning large tables is fast and initially free of additional storage cost, which is the defining benefit of the feature.

Answer

The clone consumes storage equal to the source table's Fail-safe footprint at creation time.

Answer

The clone immediately consumes 5 TB of additional storage because a full physical copy is made.

Answer

The clone consumes 5 TB only if the source table has Time Travel enabled.

Question 5

A data engineer needs to unload a large table from Snowflake to an external stage. The unloading process must produce a single compressed file. Which COPY INTO <location> option should be used to ensure the output is a single file?

Accepted Answer

SINGLE = TRUE. To unload data into a single file, the COPY INTO <location> command must include the SINGLE = TRUE option. This forces Snowflake to write all data to one file, which can be necessary when the downstream system cannot handle multiple files. However, using SINGLE = TRUE may reduce performance for large datasets because the unload operation is not parallelized across multiple files.

Answer

PARTITION_BY = <column>

Answer

DETAILED_OUTPUT = TRUE

Answer

MAX_FILE_SIZE = <size>

Question 6

A data engineer manages a transient table named STG_EVENTS with DATA_RETENTION_TIME_IN_DAYS set to 0 in a Snowflake Enterprise Edition account. A batch job accidentally truncates the table at 02:00. The engineer attempts to recover the lost rows using Time Travel queries against the table's historical versions but finds no historical data available. Which statement explains why the historical data is unavailable?

Accepted Answer

The table's Time Travel retention was set to 0, so no historical versions were retained and the truncated data is unrecoverable via Time Travel.. Transient tables support Time Travel, but their retention period can be set to zero, and when it is, Snowflake stores no historical versions. Because the engineer configured zero-day retention on STG_EVENTS, no prior state exists to query after the truncation, making Time Travel recovery impossible. The retention setting, not the table type or the truncation operation, is the controlling factor.

Answer

Historical data is only accessible through Fail-safe, which requires a support ticket and is not queryable directly.

Answer

Transient tables do not support Time Travel; only permanent tables can have historical versions retained.

Answer

The TRUNCATE operation bypasses Time Travel and immediately purges all historical micro-partitions regardless of retention settings.

Question 7

A Snowflake account uses Enterprise Edition. A permanent table FACT_ORDERS with DATA_RETENTION_TIME_IN_DAYS set to 14 is dropped at 10:00 on Monday. On Wednesday at 10:00, the engineer runs UNDROP TABLE FACT_ORDERS. Immediately after the UNDROP, what is the DATA_RETENTION_TIME_IN_DAYS value for the restored table?

Accepted Answer

14, because UNDROP restores the table with its original retention setting intact.. UNDROP restores a dropped table with the same properties it had at drop time, including its DATA_RETENTION_TIME_IN_DAYS value. Since FACT_ORDERS had 14 days configured, the restored table again has 14 days of retention. The original drop timestamp governs how much of the Time Travel window remains, not a reset to a default value.

Answer

0, because a dropped table's retention is reset to zero during the drop operation.

Answer

1, because the default retention for a restored table reverts to the account-level default of one day.

Answer

90, because UNDROP always promotes the restored table to the maximum retention allowed by the account edition.

Question 8

A data engineer is optimizing a query that joins a very large fact table to a small dimension table. The Query Profile shows the small table being redistributed across all nodes before the join. Which action is most likely to improve performance?

Accepted Answer

Broadcast the small dimension table so it is replicated to all nodes and the large fact table is not redistributed.. The profile shows the small relation being shuffled, which is the expensive but avoidable side of the join. Broadcasting the small table replicates it to every node so the large fact table stays in place, eliminating the shuffle of the big input. This is the standard remedy when a join's redistribution targets the wrong relation.

Answer

Increase the warehouse size so the redistribution of the small table completes faster across more nodes.

Answer

Rewrite the query to use a correlated subquery instead of a join so the small table is not redistributed.

Answer

Add a clustering key to the small dimension table so its rows are stored contiguously.

Question 9

A data engineer is designing a transformation pipeline that uses Snowpark to process data. The pipeline must perform complex data cleaning and feature engineering. Which TWO capabilities of Snowpark are specifically designed to support these transformations? (Choose two.)

Accepted Answer

The ability to execute user-defined functions (UDFs) written in Python, Java, or Scala within Snowflake.. Snowpark's DataFrame API allows developers to write transformations in Python, Java, or Scala, providing a familiar and expressive way to perform complex data cleaning and feature engineering. Additionally, Snowpark supports UDFs in these languages, enabling custom logic to be executed within Snowflake. These two capabilities directly empower the development of sophisticated transformation pipelines.

Answer

The ability to automatically materialize transformation results into a new table without explicit DDL.

Answer

The ability to execute transformations on data without moving it out of Snowflake's storage layer.

Answer

The ability to use the Snowpark ML library for building and deploying machine learning models directly in Snowflake.

Question 10

A data engineer is managing a Snowflake account with multiple databases. The engineer wants to ensure that critical data can be recovered in the event of accidental deletion or modification. The engineer is considering using Time Travel and Fail-safe. Which TWO statements accurately describe the capabilities and limitations of Fail-safe? (Choose two.)

Accepted Answer

Fail-safe provides a 7-day period after Time Travel expires during which Snowflake Support can recover data.. Fail-safe is a mandatory 7-day period for permanent tables that begins after Time Travel retention expires. It allows only Snowflake Support to recover data, and it cannot be disabled or queried by users. Transient and temporary tables do not have Fail-safe. Storage costs are incurred for Fail-safe data, which is no longer accessible via Time Travel. These two statements accurately describe Fail-safe's capabilities and limitations.

Answer

Users can query data in Fail-safe using the AT or BEFORE clauses, similar to Time Travel.

Answer

Fail-safe applies to transient tables, providing an additional layer of protection after Time Travel.

Answer

Fail-safe can be disabled at the account level to reduce storage costs.

Question 11

A data engineer runs a transformation that loads a 50 GB staged file into a target table using a COPY INTO statement with a single large file. The warehouse is a MEDIUM multi-cluster warehouse. The load takes far longer than expected because one node processes the entire file. Which change most directly improves load throughput?

Accepted Answer

Split the staged file into multiple smaller files of roughly 100-250 MB compressed and load them in the same COPY INTO statement.. COPY INTO distributes work per file, so throughput scales with the number of files rather than the size of the warehouse. Breaking a single large file into many moderately sized compressed files enables parallel processing across nodes, which is the direct remedy for a load dominated by one file.

Answer

Set the ON_ERROR option to CONTINUE and re-run the COPY INTO statement.

Answer

Enable multi-cluster scaling by setting the maximum cluster count to four on the warehouse.

Answer

Increase the warehouse size from MEDIUM to 4X-LARGE before running the same COPY INTO statement.

Question 12

A data engineer is using a Snowpipe to load data from an external stage. The pipe has been running successfully, but the engineer notices that some files are being loaded multiple times, resulting in duplicate records. Which action should be taken to prevent future duplicate loads?

Accepted Answer

Ensure that the pipe's load history is properly maintained and that the cloud notification integration is configured to avoid duplicate event messages.. Duplicate loads in Snowpipe are typically caused by duplicate event notifications or by clearing the load history. Snowflake maintains a 14-day load history for each pipe, which prevents re-loading the same file if it hasn't changed. Ensuring the notification integration is configured correctly to avoid duplicate events, and not manually clearing the load history, are key to preventing duplicates. Monitoring with COPY_HISTORY can help detect issues.

Answer

Set the pipe to use a file format that includes a unique key column and rely on Snowflake's automatic deduplication.

Answer

Recreate the pipe with a new name to reset the load history and start fresh.

Answer

Enable the PURGE = TRUE option in the pipe definition.

Question 13

A data engineer has a permanent table named ORDERS with DATA_RETENTION_TIME_IN_DAYS set to 5. The table is dropped accidentally. The engineer executes UNDROP TABLE ORDERS 2 days later, successfully restoring the table. What is the DATA_RETENTION_TIME_IN_DAYS setting for the restored table?

Accepted Answer

The retention period remains 5 days, as it was before the drop.. When a dropped table is restored using UNDROP, it retains its original DATA_RETENTION_TIME_IN_DAYS setting. The drop and undrop operations do not modify the retention period. Therefore, the table will continue to have a 5-day Time Travel retention, allowing historical data to be accessed within that period.

Answer

The retention period is set to 0 days because the table was dropped and restored.

Answer

The retention period is extended to 7 days to account for the time the table was dropped.

Answer

The retention period is reset to the default for the account, which is 1 day.

Question 14

A data engineer runs a long-running aggregation query and inspects the Query Profile. The profile shows a single operator with an output row count roughly 400 times larger than its input row count, and the downstream operator is bottlenecked. Which action should the engineer take to resolve this?

Accepted Answer

Reduce the number of rows produced by the exploding operator by moving the aggregation to a separate earlier stage or by rewriting the join/grouping logic so cardinality grows later in the plan.. The decisive clue is an operator emitting far more rows than it receives, which is the signature of a cardinality explosion rather than a compute or pruning problem. Adding warehouse capacity, toggling result caching, or enabling search optimization all leave that multiplication intact. Only restructuring the plan so the aggregation occurs before or alongside the fan-out keeps downstream operators from processing duplicated rows.

Answer

Enable the Search Optimization Service on the base table so the exploding operator can skip micro-partitions and emit fewer rows.

Answer

Disable the query result cache at the account level so the exploding operator re-executes and produces a smaller result set.

Answer

Increase the size of the virtual warehouse to add more compute nodes so the exploding operator can process more rows per second.

Question 15

A company clones a 5 TB permanent table named RAW_CLICKS into a development schema using CREATE TABLE DEV.RAW_CLICKS CLONE PROD.RAW_CLICKS. Immediately after the clone, the development team loads 500 GB of new data into DEV.RAW_CLICKS and also updates 100 GB of existing rows. Which statement correctly describes the storage charges that result?

Accepted Answer

Storage charges cover only the newly written and modified micro-partitions in DEV.RAW_CLICKS, while the shared unchanged partitions are billed once.. Zero-copy cloning shares micro-partitions between source and clone, and Snowflake bills each micro-partition once even when multiple tables reference it. Only partitions that the clone modifies or adds become unique and billable to the clone. The 500 GB of new loads and 100 GB of updated rows produce the incremental charge, while the unchanged shared partitions are not double-billed.

Answer

Storage charges for the clone are zero until the source table is dropped, because shared partitions are billed to the source.

Answer

The clone is billed as a full 5.6 TB copy plus Time Travel, because cloning always materializes a complete physical copy for isolation.

Answer

The clone immediately doubles storage to 10 TB because cloning copies all micro-partitions.