SnowflakeAssociate levelCOF-C03Page 4 of 4

COF-C03 exam dumps: questions 301 to 350 of 350

Page 4 of the free COF-C03 question bank for the SnowPro® Core Certification (COF-C03) exam. Questions 301 to 350 are listed below, the first 5 in full with answers and explanations. Back to page 1 for the exam overview and FAQ.

Question bank last updated April 2026

Start timed practice exam Questions 301 to 350

Free COF-C03 practice questions

Questions 301 to 305 of 350

Pick an answer before you open the explanation. Each question also has its own page with a permalink.

COF-C03 Question 301

Single answerSemi-structured

A retail company loads clickstream data from JSON files into a Snowflake table named RAW_EVENTS with a single VARIANT column called EVENT_DATA. Analysts need a query that returns the event type and customer ID for only those records where the JSON attribute customer.id exists and is not JSON null. Which query best meets this requirement?

A
SELECT EVENT_DATA:eventType::STRING AS event_type, EVENT_DATA:customer.id::STRING AS customer_id FROM RAW_EVENTS WHERE EVENT_DATA:customer.id IS NOT NULL;
B
SELECT EVENT_DATA:eventType::STRING AS event_type, EVENT_DATA:customer.id::STRING AS customer_id FROM RAW_EVENTS WHERE IS_NULL_VALUE(EVENT_DATA:customer.id) = FALSE;
C
SELECT EVENT_DATA['eventType']::STRING AS event_type, EVENT_DATA['customer']['id']::STRING AS customer_id FROM RAW_EVENTS WHERE EVENT_DATA['customer']['id'] <> NULL;
D
SELECT GET_PATH(EVENT_DATA, 'eventType')::STRING AS event_type, GET_PATH(EVENT_DATA, 'customer.id')::STRING AS customer_id FROM RAW_EVENTS WHERE GET(EVENT_DATA, 'customer.id') IS NOT NULL;

Show answer and explanation

Correct answer: B

Explanation

This question tests practical querying of semi-structured data stored in VARIANT, a core Snowflake skill. Snowflake supports path traversal with colon and dot notation, bracket notation, and functions such as GET and GET_PATH. A key nuance is the distinction between SQL NULL and JSON null inside VARIANT values. For semi-structured data, IS_NULL_VALUE is the documented function used to identify JSON null values. Candidates should also recognize that standard SQL comparisons such as <> NULL are invalid for null checks. In Snowflake best practice, when working with nested JSON attributes, use path expressions like EVENT_DATA:customer.id and explicitly account for JSON null behavior when filtering data.

A. Incorrect.
Incorrect. In Snowflake semi-structured data, SQL NULL and JSON null are not the same thing. A path expression that resolves to a JSON null value can still behave differently from a missing path, and filtering only with IS NOT NULL does not reliably enforce the requirement that the attribute exists and is not JSON null. This option may include rows where the key exists but contains a JSON null value, which violates the requirement.
B. Correct.
Correct. IS_NULL_VALUE is designed to test whether a VARIANT value is a JSON null. Using EVENT_DATA:customer.id in the predicate targets the nested attribute, and filtering with IS_NULL_VALUE(...)=FALSE returns rows where the element is present and not JSON null. This is the safest option among the choices for distinguishing JSON null handling in semi-structured data while extracting the values with path notation and casting them to STRING.
C. Incorrect.
Incorrect. Comparing anything to NULL with <> NULL does not work in SQL because NULL comparisons using = or <> evaluate to UNKNOWN rather than TRUE. The correct approach would require IS NULL / IS NOT NULL semantics or a function appropriate for VARIANT and JSON null handling. This option reflects a common SQL mistake.
D. Incorrect.
Incorrect. GET on a VARIANT object retrieves a direct child element by key. Passing 'customer.id' to GET treats it as a literal key name, not as a nested path, so it would not correctly access the nested customer.id attribute in typical JSON documents. While GET_PATH can navigate paths, this option inconsistently uses GET for filtering and therefore does not correctly implement the requirement.

