COF-C03 Practice Test 12 — 15 Questions

Question 1

A data engineer is investigating a slow-running query. The Query Profile shows a high percentage of time spent in the TableScan operator and a large number of partitions scanned. The engineer wants to reduce the number of partitions scanned by using pruning. Which TWO actions should the engineer take? (Choose two.)

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

Accepted Answer

Apply filters early in the query and avoid wrapping filter columns in functions.. Partition pruning is driven by filters that the optimizer can use against micro-partition metadata. Filtering on clustering key columns and avoiding functions on filter columns allow Snowflake to skip irrelevant micro-partitions, directly reducing the number of partitions scanned.

Answer

Increase the size of the virtual warehouse to reduce the number of partitions scanned.

Answer

Use the SEARCH function in the WHERE clause for all string filters.

Answer

Convert the table to a transient table to enable automatic pruning.

Question 2

A data engineer runs a query that joins a large fact table with a small dimension table. The Query Profile shows a Join operation with an exploding number of rows and significant spilling to remote disk. The engineer notices the join condition uses a function on the join key of the large table. Which action is most likely to improve performance?

Accepted Answer

Rewrite the join to avoid applying a function to the join key of the large table.. The function on the join key prevents the optimizer from using an efficient join strategy and can cause a row explosion. Rewriting the join to compare raw columns allows hash join and pruning, directly fixing the root cause. Larger warehouses, clustering on the raw column, or caching do not address the expression in the join predicate.

Answer

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

Answer

Increase the size of the virtual warehouse to a larger multi-cluster warehouse.

Answer

Enable the USE_CACHED_RESULT parameter for the session.

Question 3

A data engineer needs to transform semi-structured JSON data stored in a VARIANT column into a relational table. The JSON contains nested arrays and objects. Which Snowflake feature should be used to flatten the arrays into separate rows while preserving the parent-child relationship?

Accepted Answer

LATERAL FLATTEN. LATERAL FLATTEN is designed to explode nested arrays and objects into rows while maintaining the association with the source row. It is the correct tool for transforming semi-structured data into a relational format. The other functions either construct objects, aggregate into arrays, or parse strings, none of which expand arrays into multiple rows.

Answer

PARSE_JSON

Answer

ARRAY_AGG

Answer

OBJECT_CONSTRUCT

Question 4

A provider shares a database with a consumer using a direct share. The consumer reports that queries against the shared database fail with an error indicating the database does not exist, even though the share was successfully created and granted to the consumer account. The provider confirmed that the share contains the necessary tables and that the consumer account has been added to the share. What is the most likely cause of the issue?

Accepted Answer

The consumer has not created a database from the share using CREATE DATABASE ... FROM SHARE.. For a consumer to access a direct share, they must create a database from the share using CREATE DATABASE ... FROM SHARE. The provider's role is to create the share, grant privileges on objects to the share, and add the consumer account. The consumer must then mount the share by creating a database. Without this step, the shared database is not visible, leading to errors when querying.

Answer

The provider did not grant the USAGE privilege on the database to the consumer's role.

Answer

The consumer's account is in a different region than the provider's account, and cross-region sharing is not enabled.

Answer

The provider did not include a secure view in the share, so the consumer cannot access the tables.

Question 5

A data engineer is using the COPY INTO command to unload data from a Snowflake table to an external stage (Amazon S3). The engineer wants to ensure the unloaded files are encrypted and can be decrypted by the target system. Which TWO statements are true regarding the encryption of unloaded files? (Choose two.)

Accepted Answer

Client-side encryption with a master key requires the target system to use the same master key to decrypt the files.. For unloading to an external stage, Snowflake supports both server-side encryption (e.g., SSE-S3, SSE-KMS) and client-side encryption with a master key. Server-side encryption relies on the cloud provider's encryption, while client-side encryption encrypts data before upload, requiring the target system to have the master key. Both methods ensure data is encrypted at rest and can be decrypted by authorized parties.

Answer

Unloaded files are always encrypted with AES-256 regardless of the encryption settings on the external stage.

Answer

By default, Snowflake uses client-side encryption with a 128-bit key to encrypt unloaded files.

Answer

If no encryption is specified for the external stage, unloaded files are not encrypted and are stored in plaintext.

Question 6

A data engineer is building a transformation pipeline that processes semi-structured JSON data. The pipeline needs to extract values from nested objects and arrays and output a relational table. The engineer wants to minimize manual coding and ensure the transformation is maintainable. Which Snowflake feature should the engineer use?

