DEA-C02 Practice Test 13 — 15 Questions

Question 1

A data engineer is designing a storage strategy for a Snowflake account. The engineer wants to reduce Fail-safe storage costs and must understand which table types are exempt from Fail-safe. (Choose two.)

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

Accepted Answer

Temporary tables. Fail-safe applies only to permanent tables. Transient and temporary tables are exempt, so they do not generate Fail-safe storage costs. This makes them suitable for data that can be recreated or that only needs short-term protection, while permanent tables continue to carry the 7-day Fail-safe window after Time Travel expires.

Answer

Permanent tables with DATA_RETENTION_TIME_IN_DAYS set to 0

Answer

External tables referencing cloud storage files

Answer

Permanent tables in a database with a short retention default

Question 2

A data engineer is optimizing a query that aggregates a large sales table by product category and region. The query currently uses a GROUP BY on two high-cardinality columns and produces a large intermediate result set. The query profile shows significant network traffic and remote spilling. The engineer wants to reduce remote spilling without changing the aggregation logic. Which approach is most likely to achieve this?

Accepted Answer

Increase the warehouse size to provide more memory per node.. Remote spilling occurs when an operation's intermediate results exceed available memory and are written to remote storage. Increasing the warehouse size provides more memory per node, which can accommodate larger intermediate results and reduce or eliminate remote spilling. While clustering and query acceleration can improve performance in other ways, they do not directly address the memory pressure of the aggregation. Rewriting the query might help but is not as direct or reliable as scaling up.

Answer

Add a clustering key on the product category column.

Answer

Rewrite the query to use a two-step aggregation with a temporary table.

Answer

Enable the query acceleration service on the warehouse.

Question 3

A data engineer is analyzing a Query Profile for a query that joins three large tables. The profile shows that the optimizer chose a broadcast join for one of the joins, but the broadcasted table is 50 GB. The query is spilling to remote disk. Which action is most likely to improve performance by changing the join strategy?

Accepted Answer

Collect statistics on the join columns and ensure the tables are not stale.. The optimizer chose a broadcast join for a 50 GB table, which is inefficient and causes spilling. This often happens when statistics are stale or missing, leading to underestimation of the table size. Collecting statistics on the join columns provides accurate cardinality estimates, enabling the optimizer to choose a better join strategy, such as a hash join with a smaller build side. This directly addresses the root cause.

Answer

Add a clustering key on the join columns of all three tables.

Answer

Increase the warehouse size to 4X-Large.

Answer

Rewrite the query to use a CROSS JOIN instead of an INNER JOIN.

Question 4

A data engineer is configuring a Snowpipe to automatically load Parquet files from an external stage. The files are partitioned by date in the path (e.g., dt=2023-10-01/). The engineer wants to ensure that Snowpipe loads only new files and avoids reprocessing old ones, even if files are added to existing partition paths. The pipe definition includes a PATTERN option. Which approach best ensures that only new files are ingested and that previously loaded files are not reprocessed?

Accepted Answer

Ensure the pipe uses the load history to track processed files; Snowpipe automatically skips files that have already been loaded, even if they appear again.. Snowpipe's built-in load history tracks which files have been loaded for each pipe. When new files are staged, Snowpipe compares against this history and only loads files not previously processed. This ensures that previously loaded files are not reprocessed, even if they are in existing partitions. The other options either rely on non-existent features or require manual intervention that does not guarantee the requirement.

Answer

Use the PATTERN option to match only files with a specific prefix or regex that includes the current date, and update the pipe definition daily.

Answer

Rely on Snowpipe's default behavior, which automatically tracks file modification times and skips files older than the pipe creation time.

Answer

Configure the pipe with a MODIFIED_AFTER parameter set to a timestamp that is updated after each load.

Question 5

A financial services firm must enforce a policy that only users with the role 'COMPLIANCE_OFFICER' can view rows where the 'ACCOUNT_STATUS' column equals 'DELINQUENT' in the 'LOANS' table. All other users should see only non-delinquent rows. Which Snowflake feature should the data engineer implement to meet this requirement?

Accepted Answer

A row access policy on the LOANS table that checks the current role and filters rows accordingly.. Row access policies are the correct Snowflake feature for row-level security. They evaluate conditions such as CURRENT_ROLE() and filter rows accordingly. Masking policies only obfuscate column values, secure views can be bypassed if base table access exists, and network policies operate at the network layer. Only a row access policy attached to the table enforces the required filtering for all queries.

Answer

A network policy that restricts access to the LOANS table based on IP address.

