DEA-C02 Practice Test 8 — 10 Questions

Question 1

A data engineer notices that a query against a large table returns results quickly when filtering on one column but scans the entire table when filtering on another column. Both columns are used in equality predicates. What is the most likely explanation?

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

Accepted Answer

The fast column is a clustering key, so the optimizer can prune micro-partitions for that predicate.. Micro-partition pruning depends on the physical ordering of data. A clustering key aligns the data so that equality filters on that column can skip irrelevant micro-partitions, while an unclustered column forces a full scan. This accounts for the difference in the observed query times.

Answer

The fast column has a lower cardinality, so the optimizer can use metadata to skip partitions.

Answer

The fast column is part of the table's primary key, which automatically prunes partitions.

Answer

The fast column is defined with a NOT NULL constraint, which enables partition skipping.

Question 2

A data engineer is transforming a table with a column `tags` stored as a VARIANT array of strings. The goal is to produce one row per tag for downstream aggregation. Which function should be used to expand the array into rows?

Accepted Answer

FLATTEN(input => tags). FLATTEN is the Snowflake table function that unnests semi-structured arrays and objects into relational rows. When applied to a VARIANT array, it returns one row per element, making it the correct choice for expanding `tags` into individual rows. The other functions either concatenate, parse, or extract object keys, none of which achieve the required row-level expansion.

Answer

OBJECT_KEYS(tags)

Answer

ARRAY_TO_STRING(tags, ',')

Answer

PARSE_JSON(tags)

Question 3

A data engineer needs to ensure that all queries against a table containing sensitive data are logged for compliance purposes. Which Snowflake feature should the engineer use to capture the query text and the user who executed it?

Accepted Answer

Query History in the ACCOUNT_USAGE schema.. Query History in ACCOUNT_USAGE is the correct feature to log query text and user information for compliance. It provides a complete record of all queries executed in the account. Access History is for column-level access, Login History is for authentication events, and Warehouse Metering History is for cost tracking. Only Query History captures the necessary details.

Answer

Login History in the ACCOUNT_USAGE schema.

Answer

Access History in the ACCOUNT_USAGE schema.

Answer

Warehouse Metering History in the ACCOUNT_USAGE schema.

Question 4

A data engineer must validate a COPY INTO load from an external stage before promoting it to production. The team wants to confirm which files were loaded, how many rows each contained, and which rows were rejected, without leaving partial or duplicated data in the target table. (Choose two.)

Accepted Answer

Run VALIDATION_MODE = RETURN_ERRORS in a COPY INTO statement. Pre-production validation combines a dry run that surfaces rejected rows without committing data and a post-attempt audit that reports per-file outcomes. VALIDATION_MODE = RETURN_ERRORS provides the dry run, and the COPY_HISTORY table function provides the file-level audit. Options that load partial data, force reloads, or observe changes after insertion either pollute the target or fail to expose the needed diagnostics.

Answer

Set ON_ERROR = CONTINUE and inspect the target table row counts

Answer

Use FORCE = TRUE to reload all files and compare checksums

Answer

Create a stream on the target table and read the change records

Question 5

A data engineer maintains a table that receives continuous small INSERT statements throughout the day. Query performance on this table has degraded even though total data volume is modest, and the Query Profile shows many very small micro-partitions being scanned. Which action best addresses the underlying cause?

Accepted Answer

Batch the incoming rows and load them less frequently, or use a staging table and periodically merge into the target.. The profile evidence of many tiny micro-partitions points to ingestion pattern rather than query design or compute. Each small insert creates its own micro-partition, so the table becomes a patchwork of fragments that the optimizer must enumerate and scan. Batching or staging-then-merging reduces the number of partitions created, which lowers metadata and scan overhead at the source.

Answer

Schedule periodic reclustering of the table using a clustering key on the most selective filter column.

Answer

Increase the warehouse size so the larger compute pool can scan the many small micro-partitions in parallel.

Answer

Convert the table to a transient table so Snowflake stops maintaining the metadata for the small micro-partitions.

Question 6

A data engineer is designing a recovery strategy for a critical permanent table FACT_SALES in an Enterprise Edition account. The team wants the ability to restore the table to any point within the last 60 days and also wants protection against a catastrophic failure that corrupts the data. Which statement correctly describes the available recovery windows?

Accepted Answer

