COF-C03 Snowflake AI Data Cloud Features and Architecture Practice Test 2 — 15 Questions

Question 1

A security administrator needs to allow an external analytics team to read a specific set of columns in a table while preventing them from seeing customer email addresses and phone numbers. The team uses their own Snowflake account and must query the data directly without copying it. The administrator wants the masking to be enforced based on the querying user's role, even if the data is accessed through a view. Which Snowflake capability should the administrator implement?

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

Accepted Answer

Create a masking policy and attach it to the sensitive columns, then share the table through a Snowflake share with the external account.. Column-level masking policies in Snowflake evaluate the current role and return either the original value or a masked value, enabling role-based protection of sensitive fields. Because the policy is attached to the column itself, it remains in effect even when the table is shared to another Snowflake account, allowing direct queries while preventing unauthorized roles from seeing email addresses and phone numbers.

Answer

Use object tagging on the sensitive columns and grant the external role the TAG_READ privilege.

Answer

Create a secure view that excludes the sensitive columns and grant the external role access to the view.

Answer

Apply a row access policy to the table so the external team only sees rows that do not contain sensitive data.

Question 2

A data steward must ensure that analysts querying a table in the SALES database see masked values for a phone number column unless they belong to a specific role. The steward also needs the masking rule to apply consistently across all tables that use the same column name. Which Snowflake feature should be used?

Accepted Answer

A masking policy assigned to the column and applied consistently by column name.. Masking policies are schema-level objects that transform column values based on the querying role and can be attached directly to a column or via a tag. Assigning the same policy to columns with the same name across tables enforces consistent masking. Row access policies, secure views, and bare tags do not provide the same role-based column transformation across multiple tables.

Answer

A secure view that selects a constant placeholder value in place of the phone number column.

Answer

A row access policy attached to each table with a mapping table of authorized roles.

Answer

An object tag applied to the phone number column with a value of 'PII'.

Question 3

A Snowflake administrator needs to ensure that a critical data pipeline continues to run even if the primary virtual warehouse fails. The pipeline runs a series of SQL statements that load data from an external stage into a permanent table and then perform transformations. The administrator wants to configure automatic failover for the warehouse so that if the primary warehouse becomes unavailable, a secondary warehouse in a different region takes over. Which Snowflake feature should the administrator use?

Accepted Answer

Virtual warehouse replication with failover. Virtual warehouse replication with failover is designed to replicate a warehouse to a secondary region and automatically redirect queries if the primary fails. This ensures that compute resources remain available even during a regional outage, which is critical for continuous pipeline execution. Other options address scaling, data replication, or intra-region redundancy, but none provide automatic cross-region failover for the warehouse itself.

Answer

Multi-cluster warehouse with auto-scaling enabled

Answer

Database replication with failover enabled

Answer

Snowflake's built-in high availability within a single region

Question 4

A Snowflake user executes a query that joins a large fact table with a small dimension table. The query is performing poorly. The user notices that the dimension table is not being broadcast and the join is taking a long time. Which of the following actions is most likely to improve the query performance?

Accepted Answer

Use a larger virtual warehouse to provide more compute resources for the join.. When a join is performing poorly, one common cause is insufficient compute resources, leading to spilling to local or remote storage. Increasing the warehouse size provides more memory and compute power, which can allow the join to complete more efficiently. While other factors like data skew or join strategy can also affect performance, scaling up the warehouse is a straightforward action that can yield immediate improvements.

Answer

Add a clustering key to the large fact table on the join column.

Answer

Enable the USE_CACHED_RESULT parameter to force the use of cached results.

Answer

Ensure that statistics are up to date by running ANALYZE TABLE on both tables.

Question 5

A Snowflake user is querying a large table and notices that the query is slow. The user wants to improve performance by leveraging clustering. Which two actions can help improve the clustering and query performance? (Choose two.)

Accepted Answer

Enable automatic clustering on the table.. Defining a clustering key on frequently filtered columns and enabling automatic clustering are the two actions that improve clustering and query performance. The clustering key co-locates related data, reducing scan size, while automatic clustering maintains this organization as data changes. Manual sorting is temporary, increasing warehouse size does not address clustering, and RECLUSTER is not a valid command.

