DEA-C02 Practice Test 6 — 10 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.