DEA-C02 Practice Test 2 — 25 Questions

Question 1

A query is filtering a table based on a 'TRANSACTION_DATE' column using a range (e.g., BETWEEN '2023-01-01' AND '2023-01-31'). The table is 500GB and not explicitly clustered. Why might this query still perform well and show good partition pruning?

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

Accepted Answer

The data was inserted in chronological order, creating natural clustering.. Snowflake micro-partitions are immutable and created in the order data is inserted. If the data is naturally loaded in chronological order, the 'TRANSACTION_DATE' values will be naturally clustered within the micro-partitions. This 'natural clustering' allows the metadata-driven pruning to skip partitions that fall outside the date range, even without an explicit clustering key.

Answer

The Result Cache is automatically storing the range for all users.

Answer

Snowflake automatically clusters all Date columns by default.

Answer

The query is small enough to fit entirely in the warehouse's metadata cache.

Question 2

Which TWO statements describe the benefits of using Snowflake's 'Change Tracking' for data transformation?

Accepted Answer

It allows for efficient incremental processing of only modified data.. Change Tracking is the underlying mechanism that enables Streams. It allows Snowflake to record which rows were inserted, updated, or deleted, making incremental transformations possible. Instead of scanning entire tables to identify changes, the engine can simply query the stream, which significantly reduces compute time and costs for ETL processes. This is critical for high-frequency data loading and incremental updates in modern data warehouses.

Answer

It eliminates the need for primary keys on tables.

Answer

It automatically cleans up stale data in the target table.

Answer

It is only available for tables in the Enterprise edition.

Question 3

A data engineer needs to identify all columns across a multi-database Snowflake account that have been assigned the 'PII_Type' tag to ensure compliance with a new privacy regulation. Which approach provides the most comprehensive and efficient result for this account-level audit?

Accepted Answer

Query the TAG_REFERENCES view within the SNOWFLAKE.ACCOUNT_USAGE schema.. Snowflake object tagging allows for fine-grained metadata management across the account. Using the ACCOUNT_USAGE.TAG_REFERENCES view is the most efficient way to track PII tags globally, as it aggregates data from all databases. This centralized approach ensures that data engineers can maintain compliance and auditability without needing to query individual database schemas, which is crucial for scalable governance strategies.

Answer

Query the INFORMATION_SCHEMA.TAG_REFERENCES table function in every database.

Answer

Use the SYSTEM$GET_TAG function on every table in the account sequentially.

Answer

Execute a SHOW TAGS command and filter for the 'PII_Type' string in the output.

Question 4

When designing a clustering key for a table that is frequently joined with other tables, which strategy generally provides the best performance for the join operation?

Accepted Answer

Cluster on the column used as the join key.. Clustering a table on the columns used in join predicates (the join keys) ensures that Snowflake can use join pruning and potentially more efficient join algorithms. When data is co-located within micro-partitions based on the join key, the engine can skip entire partitions that do not have matching keys in the other table, reducing I/O.

Answer

Cluster on the most frequently used filter column in the WHERE clause.

Answer

Cluster on the column with the highest cardinality in the table.

Answer

Cluster on a column that is never updated or changed.

Question 5

A data engineer needs to ingest files from an S3 bucket into Snowflake as soon as they are uploaded. The files arrive every few minutes and are generally smaller than 50MB. Which approach provides the most cost-effective and low-latency solution for this requirement?

Accepted Answer

Configuring Snowpipe with auto-ingest enabled using SQS notifications.. Snowflake supports several methods for loading data, and choosing the right one depends on file size, frequency, and latency requirements. Snowpipe is specifically designed for continuous, automated loading of small files. This approach reduces manual overhead and ensures that data is available for analysis shortly after it is generated at the source, which is a core requirement for modern data engineering pipelines.

Answer

Using a Task to run a COPY INTO statement every 5 minutes on a Small warehouse.

Answer

Executing a COPY INTO command via a Python script triggered by a cron job.

Answer

Creating an External Table and using the REFRESH command every minute.

Question 6

A user runs a query twice in succession with no data changes. The second query completes in near-zero time. What feature is responsible for this performance?

Accepted Answer

