COF-C03 Practice Test 19 — 15 Questions

Question 1

A data engineer is optimizing a query that filters on a high-cardinality column in a very large table. The query currently performs a full table scan. The engineer decides to add a clustering key on that column. After clustering, the query performance improves significantly for some queries but remains poor for others that filter on a different low-cardinality column. What is the most likely reason for the inconsistent performance?

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

Accepted Answer

Clustering keys only improve performance for queries that filter on the clustering key column; other filters benefit only if they are correlated with the clustering key.. Clustering improves pruning only for predicates on the clustering key or on columns that are correlated with it. When queries filter on a different, uncorrelated column, the micro-partitions cannot be pruned based on that column, so performance remains poor. The engineer should consider whether the low-cardinality column is correlated with the clustering key or consider a different clustering strategy, such as clustering on an expression that combines both columns if that aligns with query patterns.

Answer

The clustering key must be defined on all columns used in WHERE clauses to achieve any performance improvement.

Answer

The automatic clustering service has not yet reclustered the table, so the clustering key is not effective for any query.

Answer

Clustering keys only benefit queries that use the CLUSTER BY clause in the SELECT statement.

Question 2

A provider wants to share data with a consumer and ensure that the consumer can only read the data but cannot create any objects in the shared database. The provider has already created a share and granted USAGE on the database and schema, and SELECT on the table. What additional step, if any, is required to enforce read-only access?

Accepted Answer

No additional step is required; the consumer automatically has read-only access to shared databases.. Shared databases in Snowflake are read-only for consumers. The provider grants USAGE on the database and schema, and SELECT on objects, which are read-only privileges. Consumers cannot create or modify objects in a shared database. No additional configuration is needed to enforce read-only access because it is the default and only behavior for shares.

Answer

The provider must grant the CREATE privilege on the schema to the share to allow the consumer to create objects.

Answer

The provider must set the share to read-only mode using the ALTER SHARE command.

Answer

The provider must revoke the USAGE privilege on the database from the share to prevent object creation.

Question 3

When querying an External Table, which technique provides the most significant performance improvement for selective queries?

Accepted Answer

Defining logical partitions that correspond to the storage path.. External tables reside on cloud storage outside of Snowflake. To avoid scanning all files in a bucket, Snowflake uses partitioning. By defining partition columns that match the folder structure of the external storage (e.g., year/month/day), the engine can prune irrelevant files, significantly reducing the I/O required for the query.

Answer

Converting the files in cloud storage to the CSV format.

Answer

Enabling the Search Optimization Service on the external table.

Answer

Increasing the warehouse size to 6X-Large.

Question 4

How does Snowflake's micro-partitioning architecture contribute to query performance without requiring user intervention?

Accepted Answer

It uses metadata to prune irrelevant micro-partitions during query execution.. Micro-partitioning is the foundation of Snowflake's performance and scalability. Because it is automatic, users do not need to define partitions manually as they do in traditional systems. This 'zero-management' approach ensures that data is always organized for performance, and the metadata generated during this process is what enables extremely fast pruning.

Answer

It requires users to manually define partition boundaries for every table.

Answer

It compresses data using a single global algorithm for the entire table.

Answer

It stores all data in a single massive file to avoid file system overhead.

Question 5

A data engineer creates a materialized view on a large sales fact table to improve performance for a frequently run aggregation query. After a few days, users report that the materialized view sometimes returns stale data compared to the base table. The engineer verifies that the base table is being updated continuously via Snowpipe. What is the most likely explanation for the stale results?

Accepted Answer

The materialized view is refreshed asynchronously, and there can be a delay between base table updates and the view reflecting those changes.. Materialized views in Snowflake are automatically maintained but refreshes are asynchronous, meaning there can be a delay before changes in the base table are reflected. This inherent lag explains why users might see stale data shortly after Snowpipe loads new records. The refresh process is managed by Snowflake and does not require manual intervention or interval settings.

Answer

Materialized views are not supported on tables that are updated by Snowpipe; therefore, the view is not being refreshed.

Answer

The materialized view was created without specifying a refresh interval, so it never refreshes automatically.

Answer

Materialized views in Snowflake are automatically refreshed only when the base table changes, but Snowpipe loads do not trigger refreshes.

Question 6

Which TWO statements are true regarding the costs associated with Data Sharing in Snowflake?

Accepted Answer

The data provider pays for the storage costs of the shared data.. Understanding the cost model is crucial for data providers. In Snowflake, the data provider pays for the storage of the shared data, as it resides in their account. However, the consumer is responsible for the compute costs incurred when running queries against that shared data. This separation of costs ensures that data providers can scale their offerings based on data volume, while consumers maintain control over their own compute spend and workload optimization efforts.

Answer

The data provider is responsible for all compute costs incurred by the consumer.

Answer

The consumer pays for the storage costs of the shared data.

Answer

Both the provider and consumer share the total compute costs equally.

Question 7

A compliance officer needs to confirm which roles have been granted to a specific user across the account, including grants made through role hierarchies. Which Snowflake command should be used to retrieve this information?

Accepted Answer

SHOW GRANTS TO USER <username>. SHOW GRANTS TO USER returns the roles granted to a named user, including those inherited through role hierarchies, which is exactly what a compliance audit needs. SHOW GRANTS ON targets object privileges, SHOW ROLES lists role definitions, and SHOW USERS lists user accounts. Only SHOW GRANTS TO USER maps a user to their effective roles.

Answer

SHOW GRANTS ON USER <username>

Answer

SHOW USERS

Answer

SHOW ROLES

Question 8

A data engineer is configuring a Snowpipe to automatically load data from an external stage (Google Cloud Storage) into a Snowflake table. The engineer wants to minimize latency between file arrival and data availability. Which configuration should the engineer use?

