DEA-C02 Multiple Select — 20 Questions

Question 1

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 2

A data engineer is designing a data protection strategy for a set of permanent tables in a Snowflake Enterprise Edition account. The engineer needs to ensure that accidental data modifications can be reversed and that dropped tables can be restored by the team without opening a support ticket. Which two configuration choices support these goals? (Choose two.)

Accepted Answer

Create zero-copy clones of critical tables on a schedule to maintain independent point-in-time copies.. Extending Time Travel retention beyond the default gives the team a longer window to query historical states and run UNDROP without support involvement. Scheduled zero-copy clones add independent recovery points that share storage until data diverges. Together these provide self-service reversal of modifications and restoration of dropped tables, whereas Fail-safe, transient tables, and disabling Time Travel all fail to meet the stated recovery objectives.

Answer

Disable Time Travel on critical tables to avoid storage charges and rely solely on external backups.

Answer

Rely on Fail-safe to restore dropped tables without involving Snowflake Support.

Answer

Use transient tables for all critical data to reduce storage costs while keeping recovery options.

Question 3

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 4

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 5

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 6

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 7

A data engineer is using Snowpark for Python to transform data. The engineer needs to perform a join between two DataFrames and then apply a filter based on a column from the joined result. Which TWO methods are appropriate for this transformation? (Choose two.)

Accepted Answer

Use `DataFrame.join()` to combine the DataFrames, then `DataFrame.where()` to apply the condition.. In Snowpark, join() combines two DataFrames, and filter() or its alias where() applies a row-wise condition. These methods build a lazy query plan that executes on Snowflake. Other methods like merge(), union(), or select() do not perform a join followed by a row filter, so they are not appropriate for this transformation.

Answer

Use `DataFrame.union()` to combine the DataFrames, then `DataFrame.filter()` to apply the condition.

Answer

Use `DataFrame.join()` to combine the DataFrames, then `DataFrame.select()` to apply the condition.

Answer

Use `DataFrame.merge()` to combine the DataFrames, then `DataFrame.filter()` to apply the condition.

Question 8

A data engineer is responsible for implementing data governance in Snowflake. The organization requires that all access to sensitive data be auditable and that data usage can be attributed to specific users and roles. Which two Snowflake features should the engineer use to meet these requirements? (Choose two.)

Accepted Answer

Query History in the ACCOUNT_USAGE schema to see all queries executed in the account.. Access History and Query History in the ACCOUNT_USAGE schema both provide detailed audit trails of data access. Access History records column-level read and write access, while Query History captures all queries with user and role information. Together, they enable comprehensive auditing and attribution of data usage to specific users and roles, meeting the governance requirements.

Answer

Tag-based masking policies to enforce access controls based on tags.

Answer

Data Classification to automatically tag sensitive columns and track their usage.

Answer

Object Dependencies in the ACCOUNT_USAGE schema to map relationships between tables and views.

Question 9

A data engineer is using Snowpark for Python to perform transformations on a Snowflake table. The engineer wants to leverage Snowpark's capabilities for data transformation. Which two statements accurately describe Snowpark's features for transformation? (Choose two.)

Accepted Answer

Snowpark allows you to write transformations using a DataFrame API that is lazily evaluated and executed on the Snowflake compute engine.. Snowpark's DataFrame API enables lazy evaluation and pushdown to Snowflake, and it supports UDFs in multiple languages. These two features are fundamental for performing transformations efficiently. The other options incorrectly describe Snowpark as caching client-side, requiring data extraction, or providing a visual interface, which are not accurate.

Answer

Snowpark automatically caches all intermediate transformation results in memory on the client side to speed up iterative development.

Answer

Snowpark requires data to be extracted to a local Python environment for any transformation that uses third-party libraries.

Answer

Snowpark provides a built-in visual interface for designing transformation pipelines without writing code.

Question 10

A data engineer is designing a storage strategy for a Snowflake account that uses Enterprise Edition. The engineer needs to ensure that critical tables can be recovered for up to 90 days, while non-critical staging tables should minimize storage costs. Which two statements accurately describe Snowflake Time Travel and Fail-safe behavior for permanent tables? (Choose two.)

Accepted Answer

Permanent tables in Enterprise Edition can have a Time Travel retention period of up to 90 days.. Permanent tables in Enterprise Edition support up to 90 days of Time Travel retention, and Fail-safe adds a fixed 7-day period after Time Travel expires. These two features together provide a comprehensive recovery strategy for critical data. Transient tables are limited to 1 day of Time Travel and have no Fail-safe, making them unsuitable for long-term recovery needs.

