DEA-C02 Practice Test 17 — 15 Questions

Question 1

During a bulk load from an S3 stage, a data engineer notices that the data is not being loaded despite the COPY INTO command executing successfully. What is the most likely cause?

Accepted Answer

The files in the stage do not match the 'PATTERN' specified in the COPY command.. Snowflake's COPY INTO command includes a 'VALIDATION_MODE' option, but if the command is executed normally and the files are empty or mis-filtered by the 'FILE_FORMAT' settings, no rows will be ingested. The 'LOAD_HISTORY' information can confirm if files were processed. Often, the issue stems from an incorrect 'PATTERN' argument or a misalignment between the file structure and the defined format, resulting in zero rows being accepted into the target table.

Answer

The warehouse is too small to process the files.

Answer

The user lacks the 'INSERT' privilege on the target table.

Answer

The source S3 bucket was deleted before the load started.

Question 2

A company requires that data masking policies be applied automatically whenever a column is tagged with 'PII'. How can this be achieved?

Accepted Answer

Use tag-based masking policies.. Tag-based masking policies provide a direct link between metadata tagging and security enforcement. By associating a masking policy with a specific tag, any column assigned that tag automatically inherits the associated masking behavior. This automation is crucial for governance, as it prevents manual errors and ensures that sensitive data is never exposed simply because someone forgot to manually attach a policy to a new column.

Answer

Write a stored procedure to trigger on every DDL statement.

Answer

Use a Row Access Policy with a conditional tag check.

Answer

Manually apply the masking policy every time a table is created.

Question 3

A data engineer is monitoring warehouse performance and notices a high 'Warehouse Overload' status. Which TWO metrics from the Query History or Warehouse Load monitoring views should be prioritized to confirm the warehouse is undersized? (Select TWO)

Accepted Answer

Queued Overload Time. Warehouse overloading is characterized by queries waiting in a queue because all available compute threads are occupied. High queuing times (Queued Overload Time) and significant disk spillage (Remote Disk Spillage) are the primary indicators that the current warehouse size cannot handle the concurrency or the memory requirements of the workload.

Answer

Total Number of Queries Executed

Answer

Percentage of Metadata Scans

Answer

Data Scanned from Cache

Question 4

In Snowflake's object tagging hierarchy, if a tag is applied at the Schema level and a different value for the same tag is applied at the Table level, what is the resulting behavior for the Table?

Accepted Answer

The Table-level tag value overrides the Schema-level tag value.. Snowflake utilizes a hierarchy for tag inheritance where the most specific assignment takes precedence. This allows organizations to set broad defaults at the database or schema level while still permitting exceptions at the table or column level. Understanding this precedence is essential for troubleshooting why certain objects may or may not appear in audit reports.

Answer

The Schema-level tag value overrides the Table-level tag value.

Answer

Snowflake returns an error due to a conflict in the tag lineage.

Answer

Both tag values are combined into a comma-separated string for the Table.

Question 5

A data engineer is converting a column of strings into integers. Some rows contain non-numeric characters that would normally cause the query to fail. Which function should be used to return a NULL value instead of an error when a conversion is impossible?

Accepted Answer

TRY_CAST(). Data quality issues are common during transformation. Standard casting functions like CAST() or TO_NUMBER() are strict and will terminate a query if they encounter invalid input. To build resilient pipelines, engineers use 'try' variants of these functions, which gracefully handle errors by returning a NULL, allowing the rest of the dataset to be processed without interruption.

Answer

AS_INTEGER()

Answer

TO_DECIMAL()

Answer

COALESCE()

Question 6

A data engineer needs to perform a complex transformation that involves reading from a source table and writing to multiple target tables in a single transaction. What is the recommended strategy?

Accepted Answer

Wrap the multiple INSERT statements in a single BEGIN...COMMIT transaction block.. Using an explicit transaction block (BEGIN...COMMIT) ensures that multiple DML statements are treated as a single atomic unit. This is critical for data consistency, particularly when updating related tables simultaneously. If any part of the transformation fails, the transaction can be rolled back, ensuring the system remains in a valid, predictable state, which is a core requirement for reliable enterprise data pipelines.

Answer

Use an asynchronous task for each target table to run in parallel.

Answer

Create a separate stored procedure for each insert to reduce complexity.

Answer

Use a series of independent views to union the data instead of writing to tables.

Question 7

A warehouse is being used for both ETL processes and ad-hoc BI reporting. Users report that BI reports are slow during ETL runs. What is the best optimization?

Accepted Answer

Separate workloads into two distinct warehouses.. The most effective way to address contention between different workload types is to isolate them into separate virtual warehouses. ETL processes are typically compute-intensive, while BI reporting requires low latency. By separating these into two warehouses, the ETL process cannot starve the BI reports of resources, and you can size each warehouse appropriately for its specific task, ensuring both workloads run efficiently without competing for the same compute resources.

Answer

Increase the size of the existing warehouse.

Answer

Use the 'Economy' scaling policy for the warehouse.

Answer

Enable search optimization for the BI reporting tables.

Question 8

Refer to the exhibit. A data engineer applies search optimization to a table to speed up point lookups. How does this transformation impact the storage and maintenance costs for the table?

