DEA-C02 Practice Test 9 — 10 Questions

Question 1

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?

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

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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 10

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.