DEA-C02 Practice Test 14 — 15 Questions

Question 1

A data engineer is reviewing a Query Profile for a query that filters a large table on a column with a very high cardinality, such as a UUID. The query applies an equality predicate on that column and returns a single row. The table is not clustered on that column. Which Snowflake feature is designed to accelerate this type of highly selective point lookup?

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

Accepted Answer

Search optimization service. Search optimization service is designed to accelerate highly selective point lookups and equality predicates, such as a UUID equality filter returning a single row. It maintains a search access path that lets the optimizer locate matching micro-partitions without scanning the entire table. Clustering, query acceleration, and materialized views serve different purposes and do not target point lookups as directly.

Answer

Automatic clustering

Answer

Materialized view with a cluster by clause

Answer

Query acceleration service

Question 2

A data engineer needs to categorize columns across multiple databases with custom business tags such as COST_CENTER and DATA_OWNER, and then enforce that only users with the TAG_ADMIN role can modify those tags. Which Snowflake feature should the engineer use to meet this requirement?

Accepted Answer

Object tagging with a tag created via CREATE TAG, assigning tags to columns with ALTER TABLE ... SET TAG, and granting the APPLY TAG privilege only to TAG_ADMIN.. Object tagging is Snowflake's mechanism for attaching custom metadata labels to columns and other objects. Tags are created with CREATE TAG, applied with ALTER TABLE ... SET TAG, and their modification is governed by the APPLY TAG privilege on the tag itself. Granting APPLY TAG only to TAG_ADMIN restricts who can change the tags, satisfying both categorization and access-control needs.

Answer

A masking policy that returns the tag value for authorized roles and NULL for others.

Answer

Snowflake Data Classification, which automatically detects and tags columns with system tags.

Answer

Access control policies created with CREATE ACCESS POLICY and bound to the target columns.

Question 3

A retail company has a Snowflake account with many databases and schemas. The data governance team needs to discover all columns that contain personal data such as names, email addresses, and phone numbers, and automatically assign a system tag so that a masking policy can be applied later. They want to minimize manual effort and ensure the classification is consistent. Which Snowflake feature should they use to achieve this?

Accepted Answer

Use Snowflake Data Classification to automatically scan and tag columns with system tags like SNOWFLAKE.CORE.PRIVACY_CATEGORY.. Snowflake Data Classification is designed to automatically scan and classify data, applying system tags that indicate privacy categories. This reduces manual effort and ensures consistent tagging across the account. The other options either require manual work, rely on incomplete methods, or use auditing features that do not detect sensitive data content. Thus, Data Classification is the correct choice.

Answer

Manually run SHOW COLUMNS in each database and schema, then apply tags using ALTER TABLE ... SET TAG.

Answer

Enable Access History and then use the ACCESS_HISTORY view to identify columns containing personal data.

Answer

Create a stored procedure that queries INFORMATION_SCHEMA.COLUMNS and uses pattern matching to assign tags.

Question 4

A data engineer runs a dashboard query that aggregates sales by region for the current month. The query scans a large fact table but returns only a few rows. The Query Profile shows that most time is spent scanning micro-partitions that do not contain the current month's data. Which feature should the engineer use to improve performance for this recurring query?

Accepted Answer

Create a materialized view that pre-aggregates sales by region and month.. The recurring dashboard query aggregates a large fact table but returns few rows, and the profile shows scanning of unnecessary micro-partitions. A materialized view pre-aggregates sales by region and month, so the query reads a compact result instead of the base table. Snowflake maintains the view automatically and can transparently rewrite the query to use it, cutting scan time and cost for this repetitive pattern.

Answer

Enable the USE_CACHED_RESULT session parameter for the dashboard user.

Answer

Increase the warehouse size to a larger multi-cluster warehouse.

Answer

Add a search optimization service to the fact table on the region column.

Question 5

A data engineer is tasked with implementing a data governance strategy that includes classifying sensitive data and applying tags. The engineer plans to use Snowflake's Data Classification and tag-based masking. Which two statements are true regarding the interaction between Data Classification and tags? (Choose two.)

Accepted Answer

Tag-based masking policies can be applied to system tags created by Data Classification.. Data Classification automatically assigns system tags to columns based on its analysis, and these system tags can have masking policies attached to them. This integration allows for automatic masking of sensitive data identified by classification. Manual tag creation is not required, and system tags are indeed used and can support masking.

Answer

Tag-based masking policies cannot be applied to system tags; only user-defined tags support masking.

Answer

Data Classification can only tag columns with user-defined tags, not system tags.

Answer

Data Classification requires that all tags be manually created before classification can run.

Question 6

A data engineer needs to move a large volume of archived data from an on-premises system into a Snowflake internal stage and then load it into a permanent table. The data must be protected against accidental deletion for at least 30 days. The account is Snowflake Enterprise Edition. Which configuration best meets these requirements?

