DEA-C02 Hard Questions — 25 Difficult Questions

Question 1

Refer to the exhibit. A security administrator executes this query to audit access to a sensitive table. What specific information is captured in the 'base_objects_accessed' column regarding the data lineage of this query?

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

Accepted Answer

It identifies the underlying tables that provided the data, even if the user queried a view.. The ACCESS_HISTORY view is a powerful tool for tracking data movement and consumption. The 'base_objects_accessed' field specifically records the underlying source tables (the 'base' objects) that provided the data, even if the user queried a view or a series of nested views. This is vital for accurate compliance reporting and understanding the ultimate source of truth.

Answer

It lists only the immediate view name that the user referenced in their SELECT statement.

Answer

It displays the metadata of the warehouse used to execute the query for the table.

Answer

It identifies the specific columns and rows that were filtered out by the query optimizer.

Question 2

A data engineer wants to share a subset of data with a third party while ensuring sensitive columns are masked. Which governance combination is best?

Accepted Answer

Create a secure view and apply masking policies to the base table columns.. The best practice is to combine a Secure View with a Dynamic Data Masking policy. The Secure View provides a clean abstraction layer, while the Masking Policy ensures that the data itself remains protected regardless of how the view is queried. This combination allows for precise data sharing while adhering to strict compliance standards, protecting the organization from data leaks during the sharing process.

Answer

Create a standard view with CASE statements for masking.

Answer

Grant direct access to the base table.

Answer

Physically clone the table and remove sensitive rows.

Question 3

When unloading data from Snowflake to an external stage, which TWO of the following are supported file formats?

Accepted Answer

CSV. Unloading data is just as important as loading. Snowflake supports CSV, JSON, and Parquet for unloading, providing flexibility for downstream systems. Understanding which formats are natively supported allows engineers to choose the right format for compatibility with external tools (like BI platforms or data lakes) without needing additional transformation steps, making the data movement process more efficient and standardized.

Answer

XLSX

Answer

SQL

Answer

PDF

Question 4

A data engineer is building a transformation pipeline that must replace sensitive values in a VARCHAR column with a consistent pseudonym across multiple tables. The pseudonym must be deterministic for the same input value and must not be reversible without a secret. Which Snowflake function should be used to generate the pseudonym?

Accepted Answer

SHA2 with a salt concatenated to the input value. SHA2 with a salt provides a deterministic, one-way transformation suitable for pseudonymization. The salt ensures that identical input values yield identical pseudonyms, enabling joins across tables, while the cryptographic hash makes reversal infeasible without the salt. Other options either lack determinism, are reversible, or are not designed for deriving pseudonyms from input values.

Answer

MD5 with a randomly generated initialization vector

Answer

ENCRYPT with a symmetric key and the 'AES-256' algorithm

Answer

RANDOM with a fixed seed value for reproducibility

Question 5

A data engineer is building a transformation pipeline that must handle late-arriving data. The source table has a column 'event_time' of type TIMESTAMP_NTZ. The engineer needs to create a new column 'event_date' that reflects the date in the 'America/New_York' timezone, and also a column 'event_hour' that represents the hour of day in that timezone. Which Snowflake expression correctly produces both columns?

Accepted Answer

CONVERT_TIMEZONE('America/New_York', event_time)::DATE AS event_date, EXTRACT(HOUR FROM CONVERT_TIMEZONE('America/New_York', event_time)) AS event_hour. The two-argument CONVERT_TIMEZONE interprets the input timestamp as UTC and converts it to the specified target timezone. This is the correct approach when the stored TIMESTAMP_NTZ represents UTC. Casting to DATE yields the local date, and EXTRACT(HOUR ...) yields the local hour. Using three arguments with 'UTC' as source is redundant, while reversing the arguments converts in the wrong direction. Omitting conversion entirely fails the timezone requirement.

Answer

CONVERT_TIMEZONE('UTC', 'America/New_York', event_time)::DATE AS event_date, EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', event_time)) AS event_hour

Answer

CONVERT_TIMEZONE('America/New_York', 'UTC', event_time)::DATE AS event_date, EXTRACT(HOUR FROM CONVERT_TIMEZONE('America/New_York', 'UTC', event_time)) AS event_hour

Answer

DATE_TRUNC('DAY', event_time)::DATE AS event_date, EXTRACT(HOUR FROM event_time) AS event_hour

Question 6

A data engineer is transforming a large fact table that contains billions of rows. The transformation requires calculating a moving average over a 7-day window for each product, and the result must be stored in a new table. The engineer wants to minimize the amount of data processed and avoid repeated scans of the base table. Which approach is most efficient in Snowflake?

