Practice Set 1: Snowpro Certification Practice Set with more than 250 Unique Questions
Snowflake SNOWPRO Core Certification Practice Set Questions:
Question 1: Correct
Can you share data
with multiple consumers with row based access control?
·
NO
·
YES
(Correct)
Explanation
Secure View with
Account mapping using CURRENT_ACCOUNT();
Question 2: Correct
A user can connect
Snowflake in multiple ways. Please select
·
Command line clients (e.g. SnowSQL)
(Correct)
·
A web-based user based
(Correct)
·
Native connector (e.g. Python) that can be used to develop
applications
(Correct)
·
ODBC and JDBC drivers that can be used by other applications
(Correct)
Explanation
Snowflake supports
multiple ways of connecting to the service: - A web-based user interface from
which all aspects of managing and using Snowflake can be accessed. - Command
line clients (e.g. SnowSQL) which can also access all aspects of managing and
using Snowflake. - ODBC and JDBC drivers that can be used by other applications
(e.g. Tableau) to connect to Snowflake. - Native connectors (e.g. Python) that
can be used to develop applications for connecting to Snowflake. - Third-party
connectors that can be used to connect applications such as ETL tools (e.g.
Informatica) and BI tools to Snowflake.
Please subscribe on youtube 🙏🙏
Question 3: Correct
Which of the following
are valid context functions?
·
CURRENT_CLOUD_INFRASTRUCTURE( )
·
CURRENT_CLIENT( )
(Correct)
·
CURRENT_SESSION( )
(Correct)
·
CURRENT_WORKSHEET( )
·
CURRENT_REGION( )
(Correct)
Explanation
CURRENT_WORKSHEET()
and CURRENT_CLOUD_INFRASTRUCTURE() are not valid context functions.
Question 4: Correct
Please choose all the
securable objects from the given options.
·
EXTERNAL TABLE
(Correct)
·
SCHEMA
(Correct)
·
STORED PROCEDURE
(Correct)
·
DATABASE
(Correct)
·
UDF
(Correct)
Explanation
The top-most container
is the customer account, within which reside USER, ROLE, WAREHOUSE, and
DATABASE objects. All other securable objects (such as TABLE, FUNCTION, FILE
FORMAT, STAGE, SEQUENCE, STORED PROCEDURE, UDF, EXTERNAL TABLE, etc.) are
contained within a SCHEMA object within a DATABASE.
Please subscribe on youtube 🙏🙏
Question 5: Correct
Snowflake offers the
early access of staged release to designated Standard Edition Customer. True or
false?
·
FALSE
(Correct)
·
TRUE
Explanation
Snowflake offers early
access to designated Enterprise accounts.
Question 6: Correct
What are the best
practices for JOIN on unique keys?
·
Avoid MANY-TO-MANY Join
(Correct)
·
Use distinct keys
(Correct)
·
Avoid unintentional cross join
(Correct)
Explanation
Best Practices for
Join on Unique keys: - Ensure keys are distinct - Understand relationships
between your tables before joining - Avoid many-to-many join - Avoid
unintentional cross join
Question 7: Correct
If you want to view
query history older than 14 days, where can you go to view it? Choose one path
and one "term" commonly used.
·
Account->Usage->History
·
SNOWFLAKE (Database) -> ACCOUNT_USAGE (Schema) ->
QUERY_HISTORY (Secure View)
(Correct)
·
The Usage Sharing Monitor
·
The Account Usage Share
(Correct)
·
History-> Profile -> Account_Usage
Explanation
History is available
for only 14 days through UI. Older history can be found in ACCOUNT_USAGE.
Question 8: Correct
Which query will
require compute (Virtual Warehouse)? Consider table created as - CREATE
or replace TABLE T1 ( ID number(2), Name varchar(20),
ID number (11,2) );
·
SELECT MAX(ID) FROM T1;
·
SELECT AVG(ID) FROM T1;
(Correct)
·
SELECT MIN(ID) FROM T1 WHERE NAME = 'JOHN';
(Correct)
·
SELECT MIN(ID) FROM T1;
·
SELECT MAX(ID) FROM T1 WHERE NAME = 'JOHN';
(Correct)
·
SELECT COUNT(*) FROM T1;
Explanation
Cloud Services layer
can return queries like COUNT(*), MAX, MIN without using virtual warehouse
unless there is no conditional filters. Even if SQL statement with conditional
filter ran within 24 hours, the cloud services layer can return the result
without using Virtual Warehouse from the cache.
Question 9: Correct
Which actions are not
supported with shared data?
·
Editing the comments for a shared database
(Correct)
·
Can be re-shared by data consumer
(Correct)
·
Time Travel for a shared database or any schemas/tables in the
database
(Correct)
·
Creating a clone of a shared database or any schemas/tables in
the database
(Correct)
Explanation
Share is just for
read, not for cloning or time-travel query or any edit.
Question 10: Correct
Objects that are
dropped from a shared database and then recreated with the same name are not
immediately available in the share; you must execute grant usage on the objects
to make them available
·
FALSE
·
TRUE
(Correct)
Explanation
True, you need to
GRANT on newly create object. It doesn't matter if you create the same object
what you have dropped.
Question 11: Correct
Which view does behave
like a table and get auto-refreshed?
·
None
·
Standard
·
Materialized
(Correct)
·
Secured
Explanation
Materialized views are
designed to improve query performance for workloads composed of common,
repeated query patterns. However, materializing intermediate results incurs
additional costs. As such, before creating any materialized views, you should
consider whether the costs are offset by the savings from re-using these
results frequently enough.
Question 12: Correct
Once the Snowflake
Account is provisioned, user can access Snowflake using following methods:
·
Any 3rd-party partner that can connect to Snowflake
(Correct)
·
Any client application connected via JDBC or ODBC
(Correct)
·
SnowSQL, the Snowflake command line client
(Correct)
·
Browser-based web interface
(Correct)
Explanation
Once your Snowflake
account has been provisioned, you can access Snowflake using any of the
following methods: - Browser-based web interface - SnowSQL, the Snowflake
command line client - Any client application connected via JDBC or ODBC - Any
3rd-party partner that can connect to Snowflake
Question 13: Incorrect
if another objects
gets added to a shared database. Will consumer able to see immediately?
·
Yes
(Incorrect)
·
No
(Correct)
Explanation
Consumer will only see
the object which was Granted by Provider. For example - If Provider created a
new table and if provider GRANT SELECT ON new table to the share then Consumer
will be able to see the new Object.
Question 14: Correct
The acronym ETL stands
for what three words?
·
Extract, Transition, Load
·
Extract, Transition, Loading
·
Extract, Transform, Load
(Correct)
·
Extract, Transfer, Load
Explanation
First step is
extraction of data from source and then Transform before loading into target
database like in Snowflake.
Question 15: Correct
Which cache type gets
purged regularly?
·
Results Cache
(Correct)
·
Metadata Cache
·
Warehouse Cache
Explanation
Result Cache gets
suspended every 24 hours unless queried again within 24 hours. It can go until
31 days. If you also see option for Warehouse and it is mentioned that
AUTO_SUSPEND is set then Warehouse Cache will be you best answer.
Question 16: Correct
Each Snowflake account
comes with two shared databases. One is a set of sample data and the other
contains Account Usage information. Check all true statements about these
shared databases.
·
SNOWFLAKE contains a schema called ACCOUNT_USAGE
(Correct)
·
SNOWFLAKE_SAMPLE_DATA contains a schema called ACCOUNT_USAGE
·
ACCOUNT_USAGE is a schema filled with external tables
·
ACCOUNT USAGE is a schema filled with secure views
(Correct)
·
SNOWFLAKE contains a table called ACCOUNT_USAGE
Explanation
ACCOUNT_USAGE is
Schema not a table. It contains many VIEWS which help a customer get all the
relevant details about the usage.
Question 17: Correct
Which statement
accurately describes the Snowflake Cloud Services layer?
·
Managed and scaled by the user
·
A collection of tightly coupled database management features
·
A set of Cloud providers' general services made available to
users via the internet
·
A collection of independent, scalable, and stateless services
providing crucial data management capabilities
(Correct)
Explanation
Snowflake is a cloud
native data platform. It is not a cloud provider like AWS or Azure. It offers
unique architecture of decoupled storage and compute with unlimited elasticity
capability.
Question 18: Correct
When choosing a
geographic deployment region, what factors might an enrollee consider?
·
Proximity to the point of service
(Correct)
·
Additional fees charged for regions with geo-political unrest
·
End-user perceptions of glamorous or trendy geographic locations
·
Number of availability zones within a region
(Correct)
Explanation
It is better to choose
the nearest region to avoid any lag or latency with higher number of
availability zones.
Question 19: Correct
Which are the options
available for SECURITYADMIN in Snowflake UI ribbon?(Choose all applicable)
·
Warehouses
(Correct)
·
Data Exchange
·
Notifications
·
History
(Correct)
·
Account
(Correct)
Explanation
Notifications option
is only available for ACCOUNTADMIN. Please be careful and do not confuse
with Data Exchange. As an ACCOUNTADMIN, you have option of DATA MARKETPLACE not
Data Exchange.
Question 20: Correct
Two virtual warehouses
can access the same data at the same time without causing contention issues
·
TRUE
(Correct)
·
FALSE
Explanation
This is true and
unique feature of Snowflake to make the data available for everyone intended to
access any time without any contention issues.
Question 21: Correct
Snowflake offers rich
set of drivers and connectors to connect with external applications. Select the
Connectors from the list. (Choose 3)
·
Spark
(Correct)
·
Kafka
(Correct)
·
Python
(Correct)
·
Node.js
·
JDBC
Explanation
JDBC and Node.js are
drivers not connectors. Snowflake also provides the Kafka Connector. Kafka
connector is not available in Help > download menu. Kafka connector jar file
can be downloaded from https://www.confluent.io/hub/ or https://mvnrepository.com/artifact/com.snowflake
Please subscribe on youtube 🙏🙏
Question 22: Correct
John is having
SECURITYADMIN role. He created a custom DBA_ROLE. He Granted SYSADMIN role to
DBA_ROLE. He created a user 'Monica'. John granted DBA_ROLE to Monica. Monica
creates a Database Monica_DB. Monica then created a Table T1 in Monica_DB under
PUBLIC schema. What should John do to access Table T1 created by Monica?
·
GRANT ROLE DBA_ROLE TO John; USE DATABASE monica_db; Select *
from t1;
·
USE ROLE dba_role; USE DATABASE monica_db; Select * from t1;
·
USE ROLE SECURITYADMIN; USE DATABASE monica_db; Select * from
t1;
·
GRANT TOLE DBA_ROLE TO John; USE ROLE DBA_ROLE; USE DATABASE
monica_db; Select * from t1;
(Correct)
Explanation
Doesn't matter if John
has created the DBA_ROLE. If John wants to access the object created by
DBA_ROLE, he needs to GRANT DBA_ROLE to himself.
Question 23: Correct
Which approach would
result in improved performance through linear scaling of data ingestion
workload?
·
Consider the practice of organizing data by granular path
·
Resize virtual warehouse
·
Consider the practice of splitting input file batch within the
recommended range of 10MB to 100MB
·
All of these
(Correct)
Explanation
All of these are best
practices for data loading. If you provide granular path then Snowflake will
not spend time in identifying the file. File Size of 10 MB to 100 MB in
compressed format loads faster. Please note each server can process 8 files in
parallel so, it is always recommended to split the large size files into
smaller files. if you are not loading 8 files at the same time by each of the
server in a cluster, you are wasting the compute resources.
what happened if you upload a huge file inside Snowflake Datawarehouse? Please follow on medium 🙏🙏
Question 24: Correct
Snowflake is a Cloud
Data Platform delivered as a service.
·
TRUE
(Correct)
·
FALSE
Explanation
Snowflake is a cloud
native data platform. It is not a lift and shift of on-premise data warehouse
to cloud.
Question 25: Correct
Which type of view has
an extra layer of protection to hide the SQL code from unauthorized viewing?
·
Standard
·
Materialized
·
Secure
(Correct)
·
Permanent
Explanation
Some of the internal
optimizations for views require access to the underlying data in the base
tables for the view. This access might allow data that is hidden from users of
the view to be exposed through user code, such as user-defined functions, or
other programmatic methods. Secure views do not utilize these optimizations,
ensuring that users have no access to the underlying data.
Question 26: Correct
Which of the following
terms or phrases can also be used to describe Snowflake? (Select 4)
·
Hadoop-Compliant
·
Hybrid Columnar
(Correct)
·
Multi-cluster
(Correct)
·
Native SQL
(Correct)
·
Build from the ground up for the cloud
(Correct)
Explanation
Snowflake is a cloud
native data platform. It is not a lift and shift of on-premise data warehouse
to cloud.
Question 27: Correct
Which tables will
experience the most benefit from clustering?
·
Tables with sizes between the range of 100 MB to 1 GB compressed
·
Tables in the multi-terabyte (TB) range
(Correct)
·
Tables with sizes between the range of 1 GB to 10 GB compressed
·
All sizes of tables
Explanation
In general, tables in
the multi-terabyte (TB) range will experience the most benefit from clustering,
particularly if DML is performed regularly/continually on these tables.
Question 28: Correct
Which statements
accurately describes the Snowflake Data Sharing?
·
A share can contain more than one database
·
Data sharing is only supported between accounts in the same
snowflake region
(Correct)
·
A share can't be cloned by Consumer
(Correct)
·
When creating views in a share, secured views are required
(Correct)
Explanation
Each share contains a
single database, and all other objects included in the share must be from this
same database. Consumer accounts must be in the same Snowflake Region as the
provider account.
Question 29: Correct
You create a new
worksheet in the WebUI. You want to set the user role, warehouse, database, and
schema context that will be used when running the SQL code. Which of the
options listed here are valid?
·
Run four "USE" commands, setting each context with a
separate statement.
(Correct)
·
Use the Context drop menu in the upper right corner of the
worksheet.
(Correct)
·
Run the SET_CONTEXT(role,warehouse,database,schema) command.
·
Use fully qualified references to the role and warehouse in each
select statement
Explanation
USE command or Context
menu can be used to set the Warehouse, database, schema for a new worksheet.
Question 30: Correct
Which command will
return information about the current database?
·
USE current_database( );
·
QUERY current_database( );
·
RETURN current_database( );
·
SELECT current_database( );
(Correct)
Explanation
CURRENT_DATABASE() is
a context function and returns the name current database using SELECT command.
Question 31: Correct
Who can create shares?
·
ROLE with CREATE SHARES global Privilege
(Correct)
·
ACCOUNTADMIN
(Correct)
Explanation
You must use the
ACCOUNTADMIN role or a role granted the CREATE SHARES global privilege.
Question 32: Correct
Select all the
features Snowflake supports:
·
A graphical UI to build data pipelines
·
SQL-based transformation
(Correct)
·
Semi-Structured data
(Correct)
·
Unstructured data
·
Dedicated resource for compute
(Correct)
Explanation
Snowflake currently
doesn't support unstructured data like images. There is no Graphical UI to
build the pipelines as of now.
Question 33: Correct
How is the most
effective way to test if clustering a table helped performance?
·
Use SYSTEM$CLUSTERING_INFORMATION. Check the
total_constant_partition_count
·
Run a sample query before clustering and after to compare the
results
(Correct)
·
Use the SYSTEM$CLUSTERING_DEPTH and check the depth of each
column
·
Use SYSTEM$CLUSTERING_INFORMATION. Check the average_overlaps
·
Use SYSTEM$CLUSTERING_INFORMATION. Check the average_depth
Explanation
Also, Snowflake
strongly recommends that you test a representative set of queries on the table
to establish some performance baselines.
Question 34: Correct
What all objects can
be shared?
·
Standard View
·
Table
(Correct)
·
Secure View
(Correct)
·
Secure UDF
(Correct)
Explanation
Data Share is meant
for Secures access and so, Standard View is not allowed to be shared.
Question 35: Correct
John ran a query which
took around 30 mins. He referred to Query profiler, and found the 'Bytes
spilled to local storage' has big number. What could be the issue?
·
John should contact Snowflake Personnel
·
Warehouse size has no impact on Bytes spilling
·
John is using very large warehouse
·
John is using comparatively smaller warehouse
(Correct)
Explanation
If a node has
insufficient memory to complete its portion of a query, it will
"spill" to local SSD storage. This can negatively impact performance,
but is sometimes acceptable. If a node has insufficient local SSD storage to
complete its portion of a query, it will "spill" to remote cloud
storage. This is almost always very bad for performance. The solution in either
case is... to simplify the SQL query or increase the warehouse size (to
increase scarce resources).
Question 36: Correct
Snowflake offers
multiple editions. Which one is not a offering from Snowflake.
·
Enterprise
·
Premium
(Correct)
·
Business Critical
·
Standard
Explanation
Snowflake offers 4
editions: 1 - Standard Edition 2 - Enterprise Edition 3 - Business Critical
Edition 4 - Virtual Private Snowflake (VPS)
Question 37: Correct
The following SQL
statement will require a running warehouse - CREATE TABLE T1 (NAME (VARCHAR20),
ADDRESS VARCHAR (50));
·
TRUE
·
FALSE
(Correct)
Explanation
CREATE DDL statement
is handles by Cloud Services layer and so, it doesn't require Virtual
Warehouse.
Question 38: Correct
If you are defining a
multi-column clustering key of a table, the order in which the columns are
specified in the CLUSTER BY clause is important. As general rule, Snowflake
recommends:
·
Order doesn't matter
·
Ordering the columns from lowest cardinality to highest
cardinality
(Correct)
·
Ordering the columns from highest cardinality to lowest
cardinality
Explanation
As a general rule,
Snowflake recommends ordering the columns from lowest cardinality to highest
cardinality. Putting a higher cardinality column before a lower cardinality
column will generally reduce the effectiveness of clustering on the latter
column.
Question 39: Correct
Which is not the
function under Metadata Management of Cloud Services Layer?
·
Used for storing physical micro partition
(Correct)
·
Handles Queries that can be processes completely from metadata
·
Stores Metadata as data is loaded into the system
·
Used for Time Travel and Cloning
Explanation
Metadata Management
has following major functions: - Stores metadata as data is loaded into the
system - Handles queries that can be processes completely from metadata -Used
for Time Travel and Cloning -Every aspect of Snowflake architecture leverages
metadata
Question 40: Correct
Which of the following
are unique objects introduced by Snowflake?
·
STAGE
(Correct)
·
TABLE
·
PIPE
(Correct)
Explanation
STAGE and PIPE are
unique Snowflake objects.
Question 41: Correct
Snowflake decouples
Storage and Compute. In case of increase in Compute usages, Storage usage also
increases..
·
FALSE
(Correct)
·
TRUE
Explanation
Storage and Compute
are separate.
Question 42: Correct
Which of these can
access database objects and issue SQL statements
·
Both
·
Stored Procedure
(Correct)
·
User-Defined Function
Explanation
DDL and DML operations
are not permitted in UDF. Stored Procedure can access database objects and
issue SQL statements.
Question 43: Correct
Which query will
require compute (Virtual Warehouse)? Consider table created as - CREATE or
replace TABLE T1 ( ID number(2), Name varchar(20), ID number (11,2) );
·
SELECT MAX(ID) FROM T1;
·
SELECT * FROM T1;
(Correct)
·
SELECT MIN(ID) FROM T1;
·
SHOW DATABASES;
·
SELECT CURRENT_ACCOUNT();
Explanation
Except SELECT * FROM
T1, all the queries return results from cloud services layer metadata, So these
queries do not need a running Virtual Warehouse.
Question 44: Correct
Which of the following
options would result in a column named FIRST NAME being sandwiched between to
percent signs? (Check all that apply)
·
MERGESTRING('%',FIRST_NAME,'%')
·
CONCAT('%',CONCAT(FIRST_NAME,'%'))
(Correct)
·
'%'||FIRST_NAME||'%'
(Correct)
·
CONCATN('%',FIRST_NAME,'%')
Explanation
Both || and CONCAT can
be used for concatenation.
Question 45: Correct
Which of the following
are performed by the Cloud Services layer? (Select 4)
·
Metadata Storage
(Correct)
·
Metadata Management
(Correct)
·
Availability Zone Management
·
User Authentication
(Correct)
·
Data Security
(Correct)
Explanation
Availability zone
management is managed by Cloud Provider (like, AWS, AZURE, GCP) not by
Snowflake Cloud Services layer.
Question 46: Correct
Stored Procedure
supports
·
Python
·
Go
·
SQL
(Correct)
·
Java
·
JavaScript
(Correct)
Explanation
Supports both - JS and
SQL.
Snowflake stored procedures use JavaScript and, in most cases,
SQL:
JavaScript provides the control structures (branching and
looping).
SQL is executed by calling functions in a JavaScript API.
Question 47: Correct
Snowflake is a
packaged software offering that can be installed by a user.
·
FALSE
(Correct)
·
TRUE
Explanation
Snowflake is a cloud
native data platform.
Question 48: Correct
Snowflake
Administrators should utilize resource monitors to help control costs and avoid
unexpected credit usage. Which of the following actions can Snowflake's
resource monitor triggers initiate automatically? (Check all that apply)
·
Impose limits on the number of credits that warehouses consume
each month
(Correct)
·
Trigger warehouse suspension for high usage
(Correct)
·
Trigger alert notifications for high usage
(Correct)
·
Roll over query executions to under-utilized warehouses
Explanation
The assigned
warehouses gets suspended after all running queries complete. It doesn't roll
over query executions to under-utilized warehouses.
Question 49: Correct
If snowflake hostname
is https://bbx99999.us-east-1.snowflakecomputing.com, what is the customer Full
account name?
·
bbx99999.us-east-1.snowflakecomputing
·
bbx99999.us-east-1
(Correct)
·
us-east-1
·
bbx99999
·
snowflakecomputing
Explanation
A hostname for a
Snowflake account starts with an full account name (provided by Snowflake) and
ends with the Snowflake domain (snowflakecomputing.com):
<account_name>.snowflakecomputing.com
Question 50: Correct
Which of the following
Snowflake Editions automatically store data in an encrypted state?
·
Standard
(Correct)
·
Enterprise
(Correct)
·
Business Critical
(Correct)
Explanation
All of the Snowflake
editions automatically encrypt data in rest and in motion.
Question 51: Correct
User A and User B can
access one another's result sets from the Results Cache, as long as which of
the following are true? (Choose two)
·
They use the same Role.
(Correct)
·
They sign in to the same Session.
·
They run the exact same SQL Text/Query.
(Correct)
·
They use the same Warehouse.
·
They run with 59 minutes of one another.
Explanation
There must not be
change in query to get the benefit of cache as well as user should use the same
role.
Question 52: Correct
Snowflake is best
suited for OLAP solutions.
·
FALSE
·
TRUE
(Correct)
Explanation
Snowflake is best
suited for OLAP (Online Analytical Processing). Snowflake can complement OLTP
(Online Transaction Processing). OLTP is not a good use case for Snowflake.
Question 53: Correct
Which of the following
have drivers/connectors (or information about where to find them) available via
Help->Downloads in the Snowflake WebUI? (Select 4)
·
Node.js
(Correct)
·
Kafka
·
Spark
(Correct)
·
C
·
JDBC
(Correct)
·
Go
(Correct)
Explanation
There is no connector
or driver available for C in Snowflake. You can download the latest version of
connectors and drivers from Snowflake Web UI. Snowflake also provides the Kafka
Connector. Kafka connector is not available in Help > download menu. Kafka
connector jar file can be downloaded from https://www.confluent.io/hub/ or https://mvnrepository.com/artifact/com.snowflake
Question 54: Correct
Clustering keys are
not intended for all tables. (TRUE / FALSE)
·
FALSE
·
TRUE
(Correct)
Explanation
Clustering keys are
not intended for all tables. The size of a table, as well as the query
performance for the table, should dictate whether to define a clustering key
for the table.
Question 55: Correct
The BI group is
complaining about their queries taking too long to run. Checking the virtual
warehouse information shows the queued time is pretty high. What is the best
way to fix this issue?
·
Increase the virtual warehouse MAX_CLUSTER_COUNT property
(Correct)
·
Determine which users have the high priority queries and set the
other users
·
STATEMENT_QUEUED_TIMEOUT_IN_SECONDS parameter to a low value to
cancel those queries if they get in the queue
·
Increase the size of the virtual warehouse
·
Increase the virtual warehouse MAXCONCURRENCY_LEVEL parameter
·
Provide a virtual warehouse for every user in the group
Explanation
Remember, If queuing
can be solved by SCALE-OUT (add new clusters), not by SCALE-UP. SCALE-UP is
good for performance. MAX_CLUSTER_OUT helps to the maximum number of clusters
which a warehouse can run in case of high workload.
Question 56: Correct
You create a Sequence
in Snowflake with an initial value of 3 and an interval of 2. Which series of
numbers do you expect to see?
·
3,4,5,6,7
·
3,5,7,9,11
(Correct)
·
3,6,9,12,15
·
2,5,8,11,14
Explanation
It starts at initial
value and then adds the interval to get the next value.
Question 57: Correct
Snowflake calculates
usage of data stored in the system for:
·
Database tables, including historical data for Time Travel
(Correct)
·
Fail-safe for database tables
(Correct)
·
Clones of database tables that reference data deleted in the
table that owns the clones
(Correct)
·
Cloud storage used in External Stage
·
Files staged for bulk data loading/unloading
(Correct)
Explanation
Usage for data storage
is calculated on the daily average amount of data (in bytes) stored in the
system for: - Files staged for bulk data loading/unloading (can be stored
compressed or uncompressed). - Database tables, including historical data for
Time Travel (always compressed by Snowflake). - Fail-safe for database tables
(always compressed by Snowflake). - Clones of database tables that reference
data deleted in the table that owns the clones. Cloud storage used in External
Stage is not managed by Snowflake. Its customers who pay to their cloud
provider.
Question 58: Correct
Which of these must
return a value?
·
User-Defined Function
(Correct)
·
Stored Procedure
·
Both
Explanation
Stored Procedure may
or may not return the value but UDF must return the value.
Question 59: Correct
John created a table
EMPLOYEE in worksheet 1 (Session1 ) but when he ran SELECT * FROM EMPLOYEE in
worksheet 2 (Session 2) after setting up the correct Role, Database and Schema,
he received error ' Table doesn't exist'. What could be the reason?
·
EMPLOYEE is Temporary
table
(Correct)
·
XYZ is Transient Table
·
XYZ was created as EXTERNAL TABLE
·
XYZ is a Permanent Table
Explanation
Temporary table is
tied to a session only.
Question 60: Correct
How many maximum
columns (or expressions) are recommended for a cluster key?
·
3 to 4
(Correct)
·
7 to 8
·
Higher the number of columns (or expressions) in the key, better
will be the performance
·
12 to 16
Explanation
A single clustering
key can contain one or more columns or expressions. For most tables, Snowflake
recommends a maximum of 3 or 4 columns (or expressions) per key. Adding more
than 3-4 columns tends to increase costs more than benefits.
Question 61: Correct
A Snowflake customer
is responsible for all the maintenance and services.
·
FALSE
(Correct)
·
TRUE
Explanation
Snowflake is full
cloud platform and near to zero maintenance platform. Customer doesn't have to
mundane tasks like partitioning, vacuuming etc.
Question 62: Correct
Secured view can be
used to hide the definition but its performance can get degraded?
·
TRUE
(Correct)
·
FALSE
Explanation
Secure views should
not be used for views that are defined for query convenience, such as views
created for simplifying querying data for which users do not need to understand
the underlying data representation. This is because the Snowflake query optimizer,
when evaluating secure views, bypasses certain optimizations used for regular
views. This might result in some impact on query performance for secure views.
Question 63: Correct
Which type of view is
most like a table?
·
External
·
Standard
·
Materialized
(Correct)
·
Secure
Explanation
Materialized views are
designed to improve query performance for workloads composed of common,
repeated query patterns. However, materializing intermediate results incurs
additional costs. As such, before creating any materialized views, you should
consider whether the costs are offset by the savings from re-using these
results frequently enough.
Question 64: Correct
What is the right term
for Snowflake Architecture
·
Shared Data
(Correct)
·
Shared Memory
·
Shared Network
·
Shared Nothing
·
Shared Disk
Explanation
Snowflake's
architecture is a hybrid of traditional shared-disk database architectures and
shared-nothing database architectures.
Question 65: Correct
What are the types of
Caches? (Select 2)
·
Results Cache
(Correct)
·
Metadata Cache
(Correct)
·
Storage Cache
·
History Cache
Explanation
Also, Warehouse Cache.
Warehouse cache gets purged on suspension.
Question 66: Correct
Snowflake Architecture
consists of many separate layers. Please select the correct layers. (Select 3).
·
Database Storage Layer
(Correct)
·
Security Layer
·
Cloud Services Layer
(Correct)
·
Query Processing Layer
(Correct)
Explanation
Snowflake Architecture
consists of Cloud Services Layer, Query Processing (Warehouse) Layer and Cloud
Services Layer.
Question 67: Correct
Which SQL statements
will need a running Warehouse and consume credit? Consider table created as -
CREATE OR REPLACE TABLE T1 ( ID number(2), Name varchar(20), Salary number
(11,2) );
·
SELECT MAX(ID) FROM T1;
·
SELECT MIN(ID) FROM T1;
·
SELECT COUNT(*) FROM T1;
·
SELECT AVG(ID) FROM T1;
(Correct)
Explanation
Cloud services layer
does not store average value of column data, and so, it gets computed and
requires a running warehouse.
Question 68: Correct
A Snowflake account
can be hosted on any of the many cloud platforms. Please select all the correct
answers.
·
Azure
(Correct)
·
AWS
(Correct)
·
Oracle Cloud
·
Dropbox
·
Google Cloud (GCP)
(Correct)
Explanation
A Snowflake account
can be hosted on any of the following cloud platforms:
Amazon Web Services (AWS)
Google Cloud Platform (GCP), and
Microsoft Azure (Azure)
Question 69: Correct
Each time the
persisted result for a query is reused, Snowflake resets the 24-hour retention
period for the result, up to a maximum of 31 days from the date and time that
the query was first executed.(TRUE / FALSE)
·
FALSE
·
TRUE
(Correct)
Explanation
Each time the
persisted result for a query is reused, Snowflake resets the 24-hour retention
period for the result, up to a maximum of 31 days from the date and time that
the query was first executed. After 31 days, the result is purged and the next
time the query is submitted, a new result is generated and persisted.
Question 70: Correct
If you find a
data-related tool that is not listed as part of the Snowflake ecosystem, what
industry standard options could you check for as a way to easily connect to
Snowflake? (Select 2)
·
Check to see if there is a petition in the community to create a
driver
·
Check to see if you can develop a driver and put it on GitHub
·
Check to see if the tool can connect to other solutions via JDBC
(Correct)
·
Check to see if the tool can connect to other solutions via ODBC
(Correct)
Explanation
ODBC (Open Database
Connectivity) and JDBC (JAVA Database Connectivity) are the industry standard
options to connect Snowflake easily.
Question 71: Correct
Who pays for the
compute usage of the Reader account?
·
Provider
(Correct)
·
Consumer
Explanation
Provider pays the
compute of the reader account because the consumer who uses the reader account
doesn't owns the accounts( not a customer of Snowflake for that account).
Question 72: Correct
A CAST command
(symbol) will force a value to be output as a certain datatype. Which of the
following code samples will result in the "employeename" being output
using the VARCHAR datatype?
·
SELECT employeename||VARCHAR
·
SELECT VARCHAR(employeename)
·
SELECT employeename::VARCHAR
(Correct)
·
SELECT employeename AS VARCHAR
Explanation
:: is used for Casting
in Snowflake.
Question 73: Correct
What type of Privilege
Data Consumer should have to administer shares
·
EXPORT SHARE
·
LIST SHARE
·
IMPORT SHARES
(Correct)
·
SHOW SHARE
Explanation
Data Consumer must use
ACCOUNTADMIN role or a role granted the IMPORT SHARES global privilege to
administer shares.
Question 74: Correct
Which of the following
object types are child objects within schemas?
·
Stages
(Correct)
·
Stored Procedures
(Correct)
·
File Formats
(Correct)
·
User Defined Functions
(Correct)
·
Roles
·
Sequences
(Correct)
Explanation
Role is not child
object of schema.
Question 75: Correct
New or modified data
in tables in a share are immediately not available to all consumers who have
created a database from a share
·
TRUE
·
FALSE
(Correct)
Explanation
New or modified data
in tables in a share are immediately available to all consumers who have
created a database from a share. You must grant usage on new objects created in
a database in a share in order for them to be available to consumers.
Question 76: Correct
Which table type
disappears after the close of the session and therefore has no fail-safe, and
no time travel options after the close of the session?
·
External
·
Temporary
(Correct)
·
Transient
·
Permanent
Explanation
Temporary table is
tied to a session only.
Question 77: Correct
In the History Page, a
query shows Bytes Scanned having Assigned Partitions: 110, Scanned Partitions
58, and Original Partitions 110. Why did the optimizer show fewer partitions
scanned than assigned?
·
The static optimization determined the number of possible micro
partitions would be 110 but the dynamic optimization was able to prune some of
the partitions from a joined table
(Correct)
·
During the execution of the query, new data was added to the
table and the optimizer had to add those micro partitions into the scan.
·
The optimizer estimated only 58 partitions would need to be scan
but during the execution of the query, the optimizer realized it would have to
read all 110 micro partitions
·
The query was using an Xlarge warehouse and could scan the
partitions in parallel
·
One of the tables in the query was an external table and didn’t
have micro partitions The metadata for the table was out of date and there were
really only 58 partitions total
Explanation
Snowflake produces
well-clustered data in tables in micro-partitions. Snowflake only targets those
micro-partitions which come under the range of query criteria. If table is
clustered well, Snowflake scans only few of the micro-partitions.
Question 78: Correct
John wants to share a
VIEW with Monica but John wants to hide View definition and details from
Monica. What type of view John should create and share with Monica?
·
Materialized
·
Standard
·
Secured
(Correct)
·
None
Explanation
Some of the internal
optimizations for views require access to the underlying data in the base
tables for the view. This access might allow data that is hidden from users of
the view to be exposed through user code, such as user-defined functions, or
other programmatic methods. Secure views do not utilize these optimizations,
ensuring that users have no access to the underlying data.
Question 79: Correct
Snowflake data
warehouse is not built on an existing database or “big data” software platform
such as Hadoop.
·
TRUE
(Correct)
·
FALSE
Explanation
Snowflake is 100%
cloud native data platform.
Question 80: Correct
Which is not the
Security function of cloud services layer?
·
Authentication
·
Pruning using metadata about micro-partitions
(Correct)
·
Access control for shares
·
Encryption and key management
·
Access control for users and roles
Explanation
Security Services
under Cloud Services has following main functions: - Authentication -Access
control for users and roles -Access control for shares Encryption and key
management Optimizer service of Cloud services takes care of Pruning using
metadata about micro-partitions
Question 81: Correct
If you click on
"History" in the ribbon, how far back in time are you able to view
history?
·
365 days
·
24 hours
·
14 days
(Correct)
·
7days
·
30 days
Explanation
History is available
for only 14 days through UI. Older history can be found in ACCOUNT_USAGE.
Question 82: Correct
How often does
Snowflake release new features?
·
Monthly
·
Bi-Weekly
·
Weekly
(Correct)
·
Yearly
Explanation
Snowflake releases new
upgrades and patches weekly.
Question 83: Correct
What is the name of
the Snowflake-produced Command Line Interface tool?
·
SnowCLI
·
SnowSQL
(Correct)
·
SnowConnect
·
Snow Command
Explanation
SnowSQL is the
next-generation command line client for connecting to Snowflake to execute SQL
queries and perform all DDL and DML operations, including loading data into and
unloading data out of database tables.
Question 84: Correct
Reclustering in
Snowflake is automatic. (TRUE / FALSE)
·
FALSE
·
TRUE
(Correct)
Explanation
Reclustering in
Snowflake is automatic; no maintenance is needed.
Question 85: Correct
Please choose the
correct Table Type from the given access.
·
TEMPORARY
(Correct)
·
PERMANENT
(Correct)
·
INTERNAL
·
EXTERNAL
(Correct)
·
TRANSIENT
(Correct)
Explanation
There are four types
of tables - Permanent, Temporary, Transient and External.
Question 86: Correct
Which cache stores
object definitions and statistics?
·
Warehouse Cache
·
Metadata Cache
(Correct)
·
Results Cache
Explanation
Metadata Management
has following major functions: - Stores metadata as data is loaded into the system
- Handles queries that can be processes completely from metadata -Used for Time
Travel and Cloning -Every aspect of Snowflake architecture leverages met
Question 87: Correct
Which layer is
responsible for Data Security and Authentication?
·
Compute Layer
·
Storage Layer
·
Cloud Agnostic Layer
·
Cloud Services
(Correct)
Explanation
Cloud Services Layer
is responsible for Data and Security along with other services like Infrastructure
Management, Optimization, Transaction Management.
Question 88: Correct
The best method to
assist pruning on a large table is to:
·
Create a CLUSTERED INDEX on the table
·
Define a HASH TABLE for the table
·
Define a CLUSTER KEY for the table
(Correct)
·
Define a PARTITIONING KEY on the table
·
Create a DENSE INDEX on the table
Explanation
A clustering key is a
subset of columns in a table (or expressions on a table) that are explicitly
designated to co-locate the data in the table in the same micro-partitions.
This is useful for very large tables where the ordering was not ideal (at the
time the data was inserted/loaded) or extensive DML has caused the table’s
natural clustering to degrade.
Question 89: Correct
If a query is slow,
how can the query be changed to improve the query?
·
Use an ORDER BY on the query to order the data before it’s
searched
·
Reorder the list of tables in the query to change the order the
tables are read in the query
·
If possible, use the table’s defined CLUSTER KEY in the query
(Correct)
·
Provide a hint to the optimizer
·
Change the order of the where clause to force the optimizer to
consider the most effective filter first
Explanation
A good Clustering
helps reduce the scanning of micro-partitions, which results in better
performance.
Question 90: Correct
Each share contains a
single database, and all other objects included in the share must be from this
same database.
·
FALSE
(Correct)
·
TRUE
Explanation
Snowflake data
providers can share data that resides in different databases by using secure
views. A secure view can reference objects such as schemas, tables, and other
views from one or more databases, as long as these databases belong to the same
account.
Question 91: Correct
No need to grant usage
on new objects created in a database in a share in order for them to be
available to consumers.
·
TRUE
·
FALSE
(Correct)
Explanation
You must grant usage
on new objects created in a database in a share in order for them to be
available to consumers.
Question 92: Correct
Which cache type runs
on a 24 hour "clock"?
·
Results Cache
(Correct)
·
Warehouse Cache
·
Metadata Cache
Explanation
Each time the
persisted result for a query is reused, Snowflake resets the 24-hour retention
period for the result, up to a maximum of 31 days from the date and time that
the query was first executed. After 31 days, the result is purged and the next
time the query is submitted, a new result is generated and persisted.
Question 93: Correct
Choose the right
hierarchy of Snowflake objects from the given options.
·
ACCOUNT > DATABASE > SCHEMA > TABLE
(Correct)
·
ACCOUNT > STAGE > DATABASE > SCHEMA > TABLE
·
ACCOUNT > SCHEMA > DATABASE > TABLE
·
ACCOUNT > DATABASE > STAGE > SCHEMA
Explanation
The top-most container
is the customer ACCOUNT, within which resides DATABASE. Each DATABASE can have
1 or more SCHEMA and each SCHEMA can have one or more TABLES.
Question 94: Correct
Snowflake follows the
Staged release process for new releases. How many days it take to normally to
complete all the stages of the staged release.
·
7 days
·
3 days
·
1 day
·
2 days
(Correct)
Explanation
Snowflake follows 3
stages in one staged release. Which happens in 2 days. - Day 1 – Stage 1 (early
access) to designated Enterprise accounts. - Day 1 or 2 – Stage 2 (regular
access) for all Standard Edition accounts. - J31Day 2 – Stage 3 – (final) for
all Enterprise Edition and VPS accounts.
Question 95: Correct
To change the
warehouse that will be used to run a SQL command within a specific worksheet
(for example, changing the worksheet so that it uses LARGE), what two options
are available?
·
Run a SQL command like "SET WAREHOUSE
CONTEXT=LARGE_WH"
·
Run a SQL Command like "USE WAREHOUSE LARGE_WH;"
(Correct)
·
Update the Warehouse field in the Context Menu located above the
worksheet.
(Correct)
·
Go to Worksheet properties page and set the Warehouse field to
LARGE_WH
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. Warehouse can be also set using Web UI Context pane.
Question 96: Correct
Snowflake Resource
Monitor allows administrators to set thresholds and triggers to track credit
usage. What methods are most effective for resource monitoring? (Check all that
apply).
·
Setting quota thresholds for trigger actions and notifications
(Correct)
·
Tracking and controlling credit consumption on a monthly basis
(Correct)
·
Tracking consumption on an hourly basis
·
Control global monthly credit usage for an account
(Correct)
Explanation
Resource monitors are
not intended for strictly controlling consumption on an hourly basis; they are
intended for tracking and controlling credit consumption per interval (day,
week, month, etc.). Also, they are not intended for setting precise limits on
credit usage (i.e. down to the level of individual credits). For example, when
credit quota thresholds are reached for a resource monitor, the assigned
warehouses may take some time to suspend, even when the action is Suspend
Immediate, thereby consuming additional credits.
Question 97: Correct
There are two types of
Snowflake releases. Please choose all applicable.
·
Upgrade Release
·
Installation release
·
New Release
(Correct)
·
Patch Release
(Correct)
Explanation
There are two types of
releases: 1 - New Release - It covers new features, behavior changes,
enhancement, updates and fixes 2- Patch Release - It only includes fixes.
Question 98: Incorrect
Which is not the
function of Optimization activities of Cloud Services layer?
·
Handles Queries that can be processes completely from metadata
(Correct)
·
Automatic JOIN order optimization
(Incorrect)
·
Pruning using metadata about micro-partitions
·
Automatic statistic gathering
Explanation
Optimizer Service has
following main functions: - Cost-based optimization (CBO) i.e., SQL Optimizer
-Automatic JOIN order optimization -Automatic statistics gathering -Pruning
using metadata about micro-partitions Metadata Management service takes care of
Handling queries that can be processed completely from metadata like SELECT
MAX(ID) FROM T1;
Question 99: Correct
Which of the following
are Snowflake view types? (Select 3)
·
Secure
(Correct)
·
Materialized
(Correct)
·
Permanent
·
Standard
(Correct)
·
Transient
Explanation
Permanent and Transient
are types of Table not View.
Question 100: Correct
Can you have a
database overlap across two Snowflake account?
·
No
(Correct)
·
Yes
Explanation
A database can not
overlap across two Snowflake accounts.
Question 101: Correct
An hour ago, you ran a
complex query. You then ran several simple queries from the same worksheet. You
want to export the results from the complex query but they are no longer loaded
in the Results pane of the worksheet. What is the least costly way to download
the results?
·
Type the command Select
RESULTS(<Account>,<User>,<QueryID>) in the Worksheet and
click "Run"
·
Type the command SELECT RESULTS(-3) into the Worksheet and click
"Run"
·
Click on History -> Locate the Query -> Click the QueryID
-> Use the "Export Result" button
(Correct)
·
Click on History -> Locate the Query -> Click
"Download Results" in column 3
Explanation
The History page
displays queries executed in the last 14 days, starting with the most recent ones.
You can use the End Time filter to display queries based on a specified date;
however, if you specify a date earlier than the last 14 days, no results are
returned. You can export results only for queries for which you can view the
results (i.e. queries you’ve executed). If you didn’t execute a query or the
query result is no longer available, the Export Result button is not displayed
for the query. The web interface only supports exporting results up to 100 MB
in size. If a query result exceeds this limit, you are prompted whether to
proceed with the export. The export prompts may differ depending on your
browser. For example, in Safari, you are prompted only for an export format
(CSV or TSV). After the export completes, you are prompted to download the
exported result to a new window, in which you can use the Save Page As… browser
option to save the result to a file.
Question 102: Correct
A query you initiated
is taking too long. You've gone into the History area to check whether this
query (which usually runs every hour) is supposed to take a long time. Check
all true statements.
·
Information in the History area can be filtered to show a single
User
(Correct)
·
Information in the History area can be filtered to show a single
Session
(Correct)
·
Information in the History area can help you visually compare
query times
(Correct)
·
If you decide to end the query, you must return to the worksheet
to Abort the query
·
Information in the History area can be filtered to show a single
Warehouse
(Correct)
Explanation
Snowflake provides
many filter criteria's like - Status, User, Warehouse, Duration, End Time,
Session ID, SQL Text, Query ID, Statement Type, Query Tag
Question 103: Correct
SQL Clause which helps
defining the clustering key:
·
CLUSTER BY
(Correct)
·
CLUSTER ON
·
CLUSERTING ON
·
CLUSTERING BY
Explanation
example - create or
replace table t1 (c1 date, c2 string, c3 number) cluster by (c1, c2);
Question 104: Correct
Which of the following
Snowflake Editions encrypt all data transmitted over the network within a
Virtual Private Cloud (VPC)?
·
Business Critical
(Correct)
·
Enterprise
·
Standard
Explanation
Business Critical
edition has lot many additional features like - Tri-Secret Secure using
Customer-managed key, AWS and Azure Private Link supports.
Question 105: Correct
Which of the following
workload is Snowflake suited for?
·
Data Engineering
(Correct)
·
Data Lakes
(Correct)
·
Data Science
(Correct)
·
Data Applications
(Correct)
·
Data Warehouse
(Correct)
·
Data Sharing and Exchanges
(Correct)
Explanation
Snowflake is not a
just Data Warehouse platform. It is a Data platform which can handle all of
these workloads.
Question 106: Correct
User-Defined Function
(UDF) supports
·
SQL
(Correct)
·
Python
·
Java
(Correct)
·
Go
·
JavaScript
(Correct)
Explanation
UDF supports
JavaScript, SQL and Java.
Question 107: Correct
You have a dashboard
that connects to Snowflake via JDBC. The dashboard is refreshed hundreds of
times per day. The data is very stable, only changing once or twice per day.
The query run by the dashboard connector user never changes. How will Snowflake
manage changing and non-changing data? Mark all true statements.
·
Snowflake will spin up a warehouse only if the underlying data
has changed
(Correct)
·
Snowflake will show the most up-to-date data each time the
dashboard is refreshed
(Correct)
·
Snowflake will compile results cache data from all user results
so no warehouse is needed
·
Snowflake will spin up a warehouse each time the dashboard is
refreshed
·
Snowflake will re-use data from the Results Cache as long as it
is still the most up-to-date data available
(Correct)
Explanation
Until, data has not
changed and query is same - Snowflake reuses the data from cache. Please note,
Each time the persisted result for a query is reused, Snowflake resets the
24-hour retention period for the result, up to a maximum of 31 days from the
date and time that the query was first executed. After 31 days, the result is
purged and the next time the query is submitted, a new result is generated and
persisted.
Retake test
Continue
Comments
Post a Comment