Accepted Answer

The FLATTEN function with LATERAL joins to explode arrays and extract nested fields.. FLATTEN is designed to transform semi-structured data by expanding arrays and objects into rows. When combined with LATERAL, it can be applied to each row of a base table, producing a relational output that includes the exploded elements. This approach handles nested structures and is more maintainable than manual string parsing or repeated path expressions. PARSE_JSON only parses text into VARIANT, while OBJECT_CONSTRUCT and TRY_CAST serve different purposes. FLATTEN is the correct feature for relational transformation of JSON.

Answer

The PARSE_JSON function to convert the JSON into a relational table automatically.

Answer

The OBJECT_CONSTRUCT function to build a relational schema from the JSON keys.

Answer

The TRY_CAST function to coerce the entire JSON column into a relational table.

Question 7

A data engineer is tuning a query that joins a 900 million row fact table to a 2 million row dimension table. The dimension table is fully contained in the fact table's join-key range, and the join key is not the clustering key of either table. The engineer wants to eliminate the shuffle of the fact table across warehouse nodes. Which approach best achieves this?

Accepted Answer

Broadcast the 2 million row dimension table so each node joins its local fact table partitions.. The goal is to avoid moving the large relation. Broadcasting the small dimension table replicates only 2 million rows to each node, while every node keeps its local slice of the fact table and performs the join in place. This is the classic broadcast join pattern and is what the optimizer chooses automatically when one side is small. Clustering, warehouse resizing, and broadcasting the large table all fail to remove the fact table shuffle.

Answer

Force the optimizer to broadcast the fact table to every node so each node joins locally.

Answer

Increase the warehouse size so the extra nodes provide enough memory to hold both tables without spilling.

Answer

Add a clustering key on the fact table's join column so the optimizer can use partition pruning during the join.

Question 8

A data engineer is loading data from a set of CSV files stored in an external stage into a Snowflake table. The files have a header row, and the engineer wants to skip the header during loading. The engineer also wants to ensure that any rows with missing values in a NOT NULL column are skipped and logged. Which FILE_FORMAT option should be used to skip the header row?

Accepted Answer

SKIP_HEADER = 1. To skip the header row in a CSV file during loading, the FILE_FORMAT option SKIP_HEADER should be set to the number of header rows to skip, typically 1. This ensures that the first row is ignored and not loaded into the table. Other options like SKIP_BLANK_LINES or FIELD_DELIMITER do not serve this purpose. PARSE_HEADER is not a valid Snowflake option.

Answer

PARSE_HEADER = TRUE

Answer

FIELD_DELIMITER = ','

Answer

SKIP_BLANK_LINES = TRUE

Question 9

A data engineer must continuously load Parquet files arriving in an external Azure stage into a Snowflake table. The files have no consistent naming pattern and arrive at unpredictable intervals. The engineer wants Snowflake to detect and load new files automatically without running COPY on a schedule. Which Snowflake feature should be configured?

Accepted Answer

Snowpipe with a cloud messaging notification integration. Snowpipe is designed for continuous, event-driven ingestion from external stages, and pairing it with a notification integration lets the cloud provider push file-arrival events to Snowflake. This avoids polling and scheduled COPY runs while using load metadata to prevent reprocessing. External tables, streams, and tasks do not provide automatic file-triggered loading into a native table.

Answer

Streams and tasks on the target table

Answer

A scheduled task that runs COPY INTO every five minutes

Answer

An external table with automatic refresh enabled

Question 10

A data engineer is using the COPY INTO command to load data from an external stage into a Snowflake table. The engineer wants to ensure that the load operation does not fail if some files in the stage have already been loaded previously. The engineer also wants to avoid reloading files that have already been processed. Which COPY INTO option should be used to achieve this?

Accepted Answer

Use the default behavior of COPY INTO, which automatically skips files that have been loaded in the past 64 days.. The default behavior of COPY INTO is to skip files that have already been loaded, based on load metadata retained for 64 days. This prevents duplicate loading without requiring any special options. Using FORCE or LOAD_UNCERTAIN_FILES would override this and potentially cause duplicates. PURGE deletes files but is not intended for duplicate avoidance.

Answer

Set PURGE = TRUE to remove files from the stage after loading, ensuring they are not reloaded.

Answer

LOAD_UNCERTAIN_FILES = TRUE

Answer

FORCE = TRUE

Question 11