Answer

A secure view that joins the LOANS table with a role-mapping table and filters rows.

Answer

A masking policy on the ACCOUNT_STATUS column that returns NULL for non-compliance roles.

Question 6

An analytics team wants to let external partners query a curated set of rows from a Snowflake table through a secure share. The partners use their own Snowflake accounts and must not be able to see any rows outside the curated set. Which approach should the data engineer use?

Accepted Answer

Create a secure view that filters the base table, grant the share access to the view, and add the view to the share.. Secure views are the supported way to expose a constrained subset of data in a share. Because the view definition is hidden and the base table is not shared, consumers can query only the curated rows. This satisfies both the access requirement and the restriction that partners must not see the full table.

Answer

Grant the share SELECT on the base table and rely on a row access policy to filter rows for the partners.

Answer

Create a materialized view of the curated rows and add it to the share for the partners to query.

Answer

Add the base table to the share and instruct partners to query only the approved rows using their own filters.

Question 7

A data engineer is implementing row access policies to enforce data segregation for a multi-tenant application. The table ORDERS contains a column TENANT_ID. The engineer creates a row access policy that uses a mapping table TENANT_MAPPING to associate users with their allowed TENANT_ID values. After applying the policy, the engineer notices that queries against ORDERS are returning no rows for some users who should have access. The mapping table is correctly populated. What is the most likely cause of the issue?

Accepted Answer

The row access policy uses a mapping table that is not qualified with the database and schema, and the user's session does not have the correct current database and schema set.. Row access policies that reference other tables must use fully qualified names to avoid dependency on session context. If the mapping table is not fully qualified, users with different current database or schema settings may not find the table or may resolve to a different table, resulting in no rows. Fully qualifying the table name ensures that the policy behaves consistently for all users.

Answer

The row access policy is defined with a subquery that returns multiple rows for a given user, causing the policy to evaluate to false.

Answer

The row access policy uses CURRENT_USER() to filter, but the mapping table maps roles to tenants, not users.

Answer

The row access policy is defined with a subquery that references the mapping table, but the policy owner does not have the necessary privileges to access the mapping table.

Question 8

A data engineer is using COPY INTO to load data from an external stage into a table. The source files contain a column with values like '00123' that must be stored as a VARCHAR to preserve leading zeros. The target column is defined as VARCHAR(10). During the load, the engineer notices that the leading zeros are being stripped and the values are stored as '123'. What is the most likely cause?

Accepted Answer

A transformation in the COPY INTO statement is casting the field to a numeric type before loading, removing the leading zeros.. The most likely cause is a transformation in the COPY INTO statement that casts the field to a numeric type. Numeric conversion drops leading zeros, and the subsequent cast to VARCHAR cannot restore them. Ensuring the field is treated as a string throughout the load, or removing the numeric cast, will preserve the original formatting.

Answer

The file format has the PARSE_JSON or similar option that interprets numeric-looking strings as numbers.

Answer

The file format has the STRIP_OUTER_ARRAY option enabled, which removes leading zeros from string values.

Answer

The target column is implicitly cast to a numeric type because the COPY INTO statement does not specify a transformation.

Question 9

A data engineer notices that a dashboard query is running slowly. The query filters a large table on a column with high cardinality and returns a small number of rows. The engineer wants to improve performance for this specific query pattern. Which Snowflake feature is most appropriate?

Accepted Answer

Search optimization service. Search optimization service is the ideal Snowflake feature for accelerating selective point lookups on high-cardinality columns. It creates a persistent search access path that allows the optimizer to efficiently find matching rows, reducing the need to scan entire micro-partitions. This results in faster response times for dashboard queries that filter on specific values and return few rows.

Answer

Query acceleration service

Answer

Materialized view

Answer

Clustering key on the filtered column

Question 10

A data engineer is working with a table that has a VARIANT column containing JSON objects. Some objects have a key 'discount' with a numeric value, while others have it as a string, and some lack the key entirely. The engineer needs to produce a numeric column 'discount_amount' where missing keys are treated as 0 and string values are cast to numbers. Which expression correctly achieves this?

Accepted Answer

COALESCE(TRY_TO_NUMBER(raw:discount), 0). The correct expression uses TRY_TO_NUMBER to safely convert both numeric and string discount values to a number, returning NULL on failure, which COALESCE then replaces with 0. This handles missing keys and invalid strings without causing errors. The other options either use error-prone functions or unnecessary casting steps, making them less reliable for this transformation.

Answer

