Summary Snowflake Certification Exam Complete 200+ Q&A 2023 (100% correct).

True or False: Data Storage is independent from compute?
True

True or False: All virtual warehouses have access to all data?
True

True or False: Al interactions with data are initialized through the services layer?
True

True or False: Multi-Cluster Warehouses support high concurrency?
True

True or False: Warehouses can be dynamically expanded to adjust to workloads?
True

Which layer provides centralized services for the data warehouse?
Cloud Services

Which layer contains virtual warehouses for data access?
Query Processing

Which layer contains the data in compressed, columnar format?
Database Storage

True or False: Snowflake’s security and authentication includes Multi-Factor authentication?
True

True or False: Snowflake’s security and authentication includes Snowflake Failures alerts?
False

True or False: Snowflake’s security and authentication includes data encryption using Snowflake-managed keys?
False

True or False: Snowflake’s security and authentication includes object-level access?
True

True or False: Snowflake’s architecture includes advance capabilities in the cloud services layer that delivers transaction management for consistent operations on the same data at the same time?
True

True or False: Snowflake’s architecture includes advance capabilities in the cloud services layer that delivers metadata service?
True

True or False: Snowflake’s architecture includes advance capabilities in the cloud services layer that delivers security and authentication control?
True

True or False: Snowflake’s architecture includes advance capabilities in the cloud services layer that delivers query optimization?
True

True or False: Snowflake supports landing data into internal stage on the cloud storage platform?
True

True or False: Snowflake supports landing data into external stage on the cloud storage platform?
True

True or False: Snowflake supports landing data into bring your own device?
False

True or False: Compute resources used by Snowflake for data loading jobs can by provide by user managed virtual warehouse?
True

True or False: Compute resources used by Snowflake for data loading jobs can by provide by Snowflake managed service?
True

True or False: Compute resources used by Snowflake for data loading jobs can by provide by hardware provisioned by user directly from cloud providers?
False

True or False: Stages are unique database objects in Snowflake?
True

True or False: Pipe are unique database objects in Snowflake?
True

True or False: Table are unique database objects in Snowflake?
False

Which approach would result in improved performance through linear scaling of data ingestion workload:

A) Resize virtual warehouse
B) Consider the practice of organizing data by granular path
C) Consider the practice of splitting input file batch within the recommended range of 10MB and 100MB?
D) All of the above
D) All of the above

True or False: Snowflake Support Services addresses customer issues covering troubleshooting failed queries?
True

True or False: Snowflake Support Services addresses customer issues covering product usage questions?
True

True or False: Snowflake Support Services addresses customer issues covering individual query syntax improvement?
True

True or False: Snowflake Support Services addresses customer issues covering 3rd party application configuration support?
False

Snowflake users with support contracts that have a Severity-1 issue should contact Snowflake in these ways except:

A) Snowflake Lodge – set appropriate severity (1-4)
B) Send email
C) 844-SNOWFLAKE
B) Send email

True or False: The Snowflake Lodge is a community site that has technical information for Support Customers only?
False

True or False: The Snowflake Lodge is a community site that is the recommend place to submit support cases?
True

True or False: The Snowflake Lodge is a community site that contains the most up to date security alerts and product release information?
True

True or False: The Snowflake Lodge is a community site that does not allow members to post questions?
False

Snowflake includes administration settings for resource consumption in order to:

A) Help control costs associated with unexpected credit usage of warehouses
B) Manage access to data warehouse for specified users
C) Maintain availability
A) Help control costs associated with unexpected credit usage of warehouses

True or False: Create Role can be granted within a Snowflake account by the administrator?
True

True or False: Drop Role can be granted within a Snowflake account by the administrator?
True

True or False: Create User permission can be granted within a Snowflake account by the administrator?
True

True or False: Drop User permission can be granted within a Snowflake account by the administrator?
True

True or False: Grant Privilege permission can be granted within a Snowflake account by the administrator?
True

Snowflake provides specific administration features and capabilities to support the following activities except:

A) Managing databases and warehouses within a Snowflake account
B) Managing roles and users within a Snowflake account
C) Monitoring Usage and manage resources to control costs in a Snowflake account
D) Manage 3rd party applications providing data to a Snowflake account
D) Manage 3rd party applications providing data to a Snowflake account

A defined data set is a point in time snapshot and can be updated by customers is?
Snowflake’s Cloning

A defined data set can be shared with customers outside the Snowflake account is?
Snowflakes’s Data Sharing

Query statement encryption is supported on _ accounts?

A) Standard
B) Enterprise
C) Enterprise for Sensitive (ESD)
D) Virtual Private Snowflake (VPS)
C) Enterprise for Sensitive (ESD)

True or False: Caching techniques are supported by Snowflake’s performance optimizing query methods?
True

True or False: B-tree type indexes are supported by Snowflake’s performance optimizing query methods?
False

True or False: Retrieving results of previous query from cache is supported by Snowflake’s performance optimizing query methods?
True

True or False: Snowflake security supports user-based access control?
True

True or False: Federated authentication in Snowflake is complaint with SAML 2.0?
True

True or False: Tri-secret requires that customers manage their own keys?
True

All security information is stored in the _ layer in the Snowflake architecture?
A) Storage
B) Compute
C) Service
D) All of the above
C) Service

True or False: One benefit of client-side encryption is that it provides a secure system for managing data in cloud storage?
True

True or False: One benefit of client-side encryption is that the data is encrypted before loading into storage layer?
False

True or False: One benefit of client-side encryption is the storage service layer only contains encrypted version of the data?
True

True or False: One benefit of client-side encryption is that queries can be encrypted on the client side?
False

True or False: MFA (Multi-factor Authentication) is automatically enabled for your account and available for all users to self-enroll?
True

True or False: MFA (Multi-factor Authentication) is an integrated feature powered by Duo Security service?
True

True or False: MFA (Multi-factor Authentication) can be used for connecting to Snowflake via the Snowflake JDBC driver?
True

True or False: MFA (Multi-factor Authentication) login is desired only for connecting to Snowflake through the web interface?
False

Snowflake includes Role-Based Access Control to enable administrators to:

A) Limit access to data and privileges
B) Manage secure access to the Snowflake account and data
C) Establish role hierarchy and privilege inheritance to align access
D) All of the above
D) All of the above

With an IdP (identity provider) configured for your account, Snowflake supports using SSO to connect and authenticate with ODBC Driver?
True

With an IdP (identity provider) configured for your account, Snowflake supports using SSO to connect and authenticate with Python Connector?
True

With an IdP (identity provider) configured for your account, Snowflake supports using SSO to connect and authenticate with JDBC Driver?
True

With an IdP (identity provider) configured for your account, Snowflake supports using SSO to connect and authenticate with SnowSQL?

Choosing separate accounts in Snowflake enables users to have:

A) Different editions of Snowflake and different regions
B) Billing at the account level
C) Simpler database object deployment between environments
D) All of the above
D) All of the above

True or False: Different editions of Snowflake instances require separate accounts?
True

True or False: Snowflake instances in different regions require separate accounts?
True

True or False: Data can be shared READ ONLY across accounts, and can be cloned accounts?
False

An enterprise view of data is useful because:

A) A data set can be stored once and shared multiple times
B) Data set provisioning is assigned to the owner of the data set
C) Data sets can be shared securely
D) All of the above
D) All of the above

True or False: Data Sharing is only supported between accounts in the same Snowflake region?
True

True or False: A share can’t be cloned by a consumer account, but the share data CAN be copied into a table?
True

True or False: Data can be shared Read-Only across Snowflake accounts and can also be cloned?
False

True or False: There are cases where separate accounts are required such as different editions or regions?
True

Since Snowflake has several advantages over OnPrem data warehouse databases, which of the following list are differentiators for Snowflake:

A) Single place for data, both structured and semi-structured
B) Minimal management
C) Instant and live data sharing
D) Pay a set monthly fee for DWaaS and support
E) Instant and unlimited on-demand scalability in both Storage and Compute
F) All of the above
A, B, C, & E

Select all layers that are part of Snowflake architecture:

A) Security
B) Compute
C) Memory
D) Storage
E) Query Optimization
F) Transaction Management
G) Cloud Services
H) Metadata Management
B, D, & G

Select all characteristics of Snowflake’s Multi-Cluster environment:

A) Multiple virtual warehouses in a deployment
B) User has to specify which cluster each query will utilize
C) Individual warehouses automatically scale up and down base on query activity
D) Multi-cluster warehouses support all the same properties and actions as single-cluster warehouse
E) All of the above
A, C, and D

Select the two types of modes that a Multi-Cluster Warehouses:

A) Statically
B) Dynamically
C) Maximized
D) Auto-Scale
F) None of the above
C & D

To help control the usage of credits in Auto-scale mode, Snowflake provides a property, _____, that determines the scaling policy to use when automatically starting or shutting down additional clusters. Select the property:

A) Auto_Scale
B) Scaling_policy
C) Maximum_number_of_server_clusters
D) Minimum_number of server clusters
E) None of the above
B) Scaling_Policy

Standard Scaling Policy
Definition:
Prevents/minimizes queuing by favoring starting additional clusters over conserving credits.

Cluster Starts….
Immediately when either a query is queued or the system detects that there’s one more query than the currently-running clusters can execute.

Cluster Shuts down….
After 2 to 3 consecutive successful checks (performed at 1 minute intervals), which determine whether the load on the least-loaded cluster could be redistributed to the other clusters without spinning up the cluster again.

Economy Scaling Policy
Definition:
Conserves credits by favoring keeping running clusters fully-loaded rather than starting additional clusters, which may result in queries being queued and taking longer to complete. (default setting)

Cluster Starts….
Only if the system estimates there’s enough query load to keep the cluster busy for at least 6 minutes.

Cluster Shuts down….
After 5 to 6 consecutive successful checks (performed at 1 minute intervals), which determine whether the load on the least-loaded cluster could be redistributed to the other clusters without spinning up the cluster again.

Semi-structured data strings are stored in a column with a Snowflake data type of ______?

A) Object
B) Character
C) Varchar
D) Variant
E) None of the above
D) Variant
Not A) Object – key pair values like Hadoop

When sizing a Snowflake warehouse, which of the following factors should not be considered:

A) Number of users
B) Number of concurrent queries
C) Number of tables being queried
D) Data size and composition
E) All of the above
A) Number of Users – if users are not querying the database, then they don’t take any resources

Snowflake utilizes per ___ billing.

A) Millisecond
B) Second
C) Minute
D) Hour
E) None of the above
B) Second

True or False: Each server in a cluster has a position. Servers are always removed from the warehouse in reverse order of when they were added (aka LIFO, “Last In, First Out”)
True

True or False: The size of the cache is determined by the number of servers in the all of warehouses for an account
False – size of the cache is determined by the number of servers in a warehouse.

