DEA-C02 Practice Test 5 — 25 Questions

Question 1

A data engineer is loading semi-structured JSON files from an external stage into a VARIANT column. Several files contain a field named 'event_time' formatted as an ISO-8601 string, but the ingestion team wants to automatically convert it to a TIMESTAMP_NTZ during the load without using a separate transformation step. Which COPY INTO feature should be used?

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

Accepted Answer

Use a transformation in the COPY INTO statement with the TO_TIMESTAMP function on the JSON field.. COPY INTO allows inline transformations in the SELECT list, which is the supported way to cast or convert semi-structured fields during load. Using TO_TIMESTAMP on the JSON field converts the ISO-8601 string to a TIMESTAMP_NTZ as it is ingested, avoiding a separate transformation step. File format options and masking policies do not change the type of a VARIANT field.

Answer

Define the column as TIMESTAMP_NTZ and rely on implicit casting during the COPY INTO operation.

Answer

Apply a masking policy on the VARIANT column to convert the string to a timestamp at query time.

Answer

Create a file format with the TIMESTAMP_FORMAT option set to 'YYYY-MM-DD"T"HH24:MI:SS'.

Question 2

A data engineer is designing a pipeline that loads semi-structured data from an external stage into a Snowflake table with a VARIANT column. The files contain nested arrays and keys that vary between records. Which TWO configuration choices should the engineer make to handle the variability and preserve the structure? (Choose two.)

Accepted Answer

Enable STRIP_OUTER_ARRAY so a top-level JSON array is loaded as individual rows.. Loading JSON into a VARIANT column preserves nested arrays and varying keys without a fixed schema, and STRIP_OUTER_ARRAY turns a top-level array into individual rows. Together they handle the semi-structured variability. CSV cannot represent nesting, a fixed schema cannot accommodate unknown keys, and column-name matching requires pre-defined columns.

Answer

Set the file format TYPE to CSV with a delimiter that matches the JSON structure.

Answer

Define a fixed relational schema for every possible key before loading.

Answer

Use MATCH_BY_COLUMN_NAME = CASE_SENSITIVE to map JSON keys to columns.

Question 3

A data engineer needs to retain a point-in-time copy of a large permanent table SALES_HISTORY for regulatory purposes. The table is 5 TB. The engineer runs CREATE TABLE SALES_HISTORY_BACKUP CLONE SALES_HISTORY. What is the initial storage cost impact of this operation?

Accepted Answer

The clone consumes no additional storage initially because it shares the source table's micro-partitions.. Zero-copy cloning creates a new table that references the same micro-partitions as the source, so no data is physically copied at clone time and initial storage impact is effectively zero. Additional storage is charged only when the source or the clone is modified, generating new micro-partitions. This behavior is central to using clones for backups and dev environments without doubling storage.

Answer

The clone consumes additional storage equal to the table's metadata size only.

Answer

The clone consumes additional storage only after the source table is dropped.

Answer

The clone immediately consumes an additional 5 TB of storage because all micro-partitions are copied.

Question 4

A data engineer is creating a new table to hold a large volume of raw clickstream events that are loaded continuously and only retained for reporting within the same day. The team wants to minimize storage costs while still allowing recovery of rows modified within the last 24 hours. Which table type best fits these requirements?

Accepted Answer

A transient table with DATA_RETENTION_TIME_IN_DAYS set to 1. Transient tables are the right choice for large, short-lived datasets because they support up to 1 day of Time Travel for recent-change recovery while avoiding Fail-safe storage entirely. Permanent tables would add unnecessary Fail-safe cost, temporary tables are session-scoped, and external tables do not provide Snowflake-managed Time Travel over their files.

Answer

An external table over a cloud storage stage with a 1-day retention policy

Answer

A temporary table with DATA_RETENTION_TIME_IN_DAYS set to 1

Answer

A permanent table with DATA_RETENTION_TIME_IN_DAYS set to 1

Question 5

A data engineer maintains 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 accidentally truncates the table and immediately realizes the mistake. What is the most reliable way to recover the lost rows?

Accepted Answer

Query the table with the AT (TIMESTAMP => ...) clause to read the rows as they existed before the truncate.. Transient tables in Enterprise Edition support Time Travel up to a maximum of 1 day, which is exactly the configured retention here. Because the truncate just happened, the engineer can still read the historical version of the table with the AT clause and copy the rows back. Fail-safe does not cover transient tables, UNDROP only applies to dropped objects, and no REST API provides point-in-time table restoration.

Answer

