Snowflake Storage Layer frequently asked Interview Questions #snowflake #micropartition #database

Summary

This video script offers an in-depth look at Snowflake's storage architecture, focusing on its three-layer structure: cloud service, query processing, and database storage. It explains the concept of micro-partitions, their creation, and their role in data storage and querying. The script addresses common interview questions about micro-partitions, including their immutability, inability to access specific partitions, and the lack of control over their number during data loading. It also touches on Snowflake's reliance on cloud service providers for storage and the limitations regarding row-level locking, positioning Snowflake primarily for OLAP systems rather than OLTP.

Takeaways

• 🌐 Snowflake has a three-layered architecture consisting of the cloud service layer, query processing layer, and database storage layer.
• 💾 The database storage layer is responsible for storing data in micro partitions, which are subsets of a table's data set arranged in a specific format.
• 🔄 When data is loaded into a Snowflake table, it is split into micro partitions, compressed, and then stored in the storage layer with metadata.
• 🔍 Micro partitions are used for query optimization; the engine uses metadata to decide which micro partitions to load into memory for processing.
• 📏 Micro partitions are immutable, meaning data within them cannot be modified directly. Instead, updates or deletes result in the creation of new micro partitions.
• 🚫 Users cannot access or control the number of micro partitions created during data loading; this is managed automatically by Snowflake based on data volume and table structure.
• 🛡️ Micro partitions are encrypted and stored in the Snowflake storage layer, ensuring data security.
• 🚀 Snowflake uses cloud service providers like AWS, GCP, or Azure for storage services, but users do not have direct access to these underlying accounts.
• ❌ Snowflake is not suitable for OLTP systems due to its micro partition-level locking granularity, making it more appropriate for OLAP systems.
• 🔑 To overcome the limitations of micro partitions, Snowflake recommends batch loading data and minimizing update operations.
• 👍 The video encourages viewers to like and subscribe for more informative content on Snowflake's data storage layer.

Q & A

What is the three-layer architecture of Snowflake?

The three-layer architecture of Snowflake consists of the Cloud Service Layer, the Query Processing Layer, and the Database Storage Layer. The Cloud Service Layer manages the services, the Query Processing Layer handles the computation, and the Database Storage Layer is responsible for data storage.

What is a micro partition in Snowflake?

A micro partition in Snowflake is a subset of a complete table dataset that is arranged in a specific format to store data. It is the smallest unit of data storage and is used to optimize query performance.

How are micro partitions created when loading data into a Snowflake table?

When loading data, it is first split into multiple micro partitions based on the number of rows and the structure of the table. These micro partitions are then compressed, encrypted, and stored in the storage layer, with their statistics gathered and stored in the metadata layer.

What happens to micro partitions during an UPDATE or DELETE operation?

Since micro partitions are immutable, during an UPDATE or DELETE operation, a new micro partition is created with the impacted rows and all non-impacted rows. The previous micro partition is marked as invalid for time travel purposes, and the new micro partition is marked as active and added to the table's metadata.

Can users access or control the number of micro partitions created during data loading?

No, users cannot access specific micro partitions or control the number of micro partitions created. This is managed automatically by Snowflake based on the incoming data volume and table structure.

How does Snowflake obtain storage for table data?

Snowflake, being a SaaS service, uses the storage services of underlying cloud service providers like AWS, Azure, or GCP to provide storage for table data within its own account.

Is it possible to log into the cloud service provider account used by Snowflake to access table data?

No, direct access to the cloud service provider account used by Snowflake is not provided. Access to table data is only possible through Snowflake's platform.

What are the drawbacks of using micro partitions in Snowflake?

One drawback is the loss of row-level locking capability, making Snowflake less suitable for OLTP systems that require it. Instead, Snowflake is primarily used for OLAP systems where high read capabilities are needed.

How can the drawbacks of micro partitions be overcome in an OLAP system?

In an OLAP system, data is typically written in batches, and Snowflake recommends storing data in batches via SQL statements while avoiding update operations to overcome the limitations of micro partitions.

What is the size range of a micro partition in Snowflake before compression?

A micro partition in Snowflake can have a size between 50 MB to 500 MB before compression. The exact size after compression is variable and depends on the data.

What metadata is stored about micro partitions in the metadata layer of Snowflake?

The metadata stored about micro partitions includes statistics such as distinct values for string columns and range of values for numeric columns, which help in identifying which micro partitions should be considered during SQL queries.