DEA-C02 Practice Test 4 — 25 Questions

Question 1

A data engineer is designing a recovery strategy for a critical permanent table FACT_SALES in an Enterprise Edition account. The team wants the ability to restore the table to any point within the last 60 days and also wants protection against a catastrophic failure that corrupts the data. Which statement correctly describes the available recovery windows?

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

Accepted Answer

Setting DATA_RETENTION_TIME_IN_DAYS to 60 gives 60 days of Time Travel plus 7 days of Fail-safe, for a total of 67 days of recoverability.. Permanent tables in Enterprise Edition can be configured up to 90 days of Time Travel, and Fail-safe adds a fixed seven-day window after Time Travel expires. A 60-day retention therefore yields 60 days of self-service recovery plus 7 days of Support-assisted recovery. Fail-safe does not overlap Time Travel, transient tables are capped at one day, and 30 is the default rather than the maximum.

Answer

Setting DATA_RETENTION_TIME_IN_DAYS to 60 gives 60 days of Time Travel, and Fail-safe adds no extra window because it overlaps Time Travel.

Answer

The maximum Time Travel for a permanent table is 30 days, so 60 days is not achievable and the setting will fail.

Answer

Setting DATA_RETENTION_TIME_IN_DAYS to 60 requires converting the table to a transient table to extend beyond 1 day.

Question 2

A data engineer is unloading a large fact table to an external stage and wants to minimize the total volume of data transferred while keeping files readable by downstream tools. The table contains many repeated values in several columns. Which approach best reduces the unloaded data size?

Accepted Answer

Unload with FILE_FORMAT = (TYPE = PARQUET COMPRESSION = SNAPPY).. Parquet with SNAPPY compression gives the best size reduction because its columnar layout and per-column encodings efficiently handle repeated values, and SNAPPY compresses the encoded blocks. CSV, JSON, and file-count tuning do not exploit column repetition, so they leave far more data to transfer for the same table.

Answer

Unload with FILE_FORMAT = (TYPE = JSON COMPRESSION = GZIP).

Answer

Unload with FILE_FORMAT = (TYPE = CSV COMPRESSION = GZIP).

Answer

Increase MAX_FILE_SIZE so fewer, larger files are produced.

Question 3

A data engineer notices that a query performing a large GROUP BY on a high-cardinality column is slow. The Query Profile shows that the aggregation step is spilling to local disk. The engineer wants to reduce local spilling without changing the query logic. Which action is most appropriate?

Accepted Answer

Increase the warehouse size to provide more memory for the aggregation.. Local disk spilling in a high-cardinality GROUP BY means the aggregation's hash table exceeds available memory on the nodes. Increasing the warehouse size adds memory and compute nodes, allowing the hash table to remain in memory and eliminating the spill. This is the most direct fix when the query logic cannot be changed and the profile clearly points to the aggregation step as the spill location.

Answer

Add an ORDER BY clause on the grouping column to sort data before aggregation.

Answer

Reduce the warehouse size to decrease contention among nodes.

Answer

Set the MAX_CONCURRENCY_LEVEL parameter to 1 to give the query more resources.

Question 4

A data engineer is configuring a new permanent table in a Snowflake Standard Edition account. The engineer needs to ensure that the table can be recovered if it is accidentally dropped, but wants to minimize storage costs. The table will be updated frequently. Which DATA_RETENTION_TIME_IN_DAYS setting should the engineer choose to meet these requirements?

Accepted Answer

1. In Snowflake Standard Edition, permanent tables can have a maximum Time Travel retention of 1 day. To enable recovery of a dropped table while minimizing storage costs, the engineer should set DATA_RETENTION_TIME_IN_DAYS to 1. This provides a 1-day window for recovery and keeps Time Travel storage overhead to a minimum.

Answer

0

Answer

7

Answer

90

Question 5

A data engineer notices that a daily aggregation query scans 4 TB of a 5 TB table even though it only needs the last seven days of data. The table has a DATE column but no clustering key, and the query filters with DATE >= CURRENT_DATE - 7. Query Profile shows almost no partition pruning. What is the most effective change to reduce bytes scanned?

Accepted Answer

Add a clustering key on the DATE column and let Automatic Clustering maintain it.. Partition pruning depends on how well the filter column correlates with micro-partition boundaries. A DATE clustering key groups similar dates together, allowing the optimizer to skip most micro-partitions for a seven-day filter. Automatic Clustering preserves that layout over time. Predicate rewrites, materialized views, and search optimization do not change the physical clustering that governs pruning in this scenario.

Answer

Enable search optimization service on the DATE column to improve range filter performance.

Answer

Create a materialized view that selects only the last seven days and refresh it on a schedule.

