Snowflake users can create a resource monitor at which levels? (Select TWO).
User level
Pipe level
Account level
Cloud services level
Virtual warehouse level
Resource monitors in Snowflake are tools used to track and control the consumption of compute resources, ensuring that usage stays within defined limits. These monitors can be created at the account level, allowing administrators to set overall resource consumption limits for the entire Snowflake account. Additionally, resource monitors can be set at the virtual warehouse level, enabling more granular control over the resources consumed by individual warehouses. This dual-level capability allows organizations to manage their Snowflake usage efficiently, preventing unexpected costs and optimizing performance.References: Snowflake Documentation on Resource Monitors
Top of Form
What happens to the privileges granted to Snowflake system-defined roles?
The privileges cannot be revoked.
The privileges can be revoked by an ACCOUNTADMIN.
The privileges can be revoked by an orgadmin.
The privileges can be revoked by any user-defined role with appropriate privileges.
The privileges granted to Snowflake's system-defined roles cannot be revoked. System-defined roles, such as SYSADMIN, ACCOUNTADMIN, SECURITYADMIN, and others, come with a set of predefined privileges that are essential for the roles to function correctly within the Snowflake environment. These privileges are intrinsic to the roles and ensure that users assigned these roles can perform the necessary tasks and operations relevant to their responsibilities.
The design of Snowflake's role-based access control (RBAC) model ensures that system-defined roles have a specific set of non-revocable privileges to maintain the security, integrity, and operational efficiency of the Snowflake environment. This approach prevents accidental or intentional modification of privileges that could disrupt the essential functions or compromise the security of the Snowflake account.
References:
Snowflake Documentation on Access Control: Understanding Role-Based Access Control (RBAC)
Which process does Snowflake follow when a stored procedure with owner's rights is called within a session?
The procedure will be run with the privileges of the caller.
The owner can set the caller's session variables.
The owner will inherit the caller's current virtual warehouse.
The owner can view the caller's session variables.
Owner's Rights Stored Procedures:
When a stored procedure with owner’s rights is called, it runs with the privileges of the owner, not the caller.
This ensures that the procedure executes with access to objects the owner has privileges for, regardless of the caller’s permissions.
Why Other Options Are Incorrect:
B. The owner can set the caller's session variables: Stored procedures cannot modify session variables of the caller.
C. The owner will inherit the caller's current virtual warehouse: The procedure uses the session’s current warehouse, not inherited settings.
D. The owner can view the caller's session variables: Session variables are private to the caller and not visible to the owner.
References:
Stored Procedure Privileges Documentation
What does Snowflake attempt to do if any of the compute resources for a virtual warehouse fail to provision during start-up?
Repair the failed resources.
Restart failed resources.
Queue the failed resources
Provision the failed resources
If any compute resources for a virtual warehouse fail to provision during startup, Snowflake will attempt to restart those failed resources. The system is designed to automatically handle transient issues that might occur during the provisioning of compute resources. By restarting the failed resources, Snowflake aims to ensure that the virtual warehouse has the necessary compute capacity to handle the user's workloads without manual intervention.References: Snowflake Documentation on Virtual Warehouses
Which governance feature is supported by all Snowflake editions?
Object tags
Masking policies
Row access policies
OBJECT_DEPENDENCIES View
Snowflake's governance features vary across different editions, but the OBJECT_DEPENDENCIES view is supported by all Snowflake editions. This feature is part of Snowflake's Information Schema and is designed to help users understand the dependencies between various objects in their Snowflake environment.
The OBJECT_DEPENDENCIES view provides a way to query and analyze the relationships and dependencies among different database objects, such as tables, views, and stored procedures. This is crucial for governance, as it allows administrators and data engineers to assess the impact of changes, understand object relationships, and ensure proper management of data assets.
Object tags, masking policies, and row access policies are more advanced features that offer fine-grained data governance capabilities such as tagging objects for classification, dynamically masking sensitive data based on user roles, and controlling row-level access to data. These features may have varying levels of support across different Snowflake editions, with some features being exclusive to higher-tier editions.
Which Snowflake table type is only visible to the user who creates it, can have the same name as permanent tables in the same schema, and is dropped at the end of the session?
Temporary
Local
User
Transient
In Snowflake, a Temporary table is a type of table that is only visible to the user who creates it, can have the same name as permanent tables in the same schema, and is automatically dropped at the end of the session in which it was created. Temporary tables are designed for transient data processing needs, where data is needed for the duration of a specific task or session but not beyond. Since they are automatically cleaned up at the end of the session, they help manage storage usage efficiently and ensure that sensitive data is not inadvertently persisted.
References:
Snowflake Documentation on Temporary Tables: Temporary Tables
Which Snowflake native tool can be used to diagnose and troubleshoot network connections?
SnowSQL
Snowflake Python connector
Snowsight
SnowCD
SnowSQL, Snowflake's command-line client, can be used to diagnose and troubleshoot network connections. SnowSQL provides various commands and options to test connectivity, configure network settings, and troubleshoot issues related to network connections between the client and Snowflake.
References:
Snowflake Documentation: SnowSQL
What is a non-configurable feature that provides historical data that Snowflake may recover during a 7-day period?
Fail-safe
Time Travel
Cloning
Account replication
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.References: Snowflake Documentation on Fail-safe and Time Travel
What are supported file formats for unloading data from Snowflake? (Choose three.)
XML
JSON
Parquet
ORC
AVRO
CSV
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
What actions will prevent leveraging of the ResultSet cache? (Choose two.)
Removing a column from the query SELECT list
Stopping the virtual warehouse that the query is running against
Clustering of the data used by the query
Executing the RESULTS_SCAN() table function
Changing a column that is not in the cached query
The ResultSet cache is leveraged to quickly return results for repeated queries. Actions that prevent leveraging this cache include stopping the virtual warehouse that the query is running against (B) and executing the RESULTS_SCAN() table function (D). Stopping the warehouse clears the local disk cache, including the ResultSet cache1. The RESULTS_SCAN() function is used to retrieve the result of a previously executed query, which bypasses the need for the ResultSet cache.
When creating a file format to load JSON data into Snowflake. what command will remove brackets ([ ]) from the data?
STRIP_NULL_VALUES - TRUE
REPLACE_INVALID_CHARACTERS = TRUE
TRIM_SPACE = TRUE
STRIP OUTER ARRAY = TRUE
When creating a file format to load JSON data into Snowflake, the STRIP_OUTER_ARRAY option is used to remove outer brackets ([ ]) from a JSON array.
Key Use Case: This is particularly helpful when the JSON data contains a top-level array, and you want to treat its elements as separate records for loading into Snowflake.
Example:
sql
CopyEdit
CREATE FILE FORMAT my_json_format
TYPE = JSON
STRIP_OUTER_ARRAY = TRUE;
Why Other Options Are Incorrect:
A. STRIP_NULL_VALUES: Removes NULL values, not brackets.
B. REPLACE_INVALID_CHARACTERS: Replaces invalid characters but does not remove brackets.
C. TRIM_SPACE: Removes leading and trailing spaces, unrelated to JSON brackets.
References:
File Formats for JSON Data
Which data types are supported by Snowflake when using semi-structured data? (Choose two.)
VARIANT
VARRAY
STRUCT
ARRAY
QUEUE
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
Which minimum Snowflake edition allows for a dedicated metadata store?
Standard
Enterprise
Business Critical
Virtual Private Snowflake
The Enterprise edition of Snowflake allows for a dedicated metadata store, providing additional features designed for large-scale enterprises
Which statements are correct concerning the leveraging of third-party data from the Snowflake Data Marketplace? (Choose two.)
Data is live, ready-to-query, and can be personalized.
Data needs to be loaded into a cloud provider as a consumer account.
Data is not available for copying or moving to an individual Snowflake account.
Data is available without copying or moving.
Data transformations are required when combining Data Marketplace datasets with existing data in Snowflake.
When leveraging third-party data from the Snowflake Data Marketplace, the data is live, ready-to-query, and can be personalized. Additionally, the data is available without the need for copying or moving it to an individual Snowflake account, allowing for seamless integration with existing data
When does a materialized view get suspended in Snowflake?
When a column is added to the base table
When a column is dropped from the base table
When a DML operation is run on the base table
When the base table is reclustered
A materialized view in Snowflake gets suspended when structural changes that could impact the view's integrity are made to the base table, such as B. When a column is dropped from the base table. Dropping a column from the base table on which a materialized view is defined can invalidate the view's data, as the view might rely on the column that is being removed. To maintain data consistency and prevent the materialized view from serving stale or incorrect data, Snowflake automatically suspends the materialized view.
Upon suspension, the materialized view does not reflect changes to the base table until it is refreshed or re-created. This ensures that only accurate and current data is presented to users querying the materialized view.
References:
Snowflake Documentation on Materialized Views: Materialized Views
What causes objects in a data share to become unavailable to a consumer account?
The DATA_RETENTI0N_TIME_IN_DAYS parameter in the consumer account is set to 0.
The consumer account runs the GRANT IMPORTED PRIVILEGES command on the data share every 24 hours.
The objects in the data share are being deleted and the grant pattern is not re-applied systematically.
The consumer account acquires the data share through a private data exchange.
Objects in a data share become unavailable to a consumer account if the objects in the data share are deleted or if the permissions on these objects are altered without re-applying the grant permissions systematically. This is because the sharing mechanism in Snowflake relies on explicit grants of permissions on specific objects (like tables, views, or secure views) to the share. If these objects are deleted or if their permissions change without updating the share accordingly, consumers can lose access.
The DATA_RETENTION_TIME_IN_DAYS parameter does not directly affect the availability of shared objects, as it controls how long Snowflake retains historical data for time travel and does not impact data sharing permissions.
Running the GRANT IMPORTED PRIVILEGES command in the consumer account is not related to the availability of shared objects; this command is used to grant privileges on imported objects within the consumer's account and is not a routine maintenance command that would need to be run regularly.
Acquiring a data share through a private data exchange does not inherently make objects unavailable; issues would only arise if there were problems with the share configuration or if the shared objects were deleted or had their permissions altered without re-granting access to the share.
Which command allows a user to unload data from a Snowflake database table into one or more files in a Snowflake or external stage?
GET
LIST
PUT
COPY INTO
The COPY INTO
Syntax Example:
COPY INTO 's3://mybucket/'
FROM my_table
FILE_FORMAT = (TYPE = CSV);
References:
Snowflake Documentation: COPY INTO
Snowflake Data Unloading Best Practices
=========================
What are common issues found by using the Query Profile? (Choose two.)
Identifying queries that will likely run very slowly before executing them
Locating queries that consume a high amount of credits
Identifying logical issues with the queries
Identifying inefficient micro-partition pruning
Data spilling to a local or remote disk
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.
How can a Snowsight user change a Standard virtual warehouse to a Snowpark-optimized virtual warehouse?
Use the ALTER WAREHOUSE command on an active Standard virtual warehouse
Use the alter warehouse command on an active Snowpark-optimized warehouse.
Use the ALTER warehouse command on a suspended Standard virtual warehouse.
Use the ALTER WAREHOUSE command on a suspended Snowpark-optimized warehouse.
To change a Standard virtual warehouse to a Snowpark-optimized virtual warehouse, the warehouse must be in a suspended state. This ensures that no operations are disrupted during the modification process.
Suspend the Warehouse: Ensure that the warehouse is suspended.
ALTER WAREHOUSE my_warehouse SUSPEND;
Alter the Warehouse: Modify the warehouse to be Snowpark-optimized.
ALTER WAREHOUSE my_warehouse SET WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED';
Resume the Warehouse: Resume the warehouse to make it operational.
ALTER WAREHOUSE my_warehouse RESUME;
References:
Snowflake Documentation: Creating Snowpark-Optimized Warehouses
Snowflake Documentation: ALTER WAREHOUSE
A user needs to know the maximum value of a date field in a table, and runs the following query:
....part of Snowflake architecture wilt this query use?
Database Storage
Query Processing
Cloud Services
Compute
When querying the maximum value of a date field, Snowflake retrieves metadata stored in its Database Storage layer.
Snowflake stores data in columnar format with metadata (e.g., min/max values per column per micro-partition), enabling efficient queries like MAX() to use metadata without scanning all rows.
Why Other Options Are Incorrect:
B. Query Processing: Processes query execution but does not store metadata.
C. Cloud Services: Manages metadata and services but does not execute the query directly.
D. Compute: Executes queries but accesses data from Database Storage.
References:
Snowflake Architecture
Which function determines the kind of value stored in a VARIANT column?
CHECK_JSON
IS_ARRAY
IS_JSON
TYPEOF
The function used to determine the kind of value stored in a VARIANT column in Snowflake is TYPEOF.
Understanding VARIANT Data Type:
VARIANT is a flexible data type in Snowflake that can store semi-structured data, such as JSON, Avro, and XML.
This data type can hold values of different types, including strings, numbers, objects, arrays, and more.
Using TYPEOF Function:
The TYPEOF function returns the data type of the value stored in a VARIANT column.
It helps in identifying the type of data, which is crucial for processing and transforming semi-structured data accurately.
Example Usage:
SELECT TYPEOF(variant_column)
FROM my_table;
This query retrieves the type of data stored in variant_column for each row in my_table.
Possible return values include 'OBJECT', 'ARRAY', 'STRING', 'NUMBER', etc.
Benefits:
Simplifies data processing: Knowing the data type helps in applying appropriate transformations and validations.
Enhances query accuracy: Ensures that operations on VARIANT columns are performed correctly based on the data type.
References:
Snowflake Documentation: TYPEOF
Snowflake Documentation: VARIANT Data Type
If a JOIN operation is based on two columns which both contain NULL values, what SQL function will return results?
An NVL function used to change the NULL values to another value
An equal_null function
A NULLIF function
An I FNULL function used to change the NULL values to another value
In SQL, NULL values are not equal to other NULL values. To make comparisons work in join operations, the NVL function can replace NULL with a non-NULL value.
Example:
SELECT *
FROM table1 t1
JOIN table2 t2
ON NVL(t1.col, 'default') = NVL(t2.col, 'default');
Why Other Options Are Incorrect:
B. Equal_null function: No such function exists in Snowflake SQL.
C. NULLIF function: Used to return NULL when two values are equal, not for handling NULL in joins.
D. IFNULL function: Similar to NVL but less commonly used in this context.
References:
NVL Function Documentation
What Snowflake objects or services can be used to automatically load data from files as soon as. They are available in an external staged (Select TWO).
Stream
Snowpipe
Task
Directory table
User-Defined Function (UDF)
Snowflake provides mechanisms to automatically load data from files in external stages (like Amazon S3 or Azure Blob Storage).
The correct methods include:
Snowpipe (B):
Snowpipe is Snowflake’s continuous data ingestion service, which automatically detects and loads new files from external stages into Snowflake tables.
It provides near real-time data loading with low-latency ingestion.
Example:
CREATE PIPE my_pipe
AUTO_INGEST = TRUE
AS COPY INTO my_table FROM @my_stage FILE_FORMAT = (TYPE = 'CSV');
Task (C):
Tasks can be scheduled to run at specific intervals or triggered in response to events.
You can create a task to call a stored procedure, which, in turn, uses Snowpipe or COPY INTO to load data.
Example:
CREATE TASK my_task
SCHEDULE = '5 MINUTE'
AS
CALL load_data_procedure();
Why Other Options Are Incorrect:
A. Stream: Streams track changes in a table for change data capture (CDC) but do not directly load data from files.
D. Directory table: Snowflake does not have a "directory table" object for managing external file ingestion.
E. User-Defined Function (UDF): UDFs are used for custom logic within SQL queries, not for data loading.
References:
Snowpipe Overview
Tasks Overview
Which network policy has the HIGHEST precedence?
Security Integration
Account
Database
User
Network Policies in Snowflake define the IP address ranges allowed to connect to the Snowflake account.
Precedence of network policies:
Account-Level Policies (Highest Precedence): Apply to the entire Snowflake account and override lower-level policies.
User-Level Policies: Apply to individual users but are secondary to account-level policies.
Security Integrations: Used for external integrations and have lower precedence.
Why Other Options Are Incorrect:
C. Database / D. User: Database policies do not exist for network configurations, and user-level policies are secondary to account-level ones.
A. Security Integration: Lower precedence than account-level policies.
References:
Network Policy Documentation
While unloading data into a stage, how can the user ensure that the output will be a single file?
Use the copy option files=single.
Use the COPY Option SINGLE=TRUE .
Use the get option SINGLE-TRUE.
Use the GET option FILES-SINGLE.
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.
References:
Snowflake Documentation: COPY INTO
Which drivers or connectors are supported by Snowflake? (Select TWO)
Perl Connector
MongoDB Rust Driver
Go Snowflake Driver
Cobol Driver
Snowflake Connector for Python
Snowflake supports a wide range of drivers and connectors to integrate with different programming languages and platforms. Two key supported options are:
Go Snowflake Driver (C): A driver for the Go programming language.
Snowflake Connector for Python (E): A native Python connector to interact with Snowflake.
References:
Snowflake Documentation: Supported Drivers and Connectors
Snowflake Go Driver
Snowflake Python Connector
Which type of role can be granted to a share?
Account role
Custom role
Database role
Secondary role
In Snowflake, shares are used to share data between Snowflake accounts. When creating a share, it is possible to grant access to the share to roles within the Snowflake account that is creating the share. The type of role that can be granted to a share is a Custom role. Custom roles are user-defined roles that account administrators can create to manage access control in a more granular way. Unlike predefined roles such as ACCOUNTADMIN or SYSADMIN, custom roles can be tailored with specific privileges to meet the security and access requirements of different groups within an organization.
Granting a custom role access to a share enables users associated with that role to access the shared data if the share is received by another Snowflake account. It is important to carefully manage the privileges granted to custom roles to ensure that data sharing aligns with organizational policies and data governance standards.
References:
Snowflake Documentation on Shares: Shares
Snowflake Documentation on Roles: Access Control
Which object can be shared using Secure Data Sharing?
Secure view
Materialized view
Externa! function
User-Defined Function (UDF)
Snowflake's Secure Data Sharing enables sharing of data objects (e.g., tables and secure views) with other Snowflake accounts without copying or transferring the data.
Secure views are sharable because they enforce access control and prevent unauthorized access to underlying data.
Why Other Options Are Incorrect:
B. Materialized view: Materialized views cannot be shared via Secure Data Sharing.
C. External function: External functions are not supported for Secure Data Sharing.
D. User-Defined Function (UDF): UDFs cannot be shared.
References:
Secure Data Sharing
Which privilege grants the ability to set a column-level security masking policy on a table or view column?
APPLY
CREATE
SET
MODIFY
In Snowflake, the APPLY privilege is required to set a masking policy on a table or view column. This privilege allows a user to associate a masking policy with a specific column, thereby controlling how data in that column is masked or hidden.
Create a Masking Policy: Define a masking policy using the CREATE MASKING POLICY command.
Grant APPLY Privilege: Grant the APPLY privilege on the masking policy to the relevant roles or users.
Apply the Masking Policy: Use the ALTER TABLE or ALTER VIEW command to apply the masking policy to a column.
References:
Snowflake Documentation: Data Masking
Snowflake Documentation: CREATE MASKING POLICY
Snowflake Documentation: Privileges for Masking Policies
Which Snowflake governance feature allows users to assign metadata labels to improve data governance and database access control?
Secure functions
Secure views
Object tagging
Row-level security
Object tagging in Snowflake allows users to assign metadata labels to various database objects to improve data governance and access control. Tags can be used to categorize and manage data based on business needs, helping to enforce governance policies and streamline database administration.
References:
Snowflake Documentation: Object Tagging
How can the Query Profile be used to troubleshoot a problematic query?
It will indicate if a virtual warehouse memory is too small to run the query
It will indicate if a user lacks the privileges needed to run the query.
It will indicate if a virtual warehouse is in auto-scale mode
It will indicate if the user has enough Snowflake credits to run the query
The Query Profile in Snowflake provides detailed insights into the execution of a query. It helps in troubleshooting performance issues by showing the steps of the query execution and the resources consumed. One of the key aspects it reveals is whether the virtual warehouse memory was sufficient for the query.
Access Query Profile: Navigate to the Query History page and select the query you want to analyze.
Examine Query Execution Steps: The Query Profile displays the different stages of the query execution, including the time taken and resources used at each step.
Identify Memory Issues: Look for indicators of memory issues, such as spilling to disk or memory errors, which suggest that the virtual warehouse memory might be too small.
References:
Snowflake Documentation: Query Profile
Snowflake Documentation: Optimizing Queries
How does Snowflake utilize clustering information to improve query performance?
It prunes unnecessary micro-partitions based on clustering metadata.
It compresses the data within micro-partitions for faster querying.
It automatically allocates additional resources to improve query execution.
It organizes clustering information to speed-up data retrieval from storage
Snowflake utilizes clustering information to enhance query performance by pruning unnecessary micro-partitions.
Clustering Metadata: Snowflake stores clustering information for each micro-partition, which includes data range and distribution.
Pruning Micro-partitions: When a query is executed, Snowflake uses this clustering metadata to identify and eliminate micro-partitions that do not match the query criteria, thereby reducing the amount of data scanned and improving query performance.
References:
Snowflake Documentation on Clustering
Snowflake Documentation on Micro-partition Pruning
When working with table MY_TABLE that contains 10 rows, which sampling query will always return exactly 5 rows?
SELECT * FROM MY_TABLE SAMPLE SYSTEM (5);
SELECT * FROM MY_TABLE SAMPLE BERNOULLI (5);
SELECT * FROM MY_TABLE SAMPLE (5 ROWS);
SELECT * FROM MY_TABLE SAMPLE SYSTEM (1) SEED (5);
In Snowflake, SAMPLE (5 ROWS) ensures an exact count of 5 rows is returned from MY_TABLE, regardless of table size. This is different from SAMPLE SYSTEM or SAMPLE BERNOULLI, which use percentage-based sampling, potentially returning varying row counts based on probabilistic methods.
The ROWS option is deterministic and does not depend on percentage, making it ideal when an exact row count is required.
What are the main differences between the account usage views and the information schema views? (Select TWO).
No active warehouse to needed to query account usage views but one is needed to query information schema views.
Account usage views do not contain data about tables but information schema views do.
Account issue views contain dropped objects but information schema views do not.
Data retention for account usage views is 1 year but is 7 days to 6 months for information schema views, depending on the view.
Information schema views are read-only but account usage views are not.
The account usage views in Snowflake provide historical usage data about the Snowflake account, and they retain this data for a period of up to 1 year. These views include information about dropped objects, enabling audit and tracking activities. On the other hand, information schema views provide metadata about database objects currently in use, such as tables and views, but do not include dropped objects. The retention of data in information schema views varies, but it is generally shorter than the retention for account usage views, ranging from 7 days to a maximum of 6 months, depending on the specific view.References: Snowflake Documentation on Account Usage and Information Schema
What actions can be performed by a consumer account on a shared database? (Select TWO).
Cloning a shared table
Modifying the data in a shared table
Using Time Travel on a shared table
Executing the select statement on a shared table
Joining the data from a shared table with another table
Consumers of a shared database in Snowflake can:
Execute SELECT statements (D): Consumers can query the shared tables directly.
Join the data (E): Consumers can join shared tables with other tables in their account.
Limitations for Consumers:
Cannot modify shared data: Data sharing is read-only for consumers.
No cloning or Time Travel: Consumers cannot clone shared tables or use Time Travel.
References:
Snowflake Documentation: Secure Data Sharing
Data Sharing Features
What Snowflake recommendation is designed to ensure that staged data is only loaded once"?
Partitioning staged data files
Loading only the most recently-staged data files
Removing data files after loading
Identifying and removing duplicates after each data load
Snowflake recommends removing data files from the staging area after they have been loaded into the target table. This practice ensures that the data is only loaded once and prevents accidental reloading of the same data. By removing the files, you eliminate the risk of duplicate data loads.
Stage the Data: Upload the data files to a Snowflake stage (internal or external).
Load the Data: Use the COPY INTO command to load the data from the stage into the Snowflake table.
Remove the Data Files: After successfully loading the data, remove the data files from the stage using the REMOVE command.
References:
Snowflake Documentation: Loading Data into Snowflake
Snowflake Documentation: Staging Data Files
Snowflake Documentation: COPY INTO Command
Which Snowflake object can be used to record DML changes made to a table?
Snowpipe
Stage
Stream
Task
Snowflake Streams are used to track and record Data Manipulation Language (DML) changes made to a table. Streams capture changes such as inserts, updates, and deletes, which can then be processed by other Snowflake objects or external applications.
Creating a Stream:
CREATE OR REPLACE STREAM my_stream ON TABLE my_table;
Using Streams: Streams provide a way to process changes incrementally, making it easier to build efficient data pipelines.
Consuming Stream Data: The captured changes can be consumed using SQL queries or Snowflake tasks.
References:
Snowflake Documentation: Using Streams
Snowflake Documentation: Change Data Capture (CDC) with Streams
What is the MAXIMUM data retention period of transient databases when using the Snowflake Enterprise edition?
1 day
7 days
30 days
90 days
In Snowflake Enterprise Edition, the maximum data retention period for transient databases is 1 day.
Transient databases do not support Time Travel for longer durations because they are designed to minimize storage costs.
References:
Snowflake Documentation: Transient Databases
Snowflake Editions Comparison
Which function can be used with the copy into
FLATTEN
OBJECT_AS
OBJECT_CONSTRUCT
TO VARIANT
The correct function to use with the COPY INTO <location> statement to convert rows from a relational table into a single variant column and to unload rows into a JSON file is TO VARIANT. The TO VARIANT function is used to explicitly convert a value of any supported data type into a VARIANT data type. This is particularly useful when needing to aggregate multiple columns or complex data structures into a single JSON-formatted string, which can then be unloaded into a file.
In the context of unloading data, the COPY INTO <location> statement combined with TO VARIANT enables the conversion of structured data from Snowflake tables into a semi-structured VARIANT format, typically JSON, which can then be efficiently exported and stored. This approach is often utilized for data integration scenarios, backups, or when data needs to be shared in a format that is easily consumed by various applications or services that support JSON.
References:
Snowflake Documentation on Data Unloading: Unloading Data
Snowflake Documentation on VARIANT Data Type: Working with JSON
How does the authorization associated with a pre-signed URL work tor an unstructured file?
Anyone who has the URL can access the referenced file for the life of the token.
Only the user who generates the URL can use the URL to access the referenced file.
Only the users who have roles with sufficient privileges on the URL can access the referenced file.
The role specified in the GET REST API call must have sufficient privileges on the stage to access the referenced file using the URL.
A pre-signed URL in Snowflake grants access to unstructured files (e.g., in external stages) without requiring additional authentication.
The URL includes an embedded token that specifies the expiration time and access permissions.
Key Point: Anyone who has the pre-signed URL can access the referenced file for the duration of the token’s validity.
Why Other Options Are Incorrect:
B. Only the user who generates the URL can use it: The URL is shareable and not restricted to its creator.
C. Users with roles and privileges: Pre-signed URLs bypass direct role or privilege checks.
D. Role specified in GET REST API call: This is unrelated to pre-signed URL access.
References:
Pre-Signed URLs Documentation
The Snowflake Cloud Data Platform is described as having which of the following architectures?
Shared-disk
Shared-nothing
Multi-cluster shared data
Serverless query engine
Snowflake’s architecture is described as a multi-cluster, shared data architecture. This design combines the simplicity of a shared-disk architecture with the performance and scale-out benefits of a shared-nothing architecture, using a central repository accessible from all compute nodes2.
References = [COF-C02] SnowPro Core Certification Exam Study Guide, Snowflake Documentation
Which of the following is an example of an operation that can be completed without requiring compute, assuming no queries have been executed previously?
SELECT SUM (ORDER_AMT) FROM SALES;
SELECT AVG(ORDER_QTY) FROM SALES;
SELECT MIN(ORDER_AMT) FROM SALES;
SELECT ORDER_AMT * ORDER_QTY FROM SALES;
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.
What types of data listings are available in the Snowflake Data Marketplace? (Choose two.)
Reader
Consumer
Vendor
Standard
Personalized
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.
Which of the following statements apply to Snowflake in terms of security? (Choose two.)
Snowflake leverages a Role-Based Access Control (RBAC) model.
Snowflake requires a user to configure an IAM user to connect to the database.
All data in Snowflake is encrypted.
Snowflake can run within a user's own Virtual Private Cloud (VPC).
All data in Snowflake is compressed.
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-C02] SnowPro Core Certification Exam Study Guide
Which of the following objects can be directly restored using the UNDROP command? (Choose two.)
Schema
View
Internal stage
Table
User
Role
The UNDROP command in Snowflake can be used to directly restore Views and Tables. These objects, when dropped, are moved to a ‘Recycle Bin’ where they can be restored within a time limit before they are permanently deleted. References: [COF-C02] SnowPro Core Certification Exam Study Guide
What is the purpose of multi-cluster virtual warehouses?
To create separate data warehouses to increase query optimization
To allow users the ability to choose the type of compute nodes that make up a virtual warehouse cluster
To eliminate or reduce Queuing of concurrent queries
To allow the warehouse to resize automatically
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 .
What impacts the credit consumption of maintaining a materialized view? (Choose two.)
Whether or not it is also a secure view
How often the underlying base table is queried
How often the base table changes
Whether the materialized view has a cluster key defined
How often the materialized view is queried
The credit consumption for maintaining a materialized view is impacted by how often the base table changes © and whether the materialized view has a cluster key defined (D). Changes to the base table can trigger a refresh of the materialized view, consuming credits. Additionally, having a cluster key defined can optimize the performance and credit usage during the materialized view’s maintenance. References: SnowPro Core Certification materialized view credit consumption
Which task is supported by the use of Access History in Snowflake?
Data backups
Cost monitoring
Compliance auditing
Performance optimization
Access History in Snowflake is primarily utilized for compliance auditing. The Access History feature provides detailed logs that track data access and modifications, including queries that read from or write to database objects. This information is crucial for organizations to meet regulatory requirements and to perform audits related to data access and usage.
Role of Access History: Access History logs are designed to help organizations understand who accessed what data and when. This is particularly important for compliance with various regulations that require detailed auditing capabilities.
How Access History Supports Compliance Auditing:
By providing a detailed log of access events, organizations can trace data access patterns, identify unauthorized access, and ensure that data handling complies with relevant data protection laws and regulations.
Access History can be queried to extract specific events, users, time frames, and accessed objects, making it an invaluable tool for compliance officers and auditors.
What affects whether the query results cache can be used?
If the query contains a deterministic function
If the virtual warehouse has been suspended
If the referenced data in the table has changed
If multiple users are using the same virtual warehouse
The query results cache can be used as long as the data in the table has not changed since the last time the query was run. If the underlying data has changed, Snowflake will not use the cached results and will re-execute the query1.
What is the maximum Time Travel retention period for a temporary Snowflake table?
90 days
1 day
7 days
45 days
The maximum Time Travel retention period for a temporary Snowflake table is 1 day. This is the standard retention period for temporary tables, which allows for accessing historical data within a 24-hour window
What is the minimum Snowflake edition that has column-level security enabled?
Standard
Enterprise
Business Critical
Virtual Private Snowflake
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-C02] SnowPro Core Certification Exam Study Guide, Snowflake Documentation1
What do the terms scale up and scale out refer to in Snowflake? (Choose two.)
Scaling out adds clusters of the same size to a virtual warehouse to handle more concurrent queries.
Scaling out adds clusters of varying sizes to a virtual warehouse.
Scaling out adds additional database servers to an existing running cluster to handle more concurrent queries.
Snowflake recommends using both scaling up and scaling out to handle more concurrent queries.
Scaling up resizes a virtual warehouse so it can handle more complex workloads.
Scaling up adds additional database servers to an existing running cluster to handle larger workloads.
Scaling out in Snowflake involves adding clusters of the same size to a virtual warehouse, which allows for handling more concurrent queries without affecting the performance of individual queries. Scaling up refers to resizing a virtual warehouse to increase its compute resources, enabling it to handle more complex workloads and larger queries more efficiently.
Which columns are part of the result set of the Snowflake LATERAL FLATTEN command? (Choose two.)
CONTENT
PATH
BYTE_SIZE
INDEX
DATATYPE
The LATERAL FLATTEN command in Snowflake produces a result set that includes several columns, among which PATH and INDEX are includedPATH indicates the path to the element within a data structure that needs to be flattened, and INDEX represents the index of the element if it is an array2.
Snowflake supports the use of external stages with which cloud platforms? (Choose three.)
Amazon Web Services
Docker
IBM Cloud
Microsoft Azure Cloud
Google Cloud Platform
Oracle Cloud
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
Which of the following features are available with the Snowflake Enterprise edition? (Choose two.)
Database replication and failover
Automated index management
Customer managed keys (Tri-secret secure)
Extended time travel
Native support for geospatial data
The Snowflake Enterprise edition includes database replication and failover for business continuity and disaster recovery, as well as extended time travel capabilities for longer data retention periods1.
What is true about sharing data in Snowflake? (Choose two.)
The Data Consumer pays for data storage as well as for data computing.
The shared data is copied into the Data Consumer account, so the Consumer can modify it without impacting the base data of the Provider.
A Snowflake account can both provide and consume shared data.
The Provider is charged for compute resources used by the Data Consumer to query the shared data.
The Data Consumer pays only for compute resources to query the shared data.
In Snowflake’s data sharing model, any full Snowflake account can both provide and consume shared data. Additionally, the data consumer pays only for the compute resources used to query the shared data. No actual data is copied or transferred between accounts, and shared data does not take up any storage in a consumer account, so the consumer does not pay for data storage1.
References = Introduction to Secure Data Sharing | Snowflake Documentation
Network policies can be set at which Snowflake levels? (Choose two.)
Role
Schema
User
Database
Account
Tables
Network policies in Snowflake can be set at the user level and at the account level2.
Which Snowflake SQL statement would be used to determine which users and roles have access to a role called MY_ROLE?
SHOW GRANTS OF ROLE MY_ROLE
SHOW GRANTS TO ROLE MY_ROLE
SHOW GRANTS FOR ROLE MY_ROLE
SHOW GRANTS ON ROLE MY_ROLE
The SQL statement SHOW GRANTS TO ROLE MY_ROLE is used to determine which users and roles have access to a role called MY_ROLE. This statement lists all the privileges granted to the role, including which roles and users can assume MY_ROLE. References: [COF-C02] SnowPro Core Certification Exam Study Guide
Which of the following features, associated with Continuous Data Protection (CDP), require additional Snowflake-provided data storage? (Choose two.)
Tri-Secret Secure
Time Travel
Fail-safe
Data encryption
External stages
The features associated with Continuous Data Protection (CDP) that require additional Snowflake-provided data storage are Time Travel and Fail-safe. Time Travel allows users to access historical data within a defined period, while Fail-safe provides an additional layer of data protection beyond the Time Travel period. References: [COF-C02] SnowPro Core Certification Exam Study Guide
A company needs to allow some users to see Personally Identifiable Information (PII) while limiting other users from seeing the full value of the PII.
Which Snowflake feature will support this?
Row access policies
Data masking policies
Data encryption
Role based access control
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.
What COPY INTO SQL command should be used to unload data into multiple files?
SINGLE=TRUE
MULTIPLE=TRUE
MULTIPLE=FALSE
SINGLE=FALSE
The COPY INTO SQL command with the option SINGLE=FALSE is used to unload data into multiple files. This option allows the data to be split into multiple files during the unload process. References: SnowPro Core Certification COPY INTO SQL command unload multiple files
Which of the following statements describe features of Snowflake data caching? (Choose two.)
When a virtual warehouse is suspended, the data cache is saved on the remote storage layer.
When the data cache is full, the least-recently used data will be cleared to make room.
A user can only access their own queries from the query result cache.
A user must set USE_METADATA_CACHE to TRUE to use the metadata cache in queries.
The RESULT_SCAN table function can access and filter the contents of the query result cache.
Snowflake’s data caching features include the ability to clear the least-recently used data when the data cache is full to make room for new data. Additionally, the RESULT_SCAN table function can access and filter the contents of the query result cache, allowing users to retrieve and work with the results of previous queries. The other statements are incorrect: the data cache is not saved on the remote storage layer when a virtual warehouse is suspended, users can access queries from the query result cache that were run by other users, and there is no setting called USE_METADATA_CACHE in Snowflake. References: Caching in the Snowflake Cloud Data Platform, Optimizing the warehouse cache
What is the only supported character set for loading and unloading data from all supported file formats?
UTF-8
UTF-16
ISO-8859-1
WINDOWS-1253
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.
References:
Snowflake Documentation: Data Loading and Unloading
Which command should be used to download files from a Snowflake stage to a local folder on a client's machine?
PUT
GET
COPY
SELECT
The GET command is used to download files from a Snowflake stage to a local folder on a client’s machine2.
A Snowflake Administrator needs to ensure that sensitive corporate data in Snowflake tables is not visible to end users, but is partially visible to functional managers.
How can this requirement be met?
Use data encryption.
Use dynamic data masking.
Use secure materialized views.
Revoke all roles for functional managers and end users.
Dynamic data masking is a feature in Snowflake that allows administrators to define masking policies to protect sensitive data. It enables partial visibility of the data to certain roles, such as functional managers, while hiding it from others, like end users
Why would a Snowflake user decide to use a materialized view instead of a regular view?
The base tables do not change frequently.
The results of the view change often.
The query is not resource intensive.
The query results are not used frequently.
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.
References:
Snowflake Documentation: Materialized Views
Which command sets the Virtual Warehouse for a session?
COPY WAREHOUSE FROM <
SET WAREHOUSE = <
USE WAREHOUSE <
USE VIRTUAL_WAREHOUSE <
The command USE WAREHOUSE <
Which of the following are best practices for loading data into Snowflake? (Choose three.)
Aim to produce data files that are between 100 MB and 250 MB in size, compressed.
Load data from files in a cloud storage service in a different region or cloud platform from the service or region containing the Snowflake account, to save on cost.
Enclose fields that contain delimiter characters in single or double quotes.
Split large files into a greater number of smaller files to distribute the load among the compute resources in an active warehouse.
When planning which warehouse(s) to use for data loading, start with the largest warehouse possible.
Partition the staged data into large folders with random paths, allowing Snowflake to determine the best way to load each file.
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.
In the Snowflake access control model, which entity owns an object by default?
The user who created the object
The SYSADMIN role
Ownership depends on the type of object
The role used to create the object
In Snowflake’s access control model, the default owner of an object is the role that was used to create the object. This role has the OWNERSHIP privilege on the object and can grant access to other roles1
In a Snowflake role hierarchy, what is the top-level role?
SYSADMIN
ORGADMIN
ACCOUNTADMIN
SECURITYADMIN
In a Snowflake role hierarchy, the top-level role is ACCOUNTADMIN. This role has the highest level of privileges and is capable of performing all administrative functions within the Snowflake account
A virtual warehouse is created using the following command:
Create warehouse my_WH with
warehouse_size = MEDIUM
min_cluster_count = 1
max_cluster_count = 1
auto_suspend = 60
auto_resume = true;
The image below is a graphical representation of the warehouse utilization across two days.
What action should be taken to address this situation?
Increase the warehouse size from Medium to 2XL.
Increase the value for the parameter MAX_CONCURRENCY_LEVEL.
Configure the warehouse to a multi-cluster warehouse.
Lower the value of the parameter STATEMENT_QUEUED_TIMEOUT_IN_SECONDS.
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
When cloning a database, what is cloned with the database? (Choose two.)
Privileges on the database
Existing child objects within the database
Future child objects within the database
Privileges on the schemas within the database
Only schemas and tables within the database
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-C02] SnowPro Core Certification Exam Study Guide, Snowflake Documentation
Which snowflake objects will incur both storage and cloud compute charges? (Select TWO)
Materialized view
Sequence
Secure view
Transient table
Clustered table
In Snowflake, both materialized views and transient tables will incur storage charges because they store data. They will also incur compute charges when queries are run against them, as compute resources are used to process the queries. References: [COF-C02] SnowPro Core Certification Exam Study Guide
Which Snowflake feature allows a user to substitute a randomly generated identifier for sensitive data, in order to prevent unauthorized users access to the data, before loading it into Snowflake?
External Tokenization
External Tables
Materialized Views
User-Defined Table Functions (UDTF)
The feature in Snowflake that allows a user to substitute a randomly generated identifier for sensitive data before loading it into Snowflake is known as External Tokenization. This process helps to secure sensitive data by ensuring that it is not exposed in its original form, thus preventing unauthorized access3.
Which services does the Snowflake Cloud Services layer manage? (Choose two.)
Compute resources
Query execution
Authentication
Data storage
Metadata
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.
Which methods can be used to delete staged files from a Snowflake stage? (Choose two.)
Use the DROP
Specify the TEMPORARY option when creating the file format.
Specify the PURGE copy option in the COPY INTO