Free Practice Questions for the Snowflake SnowPro Core Certification COF-C03 Exam (2026 Updated)

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

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

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

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

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

 To ensure that additional multi-clusters are resumed with no delay, a virtual warehouse should be configured to a size larger than generally required. This configuration allows for immediate availability of additional resources when needed, without waiting for new clusters to start up

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

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

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

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

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

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

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

The SQL command Select * from table(validate(t1, job_id = > ' _last ' )); is used to return errors from the last executed COPY command into table t1 in the current session. It checksthe results of the most recent data load operation and provides details on any errors that occurred during that process1.

 In the Snowflake Data Marketplace, the types of data listings available include ‘Vendor’, which refers to the providers of data, and ‘Personalized’, which indicates customized data offerings tailored to specific consumer needs45.

To execute a series of SQL statements using a task, a user would use a stored procedure that contains multiple SQL statements and invoke this stored procedure from the task. References: Snowflake Documentation2.

 The pre-signed URL type allows users or applications to download or access files directly from a Snowflake stage without authentication. This URL type is open and can be used without needing to authenticate into Snowflake or pass an authorization token.

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

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

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

The Parquet file format is known for preserving the precision of floating-point numberswhen data is unloaded, preventing truncation of the values3.

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

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

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

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

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

 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 isparticularly beneficial for queries that access columns with a high number of distinct values1.

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

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

Snowflake supports the VARIANT and ARRAY data types for semi-structured data. VARIANT can store values of any other type, including OBJECT and ARRAY, making it suitable for semi-structured data formats like JSON. ARRAY is used to store an ordered list of elements

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

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

 Clustering in Snowflake refers to the organization of data within micro-partitions, which are contiguous units of storage within Snowflake tables. Clustering keys can be defined to co-locate similar rows in the same micro-partitions, improving scan efficiency and query performance12.

A scoped URL expires when the persisted query result period ends, which is typically after the results cache expires. This is currently set to 24 hours

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

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

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

 Snowflake’s secure data sharing feature allows sharing of certain database objects with other Snowflake accounts. Amongthe options provided, external tables and secure UDFs can be shared

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

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

Query parsing and compilation in Snowflake occur within the cloud services layer. This layer is responsible for various management tasks, including query compilation and optimization

The semi-structured data function that interprets an input string as a JSON document and produces a VARIANT value is PARSE_JSON. This function is used to parse a JSON formatted string and return it as a VARIANT data type, which can then beused for further processing within Snowflake3.

To address query concurrency issues, a user can add additional clusters to the virtual warehouse. This allows for the distribution of queries across multiple clusters, reducing the load on any single cluster and improving overallquery performance2.

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

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

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

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

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

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

Snowflake integrates Duo Security to support Multi-Factor Authentication (MFA). This feature provides increased login security for users connecting to Snowflake, and it is managed completely by Snowflake without the need for users to sign up separately with Duo4.

In Snowflake, when queries begin to queue in a multi-cluster virtual warehouse, an additional compute cluster starts immediately if the warehouse is set to auto-scale mode. This mode allowsSnowflake to automatically add or resume additional clusters as soon as the workload increases, and similarly, shut down or pause the additional clusters when the load decreases

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

Snowflake recommends using columns with very high cardinality and those that are most actively used in selective filters as clustering keys. High cardinality columns have a wide range of unique values, which helps in evenly distributing the data across micro-partitions. Columns used in selective filters help in pruning the number of micro-partitions to scan, thus improving query performance. References: Based on general database optimization principles.

The Cloud Services layer in Snowflake is responsible for various services, including security (like authentication and authorization) and metadata management (like query parsing and optimization). References: Based on general cloud architecture knowledge as of 2021.

One of the security features of Snowflake includes the periodic rekeying of encrypted data, which is available with the Snowflake Enterprise edition and higher2. This ensures that the encryption keys are rotated regularly to maintain a high level of security. References: [COF-C03] SnowPro Core Certification Exam Study Guide

Clones in Snowflake have the advantage of being created almost instantly and saving space by not duplicating storage. This is due to Snowflake’s zero-copy cloning feature, which allows for the creation of object clones without the additional storage costs typically associated with data duplication23. Clones are independent of the original table and do not stay in sync with it, nor do they inherently have better query performance. However, they do inherit the time travel history from the original table at the time of cloning

The details section of a Query Profile in Snowflake provides users with various statistics and information about the execution of a query. One of the key pieces of information that can be determined from this section is the total duration of the query, which helps in understanding the performance and identifying potential bottlenecks. References: [COF-C03] SnowPro Core Certification Exam Study Guide

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