Snowflake has three types of caching to optimize performance. Select the three types of caches from the list:

A) Server
B) Warehouse
C) Client
D) Results
E) Security
F) Metadata
G) Database
H) User
B, D, and F

This type of cache lives on the Compute instance?

A) Server
B) Results
C) Metadata
D) Warehouse
D) Warehouse cache

This type of cache lives on the Cloud Services layer?

A) Server
B) Results
C) Metadata
D) Warehouse
C) Metadata

True or False: Warehousing cache stores the results of the queries that have been executed for 24 hours unless the underlying data changes, at which point the entry is invalidated?
False – Results Cache

True or False: Metadata cache is used to optimize queries and improve query compile time?
True

True or False: Warehouse cache may be reset or invalidated if the Virtual Warehouse is suspended and resumed?
True

True or False: Semi-structured data can be queried using SQL while incorporating JSON path notation?
True

True or False: In general, you should try to match the size of the warehouse to the expected size and complexity of the queries to be processed by the warehouse?
True

SQL functionality can be extended via (select all of the appropriate answers):

A) SQL User Functions (UDF)
B) Javascript UDFs
C) Session Variables
D) Only SQL and Javascript UDFs
E) None of
A, B, and C

True or False: A customer needs to have a preexisting account with AWS or MicroSoft Azure in order to have a Snowflake account or to use Snowflake?
False – not a requirement

How many availability zones does Snowflake replicate to for disaster recovery and high availability?

A) Zero
B) One
C) Three
D) As many as the user specifies in the configuration
C) Three – Snowflake automatically does this for each account. It is built-in and fully managed by Snowflake

True or False: Snowflake only replicates Storage layer to the other availability zones within a region?
False – Storage and Cloud Services layers are replicated

True or False: Multi-region accounts are supported by Snowflake?
False – Each Snowflake accounts is located in a single region

Snowflake is currently available on the following Cloud Providers:

A) AWS
B) Google
C) Microsoft’s Azure
G) All of the above
A & C – Google is not support, but is coming in the future

True or False: Snowflake automatically partitions the data so that the user does not need to define partition scheme?
True

Which of the following is not a characteristic of “automatic micro-partitions” in Snowflake?

A) New partitions are created on logical properties
B) Avoids skews between partitions
C) Partitioning is automatically completed on the natural ingestion order of the data
D) The natural ingestion order maintains correlations between columns which could be useful for pruning
A) New partitions are created on logical properties

Snowflake uses physical properties. The partitions can overlap in ranges.

True or False: Snowflake optimizes the storage for semi-structured data based on the repeating elements within the semi-structured strings?
True

True or False: Snowflake store structure and semi-structure data in different proprietary file formats?
False – they use the same proprietary file format

Snowflake has two key features in their storage architecture. They are (select two):

A) Time Travel
B) Time Machine
C) Time Warp
D) Snapshot Cloning
E) Zero-Copy Cloning
F) Replication
A & E

Select the best description of Zero-Copy Cloning:

A) Metadata-only operation
B) No replication of data
C) Unmodified data stored once; modified data stored as new blocks
D) All of the above
D) All of the above

Which is not a characteristic of Time Travel?

A) Protection from accidental data operations
B) Recover data with the cost of running backups
C) Previous versions of data automatically retained
D) Retention period selected by customers (up to 90 days for Enterprise edition)
B) Recover data with the cost of running backups

There is no cost in Snowflake for running backups, purchasing additional hardware, incurring downtime and overhead of additional administration

True or False: When defining columns to contain dates or timestamps, Snowflake recommend choosing a date or timestamp data type rather than a character data type?
False – Snowflake stores DATE and TIMESTAMP data more efficiently than VARCHAR, resulting in better query performance.

True or False: Referential integrity constraints in Snowflake are enforced?
False – Referential integrity constraints in Snowflake are informational are not enforced.

True or False: Since integrity constraints are not enforced, the developers should not the creation of primary and foreign keys.
False – The primary keys and foreign keys enable members of your project team to orient themselves to the schema design and familiarize themselves with how the tables relate with one another. And most business intelligence (BI) and visualization tools import the foreign key definitions with the tables and build the proper join conditions. This approach saves you time and is potentially less prone to error than someone later having to guess how to join the tables and then manually configuring the tool.

True or False: Reclustering a small table typically doesn’t improve query performance significantly?
True

When might you might consider specifying a clustering key and reclustering the table?

A) When a small is get out of sequence with the clustering key
B) The order in which the data is loaded does not match the dimension by which it is most commonly queried
C) To reorder a table to match another clustering key in another table that the users perform frequent joins
D) All of the above
E) None of the above
B) The order in which the data is loaded does not match the dimension by which it is most commonly queried

True or False: Zero-Copy cloning allow a customer to provision real, Production data for development and test environments without physically copying the data?
True

True or False: Snowflake storage cost are based only on a daily average of all database compressed data storage?
False – Database data and data stored for Time Travel retention and failsafe.

True or False: A Snowflake Role is the only thing that connects Storage (Database) to Compute (Virtual Warehouse)?
True

True or False: Snowflake provides embedded multi-factor authentication across some of its editions?
False – multi-factor authentication is embedded in all editions

Which security features are provided as part of Enterprise editions (select all that apply)?

A) Snowflake data encrypted using AES 256
B) Support for encrypting data using customer-managed keys
C) Periodic rekeying of encrypted data
D) Support for HIPAA
D) Support for PCI DSS compliance
F) Support for user SSO through federated authentication
A, C, and F

Data Sharing Providers will have to:

A) Incur the cost of the data storage – normal rates apply
B) May share a data set with an unlimited number of accounts
C) May set up and manage “Reader Accounts” for consumers who are not already Snowflake customers
D) All of the above
D) All of the above

Data Sharing Consumers will have to:

A) Incur the cost of Compute (virtual warehouse)
B) Must create a database based on the share using the ACCOUNTADMIN role
C) Can query shared objects in the same query that they query their own objects
D) All of the above
D) All of the above

All statements are true about Data (Storage) except:

A) All data in Snowflake is maintained in databases
B) Each database consists of one or more schemas , and, within these schemas, one or more tables and/or views
C) Schemas can be thought of as a physical grouping of database objects
D) Snowflake does not place any hard limits on the number of databases, schemas (within a database), or objects (within a schema) you can create
C) Schemas can be thought of as a physical grouping of database objects

Logical grouping not physical grouping

True or False: Virtual Warehouse can be configured to auto-suspend after a specific period of inactivity, or auto-resume as soon as the administrator cancels the auto-suspend?
False – Auto-Resume restarts the virtual warehouse as soon as a user requests an operation that requires Compute resources.

Upon startup or auto-resume of a Virtual Warehouse, how much time is automatically billing to the account:

A) One minute
B) Two minutes
C) Five minutes
D) Accounts are always billed by the second
E) None of the above
A) One minute
After the one minute – accounts are billed by the second

True or False: Virtual Warehouse in Snowflake is where the data is stored?
False – Virtual Warehouses is the Compute operation

Snowflake supports data in VARIANTs up to a maximum size of:

A) 4 MB uncompressed
B) 8 MB uncompressed
C) 16 MB compressed
D) 32 MB compressed
E) None of the above
C) 16 MB compressed

Non-native values such as dates and timestamps are stored as strings when loaded into a VARIANT column so which statements are true:

A) Operations on these values could be slower
B) These columns would consume more space than when stored in a relational column with the corresponding data type
C) Both A and B are true
D) Neither A or B are true
C) Both A and B are true

The Snowflake UI is divided into for basic areas. Which of the following areas is not part of the UI:

A) Query
B) Databases
C) Warehouse
D) History
A) Query
Worksheet is the name of the query section

True or False: The PUT and GET commands can be executed via the Snowflake UI?
False – These commands can only be executed using SNOWSQL client. They are not supported by ODBC driver.

The commands for loading data into Snowflake are:

A) COPY & PUT
B) COPY & INSERT
C) INSERT & PUT
D) None of the above
B) COPY & INSERT

True or False: COPY statement allows insert on SELECT against a staged file, and a WHERE clause can be used?
False – INSERT command allows the Where clause, not the COPY command

True or False: The COPY command is more performant than the INSERT statement?
Trie

To load data into Snowflake, what needs to be in place (check all that apply)?

A) Virtual Warehouse
B) Predefined target table
C) Staging location with data staged
D) File Format
A, B, C, and D

True or False: Snowpipe is a continuous data ingestion service that detects and loads streaming data?
True

Which of the following is not a feature of Snowpipe:

A) The service can load data from any internal or external stage
B) Snowpipe has a server-less compute model
C) The service provides REST endpoints and uses Snowflake provided compute resources to load the data and retrieve history reports
D) Snowpipe loads data after it is stage and the user executes the LOADDATA command
D) Snowpipe loads data after it is stage and the user executes the LOADDATA command

No LOADDATA command – and the data is automatically loaded within minutes after the files are added to the stage

Name all of the file/data types that Snowflake support for data loading?
Text Delimited (CSV, TAB, etc.)
JSON
XML
Avro,
Parquet
ORC

Which layer does Snowflake store the various statistics for databases, tables, columns, and files?

A) Storage
B) Compute
C) Cloud Services
D) Snowflake does not store statistics
C) Cloud Services – the metadata is stored in this layer

True or False: Can a query run against Result Cache with no Virtual Warehouse running and retrieve results (assuming the query is cached)?
True

__ is used to analyze the execution details of a query?

A) Statistics
B) Metadata
C) Query Plan
D) Query Profile
D) Query Profile

True or False: A query plan can only be used for completed queries?
False – it can be used for both in-progress and completed queries

Which feature does not come with the Query Profile?

A) Graphical representation of the main components of the processing plan for the query
B) Details and statistics for the overall query
C) Hints for improving the query performance
D) Statistics for each component of the query
C) Hints for improving the query performance

Which proven data modeling techniques does Snowflake support (check all that apply)?

A) 3rd Normal Form
B) Data Vault
C) Star Schema
D) Snowflake Schema
E) All of the above
E) All of the above

True or False: The use case and audience drives the selection of the proven data modeling techniques?
True

True or False: Snowflake enforces all constraints?
False – Snowflake only enforces NOT NULL constraint

True or False: A best practice of load and store Semi-structured data in Snowflake is to parse the semi-structure string into structured columns on source data load?
False – the data should be loaded and stored in a VARIANT data type.

What is the best practice for handling semi-structured data with 3rd party BI tools?

A) Use the BI tool to create a metadata object to view the column
B) ETL the column with other columns that are part of the query into a structured table
C) Create a Snowflake view that parse the semi-structured column into structure columns for the BI tool to retrieve
D) All of the above
C) Create a Snowflake view that parse the semi-structured column into structure columns for the BI tool to retrieve

