Free Snowflake COF-C03 Practice Exam with Questions & Answers | Set: 13

Snowflake ' s Data Exchange allows data providers to publish data that is accessible only to users they explicitly invite. This mechanism ensures that only approved users or Snowflake accounts have access, adding a layer of security and control. Invited users can query the shared data directly within their Snowflake account without requiring data copies, reducing redundancy and enhancing data accessibility within controlled boundaries. This selective sharing feature is part of Snowflake’s secure data-sharing capabilities.

A materialized view stores precomputed query results. Snowflake maintains the materialized view when its underlying base-table data changes. Because the source table changes only once per week, maintenance occurs much less frequently than the reporting query is executed.

Repeated queries can read the much smaller precomputed result instead of scanning the 100 TB base table hundreds of times per day. Snowflake’s optimizer can also automatically rewrite an eligible query to use a materialized view even when the query references the base table.

A standard view stores only its SQL definition and normally executes the underlying query whenever it is referenced. A temporary table would require manual creation, refresh, and lifecycle management. A dynamic table could materialize transformed results, but it requires refresh scheduling and refresh compute. For this repeated, stable aggregation pattern, the intended optimization object is a materialized view.

Materialized-view maintenance itself can consume credits, so it is most beneficial when query savings outweigh maintenance costs. The table’s infrequent load schedule makes that condition more likely.

The correct answer is A. Snowflake will start all available clusters .

A multi-cluster warehouse can run in Auto-scale mode or Maximized mode. When MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT are set to the same value greater than 1, the warehouse runs in Maximized mode.

Why A is correct:

In Maximized mode, Snowflake starts all configured clusters when the warehouse starts. For example, if both MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT are set to 3, Snowflake starts 3 clusters when the warehouse starts.

Clarification:

“Available clusters” here means all clusters configured for that warehouse, not every cluster in the Snowflake account.

Why the other options are incorrect:

B. Starting enough clusters based on workload describes Auto-scale behavior, not Maximized mode.

C. This describes a queued-query scaling behavior but does not apply when minimum and maximum cluster counts are equal.

D. This describes the Economy scaling policy behavior in Auto-scale mode, not Maximized mode.

Official Snowflake documentation reference:

Snowflake documentation explains that when the minimum and maximum number of clusters are the same, a multi-cluster warehouse runs in Maximized mode and starts all clusters when the warehouse starts.

The correct answer is C. To track how data has been transformed among columns .

Snowflake’s data lineage capability is used to understand how data flows and changes across objects. It helps users see relationships between source and target objects and understand how data is transformed, especially at the column level.

Corrected wording:

The original option said “records,” but Snowflake data lineage is more accurately described as tracking how data is transformed among columns, not individual row-level record transformations.

Why C is correct:

Data lineage helps track how data moves from source columns to target columns through operations such as views, CTAS statements, INSERT statements, and other transformations. This is useful for governance, impact analysis, auditing, and understanding downstream dependencies.

Why the other options are incorrect:

A. Tracking how records are read by a user is more closely related to access history or query history, not data lineage.

B. Tracking dependencies at the object level is related to object dependencies. Data lineage is more specific because it can show how data flows between columns and objects.

D. Snowflake data lineage is not primarily a row-level feature for tracking the impact of transformations on individual records.

Official Snowflake documentation reference:

Snowflake documentation describes data lineage as a governance capability that helps track how data moves and transforms across Snowflake objects, including column-level relationships.

When creating a Snowpark-optimized virtual warehouse in Snowflake, the minimum size requirement is Small. Snowpark is designed to handle data processing workloads efficiently, and the Small size ensures adequate resources for such tasks.

Virtual Warehouse Sizes:

Snowflake offers different sizes for virtual warehouses, ranging from X-Small to 6X-Large.

Each size corresponds to a specific level of compute resources.

Minimum Size Requirement for Snowpark:

A Small virtual warehouse is the minimum size required to optimize performance and resource allocation for Snowpark workloads.

This ensures that the warehouse has sufficient capacity to handle data processing and transformation tasks efficiently.

Snowflake Documentation: Virtual Warehouse Sizes

The correct answer is A. Multi-Factor Authentication, or MFA .

Multi-Factor Authentication reduces the risk of relying only on a username and password. Even if a password is compromised, MFA requires an additional verification factor before the user can access Snowflake.

Why A is correct:

