DEA-C02 Practice Test 18 — 15 Questions

Question 1

What is the result of increasing the DATA_RETENTION_TIME_IN_DAYS from 1 to 5 days on an existing table?

Accepted Answer

The table begins retaining historical data for 5 days moving forward.. Increasing the retention period immediately updates the table's metadata. Going forward, Snowflake will begin retaining the historical data for 5 days instead of 1. This change does not retroactively 'create' historical data that has already been purged from the 1-day window; it only applies to the data state from the moment of the change onwards. This distinction is vital for planning data recovery and compliance audits.

Answer

All data from the last 5 days becomes immediately accessible.

Answer

The storage cost remains the same as it was.

Answer

The change takes effect only after the next vacuum process.

Question 2

Which governance tool allows an administrator to audit who accessed a specific table and when?

Accepted Answer

ACCESS_HISTORY.. The ACCESS_HISTORY view within the SNOWFLAKE.ACCOUNT_USAGE schema is the definitive source for auditing data access. It captures comprehensive details, including the query ID, the user, the objects queried, and the columns accessed. This level of granularity is essential for compliance reporting and security monitoring, enabling administrators to identify potential data breaches or unauthorized access patterns across the entire account.

Answer

QUERY_HISTORY.

Answer

OBJECT_DEPENDENCIES.

Answer

The Security Dashboard.

Question 3

When a large production table is cloned to a development environment using the CLONE keyword, how is the initial storage for the cloned table billed?

Accepted Answer

No additional storage is billed until the cloned table or the source table is modified.. Snowflake's zero-copy cloning feature works by duplicating the metadata of the source table without copying the underlying micro-partitions. Because both the source and the clone point to the same immutable data blocks initially, there is no additional storage cost until data in either table is modified or new data is added.

Answer

The clone is billed at 50% of the original table's storage rate until it is modified.

Answer

The storage cost is doubled immediately because a physical copy of the data is created.

Answer

Only the metadata storage is billed, which is a flat fee of 1 credit per month per clone.

Question 4

A user is experiencing 'Data Spilling' in a query that performs a complex window function over a very large dataset. What is the most likely reason this is occurring and how should it be addressed?

Accepted Answer

The query requires a large sort buffer; scale up the warehouse.. Window functions require sorting data based on the partition and order keys specified in the query. If the dataset is too large for the memory assigned to the warehouse nodes, Snowflake will spill the sorting process to local disk or remote storage. Increasing the warehouse size allocates more memory per node, which can accommodate larger sort buffers, thereby reducing or eliminating the need for disk spilling during the window function operation.

Answer

The query is missing a filter, and scaling out will fix it.

Answer

The table is not clustered, which causes excessive scanning.

Answer

The query needs a dedicated 'Snowpark-optimized' warehouse.

Question 5

When analyzing a Query Profile, which indicator most strongly suggests that 'Partition Pruning' is performing effectively?

Accepted Answer

Low partitions scanned compared to total partitions.. Partition pruning is the process where Snowflake skips scanning micro-partitions that cannot possibly contain the data requested by the query. A low 'Partitions scanned' count relative to the total 'Partitions total' is the most direct indicator that the pruning process is working. This is highly efficient as it avoids I/O operations for data that is not relevant to the query's filters, leading to faster execution times.

Answer

High bytes scanned relative to bytes total.

Answer

High network transfer metrics in the profile.

Answer

The presence of a 'Join' operator in the query profile.

Question 6

When using the COPY INTO command to load data from an S3 bucket, which of the following best describes how Snowflake handles file partitioning?

Accepted Answer

Snowflake automatically parallelizes the load by distributing files across the nodes of the virtual warehouse.. Snowflake can load files in parallel if they are partitioned or if multiple files are present. By default, the COPY INTO command leverages the compute power of the warehouse to process multiple files concurrently. This parallelization is a key driver of Snowflake's performance in data movement, allowing massive datasets to be ingested in a fraction of the time required by traditional serial loading methods.

Answer

Snowflake only processes one file at a time, regardless of the warehouse size.

Answer

The files must be manually split by the engineer before the COPY INTO command is executed.

Answer

Partitioning is only supported when loading data from internal stages.

Question 7

Which transformation technique should be used when you need to pivot data from a long-form format (rows) to a wide-form format (columns) for reporting?

Accepted Answer

Using the PIVOT clause.. The `PIVOT` clause is designed specifically to rotate data from a row-based structure into a column-based format. By specifying the column to aggregate and the values to pivot, Snowflake transforms the data set, making it easier for BI tools to consume metrics directly. This is a common requirement when generating reports that represent time-series data or categorical breakdowns where metrics are required side-by-side rather than in long, narrow tables.

Answer

Using a series of CASE statements within a GROUP BY clause.

Answer

Using a self-join to correlate rows.

Answer

Using a stored procedure to iterate through rows.

Question 8

A data engineer wants to load data from an S3 bucket and perform a transformation during the load. Which method is the most appropriate for this task?

Accepted Answer

Use a COPY INTO command with a subquery that includes transformations.. Performing transformations during the load process using a COPY INTO command is highly efficient. By selecting, casting, or filtering data as it moves from the stage into the target table, the engineer avoids the need for a separate staging table and subsequent transformation job. This reduces compute costs and latency, demonstrating a deep understanding of Snowflake's ability to combine ingestion and transformation (ELT) into a single, high-performance operation.

