SnowPro-Core SnowPro Core Certification Exam Questions and Answers

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

 Snowflake supports semi-structured data types, which include OBJECT and VARIANT. These data types are capable of storing JSON-like data structures, allowing for flexibility in data representation.  OBJECT can directly contain VARIANT, and thus indirectly contain any other data type, including itself 1 .

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

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

In addition to performing all the standard steps to share data, the USAGE privilege must be granted on each database referenced by a secure view in order to be shared. When sharing a database or specific objects like secure views, the receiving account needs to have the USAGE privilege on the database and schema to access the shared data. This privilege enables the receiving account to access the database and its schemas but does not allow for any DML operations. It ' s a prerequisite for accessing any objects within the database.

For a secure view to be part of a share, not only does the view itself need to be shared, but the underlying database (and schema, if applicable) must also be accessible to the recipients. Granting USAGE privilege on the database ensures that the receiving account can access the database in a read-only mode to utilize the shared view.

References:

Snowflake Documentation on Shares: Creating and Managing Shares

 The command  list @mystage;  is used to view the list of files that have been uploaded to an internal stage in Snowflake. The  list  command displays the metadata for all files in the specified stage, which in this case is  mystage . This command is particularly useful for verifying that files have been successfully unloaded from a Snowflake table to the stage and for managing the files within the stage.

References:

Snowflake Documentation on Stages

SnowPro® Core Certification Study Guide

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

 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

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

References:

[COF-C02] SnowPro Core Certification Exam Study Guide

Key Concepts & Architecture | Snowflake Documentation 2

 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.

References:

Snowflake Documentation on Multi-Cluster Warehousing

SnowPro Core Certification best practices

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.

References:

Snowflake Documentation on Secure Data Sharing

SnowPro™ Core Certification Companion: Hands-on Preparation and Practice

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

In Snowflake, permissions for accessing database objects, including tables, are not granted directly to users but rather to roles. A role encapsulates a collection of privileges on various Snowflake objects. Users are then granted roles, and through those roles, they inherit the permissions necessary to read a table or perform other actions. This approach adheres to the principle of least privilege, allowing for granular control over database access and simplifying the management of user permissions.

 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

Snowflake supports two geospatial data types:  GEOGRAPHY  and  GEOMETRY . The  GEOGRAPHY  data type is used to store geospatial data that models the Earth as a perfect sphere, which is suitable for global geospatial data. This data type follows the WGS 84 standard and is used for storing points, lines, and polygons on the Earth’s surface. The  GEOMETRY  data type, on the other hand, represents features in a planar (Euclidean, Cartesian) coordinate system and is typically used for local spatial reference systems.  Since the question specifically asks about geospatial data, which commonly refers to Earth-related spatial data, the correct answer is  GEOGRAPHY 3 .  References:  [COF-C02] SnowPro Core Certification Exam Study Guide

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

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.

References:

Snowflake Documentation on Pipes

SnowPro® Core Certification Study Guide

https://docs.snowflake.com/en/user-guide/data-load-snowpipe-intro.html

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

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

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

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

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

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

 A network policy in Snowflake is used to restrict access to the Snowflake account from unauthorized or unknown sources.  It allows administrators to specify allowed IP address ranges, thus preventing access from any IP addresses not listed in the policy 1 .

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 Snowflake, shares are used to share data between Snowflake accounts. When creating a share, it is possible to grant access to the share to roles within the Snowflake account that is creating the share. The type of role that can be granted to a share is a Custom role. Custom roles are user-defined roles that account administrators can create to manage access control in a more granular way. Unlike predefined roles such as ACCOUN TADMIN or SYSADMIN, custom roles can be tailored with specific privileges to meet the security and access requirements of different groups within an organization.

Granting a custom role access to a share enables users associated with that role to access the shared data if the share is received by another Snowflake account. It is important to carefully manage the privileges granted to custom roles to ensure that data sharing aligns with organizational policies and data governance standards.

References:

Snowflake Documentation on Shares: Shares

Snowflake Documentation on Roles: Access Control

The purpose of an External Function in Snowflake is to call code that executes outside of the Snowflake environment.  This allows Snowflake to interact with external services and leverage functionalities that are not natively available within Snowflake, such as calling APIs or running custom code hosted on cloud services 3 .

https://docs.snowflake.com/en/sql-reference/external-functions.html

The ACCOUNTADMIN role is required for a user to create and manage accounts in Snowflake.  This role has the highest level of privileges and is responsible for managing all aspects of the Snowflake account, including the ability to create and manage other user accounts 1 .

https://docs.snowflake.com/en/user-guide/security-access-control-considerations.html

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

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

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

 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 do not need to sign up separately with Duo 1 .

