Snowflake COF-C03 Dumps

The Query Profile in Snowflake provides a graphical representation and statistics for each component of the query ' s execution plan. This includes details such as the execution time, the number of rows processed, and the amount of data scanned for each operation within the query. The Query Profile is a crucial tool for understanding and optimizing the performance of queries, as it helps identify potential bottlenecks and inefficiencies.

Purpose:TheBUILD_STAGE_FILE_URLfunction generates a temporary, pre-signed URL that allows you to access a file within a Snowflake stage (internal or external). This URL requires authentication to use.

Key Points:

Security: The URL has a limited lifespan, enhancing security.

Use Cases: Sharing staged data with external tools or applications, or downloading it directly.

Snowflake Documentation

To access a file URL from an external stage in Snowflake, the minimum permission required is USAGE on the stage object. USAGE permission allows a user to reference the stage in SQL commands, necessary for actions like listing files or loading data from the stage, but does not permit the user to alter or drop the stage.

Query pruning in Snowflake refers to the optimization technique where the system reduces the amount of data scanned by a query based on the query conditions. This typically involves skipping unnecessary data partitions that do not contribute to the query result. The effectiveness of this technique can be observed through:

Option A: Partitions scanned. This statistic indicates how many data partitions were actually scanned as a result of query pruning, showing the optimization in action.

Option C: Bytes scanned. This measures the volume of data physically read during query execution, and a reduction in this number indicates effective query pruning, as fewer bytes are read when unnecessary partitions are skipped.

Options B, D, and E do not directly relate to observing the effects of query pruning. " Partitions total " shows the total available, not the impact of pruning, while " Bytes read from result " and " Bytes written " relate to output rather than the efficiency of data scanning.

By default, the ACCOUNTADMIN role in Snowflake has the ability to create a share from a shared database. This role has the highest level of access within a Snowflake account,including the management of all aspects of the account, such as users, roles, warehouses, and databases, as well as the creation and management of shares for secure data sharing with other Snowflake accounts.

To utilize the search optimization service in Snowflake, the correct syntax for granting privileges to a role involves specific commands that include adding search optimization capabilities:

Option C: GRANT ADD SEARCH OPTIMIZATION ON SCHEMA < schema_name > TO ROLE < role > . This command grants the specified role the ability to implement search optimization at the schema level, which is essential for enhancing search capabilities within that schema.

Options A and B do not include the correct verb " ADD, " which is necessary for this specific type of grant command in Snowflake. Option D incorrectly mentions the database level, as search optimization privileges are typically configured at the schema level, not the database level.

To unload data from a table into one or more files in a named stage, theCOPY INTO < location > command should be used. This command exports the result of a query, such as selecting all rows from a table, into files stored in the specified stage. TheCOPY INTOcommand is versatile, supporting various file formats and compression options for efficient data unloading.

The Query Profile in Snowflake can identify common query problems, including:

B. Inefficient pruning: This refers to the inability of a query to effectively limit the amount of data being scanned, potentially leading to suboptimal performance.

D. Queries too large to fit in memory: This indicates that a query requires more memory than is available in the virtual warehouse, which can lead to spilling to disk and degraded performance.

The Query Profile helps diagnose these issues by providing detailed execution statistics and visualizations, aiding in query optimization and troubleshooting.

In Snowflake, overloading refers to the creation of multiple User-Defined Functions (UDFs) with the same name but differing in the number or types of their arguments. This feature allows for more flexible function usage, as Snowflake can differentiate between functions based on the context of their invocation, such as the types or the number of arguments passed. Overloading helps to create more adaptable and readable code, as the same function name can be used for similar operations on different types of data.

When initiating a session in Snowflake, the system determines the current role based on the user ' s connection details and role assignments. If a user specifies a role during the connection, and that role is already granted to them, Snowflake sets it as the current role for the session. Alternatively, if no role is specified during the connection, but the user has a default role assigned, Snowflake will use this default role as the current session role. These mechanisms ensure that users operate within their permissions, enhancing security and governance within Snowflake environments.

In Snowflake, semi-structured data is optimally stored using specific data types that are designed to handle the flexibility and complexity of such data. TheVARIANTdata type can store structured and semi-structured data types, including JSON, Avro, ORC, Parquet, or XML, in a single column. TheARRAYdata type, on the other hand, is suitable for storing ordered sequences of elements, which can be particularly useful for semi-structured data types like JSON arrays. These data types provide the necessary flexibility to store and query semi-structured data efficiently in Snowflake.

TheSYSTEM$GET_PRIVATELINKfunction in Snowflake provides proxy information necessary for configuring PrivateLink connections, which can protect Snowsight as well as other Snowflake services. PrivateLink enhances security by allowing Snowflake to be accessed via a private connection within a cloud provider’s network, reducing exposure to the public internet.

UTF-8 is the only supported character set for loading and unloading data from all supported file formats in Snowflake. UTF-8 is a widely used encoding that supports a large range of characters from various languages, making it suitable for internationalization and ensuring data compatibility across different systems and platforms.

The search optimization service in Snowflake supports use cases involving conjunctions (AND) of multiple equality predicates. This service enhances the performance of queries that include multiple equality conditions by utilizing search indexes to quickly filter data without scanning entire tables or partitions. It ' s particularly beneficial for improving the response times of complex queries that rely on specific data matching across multiple conditions.

To verify the privileges that have been granted to a specific role in Snowflake, the correct SQL command isSHOW GRANTS TO ROLE < Role Name > . This command lists all the privileges granted to the specified role, including access to schemas, tables, and other database objects. This is a useful command for administrators and users with sufficient privileges to audit and manage role permissions within the Snowflake environment.

Snowflake ' s architecture is unique in that it combines elements of both traditional shared-disk and shared-nothing database architectures. This hybrid approach allows Snowflake to offer the scalability and performance benefits of a shared-nothing architecture (with compute and storage separated) while maintaining the simplicity and flexibility of a shared-disk architecture in managing data across all nodes in the system. This results in an architecture that provides on-demand scalability, both vertically and horizontally, without sacrificing performance or data cohesion.

To easily transfer data from a production account to a non-production account in Snowflake within the same cloud provider region, creating a data share is the most efficient approach. Data sharing allows for live, read-only access to selected data objects from the production account to the non-production account without the need to duplicate or move the actual data. This method facilitates seamless access to the data for development, testing, or analytics purposes in the non-production environment.

Valid sub-clauses to theOVERclause for a window function in SQL are:

C. ORDER BY: This clause specifies the order in which the rows in a partition are processed by the window function. It is essential for functions that depend on the row order, such as ranking functions.

D. PARTITION BY: This clause divides the result set into partitions to which the window function is applied. Each partition is processed independently of other partitions, making it crucial for functions that compute values across sets of rows that share common characteristics.

These clauses are fundamental to defining the scope and order of data over which the window function operates, enabling complex analytical computations within SQL queries.

The VALIDATE table function in Snowflake would typically use a unique identifier, such as a UUID_STRING, as an input argument. This function is designed to validate the data within a table against a set of constraints or conditions, often requiring a specific identifier to reference the particular data or job being validated.

Network policies in Snowflake are defined as a set of rules that manage the network-level access to Snowflake accounts. These rules specify which IP addresses or IP ranges are permitted to connect to Snowflake, enhancing the security of Snowflake accounts by preventing unauthorized access. Network policies are an essential aspect of Snowflake ' s security model, allowing administrators to enforce access controls based on network locations.

Transient tables in Snowflake are designed for temporary or intermediate workloads with the following characteristics:

B. Transient tables can be cloned to permanent tables: This feature allows users to create copies of transient tables for permanent use, providing flexibility in managing data lifecycles.

C. Transient tables persist until they are explicitly dropped: Unlike temporary tables that exist for the duration of a session, transient tables remain in the database until explicitly removed by a user, offering more durability for short-term data storage needs.

The Cloud Services layer in Snowflake is responsible for a wide range of services that facilitate the management and use of Snowflake, including:

D. User authentication: This service handles identity and access management, ensuring that only authorized users can access Snowflake resources.

E. Infrastructure management: This service manages the allocation and scaling of resources to meet user demands, including the management of virtual warehouses, storage, and the orchestration of query execution.

These services are part of Snowflake ' s fully managed, cloud-based architecture, which abstracts and automates many of the complexities associated with data warehousing.

A Pre-signed URL provides access to files stored in Snowflake without requiring authorization at the time of access. This feature allows users to generate a URL with a limited validity period that grants temporary access to a file in a secure manner. It ' s particularly useful for sharing data with external parties or applications without the need for them to authenticate directly with Snowflake.

Snowflake supports a variety of file formats for unloading semi-structured data, among which Parquet and Newline Delimited JSON (NDJSON) are two widely used formats.

B. Binary file in Parquet: Parquet is a columnar storage file format optimized for large-scale data processing and analysis. It is especially suited for complex nested data structures.

D. Newline Delimited JSON (NDJSON): This format represents JSON records separated by newline characters, facilitating the storage and processing of multiple, separate JSON objects in a single file.