Accepted Answer

Create a permanent table and set DATA_RETENTION_TIME_IN_DAYS to 30.. A permanent table on Enterprise Edition can be configured with up to 90 days of Time Travel, so setting 30 days meets the protection requirement directly. Transient and temporary tables cap retention at 1 day, and relying on Fail-safe alone yields only a fixed 7-day window that requires Support intervention. Only the permanent table with 30-day retention satisfies the requirement.

Answer

Create a transient table and set DATA_RETENTION_TIME_IN_DAYS to 30.

Answer

Create a permanent table and rely on the default 1-day retention, since Fail-safe will cover the remaining 29 days.

Answer

Create a temporary table and set DATA_RETENTION_TIME_IN_DAYS to 30.

Question 7

A data engineer must transform semi-structured event data stored in a VARIANT column named 'event_payload'. The payload contains an array under the key 'tags', and each element of the array is an object with keys 'name' and 'score'. The engineer needs to produce one row per tag element, preserving the original event ID and extracting the 'name' and 'score' values. Which Snowflake construct should be used to achieve this transformation?

Accepted Answer

Use the FLATTEN function in the FROM clause with the INPUT argument set to event_payload:tags.. Flattening a semi-structured array into rows is a core transformation in Snowflake. The FLATTEN table function directly expands array elements, producing one row per element and enabling easy extraction of nested fields. It preserves the parent row context, so the event ID remains available. Other functions like PARSE_JSON or SPLIT_TO_TABLE do not achieve the same row expansion for VARIANT arrays.

Answer

Use the SPLIT_TO_TABLE function with a delimiter of comma on the string representation of the tags array.

Answer

Use the PARSE_JSON function on event_payload:tags and then apply a lateral join with a VALUES clause.

Answer

Use the GET_PATH function to extract the array, then use a recursive CTE to iterate over its elements.

Question 8

A data engineer runs a nightly transformation that joins a 12 TB fact table to a 300 GB dimension table. The fact table is clustered by DATE_KEY, and the dimension is small enough to fit in memory. Query Profile shows the join operator building a hash table on the 12 TB side and spilling to remote disk. The engineer wants to eliminate the remote spill without increasing warehouse size. Which action should the engineer take?

Accepted Answer

Rewrite the join so the 300 GB dimension table is the build side and the 12 TB fact table is the probe side.. Hash join performance depends on which input is used to build the in-memory hash table. Building on the smaller dimension and probing with the large fact table keeps the hash table resident in memory, eliminating remote disk spilling. Clustering, resizing, or materializing the join do not correct the build-side selection, so they leave the root cause of the spill unaddressed.

Answer

Increase the warehouse size by one step so the join operator receives more memory per node.

Answer

Add a clustering key on the 12 TB fact table using the dimension's primary key column.

Answer

Convert the 12 TB fact table to a materialized view joined with the dimension table and refresh it nightly.

Question 9

A data engineer is reviewing storage costs in a Snowflake account. The engineer notices that a permanent table named CUSTOMER_DIM has a DATA_RETENTION_TIME_IN_DAYS value of 90, but the account is on Standard Edition. The engineer wants to confirm whether this is valid. What is the maximum Time Travel retention period for a permanent table in a Standard Edition account?

Accepted Answer

1 day. On Standard Edition, permanent tables support a maximum Time Travel retention of 1 day, with 0 disabling Time Travel entirely. Longer retention periods such as 7 or 90 days require Enterprise Edition or higher. A 90-day setting in a Standard Edition account is therefore invalid, and the engineer should expect the maximum to be 1 day.

Answer

90 days

Answer

0 days

Answer

7 days

Question 10

A data engineer is managing a Snowflake account where a transient table named STG_ORDERS is created in a schema. The table holds intermediate ETL results. The engineer wants to ensure that if the table is accidentally dropped, it can be recovered within 24 hours without incurring Fail-safe storage costs. What should the engineer do?

Accepted Answer

Set DATA_RETENTION_TIME_IN_DAYS to 1 on the transient table.. Transient tables support Time Travel up to 1 day and have no Fail-safe period, so setting DATA_RETENTION_TIME_IN_DAYS to 1 provides 24-hour recovery without Fail-safe costs. Permanent tables have a mandatory 7-day Fail-safe period, which would incur costs. Disabling Time Travel eliminates recovery, and cloning does not address recovery of the original table.

Answer

Create a zero-copy clone of the transient table and set DATA_RETENTION_TIME_IN_DAYS to 1 on the clone.

Answer

Set DATA_RETENTION_TIME_IN_DAYS to 0 on the transient table and rely on Fail-safe for recovery.

Answer

