COF-C03 Practice Test 3 — 30 Questions

Question 1

A company is using an external S3 stage to load data daily. They want Snowflake to automatically delete the source files from the S3 bucket only after they have been successfully loaded into the table. Which COPY INTO option should they enable?

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

Accepted Answer

PURGE = TRUE. The PURGE parameter in the COPY INTO command is designed for automated cleanup of staged files. When set to TRUE, Snowflake will issue a delete command to the stage (internal or external) for every file that was successfully ingested. This helps manage storage costs and ensures that the same files are not accidentally processed again in future load cycles.

Answer

DELETE_AFTER_LOAD = TRUE

Answer

REMOVE_FILES = TRUE

Answer

AUTO_CLEAN = TRUE

Question 2

A provider wants to share data with a consumer using a reader account because the consumer does not have a Snowflake account. Which statement describes the correct capability of a reader account in this scenario?

Accepted Answer

A reader account is created and fully managed by the provider, can query shared data, and cannot load or modify data or create its own objects beyond limited query usage.. Reader accounts exist specifically for consumers without a Snowflake account. The provider creates, owns, and pays for them, and the consumer uses a limited interface to query only the shared data. They cannot load data, create shares, or be upgraded into normal accounts.

Answer

A reader account is created by the consumer and billed to the consumer, with full privileges to create databases and shares.

Answer

A reader account can be converted into a standard Snowflake account at any time by the consumer with no provider involvement.

Answer

A reader account can create its own shares and publish listings to the Snowflake Marketplace for other accounts.

Question 3

Refer to the exhibit. This JSON policy is part of the setup for a Snowflake Storage Integration. What is the architectural purpose of the 'sts:ExternalId' condition in this cross-account IAM trust relationship?

Accepted Answer

It prevents the 'confused deputy' problem by ensuring only the correct Snowflake account can assume the role.. The External ID is a security best practice used in cross-account IAM roles to prevent the 'confused deputy' problem. In the Snowflake architecture, it ensures that the cloud provider only allows Snowflake to assume the role if the request includes the specific ID unique to that Snowflake account. This prevents one customer from potentially accessing another customer's cloud resources.

Answer

It identifies the specific S3 bucket that Snowflake is allowed to access.

Answer

It maps Snowflake users to specific AWS IAM users for fine-grained access control.

Answer

It encrypts the data during transit between the cloud provider and Snowflake.

Question 4

What happens to the performance of a warehouse when multiple users query the same data simultaneously?

Accepted Answer

Performance remains consistent due to shared storage.. Snowflake's architecture handles concurrent queries by using the metadata from the Cloud Services layer and the underlying micro-partitioning in the Storage layer. Because data is immutable and stored in a shared object store, every query has a consistent view of the data. When multiple users query the same data, the system optimizes reads through its caching mechanisms, ensuring that performance remains stable and predictable regardless of the number of concurrent users.

Answer

Performance degrades significantly due to locking.

Answer

The warehouse must be cloned to allow concurrent access.

Answer

Each user must have their own dedicated storage.

Question 5

A security administrator needs to ensure that all data loaded into Snowflake is encrypted using a customer-managed key. Which feature should be configured?

Accepted Answer

Tri-Secret Secure.. Tri-Secret Secure is the Snowflake feature that enables customers to maintain control over their data encryption keys. By combining a customer-managed key with a Snowflake-managed key, the organization ensures that data is encrypted at the storage layer while allowing for key rotation and revocation. This is a vital component for high-compliance industries requiring total control over the data lifecycle and access to encrypted storage buckets.

Answer

Snowflake-managed keys with automatic rotation.

Answer

Always Encrypted feature.

Answer

Column-Level Security encryption.

Question 6

A data architect is designing a multi-cluster warehouse to handle unpredictable bursts in query volume. Which architectural feature ensures that the warehouse automatically scales out to maintain performance without manual intervention?

Accepted Answer

Multi-cluster warehouse scaling. Multi-cluster warehouses use a scaling policy to automatically start and stop clusters based on the current load. This is critical for Snowflake's elasticity, allowing the system to handle concurrent users efficiently while controlling costs. By configuring the minimum and maximum cluster counts, the architect ensures the platform scales dynamically to meet demand, preventing performance bottlenecks during peak processing times while minimizing resource idle time during low activity.

Answer

Warehouse auto-suspend

Answer

Query result cache

Answer

Warehouse resizing

Question 7

What is the role of the 'Search Optimization Service' in Snowflake?

Accepted Answer

It accelerates point-lookup queries.. The Search Optimization Service is specifically designed to improve the performance of point-lookup queries on large tables. By creating a persistent search index, it allows Snowflake to quickly identify specific rows that match a filter, even in massive datasets. This is essential for applications requiring sub-second response times for single-record lookups, which would otherwise require full table scans that are expensive and slow in traditional analytical environments.

