DEA-C02 Practice Test 7 — 10 Questions

Question 1

A data engineer is analyzing a slow-running query that performs a large aggregation over a table with many columns. The query profile shows that most time is spent in the 'Aggregate' operator, and there is significant data spilling to local disk. Which action is most likely to reduce the spilling and improve performance?

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

Accepted Answer

Increase the warehouse size to provide more memory for the aggregation.. Local disk spilling during aggregation indicates that the aggregation's working set exceeds the available memory on the warehouse nodes. Scaling up the warehouse provides more memory per node, which can allow the aggregation to be performed in-memory, reducing spilling and improving performance. This is a direct and effective solution for memory-bound operations.

Answer

Rewrite the query to use a window function instead of a GROUP BY aggregation.

Answer

Add a clustering key on the column used in the GROUP BY clause.

Answer

Enable the USE_CACHED_RESULT parameter to reuse previous aggregation results.

Question 2

A data engineer needs to audit all tag assignments across the account to ensure that no sensitive columns are missing required tags. Which Snowflake view should the engineer query to retrieve a list of all tags applied to columns, including the tag name, value, and the object it is applied to?

Accepted Answer

SNOWFLAKE.ACCOUNT_USAGE.TAG_REFERENCES. To audit tag assignments, the TAG_REFERENCES view is the correct choice. It provides a comprehensive list of all tag references, including the tag name, value, and the object (e.g., column) it is applied to. The TAGS view only provides tag metadata, while COLUMNS and POLICY_REFERENCES do not include tag assignments.

Answer

SNOWFLAKE.ACCOUNT_USAGE.TAGS

Answer

SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES

Answer

SNOWFLAKE.ACCOUNT_USAGE.COLUMNS

Question 3

A data engineer runs the following statement on a permanent table in a Snowflake account: ALTER TABLE SALES_ARCHIVE SET DATA_RETENTION_TIME_IN_DAYS = 14; The table previously had a retention of 1 day, and the account is on Enterprise Edition. Which statement accurately describes the effect?

Accepted Answer

The table retains historical data for 14 days going forward, but data older than the prior 1-day window is not recovered.. Increasing DATA_RETENTION_TIME_IN_DAYS extends the Time Travel window for future data changes. It does not retroactively recover historical versions that were already purged under the shorter retention period. The engineer should expect protection for new modifications over the next 14 days, not restoration of previously expired history.

Answer

The change is rejected because retention can only be increased at table creation time.

Answer

The table's storage usage doubles because Snowflake clones all existing micro-partitions to extend retention.

Answer

Existing historical data older than 1 day becomes immediately available for Time Travel queries.

Question 4

A data engineer needs to periodically copy incremental data from an on-premises database into Snowflake. The source system can export CSV files to a network share, and the engineer wants to use a Snowflake-managed stage that does not require configuring an external cloud storage bucket. Which stage type should be used?

Accepted Answer

An internal stage created with CREATE STAGE, then upload files using PUT.. An internal stage is the correct choice because it is managed by Snowflake and does not require an external cloud storage bucket. Files from the network share can be uploaded with PUT and then loaded with COPY INTO. External stages and the various internal stage types do not provide automatic ingestion from network shares.

Answer

A table stage referenced with @%table_name, which is shared across all users and databases.

Answer

A user stage referenced with the @~ prefix, which automatically ingests files from any network share.

Answer

An external stage pointing to an Amazon S3 bucket managed by the engineer.

Question 5

A Snowflake data engineer has been tasked with implementing dynamic data masking on a CUSTOMERS table so that the SSN column is fully redacted for all users except those with the role PII_ADMIN. The engineer wants the masking to apply automatically whenever the column is queried, without changing any application SQL. Which Snowflake object should the engineer create and attach to the SSN column?

Accepted Answer

A masking policy created with CREATE MASKING POLICY and applied to the SSN column using ALTER TABLE ... MODIFY COLUMN ... SET MASKING POLICY.. Dynamic column redaction in Snowflake is implemented with masking policies, which are schema-level objects attached directly to a column. Once attached, the policy expression evaluates on every query, returning the original value to authorized roles and a masked value to others, with no application changes required. This satisfies both the automatic enforcement requirement and the role-based exception for PII_ADMIN.

Answer

A row access policy created with CREATE ROW ACCESS POLICY and attached to the CUSTOMERS table.

Answer

A secure view that selects all columns from CUSTOMERS but replaces SSN with a constant for non-admins.

Answer

A tag-based classification using ALTER TABLE ... SET TAG on the SSN column.

Question 6

A data engineer has a permanent table ORDERS_FACT in an Enterprise Edition account with DATA_RETENTION_TIME_IN_DAYS set to 14. The table was dropped 20 days ago. The engineer now runs UNDROP TABLE ORDERS_FACT. What is the outcome?

Accepted Answer