Answer

Increase the warehouse size to a larger size.

Answer

Manually sort the table by the desired columns using ORDER BY in a CREATE TABLE AS SELECT statement.

Answer

Use the RECLUSTER command to manually recluster the table.

Question 6

A data architect is designing a table that will store JSON documents with varying schemas. The architect wants to optimize query performance for frequently accessed fields within the JSON. Which Snowflake feature should be used to achieve this?

Accepted Answer

Use the VARIANT data type and create a materialized view that extracts the frequently accessed fields.. For semi-structured JSON data, using the VARIANT data type preserves flexibility, while a materialized view that extracts frequently accessed fields can pre-compute those values. Materialized views are automatically maintained and can significantly speed up queries that filter or join on those extracted fields.

Answer

Define a clustering key on the JSON column.

Answer

Store the JSON as a string and use the PARSE_JSON function in queries.

Answer

Enable search optimization on the JSON column.

Question 7

A company is evaluating Snowflake's data sharing capabilities. Which two statements accurately describe how Snowflake Secure Data Sharing works? (Choose two.)

Accepted Answer

Providers can revoke access to a share at any time, and consumers immediately lose access.. Secure Data Sharing allows providers to share data without copying it. Consumers access the data live and pay only for the compute used to query it. Providers can revoke access at any time, immediately cutting off consumer access. These characteristics make sharing efficient, secure, and cost-effective. The other options incorrectly describe data copying, region limitations, or consumer requirements.

Answer

Shares can be created only between accounts in the same Snowflake region.

Answer

Consumers must create a copy of the shared database before they can query it.

Answer

Data is copied from the provider's account to the consumer's account upon share creation.

Question 8

A developer runs a complex aggregation query on a large fact table and notices that the query takes 40 seconds the first time. The same query is executed again unchanged 10 minutes later and returns in under 2 seconds, even though no virtual warehouse was resized and the underlying table was not modified. Which Snowflake feature most directly explains the faster second execution?

Accepted Answer

The Query Result Cache in Cloud Services returned the previously computed result set for the identical query text.. The Query Result Cache in the Cloud Services layer persists query results for 24 hours, provided the query text is identical and the underlying data has not changed. When those conditions hold, Snowflake returns the cached result without provisioning or using warehouse compute, which explains why the second execution completed in under two seconds without any warehouse resize.

Answer

Snowflake rewrote the query into a materialized view and served the result from that view.

Answer

The local disk cache on the virtual warehouse retained the micro-partitions read by the first query.

Answer

The virtual warehouse automatically scaled out additional clusters to parallelize the second execution.

Question 9

A data engineer is loading data into a Snowflake table using the COPY command from an external stage. The engineer notices that the load is taking longer than expected and wants to improve performance. The data files are large (about 1 GB each) and there are many of them. Which action should the engineer take to optimize the load performance?

Accepted Answer

Increase the size of the virtual warehouse used for the load.. Increasing the warehouse size provides more compute resources, allowing Snowflake to parallelize the load across more threads and nodes. This directly improves performance for large data loads. Other options either do not address compute capacity or could degrade performance. Therefore, scaling up the warehouse is the correct action.

Answer

Enable auto-suspend on the warehouse to save credits.

Answer

Use a smaller warehouse to reduce contention.

Answer

Convert the files to a columnar format like Parquet before loading.

Question 10

A Snowflake user needs to share a database with another Snowflake account in a different region. The user wants to ensure that the shared data is always up-to-date and that the consumer can query it without copying. Which Snowflake feature should be used?

Accepted Answer

Database Replication. Database Replication is the correct feature for sharing data across regions. It replicates a database to another region, where it can be queried directly. The replica is read-only and can be refreshed to stay current. Data Sharing is limited to the same region, External Tables are for external storage, and Snowpipe is for data loading. Thus, Database Replication is the only option that meets the cross-region requirement.

Answer

Snowpipe

Answer

Data Sharing

Answer

External Tables