Accepted Answer

Use a single INSERT INTO ... SELECT statement with a window function that computes the moving average and writes directly to the target table.. The most efficient method is to perform the transformation in a single SQL statement using a window function. This minimizes data movement and leverages Snowflake's parallel processing. Other approaches introduce extra steps, data movement, or limitations that make them less efficient for large-scale data.

Answer

Create a temporary table with the base data, then run a separate UPDATE statement to compute the moving average and store it in a new column.

Answer

Use a Snowpark Python stored procedure that reads the data into a DataFrame, computes the moving average using pandas, and writes the result back to Snowflake.

Answer

Create a materialized view that includes the moving average calculation, then query the materialized view to populate the target table.

Question 7

A data engineer needs to transform a large dataset by applying a complex JavaScript user-defined function (UDF) to each row. The UDF is computationally expensive and the dataset is several terabytes. The engineer wants to minimize cost and execution time. Which Snowflake feature should be used to process the data efficiently?

Accepted Answer

Use a vectorized Python UDF (via the Snowpark API) on a Snowpark-optimized warehouse.. Vectorized Python UDFs allow processing data in batches, which is much faster than row-by-row processing for large datasets. When paired with a Snowpark-optimized warehouse, which offers higher memory and compute per node, the transformation can be executed efficiently, reducing both time and cost. This is the recommended approach for complex, expensive UDFs on large data volumes.

Answer

Use a JavaScript UDF within a SELECT statement on a virtual warehouse.

Answer

Use a Java UDF with vectorized execution on a standard warehouse.

Answer

Use a Snowpark Python user-defined function (UDF) executed on a Snowpark-optimized warehouse.

Question 8

A data engineer is building a transformation pipeline that must process streaming data from a Snowpipe. The pipeline needs to apply a series of transformations, including filtering, joining with a dimension table, and aggregating results. The engineer wants to minimize latency and cost. Which Snowflake feature is most appropriate for this scenario?

Accepted Answer

Use Snowpipe with a transformation function to apply filtering and aggregation during ingestion.. Snowpipe's transformation capabilities allow filtering and aggregation during ingestion, which is ideal for streaming data pipelines requiring low latency. This approach eliminates the need for separate transformation steps, reducing latency and cost. Other options introduce delays or are not designed for streaming data, making them unsuitable for this scenario.

Answer

Create a stream on the target table and use a task with a WHEN clause to process only new data.

Answer

Use a task that runs every minute to transform the data using a stored procedure.

Answer

Create a materialized view that joins the stream with the dimension table and pre-aggregates the results.

Question 9

A data engineer needs to transform a table by replacing NULL values in a numeric column with the average of the non-NULL values from the same column, partitioned by a category column. The engineer wants to achieve this in a single SQL statement without using a subquery. Which Snowflake function should be used?

Accepted Answer

COALESCE(column, AVG(column) OVER (PARTITION BY category)). The COALESCE function is designed to return the first non-NULL value from its arguments. When combined with a windowed AVG partitioned by category, it replaces NULLs in the column with the category-specific average. This achieves the transformation in a single SQL statement without subqueries. Other functions like NULLIF or ARRAY_TO_STRING do not provide this behavior.

Answer

IFNULL(column, AVG(column) OVER (PARTITION BY category))

Answer

NVL(column, AVG(column) OVER (PARTITION BY category))

Answer

NULLIF(column, AVG(column) OVER (PARTITION BY category))

Question 10

A data engineer is optimizing a query that performs a large aggregation over a clustered table. The Query Profile shows that the aggregation step is spilling to local disk. The warehouse size is currently Medium. The engineer wants to reduce spilling without increasing warehouse size. Which action is most likely to improve performance?

Accepted Answer

Rewrite the query to use a temporary table to pre-aggregate the data before the final aggregation.. Pre-aggregating data into a temporary table reduces the number of rows that the final aggregation must process, which lowers memory pressure and prevents spilling. This technique allows the query to be executed in stages, each fitting within the available memory of the current warehouse. It is an effective way to optimize without increasing warehouse size.

Answer

Add a clustering key on the group-by columns to improve data pruning and reduce the number of rows processed.

Answer

Enable the USE_CACHED_RESULT parameter to reuse previous aggregation results.

Answer

Increase the warehouse size to Large to provide more memory for the aggregation.

Question 11

A data engineer is designing a transformation that uses the PIVOT operator to convert rows into columns. The source table 'sales' has columns 'product', 'month', and 'revenue'. The engineer wants to pivot on 'month' and aggregate 'revenue' using SUM. Which TWO statements are true regarding the behavior of the PIVOT operator in Snowflake? (Choose two.)

