DEA-C02 Practice Test 3 — 10 Questions

Question 1

A company requires continuous ingestion of JSON logs from an S3 bucket into a Snowflake table with minimal latency. They decide to use Snowpipe with auto-ingest. How does Snowflake determine which new files need to be processed once the pipe is created?

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

Accepted Answer

It relies on event notifications from S3 sent to a Snowflake-managed SQS queue to trigger the pipe.. Snowpipe with auto-ingest relies on cloud-native messaging services to notify Snowflake of new data. When a file is uploaded to S3, an S3 Event Notification is triggered, which sends a message to a Snowflake-managed SQS queue. Snowpipe constantly monitors this queue and triggers the ingestion process as soon as it receives a notification, ensuring near real-time data movement without manual intervention.

Answer

Snowflake performs a metadata scan of the S3 bucket every 60 seconds to identify files with new timestamps.

Answer

The Snowpipe object uses the LIST command internally to compare the stage contents against the load history table.

Answer

The data engineer must execute the ALTER PIPE... REFRESH command every time new data is uploaded to the stage.

Question 2

A data engineer must load semi-structured JSON from an external stage where each file contains a top-level array of objects. The engineer wants each object in the array to become one row, with each object's keys exposed as columns. Which file format option should be configured?

Accepted Answer

STRIP_OUTER_ARRAY = TRUE. When JSON files contain a top-level array, Snowflake by default loads the entire array as one row. Setting STRIP_OUTER_ARRAY to TRUE in the JSON file format removes the outer brackets so each element of the array is parsed as an individual row. This aligns each object with a table row while preserving the object keys as columns or variant fields.

Answer

STRIP_NULL_VALUES = TRUE

Answer

REPLACE_INVALID_CHARACTERS = TRUE

Answer

SKIP_HEADER = 1

Question 3

A data engineer must unload query results from a Snowflake table into a named internal stage so another team can download the files. The engineer wants the unloaded data to be encodable in a columnar format. Which command should be used?

Accepted Answer

COPY INTO @my_stage/path/ FROM my_table FILE_FORMAT = (TYPE = PARQUET);. COPY INTO targeting a stage is the unload mechanism, and pairing it with TYPE = PARQUET produces columnar files in the named internal stage. GET and PUT move files between local storage and stages, and CREATE STAGE only defines the object, so none of those perform the required table-to-stage unload.

Answer

PUT file://local_data.csv @my_stage/path/ AUTO_COMPRESS = TRUE;

Answer

CREATE STAGE my_stage FILE_FORMAT = (TYPE = PARQUET);

Answer

GET @my_stage/path/ file://local_path;

Question 4

What is the effect of the UNDROP command on a table that has already been recreated with the same name?

Accepted Answer

The UNDROP command fails due to a name collision.. If a table is dropped and a new table is created with the same name, the UNDROP operation will encounter a name conflict. In such cases, the UNDROP command fails, as the target namespace is already occupied. To recover the dropped table, the user must first rename or drop the new table, then execute UNDROP on the original. This safety mechanism prevents data loss and accidental overwriting of active production tables.

Answer

It automatically renames the dropped table with a suffix.

Answer

It overwrites the existing table with the old version.

Answer

It merges the data from both tables.

Question 5

A data engineer needs to ensure that PII data in the 'SALES' table is obscured for non-admin users while maintaining original data types for downstream analytical models. Which approach provides the most scalable governance?

Accepted Answer

Apply a masking policy to the columns and grant the APPLY MASKING POLICY privilege.. Dynamic Data Masking allows policies to be applied to columns based on the user's role without duplicating data. By leveraging masking policies, you maintain a single source of truth while ensuring sensitive information is protected at query runtime. This method is highly scalable as a single policy can be assigned to multiple columns across different tables, simplifying maintenance and ensuring consistent security posture across the enterprise.

Answer

Create separate secure views for every user role requiring masked access.

Answer

Use row-level security to filter rows containing PII data for authorized users.

Answer

Physically transform the data during the ETL process and store it in a new table.

Question 6

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?

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 7

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 8

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 9

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 10

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.