Query Result Cache.. The Query Result Cache stores the output of every query for a rolling 24-hour period. When a subsequent query matches the exact SQL text and the underlying data in the source tables has not changed, Snowflake returns the result from the cache. This mechanism avoids the need to spin up compute resources or scan data, providing an instantaneous response for repeated queries, which is a major efficiency feature for frequently accessed reports.

Answer

Warehouse Local Cache.

Answer

Metadata Cache.

Answer

Search Optimization Service.

Question 7

What is the primary benefit of using a file format object in Snowflake when dealing with multiple stages and load jobs?

Accepted Answer

It centralizes file configuration to ensure consistency across multiple load jobs.. File format objects provide a centralized, reusable definition of file settings (like delimiter, compression, or error handling). By referencing a single object in multiple COPY commands, an engineer ensures consistency across the entire data pipeline. This eliminates drift and makes it easy to update the configuration for all load jobs at once, reducing maintenance and preventing errors associated with manually defining parameters repeatedly across many different SQL statements.

Answer

It automatically compresses data before it is uploaded to the cloud.

Answer

It encrypts the data at rest in the stage before loading.

Answer

It stores the credentials for the external stages for easier access.

Question 8

A data engineer is tuning a query that joins two large tables. The query is performing a 'Remote Disk Spilling' operation. Which optimization strategy is most effective to resolve this?

Accepted Answer

Scale up the warehouse to a larger size.. Remote disk spilling occurs when the memory allocated to the virtual warehouse is insufficient to hold the intermediate result sets of a join or aggregation. By increasing the size of the warehouse, the memory available per node doubles, allowing larger datasets to be processed entirely in-memory. This significantly reduces latency associated with I/O operations and speeds up complex join operations that frequently cause spilling in standard configurations.

Answer

Increase the multi-cluster warehouse scaling policy to 'Maximized'.

Answer

Enable Result Cache by setting USE_CACHED_RESULT to TRUE.

Answer

Implement a materialized view on the joined columns.

Question 9

Which approach is most efficient for transforming a large volume of data in Snowflake when the logic requires complex window functions and stateful processing?

Accepted Answer

Using set-based SQL transformations in a materialized view or dynamic table.. For large-scale, complex transformations, using SQL within a View, Dynamic Table, or a CTAS (Create Table As Select) operation is highly efficient because it runs directly on the Snowflake compute engine. Snowflake's query optimizer is highly tuned for window functions and complex joins, allowing it to distribute these operations across all nodes in the warehouse, providing superior performance compared to row-by-row procedural processing.

Answer

Using a Python script to fetch data, process locally, and upload.

Answer

Using a single Stored Procedure with row-by-row cursor processing.

Answer

Using a User-Defined Function (UDF) for every column transformation.

Question 10

An organization is unloading data from a Snowflake table to an external S3 bucket for use in a machine learning pipeline. Which TWO practices will optimize the performance and manageability of the exported files? (Select TWO)

Accepted Answer

Use the PARTITION BY clause in the COPY INTO <location> statement to organize files into subfolders.. Optimizing data unloading requires balancing file size and organizational structure. Using the PARTITION BY clause ensures that the exported files are organized into a logical folder hierarchy, which facilitates faster data discovery for downstream tools. Additionally, controlling the MAX_FILE_SIZE ensures that the files are not too large for the consuming application to process efficiently in memory while maintaining parallelism.

Answer

Set the SINGLE parameter to TRUE to ensure all data is consolidated into one large file.

Answer

Disable compression using the COMPRESSION = NONE parameter to reduce the CPU load on the warehouse.

Answer

Always use the OVERWRITE = TRUE parameter to prevent the command from failing if files already exist.

Question 11

Which approach is most effective for managing governance policies across a large, multi-schema data warehouse environment?

Accepted Answer

Deploy policies to a centralized database and schema.. Centralizing governance objects in a dedicated 'GOVERNANCE' database is the best practice. By keeping policies, tags, and data classification results in a single, well-controlled schema, organizations ensure consistency and ease of maintenance. This centralized approach simplifies access control for the security team and allows for easier auditing of policy changes compared to scattering governance artifacts across disparate, business-specific databases or schemas.

