COF-C02 SnowPro Core Certification Exam Questions and Answers

SnowSQL, Snowflake's command-line client, can be used to diagnose and troubleshoot network connections. SnowSQL provides various commands and options to test connectivity, configure network settings, and troubleshoot issues related to network connections between the client and Snowflake.

References:

Snowflake Documentation: SnowSQL

https://docs.snowflake.com/en/sql-reference/data-sharing-usage

A directory table in Snowflake is an implicit object layered on a stage, whether internal or external. It allows users to query the contents of a stage as if it were a table, providing metadata about the files stored in the stage, such as filenames, file sizes, and last modified timestamps.

References:

Snowflake Documentation: Directory Tables

The ACCESS_HISTORY view in Snowflake provides detailed information about queries executed in the account, including metadata such as the time of execution, the user, and the SQL text of the queries. This view is available in the Snowflake Enterprise edition and higher editions. The Standard edition does not include this feature.

References:

Snowflake Documentation: Access History

Snowflake Editions: Snowflake Pricing

Access History in Snowflake is a feature designed to support auditing by tracking access to data within Snowflake, including when a user's query reads column data. It provides detailed information on queries executed, including the user who ran the query, the query text, and the objects (e.g., tables, views) accessed by the query. This feature is instrumental for auditing purposes, helping organizations to monitor and audit data access for security and compliance.

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

References:

Snowflake Documentation on Virtual Warehouses: Understanding and Managing Warehouse Credit Usage

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.

Based on Snowflake recommendations, when creating a hierarchy of custom roles, the top-most custom role should ideally be granted to theACCOUNTADMINrole. This recommendation stems from the best practices for implementing a least privilege access control model, ensuring that only the necessary permissions are granted at each level of the role hierarchy. The ACCOUNTADMIN role has the highest level of privileges in Snowflake, including the ability to manage all aspects of the Snowflake account. By assigning the top-most custom role to ACCOUNTADMIN, you ensure that theadministration of role hierarchies and the assignment of roles remain under the control of users with the highest level of oversight and responsibility within the Snowflake environment.

References:

Snowflake Documentation on Access Control: Managing Access Control

The search optimization service in Snowflake enhances query performance by maintaining a persistent data structure. This structure indexes the values of table columns across micro-partitions, allowing Snowflake to quickly identify which micro-partitions contain relevant data for a query. By efficiently narrowing down the search space, this service reduces the amount of data scanned during query execution, leading to faster response times and more efficient use of resources.References:Snowflake Documentation on Search Optimization Service

Snowflake's Cloud Services layer is responsible for managing various aspects of the platform that are not directly related to computing or storage. Specifically, it handles authorisation, ensuring that users have appropriate access rights to perform actions or access data. Additionally, it takes care of query parsing and optimization, interpreting SQL queries and optimizing their execution plans for better performance. This layer abstracts much of the platform's complexity, allowing users to focus on their data and queries without managing the underlying infrastructure.References:Snowflake Architecture Documentation

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

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

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

In Snowflake, a role is an access control entity that can be created as part of a hierarchy within an account. Roles are used to grant and manage privileges in a structured and scalable manner.

Understanding Roles:

Roles are logical entities that group privileges together.

They are used to control access to securable objects like tables, views, warehouses, and more.

Role Hierarchy:

Roles can be organized into a hierarchy, allowing for the inheritance of privileges.

A role higher in the hierarchy (parent role) can grant its privileges to a lower role (child role), simplifying privilege management.

Creating Roles:

Roles can be created using the CREATE ROLE command.

You can define parent-child relationships by granting one role to another.

Example Usage:

CREATE ROLE role1;

CREATE ROLE role2;

GRANT ROLE role1 TO role2;

In this example,role2inherits the privileges ofrole1.

Benefits:

Simplifies privilege management: Hierarchies allow for efficient privilege assignment and inheritance.

Enhances security: Roles provide a clear structure for managing access control, ensuring that privileges are granted appropriately.

References:

Snowflake Documentation: Access Control in Snowflake

Snowflake Documentation: Creating and Managing Roles

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

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

