Facts & Figures Flashcards
Metrics tested in Snowflake Snowpro Core cert
1
Q
Micropartition maximum size
A
16MB compressed, 50-500MB uncompressed data
2
Q
Snowflake now issues behavior change bundles into which an account can opt-in early, or defer. How often are these released
A
Frequency ~ monthly
Opt-in - 4 weeks early
Opt-out - 4 weeks late
3
Q
How often are storage keys rotated?
A
Key rotation - 30 days
4
Q
How often are storage keys re-encrypted?
A
Re-encryption - 365 days
5
Q
What is the default session timeout
A
5 min
6
Q
What is the maximum session timeout
A
240 min
7
Q
How long is Information Schema retained?
A
7 days to 6 month, Complete Task Graph 60 minutes
8
Q
What is the latency for Information Schema?
A
Essentially zero-latency
9
Q
Is the Account Usage schema a super- or sub-set of the Information Schema?
A
Superset.
10
Q
What is the latency for Account Usage Schema?
A
45 min - 3 hours
11
Q
How long is Account Usage Schema stored?
A
1 year
12
Q
What is the minimum the Query Result Cache retained
A
24 hours
13
Q
What is the maximum the Query Result Cache retained
A
31 days
14
Q
What setting controls query timeout?
A
Statement_Timeout_in_Seconds?
15
Q
What is the default query timeout?
A
2 days
16
Q
What is the maximum value for the query timeout?
A
7 days
17
Q
How long is the retention for the Query History in Snowsight?
A
14 days
18
Q
What is the default auto-suspend value for a new Warehouse?
A
10 minutes
19
Q
What is the default auto-suspend value for a new Warehouse created in Snowsight?
A
5 minutes
20
Q
What is the minimum value for multi-cluster warehouses?
A
1
21
Q
What is the maximum value for multi-cluster warehouses?
A
10
22
Q
What are the two scaling policies for a multi-cluster warehouse?
A
Standard, Economy
23
Q
How long does it take to scale out a multi-cluster warehouse set to Standard scaling?
A
As soon as a query queues.
24
Q
How long does it take to scale out a multi-cluster warehouse set to Economy scaling?
A
6 minutes
25
What is the default QAS setting Query_Acceleration_Max_Scale_Factor?
8
26
What is the valid range for the QAS setting Query_Acceleration_Max_Scale_Factor?
0-100
27
What is the minimum Snowflake edition for the query acceleration service?
Enterprise
28
What is the minimum Snowflake edition for the multi-cluster service?
Enterprise
29
What is the minimum Snowflake edition for the tri-security service?
Business Critical
30
What is the maximum size of a variant column?
16MB
31
Recommended file size for COPY (and file-based Snowpipe, for reasonable costs)
100-250MB compressed
32
Maximum # of files loaded by explicit list
100
33
Number of days COPY retains metadata to avoid duplicate loads
64
34
Number of days Snowpipe retains metadata to avoid duplicate loads
14
35
If if input is too big for variant column, what is a potential resolution?
strip_outer_array
36
Default file size during file unload
16MB
37
Maximum file size during file unload
5GB
38
What is the default setting for Max_Extention_Time_In_Days to keep a stream from going stale?
14 days
39
What is the maximum setting for Max_Extention_Time_In_Days to keep a stream from going stale?
90 days
40
What are the maximum number of tasks in a DAG?
1000 tasks
41
What are the maximum number of children in a DAG?
100
42
What is the maximum number of days available for Time-travel in Standard Ed?
1 day
43
What is the maximum number of days available for Time-travel in Enterprise Ed?
90 days
44
What is the available setting for Time-travel in Transient tables?
1 day
45
What is the available setting for Time-travel in Temporary tables?
0 days (not available)
46
What OSes does SnowSQL support?
Mac, Linux, Red Hat, Ubuntu, Windows
47
When creating a new top-level role, what system role should it be granted to?
SYSADMIN
48
What can User Admin manage?
Users and Roles created by the User Admin
49
What can SecurityAdmin manage?
Same as User Admin and can manage GRANTS
50
What is the recommended number of columns in a cluster for a table?
3-4
51
When using multiple columns in a cluster, should they be ordered by cardinality High to Low or Low to High?
Low to High
52
What does it mean to have "low cardinality"?
Few distinct values, e.g. TRUE, FALSE
53
What does it mean to have "high cardinality?"
Many distinct values, e.g. SSN
54
What happens in a join without a join condition?
Exploding joins or cartesian products, where the result is N x N
55
What is the difference in results between Union vs Union All?
Union removes duplicates. Union All includes duplicates?
56
What are options available if results are spilled to disk in a query?
Larger warehouse or processing data in smaller batches
57
What is the problem if Partitions Scanned is all or nearly all of Partitions Total?
Inefficient pruning. Add filters to reduce total rows scanned.
58
What kinds of queries benefit from Search Optimization?
Point lookups (IN, or ==)
Substring query
Semi-structured search
Geospatial
AND and OR
59
What kinds of queries benefit from Query Acceleration Service?
Select, Insert, CTAS
60
How to reference a User Stage?
@~myStage
61
How to reference a Table Stage?
@%myTable
62
How to reference a Named Stage?
@myCustomStage
63
What locations does PUT and GET load/unload data between?
Stage to/from Local Storage
64
What data formats are supported by Copy Load?
XCOPAJ
xml, csv, orc, parquet, avro, json
65
What data formats are supported by Copy Unload?
CSV, JSON, Parquet
66
What file compression formats are used by default when Copying data out?
GZIP or, SNAPPY for Parquet
67
What is the expiration for a Scoped URL?
24 hours
68
What access is required for a Scoped file?
DB access
69
What is the expiration for a File URL?
No expiration
70
What access is required for a File URL?
Stage access
71
What is the expiration for a Signed URL?
Configurable
72
What access is required for a Signed URL?
None
73
MyTable has 200 rows. How many rows are retuned by SELECT * FROM MyTable SAMPLE(10)?
10% or 20 rows.
74
How do you make "SELECT * FROM MyTable SAMPLE(10)" deterministic in back to back calls?
Add SEED(123)
75
MyTable has 200 rows. How many rows are retuned by SELECT * FROM MyTable SAMPLE(10 ROWS)?
10 ROWS.
76
Can a UDF run multiple calculations and statements?
No
77
Can a Stored Procedure run multiple calculations and statements?
Yes
78
When cloning a database or schema, what is NOT included?
Internal Named Stages
Pipes on Internal Stages
Time Travel
79
How do you turn off cache?
USE_CACHED_RESULT = FALSE
Can be turned off at the Account, User, or Session level
80
What algorithm does Snowflake use to determine cardinality?
HyperLogLog
81
What function can turn a SHOW xxx call into a table?
RESULT_SCAN
82
Can an administrator see the results of a query from another user?
No.
83
What can you use to get the query ID of the second-to-last query?
LAST_QUERY_ID(-2)
(note the negative)
84
What can you use to get the query ID of the second query of the session?
LAST_QUERY_ID(2)
(note the positive)
85
Return a list of customers from variant column named src in table cars.
{
"customer": [
"age":"23" ]
"dealership name": "Valley"
...
}
SELECT
src:customer
FROM
cars;
Note the lack of NEED of quotes around customer
86
Return a list of dealerships from variant column named src in table cars.
{
"customer": [
"age":"23" ]
"dealership name": "Valley"
...
}
SELECT
src:"dealership name"
FROM
cars;
Note the NEED of quotes around DEALERSHIP[space]NAME
87
Cast customer age to a number from variant column named src in table cars.
{
"customer": [
"age":"23" ]
"dealership name": "Valley"
...
}
SELECT
src:customer.age::NUM
FROM
cars;
Note dot and ::
88
What does the VALIDATION_MODE do in a COPY INTO command?
Does NOT copy data, but returns the number of rows that would result from the COPY INTO command.
89
What is Snowflake's hybrid architecture?
Shared Disk (central data repository) and Shared Nothing (independent query clusters)
90
What schema and view would you look to find login information within the last 15 minutes?
INFORMATION_SCHEMA.LOGIN_HISTORY().
info schema is updated nearly instantly vs account_usage (45 min lag)
91
What schema and view would you look to find login information within the last 8 months?
ACCOUNT_USAGE.LOGIN_HISTORY()
account usage stores data for up to 1 year, vs information_schema 1w-6mo storage
92
What is the oldest query you can see in the Snowsight History page?
14 days old
93
Question 1
Incorrect
Which privilege is required to change a warehouse's state (stop, start, suspend, resume)?
[Usage, Operate, Modify, Monitor]
Operate
94
How many network policies can be activated for a user at a time?
1
95
What is the command to define clustering when creating a table?
CREATE TABLE ...
CLUSTER BY (exp1, exp2....)
96
Is BOOLEAN supported by Snowflake?
No.
97
During Warehouse provisioning, if any of the compute resources fail to provision, then Snowflake will [restart the warehouse, fix resources]
If any of the compute resources for the warehouse fail to provision during start-up, Snowflake attempts to repair the failed resources
98
When calling a UDF, the UDF will run as the [caller, owner]?
Owner. Always.
99
When calling a SPROC, the SPROC will run as the [caller | owner].
Either.
100
What is the default ownership of a SPROC? [caller | owner]
Owner.
101
What kind of URL is ideal for business intelligence applications or reporting tools that need to display unstructured file contents
Pre-signed URL
102
What kind of URL is ideal for use in custom applications, providing unstructured data to other accounts via a share, or for downloading and ad hoc analysis of unstructured data via Snowsight.
Scoped URL
103
What kind of URL is ideal for custom applications that require access to unstructured data files.
File URL
104
What kind of URL is a Simple HTTPS URL used to access a file via a web browser
Pre-signed
105
What kind of URL is encoded and permits temporary access to a staged file without granting privileges to the stage
Scoped
106
What kind of URL identifies the database, schema, stage, and file path to a set of files
File URL
107
[Serverless | User-managed] Tasks is recommended when you cannot fully utilize a warehouse because too few tasks run concurrently or they run to completion quickly (in less than 1 minute)
Serverless
108
[Serverless | User-managed] Tasks is recommended when you can fully utilize a single warehouse by scheduling multiple concurrent tasks to take advantage of available compute resources.
User-managed
109
What multi-cluster mode is enabled when cluster size is set to [5:5]?
Maximized
110
What multi-cluster mode is enabled when cluster size is set to [4:9]
Auto-scale
111
When creating a stage, this encryption type supports tri-secret secure [SNOWFLAKE_SSE | SNOWFLAKE_FULL]
SNOWFLAKE_FULL
112
When creating a stage, this encryption type supports client encryption [SNOWFLAKE_SSE | SNOWFLAKE_FULL]
SNOWFLAKE_FULL
113
When creating a stage with encryption, which encryption type supports pre-signed URLs [SNOWFLAKE_SSE | SNOWFLAKE_FULL]
SNOWFLAKE_SSE
114
If you have a sequence of 1, insert 5 rows...
then change the sequence to 2
and insert 2 rows, what is the last sequence value (assuming starting at 1?
8
First 5 [ 1, 2, 3, 4, 5]
ALTER to 2
Next 2 [6, 8]
NOT
[1, 2, 3, 4, 5] , [ 7 , 9]
115
What standard role can create resource monitor? [ACCOUNTADMIN, SYSADMIN, ORGADMIN]
ACCOUNTADMIN
116
What grants can view resource monitors?
MONITOR, MODIFY
117
Which Snowflake schema houses information about usage and users?
Account_Usage
118
Which Snowflake schema houses information about system objects?
Information_Schema
119
Which database objects are currently not supported for replication? [Streams, Temp Tables, Transient Tables, Stages]
Stages & TEMPORARY tables
120
Which role has privileges to create warehouses and databases (and other objects) in an account? [ SYSADMIN | ORGADMIN | ACCOUNTADMIN ]
SYSADMIN
121
What is the difference between a Snowflake managed Task and a User managed Task?
User managed task has an assigned warehouse.
122
What drivers does Snowflake support?
JPONG
JDBC
PHP
ODBC
.NET
Go
123
What privileges allows a consumer account to create a database from a share?
AccountAdmin, or IMPORT SHARE privilege
124
Is Snowflake [IRAP – Protected] compliant?
Yes
125
Is Snowflake [ITAR] compliant?
Yes
126
Which transformations are NOT supported by COPY commands? [JOIN | SUM | GROUP BY | TRUNCATE | AGGREGATE | FILTER | FLATTEN]
TRUNCATE, AGGREGATE, FILTER, FLATTEN
127
Do external tables support internal stages?
NO. Internal Tables <> Internal Stages. External Tables <> External Stages
128
What roles can create Resource Monitors? [ SYSADMIN, ACCOUNTADMIN, ORGADMIN]
Only ACCOUNTADMIN
129
What is the minimum edition required to support data clustering?
Standard
130
Can you clone a cloned table?
YES
131
Do Secure UDFS support SQL optimizations?
NO. This can cause data to become insecure through other measures.
132
What is the at-rest encryption mechanism?
AES-256
133
What is the setting to enable warehouse scale-out
MAX_CLUSTER_COUNT
134
What is the minimum Snowflake built-in role that can be used to enforce a network policy on a Snowflake account?
SECURITYADMIN
135
What is another term for DATA EXCHANGE
Data Listing
136
When creating a new Account, can you choose an Org Name?
No. Remember that this may be a new account in an existing org!
137
