Hard — COF-C03 Practice Quiz

Question 1

A data engineer loads several years of transaction data into a large Snowflake table using daily batch loads ordered by transaction_date. Analysts almost always filter queries by transaction_date ranges, and query pruning is already very effective. The engineer is deciding whether to define an explicit clustering key on transaction_date. What is the most appropriate recommendation?

Accepted Answer

Do not add an explicit clustering key, because the natural load order already aligns micro-partitions with transaction_date, providing effective pruning without incurring reclustering credit costs.

Answer

Add a clustering key on transaction_date immediately, because explicit clustering is always required for any table larger than a few gigabytes.

Answer

Add a clustering key on a high-cardinality column such as transaction_id to maximize the number of micro-partitions scanned.

Answer

Convert the table to a materialized view instead, since clustering keys cannot be applied to tables that receive daily batch loads.

Question 2

A data provider on Snowflake runs in AWS us-east-1 and has created a direct share containing several secure views. A prospective consumer runs their Snowflake account in Azure West Europe. The provider wants to share the data with this consumer with the least ongoing management overhead while ensuring the data remains accessible. What must the provider do to enable this cross-cloud, cross-region sharing?

Accepted Answer

Set up database replication to a secondary database in an account located in the consumer's region/cloud, then create and share from that replicated database (or use an auto-fulfillment listing).

Answer

Nothing special — direct shares automatically work across any cloud and region because sharing does not copy data.

Answer

Create a reader account for the consumer so they can access the share without their own Snowflake account.

Answer

Export the secure views to an external stage in Azure and grant the consumer access to the stage.

Question 3

A data provider on AWS US-East-1 has published a listing on the Snowflake Marketplace. A prospective consumer runs their Snowflake account on Azure West Europe and wants to query the shared dataset. The provider does not want the consumer's queries to incur any compute charges on the provider's account. Which statement correctly describes what must happen for this cross-cloud collaboration to work and who bears the compute cost?

Accepted Answer

The provider must replicate the shared database to a Snowflake account in Azure West Europe (or use auto-fulfillment), and the consumer's own virtual warehouse pays for the query compute.

Answer

Direct sharing works across clouds and regions with no replication, and the provider's warehouse is billed for consumer queries.

Answer

The consumer must create a reader account under the provider, and the provider is billed for all compute the reader consumes.

Answer

Cross-cloud sharing is not supported on the Marketplace; the consumer must migrate their account to AWS US-East-1 first.

Question 4

A data engineer notices a query joining an orders table (10 million rows) with a shipments table (12 million rows) is running far slower than expected. In the Query Profile, the engineer sees that a single Join operator outputs roughly 480 million rows, and a large amount of bytes are spilled to local and remote storage. The join condition uses only a partial key. What is the MOST likely cause of the performance problem revealed by the Query Profile?

Accepted Answer

An exploding (row-multiplying) join is producing a Cartesian-like result set because the join key is not sufficiently selective, dramatically increasing rows processed and causing spillage.

Answer

The result cache was invalidated, forcing Snowflake to recompute the entire join from scratch.

Answer

The tables are missing clustering keys, which prevents the join operator from pruning micro-partitions during the join.

Answer

The warehouse is undersized, and simply enabling the Query Acceleration Service will eliminate the row multiplication.

Question 5

A data engineering team runs a nightly transformation job consisting of a single very large, complex analytical query that joins several billion-row tables. The query currently spills to remote storage and takes hours to complete on an X-Small warehouse. During the day, the same warehouse serves a fluctuating number of concurrent BI dashboard queries, and users report queuing during peak hours. The team wants to address BOTH problems with the correct warehouse configuration strategy. What should they do?

Accepted Answer

Scale up the warehouse to a larger size for the nightly job, and configure a multi-cluster warehouse (scale out) to handle the daytime concurrent dashboard queries.

Answer

Configure a multi-cluster warehouse for both workloads, since adding clusters increases the memory and compute available to any individual query.

Answer

Scale up to a larger warehouse size for both workloads, because a larger size automatically resolves query queuing during high concurrency.

Answer

Keep the X-Small warehouse but lower the auto-suspend value so the warehouse restarts frequently and clears the query queue.

Question 6

An analytics team enables the Search Optimization Service on a 5 TB table that receives frequent bulk inserts throughout the day. After a week, the finance team notices unexpected credit consumption even during periods when no queries are running against the table. What is the MOST likely cause of the ongoing cost?

Accepted Answer

The search access path (search optimization data structure) is being automatically maintained by serverless compute as new rows are inserted

Answer

The result cache is being continuously refreshed for the table's queries

Answer

The virtual warehouse assigned to the table cannot auto-suspend while search optimization is enabled

Answer

Metadata cache regeneration consumes credits each time the table changes

Question 7

A data engineering team ingests data using Snowpipe with auto-ingest. Their upstream process lands data in the external stage as thousands of very small files (a few KB each) every minute. The team notices that their Snowpipe costs are surprisingly high relative to the small volume of data being loaded. What is the MOST likely cause and the best remediation?

Accepted Answer

Snowpipe charges a per-file overhead in addition to compute; batching the source data into fewer, larger files before staging will reduce cost.

Answer

Snowpipe requires a dedicated virtual warehouse that is always running; assigning a smaller warehouse will reduce cost.

Answer

Snowpipe bills based on the number of columns loaded; reducing the number of columns in the target table will reduce cost.

Answer

Snowpipe charges for each SNS/SQS notification at premium rates; disabling auto-ingest and switching to manual REST calls will eliminate the cost.