Snowflake Snowpro Core Certification – Q&A
Query processing layer, storage layer, cloud services layer ✔️Ans - What are
snowflake's three disctinct layers?
Classic web UI, Snowsight ✔️Ans - What are the two web UIs Snowflake
offers?
Modern new web UI ✔️Ans - What is Snowsight?
command line interface to snowflake ✔️Ans - what is snowSQL?
Snowpark ✔️Ans - SF provided library that provides APIs to process and
query using standard application code written in language other than in SQL
Temporary, Transient, Permanent ✔️Ans - Table types?
Temporary tables ✔️Ans - · Exist only for the lifetime of a session
· They are not visible to other sessions and are removed immediately once the
session
· Max time travel of 1 day and no fail safe
Transient tables ✔️Ans - · Similar to temporary tables, but they persist
between sessions. They are designed to hold temporary tables that needs to
be accessed across sessions
· Max time travel of 1 day and no fail safe
Permanent tables ✔️Ans - · This is the default type of table in Snowflake, if
you don't specify the type, a table is created as ___________.
· 90 days of time travel and 7 days of failsafe
Micro partitions ✔️Ans - - Managed automatically, no manual work needed
- Each ____________ contains 50-500mb of uncompressed data
- ______________ are added in the order the data is added to the table
- __________ are immutable
- Data is stored in a columnar format
Duo Security Service ✔️Ans - What service is used for MFA?
, Metadata cache, query result cache, virtual warehouse cache (local disk cache)
✔️Ans - three types of caches?
metadata cache ✔️Ans - - Cloud services layer
- Every time a new micro-partition is returned, snowflake stores info about
the partition in the metadata, storing max and min values for each column for
each micro-partition
- It also stores the count of distinct values for each column in the metadata
query result cache ✔️Ans - - Cloud services layer
- Stores results from a query and re-uses for future matching queries
- Results stored for 24 hours, unless re-used during that time in which case
results are kept for another 24 hours and so on
- Can be refreshed up to 31 days after which it is discarded
Virtual warehouse cache (local disk cache) ✔️Ans - - Virtual warehouse
layer
- Each individual warehouse has its own cache
- Warehouse cache may be reused if subsequent queries can ready the
required data from cache rather than from the table
- The warehouse cache is removed if the Virtual warehouse is suspended
Partition pruning ✔️Ans - metadata allows snowflake to eliminate
unnecessary micro-partitions when running queries, boosting overall query
performance. The process of eliminating micro-partitions is known as
_________________
Clustering depth ✔️Ans - Number of micro-partitions that overlap with
other micro-partitions (specific columns)
1 ✔️Ans - What is the most optimal Clustering depth for a table?
zero ✔️Ans - What is the Clustering depth of a table that is unpopulated?
Query profile ✔️Ans - · Provides a graphical representation of a query's
execution
· Provides execution details for a query
· Statistics and details for each step and whole query
Query processing layer, storage layer, cloud services layer ✔️Ans - What are
snowflake's three disctinct layers?
Classic web UI, Snowsight ✔️Ans - What are the two web UIs Snowflake
offers?
Modern new web UI ✔️Ans - What is Snowsight?
command line interface to snowflake ✔️Ans - what is snowSQL?
Snowpark ✔️Ans - SF provided library that provides APIs to process and
query using standard application code written in language other than in SQL
Temporary, Transient, Permanent ✔️Ans - Table types?
Temporary tables ✔️Ans - · Exist only for the lifetime of a session
· They are not visible to other sessions and are removed immediately once the
session
· Max time travel of 1 day and no fail safe
Transient tables ✔️Ans - · Similar to temporary tables, but they persist
between sessions. They are designed to hold temporary tables that needs to
be accessed across sessions
· Max time travel of 1 day and no fail safe
Permanent tables ✔️Ans - · This is the default type of table in Snowflake, if
you don't specify the type, a table is created as ___________.
· 90 days of time travel and 7 days of failsafe
Micro partitions ✔️Ans - - Managed automatically, no manual work needed
- Each ____________ contains 50-500mb of uncompressed data
- ______________ are added in the order the data is added to the table
- __________ are immutable
- Data is stored in a columnar format
Duo Security Service ✔️Ans - What service is used for MFA?
, Metadata cache, query result cache, virtual warehouse cache (local disk cache)
✔️Ans - three types of caches?
metadata cache ✔️Ans - - Cloud services layer
- Every time a new micro-partition is returned, snowflake stores info about
the partition in the metadata, storing max and min values for each column for
each micro-partition
- It also stores the count of distinct values for each column in the metadata
query result cache ✔️Ans - - Cloud services layer
- Stores results from a query and re-uses for future matching queries
- Results stored for 24 hours, unless re-used during that time in which case
results are kept for another 24 hours and so on
- Can be refreshed up to 31 days after which it is discarded
Virtual warehouse cache (local disk cache) ✔️Ans - - Virtual warehouse
layer
- Each individual warehouse has its own cache
- Warehouse cache may be reused if subsequent queries can ready the
required data from cache rather than from the table
- The warehouse cache is removed if the Virtual warehouse is suspended
Partition pruning ✔️Ans - metadata allows snowflake to eliminate
unnecessary micro-partitions when running queries, boosting overall query
performance. The process of eliminating micro-partitions is known as
_________________
Clustering depth ✔️Ans - Number of micro-partitions that overlap with
other micro-partitions (specific columns)
1 ✔️Ans - What is the most optimal Clustering depth for a table?
zero ✔️Ans - What is the Clustering depth of a table that is unpopulated?
Query profile ✔️Ans - · Provides a graphical representation of a query's
execution
· Provides execution details for a query
· Statistics and details for each step and whole query
