SnowPro Core Certification Questions part -2
Question 1: Correct
Which of the Snowflake shared view can be used to query the Snowflake Query History?
(Select 1)
•
QUERY_HISTORY view in INFORMATION_SCHEMA
•
QUERY_HISTORY_VIEW view in ACCOUNT_USAGE
•
QUERY_HISTORY view in ACCOUNT_USAGE
(Correct)
•
QUERY_HISTORY_VIEW view in INFORMATION_USAGE
Explanation
QUERY_HISTORY view in ACCOUNT_USAGE view can be used to query Snowflake
query history by various dimensions (time range, session, user, warehouse, etc.) within
the last 365 days (1 year).
Question 2: Correct
What is the minimum billing charge for provisioning compute resources?
•
1 second
, •
120 seconds
•
30 seconds
•
60 seconds
(Correct)
Explanation
The minimum billing charge for provisioning compute resources 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.
Question 3: Incorrect
You have a table t1 with a column j that gets populated by a sequence s1. s1 is defined
to start from 1 and with an increment of 1. create or replace sequence s1 start = 1
increment = 1 ; create or replace table t1 ( i int, j int default s1.nextval ); You inserted 3
records in table t1: insert into t1 values (1,s1.nextval), (2,s1.nextval), (3,s1.nextval);
After that insert statement, you altered the sequence s1 to set the increment to -4:
alter sequence s1 set increment = -4; You again inserted 2 records in table t1: insert
into t1 values (4,s1.nextval), (5,s1.nextval); What would be the result of the following
query? select j from t1 where i = 4;
•
5
•
, 0
•
-1
(Incorrect)
•
3
•
4
(Correct)
Explanation
ALTER SEQUENCE command takes effect after the second use of the sequence after
executing the ALTER SEQUENCE command.
So, if you fetch row where i = 5, you will find j = 0 [row 4 value of j i.e., 4 + (-4) = 0]
Question 4:
Skipped
Which of these sampling method keywords are used to specify which method to use?
(Select 2)
•
BERNOULLI | BLOCK
•
, BERNOULLI | ROW
(Correct)
•
SYSTEM | ROW
•
SYSTEM | BLOCK
(Correct)
Explanation
BERNOULLI | ROW and SYSTEM | BLOCK are used to specify the sampling method in
SELECT query. BERNOULLI (or ROW): Includes each row with a <probability> of p/100.
Similar to flipping a weighted coin for each row. SYSTEM (or BLOCK): Includes each
block of rows with a <probability> of p/100. Similar to flipping a weighted coin for each
block of rows. This method does not support fixed-size sampling. Sampling method is
optional. If no method is specified, the default is BERNOULLI. Example : select * from t1
tablesample bernoulli (25); This query will return a sample of a table in which each row
has a 25% probability of being included in the sample
Question 5:
Skipped
Which function can be used in combination with COPY command to convert the rows
in a relational table to a single VARIANT column and unload the rows into a file?
•
COPY_CONSTRUCT
•
Question 1: Correct
Which of the Snowflake shared view can be used to query the Snowflake Query History?
(Select 1)
•
QUERY_HISTORY view in INFORMATION_SCHEMA
•
QUERY_HISTORY_VIEW view in ACCOUNT_USAGE
•
QUERY_HISTORY view in ACCOUNT_USAGE
(Correct)
•
QUERY_HISTORY_VIEW view in INFORMATION_USAGE
Explanation
QUERY_HISTORY view in ACCOUNT_USAGE view can be used to query Snowflake
query history by various dimensions (time range, session, user, warehouse, etc.) within
the last 365 days (1 year).
Question 2: Correct
What is the minimum billing charge for provisioning compute resources?
•
1 second
, •
120 seconds
•
30 seconds
•
60 seconds
(Correct)
Explanation
The minimum billing charge for provisioning compute resources 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.
Question 3: Incorrect
You have a table t1 with a column j that gets populated by a sequence s1. s1 is defined
to start from 1 and with an increment of 1. create or replace sequence s1 start = 1
increment = 1 ; create or replace table t1 ( i int, j int default s1.nextval ); You inserted 3
records in table t1: insert into t1 values (1,s1.nextval), (2,s1.nextval), (3,s1.nextval);
After that insert statement, you altered the sequence s1 to set the increment to -4:
alter sequence s1 set increment = -4; You again inserted 2 records in table t1: insert
into t1 values (4,s1.nextval), (5,s1.nextval); What would be the result of the following
query? select j from t1 where i = 4;
•
5
•
, 0
•
-1
(Incorrect)
•
3
•
4
(Correct)
Explanation
ALTER SEQUENCE command takes effect after the second use of the sequence after
executing the ALTER SEQUENCE command.
So, if you fetch row where i = 5, you will find j = 0 [row 4 value of j i.e., 4 + (-4) = 0]
Question 4:
Skipped
Which of these sampling method keywords are used to specify which method to use?
(Select 2)
•
BERNOULLI | BLOCK
•
, BERNOULLI | ROW
(Correct)
•
SYSTEM | ROW
•
SYSTEM | BLOCK
(Correct)
Explanation
BERNOULLI | ROW and SYSTEM | BLOCK are used to specify the sampling method in
SELECT query. BERNOULLI (or ROW): Includes each row with a <probability> of p/100.
Similar to flipping a weighted coin for each row. SYSTEM (or BLOCK): Includes each
block of rows with a <probability> of p/100. Similar to flipping a weighted coin for each
block of rows. This method does not support fixed-size sampling. Sampling method is
optional. If no method is specified, the default is BERNOULLI. Example : select * from t1
tablesample bernoulli (25); This query will return a sample of a table in which each row
has a 25% probability of being included in the sample
Question 5:
Skipped
Which function can be used in combination with COPY command to convert the rows
in a relational table to a single VARIANT column and unload the rows into a file?
•
COPY_CONSTRUCT
•