MFA strengthens password-based authentication by requiring an extra factor, such as a push approval, passcode, or other verification method. Snowflake’s native MFA is powered by Duo Security.

Why the other options are incorrect:

B. Standard authentication usually refers to username and password authentication, which is exactly the risk the question wants to reduce.

C. Snowflake OAuth is an authorization/authentication integration method, but MFA is the direct feature designed to reduce password-only risk.

D. External OAuth can centralize authentication through an external identity provider, but the best Snowflake security feature for reducing password authentication risk is MFA.

Official Snowflake documentation reference:

Snowflake documentation describes MFA as an additional layer of security for user login and explains that Snowflake MFA is powered by Duo Security.

The search optimization service in Snowflake uses persistent data structures known as search access paths to improve the performance of point lookups. These structures enable efficient retrieval of data by reducing the amount of data scanned during queries.

Search Access Paths:

Search access paths are special indexing structures maintained by the search optimization service.

They store metadata about the distribution of data within tables, enabling faster lookups for specific values.

Point Lookups:

Point lookups involve searching for a specific value within a column.

By leveraging search access paths, Snowflake can quickly locate the exact micro-partition containing the value, minimizing the amount of data scanned.

Performance Improvement:

The use of search access paths significantly reduces query execution time for point lookups.

This is especially beneficial for large tables where scanning all micro-partitions would be computationally expensive.

Snowflake Documentation: Search Optimization Service

Snowflake Documentation: Understanding Search Access Paths

The correct answers are A. GET() and C. GET_PATH() .

Snowflake supports functions for extracting values from semi-structured data stored in a VARIANT, OBJECT, or ARRAY column. GET() and GET_PATH() can retrieve values by specifying keys, indexes, or paths.

Why A is correct:

GET() extracts a value from an object or array. For an object, it can retrieve the value associated with a specified key.

Example:

SELECT GET(src, ' customer ' )

FROM my_table;

Why C is correct:

GET_PATH() extracts a value from semi-structured data using a path expression. This is useful when specifying a hierarchy or nested path.

Example:

SELECT GET_PATH(src, ' customer.name ' )

FROM my_table;

Why the other options are incorrect:

B. TO_JSON() converts a VARIANT value to a JSON string. It does not extract values by hierarchy name.

D. PARSE_JSON() parses a string into a VARIANT. It does not extract an existing nested value.

E. LATERAL FLATTEN expands arrays or objects into rows. It is useful for traversing semi-structured data, but it is not a scalar extraction function that retrieves a value by specifying a hierarchy name.

Official Snowflake documentation reference:

Snowflake documentation describes GET() as a function that extracts a value from an object or array, and GET_PATH() as a function that extracts a value from semi-structured data using a path name.

The correct answer is D. To allow different authentication methods for multiple user groups .

Multiple identity providers allow an organization to support different authentication configurations for different users, departments, partners, or applications. This is useful when separate user groups authenticate through different SSO providers or require different authentication flows.

Why D is correct:

Configuring multiple IdPs allows Snowflake to support multiple federated authentication integrations and assign appropriate authentication behavior to different users or groups.

Why the other options are incorrect:

A. Complex password rules are controlled by password policies, not by configuring multiple IdPs.

B. Identity providers affect authentication, not query performance.

C. Encryption key management is handled by Snowflake encryption features and key management options, not IdP configuration.

Official Snowflake documentation reference:

Snowflake documentation describes federated authentication and multiple identity provider support as a way to configure different SSO/authentication integrations for different users.

To download a 5 GB table into a single file most efficiently in Snowflake, you should set the SINGLE parameter to TRUE. This parameter ensures that the COPY INTO command outputs the result into a single file, regardless of the file size. This approach is more efficient than relying on the default MAX_FILE_SIZE setting, which would split the output into multiple files.

Snowflake Documentation: COPY INTO < location >

The correct answers are C. To determine if the pipe is suspended and D. To troubleshoot errors in a pipe execution .

SYSTEM$PIPE_STATUS returns status information about a Snowpipe pipe. This function is useful for monitoring Snowpipe health and troubleshooting ingestion issues.

Why C is correct:

The output of SYSTEM$PIPE_STATUS includes information that can indicate whether a pipe is paused or running, helping determine whether ingestion is suspended.

Why D is correct:

The function can return diagnostic information about pipe execution, including pending files, notification channel status, and error-related information. This helps troubleshoot Snowpipe loading problems.

Example:

SELECT SYSTEM$PIPE_STATUS( ' my_pipe ' );

