Mock Exam 1

Question 1

Which of the following privileges allows a user in a consumer account to create a database from a share? Select two.
- IMPORT SHARE priv
- SYSADMIN role
- ACCOUNTADMIN role
- SECURITY ADMIN role

Answer 1

A user in a consumer account can create a database from the Share if they have the ACCOUNTADMIN role OR the IMPORT SHARE privileges https://docs.snowflake.com/en/user-guide/data-share-consumers

Data Sharing

Question 2

An ACCOUNTADMIN can see the results of any query executed by any user in a Snowflake system.

Answer 2

False
You can only view results for queries you have personally executed. For example, as an administrator, If you have permission to view queries run by another user, the Query Detail page displays the query’s details but not the actual query result for data privacy reasons.

Question 3

Can Named Internal Stage be cloned?

Answer 3

Named Internal Stages cannot be cloned. When a database or schema is cloned, any Snowpipe that points to a Named Internal Stage is not cloned. Named External Stages can be cloned. Since a table stage is associated with a table, it is automatically cloned when the table is cloned. Additionally, external tables cannot be cloned either. Databases, Schema, Tables, etc., can be cloned. https://docs.snowflake.com/en/user-guide/object-clone#cloning-and-stages

Question 4

A data consumer has created a read-only database on a Share object shared by a data provider. The data provider adds an object to the Share. The data consumer needs to REFRESH. the SHARE?

Answer 4

No.
All new objects added to a share object by the data provider automatically become accessible to the consumer
https://docs.snowflake.com/en/user-guide/data-sharing-intro#what-is-a-share

Question 5

In Snowsight (Snowflake web user interface), you can execute only one query at a given time.

Answer 5

False
Multiple worksheets can be opened in Snowsight, each with a different query. The queries continue to execute even if the worksheets are inactive; thus, multiple queries can be executed simultaneously. https://docs.snowflake.com/en/user-guide/ui-snowsight

Question 6

Which Snowflake editions support database failover and failback between Snowflake accounts, thus providing business continuity and disaster recovery?

Answer 6

Database failover and failback between Snowflake accounts are provided first in the Business Critical edition and are also available in the virtual private Snowflake (VPS) edition. https://docs.snowflake.com/en/user-guide/intro-editions.html

Question 7

Which of the following strategies should be used to optimize the performance of a virtual warehouse?
- Increase VW size
- Suspend the VW
- Increase disc cache
- configure MAX_CONCURRENCY_LEVEL
- Reduce queuing

Answer 7

The following strategies may be applied to improve the performance of a virtual warehouse.

1. Reduce queuing
2. Resolve memory spillage.
3. Increase warehouse size.
4. Try query acceleration.
5. Optimize the warehouse cache.
6. Limit concurrently running queries.

https://docs.snowflake.com/en/user-guide/performance-query-warehouse

Question 8

True/False: It is possible to disable failsafe entirely for a Snowflake account.

Answer 8

Once the Time Travel period ends, Snowflake keeps the data for a further 7-day period as further protection. This fail-safe can not be disabled or configured. You can NOT change it for a Snowflake account, database, schema, or table.

Question 9

Concurrent queries is suitable for scaling up a virtual warehouse to a larger size?

Answer 9

Based on the complexity of the queries and the desired performance, a virtual warehouse can be scaled up or down. In general, increasing the virtual warehouse size improves query speed for CPU-intensive queries. On the other hand, scaling up is ineffective when dealing with a high number of concurrent users or queries. Instead, a multi-cluster virtual warehouse (scaling out) is utilized to accommodate an increased number of users and queries
https://docs.snowflake.com/en/user-guide/warehouses-considerations

Question 10

True or False: A reader account can be used to share data with a non-Snowflake user or a non-Snowflake organization.

Answer 10

Sharing data with a non-Snowflake user or organization is possible by creating a reader account. This reader account is created by the data provider solely for sharing purposes.
https://docs.snowflake.com/en/user-guide/data-sharing-reader-create

Question 11

True or False: Files already loaded from a stage to a table can be loaded again into a cloned table.

Answer 11

True
Cloning doesn’t copy the load metadata of a cloned table. Therefore, the load metadata for a cloned table would be empty. Thus, files already loaded for the source table can be loaded again into the cloned table.

Question 12

What is not supported by Search Optimization?

Answer 12

Search optimization does not support

External tables.

Materialized views.

Columns defined with a COLLATE clause.

Column concatenation.

Analytical expressions.

Casts on table columns (except for fixed-point numbers cast to strings).

https://docs.snowflake.com/en/user-guide/search-optimization-service#queries-not-supported-by-the-search-optimization-service

Question 13

How does a secure UDF differ from a different typical UDF?

Answer 13

