Practice Free COF-C03 SnowPro Core Certification 2026 Exam Exam Questions Answers With Explanation

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.

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

 The kind of Snowflake table that stores file-level metadata for each file in a stage is a directory table. A directory table is an implicit object layered ona stage and stores file-level metadata about the data files in the stage3.

Efficient pruning in Snowflake is indicated by the number of partitions scanned out of the total available. If a small percentage of partitions are scanned, it suggests thatthe pruning process is effectively narrowing down the data, which can lead to improved query performance

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.

 Periodic rekeying of micro-partitions is a feature that requires the Enterprise Edition of Snowflake or higher. This feature is part of Snowflake’s comprehensive approach to encryption key management, ensuring data security through regular rekeying1. References: [ COF-C03 ] SnowPro Core Certification Exam Study Guide

 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.

 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

https://docs.snowflake.com/en/user-guide/snowcd.html#:~:text=SnowCD%20(i.e.%20Snowflake%20Connectivity%20Diagnostic,their%20network%20connection%20to%20Snowflake.

Two key metrics in Snowflake’sQuery Profilethat provide insights for performance improvement are:

Remote Disk IO: This measures the time the query spends waiting on remote disk access, indicating potential performance issues related to I/O bottlenecks.

Pruning: This metric reflects how effectively Snowflake’s micro-partition pruning is reducing the data scanned. Better pruning (more partitions excluded) leads to faster query performance, as fewer micro-partitions need to be processed.

These metrics are essential for identifying and addressing inefficiencies in data retrieval and storage access, optimizing overall query performance.

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 optimize query performance in Snowflake, users can cluster a table, which organizes the data in a way that minimizes the amount of data scanned during queries. Additionally, enabling the searchoptimization service can improve the performance of selective point lookup queries on large tables34.

The query that contains a Snowflake hosted file URL in a directory table for a stage named bronzestage is select * from directory(@bronzestage). This query retrieves a list of all files on the stage along with metadata, including the Snowflake file URL for each file3.

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

In Snowflake, the ACCOUNTADMIN role is required to create resource monitors. Resource monitors are used to manage and monitor the consumption of compute resources. The ACCOUNTADMIN role has the necessary privileges to create, configure, and manage resource monitors across the account.

Snowflake Documentation: Resource Monitors

 For the ALLOWED VALUES tag property, the maximum number of possible string values for a single tag is 256. This allows for a wide range of values to be assigned to a tag when it is set on an object

The Kafka connector stores formatted information in a single column of type VARIANT. This column type is used to store semi-structured data like JSON or Avro, which allows for flexibility in the data structure

Pre-signed URLs in Snowflake allow users to access unstructured data without the need for authentication into Snowflake or passing an authorization token. These URLs are open and can be directly accessed ordownloaded by any user or application, making them ideal for business intelligence applications or reporting tools that need to display unstructured file contents

The WAIT_FOR_COMPLETION = TRUE parameter in an ALTER WAREHOUSE command ensures that the command does not return until the warehouse has completed resizing. This means that the command will wait until all the necessary compute resources have been provisioned and the warehouse size has been changed. References: [ COF-C03 ] SnowPro Core Certification Exam Study Guide

To ensure a data consumer has access to the required objects, a data provider can enable the data sharing feature and validate that the consumer can access the views or tables shared with them. References: Based on general data sharing practices in cloud services as of 2021.

 Snowflake data providers can share data residing in different databases through secure views. Secure views allow for the referencing of objects such as schemas, tables, and other views contained in one or more databases, as long as those databases belong to the same account. This enables providers to share data securely and efficiently with consumers. References: [ COF-C03 ] SnowPro Core Certification Exam Study Guide

 To use Partner Connect, the ACCOUNTADMIN role is required. Partner Connect allows account administrators to easily create trial accounts with selected Snowflake business partners and integrate these accounts with Snowflake

The correct answer is A. NOT NULL .

For standard Snowflake tables, Snowflake enforces the NOT NULL constraint. Other constraints such as PRIMARY KEY, FOREIGN KEY, and UNIQUE can be defined, but they are generally informational and are not enforced for standard tables.

Why A is correct:

A NOT NULL constraint prevents NULL values from being inserted into the constrained column.

Example:

CREATE TABLE customers (

customer_id NUMBER NOT NULL,

customer_name STRING

);

Why the other options are incorrect:

B. PRIMARY KEY constraints are not enforced for standard Snowflake tables.

C. FOREIGN KEY constraints are not enforced for standard Snowflake tables.

D. UNIQUE constraints are not enforced for standard Snowflake tables.

Official Snowflake documentation reference:

Snowflake documentation explains that for standard tables, NOT NULL constraints are enforced, while unique, primary key, and foreign key constraints are not enforced.

The SnowSQL file that can store connection information is named ‘config’. It is used to store user credentials and connection details for easy access to Snowflake instances. References: Based on general database knowledge as of 2021.

When using theCOPY INTO < table > command to load CSV data from a stage into a Snowflake table, one of the crucial guidelines to follow is that the number of columns in each row within the CSV file should be consistent. This ensures data integrity and allows for a smooth data loading process, as Snowflake expects each row in the CSV file to map directly to a row in the target table based on the number of columns. If there ' s a mismatch in the number of columns between any row in the file and the target table, Snowflake might return an error or produce unexpected results during the load operation.

It ' s important to note that while the CSV field delimiter can be specified to something other than a comma, ensuring the consistency in the number of columns across all rows is fundamental to successfully loading data.

??VeryComprehensiveExplanation=InSnowflake,variablesaredenotedbyadollarsign().Variables can be used in SQL statements where a literal constant is allowed, and they must be prefixedwith a $ sign to distinguish them from bind values and column names.

Credit charges for Snowflake virtual warehouses are calculated based on the size of the virtual warehouse and the length of time the warehouse is running. The size determines the compute resources available, and charges are incurred for the time these resources are utilized

A characteristic of a materialized view is that the data accessed through it can be stale. This is because the data in a materialized view may not reflect the latest changes in the base tables until the viewis refreshed

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.

 To monitor data storage for individual tables, the commands and objects that can be used are ‘SHOW STORAGE BY TABLE;’ and the Information Schema view ‘TABLE_STORAGE_METRICS’. These tools provide detailed information about the storage utilization for tables. References: Snowflake Documentation

 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

 Snowflake allows for only one resource monitor to be assigned at the account level. This monitor overseesthe credit usage of all the warehouses in the account. References: Snowflake Documentation

 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.

To provide a user with the necessary access to create a materialized view in a schema, the user must be granted a role that has the CREATE MATERIALIZED VIEW privilege on that schema. First, the role is granted to the user, and then the privilege is granted to the role

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.

 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 supports three types of internal stages: Named, User, and Table stages. These stages are used for staging data files to be loaded into Snowflake tables. Schema, Stream, and Database stages are not supported as internal stages inSnowflake. References: Snowflake Documentation1.