Accepted Answer

Set up a cloud messaging service (e.g., Google Cloud Pub/Sub) to send event notifications to Snowflake, and create a pipe with AUTO_INGEST = TRUE.. To minimize latency, Snowpipe should be configured with AUTO_INGEST = TRUE, which leverages cloud messaging services to receive event notifications when new files arrive. This event-driven approach triggers immediate ingestion, ensuring data is loaded as soon as it lands in the external stage.

Answer

Configure the external stage to use a storage integration and set the pipe's file format to automatically detect new files via directory listing.

Answer

Create a pipe with AUTO_INGEST = FALSE and schedule a task to call the pipe every minute using SYSTEM$PIPE_FORCE_REFRESH.

Answer

Use the Snowpipe REST API to call insertFiles every 30 seconds from an external scheduler to check for new files.

Question 9

A user needs to query data stored in an external stage that points to an Amazon S3 bucket. The user has been granted the necessary privileges on the stage and the storage integration. Which of the following is required to successfully query the data directly from the stage using a SELECT statement?

Accepted Answer

The external stage must be defined with a file format that matches the data.. To query data directly from an external stage, the stage must have a file format defined that matches the data files. This enables Snowflake to interpret the data correctly. Loading data into a table is optional, and administrative roles or same-region buckets are not required. The key is proper configuration of the stage and appropriate privileges.

Answer

The S3 bucket must be in the same AWS region as the Snowflake account.

Answer

The user must have the ACCOUNTADMIN role to query external stages.

Answer

The data must first be loaded into a Snowflake table using COPY INTO.

Question 10

A data engineer runs a long-running aggregation query on a virtual warehouse. Mid-execution, the warehouse is resized from MEDIUM to LARGE. What happens to the query?

Accepted Answer

The query continues running on the MEDIUM warehouse until completion, and the resize applies to subsequent queries.. When a warehouse is resized while queries are running, Snowflake postpones the change until the warehouse becomes idle. The running query continues on the original size and completes normally. The new size applies only to future queries after the warehouse restarts. This design avoids disrupting active workloads and ensures consistent execution.

Answer

The query is cancelled and must be resubmitted manually after the resize completes.

Answer

The query fails with an error indicating the warehouse size changed during execution.

Answer

The query is paused, the warehouse resizes, and the query automatically resumes on the LARGE warehouse with full progress preserved.

Question 11

A data engineer is optimizing a complex query that joins multiple large tables and includes several aggregations. The Query Profile shows a high number of rows spilled to local disk and remote disk. Which TWO actions are most likely to reduce spilling and improve performance? (Choose two.)

Accepted Answer

Rewrite the query to filter and aggregate data earlier in the pipeline.. Spilling indicates that intermediate results exceed available memory. Increasing warehouse size provides more memory, and rewriting the query to filter and aggregate earlier reduces the volume of intermediate data. Clustering, result caching, and temporary tables do not directly address the memory pressure causing the spilling.

Answer

Add a clustering key on the join columns of the largest table.

Answer

Convert the query to use a temporary table to materialize intermediate results.

Answer

Enable the USE_CACHED_RESULT parameter to reuse previous results.

Question 12

An organization wants to create a 'Data Exchange' but only for its internal departments and a few selected vendors. They want to control who can join and what data is listed. Which Snowflake feature is best suited for this?

Accepted Answer

A Private Data Exchange.. A Snowflake Data Exchange (often referred to as a Private Exchange) is a private version of the Marketplace. It allows an organization to create its own curated data ecosystem. The organizer can invite specific members, approve or reject listings, and maintain strict governance over the collaboration environment, making it ideal for internal or closed-ecosystem sharing.

Answer

Snowflake Marketplace with Public Visibility.

Answer

Direct Sharing with a single 'Global Share' object.

Answer

Database Replication across all department accounts.

Question 13

A user is assigned the 'SECURITYADMIN' role. Which of the following tasks can this user perform?

Accepted Answer

Grant and revoke privileges to other roles.. The SECURITYADMIN role is specifically designed for managing security objects such as roles, grants, and users. This role has the power to grant and revoke privileges to other roles. Keeping this role separate from SYSADMIN (which manages warehouses and databases) is a key governance practice known as Segregation of Duties, ensuring that no single person has unchecked control over both security and data.

Answer

Create and manage warehouses.

Answer

Modify account-level billing settings.

Answer

Drop databases without restriction.

Question 14

A data engineer is using the Snowpipe REST API to ingest data from an external stage. They need to ensure that the pipe does not reprocess files that have already been loaded. Which mechanism does Snowpipe use to track which files have been processed?

Accepted Answer

A load history table maintained by Snowpipe. Snowpipe uses an internal load history that records the filename and checksum of each ingested file. When a new file notification arrives, Snowpipe consults this history to determine if the file has already been processed. This prevents duplicate ingestion even if the same file is staged multiple times. The other options do not provide this tracking capability.

Answer

A unique file naming convention enforced by the user

Answer

A metadata table in the INFORMATION_SCHEMA

Answer

A hash of the file stored in the stage metadata

Question 15

A Snowflake user needs to query data that is stored in an external table pointing to Parquet files in an external stage. The user wants to optimize query performance by reducing the amount of data scanned. Which feature should the user leverage?

Accepted Answer

Use partition pruning by organizing the external files into a directory structure that reflects common filter columns. For external tables, Snowflake can prune partitions based on the directory structure of the external files. By organizing files into folders that correspond to common filter columns (e.g., year/month/day), queries with filters on those columns can skip irrelevant files, significantly reducing data scanned and improving performance.

Answer

Create a materialized view on the external table

Answer

Define a clustering key on the external table

Answer

Enable the search optimization service on the external table