COF-C03 Practice Test 3 — 25 Questions

Question 1

A data engineer needs to load a 4.2 GB uncompressed CSV file from an internal stage into a Snowflake table. The file cannot be split because the CSV has embedded newlines within quoted fields. The engineer wants to maximize load performance. What should the engineer do?

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

Accepted Answer

Split the file into multiple smaller files (e.g., 100-250 MB compressed) and stage them together, then run a single COPY INTO command referencing the stage path.. Snowflake achieves load parallelism by processing multiple files concurrently, not by splitting a single file. When a file cannot be split due to embedded newlines, the engineer must manually divide it into multiple smaller files and stage them together. A single COPY INTO command can then load all files in parallel, significantly improving performance over loading one large file.

Answer

Load the file using the Snowpipe REST API with the insertFiles endpoint, which automatically splits large files into chunks for parallel ingestion.

Answer

Use a larger virtual warehouse and load the single file; Snowflake will automatically parallelize the load across all compute resources in the warehouse.

Answer

Compress the file with gzip and load it as a single file; Snowflake will automatically split the compressed file across all nodes in the warehouse.

Question 2

When loading semi-structured data like Parquet into a Snowflake table, what is a primary advantage of using Parquet over CSV for the ingestion process?

Accepted Answer

Parquet files contain schema metadata, allowing for easier mapping of complex data.. Parquet is a columnar storage format that includes embedded schema information and metadata about the data it contains. This allows Snowflake to automatically detect column names and types during the load process. Unlike CSV, which is a flat text format, Parquet's structure enables more efficient data mapping and better performance during ingestion of complex, nested datasets.

Answer

Parquet files are always smaller than CSV files regardless of the data content.

Answer

Parquet files can be loaded using the PUT command directly into a table.

Answer

Parquet is the only format that supports the ON_ERROR = CONTINUE parameter.

Question 3

A data steward needs to ensure that a column containing email addresses is masked for all users except those with the role 'COMPLIANCE_OFFICER'. The masking should show a fixed string '****' for unauthorized users. Which Snowflake feature should be used?

Accepted Answer

Masking policy. A masking policy in Snowflake allows column-level security by dynamically masking data based on the user's role or other conditions. By applying a masking policy to the email column that returns '****' for all roles except 'COMPLIANCE_OFFICER', the data steward ensures that only authorized users see the actual email addresses. This is the correct feature for column-level masking.

Answer

Object tagging

Answer

Secure view

Answer

Row access policy

Question 4

Refer to the exhibit. What is the primary advantage of using the VARIANT data type in this scenario?

Accepted Answer

It enables seamless querying of semi-structured data using SQL.. The VARIANT data type in Snowflake allows for the storage of semi-structured data like JSON, Avro, or Parquet in a native format. By using VARIANT, Snowflake parses the data upon ingestion and stores it in an optimized internal representation. This enables users to query semi-structured data using standard SQL, providing a bridge between the flexibility of NoSQL and the reliability and performance of relational databases.

Answer

It forces the data into a rigid relational schema.

Answer

It converts all data to string format for storage.

Answer

It increases storage costs by requiring a fixed-width format.

Question 5

A provider wants to share a database with a consumer but must prevent the consumer from seeing the database's table and schema names in its own account. The provider also wants the consumer's queries to be isolated from the provider's own warehouse usage. Which approach satisfies both requirements?

Accepted Answer

Create a secure view that projects the data and grant only the view to the share, then let the consumer use its own warehouse.. A secure view exposes only the projected result, so the consumer never sees the underlying table or schema names. Because the consumer creates a database from the share and queries it with its own warehouse, the provider's warehouse is untouched. Together, the secure view and consumer-owned warehouse meet both the naming-concealment and usage-isolation requirements.

Answer

Use a database replication task to copy the data into the consumer's account.

Answer

Create a reader account for the consumer and grant the consumer's role access to the provider's warehouse.

Answer

Grant SELECT on each table directly to the share and let the consumer create a database from the share.

Question 6

A user runs a query that filters on a column with a high cardinality and the table is not clustered. The query scans a large number of micro-partitions. Which action would most directly reduce the number of micro-partitions scanned?

Accepted Answer

Add a clustering key on the filtered column.. Clustering keys physically sort data so that similar values are co-located in micro-partitions. This enables the optimizer to prune partitions based on filter predicates, directly reducing the number of micro-partitions scanned. Larger warehouses or caching do not change the volume of data scanned for the initial query.

Answer

Use a larger warehouse with multi-cluster scaling.

Answer

Increase the size of the virtual warehouse.

Answer

Enable result caching for the session.

Question 7

A data engineer is working with a table that contains a VARIANT column storing arrays of JSON objects. The engineer needs to produce a report that lists each object's attributes in separate rows. Which Snowflake function should the engineer use to transform the array into multiple rows?

Accepted Answer

FLATTEN. The FLATTEN function is specifically designed to explode semi-structured arrays and objects into multiple rows. When applied to a VARIANT column containing an array of objects, it returns one row per object, allowing the engineer to access each object's attributes. This is the correct approach for transforming nested data into a relational format for reporting. Other functions either create semi-structured data or aggregate rows, which are not appropriate here.

Answer

OBJECT_CONSTRUCT

Answer

PARSE_JSON

Answer

ARRAY_AGG

Question 8

A data engineer loads JSON files from an external Azure stage into a table with a single VARIANT column. The JSON documents are newline-delimited, and each line is a separate object. Which file format type and option should be specified to correctly parse one JSON object per line?

Accepted Answer

FILE_FORMAT = (TYPE = 'JSON') with the default settings. Snowflake's JSON file format parses newline-delimited JSON by default, treating each line as a distinct document and loading it as a row. No additional option is required for this common layout. STRIP_OUTER_ARRAY is only relevant when the file is a single JSON array, and other JSON options control field handling rather than record splitting.

Answer

FILE_FORMAT = (TYPE = 'JSON') with STRIP_NULL_VALUES = TRUE

Answer

FILE_FORMAT = (TYPE = 'JSON') with STRIP_OUTER_ARRAY = TRUE

Answer

FILE_FORMAT = (TYPE = 'CSV') with FIELD_DELIMITER = '
'

Question 9

Refer to the exhibit. What is the impact of changing the MAX_CONCURRENCY_LEVEL parameter on this warehouse?

Accepted Answer

It allows each cluster to run more queries simultaneously.. The MAX_CONCURRENCY_LEVEL parameter controls the number of concurrent queries a single cluster can handle before queuing begins. By increasing this value, you allow more queries to run in parallel on the same compute resources. However, this also divides the CPU and memory among more queries, which can lead to individual query performance degradation if the workload is compute-intensive, making it a critical tuning parameter for balancing throughput and performance.

Answer

It increases the number of clusters in the warehouse.

Answer

It scales the warehouse size (e.g., X-SMALL to SMALL).

Answer

It disables the auto-scaling capabilities of the warehouse.

Question 10

A company wants to monetize its data by making it available to other Snowflake customers through a centralized, public platform. Which Snowflake feature should they use?

Accepted Answer

Snowflake Marketplace. The Snowflake Marketplace is a public forum where providers can list data sets, services, and applications. It facilitates the discovery and consumption of third-party data and allows providers to reach a wide audience of Snowflake users. This architecture leverages Snowflake's secure data sharing technology to enable seamless commercial transactions and data exchange across different organizations.

Answer

Snowflake Data Exchange

Answer

Direct Data Sharing

Answer

Snowflake Snowpark

Question 11

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?

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 12

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 13

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 14

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 15

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 16

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 17

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 18

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 19

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 20

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