True or False: The number of COPY operations that run in parallel can exceed the number of data files to be loaded?
False – it cannot exceed – there are no files to copy

Which of the following is not a best practice for loading data?

A) Splitting large files into a greater number of smaller files distributes the load among the servers in an active warehouse, thereby increasing performance.
B) Split files by line to avoid records that span chunks
C) Setting all of the load files to the optimal size of 10 to 100 MB in compressed size
D) The number of data files that are processed in parallel is determined by the number and capacity of servers in a warehouse
E) All of the above
E) All of the above

True or False: users control the file split and size of data being load and the how the data is divided into micro-partitions?
False – Snowflake determines how the data is divided into micro-partitions. Users cannot create or configure these partitions.

True or False: Micro-partitions are immutable?
True – subsequent changes of any type to the data will be written to additional micro-partitions

A Snowflake mechanism that is used to limit the number of micro-partitions scanned by a query is called __?

A) Governor
B) Regulator
C) Pruning
D) None of the above
C) Pruning

Database designer should consider using Clustering Keys when (check all that apply):

A) The tables is very large (multi TB)
B) Table is large enough to reside on many micro-partitions
C) Columns in the table can provide sufficient filtering to select a subset of these micro-partitions
D) All of the above
D) All of the above

What are they general indicators that can help determine whether to define a clustering key for a very large table:

A) Queries on the table are running slower than expected or have noticeably degraded over time
B) The clustering ratio for the table is very low and the clustering depth is very large

True or False: Snowflake’s metadata repository stores references to all of the micro-partitions files for each table, as well as tracking of all versions of the table data within the data retention window?
True

True or False: Both a user defined Clustering Key and a Natural Clustering Key can be establish for table?
False – Snowflake data is divided into and stored in micro-partitions. Only one clustering key can be used for that process.

True or False: For most tables, it is a best practice to allow Snowflake’s automated micro-partitioning process to fully manage the table’s micro-partitions?
True

The process of co-locates column data with same values in the same micro-partition, if possible, is called _?

A) Re-Clustering
B) Natural Clustering
C) Partitioning
D) None of the above
B) Natural Clustering

The number of Snowflake credits are consumed is determined by (check all that apply)

A) Size of the warehouse
B) Number of Users for that warehouse
C) How long (in secs) the warehouse runs
D) All of the above
A & C

True or False: Setting the Auto-Suspend value to “Never” will produce efficient credit consumption?
False – the warehouse will never go into suspend mode (i.e. it will always be running and consuming credits)

True or False: User can view and modify Resource Monitors?
True – But the ACCOUNTADMIN has to enable the user first (by granting permissions)

Snowflake’s Resource Monitors have the following capabilities (check all that apply):

A) impose limits on the number of credits that a warehouse can consume
B) Trigger actions (i.e. like suspending) when the limit is reached
C) Can only be created by an ACCOUNTADMIN
D) All of the above
D) All of the above

True or False: The key to sizing a warehouse is the experiment with different types of queries and different warehouse sizes to determine the best combinations that best meet your specific query requirements and workload.
True

Identify the best practices for ACCOUNTADMIN role and permissions (select all that apply):

A) This role should be given to any user that need a high level of authority
B) There should be at least two user granted this role
C) All ACCOUNTADMIN users should have multi-factor authentication enabled
D) Objects should not be created using this role
E) All of the above
B, C, & D

True or False: It is a best practice to have the majority of objects owned by SYSADMIN?
True

True or False: There is a technical difference between an object access role and a business function role in Snowflake.
False – There is no difference. The difference is in how they are used logically to assemble and assign sets of privileges to groups of users.

True or False: Snowflake provides “Future grants” that allow defining an initial set of privileges to grant on new (i.e. future) objects of a certain type (e.g. tables or views) in a schema.
True – As new objects are created, the defined privileges are automatically granted to a specified role.

True or False: A user cannot view the result set from a query that another user executed.
True

True or False: A user with the ACCOUNTADMIN role can view the results for a query run by another user.
False – only the user who executed a query can access the query results.

True or False: When cloning a database, schema or table creates a copy of the source object along with any privileges?
False – Any privileges granted on the source object do not transfer to the cloned object.

The ACCOUNTADMIN role can perform the following tasks (select all that apply):

A) Configuring parameters at the account level
B) View and operate on all objects in the account
C) Can stop any running SQL statements
D) Can view and manage Snowflake billing and credit data
E) All of the above
E) All of the above

In order to query a table in Snowflake, the user must be granted which privileges at a minimum (select all that apply):

A) Grant USAGE on
B) Grant USAGE on
C) Grant Select on
D) All of the above
D) All of the above

True or False: the ACCOUNTADMIN role can modify or drop objects created by a custom role?
False – The custom role must be granted to the ACCOUNTADMIN role directly or, preferably, to another role in a hierarchy with the SYSADMIN role as the parent. The SYSADMIN role is managed by the ACCOUNTADMIN role.

True or False: Snowflake recommends using a role other than ACCOUNTADMIN for automated scripts.
True

What should be done to prevent account administrators from inadvertently using the ACCOUNTADMIN role to create objects:

A) Give ACCOUNTADMIN to only one person in the organization that is not a developer
B) Nothing, because ACCOUNTADMIN cannot create objects
C) Revoke object creation authorities from the ACCOUNTADMIN role
D) Do not make ACCOUNTADMIN the default role for any users in the system
D) Do not make ACCOUNTADMIN the default role for any users in the system

True or False: What is data skew is when partitions in database become disproportionately-sized?
True

True or False: The closer the ratio of scanned micro-partitions and columnar data is to the ratio of actual data selected, the more efficient is the pruning performed on the table?
True

True or False: Some DDL operations are metadata only operations?
True – deleting all rows in a table is a metadata only

What are the steps Snowflake pruning?

A) Eliminate cluster key entries from any query filters
B) Eliminate micro-partitions that are not needed for the query
C) Eliminate columns by sorting within the micro-partition
D) Eliminate by column within the remaining micro-partitions
E) All of the above
B & D – and they are performed in that order

True or False: The clustering depth for a table is an absolute or precise measure of whether the table is well-clustered.
False – Ultimately, query performance is the best indicator of how well-clustered a table is.

True or False: A table that consists of a single micro-partition or no micro-partitions (i.e. an empty table) always has a clustering depth of 0?
True

True or False: As a general rule, Snowflake recommends ordering the keys from lowest cardinality to highest cardinality for a clustering key.
True

True or False: If you want to use a column with very high cardinality as a clustering key, we recommend defining the key as an expression on the column, rather than on the column directly, to reduce the number of distinct values.
True –
instead of c_timestamp, use to_date(c_timestamp)

True or False: To recluster a table, an admin would execute the RECLUSTER command?
False – Reclustering is done automatically. Manual reclustering has been deprecated.

Which services are multi-tenant?

A) Global Services
B) Virtual Warehouse – Compute
C) Storage
D) All of the above
E) None of the above
A & C – Compute is customer based

True or False: Snowflake deploys into a customer VPC or VNET?
False – Snowflake only deploys within it own VPC.

True or False: An account level Resource Monitor can override a Resource Monitor assignment for individual warehouse?
False – It cannot override the Resource Monitor that is assigned to individual warehouse.

Resource Monitor is a 1st class object has elements defined to its class. Select all that apply?

A) Credit terms
B) Credit Quota
C) Triggers
D) Credit Allocation
E) Credit Usage
F) Credit Remaining
G) All of the above
B, C, and E

True or False: A suspend trigger on a resource monitor cancels all in-flight transactions and bring down the warehouse once the quota is reached?
False: Suspend Immediately cancels all transactions and brings down the warehouse (i.e Kill -9). Whereas Suspend, lets all in-flight transactions complete normally.

If the Credit Quota of a Resource Monitor is reached, suspended warehouses can not be resumed until one of the conditions is met (select all that apply)?

A) A new billing cycle starts
B) Credit quota of the monitor is increased
C) The credit threshold for the trigger is increased
D) The monitor is no longer assigned to the warehouse
E) The monitor is dropped
F) All of the above
F) All of the above

True or False: When a Resource Monitor has multiple warehouses assigned to it, the credit quota is allocated evenly across the warehouses?
False – credit usage of one warehouse can impact other warehouses.

True or False: Resource Monitor notification are automatically assigned to ACCOUNTADMIN role?
False – Resource Monitor notifications are disabled by default.

True or False: ACCOUNTADMINs are the only users that receive resource monitor notifications?
True

Which command can be grant to roles outside of the ACCOUNTADMIN role to accessing resource monitors?

A) MODIFY
B) ALTER
C) VIEW
D) MONITOR
E) CREATE
A & D

True or False: Each worksheet in the UI can have its on role and be set independently?
True

Which Snowflake cache does the user/administrator have control over?

A) Results cache
B) Metadata cache
C) Data cache
D) None of the above
B) Metadata cache

How can the user/administrator increase the hit ratio on the local data cache (select all that apply):

A) Seed the cache with queries
B) Increase the cluster size
C) Alter the data cache memory parameter
D) All of the above
A & B

Why is the following SQL statement not efficient in Snowflake?

“SELECT * FROM TABLE”
There will be very little (if any) horizontal pruning because the query is selecting all of the columns.

How many cluster keys can reside on a Snowflake table?

A) 0
B) 1
C) 3
D) 4
E) it depends
B) – only one cluster key can be created on a table (natural key or defined key)

True or False: A materialize view in Snowflake will add more storage cost to the customer bill
True – a material view creates a copy of the data based on the view definition

True or False: Data Sharing is metadata only?
True – No update, no time travel, same region

True or False: The user can execute a table re-clustering to reduce micro-partition overlap and speed up performance?
False – table clustering happens automatically by a Snowflake service.

Which roles does Snowflake suggest to enable MFA (select all that apply?

A) SECURITYADMIN
B) SYSADMIN
C) ACCOUNTADMIN
D) User defined role
E) PUBLIC
F) All of the above
A, B, & C

True or False: Users can have access to many roles and they are active in every session?
False – only one active role per session

True or False: Users own database objects?
False – roles own database objects

Which of the following are security best practices for Snowflake (select all that apply)?

A) All objects owned by SYSADMIN
B) All roles and users are own by SECURITYADMIN
C) Grant all of your roles back to SYSADMIN
D) All of the above
D) All of the above

What is the frequency for Snowflake to apply software patches to the code base?

A) Daily
B) Weekly
C) Monthly
D) Semi-annually
E) As soon as they are ready
B) Weekly

True or False: Customer has COMPUTE choices when it comes to cluster definition?
False – COMPUTE clusters are predefined by Azure and AWS

When scaling up a Snowflake warehouse, what is the scaling factor when moving between T-shirt sizes?

A) 2
B) 4
C) 8
D) User specified
A) 2