COALESCE(CAST(raw:discount AS NUMBER), 0)

Answer

COALESCE(TO_NUMBER(raw:discount), 0)

Answer

COALESCE(TRY_TO_NUMBER(raw:discount::STRING), 0)

Question 11

A data engineer needs to transform a string column containing dates in the format 'YYYY-MM-DD' into a DATE type. The column may contain NULL values and occasionally invalid date strings. The engineer wants the transformation to return NULL for invalid strings without causing the query to fail. Which function should the engineer use?

Accepted Answer

TRY_TO_DATE. TRY_TO_DATE is specifically designed to return NULL instead of failing when a string cannot be converted to a date. This makes it the correct choice for transforming a column that may contain invalid date strings. TO_DATE and CAST would raise errors, and DATE is not a function. The engineer should use TRY_TO_DATE to ensure the transformation completes successfully.

Answer

DATE

Answer

TO_DATE

Answer

CAST

Question 12

A data engineer is working with a table that has a column `order_date` of type DATE. The engineer needs to create a new column that contains the year and month in the format 'YYYY-MM' (e.g., '2023-01') for each order. Which Snowflake function should be used to produce this formatted string?

Accepted Answer

TO_CHAR(order_date, 'YYYY-MM'). TO_CHAR with a format string is the correct function to convert a date into a custom string format. It directly produces the 'YYYY-MM' representation, including leading zeros for months. Other options either return a date, invalid syntax, or lack proper formatting.

Answer

DATE_TRUNC('month', order_date)

Answer

CONCAT(YEAR(order_date), '-', MONTH(order_date))

Answer

EXTRACT(YEAR_MONTH FROM order_date)

Question 13

A data engineer is working with a table that stores customer orders in a VARIANT column named order_details. The order_details column contains an array of line items under the key 'items'. Each line item is an object with keys 'product_id', 'quantity', and 'price'. The engineer needs to produce a flattened result set where each row represents a single line item with its associated order ID. Which Snowflake function should be used to achieve this transformation?

Accepted Answer

LATERAL FLATTEN(input => order_details:items). To transform an array of line items into individual rows, the LATERAL FLATTEN function is the correct choice. It takes a VARIANT array and outputs one row per element, allowing each line item to be processed separately. The other functions either aggregate, extract keys, or parse JSON but do not explode arrays into rows.

Answer

OBJECT_KEYS(order_details:items)

Answer

PARSE_JSON(order_details:items)

Answer

ARRAY_TO_STRING(order_details:items, ',')

Question 14

A data steward at a financial services company needs to automatically detect and tag columns containing Social Security numbers across all schemas in the PROD database. The steward wants the tagging to be applied without manually inspecting each table and to leverage Snowflake's built-in classifiers. Which approach should the steward use?

Accepted Answer

Use Snowflake Data Classification with a system-defined SSN semantic category and apply it to the PROD database.. Snowflake Data Classification automatically scans tables and views to identify sensitive data using system-defined semantic categories, including SSN. Applying it to a database scans all contained schemas and tables, tagging columns that match the SSN pattern. This meets the requirement for automatic detection and tagging without manual intervention, leveraging native governance features.

Answer

Enable Snowflake Access History and use it to identify columns that have been queried with SSN patterns.

Answer

Write a stored procedure that queries INFORMATION_SCHEMA.COLUMNS for column names containing 'SSN' and applies a tag.

Answer

Create a custom tag called SSN_TAG and manually apply it to every column that appears to contain SSN data.

Question 15

A data engineer is managing a Snowflake account with Enterprise Edition. A permanent table named FINANCE_RECORDS has DATA_RETENTION_TIME_IN_DAYS set to 10. The engineer wants to reduce storage costs by lowering the retention period to 1 day. What is the effect of executing ALTER TABLE FINANCE_RECORDS SET DATA_RETENTION_TIME_IN_DAYS = 1?

Accepted Answer

The change takes effect immediately, and any historical data older than 1 day becomes inaccessible and will be purged, but storage costs may not decrease immediately.. Reducing the Time Travel retention period immediately makes historical data beyond the new period inaccessible, and it will be purged. However, storage costs may not decrease immediately due to the time required for purging and because Fail-safe still applies for 7 days after the retention period. The change is allowed and takes effect for all data, not just new data.

Answer

The change fails because retention periods can only be increased, not decreased, on a permanent table.

Answer

The change takes effect immediately, and historical data beyond 1 day is purged, reducing storage costs.

Answer

The change takes effect immediately, but existing historical data remains accessible for the original 10-day period.