References = Snowflake Documentation on Multi-cluster Warehouses1

 The stream type that can be used for tracking the records in external tables is ‘External’. This type of stream is specifically designed to track changes in external tables

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

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

The first user assigned to a new Snowflake account, typically with the ACCOUNTADMIN role, should create at least one additional user with the USERADMIN administrative privilege. This role is responsible for creating and managing users and roles within the Snowflake account. References: Access control considerations | Snowflake Documentation

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

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

 To grant a role the privilege to select data from all current and future tables in a schema, two separate commands are needed. The first command grants the SELECT privilege on all existing tables within the schema, and the second command grants the SELECT privilege on all tables that will be created in the future within the same schema. 

Snowflake uses a technique called pruning to limit the number of micro-partitions scanned by each query. Pruning effectively filters out unnecessary micro-partitions based on the query’s filter conditions, which can significantly improve query performance by reducing theamount of data scanned1.

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

 The maximum Time Travel retention period for a transient table in Snowflake is 1 day. This is the default and maximumduration for which Snowflake maintains the historical data for transient tables, allowing users to query data as it appeared at any point within the past 24 hours2.

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

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

The correct answers are A. The UDF definition code and D. List of data imports .

A secure UDF is designed to protect sensitive implementation details. Secure UDFs help prevent unauthorized users from viewing the function body and other details that could expose business logic or sensitive references.

Why A is correct:

The definition code of a secure UDF is hidden from unauthorized users. This protects the logic used inside the function.

Why D is correct:

The list of imported files or data imports associated with the secure UDF is also protected from unauthorized users because it may reveal sensitive implementation details.

Why the other options are incorrect:

B. The return type is generally part of the function signature and must be visible so users can understand how to call and use the function.

C. Parameter types are also part of the function signature and are visible to users who need to call the UDF.

E. The handler language may be visible as part of function metadata and is not the best answer for protected secure UDF information.

Official Snowflake documentation reference:

Snowflake documentation explains that secure UDFs hide internal details, including the function definition and sensitive implementation information, from unauthorized users.

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

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

The correct answer is B. COPY .

Snowpipe loads data using a pipe object. The pipe definition contains a COPY INTO < table > statement that tells Snowflake how to load staged files into a target table.

Why B is correct:

Snowpipe automates loading staged files, but the actual load operation is still based on the COPY INTO < table > command.

Example:

CREATE PIPE my_pipe

AS

COPY INTO my_table

FROM @my_stage

FILE_FORMAT = (TYPE = CSV);

Why the other options are incorrect:

A. INSERT adds rows manually or through query results, but Snowpipe does not use INSERT as its load command.

C. UPDATE modifies existing rows and is not used by Snowpipe for loading files.

D. MERGE performs insert/update/delete logic based on matching conditions, but Snowpipe uses COPY INTO < table > for file ingestion.

Official Snowflake documentation reference:

Snowflake documentation explains that a pipe defines a COPY INTO < table > statement used by Snowpipe to load data from staged files.

To use the overwrite option on insert in Snowflake, theDELETEprivilege must be granted to the role. This is because overwriting data during an insert operation implicitly involves deleting the existing data before inserting the new data.

Understanding the Overwrite Option:The overwrite option (INSERT OVERWRITE) allows you to replace existing data in a table with new data. This operation is particularly useful for batch-loading scenarios where the entire dataset needs to be refreshed.

Why DELETE Privilege is Required:Since the overwrite operation involves removing existing rows in the table, the executing role must have theDELETEprivilege to carry out both the deletion of old data and the insertion of new data.

Granting DELETE Privilege:

To grant theDELETEprivilege to a role, an account administrator can execute the following SQL command:

sqlCopy code

GRANTDELETEONTABLEmy_tableTOROLE my_role;

TheTO_JSONfunction in Snowflake is used to convert a relational table or individual rows into JSON format. This function is helpful for exporting data in JSON format.

Using TO_JSON Function:

SELECT TO_JSON(OBJECT_CONSTRUCT(*))

FROM my_table;

Exporting Data:TheTO_JSONfunction converts the table rows into JSON format, which can then be exported to a file.

To download a 5 GB table into a single file most efficiently in Snowflake, you should set the SINGLE parameter to TRUE. This parameter ensures that the COPY INTO command outputs the result into a single file, regardless of the file size. This approach is more efficient than relying on the default MAX_FILE_SIZE setting, which would split the output into multiple files.