Accepted Answer

The PIVOT operator automatically excludes the column used for aggregation from the output.. PIVOT in Snowflake consumes the aggregated value column, so it does not appear in the output. It also allows a dynamic list of pivot values via a subquery in the IN clause, enabling flexible transformations. Multiple aggregates are supported, and pivot columns can be non-numeric. The generated column names are derived from the pivot values and aggregates, not the original names.

Answer

The PIVOT operator can only be used with a single aggregate function per pivot.

Answer

The PIVOT operator preserves the original column names for the pivoted columns without any modification.

Answer

The PIVOT operator can only pivot on a numeric column.

Question 12

A data engineer is designing a transformation pipeline that uses a Snowflake stream on a table to capture changes. The stream will feed a task that merges changes into a target table. Which TWO statements about stream consumption and transformation are correct? (Choose two.)

Accepted Answer

The METADATA$ACTION column in a stream indicates whether the row was inserted or deleted.. Streams provide metadata columns that describe each change: METADATA$ACTION distinguishes inserts from deletes, and METADATA$ISUPDATE flags rows that are part of an update. These columns are essential for building correct merge transformations. The other statements misrepresent stream concurrency, view support, and retention behavior, making them incorrect for this pipeline design.

Answer

A stream can be consumed by multiple concurrent transactions without advancing the offset until each transaction commits.

Answer

A stream automatically purges change data after a fixed retention period regardless of consumption.

Answer

A stream can be created on a view that joins multiple tables, and it will capture changes from all underlying tables.

Question 13

A data engineer is analyzing a Query Profile for a query that joins two large tables. The profile shows a significant amount of time spent in the 'Remote Disk Spilling' step. The warehouse is a 2XL. The engineer wants to reduce remote spilling without increasing warehouse size. Which optimization is most likely to be effective?

Accepted Answer

Repartition the data by using a different join key that distributes data more evenly.. Remote spilling in a join typically indicates data skew, where certain join keys are overrepresented, causing some nodes to handle disproportionate data and spill to remote disk. Repartitioning the data by using a more evenly distributed join key or applying salting techniques can balance the workload across nodes, reducing memory pressure and eliminating remote spilling. This optimization targets the underlying cause without requiring a larger warehouse.

Answer

Rewrite the query to filter each table before the join to reduce the number of rows processed.

Answer

Add a clustering key on the join columns of both tables.

Answer

Use a larger warehouse size to increase memory for the join.

Question 14

A data engineer notices that a recurring batch query joining a 2 TB fact table with a 200 GB dimension table consistently spills to local disk. The Query Profile shows a HashJoin operator with a large number of bytes spilled. The warehouse is an X-Large. The join key on the dimension table is not the clustering key. Which change is most likely to eliminate the local spilling while keeping credit usage reasonable?

Accepted Answer

Add a clustering key on the dimension table's join column.. The HashJoin spills because the build side, likely the 200 GB dimension table, does not fit in the warehouse memory. Clustering the dimension table on the join column reduces the build-side data volume by enabling micro-partition pruning when the query filters on that column, and can improve join performance. This targets the memory pressure without scaling up the warehouse, keeping credit usage reasonable.

Answer

Disable the query acceleration service for the warehouse.

Answer

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

Answer

Change the warehouse to a Snowpark-optimized warehouse.

Question 15

A data engineer is optimizing a Snowflake environment where multiple users run concurrent queries on the same large table. The table is frequently filtered on a high-cardinality column, and queries often scan large portions of the table. The engineer wants to reduce the amount of data scanned and improve overall concurrency. Which two actions should the engineer take? (Choose two.)

Accepted Answer

Add a clustering key on the high-cardinality column.. To reduce data scanned, adding a clustering key on the high-cardinality column allows micro-partition pruning, which is highly effective for filters on that column. To improve concurrency, using a multi-cluster warehouse enables multiple clusters to handle simultaneous queries, reducing queueing. Materialized views and search optimization have specific use cases but do not address broad filtering on high-cardinality columns or concurrency as directly. Increasing warehouse size alone does not improve concurrency as effectively as multi-cluster.

Answer

Increase the warehouse size to improve concurrency.

Answer

Create a materialized view that pre-aggregates the data by the high-cardinality column.

Answer

Enable the search optimization service on the high-cardinality column.

Question 16

A data engineer is tuning a query that joins a 10-billion-row fact table to a small 5,000-row dimension table. The Query Profile shows a broadcast join with high network transfer and long execution time. The dimension table has a reliable primary key and is updated only once per day. Which approach best reduces the join cost?

