How Does Snowflake Storage Work? (Databases & Schemas)

Snowflake data warehouse uses a high-performance columnar database structure internally, though you can’t access the stored data directly. Instead, you use databases and schemas (together called “namespaces”) to manage data storage in Snowflake.

Snowflake’s storage of data for analytics is complicated “under the hood” because it uses columnar storage, as illustrated by this close-up image of a snowflake perched vertically on a block of ice like a column.

Cover photo by Amariei Mihai on Unsplash

Databases and schemas are used to organize data stored inside tables in Snowflake data warehouse, similar to how relational databases are commonly used by data scientists, analysts, and engineers.

Unlike how Snowflake data platform defines warehouses to mean virtual compute resources that process data, the Snowflake terms database and schema have more traditional definitions.

“A database is a logical grouping of schemas. Each database belongs to a single Snowflake account. A schema is a logical grouping of database objects (tables, views, etc.). Each schema belongs to a single database.” – Snowflake Docs

Together, a database and schema create what is called a “namespace” in Snowflake. Typically, the namespace is inferred automatically while using Snowflake, based on the current database and schema that are in use during your session, but you can specify the namespace manually.

One important feature of Snowflake’s storage system is the ability to create a “share,” a set of database objects (such as schemas that contain tables and secure views) that you would like to share with other Snowflake accounts. Shares are one way Snowflake protects data privacy.

You manipulate any of these organizational pieces of Snowflake’s data storage via Snowflake’s data definition language (DDL), which is based on SQL (Structured Query Language). For a database, schema, or share, the main commands are CREATE, ALTER, DROP, and SHOW.

How Does Snowflake Storage Work?

Under the hood, groups of rows in Snowflake’s storage tables are stored as individual micro-partitions, organized internally as groups of columns. Since Snowflake is meant for big data, a Snowflake table in storage may contain thousands or millions of micro-partitions.

“When data is loaded into Snowflake, Snowflake reorganizes that data into its internal optimized, compressed, columnar format. Snowflake stores this optimized data in cloud storage.” – Snowflake Docs

Snowflake’s unique internal architecture avoids unnecessary scanning of micro-partitions during database queries, which significantly accelerates the performance of queries referring to a given set of columnar data. This process happens automatically without additional configuration.

Unlike other database solutions, data stored inside Snowflake is not accessible directly; it’s only accessible via SQL commands. And, while there’s no limit to the number of databases, schema, or tables you can create, there is a storage cost associated with keeping data inside Snowflake.

If you enjoyed this article on Snowflake analytics, please consider signing up for our free email newsletter, following our Twitter account @propeldatacloud, or joining our customer waiting list to stay in touch with the latest news and education from Propel Data.

Related posts

Snowflake API: Comprehensive Guide to 3 Methods With Examples

This is some text inside of a div block.

Heading 1

Heading 2

Heading 3

Heading 4

Heading 5
Heading 6

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.

Block quote

Ordered list

  1. Item 1
  2. Item 2
  3. Item 3

Unordered list

  • Item A
  • Item B
  • Item C

Text link

Bold text

Emphasis

Superscript

Subscript

How we reduced our Snowflake spend by 20x

This is some text inside of a div block.

Heading 1

Heading 2

Heading 3

Heading 4

Heading 5
Heading 6

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.

Block quote

Ordered list

  1. Item 1
  2. Item 2
  3. Item 3

Unordered list

  • Item A
  • Item B
  • Item C

Text link

Bold text

Emphasis

Superscript

Subscript

How to subtract days from a date in Snowflake

This is some text inside of a div block.

Heading 1

Heading 2

Heading 3

Heading 4

Heading 5
Heading 6

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.

Block quote

Ordered list

  1. Item 1
  2. Item 2
  3. Item 3

Unordered list

  • Item A
  • Item B
  • Item C

Text link

Bold text

Emphasis

Superscript

Subscript

Start shipping today

Deliver the analytics your customers have been asking for.