When dealing with a large complex query, the user must:

A) Rewrite the query
B) Scale up the cluster
C) Scale out the cluster
D) All of the above
B) Scale up the cluster – moving up a T-shirt size gives the query more resources (increase the size of the pipe)

When dealing with query throughput, the user must:

A) Rewrite the query
B) Scale up the cluster
C) Scale out the cluster
D) All of the above
C) Scale out the cluster to allow for more concurrency (increase the number of pipes)

In a multi-cluster environment, what are the names of the scaling policies that can be selected (select all that apply)?

A) Normal
B) Standard
C) Economy
D) Maximize
E) All of the above
B & C

What are the types of tables in Snowflake (select all that apply)?

A) Regular
B) Transient
C) Permanent
D) Clones
E) Temporary
F) Persistent
B, C, and E

How many resource monitors can you have at the account level?

A) 0
B) 1
C) 2
D) up to 5
E) unlimited
B) 1

What is the largest size of a micro-partition?

A) 8 MB uncompressed
B) 8 MB compressed
C) 16 MB compressed
D) 32 MB compressed
C) 16 MB compressed

Which of the following are actions that can be take from a Resource Monitor trigger (select all that apply)?

A) Suspend
B) Shutdown
C) Suspend immediately
D) Notify
E) All of the above
A, C, and D

Which of the following conditions can restart a suspended Resource monitor (select all that apply)?

A) The credit threshold for the monitor trigger is increased
B) The monitor is no longer assigned to the warehouse
C) Increase in customer quota
D) A new time interval cycle has starts
E) All of the above
A, B, and D

True or False: A warehouse can be assigned to one or more Resource monitors?
False – warehouses can be assigned to only one resource monitor.

What is the recommend size of files to be loaded via Snowflake’s Snowpipe?

A) 1 – 10MB compressed
B) 25 – 50MB compressed

True or False: Snowflake tables are logical structures of collections of columns and rows of data physically stored in Snowflake’s FDN files (micro-partitions)
True

True or False: Cluster keys should be defined with high cardinality columns
False – low cardinality columns should be used

When selecting a clustering key, which of the following should the developer consider (select all that apply):

A) Time dimensions
B) Join keys
C) Low cardinality columns
D) Predicates on tables
E) All of above
E) All of the above

True or False: Standard warehouses can scale out to meet concurrency needs?
False – Multi-Cluster warehouse can scale out

True or False: A virtual warehouse can only be resized after being stopped or suspended?
False – a virtual warehouse can be resized at any time.

True or False: User can query a STAGE object?
True

True or False: Snowflake charges additional fees to Data providers for each share they create.
A. True

B. False

Answer: A

https://www.examstrust.com/snowflake-dumps/snowpro-core-cert.html

A role is created and owns 2 tables. This role is then dropped. Who will now own the two tables?
A. The tables are now orphaned

B. The user that deleted the role

C. SYSADMIN

D. The assumed role that dropped the role

Answer: D
https://www.examstrust.com/snowflake-dumps/snowpro-core-cert.html

When should you consider disabling auto-suspend for a Virtual Warehouse?

Choose 2 answers
A. When users will be using compute at different times throughout a 24/7 period

B. When managing a steady workload

C. When the compute must be available with no delay or lag time

D. When you don’t want to have to manually turn on the Warehouse each time a user needs it

Answer: B C
https://www.examstrust.com/snowflake-dumps/snowpro-core-cert.html

True or False the longer the data retention period, the higher the resulting storage costs.
A. True

B. False

Answer: A
https://www.examstrust.com/snowflake-dumps/snowpro-core-cert.html

What parameter controls if the Virtual warehouse starts immediately after the CREATE WAREHOUSE statement?

Select one.
A. INITTIALLY_SUSPENDED = TRUE/FALSE

B. START_AFTCR_CREATE = TRUE/FALSE

C. START_TTIME = 60 // (seconds from now)

D. START.TIME = CURRENT.DATE()

Answer: A

What are the 3 Cloud Providers available for Snowflake
1) AWS – a provider since inception
2) Azure Cloud Platforms – generally available as of Sept. 2018
3) Google Cloud Platform – trialed late 2019

Snowflake is currently the only DW built for the cloud (T/F)
True

How does Snowflake describe their architecture?
A) Multi-Cluster, Shared Data
B) Single-Cluster, Shared Data
A) Multi-Cluster, Shared Data

What are the 3 components of Snowflake’s architecture (both the name and descriptor)
1) Storage – Databases
2) Compute – Virtual Warehouses
3) Cloud Services – Front End

What are the Cloud Object Storage options?
1) AWS S3
2) Azure Blobs
3) GCP

How are storage costs calculated?
A daily average of all compressed data storage, including data stored according to Time Travel retention policy and failsafe procedures

Which Microsoft and Amazon systems does Compute leverage?
1) AWS EC2
2) Azure Compute

Compute can’t scale out as queries are running (T/F)
False

Which services does Cloud Services provide?
1) Security Management
2) Infrastructure Management
3) Metadata Management
4) Query Optimization

Are Snowflake’s Storage (DB) and Compute (WH) layers
A) Separate and Independent
B) Together and Dependent
A) Separate and Independent

In addition to multiple Virtual Warehouses, Multi-Cluster means individual Virtual Warehouses can…
Scale up or down automatically

Multi-Cluster turn off clusters when activity slows down (T/F)
True

Snowflake uses ANSI-SQL (T/F)
True

What is Snowflake’s semi-structured data type called?
VARIANT

What are the three types of caching in Snowflake?
1) Warehouse
2) Metadata
3) Results

All cache data are cleared if the underlying data changes (T/F)
True

What does Warehouse Caching do? When is the data invalidated? Where does it live?
Stores data that’s been loaded into Virtual Warehouses during querying. The data is invalidated if it’s suspended. Lives in Compute.

What does Metadata Caching do? Where does it live?
It stores various info about tables. It lives in the Cloud Services layer.

What does Results Caching do? Where does it live?
It stores results of queries that have been executed within the past 24 hours (assuming underlying data hasn’t changed). It lives in the Cloud Services Layer.

A customer needs to have a preexisting account with AWS or Azure in order to have a Snowflake account (T/F)
False

What are Availability Zones?
Data centers in certain regions

How many availability zones is your data replicated in?
3 data zones in a region

What does it mean by “automatic” micro-partitions
A partition scheme doesn’t need to be defined up front. Snowflake determines and creates it.

Data partitioning is automatically completed in the natural ingestion order (T/F)
True

New Partitions are based on physical properties (T/F)
True

How does Snowflake optimize storage of semi-structured data?
Repeated elements within the strings

How far back does the Time Travel protection go?
90 days

Zero-Copy Cloning operates on more than just the metadata (T/F)
False – It’s a metadata-only operation

There’s a difference in storage cost allocation between structured and semi-structured data (T/F)
False – There’s no difference

A user can’t be assigned multiple roles (T/F)
False – A user can be assigned multiple roles

If a user creates an object, what permissions do others have?
All users with the role that created the object will have access to it, as well as the users and roles that the role that created the object role up to.

Which encryption method is used?
AES 256 Strong Encryption

What are the three “higher editions” (stronger security) of Snowflake?
1) Enterprise
2) Enterprise for Sensitive Data
3) Virtual Private Snowflake

What security protections does Snowflake provide

Single Sign On through federated authentication
Periodic Rekeying of encrypted data
HIPPA Compliance
PCI DSS Compliance

What cost does the Provider incur in the Data Sharing process?
Data Storage costs, there’s no upcharge for shared data

What cost does the Consumer incur in the Data Sharing process?
Compute costs

Data Sharing Consumers must use the ACCOUNTADMIN role to create a database based on the share (T/F)
True

Data Sharing Consumers can consume data residing from any region (T/F)
False – Consumer accounts must reside in the same Cloud Provider Region as the Provider account

Providers can only share data with users who have accounts (T/F)
False – Providers can set up reader accounts for users who aren’t snowflake customers

What are schemas? Is there a limit to the objects contained in a schema?
Logical groupings of database objects, such as tables and views by concept or purpose. There’s no limit to objects contained in a schema.

You can have multiple databases in one schema (T/F)
False – Schemas exist within databases. You can have multiple schemas within one database though.

What are sequences?
Sequences are used to generate unique numbers across sessions and statements, including concurrent statements

Virtual Warehouses have to be manually turned off (T/F)
False – Auto-suspending can turn them off

Auto-Suspension limits has to be determined when the Virtual Warehouse is configured (T/F)
False – it can be changed after initial configuration

How is compute billing charged (units of time)
Compute is charged for a base 1 minute of start up and then each second following that

What is the max size of VARIANT data in a column?
16 MB compressed

What is the difference in query impact between regular JSON types (strings and numbers) and non-native JSON types (timestamps)
Regular JSON types are easier and quicker to query than Non-Native types

What are the 4 mains sections of the Snowflake UI
1) Databases – Storage
2) Warehouses – Compute
3) Worksheets
4) History
(ACCOUNTADMIN sees the account area)

What are Snowflake connectivity options?
1) SnowSQL
2) ODBC
3) JDBC
4) Python
5) Node.JS

What are the two commands for loading data? How do they differ?
1) Copy – Bulk insert and more performant
2) Insert – Row-by-row insert and less performant

In a Copy function, you can insert on a SELECT against a staged file, but can’t use a WHERE clause (T/F)
True

In an INSERT Function, you can insert on a SELECT against a staged file, but can’t use a WHERE clause (T/F)
False – You can use the where clause

What services does Snowpipe offer
1) REST Endpoints
2) SF-Provided compute resources
3) SF-Provided Load Capacity

Snowpipes load fresh data from micro-batches manually (T/F)
False – This a continuous process

What are the 4 things needed to load data into Snowflake
1) A File Stage
2) A pre-defined table for the data to go into
3) A virtual warehouse
4) A file format

Which file formats does Snowflake data loading support?
1) Text delimited
2) JSON
3) XML
4) Avro
5) Parquet
6) ORC

Data doesn’t have to be staged prior to being loaded (T/F)
False – Data must be staged prior to loading

Once data is loaded, it’s compressed and converted automatically (T/F)
True

Where does Snowflake store the various information/statistics regarding database, tables, columns, and files?
Metadata Manager in the Cloud Services Layer

Can a cached query be run without a Virtual Warehouse running?
Yes – No compute is used in a query that’s cached in the results cache

What is the Query Profile used for?
Analyze the execution details of a query

What are the most common kinds of technology partner?
Data Integration and Business Intelligence technology partners

What advantages does Snowflake have over On-Premise EDWs?
1) Instant Scalability
2) Separation of compute and storage
3) No need for data distribution

What advantages does Snowflake have over other Cloud EDWs?
1) Concurrency
2) Automatic failover and disaster recovery
3) Built for the cloud