Implementation Steps:

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

CREATEMATERIALIZEDVIEWmy_materialized_viewASSELECT...FROMmy_tableWHERE...;

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

A Reader account in Snowflake can be used to share data with a consumer who does not have a Snowflake account. Reader accounts are a type of shared account provided by data providers to external data consumers, allowing them to access and query shared data using Snowflake's web interface without needing their own Snowflake account.

References:

Snowflake Documentation: Reader Accounts

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

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

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

Examples and Usage:

Uncorrelated Scalar Subquery:

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

Correlated EXISTS Subquery:

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

The propertymins_to_bypass_network_policyis set at the account level in Snowflake. This setting allows administrators to specify a time frame during which users can bypass network policies that have been set on their account. It is particularly useful in scenarios where temporary access needs to be granted from IPs not covered by the existing network policies. By adjusting this property at the account level, Snowflake administrators can manage and enforce network access controls efficiently across the entire account.

References:

Snowflake Documentation on Network Policies: Network Policies

Details around the privileges that have been granted for all objects in an account. TheACCESS_HISTORYview in Snowflake provides a comprehensive log of access control changes, including grants and revocations of privileges on all securable objects within the account. This information is crucial for auditing and monitoring the security posture of your Snowflake environment.

Here's how to understand and use theACCESS_HISTORYview:

Purpose of ACCESS_HISTORY View:It is designed to track changes in access controls, such as when a user or role is granted or revoked privileges on various Snowflake objects. This includes tables, schemas, databases, and more.

Querying ACCESS_HISTORY:To access this view, you can use the following SQL query pattern:

SELECT*FROMSNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORYWHEREEVENT_TYPE ='GRANT'OREVENT_TYPE ='REVOKE';

Interpreting the Results:The results from theACCESS_HISTORYview include the object type, the specific privilege granted or revoked, the grantee (who received or lost the privilege), and the timestamp of the event. This data is invaluable for audits and compliance checks.

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.

References:

Snowflake Documentation: Virtual Warehouse Sizes

In the Snowflake Data Exchange, the ability to get or request data from listings is generally controlled by specific roles and privileges:

A. Users with ACCOUNTADMIN role: This role typically has the highest level of access within a Snowflake account, including the ability to manage and access all features and functions. Users with this role can access data listings within the Data Exchange.

D. Users with import share privilege: This specific privilege is necessary for users who need to import shared data from the Data Exchange. This privilege allows them to request and access data listings explicitly shared with them.

The REMOVE command in Snowflake is used to delete files from either external cloud storage locations or internal stages. This command helps manage staged files by removing them when they are no longer needed, which is useful for maintaining organization and managing storage costs.

References:

Snowflake Documentation: REMOVE Command

In Snowflake streams, theMETADATASACTIONcolumn indicates the type of data manipulation operation that has occurred. The possible values includeINSERTandDELETE.

INSERT:Indicates that a new row has been inserted into the table.

DELETE:Indicates that a row has been deleted from the table.

References:

Snowflake Documentation: Change Data Capture (CDC) with Streams

Snowflake Documentation: Stream Data Capture

To see the definition of a secure view in Snowflake, the minimum privilege required is OWNERSHIP of the view. Ownership grants the ability to view the definition as well as to modify or drop the view. Secure views are designed to protect sensitive data, and thusthe definition of these views is restricted to users with sufficient privileges to ensure data security.

References:

Snowflake Documentation: Secure Views

In Snowflake, aTemporarytable is a type of table that is only visible to the user who creates it, can have the same name as permanent tables in the same schema, and is automatically dropped at the end of the session in which it was created. Temporary tables are designed for transient data processing needs, where data is needed for the duration of a specific task or session but not beyond. Since they are automatically cleaned up at the end of the session, they help manage storage usage efficiently and ensure that sensitive data is not inadvertently persisted.

References:

Snowflake Documentation on Temporary Tables: Temporary Tables

When a query containing a WHERE clause is running longer than expected, and the Query Profile shows that all micro-partitions are being scanned, the query can be optimized by adding a clustering key to the table.