Setting DATA_RETENTION_TIME_IN_DAYS to 60 gives 60 days of Time Travel plus 7 days of Fail-safe, for a total of 67 days of recoverability.. Permanent tables in Enterprise Edition can be configured up to 90 days of Time Travel, and Fail-safe adds a fixed seven-day window after Time Travel expires. A 60-day retention therefore yields 60 days of self-service recovery plus 7 days of Support-assisted recovery. Fail-safe does not overlap Time Travel, transient tables are capped at one day, and 30 is the default rather than the maximum.

Answer

Setting DATA_RETENTION_TIME_IN_DAYS to 60 gives 60 days of Time Travel, and Fail-safe adds no extra window because it overlaps Time Travel.

Answer

The maximum Time Travel for a permanent table is 30 days, so 60 days is not achievable and the setting will fail.

Answer

Setting DATA_RETENTION_TIME_IN_DAYS to 60 requires converting the table to a transient table to extend beyond 1 day.

Question 7

A data engineer is unloading a large fact table to an external stage and wants to minimize the total volume of data transferred while keeping files readable by downstream tools. The table contains many repeated values in several columns. Which approach best reduces the unloaded data size?

Accepted Answer

Unload with FILE_FORMAT = (TYPE = PARQUET COMPRESSION = SNAPPY).. Parquet with SNAPPY compression gives the best size reduction because its columnar layout and per-column encodings efficiently handle repeated values, and SNAPPY compresses the encoded blocks. CSV, JSON, and file-count tuning do not exploit column repetition, so they leave far more data to transfer for the same table.

Answer

Unload with FILE_FORMAT = (TYPE = JSON COMPRESSION = GZIP).

Answer

Unload with FILE_FORMAT = (TYPE = CSV COMPRESSION = GZIP).

Answer

Increase MAX_FILE_SIZE so fewer, larger files are produced.

Question 8

A data engineer notices that a query performing a large GROUP BY on a high-cardinality column is slow. The Query Profile shows that the aggregation step is spilling to local disk. The engineer wants to reduce local spilling without changing the query logic. Which action is most appropriate?

Accepted Answer

Increase the warehouse size to provide more memory for the aggregation.. Local disk spilling in a high-cardinality GROUP BY means the aggregation's hash table exceeds available memory on the nodes. Increasing the warehouse size adds memory and compute nodes, allowing the hash table to remain in memory and eliminating the spill. This is the most direct fix when the query logic cannot be changed and the profile clearly points to the aggregation step as the spill location.

Answer

Add an ORDER BY clause on the grouping column to sort data before aggregation.

Answer

Reduce the warehouse size to decrease contention among nodes.

Answer

Set the MAX_CONCURRENCY_LEVEL parameter to 1 to give the query more resources.

Question 9

A data engineer is configuring a new permanent table in a Snowflake Standard Edition account. The engineer needs to ensure that the table can be recovered if it is accidentally dropped, but wants to minimize storage costs. The table will be updated frequently. Which DATA_RETENTION_TIME_IN_DAYS setting should the engineer choose to meet these requirements?

Accepted Answer

1. In Snowflake Standard Edition, permanent tables can have a maximum Time Travel retention of 1 day. To enable recovery of a dropped table while minimizing storage costs, the engineer should set DATA_RETENTION_TIME_IN_DAYS to 1. This provides a 1-day window for recovery and keeps Time Travel storage overhead to a minimum.

Answer

0

Answer

7

Answer

90

Question 10

A data engineer notices that a daily aggregation query scans 4 TB of a 5 TB table even though it only needs the last seven days of data. The table has a DATE column but no clustering key, and the query filters with DATE >= CURRENT_DATE - 7. Query Profile shows almost no partition pruning. What is the most effective change to reduce bytes scanned?

Accepted Answer

Add a clustering key on the DATE column and let Automatic Clustering maintain it.. Partition pruning depends on how well the filter column correlates with micro-partition boundaries. A DATE clustering key groups similar dates together, allowing the optimizer to skip most micro-partitions for a seven-day filter. Automatic Clustering preserves that layout over time. Predicate rewrites, materialized views, and search optimization do not change the physical clustering that governs pruning in this scenario.

Answer

Enable search optimization service on the DATE column to improve range filter performance.

Answer

Create a materialized view that selects only the last seven days and refresh it on a schedule.

Answer

Replace the filter with a BETWEEN clause using explicit literal dates instead of CURRENT_DATE arithmetic.