Answer

Replace the filter with a BETWEEN clause using explicit literal dates instead of CURRENT_DATE arithmetic.

Question 6

A financial services firm stores account balances in a Snowflake table ACCOUNTS. A row access policy is defined so that analysts see only rows where REGION = CURRENT_REGION(). The firm also attaches a masking policy to the BALANCE column that returns NULL for users without the FINANCE role. An analyst with the ANALYST role queries SELECT REGION, BALANCE FROM ACCOUNTS. What will the analyst see?

Accepted Answer

Only rows matching the analyst's region, with BALANCE values returned as NULL because the analyst lacks the FINANCE role.. Row access policies and masking policies are complementary and both are enforced at query time. The row access policy constrains which rows are visible based on the region predicate, while the masking policy rewrites the BALANCE value for roles not granted the FINANCE role. The analyst therefore sees a region-filtered result set in which BALANCE is NULL, demonstrating defense in depth.

Answer

An error is raised because a table cannot have both a row access policy and a masking policy attached.

Answer

All rows in ACCOUNTS, but BALANCE values are NULL for every row.

Answer

Only rows matching the analyst's region, with actual BALANCE values visible because row access policies take precedence.

Question 7

A data engineer accidentally drops a permanent table named CUSTOMER_DIM that had DATA_RETENTION_TIME_IN_DAYS set to 5. Six days after the drop, the engineer runs UNDROP TABLE CUSTOMER_DIM; and it fails. What is the most likely explanation?

Accepted Answer

The 5-day Time Travel window for the dropped table has expired, so the table can no longer be undropped.. UNDROP depends on the dropped table's Time Travel retention to locate and restore it. With a 5-day retention setting, the table could only be recovered during those 5 days. At 6 days the window had closed, so the UNDROP statement failed because the historical version was no longer available for restoration.

Answer

UNDROP only works within 24 hours of a drop regardless of the retention setting.

Answer

A new table with the same name was created, which permanently blocked the UNDROP operation.

Answer

The table entered Fail-safe immediately upon being dropped, making UNDROP unavailable.

Question 8

A data engineer is setting up Snowflake Data Classification on a table containing customer feedback. The engineer wants to ensure that the classification process identifies columns with potentially sensitive information and tags them appropriately. Which two actions are required to enable Data Classification on the table? (Choose two.)

Accepted Answer

Grant the role used for classification the USAGE privilege on the database and schema containing the table.. To perform Data Classification, the role must have USAGE on the database and schema, and SELECT on the table. These privileges allow the classification process to access and sample the data. Custom classifiers and specific roles like ACCOUNTADMIN are not required. Thus, the two correct actions are granting USAGE and SELECT.

Answer

Enable the ACCOUNTADMIN role to run the classification process.

Answer

Set the CLASSIFICATION_PROFILE parameter on the table to enable automatic classification.

Answer

Create a custom classifier that defines the patterns for sensitive data before running classification.

Question 9

A data engineer needs to restore a specific version of a permanent table named FINANCE_LEDGER as it existed 40 hours ago, but the table is currently 2 TB and has DATA_RETENTION_TIME_IN_DAYS set to 1. The engineer wants to recover the historical data without affecting the current table. Which approach correctly recovers the data?

Accepted Answer

The data cannot be recovered because the 40-hour-old version is beyond the 1-day Time Travel window and Fail-safe is not directly queryable.. Time Travel access is bounded by the table's current retention setting, and changing that setting only applies going forward. A 40-hour-old version exceeds the 1-day window, and Fail-safe cannot be queried by users. Therefore the historical version is not retrievable through normal means, which is why the engineer's goal cannot be met in this configuration.

Answer

Use Fail-safe to restore the table to the 40-hour-old state, then query it.

Answer

Query the table using AT (OFFSET => -144000) and insert the results into a new table.

Answer

Increase DATA_RETENTION_TIME_IN_DAYS to 90, then query 40 hours ago using Time Travel.

Question 10

A data engineer needs to continuously load JSON event files that arrive in an Amazon S3 bucket into a Snowflake table with near-zero latency. The files are small and arrive in bursts of hundreds per minute. Which Snowflake feature should the engineer configure?

Accepted Answer

Snowpipe with AUTO_INGEST = TRUE on the pipe, using a storage integration and an S3 event notification to an SQS queue.. Snowpipe with AUTO_INGEST is the only option that reacts to new S3 objects as they arrive by consuming SQS event notifications. It loads files individually with low latency using serverless compute, which suits high-frequency, small-file workloads. Scheduled tasks, streams, and external tables do not deliver the required event-driven ingestion into a table.

Answer

A scheduled COPY INTO statement executed every minute by a Snowflake TASK.