What advantages does Snowflake have over Hadoop?
1) No hardware to manage
2) Automatic failover and disaster recovery
3) Native SQL (include on semi-structured)

What advantages does Snowflake have over Data Engines
1) No need to manage data files
2) Automated cluster management
3) Native SQL

What advantages does Snowflake have over Apache Spark?
1) No need to manage data files
2) Automated cluster management
3) Full SQL Support

What are the best practices for data modeling in Snowflake?
1) 3NF
2) Data Vault
3) Star Schema / Snowflake Schema

What is the only constraint that Snowflake enforces?
NOT NULL

Which main constraints aren’t enforced by Snowflake?
1) Referential Integrity
2) Uniqueness
3) Primary Keys

Should semi-structured data be parsed in a Table or a View?
View – This allows semi-structured data to remain in it’s complete form in Snowflake while appearing to be in structured form when accessed

What is the optimal compressed file size when loading files into Snowflake?
10 – 100 MB compressed

When loading data is it better to have one large file or multiple small files?
Multiple small files allow the Virtual Warehouses to run in parallel

Users can create the micro-partitions when data is loaded into Snowflake (T/F)
False – Snowflake is fully in control

What is pruning?
A process used to limit the number of micro-partitions

What are Clustering Keys
Clustering data in a table according to one or more columns/expressions

How large should a table be for Clustering Keys to be beneficial?
Multi-Terabyte (TB)

What are two requirements for Clustering Keys to improve performance?
1) Table must be large enough to reside upon many micro-partitions
2) Clustering Keys have to provide significant filtering

What are some of the indicators that Clustering Keys are beneficial?
1) Queries on the table are running slower than expected or have noticeably degraded over time
2) The clustering ratio for the table is very low and the clustering depth is very large

Micro-Partitions aren’t immutable (T/F)
False – Once they’ve been written, they will never be changed or overwritten. Rather, subsequent changes of any type to the data will be written to additional micro-partitions

What are two ways to effectively and efficiently use Virtual Warehouses?
1) Experiment with different types of queries and sizes
2) Don’t focus on warehouse size since they can be suspended when not in use

What can Resource Monitors do?
They can impose limits on the number of credits Warehouses consume in order to help control costs and avoid unexpected credit usage

Resource Monitors can only send notifications. They can’t suspend actions. (T/F)
False – They can send notifications and suspend

Resource Monitors can only be created by ACCOUNTADMINs (T/F)
True

Resource Monitors can only be viewed and modified by ACCOUNTADMINs (T/F)
False – Enabled users with other roles can view and modify Resource Monitors

Which role has the highest permissions?
ACCOUNTADMIN

What’s the minimum recommended number of ACCOUNTADMINs?
2

Which account level is recommended to own a majority of the objects?
SYSADMIN

Snowflake can run within a customer’s VPC (T/F)
False – Snowflake can’t run in a VPC

Which transformations are available during COPY or INSERT functions
1) Column Reodering
2) Column Omission
3) Casts
4) Truncating text strings that exceed the target length

How is Snowflake’s internal staging charged?
It incurs standard storage costs, but isn’t subject to additional costs with Time Travel or Fail-Safe

What extra transformation is available during a Snowpipe load?
Using snowpipes to load micro-batches into staging tables can allow for more transformations

What is a file format?
A standard set of procedures used to ingest files

What options are available when creating a file format?
1) Schema Name (the schema it belongs to)
2) File Format Type
3) Compression Method
4) Column Separator (Delimiter)

What’s the default encoding for delimited files (CSV, TSV, etc…)?
UTF-8

What’s the only supported character set for file formats such as JSON, Avro, and others?
UTF-8

What file compression methods can be automatically detected?
1) GZIP
2) BZIP2
3) Deflate
4) Raw_Deflate

What file compression methods can Snowflake not automatically detect?
1) Brotli
2) Zstandard

When creating a Warehouse, what’s the difference between size and clusters?
The size details the number of servers in each cluster
The clusters is the max/min numbers the warehouse can scale up/down to

What’s the shortest amount of time that can pass before a Warehouse can auto-suspend?
5 minutes

What is the url naming convention for AWS?
https://account_name.region.snowflakecomputing.com

What is the url naming convention for Azure?
https://account_name.region.azure.snowflakecomputing.com

What is the url naming convention for GCP?
https://account_name.region.gcp.snowflakecomputing.com

What two architectural types is Snowflake a hybrid of?
1) Shared-Disk DB
2) Shared-Nothing DB

What compression type does Snowflake automatically use to compress newly loaded files?
Gzip

What is the auto encryption for newly loaded files?
128-bit keys

Which command removes the outer array structure when loading JSON files?
STRIP_OUTER_ARRAY

Files uploaded to Snowflake Staging Areas aren’t automatically encrypted (T/F)
False – They’re automatically encrypted with 128-bit or 256-bit keys

What’s the max amount of files that can be uploaded at once?
1000 files

Which command explodes compounded VARIANT values into multiple rows?
FLATTEN

What are the 3 types of internal stages
1) User Stage
2) Table Stage
3) Internal Named Stage

What things are important to know about User Stages?
1) They can only be accessed by a single user
2) They can’t be altered or dropped
3) You can’t set a file format. It needs to be specified in the COPY command to table

What things are important to know about Table Stages?
1) They can be accessed by multiple users
2) Can’t be altered or dropped
3) Can’t set file format, need to specify in COPY command to table
4) No transformation while loading

What things are important to know about Internal Named Stages?
1) They’re database objects
2) They can load data into any tables (needs user with privilege to do so)
3) Ownership of stage can be transferred

Snowpipes generally load newest files first (T/F)
False – Generally loads oldest first

When unloading data, which formats are allowed for export?
1) Delimited files
2) JSON
3) Parquet

When unloading data, which compression methods are allowed?
1) Gzip
2) Bzip2
3) Brotoli
4) Zstandard

What’s the max file size default when unloading data?
16 MB

What’s the max # of server clusters in a virtual warehouse?
10

What are the 4 types of tables?
1) Temporary Tables
2) Transient Tables
3) Permanent Tables
4) External Tables

When is the data from a Temporary Table purged?
After the session ends

Temporary Tables aren’t visible to other users (T/F)
True

Temporary Tables can’t have the same names as other non-temp tables within a DB/Schema (T/F)
False – They can have the same name

When is the data in a Transient Table purged?
It stays until it’s dropped

What’s the difference between a Transient Table and a Permanent Table?
Transient Tables have no Fail-Safe mode

What’s the difference between Non-Materialized and Materialized views?
Results are stored in materialized views. This leads to faster performance and contributes towards storage costs.

Which command displays all the files in a stage?
List

What does the FORCE command do when using the COPY INTO statement?
It forces all files in a stage to load into snowflake, regardless of whether or not they’ve been loaded before. This can result in duplicate data

What’s the command to allow a share to have access to databases and views?
GRANT USAGE

What’s the command to allow a share to have access to additional databases that contain objects referenced by the view you wish to share using GRANT ?
GRANT REFERENCE_USAGE

Can you replicate data to other users who are on a different provider or region than you (AWS, Azure, GCP)?
Yes

You can create a clone of shared database as a consumer (T/F)
False – You can’t clone the database or any schemas/tables

How long does a Snowpipe store metadata?
14 days

What is quiesce mode?
The mode a server’s in when it’s waiting to shut down.

A cloned object retains all granted privileges of the source object (T/F)
False – It doesn’t retain any granted privileges

A cloned database or schema’s child objects retain privileges of the source objects (T/F)
True

What are the two values for SCALING_POLICY when creating a virtual warehouse?
1) Standard
2) Economy

Which of the two can be cloned?
A) Internal Named Stages
B) External Named Stages
B) External Named Stages

When a database or schema is cloned, it also clones the contained snowpipes that reference an internal stage (T/F)
False – It doesn’t clone snowpipes that reference internal stages, but it does clone ones that refernce external stages

Can Virtual Warehouses be cloned?
No, they can’t

Can ACCOUNTADMINS view results of other users queries?
No, they can only see the queries they ran

When creating a stage, the URL doesn’t need the “https://” in front of the name (T/F)
True

Which objects can you clone?
1) Database Objects
2) Individual external named stages
3) Snowpipes
4) Sequences
5) Streams
6) Tasks

Can you specify time travel requirements when cloning?
Yes, this is done by suing the AT and BEFORE commands

Can Transient tables be cloned?
Yes

Reclustering has to be done manually in Snowflake (T/F)
False – Reclustering is automatic, no maintenance is needed

Which user types can see the account button?
1) ACCOUNTADMIN
2) SECURITYADMIN

Data replication only needs to happen once per cloud/region (T/F)
True – Once it’s been replicated to that cloud/region, it can be shared as many times as needed

What is a maximized Virtual Warheouse? What is an auto-scale Virtual Warehouse?
Maximized is where the max and min amount of clusters is the same value. This ensures control over the used resources. Auto-scale is where the max and min amount of clusters is different.

Clustering Keys improve performance on all queries that filter or sort on that column (T/F)
True – This includes some joins

A share can only contain one database (T/F)
False – A share can contain several databases, as long as they are in the same Snowflake account

Can Snowpipes be configured to not automatically ingest data?
Yes, the auto_ingest parameter must be set to TRUE for data to automatically ingest

What are Snowpipes referred to as in queries?
Pipes

In which order do different Snowflake accounts get new releases/updates?
1) Designated accounts opting for early access
2) Standard & Premier Access
3) Enterprise & Upward

Which languages can UDFs be written in?
1) Javascript
2) SQL

How long can Results Caches last? How does it persist for this long?
31 days – this happens when the results cache is used in a query before it’s purged, which restarts the 24 hr time

Modifications to cloned databases/schemas/tables result in storage costs (T/F)
True – Any added, deleted, or updated rows in a cloned table add to storage costs

Does changing the clustering key on a table incur cost?
Yes

Snowflake doesn’t know to skip headers when ingesting files (T/F)
True – This has to be specified in the file format

Can you cluster on the combination of multiple columns?
Yes – CLUSTER BY (column1, column2)

When creating a warehouse, what unit of time is the value of the auto_suspend parameter in?
Seconds

Which command allows users to see the query id of the second query executed in the current session?
SELECT last_query_id(2)\

How many times can a share be consumed by a single account?
Once. “A share can only be consumed once per account.”

What is Federated Authentication?
Federated Authentication enables users to connect using SSO via an external SAML 2.0 compliment identity provider (IdP).

What is the defn of PaaS?
Platform as a Service. You manage only the applications and data. Everything else is managed by the vendor.

What are the three layers of Snowflake, bottom to top?
Database Storage; Query processing – Virtual Warehouses; Cloud Services

What does the Cloud Services Layer include?
Infrastructure Manager
Optimizer
Metadata Manager
Security
Authentication and Access Control