These formats are chosen for their efficiency and compatibility with data analytics tools and ecosystems, enabling seamless integration and processing of exported data.

The Search Optimization Service in Snowflake is designed to improve the performance of specific types of queries, particularly those involving extensive sets of WHERE conditions. By maintaining a search index on tables, this service can accelerate lookup and analytical queries, making it a valuable feature for optimizing query performance and reducing execution times for complex searches.

When unloading floating-point number columns to CSV or JSON files, Snowflake truncates the values to approximately 15 significant digits with 9 digits following the decimal point, which can be represented as (15,9). This ensures a balance between accuracy and efficiency in representing floating-point numbers in text-based formats, which is essential for data interchange and processing applications that consume these files.

The VARIANT data type in Snowflake is used to store JSON key-value pairs along with other semi-structured data formats like AVRO, BSON, and XML. The VARIANT data type allows for flexible and dynamic data structures within a single column, accommodating complex and nested data. This data type is crucial for handling semi-structured data in Snowflake, enabling users to perform SQL operations on JSON objects and arrays directly.

When querying semi-structured data in Snowflake, the behavior towards case sensitivity is distinct between column names and the names of elements within the semi-structured data. Column names follow the general SQL norm of being case-insensitive, meaning you can reference them in any case without affecting the query. However, element names within JSON, XML, or other semi-structured data are case-sensitive. This distinction is crucial for accurate data retrieval and manipulation in Snowflake, especially when working with JSON objects where the case of keys can significantly alter the outcome of queries.

When creating a CSV file format in Snowflake and the requirement is to wrap columns by single quotes or double quotes, theFIELD_OPTIONALLY_ENCLOSED_BYparameter should be used in the file format specification. This parameter allows you to define a character (either a single quote or a double quote) that can optionally enclose each field in the CSV file, providing flexibility in handling fields that contain special characters or delimiters as part of their data.

To get the most detailed information about individual table storage details in Snowflake, theTABLE STORAGE METRICSview should be used. This Information Schema view provides granular storage metrics for tables within Snowflake, including data related to the size of the table, the amount of data stored, and storage usage over time. It ' s an essential tool for administrators and users looking to monitor and optimize storage consumption and costs.

TheREVOKE ROLEcommand is used to remove a role from another role or a user in Snowflake. This command is part of Snowflake ' s role-based access control system, allowing administrators to manage permissions and access to database objects efficiently by adding or removing roles from users or other roles.

Characteristics of reader accounts in Snowflake include:

A. Reader account users cannot add new data to the account: Reader accounts are intended for data consumption only. Users of these accounts can query and analyze the data shared with them but cannot upload or add new data to the account.

E. Reader accounts enable data consumers to access and query data shared by the provider: One of the primary purposes of reader accounts is to allow data consumers to access and perform queries on the data shared by another Snowflake account, facilitating secure and controlled data sharing.

In Snowflake, the maximum number of clusters that can be provisioned within a multi-cluster virtual warehouse is 10. This allows for significant scalability and performance management by enabling Snowflake to handle varying levels of query load by adjusting the number of active clusters within the warehouse.

In Snowflake, the query history and the results of queries executed by a user are accessible based on the roles and permissions. If User1 executed a query with the SYSADMIN role, User2 would be able to view the result set of that query executed by User1 only if User2 has the ACCOUNTADMIN role. The ACCOUNTADMIN role has the broadest set of privileges, including the ability to access all aspects of the account ' s operation, data, and query history, thus enabling User2 to view the results of queries executed by other users.

TheOBJECT_CONSTRUCTfunction in Snowflake is used to convert rows in a relational table into a single VARIANT column that represents each row as a JSON object. This function dynamically creates a JSON object from a list of key-value pairs, where each key is a column name and each value is the corresponding column value for a row. This is particularly useful for aggregating and transforming structured data into semi-structured JSON format for further processing or analysis.

The TABLE_STORAGE_METRICS view can be used to determine if a table has frequent row updates or deletes. This view provides detailed metrics on the storage utilization of tables within Snowflake, including metrics that reflect the impact of DML operations such as updates and deletes on table storage. For example, metrics related to the number of active and deleted rows can help identify tables that experience high levels of row modifications, indicating frequent updates or deletions.

When data is loaded into Snowflake, it undergoes a reorganization process where the data is stored in a columnar format and compressed. The columnar storage format enables efficient querying and data retrieval, as it allows for reading only the necessary columns for a query, thereby reducing IO operations. Additionally, Snowflake uses advanced compression techniques to minimize storage costs and improve performance. This combination of columnar storage and compression is key to Snowflake ' s data warehousing capabilities.

In Snowflake, stored procedures and User-Defined Functions (UDFs) have different invocation patterns within SQL:

The worksheet and database explorer feature in Snowsight allows users to tag frequently accessed worksheets for ease of access. This functionality helps users organize and quickly navigate to the worksheets they use most often, enhancing productivity and streamlining the data exploration and analysis process within Snowsight, Snowflake ' s web-based query and visualization interface.

The Fail-safe period for a transient table in Snowflake, regardless of the edition (including Enterprise edition and higher), is 0 days. Fail-safe is a data protection feature that provides additional retention beyond the Time Travel period for recovering data in caseof accidental deletion or corruption. However, transient tables are designed for temporary or short-term use and do not benefit from the Fail-safe feature, meaning that once their Time Travel period expires, data cannot be recovered.

The Fail-safe retention period for transient and temporary tables in Snowflake is 0 days. Fail-safe is a feature designed to protect data against accidental loss or deletion by retaining historical data for a period after its Time Travel retention period expires. However, transient and temporary tables, which are designed for temporary or short-term storage and operations, do not have a Fail-safe period. Once the data is deleted or the table is dropped, it cannot be recovered.

To insert JSON formatted string data into a VARIANT field in Snowflake, the correct function to use isPARSE_JSON. ThePARSE_JSONfunction is specifically designed to interpret a JSON formatted string and convert it into a VARIANT type, which is Snowflake ' s flexible format for handling semi-structured data like JSON, XML, and Avro. This function is essential for loading and querying JSON data within Snowflake, allowing users to store and manage JSON data efficiently while preserving its structure for querying purposes. This function ' s usage and capabilities are detailed in the Snowflake documentation, providing users with guidance on how to handle semi-structured data effectively within their Snowflake environments.

Snowflake bills virtual warehouse usage in one-minute increments, rounding up to the nearest minute for any partial minute of compute time used. If a virtual warehouse runs for 61 seconds and then, after being shut down, restarts and runs for an additional 30 seconds, the total time billed would be 120 seconds or 2 minutes. The first 61 seconds are rounded up to 2 minutes, and the subsequent 30 seconds are within a new minute, which is also rounded up to the nearest minute.

The command to list all the file formats for which a user has access privileges in Snowflake isSHOW FILE FORMATS. This command provides a list of all file formats defined in the user ' s current session or specified database/schema, along with details such as the name, type, and creation time of each file format. It is a valuable tool for users to understand and manage the file formats available for data loading and unloading operations.

A Snowflake user would decide to use a materialized view instead of a regular view primarily when the base tables do not change frequently. Materialized views store the result of the view query and update it as the underlying data changes, making them ideal for situations where the data is relatively static and query performance is critical. By precomputing and storing the query results, materialized views can significantly reduce query execution times for complex aggregations, joins, and calculations.

The default value for auto-suspending a Virtual Warehouse in the Snowflake Web Interface (UI) is 10 minutes. This setting helps manage compute costs by automatically suspending warehouses that are not in use, ensuring that compute resources are efficiently allocated and not wasted on idle warehouses.

In the Query Profile of Snowflake, the TableScan operator represents the access to a single table. This operator indicates that the query execution involved reading data from a table stored in Snowflake. TableScan is a fundamental operation in query execution plans, showing how the database engine retrieves data directly from tables as part of processing a query.

When a Snowflake user increases the size of a suspended virtual warehouse, the changes to compute resources are queued but do not take immediate effect. The provisioning of additional compute resources occurs only when the warehouse is resumed. This ensures that resources are allocated efficiently, aligning with Snowflake ' s commitment to cost-effective and on-demand scalability.

In Snowflake, when setting up a network policy that specifies both allowed and blocked IP address lists, if an IP address appears in both lists, access from that IP address will be denied. The reason is that Snowflake prioritizes security, and the presence of an IP address in the blocked list indicates it should not be allowed regardless of its presence in the allowed list. This ensures that access controls remain stringent and that any potentially unsafe IP addresses are not inadvertently permitted access.

Tri-secret secure encryption is a security model employed by Snowflake that involves combining a customer-managed key with a Snowflake-managed key to create a composite key for encrypting data. This model enhances data security by requiring both the customer-managed key and the Snowflake-managed key to decrypt data, thus ensuring that neither party can access the data independently. It represents a balanced approach to key management, leveraging both customer control and Snowflake ' s managed services for robust data encryption.