Understanding Micro-Partitioning in Snowflake:

Snowflake automatically partitions tables into micro-partitions for efficient storage and query performance.

Each micro-partition contains metadata about the range of values it holds, which helps in pruning irrelevant partitions during query execution.

Role of Clustering Keys:

A clustering key defines how data in a table is organized within micro-partitions.

By specifying a clustering key, you can control the physical layout of data, ensuring that related rows are stored together.

This organization improves query performance by reducing the number of micro-partitions that need to be scanned.

Optimizing Queries with Clustering Keys:

Adding a clustering key based on columns frequently used in WHERE clauses helps Snowflake quickly locate and scan relevant micro-partitions.

This minimizes the amount of data scanned and reduces query execution time.

Example:

ALTER TABLE my_table CLUSTER BY (column1, column2);

This command adds a clustering key tomy_tableusingcolumn1andcolumn2.

Future queries that filter on these columns will benefit from improved performance.

Benefits:

Reduced query execution time: Fewer micro-partitions need to be scanned.

Improved resource utilization: More efficient data retrieval leads to lower compute costs.

References:

Snowflake Documentation: Clustering Keys

Snowflake Documentation: Query Profile

The function used to determine the kind of value stored in a VARIANT column in Snowflake is TYPEOF.

Understanding VARIANT Data Type:

VARIANT is a flexible data type in Snowflake that can store semi-structured data, such as JSON, Avro, and XML.

This data type can hold values of different types, including strings, numbers, objects, arrays, and more.

Using TYPEOF Function:

The TYPEOF function returns the data type of the value stored in a VARIANT column.

It helps in identifying the type of data, which is crucial for processing and transforming semi-structured data accurately.

Example Usage:

SELECT TYPEOF(variant_column)

FROM my_table;

This query retrieves the type of data stored invariant_columnfor each row inmy_table.

Possible return values include 'OBJECT', 'ARRAY', 'STRING', 'NUMBER', etc.

Benefits:

Simplifies data processing: Knowing the data type helps in applying appropriate transformations and validations.

Enhances query accuracy: Ensures that operations on VARIANT columns are performed correctly based on the data type.

References:

Snowflake Documentation: TYPEOF

Snowflake Documentation: VARIANT Data Type

The correct function to use with theCOPY INTO <location>statement to convert rows from a relational table into a single variant column and to unload rows into a JSON file isTO VARIANT. TheTO VARIANTfunction is used to explicitly convert a value of any supported data type into a VARIANT data type. This is particularly useful when needing to aggregate multiple columns or complex data structures into a single JSON-formatted string, which can then be unloaded into a file.

In the context of unloading data, theCOPY INTO <location>statement combined withTO VARIANTenables the conversion of structured data from Snowflake tables into a semi-structured VARIANT format, typically JSON, which can then be efficiently exported and stored. This approach is often utilized for data integration scenarios, backups, or when data needs to be shared in a format that is easily consumed by various applications or services that support JSON.

References:

Snowflake Documentation on Data Unloading: Unloading Data

Snowflake Documentation on VARIANT Data Type: Working with JSON

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

 The OBJECT_CONSTRUCT function is used in Snowflake to create a JSON object from relational data, which is useful when unloading semi-structured data. The SINGLE = TRUE parameter is used when unloading data to ensure that the data is exported as a single file, which can be up to 5 GB in size. References: [COF-C02] 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-C02] SnowPro Core Certification Exam Study Guide

 Snowpark is designed to bring the data programmability to Snowflake, enabling developers to write code in familiar languages like Scala, Java, and Python. It allows for the execution of these codes directly within Snowflake’s virtual warehouses, eliminating the need for a separate cluster. Additionally, Snowpark’s compatibility with Spark allows users to leverage their existing Spark code with minimal changes1.

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

