Practice Set 2: Snowpro Certification Practice Set with more than 250 Unique Questions
Snowflake SNOWPRO Core Certification Practice Set Questions:
Question 1: Correct
The compute resource
used by Snowflake for data loading jobs can be provided by:
·
User managed virtual warehouse
(Correct)
·
Snowflake managed service
(Correct)
·
Hardware provisioned by user directly from cloud providers
Explanation
When loading using
SNOWPIPE, the compute is provided by Snowflake managed service and for any
other loading it is provided by user managed virtual warehouse
Question 2: Incorrect
Please choose the
correct statements about Micro-Partitions (Select 2)
·
Snowflake automatically determines the most efficient
compression algorithm for the columns in each micro-partition.
(Correct)
·
Micro-partitions can't overlap in their range of values
·
Micro-partitions are small in size (50 to 500 MB, after
compression)
(Incorrect)
·
Snowflake micro-partitions are derived automatically
(Correct)
Explanation
Snowflake takes care
of all the compression algorithm. Micro-partitions can overlap in their range
of values, which, combined with their uniformly small size, helps prevent skew.
The micro-partitions are small in size (50 to 500 MB, BEFORE compression).
Question 3: Correct
How can you disable
auto-suspend for a warehouse?
·
You can not disable AUTO SUSPEND in standard warehouse
·
Specifying NULL in SQL
(Correct)
·
You can not disable AUTO SUSPEND in multi-cluster warehouse
·
Using UI, selecting NEVER against AUTO SUSPEND
(Correct)
Explanation
To disable
auto-suspend, you must explicitly select Never in the web interface or specify
NULL in SQL.
Question 4: Correct
Scaling up can be
configured as automated process.
·
FALSE
(Correct)
·
TRUE
Explanation
Scaling Up is a manual
process as of now.
Question 5: Correct
Snowflake Storage
layer is a Cloud Storage layer. It depends on which cloud provider you are
using. Select the current available cloud providers.
·
Azure Cloud
(Correct)
·
AWS Cloud
(Correct)
·
IBM Cloud
·
GCP
(Correct)
·
Oracle Cloud
Explanation
As of now, AWS, AZURE
and GCP are three cloud providers which provide Cloud Storage layer for
Snowflake.
Question 6: Correct
Monica mistakenly
dropped a table T1 last week. The database has Time-Travel retention period set
to 90 days. How can Monica recover the table which she dropped last week.
·
Monica cant recover it from TIME-TRAVEL as the table T1 moved to
Fail-safe
·
Monica should contact Salesforce support to get it done
·
Monica can execute UNDROP TABLE T1 command after setting up the
right context for Database and schema
(Correct)
·
Monica can use the UI > ACCOUNT menu to recover the table T1
Explanation
UNDROP TABLE command
helps recover the TABLE which is still in TIME-TRAVEL. Since, Monica dropped
last week so it is just a week old drop which will be available in the Time
Travel with retention period of 90 days.
Question 7: Correct
Snowflake compute
costs depend on which of the following? (Select 2)
·
The total number of warehouses in the account.
·
The amount of time warehouses have run.
(Correct)
·
The number of rows returned in queries.
·
The sizes of running warehouses.
(Correct)
Explanation
Compute cost depends
on the Warehouse Size and the time it was in started mode or running
Question 8: Correct
Increasing the size of
a warehouse always improves data loading performance.
·
FALSE
(Correct)
·
TRUE
Explanation
Increasing the size of
a warehouse does not always improve data loading performance. Data loading
performance is influenced more by the number of files being loaded (and the
size of each file) than the size of the warehouse.
Question 9: Correct
How much is the
Time-Travel retention period of Transient Table?
·
7 days
·
0 days
·
1 day
(Correct)
·
90 days
Explanation
Transient Table has
maximum of 1 day Time-Travel retention period
Question 10: Incorrect
A multi-cluster
virtual warehouse is Maximized when
·
MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 1
·
Minimum and Maximum number of clusters are specified
differently.
(Incorrect)
·
Minimum number of clusters and Maximum number of clusters are
same and must be specified with a value of more than 1.
(Correct)
·
MIN_CLUSTER_COUNT = 3 MAX_CLUSTER_COUNT = 3
(Correct)
Explanation
Maximized mode is
enabled by specifying the same value for both maximum and minimum clusters
(note that the specified value must be larger than 1). In this mode, when the
warehouse is started, Snowflake starts all the clusters so that maximum
resources are available while the warehouse is running.
Question 11: Correct
Only a Snowflake
employee can recover data from fail-safe storage.
·
FALSE
·
TRUE
(Correct)
Explanation
Fail-Safe data can't
be queried by customer. Customer needs to contact Snowflake support to recover
data from fail-safe storage.
Question 12: Correct
If you are
ACCOUNTADMIN, you can query and recover data from Fail-safe without the help of
Snowflake support. (TRUE/FALSE)
·
FALSE
(Correct)
·
TRUE
Explanation
Only Snowflake Support
can recover the data from Fail-safe.
Question 13: Correct
A multi-cluster
warehouse is using an ECONOMY scaling policy, how long will queries back up in
the queue before another cluster is started?
·
It will start immediately
·
1 minute
·
8 Minutes
·
2 Minutes
·
6 Minutes
(Correct)
Explanation
If a multi-cluster
warehouse is configured with SCALING policy as ECONOMY it starts cluster only
if the system estimates there’s enough query load to keep the cluster busy for
at least 6 minutes.
Question 14: Correct
You have a LARGE sized
warehouse 'MY_WAREHOUSE'. You want to change the size to SMALL. Which SQL
statement will help you to change the size of warehouse?
·
ALTER MY_WAREHOUSE SET SIZE = "SMALL";
·
ALTER WAREHOUSE MY_WAREHOUSE SET WAREHOUSE_SIZE =
"SMALL";
(Correct)
·
ALTER WAREHOUSE MY_WAREHOUSE SET SIZE = "SMALL";
·
UPDATE MY_WAREHOUSE SET SIZE = "SMALL";
Explanation
ALTER WAREHOUSE
command can help change the warehouse size
Question 15: Correct
John wants to create a
multi-cluster warehouse and wants to make sure that the virtual warehouse
starts the additional clusters only if the system estimates there's enough
query load to keep the cluster busy for at least 6 minutes. How should he
configure the Warehouse?
·
Snowflake takes care of this automatically so, John does not
have to worry about it
·
Set the SCALING POLICY as STANDARD
·
Set the SCALING POLICY as ECONOMY
(Correct)
·
Configure as SCALE-MAX so that the warehouse is always using
maximum number of specified clusters
Explanation
If a multi-cluster
warehouse is configured with SCALING policy as ECONOMY it starts cluster only
if the system estimates there’s enough query load to keep the cluster busy for
at least 6 minutes.
Question 16: Correct
Which is the correct
syntax to disable fail safe on a table?
·
ALTER TABLE SET FAILSAFE_DAYS = 0;
·
Fail safe can not be disabled on a table.
(Correct)
·
ALTER TABLE SET ENABLE_FAILSAFE = FALSE;
·
By default, fail safe is disabled on a table.
·
ALTER TABLE DROP FAILSAFE;
Explanation
Fail safe can not be
disabled.
Question 17: Correct
If a server in
warehouse cluster runs for 64 seconds, shuts down, and then restarts and runs
for less than 60 seconds, for how many seconds it will be billed?
·
64 Seconds
·
124 Seconds
(Correct)
·
4 Seconds
·
120 Seconds
Explanation
The minimum billing
charge for provisioning a server is 1 minute (i.e. 60 seconds). So, the total
billing will be for 60 + 4+ 60 = 124 seconds.
Question 18: Correct
John wants to create a
Warehouse which can auto resume whenever there is a new load / query to
execute. How should he configure the warehouse?
·
It is always manual and cant be automated.
·
AUTO_SUSPEND = TRUE
·
AUTO_RESUME = 1
·
AUTO_RESUME = TRUE
(Correct)
Explanation
AUTO_RESUME = TRUE
resumes the warehouse automatically whenever there is new load or query needing
a warehouse.
Question 19: Correct
How much is the
Time-Travel retention period of Permanent Table (Standard Edition)?
·
0 days
·
90 days
·
7 days
·
1 day
(Correct)
Explanation
Permanent Table has
maximum of 1 day for Standard Edition and maximum of 90 days Time-Travel
retention period for all other Snowflake editions.
Question 20: Correct
Which Snowflake
Editions have Elastic Data Warehousing. Check all the Snowflake editions that
have Elastic Data Warehousing enabled.
·
Virtual Private Snowflake
(Correct)
·
Standard
·
Business Critical Edition
(Correct)
·
Premium
·
Enterprise
(Correct)
Explanation
Standard Edition only
has Single Compute Cluster. Premium is not a Snowflake edition. All other
editions have multi clusters capability.
Question 21: Correct
Auto-suspend and
auto-resume apply only to the entire warehouse and not to the individual
clusters in the warehouse.
·
FALSE
·
TRUE
(Correct)
Explanation
TRUE for both type of
STANDARD and MULTI-CLUSTER Warehouses. For a multi-cluster warehouse : -
Auto-suspend only occurs when the minimum number of clusters is running and
there is no activity for the specified period of time. The minimum is typically
1 (cluster), but could be more than 1. - Auto-resume only applies when the
entire warehouse is suspended (i.e. no clusters are running).
Question 22: Correct
John has a same query
which runs for around 40 minutes every hour. Most of the time the data in the
table remain same. What could be best option for John to keep warehouse running
or AUTO_SUSPEND?
·
John should not set AUTO_SUSPEND
(Correct)
·
John should set AUTO_SUSPEND to 40 minutes
·
John should set AUTO_SUSPEND to 10 minutes
·
John doesn't have to set AUTO_SUSPEND. Snowflake will take care
of suspension as needed.
Explanation
It is better to not
suspend the warehouse as the cache gets drop on suspension. The query is same
and table data is not changing frequently so it is better to utilize the
warehouse cache as much as possible.
Question 23: Correct
Is there any cost
benefit of suspending a warehouse in less than 60 seconds?
·
YES
·
NO
(Correct)
Explanation
The minimum billing
charge for provisioning a server is 1 minute (i.e. 60 seconds). - There is no
benefit to stopping a warehouse before the first 60-second period is over
because the credits have already been billed for that period. - After the first
60 seconds, all subsequent billing for a running server is per-second (until
the server shuts down).
Question 24: Correct
Which are the types of
Virtual Warehouses in Snowflake?
·
Standard
(Correct)
·
Premier
·
Enterprise
·
Multi-Cluster
(Correct)
Explanation
There are two types of
Warehouses: 1- Standard Warehouse - It is type of single compute cluster
warehouse 2 - It can spawn additional compute clusters (scale out) to manage
changes in user and concurrency needs
Question 25: Correct
Which of the following
database objects can be cloned in Snowflake?
·
Databases
·
All the editions
(Correct)
·
Tables
·
Schemas
·
File Formats
Explanation
Databases, Schemas,
Tables, External Stages, File Formats, Sequences all can be cloned.
Question 26: Correct
Which Snowflake Object
is a named wrapper around a cluster of servers with CPU, memory, and disk
·
Database
·
Storage
·
Virtual Warehouse
(Correct)
·
Schema
Explanation
Virtual Warehouse is
the snowflake object which is a named wrapper around a cluster of servers with
CPU, memory and disk. It's Snowflake who managed all the complex setup behind
the scene. User does not need to configure EC2 instance, physical servers or anything
like that.
Question 27: Correct
Monica is a new
Snowflake user with ACCOUNTADMIN role. She wants to create a warehouse which
can scale out if needed. She tried to create the warehouse using UI but she is
not able to find the options to set Minimum and Maximum Clusters. What could be
the issue?
·
She doesn't have enough privilege to create a warehouse
·
She should ALTER any existing warehouse to set the Maximum and
Minimum clusters
·
She is using STANDARD Snowflake edition. Multi-Cluster is not
available in STANDARD edition.
(Correct)
·
She should use SQL command to create the Warehouse with Minimum
and Maximum clusters
Explanation
Multi-cluster feature
is not available in Standard edition. It starts from Enterprise Edition.
Question 28: Correct
When there is new load
of data in Snowflake, Snowflake automatically analyzes and compresses data into
table.
·
TRUE
(Correct)
·
FALSE
Explanation
Snowflake
automatically analyzes and compresses data into table on load. It finds the
optimal compression scheme for each data type.
Question 29: Correct
Scaling a Warehouse DOWN
will decrease the number of servers (e.g., Large to Medium).
·
TRUE
(Correct)
·
FALSE
Explanation
Scaling down means
reduce the Size of Virtual Warehouse by reducing number of servers.
Question 30: Correct
What is Clustering
Depth?
·
It can be used to determine whether a large table would benefit
from explicitly defining a clustering key
(Correct)
·
It is the total number of micro-partitions that comprise the
table
·
The bigger the average depth, the better clustered the table
·
The depth of the overlapping micro-partitions
(Correct)
Explanation
The clustering depth
for a populated table measures the average depth (1 or greater) of the
overlapping micro-partitions for specified columns in a table. The smaller the
average depth, the better clustered the table is with regards to the specified
columns. Clustering depth can be used for a variety of purposes, including: -
Monitoring the clustering “health” of a large table, particularly over time as
DML is performed on the table. - Determining whether a large table would
benefit from explicitly defining a clustering key.
Question 31: Correct
Two Virtual Warehouses
can access the same data at the same time without causing contention issues.
·
FALSE
·
TRUE
(Correct)
Explanation
Two different
warehouses are to different compute engine and due to unique architecture of
Snowflake, there will be no contention issue.
Question 32: Correct
The columns in the
query history include the QueryID, the SQL Text, the Warehouse name, the
Warehouse Size, the Session ID and others. Which column is a good indicator of
whether a Warehouse was used (and Compute costs incurred) by a query?
·
Session ID
·
Size
(Correct)
·
Warehouse Name
·
QueryID
Explanation
If SIZE column is
blank that means the query didn't consume compute credit and returned the
result from cache or handled by Cloud Services Layer.
Question 33: Correct
Which type of Tables
has 7 days Fail-Safe period.
·
Permanent
(Correct)
·
Temporary
·
Transient
·
External
Explanation
Only Permanent Table
retains 7 days of Fail-Safe.
Question 34: Correct
You want a list of all
the warehouses with name starting from DEMO. Select the correct SQL statement.
·
SHOW WAREHOUSES LIKE 'DEMO%';
(Correct)
·
SHOW WAREHOUSE LIKE '%DEMO';
·
SHOW WAREHOUSES LIKE '%DEMO';
·
LIST WAREHOUSES LIKE '%DEMO';
Explanation
SHOW WAREHOUSES is the
right command and you can add LIKE as well to narrow the list.
Question 35: Correct
Some compute occurs in
the cloud services layer. When customer is charged for compute which occurred
in the cloud services layer?
·
Customers are charged for cloud computing that exceeds 10% of
total compute costs for the account
(Correct)
·
There is no charge to customer for cloud services layer
·
Customers are charged for cloud computing that exceeds 10% of
total storage costs for the account
·
Customers are charged for cloud computing that exceeds 50% of
total compute costs for the account
Explanation
Usage for
cloud-services is charged only if the daily consumption of cloud services
exceeds 10% of the daily usage of the compute resources. The charge is
calculated daily (in the UTC time zone). This ensures that the 10% adjustment
is accurately applied each day, at the credit price for that day.
Question 36: Correct
Select the right SQL
statement which creates a Large Sized Warehouse and can spin new clusters if
needed up to 5 and auto suspend in 10 minutes if not in use.
·
CREATE WAREHOUSE MY_WAREHOUSE WITH WAREHOUSE_SIZE = 'LARGE'
WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = 10 AUTO_RESUME = TRUE
MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 5 SCALING_POLICY = 'STANDARD';
·
CREATE WAREHOUSE MY_WAREHOUSE WITH WAREHOUSE_SIZE = 'XLARGE'
WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = 600 AUTO_RESUME = TRUE
MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 5 SCALING_POLICY = 'STANDARD';
·
CREATE WAREHOUSE MY_WAREHOUSE WITH WAREHOUSE_SIZE = 'LARGE'
WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = NULL AUTO_RESUME = TRUE
MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 5 SCALING_POLICY = 'STANDARD';
·
CREATE WAREHOUSE MY_WAREHOUSE WITH WAREHOUSE_SIZE = 'LARGE'
WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = 600 AUTO_RESUME = TRUE
MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 5 SCALING_POLICY = 'STANDARD';
(Correct)
Explanation
Option 1 is not
correct and the AUTO_SUSPEND is set to NULL Option 2 is not correct because the
SIZE is XLARGE Option 3 is the correct SQL statement Option 4 is not correct
because the AUTO_SUSPEND is defined for 10 SECONDS NOT 10 minutes.
Question 37: Correct
John wants to create a
Warehouse which can AUTO_SUSPEND in 3 Minutes. There is no option in UI where
he can select 3 Minutes. What will you suggest him to do?
·
It is not possible to create a warehouse with AUTO_SUSPEND less
than 5 Minutes
·
He should contact Snowflake Support to get it created
·
He can create the Warehouse using SQL command with AUTO_SUSPEND
= 180
(Correct)
·
He should select Size as X-SMALL and then he will be able to set
AUTO_SUSPEND to 3 Minutes
Explanation
The minimum time for
AUTO_SUSPEND possible using UI is 5 Minutes. Using SQL Command John can set the
AUTO_SUSPEND for 3 Minutes as - CREATE OR REPLACE WAREHOUSE MY_WAREHOUSE WITH
WAREHOUSE_SIZE = 'LARGE' WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = 180
AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 2 SCALING_POLICY =
'STANDARD';
Question 38: Correct
Who owns and manages
the cloud storage layer hosted on Cloud Providers?
·
Cloud Provider
·
Customer
·
Snowflake
(Correct)
Explanation
Snowflake owned and
manages the Cloud Storage layers for all the Snowflake services for all of its
customers.
Question 39: Correct
What is the best use
of Multi-Cluster Warehouse?
·
Best utilized for scaling resources to improve performance of
slow-running queries.
·
Best utilized for scaling resources to improve concurrency for
users/queries.
(Correct)
Explanation
Multi-cluster
warehouses are best utilized for scaling resources to improve concurrency for
users/queries. They are not as beneficial for improving the performance of
slow-running queries or data loading. For these types of operations, resizing
the warehouse provides more benefits.
Question 40: Correct
A 3X-LARGE cluster
contains 64 Servers. if a 3X-Large multi-cluster warehouse runs 1 cluster for
one full hour and then runs 3 clusters for the next full hour. How many credits
will be billed?
·
512
·
212
·
128
·
256
(Correct)
Explanation
3X-LARGE contains 64
Server per cluster and uses 64 credits per cluster per hour. So, total credit
will be used = (64 x 1) + (64 x 3) = 256.
Question 41: Correct
Virtual Warehouse in
Snowflake refers to
·
Database
·
Compute Engine
(Correct)
·
Data Warehouse
·
Virtual Storage
Explanation
Virtual Warehouse
refers to compute engines which executes query in Snowflake.
Question 42: Correct
What is the default
Time-Travel retention period of Enterprise Edition?
·
0 days
·
1 day
(Correct)
·
90 days
·
7 days
Explanation
The default retention
period enabled for all the Snowflake editions is 1 day (24 hours)
Question 43: Correct
How many virtual
warehouses can be created in a Snowflake Account?
·
4
·
16
·
As many as needed
(Correct)
·
8
Explanation
There is no limit. A
customer can create as many as need.
Question 44: Incorrect
Which SQL statements
can work on a cloned Table?
·
SHOW command
·
All of these
(Correct)
·
SELECT command
(Incorrect)
·
DROP table command
Explanation
All of these commands
work with Cloned table as normal table.
Question 45: Correct
Scaling up is intended
for handling concurrency issues dure to more users or more queries
·
TRUE
·
FASE
(Correct)
Explanation
Scaling Up is intended
to handle Performance for complex queries not to handle concurrency.
Question 46: Correct
Scaling Out is an
automated process (e.g., Min clusters to max clusters)
·
FALSE
·
TRUE
(Correct)
Explanation
Snowflake Editions
except Standard has multi clusters features and it is an automated process. The
minimum and maximum number of clusters are specified while configuring the
Virtual Warehouse.
Question 47: Correct
Select the Snowflake
edition that automatic encryptions all the data.?
·
VPS
·
All the editions
(Correct)
·
Business Critical
·
Standard
·
Enterprise
Explanation
All of the Snowflake
editions automatically encrypt data in rest and in motion.
Question 48: Correct
When a database or
schema is cloned. What object is not cloned?
·
Internal Named Stages
(Correct)
·
Sequences
·
Pipes
·
Internal Stages
·
Stored Procedures
Explanation
The following rules
apply to cloning stages or objects that contain stages (i.e. databases and
schemas): - Individual external named stages can be cloned; internal named
stages cannot be cloned. - When cloning a database or schema: --External named
stages that were present in the source when the cloning operation started are
cloned. --Tables are cloned, which means their internal stages are also cloned.
--Internal named stages are not cloned. Regardless of how a stage was cloned,
the clone does not include any of the files from the source. i.e. all cloned
stages are empty.
Question 49: Correct
What are the key
considerations for using warehouse effectively and efficiently?
·
Start with smallest warehouse always and Scale up if the
performance is poor
·
Don’t focus on warehouse size. Snowflake utilizes per-second
billing, so you can run larger warehouses (Large, X-Large, 2X-Large, etc.) and
simply suspend them when not in use
(Correct)
·
Experiment with different types of queries and different warehouse
sizes to determine the combinations that best meet your specific query needs
and workload.
(Correct)
·
Snowflake utilizes per-hour billing so, customer doesn't have to
worry about the warehouse size.
Explanation
The keys to using
warehouses effectively and efficiently are: - Experiment with different types
of queries and different warehouse sizes to determine the combinations that
best meet your specific query needs and workload. - Don’t focus on warehouse
size. Snowflake utilizes per-second billing, so you can run larger warehouses
(Large, X-Large, 2X-Large, etc.) and simply suspend them when not in use.
Question 50: Correct
Snowflake does not
begin executing SQL statements submitted to a warehouse until all of the
servers for the warehouse are successfully provisioned. If any server fails to
provision, Snowflake attempts to repair the failed server. The warehouse starts
processing statement once 100% of the requested servers are successfully
provisioned.
·
FALSE
(Correct)
·
TRUE
Explanation
Snowflake does not
begin executing SQL statements submitted to a warehouse until all of the
servers for the warehouse are successfully provisioned, unless any of the
servers fail to provision: - If any of the servers for the warehouse fail to
provision during start-up, Snowflake attempts to repair the failed server(s). -
During the repair process, the warehouse starts processing SQL statements once
50% or more of the requested servers are successfully provisioned.
Question 51: Correct
How much data each of
the micro-partition contain in uncompressed form? Select the
·
1 GB
·
Between 10 MB to 40 MB
·
Between 50 MB to 500 MB
(Correct)
·
16 MB
Explanation
Micro-partitions are
small in size (50 to 500 MB, before compression).
Question 52: Correct
Which are the correct
statements about Snowflake data Storage?
·
Encryption on demand
·
Hybrid Columnar Storage
(Correct)
·
Automatic Micro-partitioning
(Correct)
·
Natural data clustering and optimization
(Correct)
Explanation
Snowflake stores the
data in hybrid columnar format in micro-partitions. Snowflake has some level of
natural data clustering and optimization capabilities. All the data in rest or
in motion are always encrypted.
Question 53: Correct
John created a
Database and set the DATA_RETENTION_TIME_IN_DAYS as 30 days, but he did not set
the DATA_RETENTION_TIME_IN_DAYS in one of the table 'T1'. After 5 days, he
accidently dropped table 'T1'. Will he able to recover the table T1?
·
Yes, as the retention period is specified at database level
(Correct)
·
John can recover the table T1 after 30 days
·
John should call Snowflake Support to recover the table from
Fail-Safe.
·
No, John cant recover the dropped table T1 because he didn't set
DATA_RETENTION_TIME_IN_DAYS for Table T1
Explanation
If a retention period
is specified for a database or schema, the period is inherited by default for
all objects created in the database/schema.
Question 54: Incorrect
Snowflake data storage
costs include which types of data?
·
Semi-Structured data - additional fees
(Incorrect)
·
Cached Results
·
Persistent data stored in permanent tables
(Correct)
·
Data retained to enable data recovery (time travel and
fail-safe)
(Correct)
·
Metadata
Explanation
All storage within
Snowflake is billable in compressed format including the persistent data stored
in permanent tables, time-travel and fail-safe. There is no additional fee for
handling Semi-Structured data.
Question 55: Correct
John wants to create a
multi-cluster warehouse and wants to make sure that whenever new queries are
queued, additional clusters should start immediately. How should he configure
the Warehouse?
·
Set the SCALING POLICY as STANDARD
(Correct)
·
Snowflake takes care of this automatically so, John does not
have to worry about it
·
Set the SCALING POLICY as ECONOMY
·
Configure as SCALE-MAX so that the warehouse is always using
maximum number of specified clusters
Explanation
If a multi-cluster
warehouse is configured with SCALING policy as STANDARD it immediately when
either a query is queued or the system detects that there’s one more query than
the currently-running clusters can execute
Question 56: Correct
Snowflake data storage
costs are calculated based on: (Select 2)
·
Amount Stored - Daily Average
(Correct)
·
Compressed Size
(Correct)
·
Uncompressed Size
·
Amount Stored on First Day of Month
·
Amount Stored on Last Day of Month
Explanation
All storage within
Snowflake is billable in compressed format. Snowflake considers the daily
average storage (in Terabytes) for billing.
Question 57: Correct
John wants to create a
warehouse which should not start immediately. Which property will him to create
a warehouse in suspended mode.
·
INITIATE = FALSE
·
AUTO_START = FALSE
·
AUTO_SUSPEND = INITIAL
·
INITIALLY_SUSPENDED = TRUE
(Correct)
Explanation
INITIALLY_SUSPENDED =
TRUE will not start the warehouse after creation. By default it is set to
FALSE.
Question 58: Correct
What types of
Infrastructure Security are available to ensure customer data is secured?
·
Customer Admin takes the backup of data into local data disk
·
Cloud Provider's physical security
(Correct)
·
Cloud provider's redundancy
(Correct)
·
Regional data centers
(Correct)
Explanation
Snowflake leverages
all the Cloud Provider's securities like physical and digital, data replication
across multiple regions etc.
Question 59: Correct
John has table T1 with
Time-Travel retention time period set to 20 days. He increases the retention
period by 10 days to make it 30 days. What impacts will happen on Table data.
Please select 2.
·
No impact on existing data which moved from table to Time-Travel
before the increase of Time-Travel retention period
·
Data that have moved to Fail-safe after 20 days will now be
available in Time-Travel for additional 10 days
·
Data that would have been removed after 20 days is now retained
for an additional 10 days before moving into Fail-safe
(Correct)
·
No impact on any data that is 20 days older and has already
moved into Fail-safe
(Correct)
·
Changes will be ONLY effective for new data coming to
Time-Travel
Explanation
Increasing Retention
causes the data currently in Time Travel to be retained for the longer time
period. The new data retains for the increased retention period as well.
Question 60: Correct
Generally, what is the
size of a micro-partition in compresses format?
·
16 GB
·
16 MB
(Correct)
·
500 MB
·
50 MB
Explanation
50 MB to 500 MB in
Uncompressed format and 16 MB in compressed format.
Question 61: Correct
Decreasing the size of
a running warehouse removes servers from the warehouse. When the servers are
removed, the cache associated with the servers is dropped.
·
FALSE
·
TRUE
(Correct)
Explanation
Decreasing the size of
a running warehouse removes servers from the warehouse. When the servers are
removed, the cache associated with the servers is dropped, which can impact
performance in the same way that suspending the warehouse can impact performance
after it is resumed. Keep this in mind when choosing whether to decrease the
size of a running warehouse or keep it at the current size. In other words,
there is a trade-off with regards to saving credits versus maintaining the
server cache.
Question 62: Correct
Monica is an admin and
wants to see the usage of Fail-Safe. How can she get details on Fail-Safe
usage?
·
Fail Safe usage details are never available to customer
·
Admins can view Fail-safe use in the Snowflake Web UI under
Account > Billing & Usage
(Correct)
·
Monica should contact Snowflake support
·
None of these
Explanation
Fail-safe usage is
available in Account's Billing & Usage section.
Question 63: Correct
Which all actions can
you perform in a defined period of time using Time Travel?
·
Query data in the past
(Correct)
·
Restores tables, schemas and databases that have been dropped
(Correct)
·
Create clones of tables, schemas and databases
(Correct)
·
Restore tables, schemas but not databases that have been dropped
Explanation
Using Time Travel, you
can perform the following actions within a defined period of time: - Query data
in the past that has since been updated or deleted. - Create clones of entire
tables, schemas, and databases at or before specific points in the past. -
Restore tables, schemas, and databases that have been dropped.
Question 64: Correct
When creating a table,
schema, or database, the account default can be overridden using the
DATA_RETENTION_TIME_IN_DAYS parameter in the command.
·
FALSE
·
TRUE
(Correct)
Explanation
Users with the
ACCOUNTADMIN role can set DATA_RETENTION_TIME_IN_DAYS to 0 at the account
level, which means that all databases (and subsequently all schemas and tables)
created in the account have no retention period by default; however, this
default can be overridden at any time for any database, schema, or table.
Question 65: Correct
In what order servers
are removed when a warehouse is suspended or resized?
·
FIFO ("FIRST IN, FIRST OUT")
·
LILO ("LAST IN, LAST OUT")
·
LIFO ("LAST IN, FIRST OUT")
(Correct)
·
FILO ("FIRST IN, LAST OUT")
Explanation
Each server in a
warehouse cluster has a position in the warehouse that is maintained, even when
the warehouse is suspended or resized. This position impacts how servers are
added and removed because servers are always removed in reverse order of when
they were added (aka LIFO, “Last In, First Out”).
Question 66: Correct
Fail-safe is a
reliable way to create Dev/Test/QA and other environments.
·
TRUE
·
FALSE
(Correct)
Explanation
Fail-safe is not good
option for Dev/TEST/QA and other non-production data. Fail-Safe is good for
important production level data.
Question 67: Correct
Temporary and
Transient Tables has 1 day of Fail-safe available. (True/False)
·
FALSE
(Correct)
·
TRUE
Explanation
Fail-safe is not
supported for Temporary and Transient Tables. It is only available for
Permanent Tables.
Question 68: Correct
Time Travel cannot be
disabled for an account. (True / false)
·
TRUE
(Correct)
·
FALSE
Explanation
Time Travel cannot be
disabled for an account; however, it can be disabled for individual databases,
schemas, and tables by specifying DATA_RETENTION_TIME_IN_DAYS with a value of 0
for the object. Also, users with the ACCOUNTADMIN role can set DATA_RETENTION_TIME_IN_DAYS
to 0 at the account level, which means that all databases (and subsequently all
schemas and tables) created in the account have no retention period by default;
however, this default can be overridden at any time for any database, schema, or
table.
Question 69: Correct
What action causes a
Warehouse's cache to be purged?
·
A change to the Metadata Cache size.
·
The passing of 24 hours.
·
Resuming the warehouse.
·
Suspending the warehouse.
(Correct)
Explanation
Cache gets purged on
suspending the warehouse.
Question 70: Correct
Which Snowflake
editions have maximum 90 days of Time-Travel retention period?
·
VPS
(Correct)
·
Enterprise
(Correct)
·
Standard
·
All the editions
·
Business Critical
(Correct)
Explanation
Except Standard, all
other Snowflake editions have maximum 90 days of Time-Travel retention period.
Question 71: Correct
What is the maximum
Time-Travel retention period of Standard Edition?
·
0 days
·
1 day
(Correct)
·
90 days
·
7 days
Explanation
For Snowflake Standard
Edition, the retention period can be set to 0 (or unset back to the default of
1 day) at the account and object level (i.e. databases, schemas, and tables).
Question 72: Correct
Multi-cluster
warehouse credit billing is based on which factors? (Select 2)
·
Number of queries it processes per hours
·
Number of servers per cluster
(Correct)
·
Number of Clusters
(Correct)
·
Number of users
Explanation
For multi-cluster
warehouse, the number of credit billed is calculated based on the number of
servers per cluster and the number of clusters that run within the time period.
Question 73: Correct
A Virtual Warehouse
can't access data loaded in the table using different warehouse.
·
TRUE
·
FALSE
(Correct)
Explanation
Any warehouse can be
used for accessing any database or table without any contention.
Question 74: Correct
Monica has a
EMPLOYEE_DATA table. Monica wants to create another table EMPLOYEE_DATA_OTHER
which should be same as EMPLOYEE_DATA table with same data. What is the best
option for Monica?
·
Clone the table with same data with SQL command as follows -
CREATE TABLE EMPLOYEE_DATA_OTHER CLONE EMPLOYEE_DATA;
(Correct)
·
CREATE SHARE EMPLOYEE_DATA;
·
Create the table with same data with SQL command as follows -
CREATE TABLE EMPLOYEE_DATA_OTHER AS SELECT * FROM EMPLOYEE_DATA;
·
Create the table with LIKE SQL command as follows - CREATE TABLE
EMPLOYEE_DATA_OTHER LIKE EMPLOYEE_DATA;
Explanation
The best option is the
Clone the table as EMPLOYEE_DATA and EMPLOYEE_DATA_OTHER has same structure and
same data. It will help save the storage cost. LIKE command only creates empty
table.
Question 75: Correct
Which SQL statement
will suspend a running Warehouse MY_WAREHOUSE?
·
SUSPEND WAREHOUSE MY_WAREHOUSE;
·
DROP WAREHOUSE MY_WAREHOUSE;
·
ALTER WAREHOUSE MY_WAREHOUSE SET AUTO_SUSPEND = 'TRUE';
·
ALTER WAREHOUSE MY_WAREHOUSE SUSPEND;
(Correct)
Explanation
ALTER SQL statement
with SUSPEND will suspend a warehouse.
Question 76: Correct
Which privilege is
needed to be able to monitor a warehouse?
·
MODIFY
·
USAGE
·
MONITOR
(Correct)
·
OPERATE
Explanation
User must be using a
role that has the MONITOR privilege on the warehouse.
Question 77: Correct
Zero Copy Cloning
allows users to have multiple copies of your data without the additional cost
of storage usually associated with replicating data. Which other statements
about the Cloning features in Snowflake are True?
·
The clone is a pointer to the original table data
(Correct)
·
Any new record in the parent table gets available in the cloned
table
·
Clone is a “point in time version” of the table data as of the
time the clone was made
(Correct)
·
Cloning is an efficient and cost effective approach for code
migration for Agile Release Management
(Correct)
Explanation
New record doesn't get
available in cloned table because cloning is "point in time version"
means only the data which were available at the time of cloning get available
in cloned table.
Question 78: Correct
When a warehouse is resized,
which queries make use of the new size?
·
Both current and subsequent queries
·
Only currently running queries
·
Only subsequent queries
(Correct)
Explanation
The current running
queries keep running on the old size server. Only subsequent queries run on new
Sized Virtual Warehouse. If queries processed by a warehouse are running
slowly, you can always resize the warehouse to provision more servers. The
additional servers do not impact any queries that are already running, but they
are available for use by any queries that are queued or newly submitted.
Question 79: Correct
The data stored as
part of fail-safe is part of storage costs charged to customers.
·
FALSE
·
TRUE
(Correct)
Explanation
All storage within
Snowflake is billable in compressed format including the persistent data stored
in permanent tables, time-travel and fail-safe.
Question 80: Correct
Resizing a warehouse
can be completed at any time, even when running
·
TRUE
(Correct)
·
FALSE
Explanation
Warehouse can be
resized any time using UI or SQL ALTER command.
Question 81: Correct
John wants to create a
Warehouse which can auto suspend in 10 minutes. How should he configure the
warehouse?
·
AUTO_SUSPEND = 10
·
AUTO_RESUME = TRUE
·
AUTO_SUSPEND = TRUE
·
AUTO_SUSPEND = 600
(Correct)
Explanation
AUTO_SUSPEND takes
care of suspension of warehouse not in use for specified time. The time
specified is in Seconds.
Question 82: Correct
Scaling Out is an
automated process. It automatically scale out during peak times and scale back
during slow times
·
FALSE
·
TRUE
(Correct)
Explanation
Scaling out is
automated process and Scaling up is manual process.
Question 83: Correct
John has table T1 with
Time-Travel retention time period set to 20 days. He decreases the retention
period by 15 days to make it 5 days. What impacts will happen on Table data.
Please select 2.
·
No impact to existing Time Travel data. That will still complete
the longer period of 20 days before going to Fail-safe
·
The data that is currently in Time Travel and if the data is
still within the new shorter period, it remains in Time Travel
(Correct)
·
The data which was in Time Travel for more than 5 days will move
to Fail-safe by Snowflake by background process
(Correct)
·
Changes will be ONLY effective for new data coming to
Time-Travel
Explanation
Decreasing Retention
reduces the amount of time data is retained in Time Travel: - For active data
modified after the retention period is reduced, the new shorter period applies.
- For data that is currently in Time Travel: --If the data is still within the
new shorter period, it remains in Time Travel. --If the data is outside the new
period, it moves into Fail-safe.
Question 84: Correct
What is the best use
of SCALING OUT?
·
Better Concurrency
(Correct)
·
Better Performance
Explanation
SCALING OUT is meant
for handling high concurrent queries.
Question 85: Correct
How does Snowflake
provide continuous availability?
·
Customer has to backup the data locally out of cloud
·
Snowflake synchronizes data across availability zones
(Correct)
·
Fail Safe Storage
(Correct)
·
Time Travel Storage
(Correct)
Explanation
Snowflake
Transparently synchronizes data across availability zones which are
geographically separated and on separate power grids. No downtime while updates
and patches. Time- Travel and Fail-Safe features help customers recover data
within a defined retention period.
Question 86: Correct
Which SQL statement
will not consume Warehouse credit?
·
SHOW WAREHOUSE LIKE '%DEMO';
(Correct)
·
SHOW WAREHOUSES;
(Correct)
·
DROP WAREHOUSE MY_WAREHOUSE;
(Correct)
·
ALTER WAREHOUSE MY_WAREHOUSE SET WAREHOUSE_SIZE =
"SMALL";
(Correct)
Explanation
None of these SQL
statements needs a running warehouse as the result comes from metadata of cloud
services layer.
Question 87: Correct
Normally a warehouse
begins to consume credits once all the servers are provisioned for the warehouse.
·
FALSE
·
TRUE
(Correct)
Explanation
A warehouse begins to
consume credits once all the servers are provisioned for the warehouse. - In a
rare instance when some of the servers fail to provision, the warehouse only
consumes credits for the provisioned servers. - Once the remaining servers are
successfully provisioned, the warehouse starts consuming credits for all
requested servers.
Question 88: Correct
Micro-partitioning is
automatically performed on all the Snowflake tables.
·
FALSE
·
TRUE
(Correct)
Explanation
Micro-partitioning is
automatically performed on all Snowflake tables. Tables are transparently
partitioned using the ordering of the data as it is inserted/loaded.
Question 89: Correct
Micro-partitions are
IMMUTABLE. What are features make micro-partitions IMMUTABLE?
·
When new data gets ingested Snowflake tries to insert data in
existing micro-partition to save the storage.
·
Snowflake purges the micro-partitions as soon as you delete any
record from table.
·
Micro-Partitions are not editable.
(Correct)
·
Snowflake creates new micro-partitions every time there is data
change.
(Correct)
Explanation
Snowflake creates new
partitions in case of any data change or new ingested data and also keeps the old
version of micro-partition. The old versions of micro-partitions are used for
Time-Travel and Fail-Safe. Services layer stores metadata about every
micro-partition like - MIN/MAX ranges of values in each column, Number of
distinct values, Row count etc.
Question 90: Correct
How to choose the
right size of warehouse to achieve the best results based on the Query
processing?
·
Execute relatively homogenous queries on the same warehouse
(Correct)
·
Execute varieties of queries on same warehouse to achieve the
best result
Explanation
To achieve the best
results, try to execute relatively homogeneous queries (size, complexity, data
sets, etc.) on the same warehouse; executing queries of widely-varying size
and/or complexity on the same warehouse makes it more difficult to analyze
warehouse load, which can make it more difficult to select the best size to
match the size, composition, and number of queries in your workload.
Question 91: Incorrect
As an ACCOUNTADMIN,
how can you find the credit usage of a warehouse?
·
Using Web interface > Account > Usage
(Correct)
·
Run SQL query on ACCOUNT_USAGE table under Snowflake Database
·
Run SQL query on WAREHOUSE_METERING_HISTORY view under
ACCOUNT_USAGE Schema
(Correct)
·
Run SQL query on METERING_HISTORY view under ACCOUNT_USAGE Schema
(Correct)
Explanation
Using Web interface
> Account > Usage section. AND using SQL - ACCOUNT_USAGE: - Query the
METERING_HISTORY to view hourly usage for an account. - Query the
METERING_DAILY_HISTORY to view daily usage for an account. - Query the WAREHOUSE_METERING_HISTORY
to view usage for a warehouse. - Query the QUERY_HISTORY to view usage for a
job. INFORMATION_SCHEMA: - Query the QUERY_HISTORY table function.
Question 92: Correct
How much is the
Time-Travel retention period of Temporary Table?
·
0 days
·
1 day
(Correct)
·
90 days
·
7 days
Explanation
Temporary Table has
maximum of 1 day Time-Travel retention period.
Question 93: Correct
Which of the following
is a common case for Cloning in Snowflake? (Select all that apply)
·
Agile Release and Development
(Correct)
·
Data Life Cycle Management
(Correct)
·
"Point in Time" Snapshot
(Correct)
·
Data Encryption Protection
Explanation
Clone is a “point in
time version” of the table data as of the time the clone was made. Cloning is
an efficient and cost effective approach for code migration for Agile Release
Management. Cloning also help in Data life cycle management.
Question 94: Correct
When configuring a
Warehouse using a Snowflake edition that has Elastic Data Warehousing enabled,
what facets or components will you need to configure that are not needed in
accounts where Elastic Data Warehousing is not enabled. (Choose two)
·
Minimum and Maximum Servers
·
Auto-Suspend
·
Scaling Policy
(Correct)
·
Minimum and Maximum Clusters
(Correct)
Explanation
Scaling Policy
(STANDARD / ECONOMY) , the number of minimum and maximum clusters are the
required while configuring multi-clustered virtual warehouse.
Question 95: Correct
The Cloning feature in
Snowflake requires less storage because:
·
The cloned data is constantly updated to synchronize with
original table data
·
Only metadata is copied; no physical data is copied
(Correct)
·
Data is replicated to ensure integrity of data in the original
table data
Explanation
As name suggest, it is
Zero copy cloning feature that means data actual data is not replicated that
means no additional storage consumption
Question 96: Correct
Monica is an
ACCOUNTADMIN and wants to change the Time Travel retention period of table T1
of database DB1 to 15 days. Which options will help her to set the new Time
Travel retention period?
·
Monica can change the retention period through ACCOUNT tab on UI
·
Execute SQL Command - ALTER TABLE T1 SET
DATA_RETENTION_TIME_IN_DAYS = 75;
·
Monica should contact Salesforce support to get it done
·
Execute SQL Command - ALTER TABLE T1 SET
DATA_RETENTION_TIME_IN_DAYS = 15;
(Correct)
Explanation
Set the correct
context for Database and Schema and run the ALTER TABLE command to set
DATA_RETENTION_TIME_IN_DAYS to 15.
Question 97: Correct
Compute can be scaled
up, down, out, or in and there is no effect on storage used.
·
FALSE
·
TRUE
(Correct)
Explanation
Compute and Storage
are decoupled in Snowflake.
Question 98: Correct
When should you avoid
enabling AUTO_SUSPEND?
·
You have a heavy, steady workload for the warehouse.
(Correct)
·
You require the warehouse to be available with no delay or lag
time
(Correct)
·
You should always enable AUTO_SUSPEND to save the compute costs.
Explanation
You might want to
consider disabling auto-suspend for a warehouse if: - You have a heavy, steady
workload for the warehouse. - You require the warehouse to be available with no
delay or lag time. Server provisioning is generally very fast (e.g. 1 or 2 seconds);
however, depending on the size of the warehouse and the availability of servers
to provision, it can take longer.
Question 99: Correct
What is the minimum
billing charge for provisioning a warehouse?
·
1 Second
·
1 Minute
(Correct)
·
60 Minutes
·
No charge
Explanation
The minimum billing
charge for provisioning a server is 1 minute (i.e. 60 seconds). - There is no
benefit to stopping a warehouse before the first 60-second period is over
because the credits have already been billed for that period. - After the first
60 seconds, all subsequent billing for a running server is per-second (until
the server shuts down).
Question 100: Correct
Which are the correct
statements for a Virtual Warehouse?
·
Standard Virtual Warehouse can Scale-Out
·
Each larger size is double the preceding, in both VMs in the
cluster and in Snowflake credits consumed
(Correct)
·
Size determines the number of servers that comprise each cluster
in a ware
(Correct)
·
Warehouse are Sized in "t-shirt" sizing
(Correct)
Explanation
Standard Virtual
Warehouse is single cluster Warehouse so it can't scale-out rather Scale-Up to
bigger size. All other options mentioned here are true.
Question 101: Correct
Scaling a Warehouse
OUT will increase the number of clusters (e.g., Min to Max)
·
FALSE
·
TRUE
(Correct)
Explanation
SCALING OUT means
adding additional clusters. It is called Multi-Clustering.
Question 102: Incorrect
A multi-cluster
virtual warehouse is Auto-Scale when
·
Minimum number of clusters and Maximum number of clusters are
same and must be specified with a value of more than 1.
(Incorrect)
·
MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 4
(Correct)
·
Minimum and Maximum number of clusters are specified
differently.
(Correct)
·
MIN_CLUSTER_COUNT = 3 MAX_CLUSTER_COUNT = 3
Explanation
This mode is enabled
by specifying different values for maximum and minimum clusters. In this mode,
Snowflake starts and stops clusters as needed to dynamically manage the load on
the warehouse.
Question 103: Correct
Which SQL command will
give the list of all the warehouses in an account?
·
LIST WAREHOUSE;
·
LIST WAREHOUSES;
·
DISPLAY WAREHOUSE;
·
SHOW WAREHOUSES;
(Correct)
·
SHOW WAREHOUSE;
Explanation
SHOW WAREHOUSES is the
right command and you can add LIKE as well to narrow the list.
Question 104: Correct
What are the correct
SQL extensions used in querying data from Time Travel? Select 2.
·
AFTER
·
AT
(Correct)
·
INITIAL
·
BEFORE
(Correct)
Explanation
AT | BEFORE clause are
used to query based on timestamp, offset or query id.
Question 105: Correct
How to change the
current warehouse of a session?
·
Execute SET WAREHOUSE command
·
Execute USE WAREHOUSE command
(Correct)
·
None of these
·
Execute SELECT WAREHOUSE command
Explanation
USE WAREHOUSE
<warehouse_name>; command sets the warehouse for the current session,
where <warehouse_name> is the name of the warehouse which needs to be set
for the session.
Comments
Post a Comment