Snowflake Documentation: COPY INTO < location >

 Transient tables in Snowflake have a minimum Fail-safe retention time period of 0 days. This means that once the Time Travel retention period ends, there is no additional Fail-safe period for transient tables

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

 In Snowflake, a task allows a user to execute a single SQL statement/command using a predefined schedule (B). Tasks are used to automate the execution of SQL statements at scheduled intervals.

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

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

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.

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

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

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

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

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

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

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

 The billable compute time includes the time the warehouse is running queries plus the auto-suspend time after the last query if the warehouse is not manually suspended. In this scenario, the warehouse runs for 3 minutes, suspends after 10 minutes of inactivity, resumes for a 10-second query, and then is manually suspended. The total billable time is the sum of the initial 3 minutes, the 10 minutes of auto-suspend time, and the brief period for the 10-second query, which is rounded up to the next full minute due to Snowflake’s billing practices. References: [COF-C03] SnowPro Core Certification Exam Study Guide

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

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

Snowflake virtual warehouses support a configuration file and command line options in SnowSQL to specify a default warehouse, which is characteristic C. Additionally, the size of a virtual warehouse can be changed at any time, which is characteristic E. Thesefeatures provide flexibility and ease of use in managing compute resources2.

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

Snowflake supports the use of external stages with Amazon Web Services (AWS), Microsoft Azure Cloud, and Google Cloud Platform (GCP). These platforms allow users to stage data externally and integrate with Snowflake for data loading operations

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

 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.

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

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

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

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

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

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

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

The Snowflake Cloud Services layer performs a variety of tasks, including the management of metadata and the parsing and optimization of queries. This layer is responsible for coordinating activities across Snowflake, including user session management, security, and query compilation3.

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

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

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

 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.

Snowflake’s Continuous Data Protection (CDP) encompasses a set of features that help protect data stored in Snowflake against human error, malicious acts, and software failure. Time Travel allows users to access historical data (i.e., data that has been changed or deleted) for a defined period, enabling querying and restoring of data. Fail-safe is an additional layer of data protection that provides a recovery option in the event of significant data loss or corruption, which can only be performed by Snowflake.

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

The maximum size limit for a record of a VARIANT data type in Snowflake is 16MB. This allows for storing semi-structured data types like JSON, Avro, ORC, Parquet, or XML within a single VARIANT column. References: Based on general database knowledge as of 2021.

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

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

A materialized view is beneficial when the query consumes many compute resources every time it runs (B), and when the results of the query do not change often and are used frequently (E). This is because materialized views store pre-computed data, which can speed up query performance for workloads that are run frequently or are complex

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

The correct answer is D. ACCOUNT_USAGE.TABLE_STORAGE_METRICS .

ACCOUNT_USAGE.TABLE_STORAGE_METRICS provides detailed storage metrics for tables, including active bytes, Time Travel bytes, Fail-safe bytes, and retained-for-clone bytes. This view is more detailed for table-level storage than SHOW TABLES or DESC TABLE.

Why D is correct:

TABLE_STORAGE_METRICS is designed specifically to report table-level storage usage.

Why the other options are incorrect:

A. SHOW TABLES provides table metadata, but not the most detailed storage breakdown.

B. DESC TABLE describes the table structure, such as columns and data types, not detailed storage consumption.

C. ACCOUNT_USAGE.STORAGE_USAGE provides account-level storage usage, not the most detailed table-level storage metrics.

Official Snowflake documentation reference:

Snowflake documentation describes ACCOUNT_USAGE.TABLE_STORAGE_METRICS as a view that provides storage information for tables, including active, Time Travel, Fail-safe, and retained-for-clone storage.

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

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

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

 The FLATTEN table function in Snowflake is used to convert semi-structured data, such as JSON or XML, into a relational format. It expands nested arrays or objects into multiple rows, making the data suitable for relational querying3.

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

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

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

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

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

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

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

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

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

Implementing Clustering Keys:

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

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

Or for an existing table:

ALTERTABLEmy_table CLUSTERBY(column1, column2);

When a Snowflake user wants to share a newly created object in a database with consumers, the correct action to take is to use theGRANTprivilege ... TO SHARE command to grant the necessary privileges for the object to be shared. This approach allows the object owner or a user with the appropriate privileges to share database objects such as tables, secure views, and streams with other Snowflake accounts by granting access to a named share.

TheGRANTstatement specifies which privileges are granted on the object to the share. The object remains in its original location; sharing does not duplicate or move the object. Instead, it allows the specified share to access the object according to the granted privileges.

For example, to share a table, the command would be:

GRANTSELECTONTABLEnew_tableTOSHARE consumer_share;

This command grants the SELECT privilege on a table namednew_tableto a share namedconsumer_share, enabling the consumers of the share to query the table.

Automatic sharing, dropping and re-adding the object, or recreating the object with a different name are not required or recommended practices for sharing objects in Snowflake. The use of theGRANTstatement to a share is the direct and intended method for this purpose.

Masking policies in Snowflake are created and applied at the column level. These policies are used to obfuscate sensitive data by masking the values in a specific column. Masking policies can enforce data privacy and protection measures by dynamically masking the data based on the role of the user querying the data.

Snowflake Documentation: Dynamic Data Masking

In Snowflake, if a table is accidentally dropped but still within the data retention period (also known as " Time Travel " ), the simplest and most efficient recovery method is the UNDROP command. This command restores the deleted table to its previous state with minimal operational effort. Since Snowflake retains dropped table data for a specific retention period (up to 90 days for the Enterprise edition), UNDROP can quickly recover the table without the need for complex cloning or data reloading processes, making it ideal for accidental deletions.

To monitor updates, such as SCIM API requests sent to Snowflake by the identity provider, an administrator can use theREST EVENT HISTORYfeature. This feature allows administrators to query historical data about REST API calls made to Snowflake, including those related to user and role management through SCIM (System for Cross-domain Identity Management).

TheREST EVENT HISTORYtable function returns information about REST API calls made over a specified period. It is particularly useful for auditing and monitoring purposes, especially when integrating Snowflake with third-party identity providers that use SCIM for automated user provisioning and deprovisioning.

An example query to check for SCIM API requests might look like this:

SELECT*FROMTABLE(information_schema.rest_event_history(date_range_start= > dateadd( ' hours ' ,-1,current_timestamp())))WHERErequest_type = ' SCIM ' ;

This query returns details on SCIM API requests made in the last hour, including the request type, the identity provider ' s details, and the outcome of each request.

The correct answer is C. Stored procedures .

In Snowflake, the CALL command is used to execute a stored procedure. Stored procedures can contain procedural logic and can be written in supported languages such as JavaScript, SQL, Python, Java, or Scala, depending on the type of procedure.

Why C is correct:

Stored procedures are invoked using the CALL statement.

Example:

CALL my_procedure();

This runs the stored procedure named my_procedure.

Why the other options are incorrect:

A. System functions are usually invoked in SQL expressions using SELECT or within statements, not with CALL.

B. Tasks are resumed, suspended, or managed using task-related commands, and they run on schedules or task graphs. They are not directly run with CALL.

D. External functions are called like regular SQL functions inside SQL statements, not with the CALL statement.

Official Snowflake documentation reference:

Snowflake documentation describes the CALL command as the command used to execute a stored procedure.

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

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

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.

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

Snowflake automatically rotates encryption keys when they are more than 30 days old. Active keys are retired, and new keys are created. This process is part of Snowflake’s comprehensive security measures to ensure data protection and is managed entirely by the Snowflake service without requiring user intervention.

 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.

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

 Snowflake supports querying semi-structured data using specific data types that are capable of handling the flexibility and structure of such data. The data types supported for this purpose are:

A. VARIANT: This is a universal data type that can store values of any other type, including structured and semi-structured types. It is particularly useful for handling JSON, Avro, ORC, Parquet, and XML data formats1.

B. ARRAY: An array is a list of elements that can be of any data type, including VARIANT, and is used to handle semi-structured data that is naturally represented as a list1.

These data types are part of Snowflake’s built-in support for semi-structured data, allowing for the storage, querying, and analysis of data that does not fit into the traditional row-column format.

Snowflake supports several official connectors and drivers, including:

Go Snowflake Driver

Snowflake Connector for Python

Perl, COBOL, and MongoDB drivers are not supported by Snowflake.

The correct answer is C. Create a stream and schedule a task every 20 minutes.

A stream is used to capture change data capture, or CDC, information for a table. It records inserts, updates, and deletes made to the source table. A task is used to automate SQL execution on a schedule. Together, streams and tasks are commonly used to automate incremental data processing in Snowflake.

Why C is correct:

The requirement has two parts:

Capture only changed rows.

Run the transformation automatically every 20 minutes.

A stream satisfies the first requirement because it tracks changed rows. A scheduled task satisfies the second requirement because it can run SQL logic at a defined interval, such as every 20 minutes.

Example concept:

CREATE STREAM my_stream

ON TABLE source_table;

CREATE TASK my_task

WAREHOUSE = my_warehouse