Disabling auto-suspend for a virtual warehouse is recommended when there is a steady workload, which ensures that compute resources are always available. Additionally, it is advisable to disable auto-suspend when immediate availability of compute resources is critical, eliminating any startup delay

 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 uses a technique called pruning to limit the number of micro-partitions scanned by each query. Pruning effectively filters out unnecessary micro-partitions based on the query’s filter conditions, which can significantly improve query performance by reducing the amount of data scanned1.

 Directory tables in Snowflake are used to store metadata about data files in a stage. They are not separate database objects but are conceptually similar to external tables. Directory tables do not have grantable privileges of their own

 In a standard multi-cluster warehouse with auto-scaling, a cluster will shut down when, after 2-3 consecutive checks, the system determines that the load on the least-loaded cluster could be redistributed to other clusters. This ensures efficient resource utilization and cost management. References: [COF-C02] SnowPro Core Certification Exam Study Guide

 In Snowflake, the OPERATE privilege is required for a role to suspend or resume a task. This privilege allows the role to perform operational tasks such as starting and stopping tasks, which includes suspending and resuming them6

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 view is refreshed

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

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

 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.

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

 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

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

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

 In a Query Profile, the statistical information that indicates a query is too large to fit in memory includes bytes spilled to local cache and bytes spilled to local storage. These metrics suggest thatthe working data set of the query exceeded the memory available on the warehouse nodes, causing intermediate results to be written to disk

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

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

The user property ‘MINS TO BYPASS MFA’ in Snowflake allows temporary bypass of MFA for a user, which can be set by an account administrator without contacting Snowflake Support2.

The cloud services layer of Snowflake architecture handles various aspects including security functions, authentication of user sessions, and access control, ensuring that only authorized users can access the data and services23.

Snowflake supports storing unstructured data and provides native support for semi-structured file formats such as JSON, Avro, Parquet, ORC, and XML1. GeoJSON, being a type of JSON, and XML are among the formats that can be stored in Snowflake. References: [COF-C02] SnowPro Core Certification Exam Study Guide

 Snowflake allows data providers to share data with consumers across different cloud platforms and regions through database replication. The data provider must replicate the database to a secondary account in the target region or cloud platform within the same organization, and then create a share to the data consumer’s account. This process ensures that the data is available in the consumer’sregion and on their cloud platform, facilitating seamless data sharing. References: Sharing data securely across regions and cloud platforms | Snowflake Documentation

 When deciding on a clustering key for a table, Snowflake recommends using the columns that are most actively used in the select filters. This is because clustering by these columns can improve the performance of queries that filter on these values, leading to more efficient scans and better overall query performance2. References: [COF-C02] SnowPro Core Certification Exam Study Guide

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

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-C02] SnowPro Core Certification Exam Study Guide

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.

 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.

 Scaling up the virtual warehouse in Snowflake involves increasing the compute resources available for a single warehouse, which can improve the performance of large and complex queries by providing more CPU and memory resources. References: Based on general cloud data warehousing knowledge as of 2021.

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

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

 Snowflake supports several methods for authenticating users, including federated authentication, key-pair authentication, and OAuth. Federated authentication allows users to authenticate using their organization’s identity provider. Key-pair authentication uses a public-private key pair for secure login, and OAuth is an open standard for access delegation commonly used for token-based authentication. References: Authentication policies | SnowflakeDocumentation, Authenticating to the server | Snowflake Documentation, External API authentication and secrets | Snowflake Documentation.

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.

References:

Snowflake Documentation on Snowpipe

SnowPro® Core Certification Study Guide

 User-level network policies in Snowflake can be created by roles with the necessary privileges to manage security and account settings. The ACCOUNTADMIN role has the highest level of privileges across the account, including the ability to manage network policies. The SECURITYADMIN role is specifically responsible for managing security objects within Snowflake, which includes the creation and management of network policies.

References:

[COF-C02] SnowPro Core Certification Exam Study Guide

Snowflake Documentation on Network Policies1

Section 1.3 - SnowPro Core Certification Study Guide

 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.

References:

Snowflake Documentation on Security Features

SnowPro® Core Certification Exam Study Guide

 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.

References:

Snowflake Documentation on Time Travel

SnowPro® Core Certification Study Guide

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.

References: Preparing your data files | Snowflake Documentation