Database Layer – What are the 3 characteristics of how data is stored

Optimized 2. Compressed 3. Columnar

Processing Layer – What is a virtual warehouse?
An MPP compute cluster composed of multiple compute nodes allocation by Snowflake from a cloud provider.

What impact to virtual warehouses (VW) have on each other?
Each VW is an independent cluster that does not share compute resources with other VW.

What are the 5 ways of connecting to Snowflake?

Web-based interface from which all aspects of managing Snowflake can be accessed.
SnowSQL – a command line interface (CLI) that can be used to manage all aspects of Snowflake.
ODBC and JDBC drivers
Native connectors (e.g. Python)
Third-party connectors that can be used to connect ETL or BI tools

What are the three supported cloud platforms?

Amazon Web Services (AWS)
Google Cloud Platform (GCP)
Microsoft Azure (Azure)

What are the 4 editions of Snowflake?

Standard
Enterprise
Business Critical
Virtual Private Snowflake (VPS)

What does Enterprise Edition get you?

Standard +
Multi-cluster warehouse
Up to 90 days of Time Travel
Annual rekey of all encrypted data
Materialized Views

What 9 things does Business Critical Edition get you?

Enterprise +
HIPPA support
PCI compliance
Data encryption everywhere
Tri-Secret Secure using customer-managed keys
AWS and Azure Private Link support
Enhanced security policy
Database failover and fallback between accounts for business continuity

What does Virtual Private Snowflake (VPS) Edition get you?

Business Critical +
Customer-dedicated virtual servers wherever the encryption key is in memory
Customer-dedicated metadata store

What are the 2 storage payment options?

On-demand storage – pay for usage month to month
Capacity storage – pay upfront for usage for some period of time

Is pricing done by cloud platform or region?
Pricing is done by region, thus there is no pricing advantage to one cloud over another. As the relationship to the chosen cloud vendor is between Snowflake and the vendor, there is no volume purchasing advantage to picking the cloud provider you have the most experience with.

Who determines the region names and how the regions are defined?
The cloud providers

How often does Snowflake release new versions?
New Snowflake versions are released weekly.

What are the two types of weekly Snowflake releases?

New release: may include new features, behavior changes, enhancements, updates and fixes. Includes documentation updates
Patch release: includes fixes only

How are releases staged for new releases?
Releases are staged as follows:

Day 1: stage 1 (early access) for designated enterprise accounts
Day 1 or 2: stage 2 (regular access) for all Standard Edition accounts
Day 2: stage 3 (final) for all Enterprise Edition and VPS accounts

Who is the Multi-Factor Authentication (MFA) vendor?
Duo is the vendor.

What is necessary to setup MFA?
Install the Duo app on a smart phone and change SF user profile. All other arrangements with Duo are taken care of by Snowflake.

What are the supported Identity Providers for Federated Authentication?
Native :

Okta – hosted service
Microsoft Active Directory Federation Services (ADFS) – on-premise software installed on Windows Server
Non-native:
Google G Suite
Microsoft Azure Active Directory
One login
Ping Identity PingOne

What is OAuth?
OAuth is an open-standard protocol that allows supported clients authorized access to Snowflake without sharing or storing user login credentials. Known as delegated authorization. OAuth is authorization and SAML is for authentication.

How is access governed?
Combines aspects from Discretionary Access Control (DAC) where each object has an owner, who can in turn grant access to that object, and Role-based Access Control (RBAC) where access privileges are assigned to roles which are in turn assigned to other roles (a hierarchy of roles) or users.

What are the key concepts to understanding access control in Snowflake?

Securable object: a database object. Unless allowed by a grant, access will be denied.
Role: An entity to which privileges can be assigned
Privilege: a defined level of access to an object
User: a user identity whether associated with a user or program (think User vs Service accounts in Oracle)

What is End-to-End Encryption (E2EE)?
E2EE is a form of communication in which no one but the end user can read the data. Not even Snowflake nor any of its partners can see the data in clear text.

What is the difference in when data is encrypted between external stages and internal stages?
Internal (to Snowflake), data is encrypted when written to the stage. Data from external stage is not encrypted until written into Snowflake. Encrypting external stages is a function of the cloud provider where the stage resides.

Define “client-side encryption”
Where the external stage is encrypted by the user using the capabilities of the cloud where the stage resides. The decryption key is passed to Snowflake so it can decrypt the data as it receives it.

What encryption algorithm is used on tables in Snowflake
“Snowflake uses strong AES 256-bit encryption with a hierarchical key model rooted in a hardware security module.

What does the customer have to do to rotate keys and re-encrypt data?
Keys are automatically rotated on a regular basis (more than 30 days old) by the Snowflake service and data can be automatically re-keyed (Enterprise Edition feature). Data encryption and key management is entirely transparent to the customer

What is Tri-Secret Secure?
It lets the customer manage encryption keys using either AWS Key Management Service (Amazon), Cloud Key Management Service (Google), or Azure Key Vault (Azure). Available only for Business Critical and above editions.

What command is used for getting data into and out of Snowflake, to or from a stage?
Bulk loader:
COPY INTO for loading or importing
COPY INTO for unloading or exporting

What commands are used for loading or unloading stage data?
PUT
GET
LIST (can also be used with named external stages)
REMOVE

What are the 4 kinds of tables and how are they different?
Key difference is in degree of permanence

Temporary tables

Only exist within the session in which they were created and persist only for the remainder of that session.

Not visible to others users or sessions. Can’t be cloned. Once dropped, can’t be recovered by the user or Snowflake.

Can have the same name as other tables; if so, temporary tables essentially hide other tables

No Time Travel

Cannot be cloned

Transient tables

Persist until they are explicitly dropped and are available to all users with privileges.

Do not have a fail-safe period.

0 or 1 day of Time Travel, as specified when the table is created

Can be cloned

Customer will be billed for storage

Permanent tables

Persist until explicitly dropped

7 day fail-safe period

0 or 1 (Enterprise Edition and up, 90) days, as specified when table is created

External Tables

Data located in external stage cloud storage that can be read-only queried just as a regular table.

What is a Lateral View
A view that specifies the LATERAL keyword in an inline view where the inline query can reference columns from table references that precede the LATERAL inline view

When should you use Materialized Views?

When the view contains a small number of rows and/or columns relative to the base table
When it contains results that require significant processing time (semi-structured data or aggregates that take a long time to calculate) (query consumes lots of compute credit)
The query is on an external table (data from an external stage)
The view’s base table is relatively static. If the view is used significantly more often than the columns in the view change.

What is an external table?
A data set in an external stage that can be queried as though it were a Snowflake table.
Only do this if the effort to load the table is substantially less than the cost of querying it from Snowflake. If you are doing lots of queries against the external table or are doing substantial transforming of it when you query it, you should probably import it.

What is Data Sharing?
Where you give read-only access of a database, schema, table, secure view, or secure UDF in one account to a user in another account.

What is the max number of shares you can create?
Unlimited

How does the consumer access shared data?
The person desiring to see shared data (consumer) must either have

A different Snowflake account
A Reader Account. If a consumer needs access to shares from multiple different accounts, they must have Reader Accounts specific to each provider account that shares data

What restrictions are there on sharing data?

Business Critical with HIPAA can only share data to another Business Critical with HIPAA account
Sharing from Business Critical to all other editions requires assistance from Snowflake
VPS does not support Secure Data Sharing due to the limitations against sharing data across regions
Consumer accounts must be in the same region as provider account.
Only Secure Views can be shared. Using a standard view generates an error

What is a Secure View?
A view that prevents the optimizer from viewing the view’s base table(s), ensuring that users have no access to the underlying tables. Can be integrated with roles to filter by rows. Thus Secure Views can limit both the columns and the rows displayed to a given users, and prevent them from gaining awareness of the structure of the underlying tables.

What are the semi-structured data types?
VARIANT, ARRAY, OBJECT

What are the data formats that can be stored in a VARIANT column?
XML
JSON.
ORC
Avro
Parquet

What is semi-structured data?
Schema or structure of the data comes with the data. You don’t have to pre-define the structure.

What do all three data types for storing semi-structured data have in common?
The values are always stored as VARIANT data types

What hierarchy levels can network policies apply to?
Account level and User level

If a user is associated to both an account-level and an user-level policy, which takes precedence?
The user-level takes precedence over account-level network policies

By default, what IP addresses can connect to Snowflake?
Any IP address can connect to Snowflake. Network Policies allow or deny specific addresses or range of addresses.

What IP version is supported by Snowflake, IPv6 or IPv4
Snowflake currently only supports IPv4

How do you activate Network Policies?
Set the NETWORK_POLICY parameter either for an account or a user

How do you get to Network Policies from the Snowflake UI?

Change role to ACCOUNTADMIN
Go to the Account tab, then to the Policies page.

If a new Network Policy is activated for an account that restricts access, when will users be impacted, immediately or on the next sign-on
Immediately. All users impacted by the account-level Network Policy will immedialy be prevented from executing any further queries.

How many Network Policies can be associated with an account at a time?
Only one network policy can be associated with an account at a time. If one is currently associated when an admin associates a new one, the currently associated policies is removed.

What account-level Network Policy privileges can be granted to roles?
Create, Delete, and Modify

What happens if you activated a user-level Network Policy that does not grant you access to Snowflake?
You will be prevented from activating the policy. Your current IP address must be included in the Allowed IP Addresses and cannot be included in the Blocked IP Address list.

When will user-level Network Policies impact a currently connected user?
Immediately. He/She will immediately be prevented from executing additional queries if their IP address is not in the Allowed IP Address list.

What happens if you associate a new user-level Network Policy to a user?
Just like with account-level Network Policies, only one user-level Network Policy can be associated with a user at a time. If you associate a new one, it immediately replaces the old one.

What user-level Network Policy privileges can be granted?
None. Network Policy privileges cannot be granted to users.

What is AWS Private Link?
A service for creating private virtual private cloud (VPC) endpoints that allow direct, secure connectivity between AWS VPCs without traversing the public internet. Snowflake on AWS is implemented as a VPC. It can only be used with Business Critical or higher edition.

What is Azure Private Link?
A service that provides private connectivity to Snowflake by ensuring that access to Snowflake is through a private IP address. It requires Business Critical edition or higher.

T/F: Fail-safe and Time Travel storage requirements do not cost anything?
False. They require additional storage which has associated fees.

How does zero-copy cloning complicate calculating storage usage?
Each clone has its own separate life-cycle so changes made to the original or any clone are independent of each other and are protected through CDP (Constant Data Protection).

T/F: You can only clone an object once.
False. “Clones can be cloned, with no limitations on the number or iterations of clones that can be created.”

What is the latency for the INFORMATION_SCHEMA.TABLE_STORAGE_METRICS view?
Latency may be up to 90 minutes.