The replication feature in Snowflake provides several benefits, with disaster recovery and database failover and fallback being two of the primary advantages. Replication allows for the continuous copying of data from one Snowflake account to another, ensuring that a secondary copy of the data is available in case of outages or disasters. This capability supports disaster recovery strategies by allowing operations to quickly switch to the replicated data in a different account or region. Additionally, it facilitates database failover and fallback procedures, ensuring business continuity and minimizing downtime.

Characteristics of Snowsight worksheets in Snowflake include:

A. Worksheets can be grouped under folders, and a folder of folders: This organizational feature allows users to efficiently manage and categorize their worksheets within Snowsight, Snowflake ' s web-based UI, enhancing the user experience by keeping related worksheets together.

E. Users can import worksheets and share them with other users: Snowsight supports the sharing of worksheets among users, fostering collaboration by allowing users to share queries, analyses, and findings. This feature is crucial for collaborative data exploration and analysis workflows.

The ORGADMIN role in Snowflake is an organizational-level role that provides administrative capabilities across the entire organization, rather than being limited to a single Snowflake account. Users with this role can:

A. Create an account for an organization: The ORGADMIN role has the privilege to create new Snowflake accounts within the organization, allowing for the expansion and management of the organization ' s resources.

D. View usage information for all accounts in an organization: This role also has access to comprehensive usage and activity data across all accounts within the organization. This is crucial for monitoring, cost management, and optimization at the organizational level.

Query pruning in Snowflake is the mechanism used to limit the number of micro-partitions scanned by a query. By analyzing the filters and conditions applied in a query, Snowflake can skip over micro-partitions that do not contain relevant data, thereby reducing the amount of data processed and improving query performance. This technique is particularly effective for large datasets and is a key component of Snowflake ' s performance optimization features.

The layer of Snowflake ' s architecture associated with virtual warehouses is the Query Processing layer. Virtual warehouses in Snowflake are dedicated compute clusters that execute SQL queries against the stored data. This layer is responsible for the entire query execution process, including parsing, optimization, and the actual computation. It operates independently of the storage layer, enabling Snowflake to scale compute and storage resources separately for efficiency and cost-effectiveness.

For directory tables, a named external stage allows for the automatic refreshing of metadata. This capability is particularly useful when dealing with files stored on external storage services (like Amazon S3, Google Cloud Storage, or Azure Blob Storage) and accessed through Snowflake. The external stage references these files, and the directory table ' s metadata can be automatically updated to reflect changes in the underlying files.

The RETURN_ERRORS value in the VALIDATION_MODE option of theCOPY command instructs Snowflake to validate the data files and return errors encountered across all specified files, including those that were partially loaded during an earlier load2. References: [COF-C03] SnowPro Core Certification Exam Study Guide

The ACCESS_HISTORY view in Snowflake is utilized to support compliance auditing. It provides detailed information on data access within Snowflake, including reads and writes by user queries. This view is essential for regulatory compliance auditing as it offers insights into the usage of tables and columns, and maintains a direct link between the user, the query, and the accessed data1.

When federated authentication is activated in Snowflake, users authenticate via an external identity provider (IdP) rather than using Snowflake-managed credentials. Therefore, a user with a password defined by Snowflake will be unable to enter a password and must use their IdP credentials to log in.

QUSTION NO:579

What value provides information about disk usage for operations where intermediate results do not fit in memory in a Query Profile?

A. IO

B. Network

C. Pruning

D. Spilling

Answer: D

In Snowflake, when a query execution requires more memory than what is available, Snowflake handles these situations by spilling the intermediate results to disk. This process is known as " spilling. " The Query Profile in Snowflake includes a metric that helps users identify when and how much data spilling occurs during the execution of a query. This information is crucial for optimizing queries as excessive spilling can significantly slow down query performance. The value that provides this information about disk usage due to intermediate results not fitting in memory is appropriately labeled as " Spilling " in the Query Profile.

Unstructured data stored in Snowflake can be retrieved by using SQL functions to generate URLs that point to the data. These URLs can then be used to download the data directly to a client

The search optimization service improves the performance of point lookup queries on large tables by using selective filters to quickly return a small number of rows. It creates an optimized data structure that helps in pruning the micro-partitions that do not contain the queried values3. References: [COF-C03] SnowPro Core Certification Exam Study Guide

 In a managed access schema, the schema owner retains the OWNERSHIP privilege of any tables added to the schema. This means that while object owners have certain privileges over the objects they create, only the schema owner can manage privilege grants on these objects1.

 A Pre-signed URL should be used to access files stored in a Snowflake stage without requiring authentication into Snowflake. Pre-signed URLs are simple HTTPS URLs that provide temporary access to a file via a web browser, using a pre-signed access token. The expiration time for the access token is configurable, and this type of URL allows users or applications to directly access or download the fileswithout needing to authenticate into Snowflake5.

The BUILD_STAGE_FILE_URL function generates a Snowflake-hosted file URL to a staged file using the stage name and relative file path as inputs2.

 Virtual warehouses in Snowflake can be started and stopped at any time, providing flexibility in managing compute resources. They can also be resized at any time, even while running, toaccommodate varying workloads910. References: [COF-C03] SnowPro Core Certification Exam Study Guide

 The COPY INTO < location >  command is used to unload data from a Snowflake database table into one or more files in a Snowflake stage1.

The STRIP NULL_VALUES file format option, when set to TRUE, removes object or array elements that contain null values during the loading process of semi-structured data files into Snowflake. This ensures that the data loaded into Snowflake tables does not contain these null elements, which can be useful when the “null” values in files indicate missingvalues and have no other special meaning2.

The FLATTEN function can be used with the recursive argument to return a list of distinct key names in all nested elements within an object. This function is particularly useful for working with semi-structured data in Snowflake

The Query Profile statistics that indicate intermediate results do not fit in memory are the bytes spilled to local storage and bytes spilled to remote storage2.

 A Query Profile provides detailed execution information for a query, which can be used to analyze the performance and behavior of queries. This information can help users optimize and right-size their virtual warehouses for better efficiency. References: [COF-C03] SnowPro Core Certification Exam Study Guide

 Once an internal stage is dropped in Snowflake, it cannot be recovered or restored using Time Travel or UNDROP commands. The only option is to recreate the dropped stage

 In a regular schema within Snowflake, the object owner has the privilege to grant object privileges. The object owner is typically the role that created the object or to whom the ownership of the object has been transferred78.

References = [COF-C03] SnowPro Core Certification Exam Study Guide

 When the DROP MANAGED ACCOUNT command is executed in Snowflake, it removes the managed account, including all objects created within the account, and access to the account is immediately restricted2.

The MIN_DATA_RETENTION_TIME_IN_DAYS parameter is set at the account level. This parameter determinesthe minimum number of days Snowflake retains historical data for Time Travel operations

The Snowflake command to unload the result of a query to a single file is COPY INTO < internal stage > with SINGLE = TRUE followed by a GET command to download the file. This command unloads the query result into a single file in the specified internal stage

The search optimization service in Snowflake is designed to improve the performance of selective point lookup queries on large tables, which is ideal for scenarios where a query returns a small amount of data from a very large base table1. References: [COF-C03] SnowPro Core Certification Exam Study Guide

Snowflake’s architecture allows for the independent scaling of compute resources, meaning you can increase or decrease the computational power as needed without affecting storage. This separation also means that storage can grow independently of compute usage, allowing for more flexible and cost-effective data management. 

 In Snowflake, a user with the SECURITYADMIN role or higher can activate and enforce a network policy for all users in an account. Additionally, a role that has ownership of the network policy can also activate and enforce it

The DATA_SHARING_USAGE schema includes views that display information about listings published in the Snowflake Marketplace or a data exchange, which includes DATA_TRANSFER_HISTORY and LISTING_TELEMETRY_DAILY2.

To reduce data spilling in Snowflake, using a larger virtual warehouse is effective because it provides more memory and local disk space, whichcan accommodate larger data operations and minimize the need to spill data to disk or remote storage1. References: [COF-C03] SnowPro Core Certification Exam Study Guide

 The Access History feature in Snowflake allows administrators to track data access patterns and identify unused data. This information can be used to make decisions about archiving or deleting data to optimize storage and reduce costs. 

A directory table in Snowflake is used to store file-level metadata about the data files in a stage. It is conceptually similar to an external table and provides information such as file size, last modified timestamp, and file URL. References: [COF-C03] SnowPro Core Certification Exam Study Guide

 Storing JSON data in a table with a VARIANT data type is optimal for querying because it allows Snowflake to leverage its semi-structured data capabilities. This approach enables efficient storage and querying without the need for flattening the data, which can be performance-intensive1.

To start configuring Snowflake for Single Sign-On (SSO), the CREATE SECURITY INTEGRATION command is used. This command sets up a security integration object in Snowflake, which is necessary for enabling SSO with external identity providers using SAML 2.01.

The Snowflake data types that can be used to build nested hierarchical data are OBJECT and VARIANT. These data types support the storage and querying of semi-structured data, allowing for the creation of complex, nested data structures