Snowpipe data load history is retained for 64 days. This retention period allows users to review and audit the data load operations performed by Snowpipe over a significant period of time, which can be crucial for troubleshooting and ensuring data integrity.

References:

[COF-C02] SnowPro Core Certification Exam Study Guide

Snowflake Documentation on Snowpipe 1

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

QUSTION NO: 579

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

A. IO

B. Network

C. Pruning

D. Spilling

Answer: D

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

References:

Snowflake Documentation on Query Profile and Performance: This section explains the various components of the query profile, including the spilling metric, which indicates disk usage for operations where intermediate results exceed available memory.

In Snowflake, a Share is the object that contains all the information required to share a database with other Snowflake accounts. Shares are used to securely share data stored in Snowflake tables and views, enabling data providers to grant data consumers access to their datasets without duplicating data. When a database is shared, it can include one or more schemas, and each schema can contain tables, views, or both.

References:

Snowflake Documentation on Shares: Shares

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

 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.

References:

Snowflake Documentation on CREATE MATERIALIZED VIEW 1 .

Snowflake Documentation on Working with Materialized Views

https://docs.snowflake.com/en/sql-reference/sql/create-materialized-view.html#:~:text=Materialized%20views%20require%20Enterprise%20Edition,upgrading%2C%20please%20contact%20Snowflake%20Support .

 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

The Statistic output in the Query Profile Overview of Snowflake provides detailed insights into the performance of different parts of the query. Specifically, it highlights the " Most expensive nodes, " which are the operations or steps within the query execution that consume the most resources, such as CPU and memory. Identifying these nodes helps in pinpointing performance bottlenecks and optimizing query execution by focusing efforts on the most resource-intensive parts of the query.

References:

Snowflake Documentation on Query Profile Overview: It details the components of the profile overview, emphasizing how to interpret the statistics section to improve query performance by understanding which nodes are most resource-intensive.

QUSTION NO: 582

How do secure views compare to non-secure views in Snowflake?

A. Secure views execute slowly compared to non-secure views.

B. Non-secure views are preferred over secure views when sharing data.

C. Secure views are similar to materialized views in that they are the most performant.

D. There are no performance differences between secure and non-secure views.

Answer: D

Secure views and non-secure views in Snowflake are differentiated primarily by their handling of data access and security rather than performance characteristics. A secure view enforces row-level security and ensures that the view definition is hidden from the users. However, in terms of performance, secure views do not inherently execute slower or faster than non-secure views. The performance of both types of views depends more on other factors such as underlying table design, query complexity, and system workload rather than the security features embedded in the views themselves.

References:

Snowflake Documentation on Views: This section provides an overview of both secure and non-secure views, clarifying that the main difference lies in security features rather than performance, thus supporting the assertion that there are no inherent performance differences.

QUSTION NO: 583

When using SnowSQL, which configuration options are required when unloading data from a SQL query run on a local machine? {Select TWO).

A. echo

B. quiet

C. output_file

D. output_format

E. force_put_overwrite

Answer: C, D

When unloading data from SnowSQL (Snowflake ' s command-line client), to a file on a local machine, you need to specify certain configuration options to determine how and where the data should be outputted. The correct configuration options required are:

C. output_file : This configuration option specifies the file path where the output from the query should be stored. It is essential for directing the results of your SQL query into a local file, rather than just displaying it on the screen.

D. output_format : This option determines the format of the output file (e.g., CSV, JSON, etc.). It is crucial for ensuring that the data is unloaded in a structured format that meets the requirements of downstream processes or systems.

These options are specified in the SnowSQL configuration file or directly in the SnowSQL command line. The configuration file allows users to set defaults and customize their usage of SnowSQL, including output preferences for unloading data.

References:

Snowflake Documentation: SnowSQL (CLI Client) at Snowflake Documentation

Snowflake Documentation: Configuring SnowSQL at Snowflake Documentation

QUSTION NO: 584

How can a Snowflake user post-process the result of SHOW FILE FORMATS?

A. Use the RESULT_SCAN function.

B. Create a CURSOR for the command.

C. Put it in the FROM clause in brackets.

D. Assign the command to RESULTSET.

Answer: A

first run SHOW FILE FORMATS

then SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID(-1)))

https://docs.snowflake.com/en/sql-reference/functions/result_scan#usage-notes

QUSTION NO: 585

Which file function gives a user or application access to download unstructured data from a Snowflake stage?

A. BUILD_SCOPED_FILE_URL

B. BUILD_STAGE_FILE_URL

C. GET_PRESIGNED_URL

D. GET STAGE LOCATION

Answer: C

The function that provides access to download unstructured data from a Snowflake stage is:

C. GET_PRESIGNED_URL : This function generates a presigned URL for a single file within a stage. The generated URL can be used to directly access or download the file without needing to go through Snowflake. This is particularly useful for unstructured data such as images, videos, or large text files, where direct access via a URL is needed outside of the Snowflake environment.

Example usage:

SELECT GET_PRESIGNED_URL( ' stage_name ' , ' file_path ' );

This function simplifies the process of securely sharing or accessing files stored in Snowflake stages with external systems or users.

References:

Snowflake Documentation: GET_PRESIGNED_URL Function at Snowflake Documentation

QUSTION NO: 586

When should a multi-cluster virtual warehouse be used in Snowflake?

A. When queuing is delaying query execution on the warehouse

B. When there is significant disk spilling shown on the Query Profile

C. When dynamic vertical scaling is being used in the warehouse

D. When there are no concurrent queries running on the warehouse

Answer: A

A multi-cluster virtual warehouse in Snowflake is designed to handle high concurrency and workload demands by allowing multiple clusters of compute resources to operate simultaneously. The correct scenario to use a multi-cluster virtual warehouse is:

A. When queuing is delaying query execution on the warehouse : Multi-cluster warehouses are ideal when the demand for compute resources exceeds the capacity of a single cluster, leading to query queuing. By enabling additional clusters, you can distribute the workload across multiple compute clusters, thereby reducing queuing and improving query performance.

This is especially useful in scenarios with fluctuating workloads or where it ' s critical to maintain low response times for a large number of concurrent queries.

References:

Snowflake Documentation: Multi-Cluster Warehouses at Snowflake Documentation

QUSTION NO: 587

A JSON object is loaded into a column named data using a Snowflake variant datatype. The root node of the object is BIKE. The child attribute for this root node is BIKEID.

Which statement will allow the user to access BIKEID?

A. select data:BIKEID

B. select data.BIKE.BIKEID

C. select data:BIKE.BIKEID

D. select data:BIKE:BIKEID

Answer: C

In Snowflake, when accessing elements within a JSON object stored in a variant column, the correct syntax involves using a colon ( : ) to navigate the JSON structure. The BIKEID attribute, which is a child of the BIKE root node in the JSON object, is accessed using data:BIKE.BIKEID . This syntax correctly references the path through the JSON object, utilizing the colon for JSON field access and dot notation to traverse the hierarchy within the variant structure. References: Snowflake documentation on accessing semi-structured data, which outlines how to use the colon and dot notations for navigating JSON structures stored in variant columns.

QUSTION NO: 588

Which Snowflake tool is recommended for data batch processing?

A. SnowCD

B. SnowSQL

C. Snowsight

D. The Snowflake API

Answer: B

For data batch processing in Snowflake, the recommended tool is:

B. SnowSQL : SnowSQL is the command-line client for Snowflake. It allows for executing SQL queries, scripts, and managing database objects. It ' s particularly suitable for batch processing tasks due to its ability to run SQL scripts that can execute multiple commands or queries in sequence, making it ideal for automated or scheduled tasks that require bulk data operations.

SnowSQL provides a flexible and powerful way to interact with Snowflake, supporting operations such as loading and unloading data, executing complex queries, and managing Snowflake objects from the command line or through scripts.

References:

Snowflake Documentation: SnowSQL (CLI Client) at Snowflake Documentation

QUSTION NO: 589

How does the Snowflake search optimization service improve query performance?

A. It improves the performance of range searches.

B. It defines different clustering keys on the same source table.

C. It improves the performance of all queries running against a given table.

D. It improves the performance of equality searches.

Answer: D

The Snowflake Search Optimization Service is designed to enhance the performance of specific types of queries on large tables. The correct answer is:

D. It improves the performance of equality searches : The service optimizes the performance of queries that use equality search conditions (e.g., WHERE column = value). It creates and maintains a search index on the table ' s columns, which significantly speeds up the retrieval of rows based on those equality search conditions.

This optimization is particularly beneficial for large tables where traditional scans might be inefficient for equality searches. By using the Search Optimization Service, Snowflake can leverage the search indexes to quickly locate the rows that match the search criteria without scanning the entire table.

References:

Snowflake Documentation: Search Optimization Service at Snowflake Documentation

QUSTION NO: 590

What compute resource is used when loading data using Snowpipe?

A. Snowpipe uses virtual warehouses provided by the user.

B. Snowpipe uses an Apache Kafka server for its compute resources.

C. Snowpipe uses compute resources provided by Snowflake.

D. Snowpipe uses cloud platform compute resources provided by the user.

Answer: C