Use the Snowflake Time Travel REST API to request a point-in-time restore of the table to the previous hour.

Answer

Run UNDROP TABLE STG_EVENTS, because TRUNCATE places the table in a recoverable dropped state.

Answer

Restore the table from Fail-safe, because Fail-safe retains the pre-truncation micro-partitions for 7 days.

Question 6

A Snowflake account has a tag-based masking policy on column CUSTOMER.SSN. An analyst runs a query that applies the SYSTEM$GET_TAG function to that column. The analyst has been granted the APPLY MASKING POLICY privilege on the tag, but not the USAGE privilege on the tag. What does the analyst see for the SSN column value?

Accepted Answer

The masked SSN value, because the masking policy is enforced regardless of tag privileges.. Masking policies attached to tags are enforced regardless of whether the querying user can read the tag. A user without USAGE on the tag cannot see tag metadata but still receives masked data because the policy is evaluated at query time against the column. APPLY MASKING POLICY is an administrative privilege for managing tag-policy associations, not for bypassing enforcement.

Answer

NULL, because the masking policy cannot be resolved without tag access.

Answer

An error, because the analyst lacks USAGE on the tag required to evaluate the masking policy.

Answer

The unmasked SSN value, because APPLY MASKING POLICY overrides tag visibility.

Question 7

A data engineer needs to audit all grants of the 'SYSADMIN' role to users across the Snowflake account. The engineer has access to the ACCOUNTADMIN role and wants to retrieve this information efficiently. Which Snowflake view should be queried?

Accepted Answer

SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS. To audit which users have been granted the SYSADMIN role, the GRANTS_TO_USERS view in the ACCOUNT_USAGE schema is the correct source. It records all role-to-user grants, including the role name and grantee. Filtering on the ROLE column for 'SYSADMIN' yields the required list. Other views either lack grant information or focus on different objects.

Answer

SNOWFLAKE.ACCOUNT_USAGE.USERS

Answer

SNOWFLAKE.ACCOUNT_USAGE.ROLES

Answer

SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES

Question 8

A data engineer notices that a dashboard query returns instantly on repeat executions but takes much longer the first time each morning. No data has changed overnight. Which Snowflake feature is most directly responsible for the fast repeat executions?

Accepted Answer

The query result cache stores the result of the identical query and returns it without re-executing, as long as the underlying data and query text are unchanged.. The near-instant repeat execution with unchanged data is the signature of the query result cache, which persists results and serves identical queries without recomputation. The first run of the day must execute because the result is not yet cached or was invalidated, and any data change would also invalidate it.

Answer

The metadata cache holds micro-partition statistics that let the optimizer skip execution entirely and return a stored row set.

Answer

The local disk cache on the warehouse nodes retains the scanned micro-partitions so the second execution reads them without remote I/O.

Answer

The virtual warehouse remains suspended overnight, so the first execution warms it and later executions reuse the warmed compute.

Question 9

A data engineer manages a Snowflake account where a transient table named STAGE_EVENTS is loaded hourly. To cut storage costs, the engineer plans to set DATA_RETENTION_TIME_IN_DAYS = 0 on this transient table. Which outcome will occur?

Accepted Answer

Time Travel is disabled for the table, and Fail-safe does not apply to it.. Setting DATA_RETENTION_TIME_IN_DAYS to 0 on a transient table disables Time Travel, and transient tables are never covered by Fail-safe. The table continues to work for active queries, but historical recovery is unavailable. This is the intended low-cost configuration for staging data that can be reloaded from source systems if needed.

Answer

The table is automatically converted to a permanent table to preserve recoverability.

Answer

The command fails because transient tables require a minimum retention of 1 day.

Answer

Time Travel is disabled, but the table still receives the standard 7-day Fail-safe period.

Question 10

A data engineer manages a Snowpipe that ingests files from an external stage. The pipe uses a file format with SKIP_HEADER = 1, and the source files are regenerated daily with the same names in the same stage path. The engineer notices that only the first day's files are loaded and subsequent regenerated files are ignored. What is the most likely cause?

Accepted Answer

Snowpipe tracks loaded files by name and path, and since the regenerated files have identical names and paths, they are skipped as already processed.. Snowpipe uses load history to avoid reprocessing files. Because the regenerated files keep identical names and paths, Snowpipe considers them already loaded and skips them. To ingest the new content, the engineer must either rename the files, move them to a new path, or clear the pipe's load history before the next load.

Answer

The external stage has a file format that includes a PATTERN option matching only the original file names.