The STRIP_NULL_VALUE function in Snowflake is used to convert a JSON null value into aSQL NULL value1.

The OWNERSHIP privilege is unique in that it must be granted by one role to another and cannot be revoked. This ensures that the transfer of ownership is deliberate and permanent, reflecting the importance of ownership in managing access and permissions.

Snowflake’s Time Travel feature is used for the maintenance of historical data, allowing users to access andrestore data that has been changed or deleted within a defined period4.

 A user with the OWNERSHIP privilege on a user object or the CREATE USER privilege on the account can create or manage other users in a Snowflake account56.

The minimum Snowflake Edition that supports secure storage of Protected Health Information (PHI) data is the Business Critical Edition. This edition offers enhancedsecurity features necessary for compliance with regulations such as HIPAA and HITRUST CSF4.

Dynamic Data Masking in Snowflake supports tables and views. These objects can have masking policies applied to their columns to dynamically mask data at query time3.

The query acceleration service in Snowflake is designed to benefit queries that involve large scans and selective filters. This service can offload portions of the query processing work to shared compute resources, which can handlethese types of workloads more efficiently by performing morework in parallel and reducing the wall-clock time spent in scanning and filtering2. References: [COF-C03] SnowPro Core Certification Exam Study Guide

The statistics that can be used to identify queries with inefficient pruning are ‘Partitions scanned’ and ‘Partitions total’. These statistics indicate how much of the data was actually needed and scanned versus the total available, which can highlight inefficiencies in data pruning34.

 In Snowflake, if a temporary table and a permanent table have the same name within the same schema, the temporary table takes precedence over the permanent table within the session where the temporary table was created4.

 One of the characteristics of materialized views in Snowflake is that they allow multiple tables to be joined in the underlying query. This enables the pre-computation of complex queries involving joins, which can significantly improve the performance of subsequent queries that access the materialized view4. References: [COF-C03] SnowPro Core Certification Exam Study Guide

The LIST and PUT commands are specific to SnowSQL and cannot be executed in the web interface or other SQL clients. LIST is used to display the contents of a stage, and PUT is used to upload files to a stage. References: [COF-C03] SnowPro Core Certification Exam Study Guide

 A directory table in Snowflake is an object layered on a stage that is used to store file-level metadata. It is not a separate database object but is conceptually similar to an external table because it stores metadata about the data files in the stage5.

Theparameter DATA_RETENTION_TIME_IN_DAYS can be set at the account level to define the minimum number ofdays Snowflake retains historical data for Time Travel1.

The Notify & Suspend action for a resource monitor in Snowflake sends a notification to all account administrators who have notifications enabled and suspends all assigned warehouses. However, the suspension only occurs after all currently running statements in the warehouses have been completed1. References: [COF-C03] SnowPro Core Certification Exam Study Guide

SnowCD is a connectivity diagnostic tool that helps users troubleshoot network connections to Snowflake. It performs a series of checks to evaluate the network connection and provides suggestions for resolving any issues4.

Snowflake external tables are read-only, which means data manipulation language (DML) operations like INSERT, RENAME, or ALTER cannot be performed on them. However, external tables can be used for query and join operations3.

When using the ALLOW_CLIENT_MFA_CACHING parameter, a cached Multi-Factor Authentication (MFA) token is valid for up to 4 hours. This allows for continuous, secure connectivity without users needing to respond to an MFA prompt at the start of each connection attempt to Snowflake within this timeframe2.

In Snowflake, permanent tables and external tables can be shared with other accounts using Secure Data Sharing. Temporary tables, transient tables, and UDTFs are not shareable objects

The COPY INTO < table >  command in Snowflake supports column reordering and column omission as part of its data transformation capabilities during the load process45.

 For optimizing the performance of queries that run on a very large table, it is recommended to choose one high cardinality column as the clustering key. This helps to co-locate similar rows in thesame micro-partitions, improving scan efficiency in queries by skipping data that does not match filtering predicates4.

 To validate data unloaded using the COPY INTO < location > command, a Snowflakeuser can use the VALIDATION_MODE parameter within the SQL statement to test the files for errors without loading them3.

When a tag object is assigned to a table, using the statement CREATE TABLE < table_name > AS SELECT * FROM TABLE_A will automatically assign the TABLE_A tag to the newly created table2.

In Snowflake, to extract a specific value from a VARIANT column, you use the column name followed by a colon and then the key. The keys are case-sensitive. Therefore, to extract the employee’s name from the “Data” column, the correct syntax is data:employee.name.

When the data retention period is changed at the schema level, all child objects that do not have an explicit retention period setwill inherit the new retention period from the schema4.

The purpose of the Snowflake SPLIT_TO_TABLE function is to split a string based on a specified delimiter and flatten the results into rows. This table function is useful for transforming a delimited string into a set of rows that can be further processed or queried5.

The purpose of a Query Profile is to provide a detailed analysis of a particular query’s execution plan, including the mechanics, behavior, and performance. It helps in identifying potential performance bottlenecks and areas for optimization

The TABLE_STORAGE_METRICS metadata table stores the storage utilization information, including for tables that have been dropped but are still incurring storage costs2.

When file format options are specified in multiple locations, the load operation applies the options in the following order of precedence: first, the COPY INTO TABLE statement; second, the stage definition; and third, the table definition1

The search optimization service and automatic clustering are features that can improve the performance of queries returning a small subset of rows from a large table. The search optimization service is designed for low-latency point lookup queries, while automatic clustering organizes data in micro-partitions based on specific dimensions to reduce the amount of data scanned during queries.

Snowflake’s architecture is divided into three layers: database storage, query processing, and cloud services. The metadata, which includes information about the structure of the data, the SQL operations performed, and the service-level policies, is stored in the cloud services layer. This layer acts as the brain of the Snowflake environment, managing metadata, query optimization, and transaction coordination.

 The REST API used with unstructured data in Snowflake is GET /api/files/, which retrieves (downloads) a data file from an internal or external stage4.

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 COPY INTO < table > command in Snowflake supports transformations such as casting using a SELECT statement. This allows for the transformation of data types as the data is being loaded into the table, which can be particularly useful when the data types in the source files do not match the data types in the target table

 Stored procedures in Snowflake can perform a variety of database operations, including DDL and DML, whereas UDFs are designed to returnvalues and cannot execute database operations1.

 Once a database has been created in a consumer account from a share, the shared objects become accessible to users in that account. The shared objects are not transferred or copied; they remain in the provider’s account and are accessible to the consumer account

 Snowsight automatically detects if a target account is in a different region and enables cross-cloud auto-fulfillment when using a paid listing on the Snowflake Marketplace. This feature allows Snowflake to manage the replication of data products to consumer regions as needed, without manual intervention1.

The Activity area of Snowsight includes the Query History page, which allows users to monitor and view details about queries executed in their account, including performance data1. It also includesthe Automatic Clustering History, which provides insights into the automatic clustering operations performed on tables2.

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 allowsSnowflake 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

Pruning in Snowflake refers to the process of filtering or disregarding micro-partitions that are not needed to satisfy the conditions of a query. This optimization technique helps reduce the amount ofdata scanned, thereby improving query performance

 The OBJECT_CONSTRUCT function is used with the COPY INTO command to convert the rows in a relational table to a single VARIANT column, which can then be unloaded into a file. This is useful for transforming table data into a semi-structured JSON format

Snowflake retains information in the ACCESS HISTORY view for 365 days. This allows users to query the access history of Snowflake objects within the last year1.

User-Defined Function (UDF) handlers must be written inline for Javascript and SQL. These languages allow the UDF logic to be included directly within the SQL statement that creates the UDF2.

 The VALIDATION_MODE parameter can be used with the COPY command to verify data files without loading them into the specified table. This parameter allows users to check for errors in the files

To change the existing file format type from CSV to JSON, the recommended way is to use the ALTER FILE FORMAT command with the SET TYPE=JSON clause. This alters the file format specification to use JSON instead of CSV. References: Based on my internal knowledge as of 2021.

The minimum privilege required on an external stage for any role to access unstructured data files using a file URL in the GET REST API is READ. This allows the role to retrieve or download data files from the stage.

 Snowflake provides Multi-Factor Authentication (MFA) support as an integrated feature, powered by the Duo Security service. This service is managed completelyby Snowflake, and users do not need to sign up separately with Duo1

The Snowflake feature that allows for small volumes of data to be continuously loaded into Snowflake and incrementally made available for analysis is Snowpipe. Snowpipe is designed for near-real-time data loading, enabling data to be loaded as soon as it’s available in the storage layer3

 Snowflake supports several semi-structured data formats for loading data. The supported formats include JSON, Avro, and Parquet12. These formats allow for efficient storage and querying of data that does not conform to a traditional relational database schema.

 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 usersto download data files that have been staged, making them available on the local file system for further use23.

 Snowflake automatically rotates the Account and Table master keys when they are more than 30 days old. Active keys are retired, and new keys are created, ensuring robust security through frequent key changes1