Snowpipe is Snowflake ' s continuous data ingestion service that allows for loading data as soon as it ' s available in a cloud storage stage. Snowpipe uses compute resources managed by Snowflake, separate from the virtual warehouses that users create for querying data. This means that Snowpipe operations do not consume the computational credits of user-created virtual warehouses, offering an efficient and cost-effective way to continuously load data into Snowflake.

References:

Snowflake Documentation: Understanding Snowpipe

QUSTION NO: 591

What is one of the characteristics of data shares?

A. Data shares support full DML operations.

B. Data shares work by copying data to consumer accounts.

C. Data shares utilize secure views for sharing view objects.

D. Data shares are cloud agnostic and can cross regions by default.

Answer: C

Data sharing in Snowflake allows for live, read-only access to data across different Snowflake accounts without the need to copy or transfer the data. One of the characteristics of data shares is the ability to use secure views. Secure views are used within data shares to restrict the access of shared data, ensuring that consumers can only see the data that the provider intends to share, thereby preserving privacy and security.

References:

Snowflake Documentation: Understanding Secure Views in Data Sharing

QUSTION NO: 592

Which DDL/DML operation is allowed on an inbound data share?

A. ALTER TA3LE

B. INSERT INTO

C. MERGE

D. SELECT

Answer: D

In Snowflake, an inbound data share refers to the data shared with an account by another account. The only DDL/DML operation allowed on an inbound data share is SELECT. This restriction ensures that the shared data remains read-only for the consuming account, maintaining the integrity and ownership of the data by the sharing account.

References:

Snowflake Documentation: Using Data Shares

QUSTION NO: 593

In Snowflake, the use of federated authentication enables which Single Sign-On (SSO) workflow activities? (Select TWO).

A. Authorizing users

B. Initiating user sessions

C. Logging into Snowflake

D. Logging out of Snowflake

E. Performing role authentication

Answer: B C

Federated authentication in Snowflake allows users to use their organizational credentials to log in to Snowflake, leveraging Single Sign-On (SSO). The key activities enabled by this setup include:

B. Initiating user sessions : Federated authentication streamlines the process of starting a user session in Snowflake by using the existing authentication mechanisms of an organization.

C. Logging into Snowflake : It simplifies the login process, allowing users to authenticate with their organization ' s identity provider instead of managing separate credentials for Snowflake.

References:

Snowflake Documentation: Configuring Federated Authentication

QUSTION NO: 594

A user wants to upload a file to an internal Snowflake stage using a put command.

Which tools and or connectors could be used to execute this command? (Select TWO).

A. SnowCD

B. SnowSQL

C. SQL API

D. Python connector

E. Snowsight worksheets

Answer: B, E

To upload a file to an internal Snowflake stage using a PUT command, you can use:

B. SnowSQL : SnowSQL, the command-line client for Snowflake, supports the PUT command, allowing users to upload files directly to Snowflake stages from their local file systems.

E. Snowsight worksheets : Snowsight, the web interface for Snowflake, provides a user-friendly environment for executing SQL commands, including the PUT command, through its interactive worksheets.

References:

Snowflake Documentation: Loading Data into Snowflake using SnowSQL

Snowflake Documentation: Using Snowsight

The function in Snowflake that returns the URL of a stage using the stage name as the input is C. GET_PRESIGNED_URL . This function generates a pre-signed URL for a specific file in a stage, enabling secure, temporary access to that file without requiring Snowflake credentials. While the function is primarily used for accessing files in external stages, such as Amazon S3 buckets, it is instrumental in scenarios requiring direct, secure file access for a limited time.

It ' s important to note that as of my last update, Snowflake ' s documentation does not specifically list a function named GET_PRESIGNED_URL for directly obtaining a stage ' s URL by its name. The description aligns closely with functionality available in cloud storage services (e.g., AWS S3 ' s presigned URLs) which can be used in conjunction with Snowflake stages for secure, temporary access to files. For direct interaction with stages and their files, Snowflake offers various functions and commands, but the exact match for generating a presigned URL through a simple function call may vary or require leveraging external cloud services APIs in addition to Snowflake ' s capabilities.

References:

Snowflake Documentation and cloud services (AWS, Azure, GCP) documentation on presigned URLs and stage interactions.

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

References:

Snowflake Documentation: PARSE_JSON

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

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

References:

Snowflake Documentation: Data Unloading Considerations

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

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

References:

Snowflake Documentation: Semi-structured Data Functions

When dealing with semi-structured data in Snowflake, and the specific types of operations to be performed on the data are not yet determined, Snowflake recommends using the VARIANT data type. The VARIANT type is highly flexible and capable of storing data in multiple formats, including JSON, AVRO, BSON, and more, within a single column. This flexibility allows users to perform various operations on the data, including querying and manipulation of nested data structures without predefined schemas.

References:

Snowflake Documentation: Semi-structured Data Types

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

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

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

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

References:

Snowflake Documentation: Virtual Warehouses

In the Snowflake Web Interface (UI), the Query History page displays the history of queries executed in Snowflake for up to 14 days. This allows users to review and analyze their query performance, troubleshoot issues, and understand their query patterns over a two-week period. The Query History page is a critical tool for monitoring and optimizing the use of Snowflake.

References:

Snowflake Documentation: Using the Web Interface

When unloading data, the Parquet file format is known for its efficiency in preserving the data values for floating-point number columns. Parquet is a columnar storage file format that offers high compression ratios and efficient data encoding schemes. It is especially effective for floating-point data, as it maintains high precision and supports efficient querying and analysis.

References:

Snowflake Documentation: Using the Parquet File Format for Unloading Data

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

References:

Snowflake Documentation: Network Policies

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

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

References:

Snowflake Documentation: TABLE_STORAGE_METRICS View

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

References:

Snowflake Documentation: Understanding the Query Profile

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

References:

Snowflake Documentation: Understanding the Query Profile

The command to list all the file formats for which a user has access privileges in Snowflake is SHOW 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

Scalar functions in Snowflake (and SQL in general) are designed to return a single value for each invocation. They operate on a single value and return a single result, making them suitable for a wide range of data transformations and calculations within queries.

References:

Snowflake Documentation: Functions

 Clustering keys in Snowflake are used to reorganize large tables based on one or more columns. This feature optimizes the arrangement of data within micro-partitions to improve query performance, especially for large tables where efficient data retrieval is crucial. References: [COF-C02] SnowPro Core Certification Exam Study Guide

https://docs.snowflake.com/en/user-guide/tables-clustering-keys.html

 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.

References:

[COF-C02] SnowPro Core Certification Exam Study Guide

Snowflake Documentation on Access Control Privileges 1

The auto-suspend and auto-resume settings in Snowflake apply to the entire virtual warehouse. These settings allow the warehouse to automatically suspend when it’s not in use, helping to save on compute costs. When queries or tasks are submitted to the warehouse, it can automatically resume operation. This functionality is designed to optimize resource usage and cost-efficiency.

References:

SnowPro Core Certification Exam Study Guide (as of 2021)

Snowflake documentation on virtual warehouses and their settings (as of 2021)

Worksheets in Snowsight can be shared directly with other Snowflake users within the same account.  This feature allows for collaboration and sharing of SQL queries or Python code, as well as other data manipulation tasks 1 .

 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

When a virtual warehouse in Snowflake is suspended, the cache is dropped and is no longer available upon restart . This means that all cached data, including results and temporary data, are cleared from memory. The purpose of this behavior is to conserve resources while the warehouse is not active. Upon restarting the warehouse, it will need to reload any data required for queries from storage, which may result in a slower initial performance until the cache is repopulated. This is a critical consideration for managing performance and cost in Snowflake.

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

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.

References: For a detailed explanation, please refer to the Snowflake documentation on querying semi-structured data.

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

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

 The COPY command is used in Snowflake to load data from files located in an external stage into a table.  This command allows for efficient and parallelized data loading from various file formats 1 .

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

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

References =  Snowflake Micro-partitions & Table Clustering

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

 In Snowflake, schema objects include Pipes and File formats.  Pipes are used for continuous data loading, and File formats specify the format of data files used in loading and unloading operations within Snowflake

 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 moving it to an individual Snowflake account, allowing for seamless integration with existing data

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

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

When publishing a Snowflake Data Marketplace listing into a remote region, it’s important to note that while the listing is replicated into all selected regions automatically, the data itself is not. Therefore, the data must be replicated separately.  Additionally, the user must have the ORGADMIN role in at least one account to manage the replication of accounts 1 .

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

 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  parse_json()  function in Snowflake interprets an input string as a JSON document and produces a VARIANT value containing the JSON document.  This function is specifically designed for parsing strings that contain valid JSON information 3 .

The  LATERAL FLATTEN  command in Snowflake produces a result set that includes several columns, among which  PATH  and  INDEX  are included PATH  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 array 2 .

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 that are present in the specified stage 2 .

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

References:

Snowflake Documentation: Data Sharing

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

References:

Snowflake Documentation: CSV File Format

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.

Column-level security is a feature that allows fine-grained control over access to specific columns within a table. This is particularly useful for managing sensitive data and ensuring that only authorized users can view or manipulate certain pieces of information. According to my last update, this feature was available in the Enterprise Edition or higher editions of Snowflake.

References: Based on my internal data as of 2021, column-level security is an advanced feature typically reserved for higher-tiered editions like the Enterprise Edition in data warehousing solutions such as Snowflake.

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

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

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