Answer

Fail-safe can be configured to extend beyond 7 days for permanent tables in Enterprise Edition.

Answer

Transient tables can have a Time Travel retention period of up to 90 days if the account is Enterprise Edition.

Answer

Time Travel retention for permanent tables can be set to 0 days to disable it, but Fail-safe still applies.

Question 11

A data engineer is investigating a query that reads a large table and returns only a few rows after filtering on a high-cardinality column. The Query Profile shows a high percentage of partitions scanned relative to partitions total. Which two actions should the engineer take to improve pruning? (Choose two.)

Accepted Answer

Define a clustering key on the frequently filtered high-cardinality column so related values are co-located in the same micro-partitions.. Effective pruning depends on two things: physical co-location of similar values and predicates the optimizer can translate into partition-value bounds. Clustering the filtered column groups similar values, while a direct comparison to a constant gives the optimizer the bounds it needs. Together they shrink the set of micro-partitions that must be read for a selective filter.

Answer

Increase the virtual warehouse size so more compute nodes are available to evaluate the filter predicate in parallel.

Answer

Convert the table to a view over the same data so the optimizer can push the filter predicate down into the view definition.

Answer

Apply a filter transformation inside the query that wraps the filtered column in a function such as UPPER or CAST before comparing it to a literal.

Question 12

A data engineer must validate a COPY INTO load from an external stage before promoting it to production. The team wants to confirm which files were loaded, how many rows each contained, and which rows were rejected, without leaving partial or duplicated data in the target table. (Choose two.)

Accepted Answer

Run VALIDATION_MODE = RETURN_ERRORS in a COPY INTO statement. Pre-production validation combines a dry run that surfaces rejected rows without committing data and a post-attempt audit that reports per-file outcomes. VALIDATION_MODE = RETURN_ERRORS provides the dry run, and the COPY_HISTORY table function provides the file-level audit. Options that load partial data, force reloads, or observe changes after insertion either pollute the target or fail to expose the needed diagnostics.

Answer

Set ON_ERROR = CONTINUE and inspect the target table row counts

Answer

Use FORCE = TRUE to reload all files and compare checksums

Answer

Create a stream on the target table and read the change records

Question 13

A data engineer is setting up Snowflake Data Classification on a table containing customer feedback. The engineer wants to ensure that the classification process identifies columns with potentially sensitive information and tags them appropriately. Which two actions are required to enable Data Classification on the table? (Choose two.)

Accepted Answer

Grant the role used for classification the USAGE privilege on the database and schema containing the table.. To perform Data Classification, the role must have USAGE on the database and schema, and SELECT on the table. These privileges allow the classification process to access and sample the data. Custom classifiers and specific roles like ACCOUNTADMIN are not required. Thus, the two correct actions are granting USAGE and SELECT.

Answer

Enable the ACCOUNTADMIN role to run the classification process.

Answer

Set the CLASSIFICATION_PROFILE parameter on the table to enable automatic classification.

Answer

Create a custom classifier that defines the patterns for sensitive data before running classification.

Question 14

A data engineer is using Snowpipe to load data from an external stage. The pipe is configured with AUTO_INGEST = TRUE. Which two statements are true regarding the behavior of Snowpipe in this configuration? (Choose two.)

Accepted Answer

Snowpipe relies on cloud storage event notifications to trigger the load process.. Snowpipe with AUTO_INGEST uses cloud storage event notifications to trigger loads and maintains a load history to avoid duplicates. It does not poll the stage, does not auto-create integrations, and is not compatible with internal stages.

Answer

Snowpipe periodically polls the external stage for new files based on a defined schedule.

Answer

AUTO_INGEST = TRUE requires the stage to be an internal stage.

Answer

Snowpipe automatically creates a notification integration if one does not exist.

Question 15

A data engineer is optimizing a Snowflake environment for a data warehouse that experiences high concurrency during business hours. The engineer observes that many queries are small and frequent, and the warehouse is often queued. The engineer wants to reduce queueing and improve throughput without increasing cost significantly. Which two actions should the engineer take? (Choose two.)

Accepted Answer