Answer

It speeds up complex join operations.

Answer

It enables automatic data partitioning.

Answer

It optimizes storage space for tables.

Question 8

A company is migrating from an on-premises Hadoop cluster to Snowflake. They are concerned about how Snowflake manages the lifecycle of micro-partitions and whether they need to manually 'defragment' the storage over time. How does Snowflake address this?

Accepted Answer

Snowflake uses an immutable storage model where data is never updated in place.. Snowflake's storage layer is immutable, meaning micro-partitions are never modified in place. When data is updated or deleted, Snowflake creates new micro-partitions and marks the old ones for Time Travel and Fail-safe. This architecture eliminates the need for manual defragmentation, as the system continuously manages the storage and metadata to maintain optimal performance and data integrity.

Answer

Users must run the 'VACUUM' command weekly to reclaim space from deleted rows.

Answer

The system automatically performs a full table rewrite every time a DML operation occurs.

Answer

Storage is managed by the underlying cloud provider's file system, which handles defragmentation.

Question 9

Which type of Snowflake stage is automatically created for every user and cannot be dropped or altered?

Accepted Answer

User Stage. Snowflake provides several types of internal stages to simplify file management. The User Stage is a unique, dedicated area for each user to store files before loading them into tables. It is automatically provisioned and managed by Snowflake, ensuring that users always have a private location for staging data without requiring administrative configuration or manual stage creation.

Answer

Named Internal Stage

Answer

Table Stage

Answer

External Stage

Question 10

Refer to the exhibit. Based on the query profile metadata provided, which Snowflake feature most likely contributed to the high efficiency of this query?

Accepted Answer

Partition Pruning. Partition pruning is a core performance feature that allows Snowflake to ignore entire blocks of data that do not meet the filter criteria. By utilizing the metadata stored in the Cloud Services layer, Snowflake identifies the specific partitions containing the relevant 'order_date' values. This dramatically reduces I/O operations and speeds up query execution, which is fundamental to Snowflake's high-performance data processing architecture.

Answer

Search Optimization Service

Answer

Materialized Views

Answer

Query Result Caching

Question 11

What is the consequence of applying a Row Access Policy to a table that already contains existing data?

Accepted Answer

The policy is applied immediately to all subsequent queries.. When a Row Access Policy is applied to an existing table, the policy immediately takes effect for all subsequent queries. Any user who runs a query against the table will only see the rows allowed by the policy logic. This is critical for governance because it provides an immediate, retroactive enforcement of security rules without needing to migrate or transform the existing data in the table, ensuring compliance from the moment the policy is activated.

Answer

The data must be reloaded to apply the policy.

Answer

Existing queries will continue to return all rows.

Answer

The table must be dropped and recreated.

Question 12

A data engineer wants to ensure that data loaded into Snowflake is encrypted at rest and in transit. Which Snowflake architecture component handles the encryption and decryption processes automatically without requiring user-managed keys or manual intervention?

Accepted Answer

The Snowflake Cloud Services layer. Snowflake utilizes a multi-layered security architecture where encryption is handled transparently by the cloud service layer. Data is automatically encrypted using AES-256 before being written to persistent storage. This architectural feature is critical because it ensures compliance and data protection without adding operational overhead for the end user, allowing architects to focus on data transformation logic rather than managing complex infrastructure security keys.

Answer

The Virtual Warehouse compute engine

Answer

The Snowflake Stage object configuration

Answer

The Snowflake Database Catalog

Question 13

Refer to the exhibit. User 'jdoe' holds the 'manager' role. Which privileges does 'jdoe' possess regarding roles and data access?

Accepted Answer

jdoe has the privileges of the manager and analyst roles.. Snowflake's role-based access control (RBAC) supports hierarchy. When 'analyst' is granted to 'manager', and 'manager' is granted to 'jdoe', 'jdoe' inherits all privileges assigned to both the 'manager' and 'analyst' roles. This inheritance model allows for complex organizational structures while keeping grant management simple. It is crucial for governance, as it prevents over-privileging by allowing admins to nest permissions logically rather than assigning every individual role directly to users.

Answer

jdoe has the privileges of the manager role only.

Answer

jdoe must explicitly switch to the analyst role to see its data.

Answer

jdoe only has access to objects created by the analyst role.

Question 14

Which function should be used to transform a single row containing a semi-structured VARIANT column with an array of objects into multiple individual rows?

Accepted Answer

FLATTEN. Transforming semi-structured data is a core capability of Snowflake, allowing users to convert nested JSON or arrays into a relational format. The FLATTEN function is specifically designed for this purpose, enabling data analysts to join the parent row with its child elements, which is essential for reporting and traditional SQL analysis.