Characteristics of reader accounts in Snowflake include:

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

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

References:

Snowflake Documentation: Reader Accounts

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 ARRAY https://docs.snowflake.com/en/user-guide/tables-clustering-keys

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

References:

There is no direct reference to a VALIDATE table function with these specific parameters in Snowflake documentation. It seems like a theoretical example for understanding function arguments. Snowflake documentation on UDFs and system functions can provide guidance on how to create and use custom functions for similar purposes.

To extract the URL of a directory table on an external stage for further transformation in Snowflake, the GET_ABSOLUTE_PATH function can be used. This function returns the full path of a file or directory within a specified stage, enabling users to dynamically construct URLs for accessing or processing data stored in external stages.

References:

Snowflake Documentation: Working with Stages

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

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

References:  [COF-C02] SnowPro Core Certification Exam Study Guide

Multi-Factor Authentication (MFA) provides increased login security for users connecting to Snowflake. Snowflake’s MFA is powered by Duo Security service, which adds an additional layer of security during the login process.

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

 An account administrator in the Data Exchange can perform tasks such as adding and removing members and approving or denying listing approval requests.  These tasks are part of managing the Data Exchange and ensuring that only authorized listings and members are part of it 1 2 .

The parameter  DATA_RETENTION_TIME_IN_DAYS  can be set at the account level to define the minimum number of days Snowflake retains historical data for Time Travel 1 .

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

The  LOOP  type of loop in Snowflake Scripting does not have a built-in termination condition and requires a  BREAK  statement to stop executing 4 .

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

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

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

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

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

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

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

References =  Introduction to Secure Data Sharing | Snowflake Documentation

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

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

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

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

 Search optimization in Snowflake is designed to improve the performance of queries that are selective and involve point lookup operations using equality and IN predicates.  It is particularly beneficial for queries that access columns with a high number of distinct values 1 .

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

 In Snowflake’s architecture, the Cloud Services layer is responsible for generating the query execution plan. This layer handles all the coordination, optimization, and management tasks, including query parsing, optimization, and compilation into an execution plan that can be processed by the Compute layer.

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

 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 accessible from all compute nodes 2 .

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

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

Snowpipe via REST API can reference both named internal stages within Snowflake and external stages, such as Amazon S3, Google Cloud Storage, or Microsoft Azure 1 . This means that Snowpipe is not limited to only external stages as a source for data loading.

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

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

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

A pre-signed URL provides a way to access objects stored in a stage without requiring authentication to Snowflake. This URL contains all the necessary information for access control, including an expiration time, and it’s signed by the service ' s credentials. This mechanism allows users or applications to access specific files stored in a cloud storage location (stage) directly, bypassing the need to authenticate into Snowflake, ensuring secure and temporary access to the data. References: Snowflake Documentation on Stages and Data Loading

Snowsight, Snowflake’s user interface for executing and analyzing queries, supports various types of visualizations to help users understand their data better. Among the supported types, area charts and bar charts are two common options. Area charts are useful for representing quantities through the use of filled areas on the graph, often useful for showing volume changes over time. Bar charts, on the other hand, are versatile for comparing different groups or categories of data. Both chart types are integral to data analysis, enabling users to visualize trends, patterns, and differences in their data effectively. References: Snowflake Documentation on Snowsight Visualizations

Snowflake’s Time Travel feature allows users to access historical data within a specific period. This feature supports the restoration of various objects, including databases and schemas, to their previous states. Time Travel can be used for recovering dropped objects, undoing accidental changes, or analyzing data changes over time. However, it does not support user or role objects like Users and Roles, or compute resources like Virtual Warehouses. References: Snowflake Documentation on Time Travel

Snowflake ' s Streams feature is specifically designed for change data capture (CDC). A stream records insert, update, and delete operations performed on a table, and allows users to query these changes. This enables actions to be taken on the changed data, facilitating processes like incremental data loads and real-time analytics. Streams provide a powerful mechanism for applications to respond to data changes in Snowflake tables efficiently. References: Snowflake Documentation on Streams

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

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 capabilities 1 2 3 .

 To find information about query activity from 90 days ago, a Snowflake user should use the  account_usage.query_history_archive  view. This view is designed to provide access to historical query data beyond the default 14-day retention period found in the standard  query_history  view. It allows users to analyze and audit past query activities for up to 365 days after the date of execution, which includes the 90-day period mentioned.

References:

[COF-C02] SnowPro Core Certification Exam Study Guide

Snowflake Documentation on Account Usage Schema 1

 To show all privileges granted to a specific schema, the command  SHOW GRANTS ON SCHEMA < schema_name >  should be used 3 . In this case, it would be  SHOW GRANTS ON SCHEMA ANALYTICS_DW.MARKETING . References: [COF-C02] SnowPro Core Certification Exam Study Guide