System tags help enable data classification for governance, privacy, and compliance. For example, Snowflake uses system-defined tags like SNOWFLAKE.CORE.DATA_CLASSIFICATION to detect and label sensitive data (e.g., PII, financial info).

The Snowflake Query Profile provides a graphic representation of the main components of the query processing. This visual aid helps users understand the execution details and performance characteristics of their queries4.

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

The Snowflake URL type used by directory tables is the scoped URL. This type of URL provides access to files in a stage with metadata, such as the Snowflake file URL, for each file

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.

 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

 In Snowflake, you can share several types of objects with other Snowflake accounts. These include schemas, secure views, and secure user-defined functions (UDFs). Sharing these objects allows for collaboration and data access across different Snowflake accounts while maintaining security and governance controls4.

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

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

 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.

The minimum Snowflake edition that supports database replication is the Enterprise edition. Database replication allows data to be replicated between different Snowflake accounts or regions, providing high availability and disaster recovery capabilities.

Snowflake Documentation: Database Replication

In Snowflake, Secure User-Defined Functions (UDFs) can be shared with other accounts using Snowflake ' s data sharing feature. This allows different Snowflake accounts to securely execute the UDFs without having direct access to the underlying data the functions operate on, ensuring privacy and security. The sharing is facilitated through shares created in Snowflake, which can contain Secure UDFs along with other database objects like tables and views.

The correct answers are A. Document AI and E. Cortex Analyst .

Snowflake Cortex is Snowflake’s integrated AI and machine learning capability set. It includes features for working with language models, document extraction, natural language analytics, and AI-powered application development.

Why A is correct:

Document AI is part of Snowflake Cortex. It is used to extract information from unstructured documents using AI.

Why E is correct:

Cortex Analyst is part of Snowflake Cortex. It enables natural language questions over structured data by using semantic models and Cortex capabilities.

Why the other options are incorrect:

B. Snowpipe is a data ingestion service, not part of the Cortex AI suite.

C. Streams are change data capture objects used to track table changes, not Cortex AI components.

D. Secure views are security/governance objects used to protect view definitions and support secure data sharing, not Cortex AI components.

Official Snowflake documentation reference:

Snowflake documentation describes Snowflake Cortex as a set of AI and ML capabilities and includes features such as Document AI and Cortex Analyst. Snowpipe, streams, and secure views belong to other Snowflake feature areas.

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

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

OBJECT_CONSTRUCT() converts each row into a single VARIANT object , which can then be unloaded in JSON format using COPY INTO. This is essential when converting relational data into semi-structured JSON .

The correct answer is A. Clone the table .

The most cost-efficient way to back up a Snowflake table is to use zero-copy cloning . A clone creates a copy of the table’s metadata without immediately duplicating the underlying micro-partitions.

Why A is correct:

When a table is cloned, Snowflake creates a new independent table object that initially shares the same micro-partitions as the source table. Because the underlying data is not copied at clone creation time, cloning is fast and storage-efficient.

This is why cloning is commonly used for backups, development, testing, and point-in-time snapshots.

Important concept:

At the instant a clone is created, the original table and cloned table share the same micro-partitions. The clone becomes independent after creation, and changes to either object do not modify the other object. Additional storage is used only when data changes occur.

Why the other options are incorrect:

B. Downloading table data to a stage creates additional data files and increases storage and operational overhead.

C. Sharing data with another account in another region is not the simplest or lowest-cost table backup method. Cross-region replication or sharing can involve additional costs.

D. A CTAS statement physically creates a new table from query results and can duplicate data storage, making it less storage-efficient than cloning.

Official Snowflake documentation reference:

Snowflake documentation explains that zero-copy cloning creates a copy of a database, schema, or table without duplicating the underlying storage at the time the clone is created.

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.

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

The minimum Snowflake edition required for database failover and fail-back between Snowflake accounts for business continuity and disaster recovery is the Business Critical edition. References: Snowflake Documentation3.

 In Snowflake, a User-Defined Table Function (UDTF) can be accessed in the FROM clause of a query. UDTFs return a set of rows with one or more columns, which can be queried like a regular table

Snowpipe uses key-based authentication for its REST endpoints. This involves generating and using a key pair (public and private keys) to securely authenticate API requests.

Generate Key Pair:Generate a public and private key pair.

Register Public Key:Register the public key with the Snowflake user that will be making the API requests.

Authenticate Requests:Use the private key to sign API requests sent to Snowpipe REST endpoints.

The correct answer is D. Directory tables can be joined with other tables in Snowflake .

A directory table stores file-level metadata for files in a stage. This metadata can be queried using SQL, which means it can also be filtered, selected from, and joined with other Snowflake tables.

Why D is correct:

Because directory table metadata can be queried like table data, users can join that metadata with other Snowflake tables. This is useful for workflows where file metadata needs to be compared with control tables, audit tables, or processing logs.

Example concept:

SELECT d.relative_path, l.load_status

FROM DIRECTORY(@my_stage) d

LEFT JOIN load_log l

ON d.relative_path = l.file_name;

Why the other options are incorrect:

A. Directory tables store file-level metadata, but not in a temporary table. They are associated with stages.

B. Directory tables do not have their own independent privilege model. Access is controlled through privileges on the associated stage.

C. Any user cannot configure directory table privileges independently.

Official Snowflake documentation reference:

Snowflake documentation explains that directory tables store file-level metadata about staged files and that users can query this metadata with SQL, enabling joins with other tables.

A Directory Table stores metadata about unstructured data files in a stage, such as size, last-modified timestamp, and file URL. This metadata helps query or track files without ingesting them into a table.

TheGET_PRESIGNED_URLfile function in Snowflake provides a URL with access to a file on a stage without requiring authentication and authorization. This is particularly useful for sharing data files stored in Snowflake stages with external parties securely and conveniently. The presigned URL generated by this function gives temporary access to the file, which expires after a specified duration.

Example usage ofGET_PRESIGNED_URL:

SELECTGET_PRESIGNED_URL( ' < stage_name > ' , ' < file_path > ' );

This function generates a URL that can be used to directly access a file in the stage, making it easier to share data without compromising security.

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

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

Why A is correct:

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

Example:

SELECT GET(src, ' customer ' )

FROM my_table;

Why C is correct:

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

Example:

SELECT GET_PATH(src, ' customer.name ' )

FROM my_table;

Why the other options are incorrect:

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

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

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

Official Snowflake documentation reference:

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

The correct SQL statement to list the version of the JDBC (Java Database Connectivity) drivers currently being used is to executeSELECT CURRENT_JDBC_VERSION();from within SnowSQL or any client application that utilizes JDBC for connecting to Snowflake. Snowflake provides specific functions to query the version of the client drivers or connectors being used, such as JDBC, ODBC, and others. TheCURRENT_JDBC_VERSION()function is designed specifically to return the version of the JDBC driver in use.