Under what conditions would you want a dimension table to be transient with 0 Time Travel rather than a normal permanent table?
High churn large tables (example is 200GB table where every row is updated 20 times per day, the total storage would be just 2TB rather than 32.2TB)

How long are query results persisted?
24 hours

What 7 conditions must be met for results cache to be returned again rather than re-executing a query?

The new query syntactically matches the previous query.
The table data included in the query results has not changed.
The persisted result for the previous query is still available.
The role accessing the cached results has the required privileges.
Any configuration options that affect how the result was produced have not changed.
The query does not include functions that must be evaluated at run time. CURRENT_DATE() is an exception to this as the old result set can simply have the new time applied to it.
The table’s micro-partitions have not changed (been reclustered or consolidated) due to changes to other data in the table that is not used in the query.

What costs are incurred in the parties involved in providing/consuming a share?
Provider incurrs storage costs and compute costs for their own use of the shared resource.
Consumer incurs only compute costs for their use of the shared resource.

How long does it take for a shared resource to be available to a consumer?
Data is immediately available to the consumer.

Once a Provider/Consumer relationship is established via a share between two accounts, what restrictions will impact future sharing?
None. Two accounts can be both Providers and Consumers to each other.

How many databases can a consumer create from a single share?
Only one database can be created and it will be read-only.

Who pays the cost of updating a replicated materialized view?
The target database account.

How do you mitigate the potentially high cost of replicating high-churn tables?

Replicate any primary databases that store such tables at a lower frequency.
Change your data model to reduce churn.

How does replication of a cloned table affect storage cost?
Increases it. When a cloned table is replicated to a secondary database, the physical data is also replicated, thus incurring storage costs on the target account.

What 3 things constitute a Share?

The privileges that grant access to the database(s) and schema containing the objects to share.
The privileges that grant access to the specific objects (tables, secure views, and secure UDFs).
The consumer accounts with which the database and its objects are shared.

Can a shared database be updated from the consumer side?
No, they are read only.

What is Snowflake Data Marketplace?
A web site that is available to all Snowflake customers hosted on non-VPS AWS or Microsoft Azure where data providers and consumers can connect.
Leverages the new web interface.

What is Data Exchange?
“Your own data hub for securely collaborating around data between a selected group of members that you invite.” “Allows you to control who can join, publish, consume, and access data.”
Requires Non-VPS accounts.
Leverages the new web interface.

T/F: a new object created in a shared database is automatically immediately available to consumers.
False. The share consists of the privilege grants to the individual shared objects. Until you grant those privileges, the new object is not shared.

T/F: Dropping and recreating an object (same name) that is shared requires you to regrant privileges to continue sharing it.
True. Recreated objects, even if using the same name as a dropped shared object, are new objects and must be explicitly shared again.

T/F: You can share data across region boundaries.
False. You can only share data to consumers within your region.

T/F: you can share standard (non-secure) views.
False. Only secure views can be shared.

T/F: New rows added to a shared table are immediately available to consumers.
True. The table is shared, not individual rows in the table. Unless they are accessing the table via a secure view that filters the new data.

T/F: You can’t share objects with references to CURRENT_USER or CURRENT_ROLE.
True. The contextual values of these functions have no relevance in the consumer’s account. You won’t get what you think you will get but no error message is displayed. (I just had a NULL result set.)

Who provides support for Reader Accounts?
Reader Accounts have no licensing agreement with Snowflake so support falls to the Provider Account.

What is the difference between a reader identifier used to create a reader account and the Locator or account name used to reference it?
The ID specified when a reader account is created is not the name used to access the account. The account name, also known as the locator, is generated by Snowflake during account creation.

How many reader accounts can a provider create?
The limit is 20. If you reach that limit and need more, contact Snowflake Support.

How soon does dropping a reader account take affect?
All objects created in the account are dropped immediately and the account is immediatly inoperable. This can not be undone.

Which account, provider or consumer, do you use to set up the users, databases, warehouses, monitors, etc?
Consumer. All setup for the managed (reader) accounts must be done from the ADMIN_NAME user established when the managed consumer account was created by the provider.

T/F: All users of a managed account should have ACCOUNTADMIN role.
F. If you do that, they will all have the ability to reconfigure monitors or drop them, thus leaving you, the provider, liable for ridiculous usage credits. All users should be established with the minimum level of privilege necessary.

What are the 3 general limitations for Shared Databases?

The are read only
Unsupported actions include
2.1. Creating a clone of a shared database or any schemas/tables in the database
2.2 Time Travel for a shared database or any schemas/tables in the database
2.3 Editing the comments for a shared database
Shared databases an all objects in them cannot be forwarded (i.e., re-shared with other accounts)

How is querying a shared database different than querying any other database in your account?
There is no difference at all. Just remember the shared data is read only.

What 2 cases result in data egress charges from Snowflake?
Transfer of data from you SF cloud provider to another region of that provider or to another provider. Includes both unloading and replicating data.

What is a first-class object?
An object that has an identity independent of any other item.

What are the 4 properties of Resource Monitors?

Credit Quotas. Number of credits allocated to the monitor for the specified frequency interval. Does not account for the 10% daily adjustment for cloud services.
Monitor Level. Specifies whether the resource monitor is specific to all warehouses in the account or a specific set of individual warehouses.
Schedule. Specifies the interval at which usage is reset to 0. Can be Daily, Weekly, Monthly, Yearly, or Never.
Actions. Also called Triggers. Specify a threshold (as a % of monitor credit quote) and the action to take when that threshold is reached.

What is Cloud Services Usage billing?
Billing for actual Cloud Services usage above 10% of the consumed daily virtual warehouse compute. Now the Cloud Services layer will operate on the same pay-for-what-you-use basis as the other two layers.

How is Snowflake related to other architectures?
Snowflake is a hybrid of traditional shared-disk and shared-nothing architectures.

What are the 6 categories of partners in the Snowflake Ecosystem?
Data Integration – ETL

SQL Development & Management – alternatives to SnowSQL & the Snowflake worksheet UI

Security & Governance – data masking, cataloging; data quality & metadata

Business Intelligence – analyzing, discovering, visualizing (typically on the more immature (descriptive and diagnostic) end of the BI continuum

ML & Data Science – Advanced analytics / AI. The more mature (predictive & prescriptive) end of the BI continuum

Programmatic – programming language interfaces

What are the 3 Snowflake Stage Types
Table
User
Named
Internal
External

When did additional billing for Cloud Service Layer become a thing?
New as of Feb 2020.

When can warehouses be started or stopped?
They can be started or stopped at any time.

What does warehouse size specify?
Size refers to the number of servers that comprise each cluster in a warehouse.

What is the default size for a warehouse added via SQL?
The default size for CREATE WAREHOUSE is X-Small.

What is the relationship between servers/cluster and credits/hour?
There is a one-to-one correspondence.

By what increment does warehouse size increment?
In terms of the number of servers, each size is one power of two greater than the next smallest: 1,2,4,8,16,32,64,128.

How is warehouse usage billed?
It is billed at 60 seconds each time the warehouse started or resized to a large size, plus by the second for each second thereafter. In the event of the resizing, you are billed the minimum 60 seconds for only the additional servers provisioned.

T/F: Increasing the size of a warehouse will improve data loading performance.
False. Load performance is influenced more by the number of files being loaded and their size than by the size of the warehouse.

When does it make sense to use a larger warehouse for loading data?
When you are loading a large number of files concurrently. Even then, improving load performance is not hugely impacted by the warehouse size.

What impact does warehouse size have on query processing?
Larger more complex queries will run faster with a larger warehouse. Generally, query performance scales linearly with warehouse size.

How is multi-clustering (Enterprise Edition and up) impacted by auto-suspense?
Auto-suspend happens only when the cluster has scaled down to the specified minimum number of clusters and there is no activity for the specified period of time.

How is multi-clustering (Enterprise Edition and up) impacted by auto-resume?
Auto-resume happens only when the entire warehouse is suspended (no clusters are running).

What determines the number of queries a warehouse can concurrently process?
The size and complexity of each query.

What happens when the size and complexity of queries outstrips a warehouse’s capability?
Queries are queued, pending resources coming available as other queries complete.

What object level parameters control query processing and concurrency?
STATEMENT_QUEUED_TIMEOUT_IN_SECONDS (no default) and
STATEMENT_TIMEOUT_IN_SECONDS (2 day default, 7 day max)

If you don’t have Enterprise Edition’s multi-cluster warehouses, how do you deal with warehouse queuing?
Create another warehouse and manually direct queries to it. Increasing the size of the warehouse will have only limited impact on concurrency and queuing.

What does increasing warehouse size get you?
Improved query performance.

How does multi-clustering in Enterprise Edition impact warehouse queuing?
New clusters are spun up (up to the max defined) to deal with the load. No manual redirecting of queries is necessary.

What are the respective impacts of scaling up vs scaling out?
Scaling up by resizing a warehouse improves query performance. It will have a small impact on concurrency but scaling up is not intended as a solution to queuing.

Scaling out by creating new warehouses and manually redirecting queries or through multi-clustering improves concurrency.

When will a warehouse not automatically start up?
When no warehouse has been assigned to the session, either explicitly or by the user’s default warehouse setting.

What is the precedence for determining which warehouse to us for a session?

Default warehouse for the user.
Default warehouse in the configuration file for the client utility.
Default warehouse specified on the command line or through the driver/connector parameters.

What about a query or the data it is accessing make it complex and compute intensive?

The overall size of the tables being queried has more impact that the number of rows.
Query filtering predicates and the number of joins/tables in the query.

What impact does executing queries of widely-varying size and/or complexity on the same warehouse have on analyzing warehouse load?
It makes it more difficult. Don’t point simple queries of small tables at the same warehouse you point complex queries of large tables.

What are the two most important factors to consider from a cost and performance perspective when creating a warehouse?
Warehouse size (number of servers per cluster)
Manual vs automated management (for starting/resuming and suspending warehouses).
If you’re using Enterprise Edition, the number of clusters in the warehouse is also important.

When might you want to consider disabling auto-suspend for a warehouse?

You have a heavy, sustained workload for the warehouse.
You require the warehouse to be available without delay.

How long does server provisioning typically take when starting or resuming a warehouse?
1-2 seconds; however the size of the warehouse and the availability of servers can force it to take longer.

Why would you want to disable auto-resume for a warehouse?
If you want to manually resume the warehouse to control costs and/or user access.

What happens to warehouse cache when you decrease the size of a warehouse?
Warehouse cache associated with the dropped servers is dropped.

What is the SQL statement for resuming or starting a warehouse?
ALTER WAREHOUSE RESUME

If a warehouse resumes but not at the specified number of servers (a rare instance) what is the impact on compute billing?
You are billed for the servers actually running in a cluster, not for the servers defined in the cluster.

If you reduce the size of a warehouse, when are servers actually removed from the warehouse?
Only when they are no longer being used.

If you resize a suspended warehouse, when are the servers actually added or removed?
When the warehouse is next resumed.

How many warehouses can be assigned to a session at once?
Only one. That one can be changed at anytime using the USE WAREHOUSE command.

What impact does running queries with a variety of complexity or table sizes on the same warehouse have on the Monitoring Warehouse Load charts.
Counts of queries don’t consider query complexity–Monitor assumes all queries are equal. If they aren’t, your path to optimizing your warehouses will be unclear leaving you to either endure queuing (poor performance) or high costs.

How is warehouse Query Load calculated?
Divide the execution time in seconds of all queries run by the warehouse in an interval by the total time (in seconds) for the interval.

Working with warehouse load monitoring, what indicates the use of a warehouse is inefficient?
Daily workload over 2 weeks shows recurring time periods where the warehouse was running but the total query load is less than 1 for substantial periods.

What can you do to resolve inefficient warehouse usage issues?

Decrease the warehouse size. This will generally increase execution time.
For a multi-cluster warehouse, decrease the MIN_CLUSTER_COUNT parameter value (lower the value for minimum clusters parameter).

How are credits charged for a warehouse?
Calculation is based on

number of servers per cluster (size)
number of clusters (if using multi-cluster warehouses)
length of time each server in each cluster runs.

What is warehouse cache?
It contains a copy of data access as queries are processed by the warehouse. Subsequent queries may use this cache instead of query underlying tables, thus being quicker.

What is the difference between warehouse cache and results cache?
Warehouse cache is stored in the Warehouse Layer and requires compute to access.
Results cache is stored in the Services Layer and doesn’t require compute to access.
Both can improve query results.

Who pays for serverless features?
They are billed to the customer on a separate line from virtual warehouse usage.

What are the 4 Serverless Features?

Tables – Automatic clustering
Views – Materialized view maintenance
Data loading – Snowpipe
Database replication – DB replication and failover/failback.

What encryption is used for stages?
Defaults AES standard (128 bit); can be set to strong (256 bit) by parameter.

T/F: Only tables can be transient. Neither schemas, databases, nor stages can be.
False. The TRANSIENT parameter can be included on the creation of tables, schemas, databases, and stages.

Which of the following can be replicated?
A) Databases
B) Users & Roles
D) Warehouses & Resource Monitors
E) Shares
A. Currently databases are the only type of objects that can be replicated.