COF-C03 Question 302

Single answerUnstructured

A media company stores product manuals and warranty PDFs in an Amazon S3 bucket and wants analysts to access those files through Snowflake without loading the file contents into table columns. The company also needs Snowflake to track file-level metadata and support SQL-based access to the files. Which approach should the architect recommend?

A
Create an external stage pointing to the S3 bucket and define an external volume so Snowflake can automatically expose the files as rows in a standard table
B
Create a directory table on an external stage that points to the S3 bucket, and query the staged files and their metadata through Snowflake
C
Load the PDF files into a VARIANT column using COPY INTO so Snowflake can treat them as unstructured data without any staging objects
D
Create a materialized view directly on the S3 bucket path so analysts can query file contents and metadata with standard SELECT statements

Show answer and explanation

Correct answer: B

Explanation

The best answer is to use an external stage that points to the S3 location and enable a directory table for that stage. Snowflake supports unstructured data by referencing files stored in cloud object storage rather than requiring those files to be loaded into table columns. Directory tables provide metadata about staged files and are a core mechanism for SQL-based interaction with unstructured data stored in stages. This aligns with Snowflake best practices for unstructured data access: keep files in object storage, expose them through stages, and use directory tables for file metadata and discovery. Relevant Snowflake documentation covers unstructured data, stages, and directory tables for external and internal stages.

A. Incorrect.
Incorrect. An external stage is part of the solution, but an external volume is not used to expose unstructured files from S3 as rows in a standard table. External volumes are associated with Snowflake-managed features such as Iceberg table storage, not with basic access to unstructured files in stages. Also, Snowflake does not automatically turn staged unstructured files into rows in a normal table simply by defining these objects.
B. Correct.
Correct. For unstructured data in Snowflake, a common pattern is to store files in cloud storage and reference them through an external stage. A directory table can then be associated with the stage to provide file-level metadata such as relative path, size, checksum information, and timestamps. This lets users access and manage unstructured files through SQL without loading the file contents into table columns.
C. Incorrect.
Incorrect. COPY INTO is used to load structured or semi-structured data into tables, but it is not the recommended way to handle files such as PDFs when the requirement is to keep them as files and access them through Snowflake. Loading binary document contents into VARIANT is not how Snowflake supports unstructured data access for this scenario.
D. Incorrect.
Incorrect. Snowflake does not support creating a materialized view directly on an S3 bucket path. Materialized views are created on query results from base tables. They are not used as a mechanism to expose external unstructured files and their metadata from cloud object storage.

COF-C03 Question 303

Single answerUnstructured

A media company stores product manuals and warranty PDFs in Amazon S3 and wants analysts to query metadata about these files from Snowflake without copying the files into database tables. The files must remain in cloud object storage, but the company wants Snowflake to manage access to the files and support SQL-based discovery of the available documents. Which solution best meets these requirements?

A
Create an external stage that points to the S3 bucket and use a directory table on the stage to expose file-level metadata for SQL queries
B
Load the PDFs into a VARIANT column in a permanent table and query the table for file metadata
C
Create a stream on the S3 bucket so Snowflake can automatically query file metadata without a stage
D
Create a materialized view directly on the S3 bucket to expose the file names and paths

Show answer and explanation

Correct answer: A

Explanation

The best answer is to use an external stage with a directory table. In Snowflake, unstructured data such as PDFs, images, and documents is accessed through stages, including external stages that reference cloud storage locations like Amazon S3. A directory table associated with the stage provides a Snowflake-managed metadata listing of the files, enabling SQL-based discovery without moving the files into relational tables. This is the practical pattern when an organization wants files to stay in object storage while still being governed and discoverable through Snowflake. By contrast, loading files into VARIANT would copy data into Snowflake tables and is intended for semi-structured formats rather than general unstructured documents. Streams and materialized views do not serve as substitutes for stages and directory tables in this use case. This aligns with Snowflake documentation on unstructured data, stages, and directory tables.