It ' s important to note that Snowflake supports various types of drivers and connectors for connecting to different client applications, including ODBC, JDBC, Python, and others. Each of these connectors has its own method or function for querying the current version in use. For JDBC, the appropriate function isCURRENT_JDBC_VERSION(), reflecting the specificity required to obtain version information relevant to the JDBC driver specifically.

To minimize Continuous Data Protection (CDP) costs when dealing with large, high-churn dimension tables in Snowflake, using transient tables is a recommended approach.

Transient Tables: These are designed for data that does not require fail-safe protection. They provide the benefit of reducing costs associated with continuous data protection since they do not have the seven-day Fail-safe period that is mandatory for permanent tables.

Periodic Copying to Permanent Tables: By periodically copying data from transient tables to permanent tables, you can achieve a balance between data protection and cost-efficiency. Permanent tables offer the extended data protection features, including Time Travel and Fail-safe, butthese features can be applied selectively rather than continuously, reducing the overall CDP costs.

Snowflake Documentation on Transient Tables

Snowflake Documentation on Time Travel & Fail-safe

The correct answer is D. When the system estimates that there is enough query load to keep the new cluster busy for at least 6 minutes .

In Snowflake, a multi-cluster warehouse can use either a Standard or Economy scaling policy. The Economy scaling policy is more conservative. It starts additional clusters only when Snowflake estimates that the extra cluster will have enough work to justify the additional compute cost.

Why D is correct:

With the Economy scaling policy, Snowflake starts another cluster only if the system estimates there is enough query load to keep the new cluster busy for approximately 6 minutes. This helps reduce credit consumption by avoiding unnecessary short-lived cluster starts.

Why the other options are incorrect:

A. The condition is not based on all active clusters running queries for more than 6 minutes.

B. A single queued query for more than 6 minutes is not the exact Economy scaling rule.

C. The rule is not specifically based on 3 or more queued queries.

D. This matches Snowflake’s documented behavior for the Economy scaling policy.

Official Snowflake documentation reference:

Snowflake documentation explains that the Economy scaling policy conserves credits by starting additional clusters only when Snowflake estimates enough query load exists to keep the cluster busy for at least 6 minutes.

In the execution time summary of a Query Profile in Snowflake, the categories included provide insights into various aspects of query execution. " Pruning " refers to the process by which Snowflake reduces the amount of data scanned by eliminating partitions of data that are not relevant to the query, thus improving performance. " Initialization " represents the time taken for query planning and setup before actual execution begins. These metrics are crucial for understanding and optimizing query performance.

The correct answer is B. In a named file format .

When data is regularly loaded or unloaded using the same format settings, Snowflake recommends defining those settings in a named file format. This avoids repeating the same file format options in every COPY command and reduces operational overhead.

Why B is correct:

A named file format stores reusable format options, such as file type and formatting behavior. For semi- structured data, named file formats can define options for formats such as JSON, Avro, ORC, Parquet, or XML.

Example:

CREATE FILE FORMAT my_json_format

TYPE = JSON

COMPRESSION = AUTO;

COPY INTO @my_stage/output/

FROM my_table

FILE_FORMAT = my_json_format;

Why the other options are incorrect:

A. A table definition defines table columns and properties, not unload formatting options.

C. A named stage can include a file format reference, but the reusable copy/file parsing settings themselves are defined in a named file format.

D. Copy options can be specified directly in the COPY command, but that is less efficient for repeated, similarly formatted unload operations.

Official Snowflake documentation reference:

Snowflake documentation explains that named file formats are reusable database objects that define format options for loading and unloading data.

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.

The ORGADMIN role in Snowflake is responsible for managing organization-level administrative functions, which include managing billing and credit data. This role has the highest level of administrative privileges and can oversee multiple Snowflake accounts within an organization.

Snowflake Documentation: Account and Organization Roles

In Snowflake, tables represent a logical structure through which users interact with the stored data. The actual physical data is stored in micro-partitions managed by Snowflake, and the logical table structure provides the means by which SQL operations are mapped to this data. This architecture allows Snowflake to optimize storage and querying across its distributed, cloud-based data storage system.

To list all network policies available in an account, the correct command isSHOW NETWORK POLICIES. Network policies in Snowflake are used to define and enforce rules for how users can connect to Snowflake, including IP whitelisting and other connection requirements. TheSHOW NETWORK POLICIEScommand provides a list of all network policies defined within the account, along with their details.

TheDESCRIBE SESSION POLICYandDESCRIBE NETWORK POLICYcommands do not exist in Snowflake SQL syntax. TheSHOW SESSION POLICIEScommand is also incorrect, as it does not pertain to the correct naming convention used by Snowflake for network policy management.

UsingSHOW NETWORK POLICIESwithout any additional parameters will display all network policies in the account, which is useful for administrators to review and manage the security configurations pertaining to network access.

Snowpark-optimized virtual warehouses in Snowflake are designed to efficiently handle workloads with large memory requirements. Snowpark is a developer framework that allows users to write code in languages like Scala, Java, and Python to process data in Snowflake. Given the nature of these programming languages and the types of data processing tasks they are typically used for, having a virtual warehouse that can efficiently manage large memory-intensive operations is crucial.

Understanding Snowpark-Optimized Virtual Warehouses:

Snowpark allows developers to build complex data pipelines and applications within Snowflake using familiar programming languages.

These virtual warehouses are optimized to handle the execution of Snowpark workloads, which often involve large datasets and memory-intensive operations.

Large Memory Requirements:

Workloads with large memory requirements include data transformations, machine learning model training, and advanced analytics.

These operations often need to process significant amounts of data in memory to perform efficiently.

Snowpark-optimized virtual warehouses are configured to provide the necessary memory resources to support these tasks, ensuring optimal performance and scalability.

Other Considerations:

While Snowpark can handle other types of workloads, its optimization for large memory tasks makes it particularly suitable for scenarios where data processing needs to be done in-memory.

Snowflake’s ability to scale compute resources dynamically also plays a role in efficiently managing large memory workloads, ensuring that performance is maintained even as data volumes grow.

Snowflake Documentation: Introduction to Snowpark

Snowflake Documentation: Virtual Warehouses

Snowflake bills data storage monthly in units of compressed terabytes (TB) . Usage below 1 TB is prorated.

In Snowflake, resizing a virtual warehouse that is currently suspended does not immediately provision the new compute resources. Instead, the change in size is recorded, and the additional compute resources are provisioned when the warehouse is resumed. This means that the action of resizing a suspended warehouse does not cause it to resume operation automatically. The warehouse remains suspended until an explicit command to resume it is issued, or until it automatically resumes upon the next query execution that requires it.

