DEA-C02 Practice Exam 3 — 20 Questions

Question 1

A data engineer needs to unload a large table to an external stage in Parquet format. The table contains a column with sensitive data that must be masked in the unloaded files. The engineer wants to use a secure view that applies a masking policy, and then unload from that view. Which statement accurately describes the behavior when unloading from a secure view?

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

Accepted Answer

Unloading from a secure view is allowed, and the masking policies defined in the view are applied to the unloaded data.. COPY INTO <location> supports unloading from secure views, and the secure view's definition, including masking and row access policies, is enforced during the unload. This means sensitive columns can be masked in the output files. The other options either deny support, claim masking is bypassed, or impose unnecessary role requirements. The correct behavior is that policies are applied, ensuring data security.

Answer

Unloading from a secure view is allowed, but the masking policies are not applied; the underlying data is unloaded unmasked.

Answer

Unloading from a secure view is not supported; COPY INTO <location> requires a base table or a regular view.

Answer

Unloading from a secure view requires the ACCOUNTADMIN role and the masking policies are ignored.

Question 2

A data engineer needs to protect a set of permanent tables in a Snowflake Enterprise Edition account. The requirement is to allow querying of historical versions for up to 30 days and to allow recovery of dropped tables within that same window. Which two actions should the engineer take to meet these requirements? (Choose two.)

Accepted Answer

Confirm the account is on Enterprise Edition, which supports retention windows longer than the Standard Edition one-day maximum.. Meeting a 30-day queryable-history requirement on permanent tables requires Enterprise Edition, which raises the maximum retention from one day to ninety days, and an explicit DATA_RETENTION_TIME_IN_DAYS setting of 30 on each table. Transient tables cap retention at one day, Fail-safe is non-queryable and fixed at seven days, and there is no configurable FAILSAFE_DAYS parameter.

Answer

Convert the permanent tables to transient tables to increase the maximum allowed retention window.

Answer

Rely on Fail-safe to provide the 30-day queryable history window for dropped tables.

Answer

Enable Fail-safe on the tables by setting FAILSAFE_DAYS to 30 using ALTER TABLE.

Question 3

A data engineer manages 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 wants Time Travel to retain data for 30 days for this table. What will happen if the engineer executes ALTER TABLE STG_EVENTS SET DATA_RETENTION_TIME_IN_DAYS = 30?

Accepted Answer

The statement will fail with an error because transient tables support a maximum Time Travel retention of 1 day.. Transient tables are limited to a maximum Time Travel retention of 1 day. Attempting to set a higher value such as 30 days causes Snowflake to reject the ALTER TABLE statement with an error. To achieve 30 days of Time Travel, the table must be a permanent table in an account with Enterprise Edition or higher. The table type is the deciding factor.

Answer

The retention period will be set to 30 days, but only if the table has not been cloned previously.

Answer

The retention period will be set to 30 days but Fail-safe will still be disabled for the table.

Answer

The retention period will be set to 30 days, and Time Travel will retain data for 30 days.

Question 4

A data engineer is implementing a data classification process using Snowflake's Data Classification feature. The engineer wants to automatically classify columns containing sensitive data and then use the results to apply masking policies. After running the classification, the engineer notices that some columns that should be classified as 'EMAIL' are not being tagged. The engineer has verified that the data contains valid email addresses. What is the most likely reason for the missing classification?

Accepted Answer

The Data Classification feature only samples a subset of rows, and the sample did not include enough email addresses to meet the threshold.. Data Classification uses sampling to analyze column data. If the sample does not contain a sufficient number of email addresses, the column may not be classified as EMAIL. This is a known limitation. The other options are incorrect because classification does not depend on column names, can handle NULLs, and is not restricted to a specific schema.

Answer

The Data Classification feature requires that the column name contains the word 'EMAIL' to classify it as EMAIL.

Answer

The Data Classification feature cannot classify columns that contain NULL values.

Answer

The Data Classification feature is only available for columns in the PUBLIC schema.

Question 5

A data engineer is auditing storage consumption in a Snowflake account. A large permanent table named SALES_FACT has DATA_RETENTION_TIME_IN_DAYS set to 60. Over the past month, ETL jobs have performed several full-table UPDATE operations that rewrote most micro-partitions. The engineer notices that the table's Time Travel storage has grown significantly even though the visible row count is unchanged. What is the most likely cause of the increased Time Travel storage?

Accepted Answer

Time Travel storage grows because each UPDATE creates new micro-partitions while prior versions are retained for the 60-day retention window.. Time Travel storage reflects historical micro-partition versions retained for the configured window. Each UPDATE that rewrites micro-partitions creates new versions, and the prior versions are stored for 60 days. Repeated full-table updates therefore accumulate substantial historical data, increasing Time Travel storage even when the visible row count is stable.

Answer

Time Travel storage grows because the 60-day retention forces Snowflake to store a full compressed copy of the table in Fail-safe.

Answer

Time Travel storage grows because Snowflake copies the entire table into a separate Time Travel schema on each UPDATE.

Answer

Time Travel storage grows because the account has exceeded its storage quota, causing Snowflake to duplicate micro-partitions as a redundancy measure.

Question 6