Convert the table to a permanent table and set DATA_RETENTION_TIME_IN_DAYS to 1.

Question 11

A data engineer needs to ensure that a column containing credit card numbers is masked for all users except those with the PAYMENT_ADMIN role. The masking should be applied consistently across all tables that use a specific tag. Which Snowflake feature should the engineer use?

Accepted Answer

A tag-based masking policy that is associated with a tag and automatically applies to all columns with that tag.. Tag-based masking policies associate a masking policy with a tag, so that any column assigned that tag automatically inherits the masking behavior. This provides consistent, scalable protection across all tables, including future columns. It eliminates the need to manually attach policies to each column, ensuring that credit card numbers are masked for unauthorized users.

Answer

A row access policy that filters rows based on the user's role.

Answer

A standard masking policy attached directly to each column containing credit card numbers.

Answer

A secure view that excludes the credit card column for non-admin users.

Question 12

A data engineer is configuring continuous ingestion of new event files from an external Amazon S3 stage into a Snowflake table. The files arrive frequently and the engineer wants Snowflake to load them automatically without building an external orchestrator. The stage already has a storage integration attached. Which Snowflake object should the engineer create to accomplish this?

Accepted Answer

A pipe with AUTO_INGEST = TRUE. Continuous, event-driven loading from an external stage is implemented with a pipe configured for auto-ingest. The pipe wraps the COPY INTO statement, and the cloud provider's event notification tells Snowflake when new files appear, so no polling or external scheduler is needed. Streams, tasks, and materialized views solve different problems and cannot react to S3 object creation events.

Answer

A stream on the external stage that captures new file names

Answer

A materialized view defined over the external stage

Answer

A task scheduled with a CRON expression that runs COPY INTO every minute

Question 13

A data engineer runs a COPY INTO statement that loads 120 files from an external S3 stage into a target table. The LOAD_UNCERTAIN_FILES option was not specified, and 42 files were already loaded by an earlier run that completed successfully. The engineer expects all 120 files to be reprocessed because the target table was truncated before this run. What will Snowflake actually do, and why?

Accepted Answer

It will skip the 42 previously loaded files and load only the remaining 78, because load metadata is retained independently of table data.. COPY INTO maintains a per-table record of which staged files have already been loaded, and this record survives operations that change or remove table rows. With LOAD_UNCERTAIN_FILES left at its default, only files that have not been successfully loaded into the target are read, so a truncated table does not force reprocessing of files already recorded as loaded.

Answer

It will load all 120 files and produce duplicate rows because the table was emptied after the first load.

Answer

It will load all 120 files because TRUNCATE TABLE removes the table's load metadata along with the rows.

Answer

It will load all 120 files but raise an error for each previously loaded file, marking those rows as rejected.

Question 14

A data engineer maintains a very large fact table that is loaded nightly with the previous day's orders. Analysts run reports that almost always filter on ORDER_DATE and also aggregate by CUSTOMER_ID. The engineer wants search optimization to accelerate point lookups on ORDER_ID and CUSTOMER_ID without adding clustering maintenance overhead. Which configuration best meets these requirements?

Accepted Answer

Enable the search optimization service on the CUSTOMER_ID and ORDER_ID columns.. Search optimization is designed for selective point lookups and equality predicates on specific columns, and it is maintained automatically as new data lands, which matches the nightly-load pattern and the desire to avoid clustering maintenance. Clustering and materialized views address different access patterns, and unique constraints do not create physical access paths.

Answer

Define a clustering key on (ORDER_DATE, CUSTOMER_ID) and let automatic clustering maintain it.

Answer

Add a unique constraint on ORDER_ID and rely on the optimizer to use it for pruning.

Answer

Create a materialized view that pre-aggregates the table by ORDER_DATE and CUSTOMER_ID.

Question 15

A Data Engineer is using the COPY INTO <table_name> command to load Parquet files. The source files contain new columns that do not yet exist in the target Snowflake table. Which TWO features or settings should be used to handle this automatically? (Select TWO)

Accepted Answer

Set the table property ENABLE_SCHEMA_EVOLUTION = TRUE.. Snowflake provides schema evolution capabilities to simplify the ingestion of evolving datasets. By enabling the ENABLE_SCHEMA_EVOLUTION property on the table, Snowflake allows the COPY command to modify the table structure. When combined with MATCH_BY_COLUMN_NAME, Snowflake maps the Parquet fields to table columns and automatically adds any missing columns found in the source files to the target table.

Answer

Use the STRIP_OUTER_ARRAY = TRUE file format option to flatten the Parquet data.

Answer

Set ON_ERROR = 'CONTINUE' to ensure the command doesn't fail when it sees new columns.

Answer

Apply a UDF in the COPY statement to dynamically cast the Parquet schema to the table.