Snowflake’s search optimization service supports tables and views that are not protected by row access policies. It is designed to improve the performance of certain types of queries on tables, including selective point lookup queries and queries on fields in VARIANT, OBJECT, and ARRAY (semi-structured) columns1.

In the Admin area of Snowsight, users can analyze query performance, manage Snowflake warehouses, set up and view details about resource monitors, manage users and roles, and administer Snowflake accounts in their organization2.

A data consumer who has a pre-signed URL can access data files using Snowflake until the expiration time is exceeded. The expiration time is set when the pre-signed URL is generated and determines how long the URL remains valid3.

 A schema in Snowflake is a logical grouping of database objects, such as tables and views, that belongs to a single database. Each schema is part of a namespace in Snowflake, which is inferred from the current database and schema in use for the session5

 To change permissions for new objects in a managed access schema, a user should be granted the MANAGE GRANTS global privilege. This privilege allows the user to manage access control through grants on all securable objects within Snowflake2. References: [COF-C03] SnowPro Core Certification Exam Study Guide

 The reader account compute will be billed to the provider account. Very Comprehensive Explanation: In Snowflake, when a provider creates a reader account for a consumer who does not have a Snowflake account, the compute resources used by the reader account are billed to the provider’s account. This allows the consumer to query the shared data without incurring any costs. References: [COF-C03] SnowPro Core Certification Exam Study Guide

 In theInformation Schema and Account Usage, the column that identifies whether a view is secure or not is IS_SECURE2.

 Snowflake’s column level security features include Dynamic Data Masking and External Tokenization. Dynamic Data Masking uses masking policies to selectively mask data at query time, while External Tokenization allows for the tokenization of data before loading it into Snowflake and detokenizing it at query runtime5.

 Network policies in Snowflake can be applied to users and accounts. These policies control inbound access to the Snowflake service and internal stages, allowing or denying access based on the originating network identifiers12.

The Snowflake PUT command is used to upload files from a local file system to Snowflake stages, specifically the user stage, table stage, and internal named stage. These stages are where the data files are temporarily stored before being loaded into Snowflake tables

Snowflake’s documentation recommends producing the largest files possible for data loading, as larger files reduce the number of files to process and the overhead associated with handling many small files. Thisapproach can maximize the load performance by leveraging Snowflake’s ability to ingest large files efficiently1. References: [COF-C03] SnowPro Core Certification Exam Study Guide

When unloading data to an external stage, the maximum file size supported is 5 GB. This limit ensures efficient data transfer and management within Snowflake’s architecture

 SnowCD (Snowflake Connectivity Diagnostic Tool) is the best tool provided by Snowflake for troubleshooting network connectivity issues. It helps diagnose and resolve issues related to connecting to Snowflake services

The VARIANT data type in Snowflake can store multiple types of data structures, as it is designed to hold semi-structured data. It can contain any other data type, including OBJECT and ARRAY, which allows it to represent various data structures

 When unloading data, Snowflake assigns each file a unique name to ensure there is no overlap or confusion between files. This is part of the bulk unloading process where data is exported from Snowflake tables into flat files3.

 In Snowflake, a namespace is comprised of a database and a schema. The combination of a database and schema uniquely identifies database objects within an account

 Snowflake reorganizes data into its internal optimized, compressed, columnar format in the Database Storage layer. This process is part of how Snowflake manages data storage, ensuring efficient data retrieval and query performance

The Cloud Services layer in Snowflake includes activities such as user authentication and infrastructure management. This layer coordinates activities across Snowflake, including security enforcement, query compilation and optimization, and more

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.

In Snowflake, a tabular User-Defined Function (UDF) is defined with a return clause that includes the keyword “TABLE.” This indicates that the UDF will return a set of rows, which can be used in the FROM clause of a query. References: Based on my internal knowledge as of 2021.

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.

The Snowflake Account_Usage.Metering_History view is used to gather the hourly credit usage for an account. This view provides details on the credits consumed by various services within Snowflake for the last 365 days1.

 In the Activity section in Snowsight, Snowflake users can explore the performance of executed queries. This includes monitoring queries, viewing details about queries, including performance data, and exploring each step of an executed query in the query profile1.

When a database is cloned in Snowflake, it does not retain any privileges that were granted on the source object. The clone will need to have privileges reassigned as necessary for users to access it. References: [COF-C03] SnowPro Core Certification Exam Study Guide

With the create data EXCHANGE listing privilege, a Snowflake user can modify the properties of listings and submit them for approval or publishing on the Data Exchange. This allows them to manage and share data sets with consumers effectively. References: Based on general data exchange practices in cloud services as of 2021.

In Snowflake, to change the columns referenced in a view, the view must be recreated with the required changes. The ALTER VIEW command does not allow changing the definition of a view; it can only be used to rename a view, convert it to or from a secure view, or add, overwrite, or remove a comment for a view. Therefore, the correct approach is to drop the existing view and create a new one with the desired column references. 

The Snowpark API allows developers to create User-Defined Functions (UDFs) in various languages, including Python, which is known for its ease of use and wide adoption in data-related tasks. References: Based on general programming and cloud data service knowledge as of 2021.

To allow secure views the ability to reference data in multiple databases, the REFERENCE_USAGE privilege must be granted on each database that contains objects referenced by the secure view2. This privilege is necessary before granting the SELECT privilege on a secure view to a share.

 A secure view in Snowflake is exposed only to users with the OWNERSHIP privilege. This privilege ensures that only authorized users who own the view, or roles that include ownership, can access the secure view

Snowflake supports reader accounts, which are a type of account that allows data providers to share data with consumers who are not Snowflake customers. However, for data sharing to occur, the consumer needs to become a licensed Snowflake customer because data sharing is only supported between Snowflake accounts. References: Introduction to Secure Data Sharing | Snowflake Documentation2.

A Snowflake Data Exchange allows groups of accounts to share data privately among the accounts (B), and it supports bidirectional sharing, meaning an account can be both a provider and a consumer of data (E). This facilitates secure and governed data collaboration withina selected group3.

 Multi-Factor Authentication (MFA) token caching is typically supported for clients that maintain a persistent connection or session with Snowflake, such as the ODBC driver and Python connector, to reduce the need for repeated MFA challenges. References: Based on general security practices in cloud services as of 2021.

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.

You execute a stored procedure using the CALL command. Nested procedure calls are allowed where the result of one procedure is passed as an argument to another.

Correct syntax:

CALL proc1(proc2(1));

Under the Economy scaling policy, Snowflake delays starting a new cluster until it ' s sure the queries will utilize it effectively for ~6 minutes . This reduces costs but may slightly delay query execution.

Snowflake supports a variety of subquery types, including both correlated and uncorrelated subqueries. The correct answers are B and E, which highlight Snowflake ' s flexibility in handling subqueries within SQL queries.

Uncorrelated Scalar Subqueries:These are subqueries that can execute independently of the outer query. They return a single value and can be used anywhere a value expression is allowed, offering great flexibility in SQL queries.

EXISTS, ANY/ALL, and IN Subqueries:These subqueries are used inWHEREclauses to filter the results of the main query based on the presence or absence of matching rows in a subquery. Snowflake supports both correlated and uncorrelated versions of these subqueries, providing powerful tools for complex data analysis scenarios.

Examples and Usage:

Uncorrelated Scalar Subquery:

SELECT*FROMemployeesWHEREsalary > (SELECTAVG(salary)FROMemployees);

Correlated EXISTS Subquery:

SELECT*FROMorders oWHEREEXISTS(SELECT1FROMcustomer cWHEREc.id = o.customer_idANDc.region = ' North America ' );

Access History in Snowflake is primarily utilized for compliance auditing. The Access History feature provides detailed logs that track data access and modifications, includingqueries that read from or write to database objects. This information is crucial for organizations to meet regulatory requirements and to perform audits related to data access and usage.

Role of Access History:Access History logs are designed to help organizations understand who accessed what data and when. This is particularly important for compliance with various regulations that require detailed auditing capabilities.

How Access History Supports Compliance Auditing:

By providing a detailed log of access events, organizations can trace data access patterns, identify unauthorized access, and ensure that data handling complies with relevant data protection laws and regulations.

Access History can be queried to extract specific events, users, time frames, and accessed objects, making it an invaluable tool for compliance officers and auditors.

In Snowflake, a user within a reader account primarily has read-only access to the shared data. Reader accounts are created to enable third parties or separate business units to access and query data shared with them without allowing them to modify the underlying data. This means a user with a reader account can perform operations like querying shared data to analyze or report on it but cannot load new data, update existing data, or create new shares. This setup is crucial for maintaining data governance and security while enabling data sharing and collaboration.

For a private GitHub repository accessed over HTTPS, Snowflake can authenticate using a GitHub personal access token. The token is stored securely in a Snowflake secret rather than written directly into the Git repository object.

The secret is referenced by the API integration or Git repository configuration. For GitHub, a personal access token is generally supplied in the password field of a password-type Snowflake secret.

A normal GitHub account password is not the recommended authentication mechanism for Git operations. Interactive MFA cannot be performed by Snowflake when it synchronizes a repository. An arbitrary OAuth token is not the standard answer for the documented private-GitHub setup represented by this question.