The ACCOUNTADMIN role in Snowflake has the highest level of privileges, including the ability to manage accounts, users, roles, and all objects within the account. This role is specifically granted the permission to rename an account and specify whether the original URL can be used to access the renamed account. The ACCOUNTADMIN role encompasses broad administrative capabilities, ensuring that users assigned this role can perform critical account management tasks. References: Snowflake Documentation on Roles and Permissions

Valid sub-clauses to the OVER clause for a window function in SQL are:

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

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

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

References:

Snowflake Documentation: Window Functions

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

References:

Snowflake Documentation: Virtual Warehouses Billing

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

References:

Snowflake Documentation: Encryption and Key Management

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

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

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

References:

Snowflake Documentation: Understanding Role-Based Access Control

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

References:

Snowflake Documentation: User-Defined Functions

The CURRENT_TASK_GRAPHS table function is designed to provide information on Directed Acyclic Graphs (DAGs) that are currently scheduled or executing within Snowflake. This function offers insights into the structure and status of task chains, enabling users to monitor and troubleshoot task executions. DAGs in Snowflake represent sequences of tasks with dependencies, and understanding their current state is crucial for managing complex workflows. References: Snowflake Documentation on Task Management

The MIN_DATA_RETENTION_TIME_IN_DAYS parameter is set at the Account level in Snowflake. This parameter specifies the minimum number of days Snowflake retains the historical data for time travel, which allows users to access and query data as it existed at previous points in time.

Here ' s how to understand and adjust this parameter:

Purpose of MIN_DATA_RETENTION_TIME_IN_DAYS: This parameter is crucial for managing data lifecycle and compliance requirements within Snowflake. It determines the minimum time frame for which you can perform operations like restoring deleted objects or accessing historical versions of data.

Setting the Parameter: Only account administrators can set or modify this parameter. It is done at the account level, impacting all databases and schemas within the account. The setting can be adjusted based on the organization ' s data retention policy.

Adjusting the Parameter:

To view the current setting, use:

SHOW PARAMETERS LIKE ' MIN_DATA_RETENTION_TIME_IN_DAYS ' ;

To change the setting, an account administrator can execute:

ALTER ACCOUNT SET MIN_DATA_RETENTION_TIME_IN_DAYS = < number_of_days > ;

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.

Managed Access Schemas: These are a special type of schema designed for fine-grained access control in Snowflake.

Roles with Grant Authority:

ACCOUNTADMIN:  The top-level administrative role can grant object privileges on all objects within the account, including managed access schemas.

SECURITYADMIN:  Can grant and revoke privileges on objects within the account, including managed access schemas.

Important Note: The ORGADMIN role focuses on organization-level management, not object access control.

In Snowflake, the use of streams impacts how the data retention period for a table is handled, particularly in scenarios where the stream has not been consumed. The key point to understand is that Snowflake ' s streams are designed to capture data manipulation language (DML) changes such as INSERTS, UPDATES, and DELETES that occur on a source table. Streams maintain a record of these changes until they are consumed by a DML operation or a COPY command that references the stream.

When a stream is created on a table and remains unconsumed, Snowflake extends the data retention period of the table to ensure that the changes captured by the stream are preserved. This extension is specifically up to the point in time represented by the stream ' s offset, which effectively ensures that the data necessary for consuming the stream ' s contents is retained. This mechanism is in place to prevent data loss and ensure the integrity of the stream ' s data, facilitating accurate and reliable data processing and analysis based on the captured DML changes.

This behavior emphasizes the importance of managing streams and their consumption appropriately to balance between data retention needs and storage costs. It ' s also crucial to understand how this temporary extension of the data retention period impacts the overall management of data within Snowflake, including aspects related to data lifecycle, storage cost implications, and the planning of data consumption strategies.

References:

Snowflake Documentation on Streams: Using Streams

Snowflake Documentation on Data Retention: Understanding Data Retention

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

The ACCESS_HISTORY view in Snowflake is a powerful tool for enhancing data governance, especially concerning monitoring and auditing data access patterns for both read and write operations. The key ways in which ACCESS_HISTORY enhances overall data governance are:

B. Provides a unified picture of what data was accessed and when it was accessed: This view logs details about query executions, including the objects (tables, views) accessed and the timestamps of these accesses. It ' s instrumental in auditing and compliance scenarios, where understanding the access patterns to sensitive data is critical.