Answer

PARSE_JSON

Answer

OBJECT_CONSTRUCT

Answer

STRIP_NULL_VALUE

Question 15

A user wants to check for potential errors in a set of staged files without actually loading the data or consuming significant warehouse credits. Which approach should they use?

Accepted Answer

Execute the COPY command with the VALIDATION_MODE = 'RETURN_ERRORS' parameter specified.. The VALIDATION_MODE parameter in the COPY command is a powerful tool for data quality assurance. It allows Snowflake to parse files and identify errors without writing data to the table, which helps prevent failed loads and saves time by catching formatting issues before the actual ingestion occurs.

Answer

Run the COPY command with the ON_ERROR = 'ABORT_STATEMENT' and PURGE = 'FALSE' options.

Answer

Query the files directly using the SELECT * FROM @stage syntax with a LIMIT 100 clause.

Answer

Use the ANALYZE_STAGE function to generate a report on the health and formatting of the staged files.

Question 16

A developer is performing a large data load using the COPY INTO command. The load is taking longer than expected. Which action should be taken to optimize this load?

Accepted Answer

Split large files into smaller, equal-sized chunks.. Optimizing bulk data loads involves ensuring the data is split into appropriately sized files to maximize parallelism during the ingestion process. Snowflake’s COPY command can leverage multiple warehouse nodes if the input files are partitioned effectively. By ensuring that the files are roughly 100MB to 250MB each, the load process can be distributed across the available nodes in the virtual warehouse, leading to significant reductions in the overall time required to complete the load.

Answer

Reduce the size of the virtual warehouse.

Answer

Change the file format to JSON.

Answer

Disable auto-clustering on the target table.

Question 17

A data engineer notices that a query on a large table is consistently slow despite the table being clustered. The query filters on a column that is not part of the clustering key. What is the most efficient way to improve performance for this query?

Accepted Answer

Redefine the clustering key to include the filter column.. Improving performance requires minimizing the amount of data scanned during query execution. Since the existing clustering key does not align with the query filter, Snowflake must scan more micro-partitions than necessary. By redefining or adding a clustering key that aligns with the frequently used filter column, the query optimizer can prune unnecessary partitions effectively. This process reduces I/O overhead and significantly speeds up query execution, demonstrating the vital role of data layout in performance.

Answer

Increase the virtual warehouse size.

Answer

Create a materialized view on the column.

Answer

Convert the table to a temporary table.

Question 18

Which technique is recommended to improve the performance of a query that must frequently filter data based on values within a VARIANT column containing JSON data?

Accepted Answer

Materialize frequently used JSON keys into separate relational columns.. Querying semi-structured data is efficient in Snowflake, but performance can be further enhanced by creating 'functional' elements. Since Snowflake micro-partitions store VARIANT data in a columnar fashion, extracting common fields into their own relational columns allows the engine to use standard pruning and statistics more effectively.

Answer

Convert the JSON into a single large string and use LIKE operators.

Answer

Create a separate table for every key-value pair in the JSON.

Answer

Disable the use of the result cache for all JSON-based queries.

Question 19

A user wants to create a table that automatically stays up-to-date with a complex transformation from a source table. The transformation involves multiple joins and aggregations. Which Snowflake object is best suited for this, assuming the user prioritizes ease of management and low latency?

Accepted Answer

Dynamic Table. Dynamic Tables represent a shift toward declarative data engineering in Snowflake. Unlike Materialized Views, which have strict limitations on joins and functions, or Tasks/Streams, which require manual orchestration, Dynamic Tables automatically manage the refresh process based on a target lag, simplifying the management of complex data pipelines.

Answer

Materialized View

Answer

Standard View

Answer

External Table

Question 20

A Snowflake administrator is configuring a new external stage to load data from an Amazon S3 bucket. The storage integration already exists and is named MY_S3_INT. The administrator needs to reference this integration when creating the stage. Which SQL statement correctly creates the stage with the necessary parameters?

Accepted Answer

CREATE STAGE my_stage URL='s3://mybucket/path/' STORAGE_INTEGRATION=MY_S3_INT;. To create an external stage that uses a storage integration, the correct syntax includes the STORAGE_INTEGRATION parameter followed by the integration name as an identifier (unquoted). This allows Snowflake to assume the IAM role defined in the integration to access the S3 bucket securely, without embedding credentials.

Answer

CREATE STAGE my_stage URL='s3://mybucket/path/' INTEGRATION=MY_S3_INT;

Answer

CREATE STAGE my_stage URL='s3://mybucket/path/' STORAGE_INTEGRATION='MY_S3_INT';

Answer

CREATE STAGE my_stage URL='s3://mybucket/path/' CREDENTIALS=(AWS_KEY_ID='...' AWS_SECRET_KEY='...');