A. Correct.
Correct. For unstructured data in Snowflake, files can remain in cloud object storage and be accessed through an external stage. Enabling and querying a directory table on the stage allows users to discover files and query file-level metadata such as relative path, size, and last modified information using SQL. This approach aligns with Snowflake support for unstructured data stored in stages.
B. Incorrect.
Incorrect. Loading PDF files into a VARIANT column would require ingesting the file contents into table storage, which violates the requirement to keep the files in object storage. VARIANT is also intended for semi-structured data such as JSON, Avro, ORC, Parquet, and XML, not as the standard approach for managing unstructured files like PDFs in place.
C. Incorrect.
Incorrect. Snowflake streams track change data on Snowflake objects such as tables and some external/table-related constructs, but they do not replace stages for accessing unstructured files in cloud storage. A stream on an S3 bucket is not how Snowflake exposes unstructured file metadata for SQL discovery.
D. Incorrect.
Incorrect. Materialized views are created on query results over supported Snowflake objects, not directly on an S3 bucket. Snowflake requires a stage to reference files in object storage, and a directory table is the feature designed to surface file listings and metadata for unstructured data.

COF-C03 Question 304

Single answerAggregate functions

A retail company stores order data in a table named ORDERS with the columns CUSTOMER_ID, REGION, ORDER_AMOUNT, and ORDER_DATE. Analysts need a report that returns one row per REGION showing: the number of orders, the total sales amount, and the average order amount for that region. They also want regions with no non-NULL ORDER_AMOUNT values to show NULL for the average rather than 0. Which SQL statement best meets this requirement?

A
SELECT REGION, COUNT(*) AS ORDER_COUNT, SUM(ORDER_AMOUNT) AS TOTAL_SALES, AVG(ORDER_AMOUNT) AS AVG_ORDER_AMOUNT FROM ORDERS GROUP BY REGION;
B
SELECT REGION, COUNT(ORDER_AMOUNT) AS ORDER_COUNT, SUM(ORDER_AMOUNT) AS TOTAL_SALES, AVG(NVL(ORDER_AMOUNT, 0)) AS AVG_ORDER_AMOUNT FROM ORDERS GROUP BY REGION;
C
SELECT REGION, COUNT() AS ORDER_COUNT, SUM(ORDER_AMOUNT) AS TOTAL_SALES, ORDER_AMOUNT / COUNT() AS AVG_ORDER_AMOUNT FROM ORDERS GROUP BY REGION;
D
SELECT REGION, COUNT(DISTINCT CUSTOMER_ID) AS ORDER_COUNT, SUM(ORDER_AMOUNT) AS TOTAL_SALES, AVG(ORDER_AMOUNT) AS AVG_ORDER_AMOUNT FROM ORDERS GROUP BY REGION;

Show answer and explanation

Correct answer: A

Explanation

This question tests practical use of aggregate functions with GROUP BY in Snowflake. The key behaviors are: COUNT() counts all rows, COUNT(column) counts only non-NULL values, SUM and AVG ignore NULL inputs, and AVG returns NULL when there are no non-NULL values to average. Therefore, the best query is the one using COUNT() for order count and AVG(ORDER_AMOUNT) without replacing NULLs. This aligns with Snowflake SQL aggregate function behavior and standard reporting best practices: preserve NULL semantics unless the business explicitly wants missing values treated as 0.

A. Correct.
Correct. GROUP BY REGION returns one row per region. COUNT(*) counts all rows in each region, which matches the requirement for number of orders. SUM(ORDER_AMOUNT) adds non-NULL order amounts, and AVG(ORDER_AMOUNT) calculates the average over non-NULL values only. In Snowflake, AVG ignores NULLs, so if a region has no non-NULL ORDER_AMOUNT values, the result is NULL, which is exactly what the analysts want.
B. Incorrect.
Incorrect. COUNT(ORDER_AMOUNT) counts only rows where ORDER_AMOUNT is not NULL, so it would undercount orders when some orders have NULL amounts. Also, AVG(NVL(ORDER_AMOUNT, 0)) converts NULL amounts to 0 before averaging, which changes the business meaning and would return 0 instead of NULL for regions with no non-NULL ORDER_AMOUNT values.
C. Incorrect.
Incorrect. This expression is not valid for the stated aggregation goal because ORDER_AMOUNT is not aggregated and not included in the GROUP BY clause. In Snowflake, non-aggregated selected columns must appear in the GROUP BY clause. Even if rewritten, dividing a single ORDER_AMOUNT value by COUNT(*) would not correctly compute the regional average.
D. Incorrect.
Incorrect. COUNT(DISTINCT CUSTOMER_ID) counts unique customers, not orders. A region can have multiple orders from the same customer, so this would not satisfy the requirement to return the number of orders. SUM and AVG are correct here, but the order count metric is wrong.