This behavior allows for efficient management of compute resources, ensuring that credits are not consumed by a warehouse that is not in use, even if its size is adjusted while it is suspended.

When querying VARIANT columns (such as JSON), the values are often returned as strings . To convert them to usable SQL types, users must apply the CAST() function (e.g., CAST(column:value AS NUMBER)).

Snowflake ' s Data Exchange allows users to create and manage a group of accounts to which they can offer data shares. This platform facilitates secure and governed data sharing within and between organizations.

Data Exchange Setup:Users can set up a Data Exchange and invite other Snowflake accounts to join.

Sharing Data:Within the Data Exchange, users can offer shares to the entire group or to specific accounts.

Governance and Security:Data Exchange provides tools for data governance and security, ensuring that shared data is only accessible to authorized accounts.

The Query Profile tool provides a visual breakdown of query execution including:

Execution time per step

Rows processed , scanned, or returned This helps analyze performance bottlenecks and optimize SQL.

To return results from the results cache in Snowflake, certain conditions must be met:

Privileges: The user must have the appropriate privileges on the objects associated with the query. This ensures that only authorized users can access cached data.

Time Frame: The query must have been run within 24 hours of the previously-run query. Snowflake’s results cache is designed to store the results of queries for a short period, typically 24 hours, to improve performance for repeated queries.

The details section of a Query Profile in Snowflake provides users with various statistics and information about the execution of a query. One of the key pieces of information that can be determined from this section is the total duration of the query, which helps in understanding the performance and identifying potential bottlenecks. 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

For Snowpipe auto-ingest on Amazon S3, cloud event notifications are configured so Snowflake is notified when new files arrive. The pipe must also be created with AUTO_INGEST = TRUE.

Why A and D are correct:

Amazon S3 event notifications can publish file arrival events through Amazon SNS/SQS integration, and Snowpipe uses those notifications when AUTO_INGEST is enabled.

In Snowflake, Cloud Services costs are incurred when the Cloud Services usage exceeds 10% of the compute usage (measured in credits). Therefore, scenarios A and E would result in Cloud Services charges because the Cloud Services usage is more than 10% of the compute credits used.

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

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

Corrected wording:

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

Why C is correct:

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

Why the other options are incorrect:

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

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

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

Official Snowflake documentation reference:

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

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

Virtual Warehouse Sizes:

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

Each size corresponds to a specific level of compute resources.

Minimum Size Requirement for Snowpark:

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

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

Snowflake Documentation: Virtual Warehouse Sizes

The correct answer is A. The table data does not require Fail-safe .

Transient tables are designed for data that does not need the same level of data recovery protection as permanent tables. Unlike permanent tables, transient tables do not have a Fail-safe period.

Why A is correct:

Fail-safe storage can add cost because Snowflake retains historical data for recovery by Snowflake support after the Time Travel retention period ends. Since transient tables do not have Fail-safe, they can reduce storage costs for data that does not require this additional protection.

Common use cases include:

Temporary ETL data

Staging data

Intermediate transformation data

Reproducible data

Non-critical working data

Why the other options are incorrect:

B. Long-term critical storage is usually better suited for permanent tables because they support Fail-safe.

C. Frequent access and modification alone does not make a transient table the best cost choice. The key factor is whether Fail-safe is needed.

D. Sharing data with external accounts is not the primary cost-effective use case for transient tables.

Official Snowflake documentation reference:

Snowflake documentation explains that transient tables persist until explicitly dropped, support Time Travel with limited retention, and do not have a Fail-safe period.

In Snowflake, the default access level for any securable object (such as a table, view, or schema) is " No access " until explicit access is granted. This means that when an object is created, only the owner of the object and roles with the necessary privileges can access it. Other users or roles will not have any form of access to the object until it is explicitly granted.

This design adheres to the principle of least privilege, ensuring that access to data is tightly controlled and that users and roles only have the access necessary for their functions. To grant access, the owner of the object or a role with the GRANT option can use the GRANT statement to provide specific privileges to other users or roles.

For example, to grant SELECT access on a table to a specific role, you would use a command similar to:

GRANTSELECTONTABLEmy_tableTOROLE my_role;

The correct answer is B. Hybrid .

A hybrid table is Snowflake’s table type designed for transactional workloads. Hybrid tables support row-level operations and are optimized for low-latency, high-concurrency transactional access patterns.

Why B is correct:

Hybrid tables are intended for transactional and operational workloads, such as applications that require fast point lookups, inserts, updates, and deletes.

Why the other options are incorrect:

A. Event tables store event and log data generated by Snowflake features such as Snowpark logging and tracing.

C. Dynamic tables are used for automated data transformation pipelines. They are not optimized for transactional workloads.

D. Permanent tables are the standard Snowflake table type for persistent analytical storage, but they are not specifically optimized for transactional workloads like hybrid tables.

Official Snowflake documentation reference:

Snowflake documentation describes hybrid tables as Snowflake tables optimized for transactional workloads that require low-latency reads and writes.

The correct answer is C. Query Acceleration Service .

The Query Acceleration Service allows Snowflake to use additional compute resources to accelerate eligible queries. It is especially useful for queries that scan large amounts of data or include expensive operations that can benefit from extra parallel processing.

Why C is correct:

Query Acceleration Service temporarily uses additional Snowflake-managed compute resources for eligible queries. This can improve performance without permanently resizing the virtual warehouse.

Why the other options are incorrect:

A. Search Optimization Service improves highly selective lookup queries by maintaining search access paths, but it does not temporarily scale compute for expensive queries.

B. A warehouse scaling policy controls how multi-cluster warehouses add or remove clusters, but it is not the feature designed to accelerate individual expensive queries.

D. A multi-cluster warehouse improves concurrency by adding clusters, but it does not split or accelerate a single complex query in the way Query Acceleration Service can.

Official Snowflake documentation reference:

Snowflake documentation describes Query Acceleration Service as a feature that can improve query performance by using additional compute resources for eligible parts of queries.

 Snowflake’s architecture allows for the use of a query result cache that stores the results of queries for a period of time. If the same query is run again and the underlying data has not changed, Snowflake can retrieve the result from this cache without needing to re-run the query on an active warehouse, thus saving on compute resources.

The Snowflake storage layer is responsible for storing data in an optimized, compressed, columnar format. This includes micro-partitions, which are the fundamental storage units that contain the actual data stored in Snowflake. Additionally, persisted query results, which are the results of queries that have been materialized and stored for future use, are also kept within this layer. This design allows for efficient data retrieval and management within the Snowflake architecture1.

The parameter STATEMENT_QUEUED_TIMEOUT_IN_SECONDS sets the limit for a query to wait in the queue in order to get its chance of running on the warehouse. The query will quit after reaching this limit. By default, the value of this parameter is 0 which mean the queries will wait indefinitely in the waiting queue