Answer

A Snowflake STREAM on the target table combined with a TASK to poll the S3 bucket.

Answer

An external table over the S3 stage with automatic refresh using METADATA$FILENAME.

Question 11

A data engineer needs to join a large fact table to a small dimension table that changes slowly. The dimension has a `valid_from` and `valid_to` timestamp, and the fact rows have an `event_ts`. The engineer wants to avoid scanning the entire dimension for every fact row and must ensure the join uses the correct validity window. Which approach is most appropriate?

Accepted Answer

Use an ASOF JOIN with `MATCH_CONDITION (event_ts >= valid_from)` and equality on the dimension key.. ASOF JOIN is purpose-built for joining time-series data to slowly changing dimensions by matching the closest preceding record. It uses MATCH_CONDITION to compare timestamps and equality predicates for the key, avoiding full scans and Cartesian products. The other options either ignore historical validity or rely on inefficient join types that do not scale for large fact tables.

Answer

Use a LEFT OUTER JOIN on the dimension key and filter with `valid_to IS NULL` to get the current record.

Answer

Use a non-equi join with `event_ts BETWEEN valid_from AND valid_to` and rely on the optimizer to prune partitions.

Answer

Use a CROSS JOIN with a WHERE clause that filters on the dimension key and timestamp range.

Question 12

A data engineer is configuring a Snowpipe to continuously load new files from an external stage backed by Google Cloud Storage. The pipeline must ingest files within seconds of their arrival. The engineer notices that files are not being loaded and that no errors appear in the pipe's copy history. The pipe was created with AUTO_INGEST = TRUE, and the notification channel is configured. Which Snowflake feature should the engineer verify to ensure that Snowpipe receives event notifications from GCS?

Accepted Answer

The GCS bucket has a Pub/Sub subscription that forwards messages to Snowflake's notification endpoint.. For Snowpipe auto-ingest from Google Cloud Storage, Snowflake does not poll the bucket. Instead, it relies on Google Cloud Pub/Sub to publish event notifications when new objects arrive. A subscription must be created that pushes these messages to the Snowflake-provided notification endpoint. If that subscription is missing, misconfigured, or lacks permissions, Snowflake never receives the event, and the pipe remains idle without errors. Verifying the Pub/Sub subscription is therefore the correct diagnostic step.

Answer

The pipe's FILE_FORMAT is set to skip header rows and handle compressed files.

Answer

The external stage URL uses the gcs:// scheme and specifies the correct bucket and folder path.

Answer

The storage integration's STORAGE_ALLOWED_LOCATIONS parameter includes the GCS bucket path.

Question 13

A data engineer needs to ensure that only users with the role FINANCE_ANALYST can view the SALARY column in the EMPLOYEES table. All other users should see a masked value. Which Snowflake feature should the engineer use?

Accepted Answer

Masking Policy. A masking policy is the correct feature for column-level security. It allows conditional masking based on the user's role, ensuring that only FINANCE_ANALYST sees the actual salary. Row access policies filter rows, secure views can be bypassed, and tags are for metadata. Thus, a masking policy is the right choice.

Answer

Row Access Policy

Answer

Object Tag

Answer

Secure View

Question 14

A financial services company stores transaction records in a Snowflake table that includes a column named 'SSN'. The data engineering team has been asked to implement a governance control that automatically detects and tags any column containing Social Security Numbers across the entire account, without manually inspecting every table. Which Snowflake feature should the team use to achieve this requirement?

Accepted Answer

Data Classification with a custom classification profile. Data Classification is designed to automatically scan and classify columns based on sensitive data patterns. By using a custom classification profile, the team can ensure SSNs are detected and tagged with system tags across the account, meeting the governance requirement without manual effort.

Answer

Object tagging with manual tag assignments

Answer

Access History view in ACCOUNT_USAGE

Answer

Dynamic Data Masking with a masking policy

Question 15

A data engineer runs a query on a Snowflake virtual warehouse that scans a 5 TB table but returns only 100 rows. The Query Profile shows that the TableScan operator processed 5 TB of data even though a highly selective filter on a non-clustered column was applied. The warehouse is sized Medium. Which action will most effectively reduce the amount of data scanned by this query?

Accepted Answer

Add a clustering key on the filtered column and enable automatic clustering.. The TableScan reads 5 TB because the filter column is not clustered, so micro-partition pruning cannot eliminate irrelevant data. Adding a clustering key on the filtered column, with automatic clustering enabled, reorganizes micro-partitions so the optimizer can skip most of them. This reduces bytes scanned and directly targets the bottleneck shown in the Query Profile, rather than adding compute or duplicating data.

Answer

Create a materialized view that selects all columns from the table.

