2025 - General Knowledge Flashcards
1
Q
What does the Snowflake architecture separate?
A
Storage and compute
2
Q
What is a key benefit of Snowflake’s architecture?
A
Users can scale up or down as needed and pay for only the resources they use
3
Q
What does Snowflake use to process queries?
A
Massive parallel processing (MPP) compute clusters
4
Q
What functionality does Snowflake support for data sharing?
A
Real-time secure data sharing
5
Q
What does MPP stand for?
A
Massive parallel processing
6
Q
In MPP, what do multiple processors work on?
A
Different sections of the same user program
7
Q
What does the leader node in MPP maintain?
A
Metadata regarding compute nodes
8
Q
True or False: In Snowflake, users pay for storage and computation together.
A
False
9
Q
Fill in the blank: Snowflake enables users to use and pay for storage and _______ independently.
A
Computation
10
Q
What is the purpose of the leader node in MPP?
A
Communicates with compute nodes for the execution of different parts of the query
11
Q
Which cloud platforms can run Snowflake?
A
AWS, Azure, GCP
12
Q
What are the editions of Snowflake available?
A
Standard, Enterprise, Business Critical, and VPS.
13
Q
What is the WebUI called?
A
Snowsight
14
Q
What is the storage layer in Snowflake?
A
The lowest layer where data is physically stored (cloud storage)
Snowflake organizes data as compressed micro-partitions, containing between 50 MB and 500 MB of uncompressed data.
15
Q
How does Snowflake organize data?
A
Data is organized in a columnar fashion and stored in compressed micro-partitions
Each micro-partition contains metadata about all rows stored.
16
Q
What is the cost of storing data in Snowflake for capacity storage?
A
Approximately $23/TB per month
The cost for on-demand storage is about $40/TB per month.
17
Q
What is the compute layer responsible for in Snowflake?
A
This layer is where queries are executed
It uses virtual warehouses to process queries.
18
Q
What are virtual warehouses in Snowflake?
A
MPP compute clusters with compute nodes allocated by Snowflake
They act as independent compute clusters with CPU, memory, and temporary storage.
19
Q
What flexibility does Snowflake provide with warehouses?
A
Warehouses can be started, stopped, or resized at any time
This flexibility accommodates customers’ computing needs based on operations.
20
Q
What is the billing model used by Snowflake for running a warehouse?
A
Per-second billing (consumption-based)
Running a warehouse consumes credit, which increases as warehouse size increases.
21
Q
What feature does Snowflake offer to limit idle time for warehouses?
A
Auto-suspend and auto-resume
This feature helps manage costs by limiting idle time.
22
Q
What are multicluster warehouses in Snowflake?
A
Additional clusters allocated to improve user performance/concurrence of queries
They help in managing concurrent query execution.
23
Q
What is the role of the cloud services layer in Snowflake?
A
Manages end-to-end workflow including authentication, access control, and query optimization
It runs on a Snowflake-managed virtual warehouse.
24
Q
What are the three layers that make up Snowflake Architecture?
A
Storage, Compute, Cloud Services
25
What two types of access controls does Snowflake support?
Discetionary (managed by the owner) and role based access control.
26
What is the purpose of the result cache?
It is available across virtual warehouses and stores query results for reuse if underlying table data has not changed.
## Footnote
The result cache allows multiple users to access the same query results without re-executing the query.
27
When is data retrieved from the warehouse cache?
Data is retrieved from the warehouse cache when users execute a new query and data is not available in the results cache.
## Footnote
This cache utilizes SSD and memory of the virtual warehouse.
28
What happens when data is already available in the warehouse cache?
There is no need to access the storage layer.
## Footnote
This improves query performance by reducing access time.
29
What does the remote disk cache refer to?
It refers to long-term storage (cloud storage) where data is physically stored.
## Footnote
Despite its name, it is not a cache in the traditional sense but rather the primary storage location.
30
Fill in the blank: The result cache is used when the underlying table data has not _______.
changed
31
What is stored in the warehouse cache?
Data retrieved from remote disk storage when users execute a new query.
## Footnote
This data remains active as long as the warehouse is active.
32
How do you enable managed access and where
On the schema level, CREATE SCHEMA ... WITH MANAGED ACCESS
33
Can the GET command be used in Snowsight
No
34
How should you start a transaction statement
With BEGIN TRANSACTION
35
How can transactions be ended
With COMMIT or ROLLBACK
36
Are there acceptable synonyms for COMMIT and ROLLBACK
Yes. COMMIT WORK and ROLLBACK WORK
37
Explicit transactions should contain only DML or DDL statements or both
DML
38
Can the AUTOCOMMIT setting be changed inside a stored procedure
No
39
What are the two data transfer modes provided by the Snowflake connector for Spark.
Internal transfer: the storage location is managed by Snowflake for data movement.
External transfer: Storage location to facilitate data movement is created and managed by the user during connector installation. User will need to manually delete data files created during data transfer.
40
What languages does Snowpark support
Python, Java, or Scala
41
What permissions are available for Snowpipe
OWNERSHIP
OPERATE
MONITOR
APPLYBUDGET
42
When using snowsql and --mfa-passcode-in-password, how is the token passed
It is appended to the end of the password
43
Using snowsql, how can you specify the mfa passcode
-m TEXT
--mfa-passcode TEXT
--mfa-passcode-in-password
44
Can you configure Snowflake so that different users authenticate using different identity providers
Yes
45
What is a consideration when data has different governance compliance levels to meet
That kind of data can be separated to another account where it can be set at a different Snowflake edition level
46
Can Snowflake handle highly normalized models (3NF)
Yes
47
For reporting and analytics, which is a better approach, 3nf or star schema
star schema
48
What could happen when cloning a table and the retention time is set to 0
If DML operations occur during the clone, and data is purged, the data becomes unavailable and the cloning process will error
49
How does Snowflake store security-related information for an external function that calls code that is executed outside of Snowflake?
API integration
50
Can Snowflake context functions be used in conjunction with column-level security
yes
51
In context functions, what does IS_ROLE_IN_SESSEION return
Returns true if the user's current role in the session inherits the privileges of the specified role
52
Is it possible for a Snowflake Business Critical to share data with an Enterprise account
Yes, a user in the provider account with the privilege to OVERRIDE SHARE RESTRICTIONS sets SHARE_RESTRICTIONS to false when adding the Enterprise consumer account
53
How can you maximize memory and compute resources for a Snowpark stored procedure execute on a WH
By setting max_concurrency_level = 1, it will ensure the procedure has access to the max memory and compute resources available to the warehouse
54
When activating Tri-Secret Secure, at what level is the customer managed key used
The account level
55
What levels make up the Tri-Secret Secure hierarchical model
Account Master Keys
Table Master Keys
File Keys
56
At what frequency does Snowflake rotate the object keys?
30 days
57
A Snowflake Architect is writing a User-Defined Function (UDF) with some unqualified object names.
How will those object names be resolved during execution
Snowflake will only check the schema the UDF belongs to.
58
What does Task_History return
History of task usage within a specified date range. It returns both the completed and running tasks.
59
What is the max rows Tax_History will return
10,000 rows.
The default is 100
60
If no time frame is specified, what date range does Task_History return
It returns the task activity within the last 7 days or the next scheduled execution within the next 8 days.
61
Which vendors provide native Snowflake support for federated authentication and SSO.
Okta and Microsoft ADFS
62
What Snowflake features should be leveraged when modeling using Data Vault?
Snowflake’s support of multi-table inserts into the data model’s Data Vault tables
Snowflake has the ability to load multiple tables at the same time using a single data source
63
In TASK_HISTORY if error_only is set to true, what is returned
Task runs that failed or were canceled.
64
When will a multi-cluster warehouse start a new cluster if it’s running with the economy scaling policy?
Only if the system estimates there’s enough query load to keep the cluster busy for at least 6 minutes.
65
Will this query cost compute credits considering that the previous query ran 5 minutes ago?
CREATE OR REPLACE TABLE MTBL AS
SELECT *
FROM TABLE
(RESULT_SCAN(LAST_QUERY_ID())
);
Yes, The SELECT command doesn't cost compute credits because we are re-using from the cache.
Creating the table structure doesn't cost compute credits either, BUT inserting the rows in the table requires compute power, so we will have to pay compute credits
66
A large join query takes around 3 hours to complete in an L warehouse. After increasing the warehouse size to XL one, the query's performance didn't improve. What can be the cause of it?`
Correct answer
One of the column's values is significantly more than the rest of the values in the column; that's why it produces a skew in your data.
67
Can external functions return a value
They must return a value, and it could be complex datatypes like variant
68
After creating a database and a schema using the following commands:
CREATE OR REPLACE DATABASE MY_DB
DATA_RETENTION_TIME_IN_DAYS=30;
CREATE OR REPLACE SCHEMA S1 DATA_RETENTION_TIME_IN_DAYS=50;
How long will we be able to access the data from the schema using the Time Travel functionality if we drop the database?
30 days, because the database was dropped
69
During the investigation of a slow query, how can an Architect obtain detailed statistics about each step in the query?
Use the Query Profile
70
In clustering info, total_partition_count field in the ouput will indicate the current total count of partitions, but will it display the partitions needed for time travel
no
71
When cloning a database, what permissions are cloned
The clone inherits all granted privileges of all child objects in the source object, excluding the database.
72
When unloading data, what are the supported data file types
JSON, CSV, Parquet
73
How can you add cluster keys using sql
ALTER TABLE MYTABLE CLUSTER BY (USER, CREATED_AT)
74
What are two limitations of insertReport
The 10,000 most recent events are retained.
Events are retained for a maximum of 10 minutes.
75
What is the default number of events returned with TASKHISTORY
100
76
What is recommended for optimizing the cost associated with the Snowflake Kafka connector
Utilize a higher Buffer.flush.time in the connector configuration.
77
Does Bytes scanned show up in the query profile
No, it belongs to the statistic screen
78
What ALTER command will impact a column's availability in Time Travel?
ALTER TABLE … SET DATA TYPE …
79
When data is transferred from a Snowflake primary account to another target account using database replication, which account is billed for the data transfer and compute charges?
The target account is charged for both the data transfer and compute charges.
80
What is the maximum response size per batch of an external function
10mb
81
Can an external fuction be a stored procedure
no
82
How does add optimization work on an alter command
each additional call adds columns to the search optimization, not overrides
83
In a select statement what is used to output a table to a json object
object_construct
84
What does TRY_PARSE_JSON do?
attempts to parse JSON data and returns NULL if the parsing fails
85
What command can we use to list all the object references of a view?
GET_OBJECT_REFERENCES
86
After how many days does the load activity of the COPY INTO command and Snowpipe of Information Schema expire?
14 days
87
What types of objects does Snowflake return when we execute the function GET_OBJECT_REFERENCES?
Tables
Views (Including Secure Views)
88
What kind of joins are supported by MVs
None, not outer or inter or sub
89
Can MVs support Max Min aggs
Yes
90
Can MVs support context functions
No
91
What is not a compression technique for AVRO file formats
BZ2
92
What values return in the Execution Time screen of the Query Profiler?
Processing
Remote Disk IO
Synchronization
Initialization
93
Tri Secret Secure requires at least what level of Edition
Business Critical
94
What is the only compression option for Parquet
Snappy
95
Which hash function in Snowflake will MINIMIZE the likelihood of collisions?
SHA2 with digest_size set to 512
96
What built-in Snowflake features make use of the change tracking metadata for a table?
a STREAMS Object
the CHANGES clause
97
What does setting a '0' or NULL value means on a warehouse
It will never suspend
98
What level is ADD SEARCH OPTIMIZATION
Schema
99
When adding search optimization to a table what do you need, ALL or Ownerhship on the table
Ownership
100
Give an example of sql to clone a table
CREATE TABLE MYTABLE_2 CLONE MYTABLE;
NO AS
101
For a shared database, how does time travel work
time travel is not supported
102
For a shared database is the Public and Information schema automatically shared
NO
103
On a stream APPEND_ONLY ignores what
Updates and Delets
104
What function provides a list of endpoints that need to be accessible to establish private connectivity for a specific Snowflake account?
SYSTEM$ALLOWLIST_PRIVATELINK()
105
By default, what is the MAXIMUM timeout value that is enforced before a SQL statement that is running is canceled by the system?
172800 seconds (2 days)
106
Can an ORG Admin delete all accounts
They can delete all but one, the last one requires Snowflake support
107
At which object type level can the APPLY MASKING POLICY, APPLY ROW ACCESS POLICY and APPLY SESSION POLICY privileges be granted?
GLOBAL
108
When cloning a database what happens to
unconsumed records in the streams
They will be inaccessible
109
With an Enterprise level account, can you adjust how often the encryption keys are rotated
Yes, Set the periodic_data_rekeying parameter to true.
110
Who can set up a Data exchange, org or account admin
accountadmin
111
Is submitting multiple sql statements supported on the REST API
Yes
112
Which commands are not supported in the REST API
PUT
GET
113
With the REST API are commands that perform explicit transactions (i.e. ROLLBACK) supported?
Yes only within a request that specifies multiple statements.
114
What role is required to enable account replication
ORGADMIN
115
What is the SQL to enable account replication
SELECT SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER('.', 'ENABLE_ACCOUNT_DATABASE_REPLICATION', 'true');
116
How do you specify a snowpark warehouse
CREATE OR REPLACE WAREHOUSE snowpark_opt_wh WITH
WAREHOUSE_SIZE = 'MEDIUM'
WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED';
117
Why would you do a snowpark warehouse
Need more control over memory and cpu.
118
What is the memory on each snowpark warehouse
xSmall -> 16 GB
Med -> 256 GB
Large -> 1TG
119