https://community.snowflake.com/s/article/Warehouse-Concurrency-and-Statement-Timeout-Parameters#:~:text=The%20parameter%20STATEMENT_QUEUED_TIMEOUT_IN_SECONDS%20sets%20the,indefinitely%20in%20the%20waiting%20queue.

 When an external stage is dropped in Snowflake, the reference to the external storage location is removed, but the actual files within the external storage (like Amazon S3, Google Cloud Storage, or Microsoft Azure) are not deleted. This means that the data remains intact in the external storage location, and only the stage object in Snowflake is removed.

On the other hand, when an internal stage is dropped, any files that were uploaded to the stage are deleted along with the stage itself. These files are not recoverable once the internal stage is dropped, as they are permanently removed from Snowflake’s storage.

Parquet is a columnar storage file format that is optimized for performance in Snowflake. It is designed to be efficient for both storage and query performance, particularly for complex queries onlarge datasets. Parquet files support efficient compression and encoding schemes, which can lead to significant savings in storage and speed in query processing, making it the most performant file format for loading data into Snowflake.

Secure data sharing in Snowflake allows users to share various objects between Snowflake accounts without physically copying the data, thus not consuming additional storage. Among the options provided, external tables can be shared through secure data sharing. External tables are used to query data directly from files in a stage without loading the data into Snowflake tables, making them suitable for sharing across different Snowflake accounts.

Alation is known for specializing in data catalog solutions and is a partner of Snowflake. Data catalog solutions are essential for organizations to effectively manage their metadata and make it easily accessible and understandable for users, which aligns with the capabilities provided by Alation.

 Micro-partitions in Snowflake are immutable objects, which means once they are written, they cannot be modified. This immutability supports the use of Time Travel, allowing users to access historical data within a defined period. Additionally, micro-partitions can significantly reduce the amount of I/O from object storage to virtual warehouses. This is because Snowflake’s query optimizer can skipover micro-partitions that do not contain relevant data for a query, thus reducing the amount of data that needs to be scanned and transferred.

 Materialized views in Snowflake are a feature that allows for the pre-computation and storage of query results for faster query performance. This feature is available starting from the Enterprise Edition of Snowflake. It is not available in the Standard Edition, and while it is also available in higher editions like Business Critical and Virtual Private Snowflake, the Enterprise Edition is the minimum requirement.

In Snowflake, Data Shares can be created and managed in two primary ways:

Via the Snowflake Web Interface (UI): Users can create and manage shares through the graphical interface provided by Snowflake, which allows for a user-friendly experience.

Via SQL commands: Snowflake also allows the creation and management of shares using SQL commands. This method is more suited for users who prefer scripting or need to automate the process.

 In all Snowflake editions, two key capabilities are universally available:

B. Automatic encryption of all data: Snowflake automatically encrypts all data stored in its platform, ensuring security and compliance with various regulations. This encryption is transparent to users and does not require any configuration or management.

D. Object-level access control: Snowflake provides granular access control mechanisms that allow administrators to define permissions at the object level, including databases, schemas, tables, and views. This ensures that only authorized users can access specific data objects.

These features are part of Snowflake’s commitment to security and governance, and they are included in every edition of the Snowflake Data Cloud.

 In Snowflake, when the account is located on Microsoft Azure, a valid source for an external stage can be an Azure container or a folder path within an Azure container. This includes Azure Blob storage which is accessible via the azure:// endpoint. A Windows server file share on Azure, if configured properly, can be a valid source for staging data files for Snowflake. Options A, B, and C are not supported as direct sources for an external stage in Snowflake on Azure12. References: [ COF-C03 ] SnowPro Core Certification Exam Study Guide

 When unloading data to a stage, it is recommended to define an individual file format. This ensures that the data is unloaded in a consistent and expected format, which can becrucial for downstream processing and analysis2

The primary responsibility of Snowflake’s virtual warehouses is to execute queries. Virtual warehouses are one of the key components of Snowflake’s architecture, providing the compute power required to perform data processing tasks such as running SQL queries, performing joins, aggregations, and other data manipulations.

 One of the key features of Snowflake’s architecture is its unique approach to eliminating resource contention through the use of virtual warehouses. This is achieved by separating storage and compute resources, allowing multiple virtual warehouses to operate independently on the same data without affecting each other. This means that different workloads, such as loading data, running queries, or performing complex analytics, can be processed simultaneously without any performance degradation due to resource contention.

 The Snowflake Cloud Services layer manages a variety of services that are crucial for the operation of the Snowflake platform. Among these services, Authentication and Metadata management are key components. Authentication is essential for controlling access to the Snowflake environment, ensuring that only authorized users can perform actions within the platform. Metadata management involves handling all the metadata related to objects within Snowflake, such as tables, views, and databases, which is vital for the organization and retrieval of data.

SnowSQL is the command-line client for Snowflake that allows users to execute SQL queries and perform all DDL and DML operations, including staging files for bulk data loading. It is specifically designed for scripting and automating tasks.

When loading data into Snowflake, it is recommended to:

C. Load files that are approximately 100-250 MB (or larger): This size is optimal for parallel processing and can help to maximize throughput. Smaller files can lead to overhead that outweighs the actual data processing time.

D. Avoid using embedded characters such as commas for numeric data types: Embedded characters can cause issues during data loading as they may be interpreted incorrectly. It’s best to clean the data of such characters to ensure accurate and efficient data loading.

These best practices are designed to optimize the data loading process, ensuring that data is loaded quickly and accurately into Snowflake.

 To address a high volume of queries queuing in a warehouse, Snowflake recommends two best practices:

A. Use multi-clustered warehousing to scale out warehouse capacity: This approach allows for the distribution of queries across multiple clusters within a warehouse, effectively managing the load and reducing the queue volume.

B. Scale up the warehouse size to allow Queries to execute faster: Increasing the size of the warehouse provides more compute resources, which can reduce the time it takes for queries to execute and thus decrease the number of queries waiting in the queue.

These strategies help to optimize the performance of the warehouse by ensuring that resources are scaled appropriately to meet demand.

 Snowpipe is used for continuous, automated data loading into Snowflake. It uses a COPY INTO < table >  statement within a pipe object to load data from files as soon as they are available in a stage. Snowpipe does not execute UDFs, stored procedures, or insert statements. References: Snowpipe | Snowflake Documentation

In Snowflake, Streams are the objects that track Data Manipulation Language (DML) changes made to tables, such as inserts, updates, and deletes. Streams record these changes along with metadata about each change, enabling actions to be taken using the changed data. This process is known as change data capture (CDC)2.