SCHEDULE = ' 20 MINUTE '

AS

INSERT INTO target_table

SELECT ...

FROM my_stream;

Why the other options are incorrect:

A. This is partially correct, but it does not clearly satisfy the requirement to run every 20 minutes. A task can include a WHEN SYSTEM$STREAM_HAS_DATA(...) condition, but the question specifically asks for processing every 20 minutes.

B. A task alone can run every 20 minutes, but it does not capture only changed rows.

D. Dynamic tables are used for declarative automated data transformation, but the question specifically asks to capture only changed rows from a frequently updated table, which is the purpose of a stream.

Official Snowflake documentation reference:

Snowflake documentation describes streams as objects that record data manipulation language, or DML, changes made to tables. Snowflake tasks execute SQL statements on a schedule or as part of a task graph.

The correct answer is A. JSON .

When unloading floating-point or high-precision numeric values, users must consider whether the target file format can preserve the values as needed. JSON stores values in a textual semi-structured representation and can preserve numeric values without forcing them into a fixed Parquet numeric precision representation.

Why A is correct:

For values that exceed a precision/scale such as (15,9) and must be preserved during unloading, JSON is the safest option among the listed choices because it can represent the values textually in the output.

Why the other options are incorrect:

B. PARQUET has limitations for certain numeric precision/scale representations during unload. Snowflake documentation notes precision-related considerations for Parquet unloads.

C. AVRO is a semi-structured file format, but it is not the best documented answer for preserving float values beyond this precision scenario.

D. ORC is not the best answer for this Snowflake unload precision scenario.

Official Snowflake documentation reference:

Snowflake documentation for unloading data explains that file format choice can affect how numeric values are represented and preserved. JSON is commonly used when preserving semi-structured or text-based representations is required.

Direct Shares allow secure data sharing between Snowflake accounts in the same region . No data is copied; Snowflake provides access to the original data using metadata and access control.

Reader accounts are created by data providers for consumers who do not have their own Snowflake accounts. This allows providers to share data without requiring the consumer to create or pay for their own Snowflake account.

While a consumer can access multiple shares via a single reader account , administrative functions (like resource monitors) are managed by the provider.

The correct answers are C. Data is not transferred between accounts and D. Consumer account will incur compute costs .

Important correction:

The provided answer A, B is not correct.

Secure Data Sharing allows data providers to share selected database objects with consumer accounts without copying or transferring the underlying data. Consumers query the shared data using their own virtual warehouses, so the consumer generally pays for the compute used to query the share.

Why C is correct:

With Secure Data Sharing, the provider’s data remains in the provider account. Snowflake shares metadata and access, not a physical copy of the data.

Why D is correct:

Consumers use their own compute resources to query shared data. Therefore, the consumer account incurs compute costs when querying the share.

Why the other options are incorrect:

A. Regular, non-secure views generally cannot be shared in the standard secure sharing model. Views shared with consumers should be secure views, unless specific account-level settings allow non-secure objects. The general SnowPro Core concept is that secure views are used to share view logic safely.

B. Sensitive data is not automatically masked simply because data is shared. Masking policies or secure views must be designed and applied intentionally.

E. Reader accounts can query shared data but cannot edit the provider’s source data.

Official Snowflake documentation reference:

Snowflake documentation explains that Secure Data Sharing does not copy or move data between accounts. Consumers use their own warehouses to query shared data, while providers retain control of the shared objects.

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

The correct answer is A. VARCHAR .

A clustering key can be defined on supported table columns or expressions to help Snowflake organize micro-partitions and improve pruning for certain query patterns. Standard scalar data types such as VARCHAR are supported.

Why A is correct:

VARCHAR is a standard scalar data type and can be used in a clustering key.

Example:

CREATE TABLE customer_orders (

customer_id VARCHAR,

order_date DATE,

amount NUMBER

)

CLUSTER BY (customer_id);

Why the other options are incorrect:

B. VARIANT stores semi-structured data. Clustering directly on raw semi-structured columns is not the best answer here; clustering keys should use supported scalar columns or expressions.

C. OBJECT is a semi-structured data type and is not the correct choice for a clustering key column in this question.

D. GEOGRAPHY is not the correct supported clustering key data type in this context.

Official Snowflake documentation reference:

Snowflake documentation explains that clustering keys can be defined using table columns or expressions, and standard scalar columns such as string, number, and date/time columns are commonly used for clustering.

