Snowflake SnowPro Core Certification Exam (COF-C03)

A team is building a CI/CD pipeline to manage database changes. They need a tool that can perform these automated tasks:

1. Run a file containing multiple complex DDL statements.
2. Dynamically pass environment variables into SQL scripts at runtime.
3. Authenticate users with key-pair authentication without requiring manual password entry.

Which tool should be used to accomplish this?

A user is investigating a slow-running query using the Query Profile. The profile shows a join operator that consumes 10,000 records from the left table and 5,000 records from the right table but produces 50,000,000 records as output. The join operator is consuming 95% of the total query execution time.

What is causing this performance issue and how should it be fixed?

A consumer runs a complex query on a shared table at 11:00 AM. At 11:01 AM, the provider executes a committed INSERT statement to the source table.

If the consumer runs the exact same query at 11:05 AM, what will be the state of the data returned?

Which types of values lead to slower queries and increased storage consumption when placed within a VARIANT column? (Choose two.)

A production table experiences slow query times due to excessive data scanning. The warehouse is correctly sized, but analysts frequently run highly selective point lookups on a high-cardinality VARCHAR column (UUIDs). This setup prevents effective micro-partition pruning.

Which action should be taken to optimize performance?

Which function should be used to access values from an object?

A team plans to build data transformation pipelines using a Snowflake supported connector that enables compute pushdown and native integration with Snowflake's features.

Which feature should be used?

Query results contain a small number of rows or columns relative to the base table with these characteristics:

1. The results require significant processing

2. The base table changes infrequently

3. The query is using only one large table

Which data object should be created to improve the performance?

Which statement can be used to take a snapshot of a table named my_table?

Which statement will return a NULL value instead of raising an error when converting an input to a date?

A user is loading line-delimited JSON from a stage. Some rows contain invalid data, but the load operation cannot be stopped.

Which COPY INTO statement will handle this requirement?

Which query creates a JSON-style object with the keys name and age, and values Alice and 30?

Which commands only use Cloud Services resources? (Select TWO).

A size Medium virtual warehouse processes scheduled reports for a team. More than 30 reports run simultaneously during business hours, and the team requires high availability with consistent performance and minimal startup time.

What action should be taken to help avoid queuing when these reports are run?

A user needs to link a private GitHub repository to Snowflake for stored procedure version control. Their company enforces secure authentication.

Which authentication method should be configured in the Git integration object?

How does clustering depth impact query performance?

When multiple Snowflake security policies are active, which option shows the correct evaluation order?

A size Medium standard virtual warehouse is being used to continuously load data. The data will be consumed using reports.

Which step will optimize costs?

A team needs to load large batches of historical data from CSV files stored in Amazon S3 into tables once per week. They want to control costs by using compute resources only when needed.

Which loading method and compute approach will accomplish this?

A reporting dashboard uses a dedicated warehouse that auto-suspends after five minutes of idle time. Similar queries run sporadically with gaps exceeding five minutes. Users notice the first query after suspension takes significantly longer than subsequent queries while the warehouse stays active.

How should query performance be improved?

A user notices that a complex Snowflake query has slowed down over time. They access the QUERY_INSIGHTS view to investigate.

What information will help identify the root cause and optimize the query?

Which virtual warehouse parameters are used to configure how clusters will run in Maximized mode or Auto-scale mode? (Choose two.)

After configuring AUTO_SUSPEND to 5 minutes to handle periods of increased query activity, stakeholders report the first query after a pause is slower than subsequent ones.

What causes this behavior?

The first query run on virtual warehouse takes longer to run than the next run of the same query an hour later.

What causes this to happen?

A Data Metric Function (DMF) can be created and scheduled to run on which object types inside a schema? (Select TWO).

A user needs to implement Slowly Changing Dimensions (SCD Type 2) to track historical changes.

Which action will accomplish this?

A table with a VARIANT column stores sales data. Inside the VARIANT is a JSON array of line items, and each item includes an amount field. The goal is to calculate the total amount.

Which approach should be used to calculate the total?

What is the initial status of a cloned task?

A Snowflake Practitioner is using the COPY INTO command to unload data to a named external stage on Amazon S3.

Which file formats can be used to unload this data? (Select TWO).

A user runs a query on a large table using a Medium virtual warehouse. The same query is run on the same table by a different user using a different warehouse.

Which caches will be used?

Which service type can be monitored by an account budget but not by a custom budget?

Which will enable temporary scaling for queries that contain complex computations that may consume many credits?

A global organization needs to share governed data products with both internal teams and external customers across multiple regions. The solution must also support tracking data usage by consumers and allow optional monetization of shared data.

Which Snowflake capability should be used?

This query was run:

select count(*) from ORDERS;

Where will the results come from?

During a COPY INTO operation data is transformed using the PARSE_JSON function.

Which is the resulting value if the string value 'null' is received by the function?

A dashboard query is running much slower than expected. The Query Profile shows a join operator consuming 70% of the total execution time and producing 1,000 times more rows than its input tables.

What is causing this issue?

How does Snowflake's micro-partitioning improve query performance and storage efficiency? (Select TWO).

Which metadata does Snowflake store about the rows in a micro-partition? (Choose two.)

Which action can be performed on a tag-based masking policy?

When will queries benefit from clustering? (Choose two.)

Which are important when working with Snowflake Notebooks? (Select TWO).

How does the search optimization service use the search access path?

A Query Profile of an aggregation query includes the Bytes spilled to remote storage: 250 GB metric. The query is running on a size Small virtual warehouse and takes 1 hour to complete.

How can the query performance be improved?

What can be shared when the SECURE_OBJECTS_ONLY property is set to = FALSE?

During a COPY INTO load, a user would like to transform staged data by joining it to a small reference table and using the FLATTEN function.

What will be the result?

What tasks can a data consumer perform using third-party data sets in the Snowflake Marketplace?

These commands are run in sequence:

After these grants are applied, a new table named customer_orders is created in the analytics_db.sales schema.

Which roles will receive privileges on the customer_orders table, and what privileges will they have?

A Snowflake user needs to share a data set and wants it to be available to any Snowflake account that resides in the same cloud region.

What is the recommended way to do this?

An external stage already contains six months of historical CSV files. New files will land in the same cloud storage location throughout the day and should load automatically soon after arrival. A team wants to load the historical files once and avoid loading them twice.

Which approach should the team use?

What is the MOST efficient method to share a subset of data from a table with a consumer account?

Personally Identifiable Information (PII) data in the EMPLOYEE table needs to be protected. Only users with the HR_ROLE should be able to see email addresses.

Which feature will meet this requirement?

What steps are required when creating an external share that includes a table from Database A and a view from Database B?

A Snowflake Practitioner needs to give Analysts read access to a reporting schema while following least privilege and keeping access easy to manage as the data changes.

Which approach meets this goal?