Answer

Create policies within each individual schema.

Answer

Use a single shared role for all policy management.

Answer

Manually recreate all policies in every environment.

Question 12

Which Snowflake feature should be used to move data from a private cloud network to Snowflake without traversing the public internet?

Accepted Answer

AWS PrivateLink or Azure Private Link.. For enterprises with strict security requirements, using AWS PrivateLink or Azure Private Link allows for a secure, private connection between the cloud network and Snowflake. This is vital for data movement in highly regulated industries. By eliminating the need for public internet exposure, organizations minimize their attack surface and comply with networking policies, ensuring that sensitive data remains within a controlled, private infrastructure environment throughout the entire transit process.

Answer

SnowSQL with VPN tunneling.

Answer

Snowflake Data Exchange.

Answer

Snowpipe with an external stage.

Question 13

What is the primary purpose of the 'VALIDATION_MODE' parameter in the COPY INTO <table_name> statement?

Accepted Answer

To check the files for errors and return the results without loading the data into the table.. The VALIDATION_MODE parameter allows data engineers to test their COPY commands without actually committing any data to the table. It parses the files and identifies errors, which is a critical step in data movement pipelines to ensure that file formats and mappings are correct before performing a potentially expensive or large-scale ingestion.

Answer

To automatically correct data type mismatches during the load process.

Answer

To verify that the person executing the command has the correct IAM permissions on the S3 bucket.

Answer

To compare the data in the stage with the data already in the table to find duplicates.

Question 14

Which TWO of the following are true regarding the use of Snowflake Data Classification?

Accepted Answer

The process uses system-defined tags to label sensitive data.. Snowflake Data Classification automatically identifies PII and sensitive data within tables, assigning system tags to columns. This process significantly reduces the manual effort required for governance. It works by scanning sample data and applying semantic labels, which can then be used to trigger automated protection measures like masking policies. Understanding how this automates compliance is essential for any modern data engineer managing large, dynamic datasets.

Answer

Data classification can only be applied to existing tables.

Answer

Classification results are stored in the user's local file system.

Answer

Classification requires the data to be in a flat file format.

Question 15

A data engineer is reviewing a query profile for a query that performs a large join. The profile shows a high percentage of time spent in the 'Join' node with significant 'Bytes spilled to local storage'. The engineer wants to reduce local spilling without changing the query. Which action is most appropriate?

Accepted Answer

Increase the warehouse size to provide more memory for the join operation.. Local spilling during a join indicates that the join operation is exceeding the memory available on a warehouse node. Increasing the warehouse size provides more memory per node, which can accommodate larger intermediate results and reduce spilling. Filtering, clustering, or changing join types may have indirect benefits but do not directly resolve the memory pressure. Therefore, scaling up the warehouse is the most appropriate action.

Answer

Reduce the size of the smaller table in the join by filtering it.

Answer

Add a clustering key on the join column of the larger table.

Answer

Change the join type from a hash join to a sort-merge join.

Question 16

A data engineer needs to unload a large table to an external stage in Parquet format. The table contains a column with sensitive data that must be masked in the unloaded files. The engineer wants to use a secure view that applies a masking policy, and then unload from that view. Which statement accurately describes the behavior when unloading from a secure view?

Accepted Answer

Unloading from a secure view is allowed, and the masking policies defined in the view are applied to the unloaded data.. COPY INTO <location> supports unloading from secure views, and the secure view's definition, including masking and row access policies, is enforced during the unload. This means sensitive columns can be masked in the output files. The other options either deny support, claim masking is bypassed, or impose unnecessary role requirements. The correct behavior is that policies are applied, ensuring data security.

Answer

Unloading from a secure view is allowed, but the masking policies are not applied; the underlying data is unloaded unmasked.

Answer

Unloading from a secure view is not supported; COPY INTO <location> requires a base table or a regular view.

Answer

Unloading from a secure view requires the ACCOUNTADMIN role and the masking policies are ignored.

Question 17

