COF-C03 Practice Test 20 — 15 Questions

Question 1

A company requires all data at rest within Snowflake to be encrypted using a customer-provided key. Which feature should they implement?

Accepted Answer

Tri-Secret Secure.. Tri-Secret Secure is the Snowflake feature that allows customers to manage their own encryption keys, providing an extra layer of security beyond Snowflake's standard managed keys. This is critical for highly regulated industries like finance or healthcare that require strict control over data encryption. By using their own keys, customers ensure that even if Snowflake infrastructure were compromised, the data remains inaccessible without the customer-held key materials.

Answer

Always Encrypted.

Answer

Transparent Data Encryption (TDE).

Answer

Client-Side Encryption.

Question 2

A data engineer needs to load a CSV file into a Snowflake table. The file has a header row, fields are separated by commas, and string values are enclosed in double quotes. The engineer wants to ensure the header row is skipped and the string values are loaded without quotes. Which FILE_FORMAT option should be used to achieve this?

Accepted Answer

SKIP_HEADER = 1 and FIELD_OPTIONALLY_ENCLOSED_BY = '"'. To load a CSV with a header and quoted strings, the file format must skip the header row and recognize the quote character as an optional enclosure. SKIP_HEADER = 1 handles the header, and FIELD_OPTIONALLY_ENCLOSED_BY = '"' ensures quotes are stripped from string values during loading. Other options either misinterpret the delimiter or fail to skip the header.

Answer

SKIP_HEADER = 1 and FIELD_DELIMITER = '"'

Answer

SKIP_HEADER = 1 and ESCAPE_UNENCLOSED_FIELD = '"'

Answer

SKIP_HEADER = 0 and FIELD_OPTIONALLY_ENCLOSED_BY = '"'

Question 3

What is the primary benefit of the Snowflake architecture's decoupling of storage and compute?

Accepted Answer

It enables independent scaling of storage and compute resources.. The separation of storage and compute allows organizations to scale these resources independently based on demand. You can store petabytes of data without paying for compute, and you can spin up massive compute clusters to run complex analytics without needing to replicate or move your data. This architectural flexibility is a core differentiator, enabling cost-effective data warehousing and high-performance processing without the rigid constraints of traditional on-premises or monolithic cloud databases.

Answer

It allows data to be stored in multiple cloud regions simultaneously.

Answer

It automatically encrypts all data at rest and in transit.

Answer

It eliminates the need for virtual warehouses.

Question 4

Which object type in Snowflake is required to store a compiled masking policy before it can be applied to a table column?

Accepted Answer

A masking policy object.. In Snowflake, masking policies are independent, schema-level objects. Before a policy can be enforced on a column, it must be created using the 'CREATE MASKING POLICY' command. This object encapsulates the logic for transformation and the conditional access rules. Once defined, the policy is then mapped to one or more columns via an 'ALTER TABLE' statement or during table creation, providing a modular approach to data governance.

Answer

A stored procedure.

Answer

A data masking role.

Answer

A secure function.

Question 5

Which Snowflake feature allows for secure, direct communication between a customer's virtual private cloud (VPC) and the Snowflake service without using the public internet?

Accepted Answer

Snowflake Private Link. For organizations with strict security and compliance requirements, avoiding the public internet for data transit is essential. Snowflake supports private connectivity options on major cloud providers to ensure that data remains within the cloud provider's network backbone, significantly reducing the attack surface for potential data interception.

Answer

Snowflake Data Exchange

Answer

SnowSQL Proxy Configuration

Answer

Network Policy Whitelisting

Question 6

A query is experiencing performance degradation, and the Query Profile indicates 'Remote Disk Spilling'. Which action is the most direct solution to resolve this specific bottleneck?

Accepted Answer

Scale up the virtual warehouse to a larger size.. Remote disk spilling occurs when the local SSD storage of a virtual warehouse is completely exhausted, forcing Snowflake to write intermediate data to slower remote cloud storage. This usually happens during large sorts, joins, or aggregations. Increasing the warehouse size provides more memory and local storage, ensuring that large intermediate result sets can be processed without hitting the high-latency remote storage layer.