Implement a separate warehouse for small, frequent queries and another for large, long-running queries.. High concurrency with many small queries is best addressed by increasing parallelism through multi-cluster warehouses and by isolating workloads to prevent resource contention. Multi-cluster warehouses dynamically add clusters to handle queued queries, while workload separation ensures that small queries are not blocked by large ones. Scaling up a warehouse or tweaking timeout parameters does not increase concurrency, and result caching is ineffective for unique queries. Therefore, the two correct actions are enabling multi-cluster and separating workloads.

Answer

Increase the warehouse size from Medium to Large.

Answer

Set the STATEMENT_QUEUED_TIMEOUT_IN_SECONDS parameter to a low value.

Answer

Enable the 'USE_CACHED_RESULT' parameter and increase the result cache size.

Question 16

A data engineer is designing a pipeline that loads semi-structured data from an external stage into a Snowflake table with a VARIANT column. The files contain nested arrays and keys that vary between records. Which TWO configuration choices should the engineer make to handle the variability and preserve the structure? (Choose two.)

Accepted Answer

Enable STRIP_OUTER_ARRAY so a top-level JSON array is loaded as individual rows.. Loading JSON into a VARIANT column preserves nested arrays and varying keys without a fixed schema, and STRIP_OUTER_ARRAY turns a top-level array into individual rows. Together they handle the semi-structured variability. CSV cannot represent nesting, a fixed schema cannot accommodate unknown keys, and column-name matching requires pre-defined columns.

Answer

Set the file format TYPE to CSV with a delimiter that matches the JSON structure.

Answer

Define a fixed relational schema for every possible key before loading.

Answer

Use MATCH_BY_COLUMN_NAME = CASE_SENSITIVE to map JSON keys to columns.

Question 17

A data engineer is designing a transformation pipeline that uses Snowpark to process data. The pipeline must perform complex data cleaning and feature engineering. Which TWO capabilities of Snowpark are specifically designed to support these transformations? (Choose two.)

Accepted Answer

The ability to execute user-defined functions (UDFs) written in Python, Java, or Scala within Snowflake.. Snowpark's DataFrame API allows developers to write transformations in Python, Java, or Scala, providing a familiar and expressive way to perform complex data cleaning and feature engineering. Additionally, Snowpark supports UDFs in these languages, enabling custom logic to be executed within Snowflake. These two capabilities directly empower the development of sophisticated transformation pipelines.

Answer

The ability to automatically materialize transformation results into a new table without explicit DDL.

Answer

The ability to execute transformations on data without moving it out of Snowflake's storage layer.

Answer

The ability to use the Snowpark ML library for building and deploying machine learning models directly in Snowflake.

Question 18

A data engineer is managing a Snowflake account with multiple databases. The engineer wants to ensure that critical data can be recovered in the event of accidental deletion or modification. The engineer is considering using Time Travel and Fail-safe. Which TWO statements accurately describe the capabilities and limitations of Fail-safe? (Choose two.)

Accepted Answer

Fail-safe provides a 7-day period after Time Travel expires during which Snowflake Support can recover data.. Fail-safe is a mandatory 7-day period for permanent tables that begins after Time Travel retention expires. It allows only Snowflake Support to recover data, and it cannot be disabled or queried by users. Transient and temporary tables do not have Fail-safe. Storage costs are incurred for Fail-safe data, which is no longer accessible via Time Travel. These two statements accurately describe Fail-safe's capabilities and limitations.

Answer

Users can query data in Fail-safe using the AT or BEFORE clauses, similar to Time Travel.

Answer

Fail-safe applies to transient tables, providing an additional layer of protection after Time Travel.

Answer

Fail-safe can be disabled at the account level to reduce storage costs.

Question 19

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.)

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 20

A data engineer is tasked with implementing a data governance strategy that includes classifying sensitive data and applying tags. The engineer plans to use Snowflake's Data Classification and tag-based masking. Which two statements are true regarding the interaction between Data Classification and tags? (Choose two.)

Accepted Answer

Tag-based masking policies can be applied to system tags created by Data Classification.. Data Classification automatically assigns system tags to columns based on its analysis, and these system tags can have masking policies attached to them. This integration allows for automatic masking of sensitive data identified by classification. Manual tag creation is not required, and system tags are indeed used and can support masking.

Answer

Tag-based masking policies cannot be applied to system tags; only user-defined tags support masking.

Answer

Data Classification can only tag columns with user-defined tags, not system tags.

Answer

Data Classification requires that all tags be manually created before classification can run.