A data engineer needs to protect a set of permanent tables in a Snowflake Enterprise Edition account. The requirement is to allow querying of historical versions for up to 30 days and to allow recovery of dropped tables within that same window. Which two actions should the engineer take to meet these requirements? (Choose two.)

Accepted Answer

Confirm the account is on Enterprise Edition, which supports retention windows longer than the Standard Edition one-day maximum.. Meeting a 30-day queryable-history requirement on permanent tables requires Enterprise Edition, which raises the maximum retention from one day to ninety days, and an explicit DATA_RETENTION_TIME_IN_DAYS setting of 30 on each table. Transient tables cap retention at one day, Fail-safe is non-queryable and fixed at seven days, and there is no configurable FAILSAFE_DAYS parameter.

Answer

Convert the permanent tables to transient tables to increase the maximum allowed retention window.

Answer

Rely on Fail-safe to provide the 30-day queryable history window for dropped tables.

Answer

Enable Fail-safe on the tables by setting FAILSAFE_DAYS to 30 using ALTER TABLE.

Question 18

A data engineer manages a transient table named STG_EVENTS in a Snowflake Enterprise Edition account. The table has a DATA_RETENTION_TIME_IN_DAYS setting of 1. The engineer wants Time Travel to retain data for 30 days for this table. What will happen if the engineer executes ALTER TABLE STG_EVENTS SET DATA_RETENTION_TIME_IN_DAYS = 30?

Accepted Answer

The statement will fail with an error because transient tables support a maximum Time Travel retention of 1 day.. Transient tables are limited to a maximum Time Travel retention of 1 day. Attempting to set a higher value such as 30 days causes Snowflake to reject the ALTER TABLE statement with an error. To achieve 30 days of Time Travel, the table must be a permanent table in an account with Enterprise Edition or higher. The table type is the deciding factor.

Answer

The retention period will be set to 30 days, but only if the table has not been cloned previously.

Answer

The retention period will be set to 30 days but Fail-safe will still be disabled for the table.

Answer

The retention period will be set to 30 days, and Time Travel will retain data for 30 days.

Question 19

A data engineer is implementing a data classification process using Snowflake's Data Classification feature. The engineer wants to automatically classify columns containing sensitive data and then use the results to apply masking policies. After running the classification, the engineer notices that some columns that should be classified as 'EMAIL' are not being tagged. The engineer has verified that the data contains valid email addresses. What is the most likely reason for the missing classification?

Accepted Answer

The Data Classification feature only samples a subset of rows, and the sample did not include enough email addresses to meet the threshold.. Data Classification uses sampling to analyze column data. If the sample does not contain a sufficient number of email addresses, the column may not be classified as EMAIL. This is a known limitation. The other options are incorrect because classification does not depend on column names, can handle NULLs, and is not restricted to a specific schema.

Answer

The Data Classification feature requires that the column name contains the word 'EMAIL' to classify it as EMAIL.

Answer

The Data Classification feature cannot classify columns that contain NULL values.

Answer

The Data Classification feature is only available for columns in the PUBLIC schema.

Question 20

A data engineer is auditing storage consumption in a Snowflake account. A large permanent table named SALES_FACT has DATA_RETENTION_TIME_IN_DAYS set to 60. Over the past month, ETL jobs have performed several full-table UPDATE operations that rewrote most micro-partitions. The engineer notices that the table's Time Travel storage has grown significantly even though the visible row count is unchanged. What is the most likely cause of the increased Time Travel storage?

Accepted Answer

Time Travel storage grows because each UPDATE creates new micro-partitions while prior versions are retained for the 60-day retention window.. Time Travel storage reflects historical micro-partition versions retained for the configured window. Each UPDATE that rewrites micro-partitions creates new versions, and the prior versions are stored for 60 days. Repeated full-table updates therefore accumulate substantial historical data, increasing Time Travel storage even when the visible row count is stable.

Answer

Time Travel storage grows because the 60-day retention forces Snowflake to store a full compressed copy of the table in Fail-safe.

Answer

Time Travel storage grows because Snowflake copies the entire table into a separate Time Travel schema on each UPDATE.

Answer

Time Travel storage grows because the account has exceeded its storage quota, causing Snowflake to duplicate micro-partitions as a redundancy measure.