Specific SQL UDF optimizations may allow data that should be hidden from users to be accessed indirectly via different techniques. Secure UDFs do not use these SQL optimizations, ensuring that users have no access to the underlying data, even indirectly. Furthermore, Secure UDFs allow only authorized users to see the definition and information of secure UDFs (i.e., users who are granted the role that owns the UDF).

https://docs.snowflake.com/en/developer-guide/secure-udf-procedure

Question 14

Is data encrypted automatically at the client machine before being transmitted to the Snowflake internal stage

Answer 14

Data is encrypted automatically at the client machine before being transmitted to the Snowflake internal stage. Once the data is in an internal stage, it is stored encrypted. This is part of the end-to-end encryption managed by Snowflake.

https://docs.snowflake.com/en/user-guide/security-encryption-end-to-end

Question 15

Dynamic Data Masking provides what sort of security in Snowflake?

Answer 15

Snowflake supports masking policies that may be applied to columns and enforced at the column level to provide column-level security. Column-level security is achieved by dynamic data masking or external Tokenization.

https://docs.snowflake.com/en/user-guide/security-column

Question 16

Which role must be granted to a Snowflake user to allow them to create new Snowflake accounts?

Answer 16

The ORGADMIN role performs organization-specific tasks like listing all accounts and creating new ones.

https://docs.snowflake.com/en/user-guide/organizations-gs#enabling-the-orgadmin-role-in-an-account

Question 17

Does Materialized views need to be refreshed manually?

Answer 17

A materialized view is a view that pre-computes data based on a SELECT query. The query’s results are pre-computed and physically stored to enhance performance for similar queries that are executed in the future. When the underlying table is updated, the materialized view refreshes automatically, requiring no additional maintenance. Snowflake-managed services perform the update in the background transparent to the user without interfering with the user’s experience.

https://docs.snowflake.com/en/user-guide/views-materialized

Question 18

For how long a query remains visible on the query history page in the Snowsight interface?

Answer 18

The query history page can show the history of queries executed in the last 14 days.

Question 19

True or False?
Snowflake uses AES - 128-bit encryption to encrypt data at rest.

Answer 19

False
Snowflake-managed keys are rotated regularly (at 30-day intervals), and an annual rekeying process re-encrypts data with new keys. The data encryption and key management processes are entirely transparent to the users. Snowflake uses AES 256-bit encryption to encrypt data at rest.

Question 20

True or False?
A user with the SYSADMIN role can create a new virtual warehouse.

Answer 20

True
The SYSADMIN role can create and manage most Snowflake objects, including databases, tables, views, virtual warehouses, etc. However, the SYSADMIN role does not have the privileges to create new users or roles.

https://docs.snowflake.com/en/user-guide/security-access-control-overview#system-defined-roles.

Question 21

True or False
Streams can be used with directory tables.

Answer 21

Streams can be used with directory tables to easily track which files have been added, removed, or changed. This is done by creating a stream on top of the stage object.

https://docs.snowflake.com/en/user-guide/data-load-dirtables-manage#streams-on-directory-tables

Question 22

True or False: The functions provided in INFORMATION_SCHEMA can be used to view account-level information.

Answer 22

True
The INFORMATION_SCHEMA provides data on the objects in the parent database of the INFORMATION_SCHEMA. It also provides data on account-level objects such as roles, warehouses, and databases.

https://docs.snowflake.com/en/sql-reference/info-schema#information-schema-views-and-table-functions

Question 23

True or False
Snowflake billing is based on the amount of data processed by queries

Answer 23

False
Snowflake doesn’t charge on how much data a query processed.

https://docs.snowflake.com/en/user-guide/cost-understanding-overall#how-are-costs-incurred

Question 24

True or False
If a virtual warehouse is suspended, it does not contribute to the cost.

Answer 24

True

Virtual warehouses in a resumed (active) state contribute to the costs. However, it does not matter if the virtual warehouse is not running a query; if it is resumed, it contributes to the costs.

https://docs.snowflake.com/en/user-guide/cost-understanding-compute Snowflake

Question 25

Which file function allows any user or application access to download unstructured data in a Snowflake stage?

Answer 25

GET_PRESIGNED_URL

A pre-signed URL is a simple HTTPS URL for accessing a file using a web browser. A pre-signed URL is generated using a pre-signed access token. Users can temporarily access a file via a pre-signed URL without authorization. The expiry duration of a pre-signed URL is configurable and can be set to the required duration.

https://docs.snowflake.com/en/user-guide/unstructured-intro#types-of-urls-available-to-access-files

Question 26

Snowflake recommends that any custom roles should be assigned to the pre-defined ____________ role.

Answer 26

SYSADMIN
Snowflake recommends establishing a hierarchy of custom roles, with the top custom role given to the pre-defined system role SYSADMIN. SYSADMIN can act as the owner of all securable objects in the system and can manage these objects.

https://docs.snowflake.com/en/user-guide/security-access-control-overview#custom-roles