A data engineer is designing a transformation that requires unpivoting a wide table with columns `q1_sales`, `q2_sales`, `q3_sales`, and `q4_sales` into a long format with columns `quarter` and `sales`. The table has millions of rows. Which Snowflake construct should be used to achieve this efficiently?

Accepted Answer

Use the `UNPIVOT` clause in a SELECT statement.. The UNPIVOT clause is specifically designed to transform columns into rows. It is a native Snowflake feature that is optimized for performance on large tables. Using UNION ALL or LATERAL FLATTEN can achieve similar results but with more complexity and potentially less efficiency. PIVOT does the reverse and is not appropriate here.

Answer

Use the `PIVOT` clause in a SELECT statement.

Answer

Use a `LATERAL FLATTEN` on an array constructed from the quarter columns.

Answer

Use a series of `UNION ALL` SELECT statements, one for each quarter column.

Question 7

A data engineer needs to continuously ingest JSON event files landing in an Amazon S3 bucket. The ingestion must be automatic, near real-time, and must reuse the same transformation logic already validated in a COPY INTO statement. The team wants the least administrative overhead. Which Snowflake feature should they configure?

Accepted Answer

A Snowpipe configured with COPY INTO from the S3 stage. Snowpipe is the Snowflake service built for continuous, event-driven loading. It runs COPY INTO statements automatically when new files arrive and can embed transformation logic, so the validated COPY logic is reused. Because it uses serverless compute triggered by cloud event notifications, it provides near real-time ingestion with very little operational management, fitting the stated constraints.

Answer

A scheduled task that runs COPY INTO every five minutes against the S3 stage

Answer

An external table defined over the S3 stage with automatic refresh enabled

Answer

A materialized view created over an external table pointing at the S3 stage

Question 8

A data engineer is using Snowpark for Python to transform data. The engineer needs to perform a join between two DataFrames and then apply a filter based on a column from the joined result. Which TWO methods are appropriate for this transformation? (Choose two.)

Accepted Answer

Use `DataFrame.join()` to combine the DataFrames, then `DataFrame.where()` to apply the condition.. In Snowpark, join() combines two DataFrames, and filter() or its alias where() applies a row-wise condition. These methods build a lazy query plan that executes on Snowflake. Other methods like merge(), union(), or select() do not perform a join followed by a row filter, so they are not appropriate for this transformation.

Answer

Use `DataFrame.union()` to combine the DataFrames, then `DataFrame.filter()` to apply the condition.

Answer

Use `DataFrame.join()` to combine the DataFrames, then `DataFrame.select()` to apply the condition.

Answer

Use `DataFrame.merge()` to combine the DataFrames, then `DataFrame.filter()` to apply the condition.

Question 9

A data engineer is building a transformation pipeline that must parse semi-structured log data stored in a VARIANT column named log_data. The JSON structure contains a nested array under the key 'events', and each element in the array has a 'timestamp' field. The engineer needs to produce one row per event with the timestamp extracted as a TIMESTAMP_NTZ value. Which SQL construct should be used to achieve this transformation efficiently?

Accepted Answer

Use the FLATTEN function with the LATERAL keyword to explode the 'events' array, and access the 'timestamp' field using the colon notation, casting it to TIMESTAMP_NTZ.. The FLATTEN function with LATERAL is the standard Snowflake construct for exploding nested arrays in VARIANT columns. It produces one row per array element, and the colon notation allows direct access to nested fields. Casting the extracted value to TIMESTAMP_NTZ ensures the correct data type for downstream processing. This method is efficient and leverages Snowflake's native semi-structured data handling.

Answer

Use the GET_PATH function to directly extract the 'timestamp' field from the array without exploding it, relying on implicit casting to TIMESTAMP_NTZ.

Answer

Use the OBJECT_CONSTRUCT function to rebuild the JSON, then use a JavaScript UDF to iterate over the array and return each timestamp.

Answer

Use the PARSE_JSON function to convert the VARIANT to a string, then use SPLIT_TO_TABLE to separate the array elements, and finally extract the timestamp with regular expressions.

Question 10

A data engineer needs to create a development copy of a 5 TB production table named FACT_SALES for query testing. The team wants the copy to share the same underlying micro-partitions as the source so no additional storage is consumed at creation time. Which command should the engineer use?

Accepted Answer

CREATE TABLE dev_db.public.fact_sales_clone CLONE prod_db.public.fact_sales;. Zero-copy cloning with CREATE TABLE ... CLONE creates a new table that references the same micro-partitions as the source, so no storage is duplicated at creation. CTAS would physically copy the data, CREATE TABLE ... LIKE would only copy the schema, and ALTER TABLE ... SET CLONE is not valid syntax. Only the CLONE form meets the no-extra-storage requirement.

Answer

CREATE TABLE dev_db.public.fact_sales_clone AS SELECT * FROM prod_db.public.fact_sales;

Answer

ALTER TABLE prod_db.public.fact_sales SET CLONE dev_db.public.fact_sales_clone;

Answer

CREATE TABLE dev_db.public.fact_sales_clone LIKE prod_db.public.fact_sales;

Question 11

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?

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 12

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 13

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 14

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 15

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 16

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 17

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 18

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 19

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 20

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.