Answer

Increase the warehouse size from Medium to 2X-Large.

Answer

Add a search optimization service to the table.

Question 16

A data engineer is using Snowflake's Time Travel feature to recover a table that was dropped 2 days ago. The table was a permanent table with DATA_RETENTION_TIME_IN_DAYS set to 1. The engineer attempts to use UNDROP TABLE but receives an error. What is the most likely reason for the failure?

Accepted Answer

The table's Time Travel retention period has expired, and the table is now in Fail-safe.. A permanent table with 1-day Time Travel retention is recoverable via UNDROP only within that 1-day window. After 2 days, the table has moved to Fail-safe, which does not support UNDROP. Therefore, the UNDROP fails because the Time Travel period has expired. Other options like transient table type, privileges, or name reuse are not applicable in this scenario.

Answer

The user lacks the necessary privileges to perform UNDROP on the table.

Answer

The table name was reused by another table, so UNDROP cannot restore the original.

Answer

The table was created as a transient table, so UNDROP is not supported.

Question 17

A data engineer is setting up a Snowpipe to automatically ingest data from an external stage (Google Cloud Storage) into a Snowflake table. The engineer wants to minimize latency and ensure that files are loaded as soon as they are available. Which Snowpipe configuration should be used?

Accepted Answer

Create a pipe with AUTO_INGEST = TRUE and configure a cloud notification integration for Google Cloud Pub/Sub.. For automatic, low-latency ingestion from Google Cloud Storage, Snowpipe must be configured with AUTO_INGEST = TRUE and a notification integration that uses Google Cloud Pub/Sub. This allows Snowflake to receive event notifications when new files arrive, triggering immediate ingestion. Storage integrations alone do not provide event notifications, and polling introduces delay and unnecessary compute.

Answer

Create a pipe with AUTO_INGEST = TRUE and rely on Snowflake's internal polling mechanism to detect new files.

Answer

Create a pipe with AUTO_INGEST = TRUE and configure the stage to use a storage integration with Google Cloud Storage.

Answer

Create a pipe with AUTO_INGEST = FALSE and schedule it to run every minute using a task.

Question 18

A data engineer is implementing a data governance strategy and needs to ensure that all tables containing sensitive data are automatically identified and tagged. The engineer wants to use Snowflake's native classification capabilities and then apply masking policies based on those tags. Which sequence of steps should the engineer follow?

Accepted Answer

Run Data Classification, review results, then create and attach masking policies to tagged columns.. Data Classification is the native feature that automatically scans and tags sensitive columns. Once tagged, the engineer can review the tags and then create masking policies attached to those columns. This ensures that policies are applied to the correct columns without manual discovery.

Answer

Manually apply tags, then create masking policies that reference those tags.

Answer

Create masking policies first, then run Data Classification to tag columns.

Answer

Use Access History to identify sensitive columns, then manually tag them.

Question 19

A data engineer is using Snowpipe to load data from an external stage. The pipe is configured with AUTO_INGEST = TRUE. Which two statements are true regarding the behavior of Snowpipe in this configuration? (Choose two.)

Accepted Answer

Snowpipe relies on cloud storage event notifications to trigger the load process.. Snowpipe with AUTO_INGEST uses cloud storage event notifications to trigger loads and maintains a load history to avoid duplicates. It does not poll the stage, does not auto-create integrations, and is not compatible with internal stages.

Answer

Snowpipe periodically polls the external stage for new files based on a defined schedule.

Answer

AUTO_INGEST = TRUE requires the stage to be an internal stage.

Answer

Snowpipe automatically creates a notification integration if one does not exist.

Question 20

A data engineer is optimizing a Snowflake environment for a data warehouse that experiences high concurrency during business hours. The engineer observes that many queries are small and frequent, and the warehouse is often queued. The engineer wants to reduce queueing and improve throughput without increasing cost significantly. Which two actions should the engineer take? (Choose two.)

Accepted Answer

Implement a separate warehouse for small, frequent queries and another for large, long-running queries.. High concurrency with many small queries is best addressed by increasing parallelism through multi-cluster warehouses and by isolating workloads to prevent resource contention. Multi-cluster warehouses dynamically add clusters to handle queued queries, while workload separation ensures that small queries are not blocked by large ones. Scaling up a warehouse or tweaking timeout parameters does not increase concurrency, and result caching is ineffective for unique queries. Therefore, the two correct actions are enabling multi-cluster and separating workloads.

Answer

Increase the warehouse size from Medium to Large.

Answer

Set the STATEMENT_QUEUED_TIMEOUT_IN_SECONDS parameter to a low value.

Answer

Enable the 'USE_CACHED_RESULT' parameter and increase the result cache size.