Question 11

A Snowflake user is querying a table that is updated frequently with new data. The user notices that queries are returning stale results from the query result cache. The user wants to ensure that queries always return the latest data. Which of the following actions will achieve this?

Accepted Answer

Disable the query result cache by setting USE_CACHED_RESULT to FALSE at the session level.. The query result cache stores the results of queries for 24 hours or until the underlying data changes. However, if the data is updated frequently, the cache may still return stale results if the updates are not detected. To guarantee fresh results, the user can disable the result cache at the session level by setting USE_CACHED_RESULT to FALSE. This forces every query to execute against the current data.

Answer

Recreate the table to invalidate the cache.

Answer

Use the RESULT_SCAN function to retrieve the latest results.

Answer

Set the warehouse to auto-suspend immediately after each query.

Question 12

A Snowflake administrator is configuring a new virtual warehouse to handle a mixed workload of small, frequent queries and occasional large, complex queries. The administrator wants to optimize performance and cost. Which TWO configurations should be applied? (Choose two.)

Accepted Answer

Enable multi-cluster warehouse with a minimum of 1 and maximum of 3 clusters.. For mixed workloads with variable concurrency, a multi-cluster warehouse with auto-scaling handles small, frequent queries by adding clusters as needed, while auto-suspend minimizes cost during idle times. Together, these settings balance performance and cost effectively.

Answer

Configure the warehouse to never suspend to ensure instant response.

Answer

Set the warehouse size to X-Large to handle the large, complex queries.

Answer

Enable the query acceleration service on the warehouse.

Question 13

A Snowflake user runs a query that joins a large fact table with a small dimension table. The query performance is poor. The user notices that the dimension table is frequently updated with new rows, and the fact table is static. The user wants to improve performance without significantly increasing credit consumption. Which technique should the user employ?

Accepted Answer

Use a larger virtual warehouse for the query.. Using a larger virtual warehouse provides more compute resources, which can accelerate the join by enabling greater parallelism. While larger warehouses consume credits at a higher rate, the query may finish faster, potentially reducing total credits consumed. Other options either do not address the join performance or introduce unnecessary overhead. Therefore, scaling up the warehouse is the most direct way to improve performance for this ad-hoc query.

Answer

Use a clustering key on the fact table's join column.

Answer

Create a materialized view on the join result.

Answer

Enable the search optimization service on the dimension table.

Question 14

A Snowflake user is designing a table that will store JSON data. The user wants to optimize query performance for queries that filter on specific JSON keys. Which approach is most effective?

Accepted Answer

Store the JSON in a VARIANT column and use a search optimization service on the keys.. The Search Optimization Service is specifically designed to improve performance of queries that filter on JSON keys within a VARIANT column. It creates an optimized search access path for equality and IN predicates. Materialized views, extracting columns, and using FLATTEN are not as effective or are not optimization techniques. Thus, enabling search optimization on the keys is the most effective approach.

Answer

Use the FLATTEN function in all queries that filter on JSON keys.

Answer

Store the JSON in a VARIANT column and create a materialized view that extracts the keys.

Answer

Extract the JSON keys into separate relational columns.

Question 15

A Snowflake administrator is setting up a new virtual warehouse for a data science team that will run a mix of short ad-hoc queries and long-running complex queries. The administrator wants to ensure optimal performance and cost-efficiency. Which TWO configurations should the administrator consider? (Choose two.)

Accepted Answer

Set the warehouse to auto-resume so it starts automatically when a query is submitted.. For a data science team with intermittent ad-hoc queries and long-running queries, auto-suspend and auto-resume are essential to balance cost and performance. Auto-suspend reduces costs during idle periods, while auto-resume ensures the warehouse is available when needed without manual intervention. These settings are configured on the virtual warehouse and are best practices for dynamic workloads.

Answer

Configure the warehouse to use a larger size (e.g., 4X-Large) to ensure fast performance for all queries.

Answer

Enable the query acceleration service to improve performance for long-running queries.

Answer

Enable multi-cluster warehouse with a minimum of 2 clusters to handle concurrent queries.