A data analyst runs a query that filters on a DATE column and returns a small number of rows from a large table. The Query Profile shows a TableScan operator with a high percentage of partitions scanned. The analyst wants to reduce the number of partitions scanned without changing the query result. What should the analyst do?

Accepted Answer

Add a cluster key on the DATE column to improve partition pruning.. Partition pruning relies on the natural ordering of data within micro-partitions. When a table is not clustered on the filter column, the optimizer may scan many partitions because matching rows are spread across them. Adding a cluster key on the DATE column reorganizes the data so that each micro-partition contains a narrow range of dates, enabling effective pruning. This reduces I/O and improves performance without altering the query semantics. Other options either add resources or change the query pattern but do not directly reduce partitions scanned.

Answer

Create a materialized view that pre-aggregates the data by DATE.

Answer

Use the RESULT_SCAN function to retrieve cached results from a previous similar query.

Answer

Increase the warehouse size to enable more parallel scans of the table.

Question 12

A data engineer is optimizing a complex query that joins five large tables and includes multiple aggregations. The Query Profile shows significant time spent in the Join and Aggregate nodes, and the engineer wants to reduce the amount of data processed. Which TWO techniques are most appropriate for improving performance in this scenario? (Choose two.)

Accepted Answer

Use `EXPLAIN` to inspect the query plan and identify steps that process disproportionately large row counts.. Early filtering reduces the number of rows entering joins and aggregations, directly cutting the data volume that flows through the plan. Using `EXPLAIN` reveals which operators process the most rows, allowing targeted fixes. Together, these techniques address the root cause of heavy Join and Aggregate nodes. Replacing joins with `UNION ALL`, scaling the warehouse, or using `CROSS JOIN` either changes semantics, fails to reduce data volume, or makes the problem worse.

Answer

Replace all joins with `UNION ALL` to combine the tables into a single result set.

Answer

Increase the warehouse size to a 4X-Large to provide more memory for the joins.

Answer

Convert all `JOIN` clauses to `CROSS JOIN` to allow the optimizer more flexibility.

Question 13

A data engineer needs to transform semi-structured data stored in a VARIANT column. The engineer wants to extract a scalar value from a JSON object and use it in a relational query. Which Snowflake feature should the engineer use?

Accepted Answer

Use the colon operator (:) to access the value by key, for example variant_column:key_name.. The colon operator is the standard way to access a scalar value within a VARIANT column in Snowflake. It allows direct key-based access without the need for additional parsing or row expansion, making it ideal for extracting a single value for relational queries.

Answer

Use the TO_VARCHAR function to cast the entire VARIANT to a string and then use a JSON parser.

Answer

Use the FLATTEN function to explode the JSON object into rows.

Answer

Use the PARSE_JSON function to convert the VARIANT to a string and then use string functions.

Question 14

A data engineer has a large table SALES_RAW with a VARIANT column PAYLOAD that stores semi-structured JSON. The engineer needs to flatten an array of product objects inside PAYLOAD into separate rows, keeping all other columns intact. Which Snowflake construct should be used in the SELECT statement to achieve this?

Accepted Answer

LATERAL FLATTEN(input => PAYLOAD:products). The LATERAL FLATTEN table function is the standard Snowflake mechanism for expanding semi-structured arrays into rows while keeping the parent row's other columns. It accepts a VARIANT input and returns one row per element, making it ideal for converting nested JSON into a relational result set without losing context.

Answer

PARSE_JSON(PAYLOAD):products

Answer

ARRAY_AGG(PAYLOAD:products)

Answer

OBJECT_CONSTRUCT('products', PAYLOAD:products)

Question 15

A finance analyst runs a monthly report that aggregates 18 months of sales data. The report executes 40 times per day, and each run currently takes 4 minutes on a medium warehouse. The underlying tables are loaded once nightly. Which approach most effectively reduces compute cost for this workload?

Accepted Answer

Enable the result cache by ensuring the query text and session context are identical across runs.. The tables are static during the business day, so the 40 daily executions read identical data. Ensuring identical query text and session context lets Snowflake serve subsequent runs from the result cache without provisioning compute, eliminating nearly all of the recurring cost. Resizing, auto-suspend tuning, and multi-cluster scaling change resource behavior but do not remove the redundant scans.

Answer

Set the warehouse to auto-suspend after 60 seconds to avoid idle credits.

Answer

Create a separate virtual warehouse for the analyst and enable multi-cluster scaling.

Answer

Resize the warehouse to 4X-Large so each run finishes faster.