DEA-C02 Practice Questions 2 — 50 Questions

Question 1

A data engineer is loading data from an external stage into a Snowflake table using a COPY INTO command. The source files are compressed with gzip and contain a header row. The engineer wants to skip the first row of each file and load the remaining data. Which COPY INTO option should be used?

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

Accepted Answer

SKIP_HEADER = 1. SKIP_HEADER = 1 is the correct COPY INTO option to skip the first row of each file during loading. It tells Snowflake to ignore the specified number of lines at the start of the file, which is typically the header. Other options control different aspects of file parsing and error handling.

Answer

FIELD_OPTIONALLY_ENCLOSED_BY = '"'

Answer

SKIP_FILE = '1'

Answer

HEADER = TRUE

Question 2

A data engineer is analyzing a query that filters on a column named 'status' which has only 5 distinct values. The table has 10 billion rows. The query is running slowly, and the query profile shows a full table scan. Which action is most likely to improve performance?

Accepted Answer

There is no effective optimization for this query; full scan is expected.. Filtering on a low-cardinality column such as 'status' with only 5 values means the query will likely retrieve a large fraction of the table. Snowflake's pruning relies on micro-partition metadata, but with so few distinct values, each micro-partition will contain multiple statuses, so pruning cannot eliminate many partitions. Clustering or search optimization on such a column is ineffective. Therefore, a full table scan is the expected and necessary operation, and there is no effective optimization for this specific filter.

Answer

Use a materialized view that pre-filters on the 'status' column.

Answer

Add a search optimization service on the 'status' column.

Answer

Create a clustering key on the 'status' column.

Question 3

A data engineer runs a query that joins a 5 TB fact table to a 200 GB dimension table. The query profile shows a broadcast operation for the dimension table and a local spilling node on the fact table. The engineer wants to reduce local spilling without changing the query results. The warehouse is a multi-cluster warehouse with sufficient memory. Which action is most likely to reduce local spilling?

Accepted Answer

Increase the warehouse size to provide more memory per node.. Local spilling indicates that an operation is exceeding the memory available on a warehouse node. Increasing the warehouse size provides more memory per node, which can prevent or reduce spilling. While other actions like filtering or clustering might reduce data volume, they do not directly address the memory constraint of the operation. Disabling broadcast could change the join strategy but may introduce other bottlenecks. Therefore, scaling up the warehouse is the most direct solution.

Answer

Add a clustering key on the join column of the fact table.

Answer

Reduce the size of the fact table by filtering out rows before the join.

Answer

Disable broadcast for the dimension table to force a shuffle join.

Question 4

An analyst needs to unload a 4 TB table from Snowflake to an external Amazon S3 stage for archival. The team wants maximum write throughput and wants the output organized so downstream tools can read subsets without scanning everything. Which combination of COPY INTO options best achieves this?

Accepted Answer

Set MAX_FILE_SIZE to a moderate value and use a partition-like folder path in the stage prefix. Unload throughput scales with the number of files written in parallel, which is governed by MAX_FILE_SIZE and the warehouse size. Organizing output under a meaningful prefix lets downstream consumers prune by path instead of reading the entire export. Single-file output, overwrite semantics, and uncompressed CSV do not advance either goal and in some cases actively harm performance or safety.

Answer

Add a FILE_FORMAT option of TYPE = CSV with COMPRESSION = NONE

Answer

Use OVERWRITE = TRUE and leave MAX_FILE_SIZE at its default

Answer

Set SINGLE = TRUE so the output is one large file

Question 5

A data engineer is responsible for implementing data governance in Snowflake. The organization requires that all access to sensitive data be auditable and that data usage can be attributed to specific users and roles. Which two Snowflake features should the engineer use to meet these requirements? (Choose two.)

Accepted Answer

Query History in the ACCOUNT_USAGE schema to see all queries executed in the account.. Access History and Query History in the ACCOUNT_USAGE schema both provide detailed audit trails of data access. Access History records column-level read and write access, while Query History captures all queries with user and role information. Together, they enable comprehensive auditing and attribution of data usage to specific users and roles, meeting the governance requirements.

Answer

Tag-based masking policies to enforce access controls based on tags.

Answer

Data Classification to automatically tag sensitive columns and track their usage.

Answer

Object Dependencies in the ACCOUNT_USAGE schema to map relationships between tables and views.

Question 6