Fail-safe is a feature in Snowflake that provides an additional layer of data protection. After the Time Travel retention period ends, Fail-safe offers a non-configurable 7-day period during which historical data may be recoverable by Snowflake. This period is designed to protect against accidental data loss and is not intended for customer access.

 Understanding and viewing Fail-safe |Snowflake Documentation

The list command in Snowflake is used to validate and display the list of files in a specified stage. When a user has unloaded data to a stage, running the list @file__stage command will show all the files that have been uploaded to that stage, allowing the user to verify the data that was unloaded.

When a cloned table is replicated to a secondary database in Snowflake, the following occurs:

C. The physical data is replicated: The actual data of the cloned table is physically replicated to the secondary database. This ensures that thesecondary database has its own copy of the data, which can be used for read-only purposes or failover scenarios1.

E. Metadata pointers to cloned tables are replicated: Along with the physical data, the metadata pointers that refer to the cloned tables are also replicated. This metadata includes information about the structure of the table and any associated properties2.

It’s important to note that while the physical data and metadata are replicated, the secondary database is typically read-only and cannot be used for write operations. Additionally, while there may be additional storage costs associated with the secondary account, this is not a direct result of the replication process but rather a consequence of storing additional data.

Data shares in Snowflake can be created and managed through the Snowflake web interface, which provides a user-friendly graphical interface for various operations. Additionally, SQL commands can be used to perform these tasks programmatically, offering flexibility and automation capabilities123.

 Virtual Warehouses in Snowflake can indeed be resized while they are suspended. Resizing a warehouse involves changing the number of compute resources (servers) allocated to it, which can be done to adjust performance and cost. When a warehouse is suspended, it is not currently running any queries, but its definition and metadata remain intact, allowing for modifications like resizing.

A VARIANT column in Snowflake can store semi-structured data types. This includes:

B. OBJECT: An object is a collection of key-value pairs in JSON, and a VARIANT column can store this type of data structure.

D. ARRAY: An array is an ordered list of zero or more values, which can be of any variant-supported data type, including objects or other arrays.

The VARIANT data type is specifically designed to handle semi-structured data like JSON, Avro, ORC, Parquet, or XML, allowing for the storage of nested and complex data structures.

 Snowflake’s architecture is designed to handle high concurrency workloads through several features, two of which are particularly effective:

B. The use of warehouse auto scaling: This feature allows Snowflake to automatically adjust the compute resources allocated to a virtual warehouse in response to the workload. If there is an increase in concurrent queries, Snowflake can scale up the resources to maintain performance.

D. Use of multi-clustered warehouses: Multi-clustered warehouses enable Snowflake to run multiple clusters of compute resources simultaneously. This allows for the distribution of queries across clusters, thereby reducing the load on any single cluster and improving the system’s ability to handle a high number of concurrent queries.

These features ensure that Snowflake can manage varying levels of demand without manual intervention, providing a seamless experience even during peak usage.

Snowflake does not require source data files to be no larger than 16MB. In fact, Snowflake recommends that for optimal load performance, data files should be roughly 100-250 MB in size when compressed. However, it is not recommended to load very large files (e.g., 100 GB or larger) due to potential delays and wasted credits if errors occur. Smaller files should be aggregated to minimize processing overhead, and larger files should be split to distribute the load among compute resources in an active warehouse.

 Even if a developer is granted ownership of a table with a masking policy, they will not be able to modify the table to read the masked data if their role does not have the necessary permissions. Ownership of a table does not automatically confer the ability to alter masking policies, which are designed to protect sensitive data. Masking policies are applied at the schema level and requirespecific privileges to modify12.

The most efficient way to get files into Snowflake, especially when new files are being written to a cloud storage location at frequent intervals, is to use Snowpipe with auto-ingest. Snowpipe is Snowflake’s continuous data ingestion service that loads data as soon as it becomes available in a cloud storage location. By setting up cloud provider notifications, Snowpipe can be triggered automatically whenever new files are written to the storage location, ensuring that the data is loaded into Snowflake with minimal latency and without the need for manual intervention or scheduling frequent tasks.

 Snowflake Time Travel is a powerful feature that allows users to access historical data within a defined period. It enables two key capabilities:

B. Restoring data-related objects that have been deleted within the past 90 days: Time Travel can be used to restore tables, schemas, and databases that have been accidentally or intentionally deleted within the Time Travel retention period.

C. Conducting point-in-time analysis for BI reporting: It allows users to query historical data as it appeared at a specific point in time within the Time Travel retention period, which is crucial for business intelligence and reporting purposes.

While Time Travel does allow querying of past data, it is limited to the retention period set for the Snowflake account, which is typically 1 day for standard accounts and can be extended up to 90 days for enterprise accounts. It does not enable querying or restoring objects created or deleted beyond the retention period, nor does it provide analysis over all periods of time.

 In the query profiler view, the components that represent areas that can be used to help optimize query performance include ‘Bytes scanned’ and ‘Number of partitions scanned’. ‘Bytes scanned’ indicates the total amount of data the query had to read and is a direct indicator of the query’s efficiency. Reducing the bytes scanned can lead to lower data transfer costs and faster query execution. ‘Number of partitions scanned’ reflects how well the data is clustered; fewer partitions scanned typically means better performance because the system can skip irrelevant data more effectively.

When a Snowflake virtual warehouse is resized, the data cached in the warehouse is not lost. This is because the cache is maintained independently of the warehouse size. Resizing a warehouse, whether scaling up or down, does not affect the cached data, ensuring that query performance is not impacted by such changes.

 The STRIP_OUTER_ARRAY file format option in Snowflake is used when loading JSON documents that are composed of a large array containing multiple records. When this option is enabled, it removes the outer array structure, which allows each record within the array to be loaded as a separate row in the table. This is particularly useful for efficiently loading JSON data that is structured as an array of records1.

 The correct statement to accommodate the request for a marketing co-worker to change the size of their medium virtual warehouse called mktg__WH is to grant the MODIFY privilege on the warehouse to the ROLE MARKETING. This privilege allows the role to change the warehouse size among other properties.

Snowflake’s architecture allows for flexibility in account hosting across different cloud vendors and regions. This means that within a single organization, different Snowflake accounts can be set up in various cloud environments, such as AWS, Azure, or GCP, and in different geographical regions. This allows organizations to leverage the global infrastructure of multiple cloud providers and optimize their data storage and computing needs based on regional requirements, data sovereignty laws, and other considerations.

https://docs.snowflake.com/en/user-guide/intro-regions.html

A clustering key in Snowflake is used to co-locate similar data within the same micro-partitions to improve query performance, especially for large tables where data is not naturally ordered or has become fragmented due to extensive DML operations. The appropriate use of a clustering key can lead to improved scan efficiency and better column compression, resulting in faster query execution times.

The indicators that it may be appropriate to use a clustering key for a table include:

D. Queries on the table are running slower than expected: This can happen when the data in the table is not well-clustered, leading to inefficient scans during query execution.

