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

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

Object Name Resolution: When unqualified object names (e.g., table name without schema) are used in a UDF, Snowflake follows a specific hierarchy to resolve them. Here ' s the order:

Current Schema:  Snowflake first checks if an object with the given name exists in the schema currently in use for the session.

PUBLIC Schema:  If the object isn ' t found in the current schema, Snowflake looks in the PUBLIC schema of the current database.

Note: The SEARCH_PATH parameter influences object resolution for queries , not within UDFs.

References:

Snowflake Documentation (Object Naming Resolution):   https://docs.snowflake.com/en/sql-reference/name-resolution.html

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

References:

Snowflake Documentation on JSON File Format

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

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

References:

[COF-C02] SnowPro Core Certification Exam Study Guide

Snowflake Documentation on Clustering 1

 In a  CREATE PIPE ... AS COPY ... FROM (....)  statement, the supported transformations include filtering data using an optional WHERE clause and omitting columns. The WHERE clause allows for the specification of conditions to filter the data that is being loaded, ensuring only relevant data is inserted into the table. Omitting columns enables the exclusion of certain columns from the data load, which can be useful when the incoming data contains more columns than are needed for the target table.

References:

[COF-C02] SnowPro Core Certification Exam Study Guide

Simple Transformations During a Load 1

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

References:

Snowflake Documentation on Stages

SnowPro® Core Certification Study Guide

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

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

 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

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

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

Option B is correct: A single executable statement can call only one stored procedure due to the procedural and potentially transactional nature of stored procedures. In contrast, a single SQL statement can call multiple UDFs because UDFs are designed to operate more like functions in traditional programming, where they return a value and can be embedded within SQL queries. References: Snowflake documentation comparing the operational differences between stored procedures and UDFs.

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

References:

Snowflake Documentation: Using Pre-signed URLs

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