A data engineer is troubleshooting a Snowpipe that is not loading new files from an external Amazon S3 stage. The pipe definition is valid and manual ALTER PIPE ... REFRESH loads the files successfully, but automatic ingestion never triggers. Which cause is most consistent with these symptoms?

Accepted Answer

The S3 bucket's event notification is not configured to publish to the pipe's notification channel. When manual pipe refresh succeeds but automatic ingestion never fires, the COPY logic and permissions are proven correct, leaving the event delivery path as the culprit. The S3 bucket must be configured to publish object-created notifications to the pipe's notification channel. Without that subscription, Snowflake has no signal that new files exist, so the pipe stays idle despite valid files and a valid definition.

Answer

The pipe's COPY INTO statement is missing the PATTERN clause

Answer

The storage integration is missing the USAGE grant to the role that owns the pipe

Answer

The target table has a stream attached that is blocking inserts

Question 7

A data engineer needs to prevent any future column additions to a critical ORDERS table from containing unprotected PII. The goal is to automatically classify new columns and receive alerts when sensitive data is detected. Which Snowflake feature should be configured to achieve this?

Accepted Answer

Data Classification with automatic tagging and notifications. Data Classification is designed to automatically scan and classify columns containing sensitive data, including PII. It can be configured to send notifications when new sensitive columns are detected, providing ongoing governance without manual effort. This directly addresses the need to protect future column additions.

Answer

Object Tagging with manual tag assignment

Answer

Dynamic Data Masking policies on the table

Answer

Row Access Policies on the table

Question 8

A data engineer needs to transform a large fact table by adding a column that contains the previous row's `sale_amount` partitioned by `customer_id` and ordered by `sale_date`. The table has billions of rows, and the transformation must run efficiently without shuffling data unnecessarily. Which Snowflake feature should be used to achieve this?

Accepted Answer

Use the `LAG` window function with a `PARTITION BY customer_id ORDER BY sale_date` clause.. The LAG window function is specifically designed to return a value from a preceding row within a partition. Partitioning by customer_id and ordering by sale_date ensures the immediate previous sale for each customer is correctly identified. Snowflake's optimizer can handle large datasets efficiently with window functions, avoiding the costly shuffles typical of self-joins or correlated subqueries.

Answer

Use a correlated subquery that selects the maximum `sale_amount` from the same table where `sale_date` is less than the current row's `sale_date`.

Answer

Use the `LEAD` window function with a `PARTITION BY customer_id ORDER BY sale_date` clause and then reverse the order.

Answer

Use a self-join on `customer_id` where the right table's `sale_date` is less than the left table's `sale_date`, then filter to the maximum right `sale_date`.

Question 9

A data engineer is analyzing a Query Profile for a query that joins a large fact table to a small dimension table. The profile shows a significant amount of time spent in the 'Join' operator, and the 'Bytes spilled to remote storage' metric is high. The engineer has already confirmed that the small table is used as the build side. Which optimization should the engineer try next to reduce remote spilling?

Accepted Answer

Increase the warehouse size to provide more memory per node.. Remote spilling indicates that the join operator exceeded its memory budget. Even with a small build side, the probe side or concurrent operations can cause memory pressure. Increasing the warehouse size provides more memory per node, allowing the join to complete without spilling. Other options either do not address memory or change semantics.

Answer

Change the join type from inner join to left join to reduce data shuffling.

Answer

Use a clustering key on the join column of the large table to reduce the build side size.

Answer

Add a filter to the small table to reduce its size further.

Question 10

A data engineer is using Snowpark for Python to perform transformations on a Snowflake table. The engineer wants to leverage Snowpark's capabilities for data transformation. Which two statements accurately describe Snowpark's features for transformation? (Choose two.)

Accepted Answer

Snowpark allows you to write transformations using a DataFrame API that is lazily evaluated and executed on the Snowflake compute engine.. Snowpark's DataFrame API enables lazy evaluation and pushdown to Snowflake, and it supports UDFs in multiple languages. These two features are fundamental for performing transformations efficiently. The other options incorrectly describe Snowpark as caching client-side, requiring data extraction, or providing a visual interface, which are not accurate.

Answer

Snowpark automatically caches all intermediate transformation results in memory on the client side to speed up iterative development.

Answer

Snowpark requires data to be extracted to a local Python environment for any transformation that uses third-party libraries.

Answer

Snowpark provides a built-in visual interface for designing transformation pipelines without writing code.

Question 11

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?

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 12

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 13

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 14

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 15

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 16

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 17

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 18

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 19

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 20

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.