E. The clustering depth for the table is large: A large clustering depth indicates that the table’s data is spread across many micro-partitions, which can degrade query performance as more data needs to be scanned.

The Information Schema and Account Usage Share in Snowflake provide metadata and historical usage data for various objects within a Snowflake account. Specifically, they offer storage information for Tables, Databases, and Internal Stages. These schemas contain views and table functions that allow users to query object metadata and usage metrics, such as the amount of data stored and historical activity.

Tables: The storage information includes data on the daily average amount of data in database tables.

Databases: For databases, the storage usage is calculated based on all the data contained within the database, including tables and stages.

Internal Stages: Internal stages are locations within Snowflake for temporarily storing data, and their storage usage is also tracked.

 To evaluate the details of dynamic data masking, the POLICY_REFERENCES and ACCESS_HISTORY views in the account_usage schema are used. The POLICY_REFERENCES view provides information about the objects to which a masking policy is applied, and the ACCESS_HISTORY view contains details about access to the masked data, which can be used to audit and verify the application of dynamic data masking policies.

When a virtual warehouse in Snowflake is resized, specifically when it is increased in size, the additional compute resources become immediately available to all running and queued queries. Thismeans that the performance of these queries can improve due to the increased resources. Conversely, when the size of a warehouseis reduced, the compute resources are not removed until they are no longer being used by any current operations1.

 The COPY INTO command in Snowflake can be configured to output data into a single file by setting the MAX_FILE_NUMBER option to 1. This option limits the number of files generated by the command, ensuring that only one file is created regardless of the amount of data being exported.

The ACCOUNTADMIN role is the most powerful role in Snowflake and should be limited to a select number of users within an organization. It is responsible for account-level configurations and should not be used for day-to-day object creation or management. Granting a custom role to ACCOUNTADMIN could inadvertently give broad access to users with this role, which is not a recommended security practice.

https://docs.snowflake.com/en/user-guide/intro-summary-loading.html#:~:text=For%20delimited%20files%20(CSV%2C%20TSV,encoding%20to%20use%20for%20loading.

For delimited files (CSV, TSV, etc.), the default character set is UTF-8. To use any other characters sets, you must explicitly specify the encoding to use for loading. For the list of supported character sets, see Supported Character Sets for Delimited Files (in this topic).

The PUT command is used to load data into an internal stage in Snowflake. This command uploads data files from a local file system to a named internal stage, making the data available for subsequent loading into a Snowflake table using the COPY INTO command.

In Snowflake, a Pipe is the object designed to enable the continuous, near-real-time loading of data from files as soon as they are available in a cloud storage location. Pipes use Snowflake’s COPY command to load data and can be associated with a Stage object to monitor for new files. When new data files appear in the stage, the pipe automatically loads the data into the target table.

Snowflake credits are the unit of measure for the compute resources used in Snowflake. The number of credits consumed depends on the size of the virtual warehouse and the time it is running. Larger warehouses consume more credits per hour than smaller ones, and credits are billed for the time the warehouse is active, regardless of the actual usage within that time.

Snowflake’s Time Travel feature is used for both querying historical data in tables and restoring and cloning historical data in databases, schemas, and tables3. It allows users to access historical data within a defined period (1 day by default, up to 90 days for Snowflake Enterprise Edition) and is a key feature for data recovery and management. References: [ COF-C03 ] SnowPro Core Certification Exam Study Guide

Secure sharing in Snowflake allows users to share specific objects with other Snowflake accounts without physically copying the data, thus not consuming additional storage. Tables and Secure User Defined Functions (UDFs) are among the objects that can be shared using this feature. Materialized views, sequences, and procedures are not shareable objects in Snowflake.

 Snowflake’s default Multi-Factor Authentication (MFA) service is powered by Duo Security. Users are required to install the Duo Mobile application on their devices to use MFA for increased login security when connecting to Snowflake. This service is managed entirely by Snowflake, and users donot need to sign up separately with Duo1.

 When a query in Snowflake is too large to fit into the available memory, it will start spilling to remote storage. This is an indication that the memory allocated for the query is insufficient for its execution, and as a result, Snowflake uses remote disk storage to handle the overflow. This spill to remote storage can lead to slower query performance due to the additional I/O operations required.

 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.

 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 PUT command is used to load data files into a Snowflake stage. This command uploads data files from a local filesystem to a specified stage in Snowflake

 In Snowflake, a schema is a logical grouping of database objects, which can include streams and external tables. A stream is an object that allows users to query data that has changed in specified tables or views, and an external table is a table that references data stored outside of Snowflake. Roles, warehouses, users, and shares are not contained within a schema. References: SHOW OBJECTS, Database, Schema, & Share DDL

 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.

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

 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

The Search Optimization Service significantly improves the performance of selective point lookup queries on tables by creatingand maintaining a persistent data structure called a search access path, which allows some micro-partitions to be skipped when scanning the table

In Snowflake, to extract a specific element from a JSON stored in a VARIANT column, the correct syntax is to use the dot notation. Therefore, the query select src.dealership from car_sales; will return the dealership information contained within each JSON object in the src column.

 Best practices for loading data into Snowflake include aiming for data file sizes between 100 MB and 250 MB when compressed, as this size is optimal for parallel processing and minimizes overhead. Enclosing fields with delimiter characters in quotes ensures proper field recognition during the load process. Splitting large files into smaller ones allows for better distribution of the load across compute resources, enhancing performance and efficiency.

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.

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

 Snowflake’s architecture is described as a multi-cluster, shared data architecture. This design combines the simplicity of a shared-disk architecture with the performance and scale-out benefits of a shared-nothing architecture, using a central repository accessiblefrom all compute nodes2.

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

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.

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

 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

Multi-cluster virtual warehouses in Snowflake are designed to manage user and query concurrency needs. They allow for the allocation of additional clusters of compute resources, either statically or dynamically, to handle increased loads and reduce or eliminate the queuing of concurrent queries2.

https://docs.snowflake.com/en/user-guide/warehouses-multicluster.html#:~:text=Multi%2Dcluster%20warehouses%20enable%20you,during%20peak%20and%20off%20hours.

 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

Serverless features in Snowflake are billed based on the time they are used, measured in minutes. The cost is calculated by multiplying the duration of the job by an automatic sizing determined by Snowflake, with a minimum billing increment of one minute. This means that even if a serverless feature is used for less than a minute, it will still be billed for the full minute.

Network policies in Snowflake can be set at the user level and at the account level2.

Operations that do not require compute resources are typically those that can leverage previously cached results. However, if no queries have been executed previously, all the given operations would require compute to execute. It’s important to note that certain operations like DDL statements and queries that hit the result cache do not consume compute credits2.