The COPY command in Snowflake allows for the reordering of columns as they are loaded into a table, and it also permits the omission of columns from the source file during the load process. This provides flexibility in handling the schema of the data being ingested. References: [COF-C02] SnowPro Core Certification Exam Study Guide

 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 size of the virtual warehouse for a task can be configured to handle concurrency automatically using a Multi-cluster warehouse (MCW). This is because tasks are designed to run their body on a schedule, and MCW allows for scaling compute resources to match the task’s execution needs without manual intervention. References: [COF-C02] SnowPro Core Certification Exam Study Guide

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.

References:

Snowflake Documentation on Semi-Structured Data Types

SnowPro® Core Certification Study Guide

 In Snowflake, a reader account is a type of account that is intended for consuming shared data rather than performing any data management or DDL operations. The CREATE SHARE command is used to share data from your account with another account, which is not a capability provided to reader accounts. Reader accounts are typically restricted from creating shares, as their primary purpose is to read shared data rather than to share it themselves.

References:

Snowflake Documentation on Reader Accounts

SnowPro® Core Certification Study Guide

 External stages can be altered and dropped in Snowflake. An external stage points to an external location, such as an S3 bucket, where data files are stored. Users can modify the stage’s definition or drop it entirely if it’s no longer needed. This is in contrast to table stages, which are tied to specific tables and cannot be altered or dropped independently.

References:

[COF-C02] SnowPro Core Certification Exam Study Guide

Snowflake Documentation on Stages1

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

References:

Snowflake Documentation on Clustering Keys & Clustered Tables

Snowflake Documentation on SYSTEM$CLUSTERING_INFORMATION

Stack Overflow discussion on cluster key selection in Snowflake

 Clustering keys in Snowflake work by sorting the designated columns over time. This process is done in the background and does not block data manipulation language (DML) operations, allowing for normal database operations to continue without interruption. The purpose of clustering keys is to organize the data within micro-partitions to optimize query performance1.

References:

[COF-C02] SnowPro Core Certification Exam Study Guide

Snowflake Documentation on Clustering1

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

References:

SnowPro Core Exam Prep — Answers to Snowflake’s LEVEL UP: Backup and Recovery

Snowflake SnowPro Core Certification Exam Questions Set 10

 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.

References:

[COF-C02] SnowPro Core Certification Exam Study Guide

Snowflake Documentation on Stages

 Snowflake applies egress charges in the case of database replication when data is transferred out of a Snowflake region to another region or cloud provider. This is because the data transfer incurs costs associated with moving data across different networks. Egress charges are not applied for data sharing within the same region, query result retrieval, or loading data into Snowflake, as these actions do not involve data transfer across regions.

References:

[COF-C02] SnowPro Core Certification Exam Study Guide

Snowflake Documentation on Data Replication and Egress Charges1

 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 require specific privileges to modify12.

References:

[COF-C02] SnowPro Core Certification Exam Study Guide

Snowflake Documentation on Masking Policies

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.

References:

[COF-C02] SnowPro Core Certification Exam Study Guide

Snowflake Documentation on Data Loading

 Materialized Views in Snowflake are designed to store the result of a query and can be refreshed to maintain up-to-date data. However, they have certain limitations, one of which is that they cannot be defined using a JOIN clause. This means that a Materialized View can only be created based on a single source table and cannot combine data from multiple tables using JOIN operations.

References:

Snowflake Documentation on Materialized Views

SnowPro® Core Certification Study Guide

Transient tables in Snowflake have a default Time Travel retention period of 1 day, which allows users to access historical data within the last 24 hours. However, transient tables do not have a Fail-safe period. Fail-safe is an additional layer of data protection that retains data beyond the Time Travel period for recovery purposes in case of extreme data loss. Since transient tables are designed for temporary or intermediate workloads with no requirement for long-term durability, they do not include a Fail-safe period by default1.

References:

Snowflake Documentation on Storage Costs for Time Travel and Fail-safe

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.

Semi-structured

JSON, Parquet

Snowflake supports unloading data in several semi-structured file formats, including Parquet and JSON. These formats allow for efficient storage and querying of semi-structured data, which can be loaded directly into Snowflake tables without requiring a predefined schema12.

