Snowflake SnowPro Core Practice Exam
3 Questions and Answers (Graded A)
A user created a transient table and made several changes to it over the course of several days.
There days after the table was created, the user would like to go back to the first version of the
table.
How can this be accomplished? - ANSWER - The transient table version cannot be retrieved
after 24 hours
Which Query Profile results indicates that a warehouse is sized too small? - ANSWER - Bytes
are spilling to external storage.
If you want a dedicated virtual warehouse, which is the lowest SnowFlake edition you should opt
for? - ANSWER - Standard
In Snowflake, all the Virtual Warehouses are dedicated to the users. If you create a virtual
warehouse, you will only be the one using it.
Which Snowflake role can set up a Snowflake Share by default? - ANSWER - ACCOUNTADMIN
Only the ACCOUNTADMIN role has the "CREATE SHARE" privilege by default. The privilege
can be granted to additional roles as needed.
How many credits will consume a medium sized warehouse with 2 clusters running in auto-
scaled mode for 3 hours, considering that the first clusters runs continuously and the second
one runs for 30 minutes in the second hour? - ANSWER - 14.
A medium size warehouse with one cluster consumes four credits per hour. The first cluster will
run continuously for three hours, consuming 12 credits. The second one will run for only 30
minutes, consuming two credits. The total of the warehouse will be 14 credits.
,If a transaction disconnects and goes into a detached state, which cannot be committed or
rolled back, how long will Snowflake take to abort the transaction? - ANSWER - 4 hours
If the transaction is left open or not aborted by the user, Snowflake automatically rolls back the
transaction after being idle for four hours.
You can still abort a running transaction with the system function:
SYSTEM$ABORT_TRANSACTION.
In Snowflake access control model, which entity owns an object by default? - ANSWER - The
role used to create the object.
To own an object means that a role has the OWNERSHIP privilege on the object. Each
securable object is owned by a single role, which by default is the role used to create the object.
Which of the following significantly improves the performance of selective point lookup queries
on a table? - ANSWER - Search Optimization Service
How can a producer share a table with a consumer located in a different region? - ANSWER -
Replicate your account to another region and create a share from that region.
Data sharing works within the same region; however, you can replicate your account to another
region and then share data from that replicated account within that account's region. This is also
true across cloud platforms.
When can user session variables be accessed in a Snowflake scripting procedure? - ANSWER -
When the procedure is defined to execute as CALLER
What COPY INTO SQL command should be used to unload data into multiple files? - ANSWER
- SINGLE=FALSE
The default is SINGLE = FALSE (i.e. unload into multiple files).
A team runs the same query daily, generally with a frequency of fewer than 24 hours, and it
takes around 10 minutes to execute. They realized that the underlying data changes because of
an ETL process that runs every morning.
, How can they use the results cache to save the 10 minutes that the query is being executed? -
ANSWER - After the ETL run, execute the identical queries so that the result remains in the
cache.
In this case, because the underlying data changes every morning due to the ETL process, the
results cache may not be useful for the daily query execution. However, suppose the team
executes an identical query immediately after the ETL process runs. In that case, the results of
that query will be stored in the results cache and can be retrieved for subsequent queries. By
doing so, the team can save the 10 minutes that the query is being executed by retrieving the
results from the cache.
A warehouse ran for 62 seconds, and it was suspended. After some time, it ran for another 20
seconds. For how many seconds will you be billed? - ANSWER - 122 seconds.
You will be billed for 122 seconds (62 + 60 seconds) because warehouses are billed for a
minimum of one minute. The price would be different if the warehouse wasn't suspended before
executing the second query.
For example, if we had only run a query, and it had only run for 62 seconds, you would be billed
for these 62 seconds. If it had only run for 20 seconds, you would've been billed for 60 seconds.
What are the main differences between the Virtual Private Snowflake (VPS) and Business
Critical Editions? - ANSWER - Snowflake VPS provides a dedicated metadata store and pool of
computing resources, whereas it's not included in the Business Critical Edition.
Snowflake VPS provides a completely separate Snowflake environment, isolated from all other
Snowflake accounts, whereas it's not included in the Business Critical Edition
What is true about sharing data in Snowflake? - ANSWER - A Snowflake account can both
provide and consume shared data.
The Data Consumer pays only for compute resources to query the shared data.
With Secure Data Sharing, no actual data is copied or transferred between accounts. The only
charges to consumers are for the compute resources (i.e. virtual warehouses) used to query the
shared data.
Any full Snowflake account can both provide and consume shared data.
3 Questions and Answers (Graded A)
A user created a transient table and made several changes to it over the course of several days.
There days after the table was created, the user would like to go back to the first version of the
table.
How can this be accomplished? - ANSWER - The transient table version cannot be retrieved
after 24 hours
Which Query Profile results indicates that a warehouse is sized too small? - ANSWER - Bytes
are spilling to external storage.
If you want a dedicated virtual warehouse, which is the lowest SnowFlake edition you should opt
for? - ANSWER - Standard
In Snowflake, all the Virtual Warehouses are dedicated to the users. If you create a virtual
warehouse, you will only be the one using it.
Which Snowflake role can set up a Snowflake Share by default? - ANSWER - ACCOUNTADMIN
Only the ACCOUNTADMIN role has the "CREATE SHARE" privilege by default. The privilege
can be granted to additional roles as needed.
How many credits will consume a medium sized warehouse with 2 clusters running in auto-
scaled mode for 3 hours, considering that the first clusters runs continuously and the second
one runs for 30 minutes in the second hour? - ANSWER - 14.
A medium size warehouse with one cluster consumes four credits per hour. The first cluster will
run continuously for three hours, consuming 12 credits. The second one will run for only 30
minutes, consuming two credits. The total of the warehouse will be 14 credits.
,If a transaction disconnects and goes into a detached state, which cannot be committed or
rolled back, how long will Snowflake take to abort the transaction? - ANSWER - 4 hours
If the transaction is left open or not aborted by the user, Snowflake automatically rolls back the
transaction after being idle for four hours.
You can still abort a running transaction with the system function:
SYSTEM$ABORT_TRANSACTION.
In Snowflake access control model, which entity owns an object by default? - ANSWER - The
role used to create the object.
To own an object means that a role has the OWNERSHIP privilege on the object. Each
securable object is owned by a single role, which by default is the role used to create the object.
Which of the following significantly improves the performance of selective point lookup queries
on a table? - ANSWER - Search Optimization Service
How can a producer share a table with a consumer located in a different region? - ANSWER -
Replicate your account to another region and create a share from that region.
Data sharing works within the same region; however, you can replicate your account to another
region and then share data from that replicated account within that account's region. This is also
true across cloud platforms.
When can user session variables be accessed in a Snowflake scripting procedure? - ANSWER -
When the procedure is defined to execute as CALLER
What COPY INTO SQL command should be used to unload data into multiple files? - ANSWER
- SINGLE=FALSE
The default is SINGLE = FALSE (i.e. unload into multiple files).
A team runs the same query daily, generally with a frequency of fewer than 24 hours, and it
takes around 10 minutes to execute. They realized that the underlying data changes because of
an ETL process that runs every morning.
, How can they use the results cache to save the 10 minutes that the query is being executed? -
ANSWER - After the ETL run, execute the identical queries so that the result remains in the
cache.
In this case, because the underlying data changes every morning due to the ETL process, the
results cache may not be useful for the daily query execution. However, suppose the team
executes an identical query immediately after the ETL process runs. In that case, the results of
that query will be stored in the results cache and can be retrieved for subsequent queries. By
doing so, the team can save the 10 minutes that the query is being executed by retrieving the
results from the cache.
A warehouse ran for 62 seconds, and it was suspended. After some time, it ran for another 20
seconds. For how many seconds will you be billed? - ANSWER - 122 seconds.
You will be billed for 122 seconds (62 + 60 seconds) because warehouses are billed for a
minimum of one minute. The price would be different if the warehouse wasn't suspended before
executing the second query.
For example, if we had only run a query, and it had only run for 62 seconds, you would be billed
for these 62 seconds. If it had only run for 20 seconds, you would've been billed for 60 seconds.
What are the main differences between the Virtual Private Snowflake (VPS) and Business
Critical Editions? - ANSWER - Snowflake VPS provides a dedicated metadata store and pool of
computing resources, whereas it's not included in the Business Critical Edition.
Snowflake VPS provides a completely separate Snowflake environment, isolated from all other
Snowflake accounts, whereas it's not included in the Business Critical Edition
What is true about sharing data in Snowflake? - ANSWER - A Snowflake account can both
provide and consume shared data.
The Data Consumer pays only for compute resources to query the shared data.
With Secure Data Sharing, no actual data is copied or transferred between accounts. The only
charges to consumers are for the compute resources (i.e. virtual warehouses) used to query the
shared data.
Any full Snowflake account can both provide and consume shared data.