Question 27

The usage data provided through the INFORMATION SCHEMA has a retention of how many days?

Answer 27

7 days - 6 months

The data in the INFORMATION_SCHEMA views is retained for a shorter period. Typical data retention in INFORMATION SCHEMA is 14 days but can be seven days for specific views and up to 6 months for usage history views. Thus, these views have retention ranging from 7 days to a maximum of 6 months, depending on the view. So typically, the views in the INFORMATION SCHEMA can be used to find more recent information.

https://docs.snowflake.com/en/sql-reference/account-usage#differences-between-account-usage-and-information-schema

Question 28

Which are caching mechanisms in Snowflake?

Answer 28

Query Result Caching
Warehouse Caching
Metadata Caching

Metadata caching is used for queries that can be fulfilled directly from metadata, e.g., the row count of a table Query Result Caching is for queries that have been executed already. Warehouse caching is within the virtual warehouse instance and is usually based on queries that have already been executed.

Question 29

True/False: Snowflake customers can control the format using which Snowflake stores the data for a table.

Answer 29

False

Snowflake stores data in a proprietary format on cloud object storage, such as AWS S3, Azure Blob Storage, or Google Cloud Storage. Users cannot see the actual files, look at how the data is stored, or access the file directly. Users can not change how Snowflake stores the data behind the scenes.

Question 30

Which Snowflake Editions support Time Travel?

Answer 30

Time Travel is supported in all Snowflake editions.

https://docs.snowflake.com/en/user-guide/data-time-travel#data-retention-period

Question 31

True or False
Privileges can NOT be assigned directly to a directory table.

Answer 31

TRUE
A directory table is not a separate database object but is an implicit object available with a stage. You can enable the directory table for a stage while creating the stage or enable it afterward.

Since Directory Tables are not separate objects, you cannot provide privileges to them.

https://docs.snowflake.com/en/user-guide/data-load-dirtables

Question 32

True or False
Failsafe is provided as an alternate means to access historical data once the Time Travel retention period has ended.

Answer 32

FALSE
After the Time Travel period has been completed, the fail-safe storage feature stores data for an additional period of seven days. The fail-safe storage offers additional security against data loss; however, only the Snowflake support team can restore data from the fail-safe storage. Fail-safe cannot be used to access historical data but is used to recover from accidental data loss.

https://docs.snowflake.com/en/user-guide/data-failsafe

Question 33

How frequently does Snowflake release new software?

Answer 33

Weekly

Question 34

JOIN is NOT supported by the COPY command?

Answer 34

True

While loading the data, complex transformations such as joins, filters, aggregations, and the use of FLATTEN are not supported as they are not essential data transformations. Therefore, joining, filtering, and aggregating the data are supported ONLY after the data has been loaded into a table. https://docs.snowflake.com/en/user-guide/data-load-overview#id2

Question 35

True or False: The ACCOUNT_USAGE views contain information on objects that have been deleted.

Answer 35

True

ACCOUNT_USAGE views include information for all dropped objects. Many of these views include a DELETED column showing the dropped object’s information. INFORMATION_SCHEMA does not include dropped objects.

Question 36

True or False
The query processing layer can run multiple compute clusters (virtual warehouses) simultaneously.

Answer 36

TRUE

The QUERY PROCESSING layer is the compute layer through which queries and data processing jobs are executed on the stored data. The compute layer can have multiple clusters for a given Snowflake instance simultaneously. The compute engines in Snowflake are known as virtual warehouses.

On the other side, The CLOUD SERVICES layer performs the query plans and optimization.

Question 37

Query Result Cache can be turned off at which levels?

Answer 37

Query result cache is enabled by default but can be turned off at a SESSION, USER, or ACCOUNT level using the USE_CACHED_RESULT parameter.

https://docs.snowflake.com/en/user-guide/querying-persisted-results

Question 38

What is the minimum Snowflake edition that supports a dedicated metadata store?

Answer 38

Virtual Private Snowflake

The VPS edition is meant to provide isolation from other customers; thus, each instance has its own metadata store and compute resources.

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

Question 39

True or False: When unloading data, each exported file is 16MB, and this configuration cannot be changed.

Answer 39

False

The default size of each output file is 16 MB but can be changed using the MAX_FILE_SIZE parameter. The maximum allowed size per file is 5GB if you export data to cloud storage.

https://docs.snowflake.com/en/user-guide/data-unload-considerations#unloading-to-a-single-file

Question 40

What is SCIM?

OKTA

Answer 40

SCIM is an open standard that provides automatic user provisioning and role synchronization based on identity provider information. When a new user is created in the identity provider, the SCIM automatically provisions the user in Snowflake. Additionally, SCIM can sync groups defined in an identity provider with Snowflake roles.

https://docs.snowflake.com/en/user-guide/scim