The settings shown in the image indicate that the data retention time in days is configured at two different levels: the account level and the object level. At the account level, theMIN_DATA_RETENTION_TIME_IN_DAYSis set to 5 days, and at the object level, theDATA_RETENTION_TIME_IN_DAYSis set to 2 days. Since the object level setting has a lower value, it takes precedence over the account level setting for the specific object. Therefore, the data will be retained for 2 days at the object level.

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

Fail-safe is a non-configurable feature in Snowflake that provides an additional layer of data protection beyond Time Travel. Time Travel allows users to access historical data within a configurable period (up to 90 days), while Fail-safe provides an additional 7-day period during which Snowflake retains historical data to recover from significant data loss or corruption incidents. This period is not accessible by users but can be used by Snowflake support to assist in data recovery efforts.

To ensure that the output will be a single file when unloading data into a stage, you should use the COPY option SINGLE=TRUE. This option specifies that the result of the COPY INTO command should be written to a single file, rather than multiple files.

Snowflake Documentation: COPY INTO < location >

Resource monitor schedules cannot be reset via update. The only way to restore the default schedule is to drop and recreate the monitor.

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

Snowflake Documentation: Resource Monitors

The correct answer is B. To compile a SQL statement and provide an execution plan while consuming cloud service credits .

The EXPLAIN command returns the logical execution plan for a SQL statement. It compiles the SQL statement but does not execute it. Because the statement is compiled and optimized by Snowflake’s cloud services layer, it does not require a running virtual warehouse.

Why B is correct:

EXPLAIN generates an execution plan without running the query. The compile and optimization work happens in the cloud services layer. Cloud services usage may apply according to Snowflake’s normal cloud services credit rules.

Why the other options are incorrect:

A. EXPLAIN does not require a virtual warehouse to execute the query because the query is not actually run.

C. EXPLAIN does not execute the SQL statement and does not provide actual query history or compute credit consumption.

D. EXPLAIN does not execute the statement or return execution history.

Official Snowflake documentation reference:

Snowflake documentation describes EXPLAIN as a command that returns the logical execution plan for a SQL statement without executing the statement. Snowflake’s cloud services layer handles query parsing and optimization.

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

Processing data in smaller batches reduces resource pressure during execution.

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

The correct answer is D. Contact Snowflake Support .

Important correction:

The provided answer A. Create an organization ID is not the best answer. A Snowflake organization is required for certain marketplace and sharing features, but users do not typically “create an organization ID” as the first procedural step for setting up a Data Exchange.

Why D is correct:

A Data Exchange is a private data hub that allows providers and consumers to securely share data. Creating or enabling a Data Exchange typically requires Snowflake involvement. The first step is to contact Snowflake Support or a Snowflake representative to enable or configure the Data Exchange.

Why the other options are incorrect:

A. An organization identifier may be involved in account and organization-level administration, but creating an organization ID is not the first step to create a Data Exchange.

B. CREATE LISTING is used for creating listings after the provider/share setup exists.

C. CREATE SHARE creates a share object, but it is not the first step for creating the Data Exchange itself.

Official Snowflake documentation reference:

Snowflake documentation describes Data Exchange as a controlled data sharing hub and indicates that setting up a Data Exchange requires working with Snowflake.

The correct answer is B. Database .

A database role is designed to manage access to objects within a database. Database roles are useful for grouping privileges on objects that belong to the same logical database-level domain, such as related schemas, tables, and views.

Why B is correct:

Database roles are created inside a database and can be granted privileges on objects in that database. They help organize access control for database-specific objects.

Example concept:

CREATE DATABASE ROLE sales_db.sales_reader;

GRANT USAGE ON SCHEMA sales_db.reporting TO DATABASE ROLE sales_db.sales_reader;

GRANT SELECT ON ALL TABLES IN SCHEMA sales_db.reporting TO DATABASE ROLE sales_db.sales_reader;

The database role can then be granted to an account role.

Why the other options are incorrect:

A. Account roles are account-level roles and can hold privileges across many databases and account objects. They are broader than the logical grouping described in the question.

C. Service roles are associated with service-related access, not general database object access control.

D. Application roles are used by Snowflake Native Apps to manage access inside an application, not to generally control access to a logical group of database objects.

Official Snowflake documentation reference:

Snowflake documentation describes database roles as roles scoped to a database and used to manage privileges on database objects.

The correct answer is A. A staged file needs to be unloaded to a local machine .

The Snowflake GET command is used to download files from an internal Snowflake stage to a local file system. It is commonly used after data has been unloaded from a table into an internal stage and the user wants to retrieve those staged files locally.

Why A is correct:

The GET command downloads files from a Snowflake internal stage to a local directory.