Answer

Load the raw data into a temporary table, then run a task to transform it.

Answer

Create a stream on the S3 bucket to trigger a transformation procedure.

Answer

Use an external function to transform the data before it reaches the stage.

Question 9

What is the primary purpose of the 'SNOWFLAKE.ACCOUNT_USAGE' schema in a governance context?

Accepted Answer

To provide audit-ready metadata on system and user activity.. The ACCOUNT_USAGE schema provides historical metadata about account activity, which is the backbone of governance and auditing. It allows organizations to query past actions to ensure compliance with internal security policies, track data usage, and identify potential risks. Without these views, administrators would lack the necessary visibility to satisfy external regulatory requirements like SOC2 or GDPR, which demand detailed accountability for data access.

Answer

To store backup copies of sensitive data.

Answer

To manage the deployment of data masking policies.

Answer

To increase the performance of analytical queries.

Question 10

What is the primary benefit of using a 'Materialized View' over a standard view in Snowflake?

Accepted Answer

It reduces compute costs by pre-computing query results.. Materialized views store the pre-computed results of a query, which avoids the overhead of re-calculating the results during each execution. This is extremely beneficial for queries that are complex, resource-intensive, and executed frequently. By contrast, a standard view computes its output every time it is called, consuming compute resources and potentially causing latency for end-users, whereas materialized views provide near-instant access to the computed dataset.

Answer

It supports real-time data streaming updates.

Answer

It automatically updates based on all underlying table changes.

Answer

It is the only way to join two tables in Snowflake.

Question 11

When performing a MERGE operation to update a large table, which factor most significantly impacts the performance of the transformation?

Accepted Answer

The clustering of the target table on the join column used in the MERGE statement.. The performance of a MERGE operation is primarily constrained by the join condition, specifically if the join column is not clustered or indexed. Because Snowflake does not use traditional indexes, clustering by the join key allows for effective partition pruning. Ensuring that the join criteria align with the table's clustering key is the most effective way to minimize data scanning and optimize the merge process.

Answer

The number of columns included in the SELECT list of the source query.

Answer

The size of the virtual warehouse, as larger warehouses always make MERGE operations faster.

Answer

The use of an explicit transaction block around the MERGE statement.

Question 12

While reviewing a Query Profile, a data engineer notices a 'Join' operator where the number of output rows is significantly larger than the sum of the input rows. Which TWO steps should be taken to resolve this performance issue? (Select TWO)

Accepted Answer

Verify if the join keys have a many-to-many relationship.. An output row count significantly larger than input row counts indicates an 'exploding join' or Cartesian product, usually caused by many-to-many relationships or missing join conditions. Validating the join predicates and ensuring the join keys are unique or properly filtered can stop the exponential growth of the intermediate result set.

Answer

Increase the size of the warehouse to handle the extra rows.

Answer

Enable the Search Optimization Service on the join columns.

Answer

Replace the JOIN with a UNION ALL operation.

Question 13

A Data Engineer needs to ensure that data in a target table is updated with changes from a source table while handling potential duplicate records. Which command should be used?

Accepted Answer

MERGE INTO target USING source ON ... WHEN MATCHED THEN UPDATE.... The MERGE command is specifically designed for complex DML operations that combine insert, update, and delete actions in a single pass. By joining the source and target on a primary key, it ensures that new records are inserted while existing records are updated, preventing duplicates and ensuring data consistency. This is the standard method for slowly changing dimensions and incremental data synchronization in modern data warehousing pipelines.

Answer

INSERT INTO ... SELECT DISTINCT

Answer

UPDATE target SET ... FROM source

Answer

CREATE OR REPLACE TABLE target AS SELECT ...

Question 14

Which of the following describes the purpose of a Snowflake storage integration object?

Accepted Answer

To provide a secure way to access cloud storage without hardcoding credentials.. A storage integration is a secure object that stores the authentication credentials (like IAM roles) required for Snowflake to access cloud storage. It eliminates the need to include secret keys or passwords in the stage definition, which follows security best practices. By centralizing credential management, it simplifies administration and enhances security, ensuring that sensitive access keys are never exposed in SQL code, which is critical for enterprise data governance.

Answer

To cache frequently accessed data to improve query performance.

Answer

To create a physical partition of data within the cloud storage bucket.

Answer

To convert data into a proprietary Snowflake format for faster loading.

Question 15

An auditor requests proof of who has accessed a specific table containing sensitive data. Which Snowflake view in the ACCOUNT_USAGE schema provides this data?

Accepted Answer

ACCESS_HISTORY.. The ACCESS_HISTORY view in the ACCOUNT_USAGE schema is the definitive source for auditing data access. It logs every query that touches a column in a table, providing a comprehensive trail that is crucial for regulatory compliance. Understanding how to query the ACCOUNT_USAGE schema is a core skill for data engineers responsible for maintaining an audit-ready environment and documenting data lineage for sensitive assets.

Answer

QUERY_HISTORY.

Answer

TABLE_STORAGE_METRICS.

Answer

OBJECT_PRIVILEGES.