COF-C03 Practice Exam 3 — 20 Questions

Question 1

How does Snowflake's architecture provide the ability to run multiple concurrent workloads without resource contention?

Accepted Answer

By deploying separate virtual warehouses for each workload.. Snowflake's multi-cluster warehouse architecture allows for complete isolation of compute resources. Each workload can be assigned to a specific virtual warehouse, ensuring that one workload's resource usage does not impact the performance of another. This decoupling is a cornerstone of the Snowflake AI Data Cloud, enabling organizations to handle diverse workloads—from heavy data engineering to real-time analytics—simultaneously without the risk of 'noisy neighbor' scenarios affecting user experience.

Answer

By using a single large warehouse for all workloads.

Answer

By relying on the underlying cloud provider's hypervisor.

Answer

By implementing software-based resource queues in the storage layer.

Question 2

A data engineer needs to ensure that sensitive PII columns are masked for all users except for a specific group of HR analysts. Which Snowflake feature is the most efficient and scalable solution to implement this requirement?

Accepted Answer

Implement Dynamic Data Masking policies on the sensitive columns.. Dynamic Data Masking (DDM) allows centralized management of data access policies based on the user's role. By attaching a masking policy to a column, Snowflake automatically evaluates the user's role at query runtime. This approach is superior to static masking or manual view management because it centralizes governance, minimizes data duplication, and ensures that security policies remain consistent regardless of how the user accesses the underlying table.

Answer

Create secure views for every user role in the account.

Answer

Use the UNMASK function in every query selecting sensitive data.

Answer

Apply Row-Level Security to hide rows containing sensitive PII.

Question 3

Which administrative role should be used to manage the lifecycle of warehouses and databases, while strictly avoiding the management of users and roles?

Accepted Answer

SYSADMIN. The SYSADMIN role is the standard role for creating and managing data objects such as databases, schemas, tables, and warehouses. It is distinct from the SECURITYADMIN role, which manages user identities and permissions. This separation of duties is a fundamental pillar of Snowflake's security architecture, ensuring that operational and administrative control over data objects is kept separate from the management of user access, thereby mitigating the risk of privilege escalation and internal misuse.

Answer

ACCOUNTADMIN

Answer

USERADMIN

Answer

SECURITYADMIN

Question 4

An administrator needs to restrict access to sensitive PII data. Which TWO of the following are valid approaches to implement governance in Snowflake?

Accepted Answer

Apply a Row Access Policy to the table.. Snowflake provides several layers of defense-in-depth to secure sensitive information. Row Access Policies filter which rows a user can see, while Dynamic Data Masking transforms column data based on user privileges. Using these in combination allows architects to build a highly restrictive environment where users only interact with the exact data subsets and column values they are authorized to access, complying with strict regulatory data privacy standards.

Answer

Use physical data partitioning to store PII in separate tables.

Answer

Assign the ACCOUNTADMIN role to all data stewards.

Answer

Export data to an encrypted S3 bucket for security.

Question 5

Refer to the exhibit. Why were zero credits consumed for the query execution?

Accepted Answer

The query was satisfied by the Query Result Cache.. The exhibit indicates that the query utilized the Query Result Cache, as evidenced by 'cached_result': 'TRUE'. When Snowflake identifies an identical query whose results are still in the cache and the underlying data has not changed, it returns the stored result set. This bypasses the virtual warehouse compute layer entirely, resulting in zero credit consumption, which is a highly efficient way to serve repeat dashboard queries.

Answer

The warehouse WH_XS is in a suspended state.

Answer

The data was already present in the warehouse local disk cache.

Answer

The query failed during the planning phase.

Question 6

When a virtual warehouse spills data to local disk, what does this indicate about the query and resource allocation?

Accepted Answer

The memory capacity of the warehouse nodes is insufficient.. Spilling to local disk occurs when the data required for an operation, such as a large join or sort, exceeds the memory capacity of the warehouse nodes. This significantly degrades performance because disk I/O is much slower than memory access. Recognizing this behavior is vital for performance tuning, as it signals that the current warehouse size is insufficient for the volume of data being processed, necessitating a larger warehouse or a more efficient query design.

Answer

The warehouse is too large.

Answer

The data is not properly clustered.

Answer

The result cache is full.

Question 7

A data engineer needs to transform semi-structured data stored in a VARIANT column that contains an array of JSON objects into a relational table with one row per object. The engineer wants to use a Snowflake function that can expand the array into multiple rows. Which function should be used?

Accepted Answer

FLATTEN. FLATTEN is the correct function to expand an array of objects into multiple rows. It is typically used in the FROM clause with LATERAL, allowing each element of the array to become a separate row while preserving access to the original row's columns. The other functions either create VARIANT values or aggregate rows, not expand arrays.

Answer

PARSE_JSON

Answer

OBJECT_CONSTRUCT

Answer

ARRAY_AGG

Question 8

A consumer has mounted a shared database named 'SALES_DATA_SHARED'. They need to add a new column to one of the tables in this database to store local annotations. Which statement best describes the outcome if they attempt this?

Accepted Answer

The operation will fail because shared databases are read-only for consumers.. Shared databases in Snowflake are strictly read-only for the consumer. The consumer cannot perform any Data Definition Language (DDL) operations like ALTER TABLE, nor any Data Manipulation Language (DML) operations like INSERT or UPDATE on the shared objects. This architecture ensures the integrity of the provider's data and maintains a single version of truth across all consumers.

Answer

The operation will succeed, but the changes will only be visible to the consumer's account.

Answer

The operation will succeed only if the provider has granted the 'MODIFY' privilege to the share.

Answer

The operation will fail unless the consumer is using the 'ACCOUNTADMIN' role.

Question 9

Which of the following describes the purpose of 'Object Tagging' in Snowflake?

Accepted Answer

To logically group objects for discovery and compliance reporting.. Object tagging allows administrators to assign metadata to objects (like tables, columns, or warehouses) to facilitate discovery, tracking, and governance. Tags are useful for identifying sensitive data, tracking costs, or enforcing compliance. This is a crucial feature for large organizations that need to report on data usage, lifecycle, and sensitivity across thousands of objects, making it easier to manage and audit data at scale.

Answer

To encrypt sensitive data at the column level.

Answer

To restrict user access based on their network location.

Answer

To automatically optimize query performance.

Question 10

What is the role of the Cloud Services layer within the Snowflake architecture when a user submits a query?

Accepted Answer

It coordinates the query compilation and optimization.. The Cloud Services layer acts as the 'brain' of Snowflake, handling authentication, metadata management, access control, and query optimization. When a user submits a query, this layer parses, compiles, and optimizes the execution plan before dispatching it to a virtual warehouse. Understanding this layer is essential because it is the common entry point for all operations, ensuring consistent security, policy enforcement, and efficient query planning regardless of the compute power used.

Answer

It executes the physical join and aggregation operations.

Answer

It persists the raw data in micro-partitions.

Answer

It manages the local SSD cache of the warehouse.

Question 11

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?

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 12

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 13

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 14

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 15

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 16

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 17

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 18

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 19

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 20

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