Querying

Question 1

With default settings, how long will a query run on snowflake

Answer 1

Snowflake will cancel the query if it runs more than 48 hours
STATEMENT_TIMEOUT_IN_SECONDS

Question 2

T/F Any query that uses the metadata repository does not consume any compute credit

Answer 2

True

Question 3

T/F This statement would use the metadata repository
SELECT COUNT(UDEMY_ID) FROM SNOWFLAKE GROUP BY UDEMY_ID;

Answer 3

False, the Group by is the key

Question 4

What is RESULT_SCAN

Answer 4

A system defined table function. To use in a create table query, use
from table(result_scan(last_query_id()))

Question 5

When choosing a cluster key, what is reccomended

Answer 5

• Cluster columns that are most actively used in selective filters
• If there is room, columns frequently used in join predicates

Question 6

What does the validation_mode=’RETURN_ROWS’ with the Copy Command do

Answer 6

helps to validate the data that is unloaded using the COPY INTO cmd

Question 7

When would you consider adding a cluster key to a table

Answer 7

• The performance of a query has deteriorated over a period of time
• It is multi TB size

Question 8

When do temporary tables expire

Answer 8

as soon as the session ends

Question 9

T/F Transient tables do not have a fail safe period

Answer 9

True

Question 10

Name four times Materialized views may be helpful

Answer 10

Query results contain a small number of rows and/or columns as compared to the original table

Query involves significant processing (aggregates, analysis of semi structured data)

Query is on an external table

View’s base table changes infrequently

Question 11

When calling loadHistoryScan, how should you specify the time range

Answer 11

The most narrow time range that includes a set of data loads. For example, reading the last 10 minutes of history every 8 minutes

Question 12

When creating a clustering key, what is the recommendation for the max number of columns

Answer 12

3 to 4

Question 13

for COPY INTO, what are the copy options

Answer 13

ON_ERROR = { CONTINUE | SKIP_FILE | SKIP_FILE_<num> | 'SKIP_FILE_<num>%' | ABORT_STATEMENT }</num></num>

 SIZE_LIMIT = <num>

 PURGE = TRUE | FALSE

 RETURN_FAILED_ONLY = TRUE | FALSE

 MATCH_BY_COLUMN_NAME = CASE_SENSITIVE | CASE_INSENSITIVE | NONE

 ENFORCE_LENGTH = TRUE | FALSE

 TRUNCATECOLUMNS = TRUE | FALSE

 FORCE = TRUE | FALSE

 LOAD_UNCERTAIN_FILES = TRUE | FALSE

Question 14

What does the default behavior ON_ERROR = ABORT_STATEMENT do

Answer 14

aborts the load operation

Question 15

What are the optional paramaters for COPY INTO

Answer 15

[ FILES = ( ‘<file_name>' [ , '<file_name>' ] [ , ... ] ) ]</file_name></file_name>

[ PATTERN = ‘<regex_pattern>' ]</regex_pattern>

[ FILE_FORMAT = ( { FORMAT_NAME = ‘[<namespace>.]<file_format_name>' |
TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML } [ formatTypeOptions ] } ) ]</file_format_name></namespace>

[ copyOptions ]

[ VALIDATION_MODE = RETURN_<n>_ROWS | RETURN_ERRORS | RETURN_ALL_ERRORS ]</n>

Question 16

What does ENCRYPT do

Answer 16

Encrypts a varchar or binary value using a varchar passphrase

ENCRYPT( <value_to_encrypt> , <passphrase> ,
[ [ <additional_authenticated_data> , ] <encryption_method> ]
)</encryption_method></additional_authenticated_data></passphrase></value_to_encrypt>

Question 17

What does ENCRYPT return

Answer 17

Binary made up of Two or Three concatenated fields:
an initialization vector
the ciphertext
If the encryption mode is AEAD-enabled, then the AEAD tag.

Question 18

T/F To decrypt data encrypted by ENCRYPT(), use DECRYPT(). Do not use DECRYPT_RAW().

Answer 18

True

Question 19

What is the size recommended for the catchphrase in ENCRYPT

Answer 19

at least 8 bytes
follow general best practices for passwords, such as using a mix of uppercase letters, lowercase letters, numbers, and punctuation.