D4.1 - OUTLINE BEST PRACTICES FOR SNOWFLAKE PERFORMANCE MANAGEMENT ON STORAGE

Question 1

What is micro-partitioning?

Answer 1

• The physical tables that comprise Snowflakes logical tables
• Snowflake automatically partitions the data so that users do not need to define a a partition schema
• Micro partitions are immutable and each micro-partition obtains between 50-500 MB on uncompressed data
• Snowflake stores metadata about all rows stored in a micro-partition

Question 2

How does micro-partitioning enable clustering?

Answer 2

• By creating a metadata unique to the clusters created by micro-partitioning

Question 3

What metadata is saved for clustered data?

Answer 3

• The total number of micro-partitions that comprise the table.
• The number of micro-partitions containing values that overlap with each other (in a specified subset of table columns).
• The depth of the overlapping micro-partitions.
• The unique values contained within partitions

Question 4

What is a clustering key?

Answer 4

• A clustering key is a subset of columns in a table (or expressions on a table) that are explicitly designated to co-locate the data in the table in the same micro-partitions.
• This is useful for very large tables where the ordering was not ideal (at the time the data was inserted/loaded) or extensive DML has caused the table’s natural clustering to degrade.

Question 5

What is a materialized view?

Answer 5

• A materialized view if a pre-computed data set derived from a query specification (the SELECT in the view definition) and stored for later use
• because the data is pre-computed querying the view is faster, which can be significant when a query is being run frequently` or is sufficiently complex.
• Materialized views can speed up expansive aggregation, projection, and selection operations on large datasets

Question 6

What is the benefit and cost of a materialized view?

Answer 6

• Materialized views are designed to improve query performance for workloads composed of common, repeated query patterns.
• However, materializing intermediate results incur additional costs. As such, before creating a materialized view, you should consider whether the costs are offset by the savings from re-using these results frequently enough.

Question 7

What is the ‘Search Optimization Service’?

Answer 7

• The search optimization service aims to significantly improve the performance of selective point lookup queries on tables
• To improve performance for point lookups, the search optimization service relies on a persistent data structure that serves as an optimized search access path