Accepted Answer

It increases storage costs and uses serverless compute for maintenance.. The Search Optimization Service is a background process that creates a persistent search access path (index-like structure). It significantly improves performance for point lookups but comes at a cost. The service consumes both storage for the search access path and serverless compute for the background maintenance as data in the base table is modified over time.

Answer

It has no impact on storage as it uses the existing metadata of the micro-partitions.

Answer

It only increases compute costs during the initial build phase, with no ongoing costs.

Answer

It reduces storage costs by compressing the base table micro-partitions more efficiently.

Question 9

A user runs the same complex analytical query twice within 5 minutes and notices the second execution is nearly instantaneous. Which Snowflake feature is primarily responsible for this performance improvement?

Accepted Answer

Result Cache. Snowflake's Result Cache stores the results of queries for 24 hours. If a user executes the exact same query and the underlying data in the table has not changed, Snowflake will return the results directly from the cache, bypassing the compute warehouse entirely and providing sub-second response times.

Answer

Metadata Caching

Answer

Warehouse Local Disk Caching

Answer

Query Acceleration Service

Question 10

Which of the following is the best practice for using the query profile to identify performance bottlenecks?

Accepted Answer

Start by identifying the operator with the highest time contribution.. The most effective approach is to focus on the 'heavy' operators, which are the parts of the query plan consuming the most time or resources. By analyzing the Query Profile from the top-down, an engineer can isolate where the processing is stalling. This targeted analysis is crucial for performance tuning, as it prevents wasting time on minor optimizations and ensures that efforts are directed toward the root causes of latency, such as spilling or high I/O.

Answer

Only analyze queries that take longer than one hour.

Answer

Ignore spilling metrics if the query eventually finishes.

Answer

Focus primarily on the number of partitions scanned.

Question 11

In a data transformation pipeline, what is the primary benefit of using 'Zero-Copy Cloning' for creating a development environment from production data?

Accepted Answer

It provides a metadata-only copy that does not incur additional storage costs until the data is modified.. Zero-copy cloning is a unique Snowflake feature that allows for the creation of identical copies of tables, schemas, or databases without duplicating the underlying data. This is a game-changer for data engineers, as it enables safe, isolated testing of transformations using real-world data volumes without the time or cost associated with traditional data movement.

Answer

It ensures that any changes made in the clone are immediately reflected in the production source.

Answer

It automatically transforms the data into a more efficient format for testing.

Answer

It encrypts the data using a different key to ensure developer privacy.

Question 12

Refer to the exhibit. Why can the user not change the retention time of the table 'SENSITIVE_DATA' to 30 days?

Accepted Answer

The table is transient, which limits retention to one day.. The exhibit shows the table is a TRANSIENT table. Snowflake limits the retention period for transient tables to a maximum of one day. Because the table was created with the TRANSIENT keyword, it does not support long-term Time Travel, and the DATA_RETENTION_TIME_IN_DAYS parameter cannot be set to a value higher than 1. To support 30 days, the table must be recreated as a permanent table.

Answer

The user lacks the OWNERSHIP privilege on the table.

Answer

The table is currently locked by a running query.

Answer

The table has change tracking enabled.

Question 13

To comply with privacy regulations, a data engineer must ensure that certain columns in a table are masked for unauthorized users during transformation. Which Snowflake feature provides a way to define re-usable masking logic that is automatically applied at query time?

Accepted Answer

Dynamic Data Masking Policies. Dynamic Data Masking is a security-focused transformation feature that allows engineers to protect sensitive data without changing the underlying stored values. By creating Masking Policies and applying them to columns, Snowflake ensures that the transformation (masking) happens dynamically based on the role and context of the user executing the query.

Answer

Row Access Policies

Answer

External Tables with encrypted files

Answer

Secure Views with hardcoded filters

Question 14

Which feature should be used to automate the loading of files from an S3 bucket into Snowflake as soon as they are uploaded, without manual intervention?

Accepted Answer

Snowpipe using event-based notifications.. Snowpipe is the native Snowflake service for continuous data ingestion. It leverages event notifications (such as S3 Event Notifications) to trigger the ingestion process immediately when new files arrive in cloud storage. This automation is vital for real-time reporting and analytics, ensuring that downstream data consumers have access to the freshest data without needing a developer to manually trigger batch processes or schedule cron jobs.

Answer

A scheduled task using the EXECUTE TASK command.

Answer

An external function that writes directly to the table.

Answer

A recurring COPY INTO command inside a stored procedure.

Question 15

Which Snowflake feature helps in managing data transformation pipelines by providing a visual and programmatic way to handle task dependencies and state management?

Accepted Answer

Tasks with dependencies (DAG). A Directed Acyclic Graph (DAG) of tasks allows for complex, multi-step transformation sequences where each task can have dependencies on the success of prior tasks. This structure provides a clear, manageable workflow for production ELT. By centralizing the orchestration logic within Snowflake, engineers avoid the need for external workflow schedulers, simplifying the architecture and improving observability through standard Snowflake monitoring views and query history tools.

Answer

Streams

Answer

Stored Procedures

Answer

External Tables