https://docs.snowflake.com/en/user-guide/data-unload-prepare.html#:~:text=Supported%20File%20Formats,-The%20following%20file &text=Delimited%20(CSV%2C%20TSV%2C%20etc.)

 The COPY INTO command is used in Snowflake to unload data from a table into a file in a stage. This command allows for the export of data from Snowflake tables into flat files, which can then be used for further analysis, processing, or storage in external systems.

References:

Snowflake Documentation on Unloading Data

Snowflake SnowPro Core: Copy Into Command to Unload Rows to Files in Named Stage

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.

References:

[COF-C02] SnowPro Core Certification Exam Study Guide

Snowflake’s official documentation and partner listings

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

https://docs.snowflake.com/en/user-guide/admin-security-privatelink.html

 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.

References:

[COF-C02] SnowPro Core Certification Exam Study Guide

Snowflake Documentation on Query Profile1

Snowpro Core Certification Exam Flashcards2

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-C02] SnowPro Core Certification Exam Study Guide

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.

To generate a report listing all Snowflake logins within the last 90 days, the ACCOUNT_USAGE.LOGIN_HISTORY view should be used. This view provides information about login attempts, including successful and unsuccessful logins, and is suitable for security audits4.

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.

References:

[COF-C02] SnowPro Core Certification Exam Study Guide

Snowflake’s official documentation on billing and usage1

 To view warehouse usage over time, the Query history view and the WAREHOUSE_METERING__HISTORY View can be utilized. The Query history view allows users to monitor the performance of their queries and the load on their warehouses over a specified period1. The WAREHOUSE_METERING__HISTORY View provides detailed information about the workload on a warehouse within a specified date range, including average running and queued loads2. References: [COF-C02] SnowPro Core Certification Exam Study Guide

The default file format for the COPY command in Snowflake, when not specified, is CSV (Comma-Separated Values). This format is widely used for data exchange because it is simple, easy to read, and supported by many data analysis tools.

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

References:

Snowflake Documentation: Snowsight (UI for Snowflake)

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

References:

Snowflake Documentation: SHOW FILE FORMATS

The Virtual Private Snowflake (VPS) edition offers the highest level of security for organizations with the strictest security requirements. This edition provides a dedicated and isolated instance of Snowflake, including enhanced security features and compliance certifications to meet the needs of highly regulated industries or any organization requiring the utmost in data protection and privacy.

References:

Snowflake Documentation: Snowflake Editions

To obtain the most detailed information about individual table storage details in Snowflake, the TABLE STORAGE METRICS view is the recommended option. This view provides comprehensive metrics on storage usage, including data size, time travel size, fail-safe size, and other relevant storage metrics for each table. This level of detail is invaluable for monitoring, managing, and optimizing storage costs and performance.

References:

Snowflake Documentation: Information Schema

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

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

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

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

References:

Snowflake Documentation: Data Unloading

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

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

Key Points:

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

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

Snowflake Documentation (BUILD_STAGE_FILE_URL): https://docs.snowflake.com/en/sql-reference/functions/build_stage_file_url.html

Snowflake recommends enhancing the granularity and management of privileges by creating and utilizing custom roles. When additional privileges are needed beyond those provided by the system-defined roles for a virtual warehouse or any other resource, these privileges should be granted to a custom role. This approach allows for more precise control over access rights and the ability to tailor permissions to the specific needs of different user groups or applications within the organization, while also maintaining the integrity and security model of system-defined roles.

References:

Snowflake Documentation: Roles and Privileges

A clustering key can be defined when a table is created by appending a CLUSTER Where each clustering key consists of one or more table columns/expressions, which can be of any data type, except GEOGRAPHY, VARIANT, OBJECT, or ARRAYhttps://docs.snowflake.com/en/user-guide/tables-clustering-keys

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

References:

Snowflake Documentation: Data Loading and Unloading

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

References:

Snowflake Documentation: Snowflake Architecture

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

References:

Snowflake Documentation: Query Performance Optimization