E. Determines whether a given row in a table can be accessed by the user by filtering the data based on a given policy: While this option is a bit of a misinterpretation of what ACCESS_HISTORY directly offers, it indirectly supports data governance by providing the information necessary to analyze access patterns. This analysis can then inform policy decisions, such as implementing Row-Level Security (RLS) to restrict access to specific rows based on user roles or attributes.

ACCESS_HISTORY does not automatically apply data masking or tag columns with personal information. However, the insights derived from analyzing ACCESS_HISTORY can be used to identify sensitive data and inform the application of masking policies or other security measures.

References:

Snowflake Documentation on ACCESS_HISTORY : Access History

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. This means that the performance of these queries can improve due to the increased resources.  Conversely, when the size of a warehouse is reduced, the compute resources are not removed until they are no longer being used by any current operations 1 .

References:

[COF-C02] SnowPro Core Certification Exam Study Guide

Snowflake Documentation on Virtual Warehouses 2

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 missing values and have no other special meaning 2 .

References:  [COF-C02] SnowPro Core Certification Exam Study Guide

Snowflake’s Time Travel feature is used for both querying historical data in tables and restoring and cloning historical data in databases, schemas, and tables 3 . 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

Data Robot is recognized as a machine learning and data science partner within the Snowflake Partner Ecosystem. It provides an enterprise AI platform that enables users to build and deploy accurate predictive models quickly.  As a partner, Data Robot integrates with Snowflake to enhance data science capabilities 2 .

References:

[COF-C02] SnowPro Core Certification Exam Study Guide

Snowflake Documentation on Machine Learning & Data Science Partners

https://docs.snowflake.com/en/user-guide/ecosystem-analytics.html

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:

GRANT SELECT ON TABLE my_table TO ROLE my_role;

 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 , and is often used to represent semi-structured data formats like JSON, Avro, ORC, Parquet, or XML 3 4 .

References:  [COF-C02] 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 clause 2 3 4 5 . References: [COF-C02] SnowPro Core Certification Exam Study Guide

The SECURITYADMIN role in Snowflake can manage any object grant globally, including modifying and revoking grants.  This role has the necessary privileges to oversee and control access to all securable objects within the Snowflake environment 4 .

Purpose: The BUILD_STAGE_FILE_URL function 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

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

References:

Snowflake Documentation: Search Optimization Service

The LOGIN_HISTORY view in SNOWFLAKE.ACCOUNT_USAGE shows from which IP address a user connected to Snowflake. This view is particularly useful for auditing and monitoring purposes, as it helps administrators track login attempts, successful logins, and the geographical location of users based on their IP addresses.

Reference to Snowflake documentation on LOGIN_HISTORY:

Monitoring Login Attempts

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

Snowflake supports semi-structured data types that can store complex data structures within a single column. The VARIANT data type can hold structured and semi-structured data formats such as JSON, Avro, ORC, Parquet, and XML. The ARRAY data type is also used for semi-structured data and can store an ordered list of elements. These data types enable users to work with semi-structured data directly in Snowflake without needing to flatten the data into a relational schema first. References: Snowflake Documentation on Semi-Structured Data Types

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

The GET_PRESIGNED_URL file 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 of GET_PRESIGNED_URL :

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

Objects in a data share become unavailable to a consumer account if the objects in the data share are deleted or if the permissions on these objects are altered without re-applying the grant permissions systematically. This is because the sharing mechanism in Snowflake relies on explicit grants of permissions on specific objects (like tables, views, or secure views) to the share. If these objects are deleted or if their permissions change without updating the share accordingly, consumers can lose access.

The DATA_RETENTION_TIME_IN_DAYS parameter does not directly affect the availability of shared objects, as it controls how long Snowflake retains historical data for time travel and does not impact data sharing permissions.

Running the GRANT IMPORTED PRIVILEGES command in the consumer account is not related to the availability of shared objects; this command is used to grant privileges on imported objects within the consumer ' s account and is not a routine maintenance command that would need to be run regularly.

Acquiring a data share through a private data exchange does not inherently make objects unavailable; issues would only arise if there were problems with the share configuration or if the shared objects were deleted or had their permissions altered without re-granting access to the share.

The PARTITION BY <</b> expression > parameter option in the COPY INTO <</b> location > command is used to split the output into multiple files based on the distinct values of the specified expression. This feature is particularly useful for organizing large datasets into smaller, more manageable files and can help with optimizing downstream processing or consumption of the data. For example, if you are unloading a large dataset of transactions and use PARTITION BY DATE(transactions.transaction_date) , Snowflake generates a separate output file for each unique transaction date, facilitating easier data management and access.

This approach to data unloading can significantly improve efficiency when dealing with large volumes of data by enabling parallel processing and simplifying data retrieval based on specific criteria or dimensions.

References:

Snowflake Documentation on Unloading Data: COPY INTO < location >