An explain plan is beneficial because it allows for the evaluation of how a query will be processed without the need to actually execute the query. This can help in understanding the query’s performance implications and potential bottlenecks without consuming resources that would be used if the query were run

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

The SQL commands that consume a stream and advance the stream offset are those that result in changes to the data, such as UPDATE and INSERT operations. Specifically, ‘UPDATE TABLE FROM STREAM’ and ‘INSERT INTO TABLE SELECT FROM STREAM’ will consume the stream and move the offset forward, reflecting the changes made to the data.

 In Snowflake’s data sharing model, any full Snowflake account can both provide and consume shared data. Additionally, the data consumer pays only for the compute resources used to query the shared data. No actual data is copied or transferred between accounts, and shared data does not take up any storage in a consumer account, so the consumer does not pay for data storage1.

References = Introductionto Secure Data Sharing | Snowflake Documentation

 By default, the Snowflake role required to create a share is ACCOUNTADMIN (D). This role has the necessary privileges to perform administrative tasks, including creating shares for data sharing purposes

 In the Snowflake Enterprise Edition, the default Time Travel retention is set to 1 day, the maximum Time Travel retention can be set up to 90 days, and the Fail-safe retention timeis 7 days3.

The graphical representation of warehouse utilization indicates periods of significant queuing, suggesting that the current single cluster cannot efficiently handle all incoming queries. Configuring the warehouse to a multi-cluster warehouse will distribute the load among multiple clusters, reducing queuing times and improving overall performance1.

References = Snowflake Documentation on Multi-cluster Warehouses1

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

 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

The LOGIN_HISTORY view in the ACCOUNT_USAGE schema provides information about login attempts, including both successful and failed logins. This view can be used to review the failed login attempts of a specific user for the past 30 days. References: [ COF-C03 ] SnowPro Core Certification Exam Study Guide

 Snowflake stored procedures can be created to execute with the privileges of the role that owns the procedure (owner’s rights) or with the privileges of the role that calls the procedure (caller’s rights). This allows for flexibility in managing security and access control within Snowflake1.

The minimum Snowflake edition required to use Dynamic Data Masking is the Enterprise edition. This feature is not available in the Standard edition2.

 For a temporary table, the maximum total Continuous Data Protection (CDP) charges incurred are forthe duration of the session in which the table was created, which does not exceed 24 hours2.

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

 Users are responsible for data storage costs in Snowflake until the data expires from the Fail-safe period. Fail-safe is the final stage in the data lifecycle, following Time Travel, and provides additional protection against accidental data loss. Once data exits the Fail-safe state, users are no longer billed for its storage

The Snowflake Search Optimization Service is designed to support improved performance for selective point lookup queries. These are queries that retrieve specific records from a database, often based on a unique identifier or a small set of criteria3.

 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 features associated with Continuous Data Protection (CDP) that require additional Snowflake-provided data storage are Time Travel and Fail-safe. Time Travel allows users to access historical data within a defined period, while Fail-safe provides an additional layer of data protection beyond the Time Travel period. References: [ COF-C03 ] SnowPro Core Certification Exam Study Guide

The Query Profile in Snowflake is used to identify performance issues with queries. Common issues that can be found using the Query Profile include identifying inefficient micro-partition pruning (D) and data spilling to a local or remote disk (E). Micro-partition pruning is related to the efficiency of query execution, and data spilling occurs when the memory is insufficient, causing the query to write data to disk, which can slow down the query performance1.

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

 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.

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

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

The Snowflake Enterprise edition includes database replication and failoverfor business continuity and disaster recovery, as well as extended time travel capabilities for longer data retention periods1.

 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.

The UNDROP command in Snowflake can be used to directly restore Views and Tables. These objects, when dropped, are moved to a ‘Recycle Bin’ where they can be restored within a time limit before they are permanently deleted. References: [ COF-C03 ] SnowPro Core Certification Exam Study Guide

 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.

The Snowflake cloud services layer is responsible for tasks such as authentication and access control, ensuring secure access to the platform, and metadata management, which involves organizingand maintaining information about the data stored in Snowflake56.

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.

A Stream object in Snowflake is used for change data capture (CDC), which records data manipulation language (DML) changes made to tables, including inserts, updates, and deletes3.

 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.

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.

 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

 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

In owner’s rights stored procedures, certain commands are restricted to maintain security and integrity. The SHOW and DESCRIBE commands are limited because they can reveal metadata and structure information that may not be intended for all roles. 

 Snowflake supports the storage of semi-structured data using the ARRAY and VARIANT data types. The ARRAY data type can directly contain VARIANT, and thus indirectly contain any other data type, including itself. The VARIANT data type can store a value of any other type, including OBJECT and ARRAY, andis often used to represent semi-structured data formats like JSON, Avro, ORC, Parquet, or XML34.

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.

To determine which user has accessed a database object containing sensitive information, a Snowflake administrator can query the ACCESS_HISTORY view in the ACCOUNT_USAGE schema, which provides information about access to database objects3.

 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

 The factors that impact storage costs in Snowflake include the account type (Capacity or On Demand) and the cloud region used by the account. These factors determine the rate at which storage is billed, with different regions potentially having different rates3.

Views can be used with Secure Data Sharing in Snowflake. Materialized views, external tables, and UDFs are not typically shared directly for security and performance reasons2.

 The TO_JSON function is used to convert a VARIANT value into a string containing the JSON representation of the value. This function is suitable for unloading data from a relational table to JSON format. References: [ COF-C03 ] SnowPro Core Certification Exam Study Guide

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

 Tags in Snowflake allow users to track sensitive data for compliance, discovery, protection, and resource usage. They enable the categorization and tracking of data, supporting compliance with privacy regulations678. References: [ COF-C03 ] SnowPro Core Certification Exam Study Guide

 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.

 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 LOOP type of loop in Snowflake Scripting does not have a built-in termination condition and requires a BREAK statement to stop executing4.

Snowflake’s Data Exchange feature provides a data hub for secure data collaboration. It allows providers to publish data that can be discovered and accessed by a selectedgroup of invited members, facilitating secure and controlled data sharing within a collaborative environment3. References: [ COF-C03 ] SnowPro Core Certification Exam Study Guide

The LATERAL modifier for the FLATTEN function allows joining information outside the object (such as other columns in the source table) with the flattened data, creating a lateral view that correlates with the preceding tables in the FROM clause2345. References: [ COF-C03 ] SnowPro Core Certification Exam Study Guide

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

 A masking policy in Snowflake consists of a single data type, with one or more conditions, and one or more masking functions. These components define how the data is masked based on the specified conditions3.

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

 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.

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 stores metadata for rows in micro-partitions, including the range of values for each column and the number of distinct values1.

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.

 To traverse semi-structured data in Snowflake, a user can insert a colon (:) between the VARIANT column name and any first-level element. This path syntax is used to retrieve elements in a VARIANT column4.