In Snowflake, when an object is created, it is owned by the role that is currently active. This active role is the one that is being used to execute the creation command. Ownership implies full control over the object, including the ability to grant and revoke access privileges. This is specified in Snowflake ' s documentation under the topic of Access Control, which states that " the role in use at the time of object creation becomes the owner of the object. "

Snowflake Documentation: Object Ownership

TheSnowpark-optimized warehouseis designed with significantly higher memory per node, making it ideal formachine learning (ML) trainingand other memory-intensive workloads. Snowpark-optimized warehouses provide the necessary computational power and memory for complex, large-scale data processing tasks.

Standard and multi-cluster warehouses do not offer the same memory advantage, while the 6XL warehouse is large but not specifically optimized for memory-intensive operations.

The primary factor that determines the cost of using a virtual warehouse in Snowflake isD. The length of time the compute resources in each cluster run. Snowflake ' s pricing model for compute usage is based on the concept of Snowflake credits, which are consumed based on the time virtual warehouses are running and the size of the warehouses. The more compute resources are utilized and the longer they run, the more credits are consumed, which directly impacts the cost.

The correct answer is D. By identifying where column-level values are located in each micro-partition .

The Search Optimization Service improves query performance for highly selective queries. It works by creating and maintaining a persistent data structure called a search access path .

The search access path records information about where specific values are located within the table’s micro-partitions. This allows Snowflake to find qualifying rows more efficiently without scanning unnecessary micro-partitions.

Why D is correct:

The search access path helps Snowflake identify which micro-partitions contain column values that match a query predicate. This improves pruning and can speed up selective point lookup queries.

Why the other options are incorrect:

A. Search optimization does not create a real-time index for every database. It is enabled on selected tables and columns.

B. A catalog of tables is not the purpose of the search access path.

C. Query history tracks executed queries. Search optimization does not work by listing previous queries.

Official Snowflake documentation reference:

Snowflake documentation describes the search access path as a persistent data structure maintained by the Search Optimization Service. It helps Snowflake determine which micro-partitions contain values needed by selective queries.

The useful calculation is based on BYTES_SCANNED and PERCENTAGE_SCANNED_FROM_CACHE. These metrics help estimate how much scanned data came from the warehouse cache. A low cache percentage on a query that scans many bytes may indicate that warehouse cache optimization could help.

Corrected note: the original options had OCR errors. The best matching valid answer is B .

In Snowflake, theAPPLYprivilege is required to set a masking policy on a table or view column. This privilege allows a user to associate a masking policy with a specific column, thereby controlling how data in that column is masked or hidden.

Create a Masking Policy:Define a masking policy using theCREATE MASKING POLICYcommand.

Grant APPLY Privilege:Grant theAPPLYprivilege on the masking policy to the relevant roles or users.

Apply the Masking Policy:Use theALTER TABLEorALTER VIEWcommand to apply the masking policy to a column.

For Parquet files, using the vectorized scanner supports improved schema inference behavior, including use cases involving Iceberg-compatible column definitions.

Corrected typos:

CREATE FIZE FORMAT should be CREATE FILE FORMAT.

VSE_VECTORIZED_SCANNER should be USE_VECTORIZED_SCANNER.

Python worksheets in Snowsight enable users to perform data manipulations and transformations using the Python programming language directly within the Snowflake environment. This feature integrates the power of Python with Snowflake ' s data warehousing capabilities, allowing for sophisticated data analysis and manipulation.

Introduction to Python Worksheets:

Python worksheets provide an interactive environment to write and execute Python code.

They are designed to facilitate data science and data engineering tasks.

Functionality:

Users can run Python scripts to manipulate data stored in Snowflake.

It allows for leveraging Python ' s extensive libraries for data analysis, machine learning, and more.

Integration with Snowflake:

Python worksheets run on Snowflake ' s compute infrastructure, ensuring scalability and performance.

They can access and manipulate Snowflake tables directly, making them a powerful tool for data transformation.

Snowflake Documentation: Snowsight Python Worksheets

To start, resume, or suspend a warehouse, the role needs the OPERATE privilege. USAGE only allows reference to the object but not its activation.

Required Privileges for Starting a Warehouse:

USAGE (to reference)

OPERATE (to start/stop)

The MERGE command in Snowflake is used for incremental loading based on column values in a source table or subquery. It enables the insertion, updating, or deletion of records in a target table depending on whether matching rows are found, making it ideal for loading data that changes incrementally, such as daily updates or modifications.

Resource monitors in Snowflake can perform actions such as suspending a virtual warehouse when its credit usage reaches a defined limit and triggering a notification to account administrators when credit usage reaches a specified threshold. These actions help manage and control resource usage and costs within Snowflake.

Snowflake Documentation: Resource Monitors

The correct answer is D. Document AI .

Document AI is a Snowflake Cortex feature designed to extract information from unstructured documents and make that information usable in structured form, such as rows and columns in Snowflake tables.

Why D is correct:

Document AI processes documents such as PDFs or forms and extracts values from them. The extracted data can then be stored in Snowflake tables for querying, analytics, and downstream processing.

Why the other options are incorrect:

A. Cortex Analyst enables natural language questions over structured data using semantic models. It is not the primary service for extracting data from documents into tables.

B. Cortex Search provides search over text and unstructured/semi-structured data, but it is not the main service for document extraction into tables.

C. Snowflake Copilot assists users with SQL and Snowflake workflows, but it is not the service specifically designed to process documents into table data.

Official Snowflake documentation reference:

Snowflake documentation describes Document AI as a Snowflake Cortex feature that uses AI to extract data from documents and process unstructured data for use in Snowflake.

Processing data in smaller batches reduces resource pressure during execution.

Increasing warehouse size provides more memory and CPU, which helps reduce memory spillage , where intermediate data is written to disk.

The GET_STAGE_LOCATION function retrieves the full external URL associated with a named stage. It is useful for accessing the storage location of external stages configured in Snowflake.

In Snowflake, reader accounts are special types of accounts that allow data sharing with external consumers without them having their own Snowflake account. The creation and management of reader accounts can be performed by users with the ACCOUNTADMIN role or the ORGADMIN role. The ACCOUNTADMIN role has comprehensive administrative privileges within a Snowflake account, including managing other accounts and roles. The ORGADMIN role, which is higher in hierarchy, oversees multiple accounts within an organization and can manage reader accounts across those accounts.

Snowflake Documentation: Creating and Managing Reader Accounts

The correct answer is B. Only one refresh will run at a time .

Failover groups can be refreshed on a schedule to replicate supported account objects and database objects to target accounts. Snowflake does not run overlapping refresh operations for the same failover group.

Why B is correct:

If a scheduled refresh is still running, Snowflake does not start another concurrent refresh for the same failover group. Refresh operations are managed so that only one refresh runs at a time.

Why the other options are incorrect:

A. Multiple refreshes for the same failover group do not run concurrently.

C. Refreshes still require the appropriate privileges and configured replication/failover group permissions.

D. A delayed or long-running refresh does not mean the next refresh is automatically canceled in the way stated. The key behavior is that refreshes do not overlap.

Official Snowflake documentation reference:

Snowflake documentation for replication and failover groups explains that scheduled refreshes are managed by Snowflake and that refresh operations do not run concurrently for the same group.

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.

Stored procedures in Snowflake can be created with either ' owner ' s rights ' or ' caller ' s rights ' . A stored procedure created with caller ' s rights executes with the privileges of the role that calls the procedure, not the privileges of the role that owns the procedure. This is particularly useful in scenarios where the procedure needs to perform operations that depend on the caller ' s access permissions, ensuring that the procedure can only access objects that the caller is authorized to access.

In a scenario where a Snowflake user queries only a small number of rows that require significant processing and the data in the table does not change frequently, the most effective way to optimize performance is by creating a materialized view based on the query. Materialized views store the result of the query and can significantly reduce the computation time for queries that are executed frequently over unchanged data.

Why Materialized Views:Materialized views precompute and store the result of the query. This is especially beneficial for queries that require heavy processing. Since the data does not change frequently, the materialized view will not need to be refreshed often, making it an ideal solution for this use case.

Implementation Steps:

To create a materialized view, use the following SQL command:

CREATEMATERIALIZEDVIEWmy_materialized_viewASSELECT...FROMmy_tableWHERE...;

When the query is run, Snowflake uses the precomputed results from the materialized view, thus skipping the need for recalculating the data and improving query performance.

The Activity area of Snowsight, Snowflake ' s web interface, allows users to perform several important tasks related to query management and performance analysis. Among the options provided, the correct ones are:

B. Explore each step of an executed query:Snowsight provides detailed insights into query execution, including the ability to explore the execution plan of a query. This helps users understand how a query was processed, identify performance bottlenecks, and optimize query performance.

C. Monitor queries executed by users in an account:The Activity area enables users to monitor the queries that have been executed by users within the Snowflake account. This includes viewing the history of queries, their execution times, resources consumed, and other relevant metrics.