Example:

GET @my_stage file:///tmp/data/;

This command downloads files from the internal stage @my_stage to the local /tmp/data/ directory.

Why the other options are incorrect:

B. Loading a local file onto a stage is done with the PUT command, not GET.

C. Opening query results in a third-party tool is not the purpose of the GET command.

D. Permanently deleting files from a Snowflake stage is done with the REMOVE command, not GET.

Official Snowflake documentation reference:

Snowflake documentation describes the GET command as the command used to download data files from a Snowflake stage to a local directory or folder.

The correct answer is B. DROP PROCEDURE SALES_REPORT(INT, INT); .

In Snowflake, stored procedures can be overloaded. This means multiple procedures can have the same name but different argument signatures. Because of this, when dropping a stored procedure, the procedure name and argument data types are used to identify the exact procedure to remove.

Why B is correct:

The procedure was created with two arguments:

MONTH INT, YEAR INT

When dropping the procedure, Snowflake uses the argument data types, not the argument names. Therefore, the correct statement is:

DROP PROCEDURE SALES_REPORT(INT, INT);

Why the other options are incorrect:

A. The argument names are not included in the drop signature. Snowflake expects the argument data types.

C. This includes only argument names without data types, which is not the correct procedure signature syntax.

D. This does not identify the procedure signature and is not sufficient when dropping a stored procedure.

Official Snowflake documentation reference:

Snowflake documentation for DROP PROCEDURE explains that the procedure name and argument data types are required to identify the stored procedure to drop.

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

Introduction to Python Worksheets:

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

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

Functionality:

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

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

Integration with Snowflake:

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

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

Snowflake Documentation: Snowsight Python Worksheets

The correct answer is D. Increase the value of the MAX_CLUSTER_COUNT .

Queries wait in a queue when a warehouse does not have enough available compute capacity to handle the current concurrency. For a multi-cluster warehouse, increasing MAX_CLUSTER_COUNT allows Snowflake to start more clusters when concurrency increases.

Why D is correct:

Increasing MAX_CLUSTER_COUNT allows a multi-cluster warehouse in Auto-scale mode to add more clusters, up to the configured maximum. This helps reduce query queuing for concurrent workloads.

Why the other options are incorrect:

A. Increasing WAREHOUSE_SIZE gives each cluster more compute power, which can help individual queries run faster, but it is not the best direct solution for reducing queued concurrent queries.

B. Increasing AUTO_SUSPEND keeps the warehouse running longer but does not increase concurrency capacity.

C. WAIT_FOR_COMPLETION = FALSE is used with warehouse resize behavior and does not reduce query queuing.

Official Snowflake documentation reference:

Snowflake documentation explains that multi-cluster warehouses improve concurrency by starting additional clusters as query demand increases. The MAX_CLUSTER_COUNT property defines the maximum number of clusters that can run.

The correct answer is A. GZIP .

When unloading data from Snowflake to staged files using COPY INTO < location > , the default compression for CSV output is GZIP unless a different compression option is specified.

Why A is correct:

For CSV unloads, Snowflake compresses output files using GZIP by default.

Example:

COPY INTO @my_stage/output/

FROM my_table

FILE_FORMAT = (TYPE = CSV);

If no compression is specified, the unloaded CSV files are compressed with GZIP by default.

Why the other options are incorrect:

B. AUTO is commonly used when loading data so Snowflake can automatically detect compression. It is not the default unload compression for CSV.

C. NONE disables compression, but it is not the default.

D. DEFLATE is a supported compression type, but it is not the default for CSV unload.

Official Snowflake documentation reference:

Snowflake documentation for CSV file format options explains that the default COMPRESSION value for unloading CSV data is GZIP.

TheREMOVEcommand is used in Snowflake to delete files from a stage after they have been successfully ingested into Snowflake tables. This command helps manage storage by allowing users to clean up staged files that are no longer needed, ensuring that the stage does not accumulate unnecessary data over time. UnlikeDELETE,DROP, orTRUNCATEcommands, which are used for managing data within Snowflake tables or dropping objects,REMOVEspecifically targets the management of files in stages.

The correct answer is C. SAML .

In Snowflake, Single Sign-On is commonly implemented using federated authentication through a SAML 2.0 identity provider. When creating an authentication policy, including SAML in the allowed AUTHENTICATION_METHODS enables SSO-based login options.

Why C is correct:

SAML represents SAML-based federated authentication. This is the authentication method associated with SSO login in Snowflake.

Example concept:

CREATE AUTHENTICATION POLICY sso_policy