COF-C03 Question 305

Single answerAggregate functions

A retail company stores order data in a Snowflake table named ORDERS with the columns CUSTOMER_ID, ORDER_ID, REGION, and ORDER_TOTAL. Analysts need a query that returns one row per REGION showing the average order value, but only for regions that have more than 100 orders. Which SQL statement correctly meets this requirement?

A
SELECT REGION, AVG(ORDER_TOTAL) AS AVG_ORDER_VALUE FROM ORDERS WHERE COUNT(ORDER_ID) > 100 GROUP BY REGION;
B
SELECT REGION, AVG(ORDER_TOTAL) AS AVG_ORDER_VALUE FROM ORDERS GROUP BY REGION HAVING COUNT(ORDER_ID) > 100;
C
SELECT REGION, AVG(ORDER_TOTAL) AS AVG_ORDER_VALUE FROM ORDERS HAVING COUNT(ORDER_ID) > 100 GROUP BY REGION;
D
SELECT REGION, COUNT(ORDER_ID), AVG(ORDER_TOTAL) AS AVG_ORDER_VALUE FROM ORDERS WHERE COUNT(ORDER_ID) > 100;

Show answer and explanation

Correct answer: B

Explanation

The key concept is the difference between WHERE and HAVING when working with aggregate functions in Snowflake SQL. WHERE filters rows before aggregation, while HAVING filters groups after aggregate calculations are performed. Because the requirement is to return only regions with more than 100 orders, the filter must be applied to COUNT(ORDER_ID) in the HAVING clause. Snowflake follows standard SQL behavior for GROUP BY and HAVING: any non-aggregated column in the SELECT list must be included in GROUP BY, and aggregate conditions belong in HAVING rather than WHERE. This is a common applied scenario on the SnowPro Core exam because it tests practical understanding of how aggregate functions are used in reporting queries.

A. Incorrect.
Incorrect. COUNT(ORDER_ID) is an aggregate function, and aggregate filters cannot be placed in the WHERE clause. WHERE filters individual rows before grouping occurs. In Snowflake, conditions on grouped results must be placed in the HAVING clause after GROUP BY.
B. Correct.
Correct. This query groups rows by REGION, calculates AVG(ORDER_TOTAL) for each group, and uses HAVING COUNT(ORDER_ID) > 100 to keep only regions with more than 100 orders. This matches the requirement of returning one row per region with an aggregate filter applied to the grouped result set.
C. Incorrect.
Incorrect. In SQL syntax, HAVING is evaluated after GROUP BY logically, but in the written query, GROUP BY must come before HAVING. This option reflects a common misunderstanding between logical query processing order and valid SQL statement syntax.
D. Incorrect.
Incorrect. This query has multiple issues: it uses COUNT(ORDER_ID) in the WHERE clause, which is not valid for aggregate filtering, and it does not include a GROUP BY clause even though REGION is selected alongside aggregates. In Snowflake, non-aggregated selected columns must appear in the GROUP BY clause.

Timed practice exam

Take a COF-C03 practice test under exam conditions

100 questions in 115 minutes, drawn from this bank, with a score report and a per-question review when you finish.

Start timed exam

COF-C03 practice questions 301 to 350 of 350

Every question has a page with the answer and explanation. Numbers are stable, so you can bookmark or share them. The bank is split into 4 pages of up to 100 questions.