These features are crucial for effective query performance tuning and ensuring efficient use of Snowflake ' s resources.

The correct answer is D. Dynamic Data Masking .

Dynamic Data Masking is used to protect sensitive column values at query run time. It allows Snowflake to return different values depending on the user’s role or other policy logic.

Why D is correct:

A masking policy can be applied to a table or view column. When a query runs, Snowflake evaluates the masking policy and determines whether to return the original value, a partially masked value, or a fully masked value.

Example concept:

CREATE MASKING POLICY phone_mask AS (val STRING)

RETURNS STRING - >

CASE

WHEN CURRENT_ROLE() IN ( ' CUSTOMER_SUPPORT_FULL ' ) THEN val

ELSE CONCAT( ' XXX-XXX- ' , RIGHT(val, 4))

END;

This protects sensitive phone numbers at query time without changing the underlying stored data.

Why the other options are incorrect:

A. Object tagging classifies or labels data for governance, but it does not mask values at query runtime by itself.

B. Data encryption protects data at rest and in transit, but it does not dynamically change query results based on role.

C. UDFs can implement custom logic, but Snowflake’s built-in governance feature for runtime masking is Dynamic Data Masking.

Official Snowflake documentation reference:

Snowflake documentation describes Dynamic Data Masking as a column-level security feature that uses masking policies to selectively mask plain-text data in query results at runtime.

Snowflake ' s governance features vary across different editions, but theOBJECT_DEPENDENCIESview is supported by all Snowflake editions. This feature is part of Snowflake ' s Information Schema and is designed to help users understand the dependencies between various objects in their Snowflake environment.

TheOBJECT_DEPENDENCIESview provides a way to query and analyze the relationships and dependencies among different database objects, such as tables, views, and stored procedures. This is crucial for governance, as it allows administrators and data engineers to assess the impact of changes, understand object relationships, and ensure proper management of data assets.

Object tags, masking policies, and row access policies are more advanced features that offer fine-grained data governance capabilities such as tagging objects for classification, dynamically masking sensitive data based on user roles, and controlling row-level access to data. These features may have varying levels of support across different Snowflake editions, with some features being exclusive to higher-tier editions.

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.

When unloading data using the COPY INTO < location > command, Snowflake offers options to compress files. The default behavior is governed by the COMPRESSION parameter. Setting COMPRESSION = AUTO allows Snowflake to automatically use its default compression algorithm, which is GZIP for most formats like CSV.

Snowflake streams are objects that enable users to track changes (inserts, updates, and deletes) to the data in tables, facilitating real-time data processing and integration workflows. Streams can be created on standard tables, capturing DML changes made to these tables so that downstream processes can consume the changes incrementally. This feature supports efficient data ETL, replication, and real-time analytics by providing a mechanism to process only the data that has changed. Note: The correct options should likely include a distinction between " Standard tables " and another object type such as " External tables " rather than repeating " Standard tables " twice.

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.

When unloading data in Snowflake, it is essential to differentiate between empty strings and NULL values to preserve data integrity. The parameters FIELD_OPTIONALLY_ENCLOSED_BY and EMPTY_FIELD_AS_NULL are used together to address this:

FIELD_OPTIONALLY_ENCLOSED_BY: This parameter specifies the character used to enclose fields, which can differentiate between empty strings (as enclosed fields) and NULLs.

EMPTY_FIELD_AS_NULL: By setting this parameter, Snowflake interprets empty fields as NULL values when unloading data, ensuring accurate representation of NULLs versus empty strings.

These parameters are crucial when exporting data for systems that need explicit differentiation between NULL and empty string values.

The correct answers are C. ACCOUNTADMIN and E. DOCUMENT_INTELLIGENCE_CREATOR .

Document AI is part of Snowflake Cortex and uses specific privileges/roles for access and administration. The ACCOUNTADMIN role is commonly required for initial setup or granting required privileges, while DOCUMENT_INTELLIGENCE_CREATOR is used to create and work with Document AI objects.

Why C is correct:

ACCOUNTADMIN has the account-level authority needed to enable or manage required access for Document AI features.

Why E is correct:

DOCUMENT_INTELLIGENCE_CREATOR is the Snowflake role associated with creating Document AI-related objects and using Document AI capabilities.

Why the other options are incorrect:

A. PUBLIC is available to all users but does not provide Document AI creation privileges.

B. SYSADMIN manages many account objects, but it is not the specific Document AI role in the answer set.

D. OBJECT_VIEWER is a Snowflake database role for object metadata visibility, not Document AI access.

Official Snowflake documentation reference:

Snowflake documentation for Document AI describes the required access model and includes the DOCUMENT_INTELLIGENCE_CREATOR role for creating Document AI objects, with account administrator involvement for setup.

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.

In Snowflake, the authorization to executeCREATE < object > statements, such as creating tables, views, databases, etc., is determined by the role currently set as the user ' s primary role. The primary role of a user or session specifies the set of privileges (including creation privileges) that the user has. While users can have multiple roles, only the primary role is used to determine what objects the user can create unless explicitly specified in the session.

Temporary and transient tables in Snowflake share several characteristics, notably, neither table type has a Fail-safe period. Fail-safe is a feature that provides additional data protection beyond the Time Travel period. However, this feature does not apply to temporary or transient tables. Additionally, for both types of tables, the data retention period effectively ends when the tables are dropped. This means that once these tables are deleted, their data is not recoverable, distinguishing them from permanent tables, which benefit from Snowflake ' s Time Travel and Fail-safe features.

Snowflake recommends considering adding a clustering key to a table when its size reaches1 Terabyte (TB)or larger. Clustering keys help optimize the storage and query performance by organizing the data in a table based on the specified columns. This is particularly beneficial for large tables where data retrieval can become inefficient without proper clustering.

Why Clustering Keys Are Important:Clustering keys ensure that data stored in Snowflake is physically ordered in a way that aligns with the most frequent access patterns, thereby reducing the amount of scanned data during queries and improving performance.

Recommendation Basis:The recommendation for tables of size 1 TB or larger is based on the observation that smaller tables generally do not benefit as much from clustering, given Snowflake ' s architecture. However, as tables grow in size, the benefits of clustering become more pronounced.

Implementing Clustering Keys:

To set a clustering key for a table, you can use theCLUSTER BYclause during table creation or alter an existing table to add it:

CREATETABLEmy_table (... ) CLUSTERBY(column1, column2);

Or for an existing table:

ALTERTABLEmy_table CLUSTERBY(column1, column2);

The correct answers are C. Consume datasets and E. Browse exchange listings .

Important correction:

The provided answer A, B is not correct for consumers. Creating and publishing listings are provider actions, not consumer actions.

Why C is correct:

Consumers join a Data Exchange so they can access and consume datasets made available by providers.

Why E is correct:

Consumers can browse listings in the Data Exchange to discover datasets that are available to them.

Why the other options are incorrect:

A. Creating a listing is a provider action.

B. Publishing a listing is a provider or exchange administrator/provider action.

D. Defining listing access is an administrative/provider function, not a typical consumer action.

Official Snowflake documentation reference:

Snowflake documentation describes a Data Exchange as a private hub where providers publish listings and consumers browse and access shared datasets.

Snowflake’s architecture is designed to operate entirely on cloud infrastructure. It uses the core compute and storage services of each cloud provider, which allows it to leverage the scalability and reliability of cloud resources. Snowflake’s services are deployed across multiple availability zones within a cloud provider’s region to ensure high availability and fault tolerance. References: [COF-C03] SnowPro Core Certification Exam Study Guide

 The GET command is used to download files from a Snowflake stage to a local folder on a client’s machine2.

The SQL statement SHOW GRANTS TO ROLE MY_ROLE is used to determine which users and roles have access to a role called MY_ROLE. This statement lists all the privileges granted to the role, including which roles and users can assume MY_ROLE. References: [COF-C03] SnowPro Core Certification Exam Study Guide

The ResultSet cache is leveraged to quickly return results for repeated queries. Actions that prevent leveraging this cache include stopping the virtual warehouse that the query is running against (B) and executing the RESULTS_SCAN() table function (D). Stopping the warehouse clears the local diskcache, including the ResultSet cache1. The RESULTS_SCAN() function is used to retrieve the result of a previously executed query, which bypasses the need for the ResultSet cache.

 When leveraging third-party data from the Snowflake Data Marketplace, the data is live, ready-to-query, and can be personalized. Additionally, the data is available without the need for copying or movingit to an individual Snowflake account, allowing for seamless integration with existing data

 When cloning a database in Snowflake, the clone includes all privileges on the database as well as existing child objects within the database, such as schemas, tables, views, etc. However, it does not include future child objects or privileges on schemas within the database2.

References = [COF-C03] SnowPro Core Certification Exam Study Guide, Snowflake Documentation