Why the other options are incorrect:

A. The pipe name is supplied as input to the function; the function is not primarily used to identify the pipe name.

B. SYSTEM$PIPE_STATUS is informational only. It does not modify pipe configuration. Configuration changes are made with commands such as ALTER PIPE.

E. Snowpipe loads data into tables. It does not unload data. Listing staged files is done using stage listing commands or directory tables, not SYSTEM$PIPE_STATUS.

Official Snowflake documentation reference:

Snowflake documentation describes SYSTEM$PIPE_STATUS as a system function that retrieves a JSON representation of the current status of a pipe, useful for monitoring and troubleshooting Snowpipe.

The correct answer is D. Infrastructure management .

Snowflake’s architecture includes the storage layer, compute layer, and Cloud Services layer. The Cloud Services layer coordinates many system-level services required to operate Snowflake.

Why D is correct:

Infrastructure management is handled by the Cloud Services layer. Other Cloud Services responsibilities include authentication, metadata management, query parsing and optimization, access control, and transaction management.

Why the other options are incorrect:

A. Data storage is handled by the database storage layer.

B. File compression is related to storage and data loading/unloading behavior, not the primary Cloud Services responsibility tested here.

C. Query processing is performed by virtual warehouses in the compute layer. Query parsing and optimization happen in Cloud Services, but execution/processing happens in compute.

Official Snowflake documentation reference:

Snowflake documentation describes the Cloud Services layer as managing services such as authentication, infrastructure management, metadata management, query parsing and optimization, access control, and transaction management.

The Fail-safe feature in Snowflake is the last-resort data recovery option that should only be used when all other options, such as Time Travel or Cloning, have been exhausted. Fail-safe provides a 7-day recovery period after the Time Travel window has expired but requires Snowflake Support intervention for data recovery. It is intended for disaster recovery and carries additional storage costs.

The correct answer is C. The privileges accessed by the PUBLIC role .

Every Snowflake user is automatically granted the PUBLIC role. This means that when a user receives a custom role, the user still also has access to privileges granted to PUBLIC.

Why C is correct:

The PUBLIC role is automatically available to all users. Therefore, in addition to privileges granted through the custom role, the user also has privileges available through PUBLIC.

Why the other options are incorrect:

A. Granting a custom role to a user does not give the user ACCOUNTADMIN privileges.

B. This describes the privileges provided by the custom role itself, not the additional privileges the user has by default.

D. Granting a custom role does not give the user USERADMIN privileges.

Official Snowflake documentation reference:

Snowflake documentation explains that the PUBLIC role is automatically granted to every user and every role. Privileges granted to PUBLIC are available to all users.

The correct answer is A. View .

In Snowflake data sharing, secure objects are normally required when sharing views or functions because secure objects prevent sensitive underlying query details from being exposed. However, when the SECURE_OBJECTS_ONLY property is set to FALSE, non-secure views can be shared.

Why A is correct:

A view can be shared when SECURE_OBJECTS_ONLY = FALSE. This setting relaxes the requirement that only secure views be included.

Why the other options are incorrect:

B. Stored procedures are not standard objects shared through Snowflake secure data sharing in the same way as tables and views.

C. Storage integration access is not shared as a database object through a Snowflake share.

D. UDF sharing has security restrictions, and the safest exam answer here is a view when SECURE_OBJECTS_ONLY is disabled.

Official Snowflake documentation reference:

Snowflake documentation explains that shares can include database objects such as tables and views. Secure object requirements apply to certain shared objects, and the SECURE_OBJECTS_ONLY property controls whether only secure objects are allowed.

TheGET_PRESIGNED_URLfunction in Snowflake generates a pre-signed URL for a file in an internal stage. This URL can be shared with external tools or users who do not have direct access to Snowflake, allowing them to download the file.

Generate Pre-Signed URL:

SELECT GET_PRESIGNED_URL(@my_stage/file.txt);

Share the URL:The generated URL can be shared with external users or applications, enabling them to access the file directly.

When attempting to load large files, such as a 125 GB file, into Snowflake using SnowSQL, the process might encounter limitations related to the maximum execution time for queries or data loading operations. If the loading process exceeds this time limit (typically around 24 hours), it could be aborted without committing any part of the file to the database. This behavior is designed to prevent indefinite resource consumption and to maintain system stability, emphasizing the need for optimizing data load operations, possibly through file segmentation or parallel loading strategies.