AUTHENTICATION_METHODS = ( ' SAML ' );

Why the other options are incorrect:

A. PASSWORD allows username and password authentication, not SSO.

B. KEYPAIR allows key-pair authentication, commonly used for service accounts and applications.

D. OAUTH allows OAuth-based authentication, but the SSO login option in this context is associated with SAML.

Official Snowflake documentation reference:

Snowflake documentation describes authentication policies as controlling allowed authentication methods, including password, SAML, OAuth, and key-pair authentication. SAML is used for federated SSO authentication.

The command used to export or unload data from Snowflake to a stage (such as a file in an S3 bucket, Azure Blob Storage, or Google Cloud Storage) is thePUTcommand. ThePUTcommand is designed to upload data files from a local file system (in the case of SnowSQL or other client) or a virtual warehouse to a specified stage. This functionality is critical for scenarios where data needs to be extracted from Snowflake for use in external systems, backups, or further processing.

The syntax for thePUTcommand follows the structure:PUT file:// < local_file_path > @ < stage_name > , where < local_file_path > specifies the path to the file(s) on the localfile system that you wish to upload, and < stage_name > specifies the destination stage in Snowflake.

It ' s important to distinguish that thePUTcommand is used for exporting data out of Snowflake, whereas theCOPY INTO < table > command is used for importing data into Snowflake from a stage. TheGETcommand, on the other hand, is used to download files from a stage to the local file system, essentially the inverse operation of thePUTcommand.

A materialized view in Snowflake denotes a pre-computed data set derived from a SELECT query specification and stored for later use. Unlike standard views, which dynamically compute the data each time the view is accessed, materialized views store the result of the query at the time it is executed, thereby speeding up access to the data, especially for expensive aggregations on large datasets.

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

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

Snowflake ' s unique architecture is described as a multi-cluster, shared data architecture that leverages massively parallel processing (MPP). This architecture separates compute and storage resources, enabling Snowflake to scale them independently. It does not use a single cluster or rely solely on symmetric multiprocessing (SMP); rather, it uses a combination of shared-nothing architecture for compute clusters (virtual warehouses) and a centralized storage layer for data, optimizing for both performance and scalability.

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

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.

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.

Characteristics of Snowsight worksheets in Snowflake include:

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

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

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

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

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

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

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

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

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

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

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

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: TheSEARCH_PATHparameter influences object resolution for queries, not within UDFs.

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

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.

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.

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

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.

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

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

By default, the ACCOUNTADMIN role in Snowflake has access to the SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER function. This function is used to set global account parameters, impacting the entire Snowflake account ' s configuration and behavior. The ACCOUNTADMIN role is the highest-level administrative role in Snowflake, granting the necessary privileges to manage account settings and security features, including the use of global account parameters.

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

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

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

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

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

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.

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.

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

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

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

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

Key Points:

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

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

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

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.

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

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.

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

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

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

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

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

Temporary tables in Snowflake do not consume storage costs. They are designed for transient data that is needed only for the duration of a session. Data stored in temporary tables is held in the virtual warehouse ' s cache and does not persist beyond the session ' s lifetime, thereby not incurring any storage charges.

A materialized view in Snowflake is a database object derived from a query specification, stored for later use, and can significantly speed up expensive aggregations on large data sets. Materialized views store the result of their underlying query, reducing the need to recompute the result each time the view is accessed. This makes them ideal for improving the performance of read-heavy, aggregate-intensive queries.

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.

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

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

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

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

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

A cached MFA token is valid for up to four hours.https://docs.snowflake.com/en/user-guide/security-mfa#using-mfa-token-caching-to-minimize-the-number-of-prompts-during-authentication-optional

By default, the standard retention period for Time Travel in Snowflake is 1 day across all Snowflake accounts. Time Travel enables users to access historical data within this retention window, allowing for point-in-time data analysis and recovery. This feature is a significant aspect of Snowflake ' s data management capabilities, offering flexibility in handling data changes and accidental deletions.

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

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.

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

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.

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

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

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

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

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

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

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

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

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.

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

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

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

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

 A Scoped URL permits temporary access to a staged file without the need to grant privileges to the stage or to issue access tokens. It provides a secure way to share access to filesstored in Snowflake

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

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

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

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

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

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

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

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

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

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

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

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

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

Micro-partitioning is a Snowflake function that is maintained separately from the data and supports features such as Time Travel, Secure Data Sharing, and pruning. It allows Snowflake to efficiently manage and query large datasets by organizing them into micro-partitions1.

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

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

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

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

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

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