dumpstool logo
If a virtual warehouse runs for 61 seconds, shut down, and then restart and runs for 30 seconds, for how many seconds is it billed?
60
91
120
121
Snowflake bills virtual warehouse usage in one-minute increments, rounding up to the nearest minute for any partial minute of compute time used. If a virtual warehouse runs for 61 seconds and then, after being shut down, restarts and runs for an additional 30 seconds, the total time billed would be 120 seconds or 2 minutes. The first 61 seconds are rounded up to 2 minutes, and the subsequent 30 seconds are within a new minute, which is also rounded up to the nearest minute.
References:
Which use case does the search optimization service support?
Disjuncts (OR) in join predicates
LIKE/ILIKE/RLIKE join predicates
Join predicates on VARIANT columns
Conjunctions (AND) of multiple equality predicates
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.
References:
What are characteristics of reader accounts in Snowflake? (Select TWO).
Reader account users cannot add new data to the account.
Reader account users can share data to other reader accounts.
A single reader account can consume data from multiple provider accounts.
Data consumers are responsible for reader account setup and data usage costs.
Reader accounts enable data consumers to access and query data shared by the provider.
Characteristics of reader accounts in Snowflake include:
References:
Which command should be used to unload all the rows from a table into one or more files in a named stage?
COPY INTO
GET
INSERT INTO
PUT
To unload data from a table into one or more files in a named stage, the COPY 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. The COPY INTO command is versatile, supporting various file formats and compression options for efficient data unloading.
References:
What are characteristics of transient tables in Snowflake? (Select TWO).
Transient tables have a Fail-safe period of 7 days.
Transient tables can be cloned to permanent tables.
Transient tables persist until they are explicitly dropped.
Transient tables can be altered to make them permanent tables.
Transient tables have Time Travel retention periods of 0 or 1 day.
Transient tables in Snowflake are designed for temporary or intermediate workloads with the following characteristics:
References:
A user wants to add additional privileges to the system-defined roles for their virtual warehouse. How does Snowflake recommend they accomplish this?
Grant the additional privileges to a custom role.
Grant the additional privileges to the ACCOUNTADMIN role.
Grant the additional privileges to the SYSADMIN role.
Grant the additional privileges to the ORGADMIN role.
Snowflake recommends enhancing the granularity and management of privileges by creating and utilizing custom roles. When additional privileges are needed beyond those provided by the system-defined roles for a virtual warehouse or any other resource, these privileges should be granted to a custom role. This approach allows for more precise control over access rights and the ability to tailor permissions to the specific needs of different user groups or applications within the organization, while also maintaining the integrity and security model of system-defined roles.
References:
How does a Snowflake stored procedure compare to a User-Defined Function (UDF)?
A single executable statement can call only two stored procedures. In contrast, a single SQL statement can call multiple UDFs.
A single executable statement can call only one stored procedure. In contrast, a single SQL statement can call multiple UDFs.
A single executable statement can call multiple stored procedures. In contrast, multiple SQL statements can call the same UDFs.
Multiple executable statements can call more than one stored procedure. In contrast, a single SQL statement can call multiple UDFs.
In Snowflake, stored procedures and User-Defined Functions (UDFs) have different invocation patterns within SQL:
How long is a query visible in the Query History page in the Snowflake Web Interface (Ul)?
60 minutes
24 hours
14 days
30 days
In the Snowflake Web Interface (UI), the Query History page displays the history of queries executed in Snowflake for up to 14 days. This allows users to review and analyze their query performance, troubleshoot issues, and understand their query patterns over a two-week period. The Query History page is a critical tool for monitoring and optimizing the use of Snowflake.
References:
Which common query problems are identified by the Query Profile? (Select TWO.)
Syntax error
Inefficient pruning
Ambiguous column names
Queries too large to fit in memory
Object does not exist or not authorized
The Query Profile in Snowflake can identify common query problems, including:
The Query Profile helps diagnose these issues by providing detailed execution statistics and visualizations, aiding in query optimization and troubleshooting.
References:
Top of Form
Which statement accurately describes Snowflake's architecture?
It uses a local data repository for all compute nodes in the platform.
It is a blend of shared-disk and shared-everything database architectures.
It is a hybrid of traditional shared-disk and shared-nothing database architectures.
It reorganizes loaded data into internal optimized, compressed, and row-based format.
Snowflake's architecture is unique in that it combines elements of both traditional shared-disk and shared-nothing database architectures. This hybrid approach allows Snowflake to offer the scalability and performance benefits of a shared-nothing architecture (with compute and storage separated) while maintaining the simplicity and flexibility of a shared-disk architecture in managing data across all nodes in the system. This results in an architecture that provides on-demand scalability, both vertically and horizontally, without sacrificing performance or data cohesion.
References:
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:
Which role has the ability to create a share from a shared database by default?
ACCOUNTADMIN
SECURITYADMIN
SYSADMIN
ORGADMIN
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.
References:
What is the Fail-safe period for a transient table in the Snowflake Enterprise edition and higher?
0 days
1 day
7 days
14 days
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 case of 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.
References:
What information does the Query Profile provide?
Graphical representation of the data model
Statistics for each component of the processing plan
Detailed Information about I he database schema
Real-time monitoring of the database operations
The Query Profile in Snowflake provides a graphical representation and statistics for each component of the query's execution plan. This includes details such as the execution time, the number of rows processed, and the amount of data scanned for each operation within the query. The Query Profile is a crucial tool for understanding and optimizing the performance of queries, as it helps identify potential bottlenecks and inefficiencies.
References:
While clustering a table, columns with which data types can be used as clustering keys? (Select TWO).
BINARY
GEOGRAPHY
GEOMETRY
OBJECT
VARIANT
A clustering key can be defined when a table is created by appending a CLUSTER Where each clustering key consists of one or more table columns/expressions, which can be of any data type, except GEOGRAPHY, VARIANT, OBJECT, or ARRAY https://docs.snowflake.com/en/user-guide/tables-clustering-keys
What criteria does Snowflake use to determine the current role when initiating a session? (Select TWO).
If a role was specified as part of the connection and that role has been granted to the Snowflake user, the specified role becomes the current role.
If no role was specified as part of the connection and a default role has been defined for the Snowflake user, that role becomes the current role.
If no role was specified as part of the connection and a default role has not been set for the Snowflake user, the session will not be initiated and the log in will fail.
If a role was specified as part of the connection and that role has not been granted to the Snowflake user, it will be ignored and the default role will become the current role.
If a role was specified as part of the connection and that role has not been granted to the Snowflake user, the role is automatically granted and it becomes the current role.
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.
References:
For Directory tables, what stage allows for automatic refreshing of metadata?
User stage
Table stage
Named internal stage
Named external stage
For directory tables, a named external stage allows for the automatic refreshing of metadata. This capability is particularly useful when dealing with files stored on external storage services (like Amazon S3, Google Cloud Storage, or Azure Blob Storage) and accessed through Snowflake. The external stage references these files, and the directory table's metadata can be automatically updated to reflect changes in the underlying files.
References:
How does a Snowflake user extract the URL of a directory table on an external stage for further transformation?
Use the SHOW STAGES command.
Use the DESCRIBE STAGE command.
Use the GET_ABSOLUTE_PATH function.
Use the GET_STAGE_LOCATION function.
To extract the URL of a directory table on an external stage for further transformation in Snowflake, the GET_ABSOLUTE_PATH function can be used. This function returns the full path of a file or directory within a specified stage, enabling users to dynamically construct URLs for accessing or processing data stored in external stages.
References:
How can a user get the MOST detailed information about individual table storage details in Snowflake?
SHOW TABLES command
SHOW EXTERNAL TABLES command
TABLES view
TABLE STORAGE METRICS view
To obtain the most detailed information about individual table storage details in Snowflake, the TABLE STORAGE METRICS view is the recommended option. This view provides comprehensive metrics on storage usage, including data size, time travel size, fail-safe size, and other relevant storage metrics for each table. This level of detail is invaluable for monitoring, managing, and optimizing storage costs and performance.
References:
What activities can a user with the ORGADMIN role perform? (Select TWO).
Create an account for an organization.
Edit the account data for an organization.
Delete the account data for an organization.
View usage information for all accounts in an organization.
Select all the data in tables for all accounts in an organization.
The ORGADMIN role in Snowflake is an organizational-level role that provides administrative capabilities across the entire organization, rather than being limited to a single Snowflake account. Users with this role can:
References:
Which Snowflake data type is used to store JSON key value pairs?
TEXT
BINARY
STRING
VARIANT
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.
References:
What computer language can be selected when creating User-Defined Functions (UDFs) using the Snowpark API?
Swift
JavaScript
Python
SQL
The Snowpark API allows developers to create User-Defined Functions (UDFs) in various languages, including Python, which is known for its ease of use and wide adoption in data-related tasks. References: Based on general programming and cloud data service knowledge as of 2021.
What statistical information in a Query Profile indicates that the query is too large to fit in memory? (Select TWO).
Bytes spilled to local cache.
Bytes spilled to local storage.
Bytes spilled to remote cache.
Bytes spilled to remote storage.
Bytes spilled to remote metastore.
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 that the working data set of the query exceeded the memory available on the warehouse nodes, causing intermediate results to be written to disk
What is used to diagnose and troubleshoot network connections to Snowflake?
SnowCD
Snowpark
Snowsight
SnowSQL
SnowCD (Snowflake Connectivity Diagnostic Tool) is used to diagnose and troubleshoot network connections to Snowflake. It runs a series of connection checks to evaluate the network connection to Snowflake
How can a user improve the performance of a single large complex query in Snowflake?
Scale up the virtual warehouse.
Scale out the virtual warehouse.
Enable standard warehouse scaling.
Enable economy warehouse scaling.
Scaling up the virtual warehouse in Snowflake involves increasing the compute resources available for a single warehouse, which can improve the performance of large and complex queries by providing more CPU and memory resources. References: Based on general cloud data warehousing knowledge as of 2021.
Using variables in Snowflake is denoted by using which SQL character?
@
&
$
#
∗∗VeryComprehensiveExplanation=InSnowflake,variablesaredenotedbyadollarsign(). Variables can be used in SQL statements where a literal constant is allowed, and they must be prefixed with a $ sign to distinguish them from bind values and column names.
What is cached during a query on a virtual warehouse?
All columns in a micro-partition
Any columns accessed during the query
The columns in the result set of the query
All rows accessed during the query
During a query on a virtual warehouse, the columns in the result set of the query are cached. This allows for faster retrieval of data if the same or a similar query is run again, as the system can retrieve the data from the cache rather than reprocessing the entire query. References: [COF-C02] SnowPro Core Certification Exam Study Guide
Which Snowflake URL type allows users or applications to download or access files directly from Snowflake stage without authentication?
Directory
File
Pre-signed
Scoped
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.
At what levels can a resource monitor be configured? (Select TWO).
Account
Database
Organization
Schema
Virtual warehouse
Resource monitors in Snowflake can be configured at the account and virtual warehouse levels. They are used to track credit usage and control costs associated with running virtual warehouses. When certain thresholds are reached, resource monitors can trigger actions such as sending alerts or suspending warehouses to prevent excessive credit consumption. References: [COF-C02] SnowPro Core Certification Exam Study Guide
Where is Snowflake metadata stored?
Within the data files
In the virtual warehouse layer
In the cloud services layer
In the remote storage layer
Snowflake’s architecture is divided into three layers: database storage, query processing, and cloud services. The metadata, which includes information about the structure of the data, the SQL operations performed, and the service-level policies, is stored in the cloud services layer. This layer acts as the brain of the Snowflake environment, managing metadata, query optimization, and transaction coordination.
Which Snowflake object can be accessed in he FROM clause of a query, returning a set of rows having one or more columns?
A User-Defined Table Function (UDTF)
A Scalar User Function (UDF)
A stored procedure
A task
In Snowflake, a User-Defined Table Function (UDTF) can be accessed in the FROM clause of a query. UDTFs return a set of rows with one or more columns, which can be queried like a regular table
Which parameter prevents streams on tables from becoming stale?
MAXDATAEXTENSIONTIMEINDAYS
MTN_DATA_RETENTION_TTME_TN_DAYS
LOCK_TIMEOUT
STALE_AFTER
The parameter that prevents streams on tables from becoming stale is MAXDATAEXTENSIONTIMEINDAYS. This parameter specifies the maximum number of days for which Snowflake can extend the data retention period for the table to prevent streams on the table from becoming stale4.
In which Snowflake layer does Snowflake reorganize data into its internal optimized, compressed, columnar format?
Cloud Services
Database Storage
Query Processing
Metadata Management
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
Which native data types are used for storing semi-structured data in Snowflake? (Select TWO)
NUMBER
OBJECT
STRING
VARCHAR
VARIANT
Snowflake supports semi-structured data types, which include OBJECT and VARIANT. These data types are capable of storing JSON-like data structures, allowing for flexibility in data representation. OBJECT can directly contain VARIANT, and thus indirectly contain any other data type, including itself1.
Which database objects can be shared with the Snowflake secure data sharing feature? (Choose two.)
Files
External tables
Secure User-Defined Functions (UDFs)
Sequences
Streams
Snowflake’s secure data sharing feature allows sharing of certain database objects with other Snowflake accounts. Among the options provided, external tables and secure UDFs can be shared
A tabular User-Defined Function (UDF) is defined by specifying a return clause that contains which keyword?
ROW_NUMBER
TABLE
TABULAR
VALUES
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.
User INQUISITIVE_PERSON has been granted the role DATA_SCIENCE. The role DATA_SCIENCE has privileges OWNERSHIP on the schema MARKETING of the database ANALYTICS_DW.
Which command will show all privileges granted to that schema?
SHOW GRANTS ON ROLE DATA_SCIENCE
SHOW GRANTS ON SCHEMA ANALYTICS_DW.MARKETING
SHOW GRANTS TO USER INQUISITIVE_PERSON
SHOW GRANTS OF ROLE DATA_SCIENCE
To show all privileges granted to a specific schema, the command SHOW GRANTS ON SCHEMA
Which feature is integrated to support Multi-Factor Authentication (MFA) at Snowflake?
Authy
Duo Security
One Login
RSA SecurlD Access
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.
What are advantages clones have over tables created with CREATE TABLE AS SELECT statement? (Choose two.)
The clone always stays in sync with the original table.
The clone has better query performance.
The clone is created almost instantly.
The clone will have time travel history from the original table.
The clone saves space by not duplicating storage.
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
Credit charges for Snowflake virtual warehouses are calculated based on which of the following considerations? (Choose two.)
The number of queries executed
The number of active users assigned to the warehouse
The size of the virtual warehouse
The length of time the warehouse is running
The duration of the queries that are executed
Credit charges for Snowflake virtual warehouses are calculated based on the size of the virtual warehouse and the length of time the warehouse is running. The size determines the compute resources available, and charges are incurred for the time these resources are utilized
How many resource monitors can be assigned at the account level?
1
2
3
4
Snowflake allows for only one resource monitor to be assigned at the account level. This monitor oversees the credit usage of all the warehouses in the account. References: Snowflake Documentation
Which stages are used with the Snowflake PUT command to upload files from a local file system? (Choose three.)
Schema Stage
User Stage
Database Stage
Table Stage
External Named Stage
Internal Named Stage
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
Which Snowflake objects can be shared with other Snowflake accounts? (Choose three.)
Schemas
Roles
Secure Views
Stored Procedures
Tables
Secure User-Defined Functions (UDFs)
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.
What is the name of the SnowSQLfile that can store connection information?
history
config
snowsqLcnf
snowsql.pubkey
The SnowSQL file that can store connection information is named ‘config’. It is used to store user credentials and connection details for easy access to Snowflake instances. References: Based on general database knowledge as of 2021.
Which feature allows a user the ability to control the organization of data in a micro-partition?
Range Partitioning
Search Optimization Service
Automatic Clustering
Horizontal Partitioning
Automatic Clustering is a feature that allows users to control the organization of data within micro-partitions in Snowflake. By defining clustering keys, Snowflake can automatically reorganize the data in micro-partitions to optimize query performance1.
Which URL type allows users to access unstructured data without authenticating into Snowflake or passing an authorization token?
Pre-signed URL
Scoped URL
Signed URL
File URL
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 or downloaded by any user or application, making them ideal for business intelligence applications or reporting tools that need to display unstructured file contents
For non-materialized views, what column in Information Schema and Account Usage identifies whether a view is secure or not?
CHECK_OPTION
IS_SECURE
IS_UPDATEABLE
TABLE_NAME
In the Information Schema and Account Usage, the column that identifies whether a view is secure or not is IS_SECURE2.
How can a data provider ensure that a data consumer is going to have access to the required objects?
Enable the data sharing feature in the account and validate the view.
Use the CURRENT_ROLE and CURRENT_USER functions to validate secure views.
Use the CURRENT_ function to authorize users from a specific account to access rows in a base table.
Set the SIMULATED DATA SHARING CONSUMER session parameter to the name of the consumer account for which access is being simulated.
To ensure a data consumer has access to the required objects, a data provider can enable the data sharing feature and validate that the consumer can access the views or tables shared with them. References: Based on general data sharing practices in cloud services as of 2021.
Which stream type can be used for tracking the records in external tables?
Append-only
External
Insert-only
Standard
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
What privilege should a user be granted to change permissions for new objects in a managed access schema?
Grant the OWNERSHIP privilege on the schema.
Grant the OWNERSHIP privilege on the database.
Grant the MANAGE GRANTS global privilege.
Grant ALL privileges on the schema.
To change permissions for new objects in a managed access schema, a user should be granted the MANAGE GRANTS global privilege. This privilege allows the user to manage access control through grants on all securable objects within Snowflake2. References: [COF-C02] SnowPro Core Certification Exam Study Guide
What happens to the shared objects for users in a consumer account from a share, once a database has been created in that account?
The shared objects are transferred.
The shared objects are copied.
The shared objects become accessible.
The shared objects can be re-shared.
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
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 can a Snowflake user do in the Activity section in Snowsight?
Create dashboards.
Write and run SQL queries.
Explore databases and objects.
Explore executed query performance.
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.
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