Which type of table cannot be replicated?
A)Permanent
B) Transient
C) Temporary
D) External
E) B and D
D. Creating or refreshing a replicated secondary db is blocked if an external table exists in the primary database. Plans are to allow external table replication in a future version.

T/F: Privileges are replicated to the secondary database.
False. Privileges granted on db objects are not replicated to a secondary replicated db.

T/F: Business Critical and higher editions can never be replicated to lower edition accounts.
F: When executing the ALTER DATABASE … ENABLE REPLICATION TO ACCOUNTS statement, if IGNORE EDITION CHECK is set, the primary database can be replicated to the specified accounts on any edition.

T/F: DBs created from shares can be replicated.
False. DBs created from shares cannot be replicated.

T/F: Secondary DBs are never reclustered, they just always have the clustering done (or redone) in the primary.
False. If the secondary DB contains clustered tables and the DB is promoted to become a primary db (it is cloned), SF begins Auto Clustering the tables in that DB and suspends monitoring clustering in the previous primary db.

T/F: The data in Materialized Views is automatically updated in the secondary db when the replication is refreshed.
False. By default, only the definitions of MVs are updated on refresh. To perform automatic maint on MVs in a secondary DB, explicitly set AUTO_REFRESH_MATERIALIZED_VIEWS_ON_SECONDARY = TRUE. On the secondary db MVs. The secondary db account is billed for the MV refresh.

T/F: Time Travel queries of a primary and secondary (replicated) db will always deliver the same results.
False. Replication does not replicate table history, only the latest version of the table is replicated.

T/F: Replicating a db with a cloned table physically copies data to the secondary db so the cloned table storage charges are incurred on the secondary account.
True. Replicating physically copies data even if the source is a cloned object.

T/F: Having any views in a replicated db that reference tables in other non-replicated DBs will prevent the replication from happening.
False. Only if the views are Materialized. Normal views will be copied but if the other db is not also replicated, execution of the view will fail because the table won’t exist.

T/F: For billing purposes, if a cluster is running, all servers in the cluster are assumed to be running.
True. You are billed for all servers in a cluster when the cluster is running.

T/F: Data load performance is proportional to the size of the warehouse.
False. Increasing warehouse size does not always improve load performance. The number and size of the input files impact load performance more.

What is the optimal file size for getting the best data load performance?
A) 5MB – 10MB
B) < 100MB
C) 10MB to 100MB
D) File size doesn’t matter because multi-cluster warehouses can handle unlimited file sizes.
C. If the file is bigger than 100MB, split it.

Each server in a warehouse has:
A) an internal time that tracks when it started
B) 4GB of RAM
C) A static position assignment in the cluster
D) 4 Cores
A & C

The size of warehouse cache is driven by
A) The size of the data database(s) it accesses
B) When the cluster was provisioned
C) The size of the cluster
D) The Snowflake edition it is running in
C

T/F: Snowflake supports shared user ownership of securable objects.
False in the sense that users don’t own objects, roles do.
True in the sense that users who have roles that own objects all share ownership of those objects.

Custom roles should all be assigned directly or indirectly (via a hierarchy) to:
A) ACCOUNTADMIN
B) SECURITYADMIN
C) USERADMIN
D) SYSADMIN
D) SYSADMIN

A stream is:
A) the same as a pipe
B) a collection of DML changes to tables
B) a collection of DML changes to a table

A task object is:
A) Part of a Snowpipe
B) Part of Snowflake’s ETL tool to get data from Oracle source systems
C) Defines a recurring schedule for executing SQL statements, including a call to a stored procedure
C) Defines a recurring schedule for executing SQL statements, including a call to a stored procedure. There is no specific Snowflake tool for getting data from Oracle.

When will a task scheduled to run at 1:00AM run twice within one day?
On the day of the Autumn daylight saving time change because 1:00AM happens twice.

T/F: The SECURITYADMIN and SYSADMIN roles are separate from and unrelated to the ACCOUNTADMIN role.
False. Both roles are owned by ACCOUNTADMIN.

What are the 7 precautions that should be followed with regard to the ACCOUNTADMIN role?

Assign the role only to a limited and carefully selected number of people
Users with ACCOUNTADMIN should be required to use MFA
Assign this role to at least two users
Associate an actual person’s email address to the ACCOUNTADMIN users so that SF knows who to contact in an urgent situation
Avoid using the role to create objects
Avoid using the role for automated scripts
It should never be designated as a user’s default role

T/F: Privileges granted to lower level roles in a hierarchy are automatically inherited by higher level roles.
True, the parent roles inherit the children privileges without them being explicitly granted.

T/F: If you start, stop, and restart a warehouse all within 60 seconds, you are charged 60 seconds of credits for each start.
False. Stopping and restarting a warehouse within the first minute does not change the amount billed.

How is Cloud Services usage billed?
Snowflake credits are used to pay for the usage of the cloud services that exceeds 10% of the daily usage of the compute resources.

T/F: Usage of Cloud Services credits can never be tracked back to the warehouses, databases, or users.
False. The snowflake.account_usage.query_history has all of those attributes plus a credits_used_cloud_service attribute.

T/F: A connection and a session refer to the same concept.
False. When logged into SF, one or more sessions can be created. Sessions are only terminated if the user explicitly does so or the session times out after 4 hours of inactivity. Disconnecting from SF does not terminate active sessions.

T/F: Resource monitors monitor and control serverless feature use just like they do warehouse use.
False. Serverless feature use are not covered by any monitors.

What are the three types of Parameters?

Account
Session
Object

When is the password policy not enforced?

When a user is created and the password is first set.

What two SQL statements must be used to create a user with a default role and assign the user that role?
CREATE USER test_user DEFAULT_ROLE = “SYSADMIN”
and
GRANT ROLE “SYSADMIN” TO USER test_user

If a user login has failed 5 consecutive times, their account is locked for 15 minutes. What SQL command do you use to re-enable their account?
ALTER USER janesmith SET mins_to_unlock = 0;

T/F: All Snowflake editions come with Premier Support.
True, effective May1, 2020

T/F: Patch releases follow the same staged release approach used for new releases.
False. For patch releases, all accounts are moved to the patch release on the same day.

When defining a multi-column clustering key, the columns should be ordered:
A) Lowest cardinality to highest cardinality
B) Highest cardinality to lowest cardinality
A) Lowest to highest.

T/F: When defining a clustering key, you should use an expression to limit really high cardinality columns.
True. TO_DATE if the data time is timestamp.

T/F: An existing clustering key is copied when a table is created using CREATE TABLE … CLONE.
True. Cloning a table copies the clustering key.

T/F: An existing clustering key is propagated when a table is created using CREATE TABLE … LIKE
False. Only cloning a table will copy the clustering key.

T/F: An existing clustering key is supported when a table is created using CREATE TABLE … AS SELECT.
False. However, you can define the clustering key after the table is created.

T/F: Defining a clustering key directly on top of VARIANT columns is not supported.
True. However, you can specify a VARIANT column in a clustering key if you provide an expression consisting of the path and target type.

T/F: All valid expressions on a column can be used in the creation of a clustering key.
False. Some column functions are not allowed in clustering key definitions.

T/F: A table will be reclustered starting immediately after the clustering key is defined or changed.
False. Snowlake only reclusters a clustered table if it will benefit from the operation.

T/F: Custering or reclustering has no impact on storage charges.
False. Time Travel and Failsafe require the micro-partitions as they originally were to be created for perhaps as long as 97 days, depending on edition and table setting.

T/F: Automatic clustering blocks DML statements issued against a table while it is being reclustered.
False.

T/F: Just as tables, stages can be created to be temporary as well.
True.

Which of the following can have the TRANSIENT keyword in their create statements?
A) Table
B) Schema
C) Database
D) User
A, B, & C

When should you consider adding Search Optimization to a table?

Table size at least 100GB
Table is not clustered or is frequently queried on columns other than the cluster key.
Query typically runs for tens of seconds.
At least one of the access columns has at least 100K-200K distinct values.
Query uses equality or IN predicates

T/F: A materialized view can only query a single table.
True. A materialized view can only query a single table; joins are not supported.

The following aggregate functions can be used in materialized views:
A) AVG
B) MODE
C) MEDIAN
D) STDDEV
E) All of the above
A & D. Only 14 of the valid SF aggregate functions are allowed in materialized views.