Snowflake COF-C02 Practice Test - Questions Answers, Page 29

Snowflake uses a technique called pruning to limit the number of micro-partitions scanned by each query.Pruning effectively filters out unnecessary micro-partitions based on the query's filter conditions, which can significantly improve query performance by reducing the amount of data scanned1.

The command used to unload files from an internal or external stage to a local file system in Snowflake is the GET command.This command allows users to download data files that have been staged, making them available on the local file system for further use23.

Snowflake integrates Duo Security to support Multi-Factor Authentication (MFA).This feature provides increased login security for users connecting to Snowflake, and it is managed completely by Snowflake without the need for users to sign up separately with Duo4.

In Snowflake, when queries begin to queue in a multi-cluster virtual warehouse, an additional compute cluster starts immediately if the warehouse is set to auto-scale mode.This mode allows Snowflake to automatically add or resume additional clusters as soon as the workload increases, and similarly, shut down or pause the additional clusters when the load decreases

The Snowflake object that helps evaluate virtual warehouse performance impacted by query queuing is the Information_schema.warehouse_load_history.This view provides historical data about the load on a warehouse, including the average number of queries that were running or queued within a specific interval, which can be used to assess performance and identify potential issues with query queuing3.

To find out which numeric values are present in both tables, the INTERSECT set operator should be used.This operator returns rows from one query's result set which also appear in another query's result set, effectively finding the common elements between the two tables45.

Snowflake recommends using columns with very high cardinality and those that are most actively used in selective filters as clustering keys. High cardinality columns have a wide range of unique values, which helps in evenly distributing the data across micro-partitions. Columns used in selective filters help in pruning the number of micro-partitions to scan, thus improving query performance. References: Based on general database optimization principles.

The Snowflake Cloud Services layer coordinates activities within the Snowflake account. It is responsible for tasks such as authentication, infrastructure management, metadata management, query parsing and optimization, and access control. References: Based on general cloud database architecture knowledge.

Snowflake recommends creating objects with a role that has the necessary privileges and is not overly permissive. SYSADMIN is typically used for managing system-level objects and operations. Creating objects with a custom role and granting this role to SYSADMIN allows for more granular control and adherence to the principle of least privilege. References: Based on best practices for database object ownership and role management.