Answer

Enable the Search Optimization Service on the table.

Answer

Implement a clustering key on the table's join columns.

Answer

Create a Materialized View for the underlying query.

Question 7

A data engineer needs to transform semi-structured JSON data stored in a VARIANT column into a relational table with separate columns for each attribute. The JSON contains nested objects and arrays. Which Snowflake feature should the engineer use to flatten the arrays and extract the nested attributes in a single SQL statement?

Accepted Answer

LATERAL FLATTEN with the INPUT => column and PATH => 'nested_array' arguments, combined with the VALUE and THIS keywords.. LATERAL FLATTEN is the primary Snowflake construct for expanding arrays within a VARIANT column. It produces one row per element in the array, and by using the VALUE or THIS keyword, the engineer can access the element's attributes. Combining this with a SELECT that references the original table's columns and the flattened output allows a single SQL statement to transform nested JSON into a relational result set.

Answer

Create a materialized view over the VARIANT column and query it with standard SQL.

Answer

Use the TRY_CAST function to convert the VARIANT to a string and then use string functions to parse the JSON.

Answer

Use the PARSE_JSON function to convert the VARIANT into a relational table automatically.

Question 8

A data administrator needs to identify which users have failed to log in successfully over the last 30 days due to authentication errors. Which Snowflake object should they query?

Accepted Answer

The LOGIN_HISTORY view.. The LOGIN_HISTORY table function in the ACCOUNT_USAGE schema provides a detailed audit trail of all connection attempts to the account. By filtering for the IS_SUCCESS column set to 'NO' and specifying the timeframe, the administrator can effectively monitor for potential brute-force attempts or configuration errors. This is a critical component for maintaining a robust security posture and ensuring compliance with organizational access monitoring policies.

Answer

The ACCESS_HISTORY view.

Answer

The QUERY_HISTORY view.

Answer

The SESSIONS view.

Question 9

Which property of the FILE_FORMAT object should be adjusted if a CSV file uses a semicolon (;) instead of a comma to separate values?

Accepted Answer

FIELD_DELIMITER. File formats in Snowflake are highly granular, allowing users to define the exact structure of their source data. Correctly identifying the delimiter is the most basic requirement for successful parsing. Snowflake provides specific parameters for field and record delimiters to accommodate a wide variety of data export formats.

Answer

RECORD_DELIMITER

Answer

FIELD_OPTIONALLY_ENCLOSED_BY

Answer

ESCAPE_CHARACTER

Question 10

A data engineer loads a batch of records into a table and then runs a query that filters on a column with a high-cardinality value. The query profile shows partition pruning eliminated most micro-partitions, but the query still scans many rows within the surviving partitions. The table has never been clustered. Which characteristic of Snowflake micro-partitions best explains why pruning was effective at the partition level yet still left many rows to scan?

Accepted Answer

Micro-partitions are immutable and store columnar data, so pruning uses min/max metadata while row-level filtering still requires reading surviving partitions.. Micro-partitions are immutable columnar storage units that Snowflake automatically maintains with per-column metadata, including min/max values. That metadata lets the optimizer prune partitions that cannot satisfy a filter even on unclustered tables. However, pruning is coarse: once a partition survives, its columnar data must be scanned for matching rows, which is why residual row scanning remains and why clustering can further improve pruning.

Answer

Micro-partitions are compressed and encrypted, so the optimizer cannot read metadata until the files are decompressed on the warehouse.

Answer

Micro-partitions are immutable and therefore require a full table scan whenever a filter column is not the clustering key.

Answer

Micro-partitions are hash-distributed across compute nodes, so pruning depends on which node holds the relevant hash bucket.

Question 11

Refer to the exhibit. When the COPY INTO command is executed, which field delimiter will Snowflake use to parse the files located in the 'data/' folder of the stage?