Snowflake recommends file sizes between 100-250 MB compressed when loading data to optimize parallel processing. Smaller, compressed files can be loaded in parallel, which maximizes the efficiency of the virtual warehouses and speeds up the data loading process

 Snowflake uses a Role-Based Access Control (RBAC) model to manage access to data and resources. Additionally, Snowflake ensures that all data is encrypted, both at rest and in transit, to provide a high level of security for data stored within the platform. References: [COF-C03] SnowPro Core Certification Exam Study Guide

 When the retention period for an object ends in Snowflake, Time Travel on the historical data is dropped ©. This means that the ability to access historical data via Time Travel is no longer available once the retention period has expired2.

 Scaling out in Snowflake involves adding clusters of the same size to a virtual warehouse, which allows for handling more concurrent queries without affecting the performance of individual queries. Scaling up refers to resizing a virtual warehouse to increase its compute resources, enabling it to handle more complex workloads and larger queries more efficiently.

The list @my_stage command in Snowflake can be used to verify if data has been uploaded to an external stage named my_stage. This command provides a list of files thatare present in the specified stage2.

 Snowflake’s Fail-safe provides a 7-day period during which data in a permanent table may be recoverable, but only by Snowflake Support, not by user operations3.

When cloning a database containing stored procedures and regular views with fully qualified table references, the cloned views and stored procedures will reference the cloned tables in the cloned database (A). This ensures that the cloned database is a self-contained copy of the original, with all references pointing to objects within the same cloned database. References: SnowPro Core Certification cloningdatabase stored procedures views

When a running virtual warehouse in Snowflake issuspended and then restarted, the minimum amount of time it will incur charges for is 60 seconds2.

 Snowpipe retains load history for 14 days. This allows users to view and audit the data that has been loaded into Snowflake using Snowpipe within this time frame3.

The query results cache can be used as long as the data in the table has not changed since the last time the query was run. If the underlying data has changed, Snowflake will not use the cached results and will re-execute the query1.

Data masking policies in Snowflake allow for the obfuscation of specific data within a field, enabling some users to see the full data while limiting others. This feature is particularly useful for handling PII, ensuring that sensitive information is only visible to authorized users1.

To delete staged files from a Snowflake stage, you can specify the PURGE option in the COPY INTO < table >  command, which will automatically delete the files after they have been successfully loaded. Additionally, you can use the REMOVE command after the load completes to manually delete the files from the stage12.

References = DROP STAGE, REMOVE

 In a Snowflake role hierarchy, the top-level role is ACCOUNTADMIN. This role has the highest level of privileges and is capable of performing all administrative functions within the Snowflake account

The command USE WAREHOUSE < < warehouse name > > ; is used to set the virtual warehouse for the current session in Snowflake. This command specifies which virtual warehouse to use for executing queries in that session1.

The error “Max LOB size (16777216) exceeded” indicates that the size of the parsed column exceeds the maximum size allowed for a single column value in Snowflake, which is 16 MB. To resolve this issue, the file should be split into multiple smaller files that are within the recommended size range of 100 MB to 250 MB. This will ensure that each JSON document within the files is smaller than the maximum LOB size allowed. Compressing the file, using a larger-sized warehouse, or setting STRIP_OUTER_ARRAY=TRUE will not resolve the issue of the column size exceeding the maximum allowed. References: COPY INTO Error during Structured Data Load: “Max LOB size (16777216) exceeded…”

The parse_json() function in Snowflake interprets an input string as a JSON document and produces a VARIANT value containing the JSON document. This function is specifically designed for parsing strings that contain valid JSON information3.

 In Snowflake, both materialized views and transient tables will incur storage charges because they store data. They will also incur compute charges when queries are run against them, as compute resources are used to process the queries. References: [COF-C03] SnowPro Core Certification Exam Study Guide

 Protegrity is listed as a data tokenization integration partner for Snowflake. This partnership allows Snowflake users to utilize Protegrity’s tokenization solutions within the Snowflake environment3.

References = [COF-C03] SnowPro Core Certification Exam Study Guide, Snowflake Documentation

The Cloud Services layer in Snowflake is responsible for managing the result cache. When a query is executed, the results are stored in this cache, and subsequent identical queries can leverage these cached results without re-executing the entire query1.

The SQL command used to delete files from a Snowflake internal stage is REMOVE. This command can be used to remove files from either an internal or external stage within Snowflake

 Temporary and transient tables in Snowflake do not have a Fail-safe period. Once the session ends or the tables are dropped, the data is purged and not recoverable1.

To manage compliance with Personal Identifiable Information (PII) requirements, the Virtual Private Snowflake and Business Critical Editions of Snowflake should be used. These editions provide advanced security features necessary for handling sensitive data

The ACCESS_HISTORY view in the SNOWFLAKE.ACCOUNT_USAGE schema contains information about the access history of Snowflake objects, such as tables and views, within thelast 365 days1.

 The credit consumption for maintaining a materialized view is impacted by how often the base table changes © and whether the materialized view has a cluster key defined (D). Changes to the base table can trigger a refresh of the materialized view, consuming credits. Additionally, having a cluster key defined can optimize the performance and credit usage during the materialized view’s maintenance. References: SnowPro Core Certification materialized viewcredit consumption

Shares in Snowflake are named objects that encapsulate all the information required to share databases, schemas, tables, secure views, and secure UDFs. These objects can be added to a share by granting privileges on them to the share via a database role

 In Snowflake’s access control model, the default owner of an object is the role that was used to create the object. This role has the OWNERSHIP privilege on the object and can grant access to other roles1

The responsibilities of Snowflake’s Cloud Service layer include authentication (A), which ensures secure access to the platform; resource management (B), which involves allocating and managing compute resources; and query parsing and optimization (D), which improves the efficiency and performance of SQL query execution3.

Column-level security, which allows for the application of masking policies to columns in tables or views, is available starting from the Enterprise edition of Snowflake1.

References = [COF-C03] SnowPro Core Certification Exam Study Guide, Snowflake Documentation1

 Data encryption and Time Travel are part of Snowflake’s Continuous Data Protection (CDP) feature set that do not require additional configuration. Data encryption is automatically applied to all filesstored on internal stages, and Time Travel allows for querying and restoring data without any extra setup

 The COPY INTO SQL command with the option SINGLE=FALSE is used to unload data into multiple files. This option allows the data to be split into multiple files during the unload process. References: SnowPro Core Certification COPY INTO SQLcommand unload multiple files

 For loading data into Snowflake via Snowpipe, the recommended compressed file size is between 100-250 MB. This size range is optimal for balancing the performance of parallel processing and minimizing the overhead associated with handling many small files2.

 Dynamic data masking is a feature in Snowflake that allows administrators to define masking policies to protect sensitive data. It enables partial visibility of the data to certain roles, such as functional managers, while hiding it from others, like end users

The Enterprise edition of Snowflake allows for a dedicated metadata store, providing additional features designed for large-scale enterprises

Best practices for using the ACCOUNTADMIN role include ensuring that all users with this role use Multi-factor Authentication (MFA) for added security. Additionally, it is recommendedto assign the ACCOUNTADMIN role to at least two users to avoid delays in case of password recovery issues, but to as few users as possible to maintain strict control over account-level operations4.

A well-clustered table in Snowflake means that the data is organized in such a way that related data points are stored close to each other within the micro-partitions. This optimizes query performance by reducing the amount of scanned data. The diagram indicated by option C shows a well-clustered table, as it likely represents a more evenly distributed range of values across the micro-partitions1.

References = Snowflake Micro-partitions & TableClustering

 Organizing files into logical paths can significantly improve the efficiency of data loading from an external stage. This practice helps in managing and locating files easily, which can be particularly beneficial when dealing with large datasets or complex directory structures1.

When loading data into Snowflake, it is recommended to organize the data into single files with 100-250 MB of compressed data per file. This size range is optimal for parallel processing and can help in achieving better performance during data loading operations. References: [COF-C03] SnowPro Core Certification Exam Study Guide

The feature in Snowflake that allows a user to substitute a randomly generated identifier for sensitive data before loading it into Snowflake is known as External Tokenization. This process helps to secure sensitive data by ensuring that it is not exposed in its original form, thus preventing unauthorized access3.

 The supported file formats for unloading data from Snowflake include JSON, Parquet, and CSV. These formats are commonly used for their flexibility and compatibility with various data processing tools

 The Snowflake Cloud Services layer manages various services, including authentication and metadata management. This layer ties together all the different components of Snowflake to process user requests, manage sessions, and control access3.

To go back to the first version of a transient table created three days prior, one can use Time Travel if the DATA_RETENTION_TIME_IN_DAYS was set to at least 3 days. This allows the user to access historical data within the specified retention period. References: [COF-C03] SnowPro Core Certification Exam Study Guide

The LATERAL FLATTEN command in Snowflake produces a result set that includes several columns, among which PATH and INDEX are includedPATH indicates the path to the element within a data structure that needs to be flattened, and INDEX represents the index of the element if it is an array2.

 When a pipe is recreated using the CREATE OR REPLACE PIPE command, the load history of the pipe is reset. This means that Snowpipe will consider all files in the stage as new and will attempt to load them, evenif they were loaded previously by the old pipe2.