Answer

The pipe's AUTO_INGEST setting is disabled, so Snowpipe only polls for new files every 24 hours.

Answer

The pipe's file format has SKIP_HEADER set to 1, which causes Snowflake to skip all files after the first load.

Question 11

A data engineer loads CSV files into a Snowflake table using COPY INTO from an internal stage. Some rows fail validation because a numeric column contains non-numeric text. The engineer wants the load to continue and capture the rejected rows for later analysis. Which approach should be used?

Accepted Answer

Set ON_ERROR = 'CONTINUE' and use the COPY statement's error output to query rejected rows. ON_ERROR = 'CONTINUE' is designed for tolerant loading: rows that fail conversion or validation are skipped, valid rows are inserted, and Snowflake retains error details that can be queried from the COPY result or load history. This allows the pipeline to proceed while preserving rejected rows for later inspection and remediation.

Answer

Set ON_ERROR = 'CONTINUE' and configure VALIDATION_MODE = 'RETURN_ERRORS'

Answer

Set ON_ERROR = 'ABORT_STATEMENT' and review the query history for the failed statement

Answer

Set ON_ERROR = 'SKIP_FILE' and rely on the load history to identify which files were skipped

Question 12

A data engineer maintains a transient table named RAW_EVENTS in a Snowflake Enterprise Edition account. The table currently has DATA_RETENTION_TIME_IN_DAYS set to 1. The engineer attempts to run ALTER TABLE RAW_EVENTS SET DATA_RETENTION_TIME_IN_DAYS = 14; and the statement fails. What is the reason for the failure?

Accepted Answer

Transient tables support a maximum Time Travel retention of 1 day, so 14 days exceeds the allowed limit.. Transient tables are designed for data that does not require long recovery windows, so Snowflake caps their Time Travel retention at 1 day. Attempting to set 14 days violates that cap and the ALTER statement fails. Permanent tables, by contrast, can go up to 90 days on Enterprise Edition, which is the distinction the engineer is missing.

Answer

The account is on Enterprise Edition, which limits Time Travel to 1 day for all table types.

Answer

Time Travel retention can only be increased during the table creation statement, not with ALTER TABLE.

Answer

The table must be cloned before its retention period can be modified beyond the default.

Question 13

An organization wants to track all data access in their Snowflake account for compliance. They need to know which columns were accessed by which queries, and they want to retain this information for at least one year. Which Snowflake feature should they use to meet this requirement?

Accepted Answer

Access History. Access History is specifically designed to provide column-level access auditing, capturing which columns were read or written by queries. It retains data for 365 days, satisfying the one-year retention requirement. Query History lacks column-level detail and has a shorter retention. Login History and Object Dependencies do not track data access. Thus, Access History is the correct feature.

Answer

Login History

Answer

Object Dependencies

Answer

Query History

Question 14

A data engineer is building a transformation pipeline that must read semi-structured events from a stage, parse them, and write the output to a target table. The pipeline will run on a schedule and must be version-controlled and testable like other software artifacts. The team wants to avoid writing SQL scripts that are hard to unit test. Which Snowflake feature should the engineer use to implement this transformation?

Accepted Answer

Snowpark with a Python stored procedure. Snowpark is the appropriate choice because it allows developers to write transformation logic in Python, which can be unit tested and version controlled. It also integrates with Snowflake's compute and handles semi-structured data. The other options either lack testability, are not designed for complex transformations, or are technically invalid for the described use case.

Answer

A materialized view over the stage

Answer

A series of SQL scripts executed by a task

Answer

Snowpipe with a transformation function

Question 15

A data engineer needs to move a large table from an on-premises Oracle database into Snowflake on a recurring nightly basis. The source system allows outbound connections to cloud endpoints but does not permit installing third-party agents on the database host. Which Snowflake-native approach best fits these constraints?

Accepted Answer

Stage the extracted files in cloud storage and use COPY INTO with a storage integration. When agents cannot be installed on a source database, the practical Snowflake-native pattern is to extract to cloud storage and load with COPY INTO. A storage integration keeps credentials out of SQL, and COPY INTO handles parallelism, error handling, and load history. Streaming connectors and external tables assume different source technologies and would not work against a plain Oracle host under these constraints.

Answer

Create an external table directly over the Oracle database

Answer

Mount the Oracle data files as a network share and query them with a directory table

Answer

Use Snowpipe Streaming with the Snowflake Kafka connector pointed at Oracle

Question 16

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?

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 17

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 18

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 19

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 20

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>