Accepted Answer

Add a clustering key on the fact table's join column and filter columns to improve partition pruning before the join.. When a broadcast join is dominated by the probe side, the effective remedy is to shrink the probe side before the join. Clustering the fact table on join and filter columns enables pruning so fewer micro-partitions are read and fewer rows are broadcast-joined, cutting network transfer and runtime.

Answer

Rewrite the query so the small dimension is the build side and rely on the optimizer to choose a more efficient join distribution.

Answer

Convert the dimension table into a materialized view and join against the view instead.

Answer

Increase the warehouse size to add more nodes so the broadcast join finishes faster.

Question 17

A data engineer notices that a query joining two large tables is performing a remote disk spill. The Query Profile shows that the build side of the join is much larger than the probe side. The engineer wants to reduce remote spilling without increasing warehouse size. Which action is most appropriate?

Accepted Answer

Ensure the smaller table is used as the build side by rewriting the join order or using a subquery to filter the larger table first.. Remote spilling happens when the build side of a join is too large to fit in a node's memory and must be repartitioned over the network. Making the smaller table the build side keeps the hash table in memory. Filtering the larger table before the join further reduces the probe-side data, lowering both memory and network pressure, which eliminates remote spilling without scaling the warehouse.

Answer

Add a clustering key on the join column of the larger table to improve join pruning and reduce data movement.

Answer

Rewrite the query to use a UNION ALL instead of a join to avoid the build side entirely.

Answer

Disable the join spill-to-disk feature by setting the ENABLE_JOIN_SPILL parameter to FALSE.

Question 18

A data engineer is optimizing a query that joins a large fact table 'sales' with a small dimension table 'products'. The query filters on 'products.category' and aggregates sales amounts. The engineer notices that the join is a broadcast join in the query profile. Which action would most likely improve performance by enabling a more efficient join strategy?

Accepted Answer

Ensure the 'products' table is small enough to be broadcast, and if not, consider filtering it before the join to reduce its size.. Filtering the smaller dimension table before the join reduces the amount of data that needs to be broadcast or shuffled, which can make the broadcast join more efficient or enable a more optimal join strategy. This is a common optimization when a dimension table is larger than expected or when the filter is highly selective. Reducing data early in the query plan minimizes compute and I/O.

Answer

Add a clustering key on the 'sales' table on the join key 'product_id'.

Answer

Add a search optimization service to the 'sales' table to speed up point lookups on 'product_id'.

Answer

Rewrite the query to use a LEFT JOIN instead of an INNER JOIN to reduce the amount of data processed.

Question 19

A data engineer is investigating a slow query that joins a 5 billion row fact table to a 200 million row dimension table. The Query Profile shows a single join node with a very high percentage of time in 'Bytes spilled to remote storage' and a high cardinality on the join key. The fact table is clustered by a different column than the join key. Which optimization is most likely to reduce remote spilling for this join?

Accepted Answer

Add a clustering key on the fact table's join key column to align micro-partitions with the join.. Remote spilling in a large join often results from data being repartitioned across nodes because the join key does not align with the table's clustering. By clustering the fact table on the join key, micro-partition ranges align with the join predicate, allowing the optimizer to prune partitions and co-locate matching rows more effectively. This reduces the volume of data shuffled and spilled during the join, improving performance without merely adding compute.

Answer

Disable the join by rewriting the query to use a correlated subquery instead.

Answer

Increase the warehouse size to add more compute nodes and memory.

Answer

Set the JOIN_MEMORY_LIMIT parameter to allow more memory for the join operator.

Question 20

A data engineer is optimizing a Snowflake environment where several long-running queries frequently spill to remote storage. The engineer has already confirmed that the queries cannot be rewritten. Which TWO actions should the engineer take to reduce remote spilling and improve performance? (Choose two.)

Accepted Answer

Increase the size of the warehouse used for these queries to provide more memory per node.. Remote spilling occurs when operators exceed memory and local disk capacity. Increasing warehouse size provides more memory per node and more nodes, allowing joins and aggregations to keep their working sets in memory. Clustering on frequently joined or filtered columns reduces the volume of data that must be repartitioned and processed, lowering memory pressure. Together, these actions address both the resource and data-distribution causes of remote spilling.

Answer

Reduce the warehouse size to decrease the number of nodes competing for memory.

Answer

Disable the query result cache to free memory for spilling operations.

Answer

Set the ENABLE_REMOTE_SPILLING parameter to FALSE to force local spilling only.