SnowPro Advanced: Data Engineer DEA-C02 Questions 226–229 | Page 4/4

226

MCQhard

A data engineer runs a COPY INTO statement that loads 120 files from an external S3 stage into a target table. The LOAD_UNCERTAIN_FILES option was not specified, and 42 files were already loaded by an earlier run that completed successfully. The engineer expects all 120 files to be reprocessed because the target table was truncated before this run. What will Snowflake actually do, and why?

A.It will skip the 42 previously loaded files and load only the remaining 78, because load metadata is retained independently of table data.

B.It will load all 120 files and produce duplicate rows because the table was emptied after the first load.

C.It will load all 120 files because TRUNCATE TABLE removes the table's load metadata along with the rows.

D.It will load all 120 files but raise an error for each previously loaded file, marking those rows as rejected.

AnswerA

Snowflake records which staged files have been loaded into each table in metadata that persists across DML such as TRUNCATE. Because LOAD_UNCERTAIN_FILES was omitted, files with a LOADED status for that table are filtered out before reading. Only the 78 files with no prior successful load are processed in this run.

Why this answer

COPY INTO maintains a per-table record of which staged files have already been loaded, and this record survives operations that change or remove table rows. With LOAD_UNCERTAIN_FILES left at its default, only files that have not been successfully loaded into the target are read, so a truncated table does not force reprocessing of files already recorded as loaded.

Exam trap

The trap here is assuming that clearing or truncating the target table also clears the per-table load metadata that COPY INTO uses to filter staged files.

Full explanation →

227

MCQmedium

A data engineer maintains a very large fact table that is loaded nightly with the previous day's orders. Analysts run reports that almost always filter on ORDER_DATE and also aggregate by CUSTOMER_ID. The engineer wants search optimization to accelerate point lookups on ORDER_ID and CUSTOMER_ID without adding clustering maintenance overhead. Which configuration best meets these requirements?

A.Define a clustering key on (ORDER_DATE, CUSTOMER_ID) and let automatic clustering maintain it.

B.Add a unique constraint on ORDER_ID and rely on the optimizer to use it for pruning.

C.Create a materialized view that pre-aggregates the table by ORDER_DATE and CUSTOMER_ID.

D.Enable the search optimization service on the CUSTOMER_ID and ORDER_ID columns.

AnswerD

Search optimization builds a persistent search access path that accelerates equality and IN predicates on the designated columns, which is exactly the point-lookup pattern described. It is maintained automatically as new micro-partitions are added by nightly loads, so no manual reclustering work is required. Clustering keys would instead target range pruning and require background maintenance, so enabling search optimization is the appropriate low-overhead choice here.

Why this answer

Search optimization is designed for selective point lookups and equality predicates on specific columns, and it is maintained automatically as new data lands, which matches the nightly-load pattern and the desire to avoid clustering maintenance. Clustering and materialized views address different access patterns, and unique constraints do not create physical access paths.

Exam trap

The trap here is assuming that a clustering key or unique constraint can substitute for search optimization when the real workload is single-value lookups.

Full explanation →

228

MCQhard

A data engineer runs a nightly transformation that joins a 12 TB fact table to a 300 GB dimension table. The fact table is clustered by DATE_KEY, and the dimension is small enough to fit in memory. Query Profile shows the join operator building a hash table on the 12 TB side and spilling to remote disk. The engineer wants to eliminate the remote spill without increasing warehouse size. Which action should the engineer take?

A.Increase the warehouse size by one step so the join operator receives more memory per node.

B.Add a clustering key on the 12 TB fact table using the dimension's primary key column.

C.Convert the 12 TB fact table to a materialized view joined with the dimension table and refresh it nightly.

D.Rewrite the join so the 300 GB dimension table is the build side and the 12 TB fact table is the probe side.

AnswerD

Hash joins build the hash table on the smaller input and stream the larger input as the probe side. With a 300 GB dimension as the build side and the 12 TB fact as the probe side, the hash table is far smaller and can be held in memory, avoiding remote disk spill. This directly addresses the spill without resizing the warehouse.

Why this answer

Hash join performance depends on which input is used to build the in-memory hash table. Building on the smaller dimension and probing with the large fact table keeps the hash table resident in memory, eliminating remote disk spilling. Clustering, resizing, or materializing the join do not correct the build-side selection, so they leave the root cause of the spill unaddressed.

Exam trap

The trap here is assuming that a remote spill is always solved by a larger warehouse or by clustering, when the join's build-side choice is what determines whether the hash table fits in memory.

Full explanation →

229

MCQmedium

A data engineer must transform semi-structured event data stored in a VARIANT column named 'event_payload'. The payload contains an array under the key 'tags', and each element of the array is an object with keys 'name' and 'score'. The engineer needs to produce one row per tag element, preserving the original event ID and extracting the 'name' and 'score' values. Which Snowflake construct should be used to achieve this transformation?

A.Use the FLATTEN function in the FROM clause with the INPUT argument set to event_payload:tags.

B.Use the SPLIT_TO_TABLE function with a delimiter of comma on the string representation of the tags array.

C.Use the PARSE_JSON function on event_payload:tags and then apply a lateral join with a VALUES clause.

D.Use the GET_PATH function to extract the array, then use a recursive CTE to iterate over its elements.

AnswerA

The FLATTEN table function is designed to explode semi-structured arrays into multiple rows. By specifying event_payload:tags as the INPUT, Snowflake returns one row per element in the array, allowing direct access to the 'name' and 'score' fields. This is the canonical way to transform nested arrays into a relational format while preserving the parent event ID.

Why this answer

Flattening a semi-structured array into rows is a core transformation in Snowflake. The FLATTEN table function directly expands array elements, producing one row per element and enabling easy extraction of nested fields. It preserves the parent row context, so the event ID remains available.

Other functions like PARSE_JSON or SPLIT_TO_TABLE do not achieve the same row expansion for VARIANT arrays.

Exam trap

The trap here is assuming that PARSE_JSON or SPLIT_TO_TABLE can flatten arrays, when only FLATTEN is designed for that purpose.

Full explanation →

SnowPro Advanced: Data Engineer (DEA-C02) — Questions 226–229