Accepted Answer

The comma character (,) because the FILE_FORMAT in the COPY command overrides the stage default.. In Snowflake, if a FILE_FORMAT is specified in both the stage definition and the COPY INTO statement, the options provided in the COPY INTO statement take precedence. This allows developers to define a general default format for a stage while still having the flexibility to override specific parameters for individual load jobs without altering the permanent stage object itself.

Answer

The pipe character (|) because it was defined first in the CREATE STAGE command.

Answer

Snowflake will return an error because the delimiters in the stage and the COPY command conflict.

Answer

Snowflake will attempt to auto-detect the delimiter, ignoring both specified values.

Question 12

Refer to the exhibit. A provider has executed these commands to prepare data for a consumer. What is the final mandatory step required before the consumer can access the 'campaign_stats' table?

Accepted Answer

The provider must execute: ALTER SHARE marketing_share ADD ACCOUNTS = <consumer_account_locator>;. After creating a share and granting the necessary object privileges (Database, Schema, and Table/View), the share must be associated with one or more consumer accounts. Without this step, the share exists in the provider account but is not visible or accessible to any external entity. The ALTER SHARE command is used to add the consumer's account to the share's distribution list.

Answer

The consumer must execute: CREATE DATABASE marketing_data FROM SHARE <provider_account>.marketing_share;

Answer

The provider must refresh the metadata of the 'marketing_share' object using the 'COMMIT SHARE' command.

Answer

The provider must grant the 'IMPORTED PRIVILEGES' role to the share for the campaign_stats table.

Question 13

A Snowflake administrator needs to grant a new analyst the ability to view all tables in the 'SALES' database and query them, but should not be able to modify any data or schema objects. Which sequence of privileges should the administrator grant to meet this requirement with least privilege?

Accepted Answer

Grant USAGE on the SALES database, USAGE on all schemas in the database, and SELECT on all present and future tables in the database.. To provide read-only access to all tables in a database, the administrator must grant USAGE on the database, USAGE on the schemas, and SELECT on the tables. Including future tables ensures that new tables are automatically accessible. This set of privileges allows querying without any modification rights, aligning with least privilege. Omitting schema USAGE or granting excessive roles would fail the requirement.

Answer

Grant the ACCOUNTADMIN role to the analyst, as it includes all necessary privileges for viewing and querying tables.

Answer

Grant the predefined role PUBLIC to the analyst, as PUBLIC has SELECT on all tables by default.

Answer

Grant USAGE on the SALES database and SELECT on all tables in the database, but no privileges on schemas.

Question 14

A Python developer wants to upload a Pandas DataFrame to a Snowflake table as efficiently as possible without manually writing files to a local stage. Which function from the Snowflake Connector for Python should be used?

Accepted Answer

write_pandas(). The Snowflake Connector for Python provides a high-level function called write_pandas specifically for this purpose. This function automates the process of converting the DataFrame to Parquet format, staging the files in a temporary internal stage, and executing the COPY INTO command. This abstraction simplifies the developer's workflow while maintaining high performance for large data transfers.

Answer

cursor.execute("INSERT INTO...")

Answer

snowflake.load_df()

Answer

pd.to_sql() with the default engine

Question 15

How does Snowflake's architecture provide high availability and disaster recovery across different geographic locations?

Accepted Answer

By enabling Database Replication and Failover to a secondary region or cloud.. Snowflake's architecture leverages 'Snowgrid' to enable cross-cloud and cross-region capabilities. This allows organizations to replicate databases and synchronize metadata across different regions or even different cloud providers. In the event of a regional outage, users can failover to a secondary region to maintain business continuity, ensuring that critical data applications remain available.

Answer

By automatically replicating all data to every cloud region globally by default.

Answer

Through the use of Fail-safe, which allows users to restore data from any region.

Answer

By using Virtual Warehouses that automatically span across multiple cloud providers.