The UNDROP fails because the table is beyond the 14-day Time Travel window and is now only recoverable through Snowflake Support from Fail-safe.. UNDROP restores a dropped table only while it remains within its Time Travel retention window. With a 14-day retention and a drop 20 days ago, the table has left Time Travel and entered Fail-safe, which lasts 7 days and is accessible only through Snowflake Support. Therefore the UNDROP fails and user-level recovery is no longer available.

Answer

The UNDROP succeeds because Fail-safe keeps dropped tables recoverable for up to 90 days.

Answer

The UNDROP succeeds and restores the table with its data intact.

Answer

The UNDROP succeeds but only restores the table structure without any data.

Question 7

A data engineer is designing a storage strategy for a Snowflake account that uses Enterprise Edition. The engineer needs to ensure that critical tables can be recovered for up to 90 days, while non-critical staging tables should minimize storage costs. Which two statements accurately describe Snowflake Time Travel and Fail-safe behavior for permanent tables? (Choose two.)

Accepted Answer

Permanent tables in Enterprise Edition can have a Time Travel retention period of up to 90 days.. Permanent tables in Enterprise Edition support up to 90 days of Time Travel retention, and Fail-safe adds a fixed 7-day period after Time Travel expires. These two features together provide a comprehensive recovery strategy for critical data. Transient tables are limited to 1 day of Time Travel and have no Fail-safe, making them unsuitable for long-term recovery needs.

Answer

Fail-safe can be configured to extend beyond 7 days for permanent tables in Enterprise Edition.

Answer

Transient tables can have a Time Travel retention period of up to 90 days if the account is Enterprise Edition.

Answer

Time Travel retention for permanent tables can be set to 0 days to disable it, but Fail-safe still applies.

Question 8

A data engineer is investigating a query that reads a large table and returns only a few rows after filtering on a high-cardinality column. The Query Profile shows a high percentage of partitions scanned relative to partitions total. Which two actions should the engineer take to improve pruning? (Choose two.)

Accepted Answer

Define a clustering key on the frequently filtered high-cardinality column so related values are co-located in the same micro-partitions.. Effective pruning depends on two things: physical co-location of similar values and predicates the optimizer can translate into partition-value bounds. Clustering the filtered column groups similar values, while a direct comparison to a constant gives the optimizer the bounds it needs. Together they shrink the set of micro-partitions that must be read for a selective filter.

Answer

Increase the virtual warehouse size so more compute nodes are available to evaluate the filter predicate in parallel.

Answer

Convert the table to a view over the same data so the optimizer can push the filter predicate down into the view definition.

Answer

Apply a filter transformation inside the query that wraps the filtered column in a function such as UPPER or CAST before comparing it to a literal.

Question 9

A data engineer has a permanent table ORDERS_ARCHIVE with DATA_RETENTION_TIME_IN_DAYS set to 30. An analyst accidentally runs DELETE FROM ORDERS_ARCHIVE WHERE order_date < '2022-01-01' and commits the transaction. Twenty minutes later, the engineer wants to recover only the deleted rows without disturbing the current state of the table. Which approach is correct?

Accepted Answer

Query the table with SELECT * FROM ORDERS_ARCHIVE BEFORE (STATEMENT => '<query_id>') and insert the missing rows back.. Recovering deleted rows from a table that still exists requires reading its historical state with Time Travel and re-inserting the missing data. The BEFORE (STATEMENT => ...) clause targets the exact snapshot preceding the delete, so only the deleted rows are restored and post-delete changes are preserved. UNDROP applies to dropped tables, retention changes do not restore data, and cloning at an arbitrary timestamp does not precisely target the delete.

Answer

Run UNDROP TABLE ORDERS_ARCHIVE to restore the table to its pre-delete state.

Answer

Run ALTER TABLE ORDERS_ARCHIVE SET DATA_RETENTION_TIME_IN_DAYS = 30 to refresh the recovery window and then query the deleted rows.

Answer

Run CREATE TABLE ORDERS_RESTORE CLONE ORDERS_ARCHIVE AT (TIMESTAMP => CURRENT_TIMESTAMP - INTERVAL '30 minutes') and swap the tables.

Question 10

A production team uses a permanent table with DATA_RETENTION_TIME_IN_DAYS set to 14. After an accidental DELETE, the team successfully runs a Time Travel query to recover the rows. Two days later, the same team needs to recover a different set of rows that were deleted 20 days ago. What will happen when they attempt Time Travel for the 20-day-old deletion?

Accepted Answer

The Time Travel query will fail because the 20-day-old deletion is beyond the 14-day retention period.. Time Travel availability is determined by the table's DATA_RETENTION_TIME_IN_DAYS setting. With a 14-day retention, a deletion 20 days in the past is outside the window, so the query cannot return that historical state. A previous successful recovery does not reset or extend the retention period; each recovery must fall within the configured window.

Answer

The Time Travel query will succeed because Fail-safe retains data for 7 days after the Time Travel period.

Answer

The Time Travel query will succeed only if the table was cloned before the deletion.

Answer

The Time Travel query will succeed because permanent tables always retain 90 days of history regardless of the configured setting.