Why object hierarchy organizes data in Snowflake - Why It Works

Introduction

When you store data in Snowflake, it is organized in layers called an object hierarchy. This helps keep data neat and easy to find, like folders on your computer. It solves the problem of managing many pieces of data by grouping them logically.

When you want to keep your data organized so you can find it quickly.

When you need to control who can see or change certain data.

When you want to separate data by project, team, or purpose.

When you want to manage storage and performance by grouping related data.

When you want to build reports or queries that use specific sets of data.

Commands

This command lists all databases in your Snowflake account, showing the top level of the data hierarchy.

Terminal

SHOW DATABASES;

Expected Output

name       | created_on          | owner

MY_DATABASE | 2024-06-01 10:00:00 | ACCOUNTADMIN

This command sets the current database to MY_DATABASE so you can work inside it.

Terminal

USE DATABASE MY_DATABASE;

Expected Output

Database changed.

This command lists all schemas inside the current database, showing the next level in the hierarchy.

Terminal

SHOW SCHEMAS;

Expected Output

name        | created_on          | owner

PUBLIC      | 2024-06-01 10:05:00 | ACCOUNTADMIN

This command sets the current schema to PUBLIC so you can work with tables inside it.

Terminal

USE SCHEMA PUBLIC;

Expected Output

Schema changed.

This command lists all tables inside the current schema, showing the lowest level of the hierarchy where data is stored.

Terminal

SHOW TABLES;

Expected Output

name         | created_on          | rows

CUSTOMERS    | 2024-06-01 10:10:00 | 1000

Key Concept

If you remember nothing else from this pattern, remember: Snowflake organizes data in a clear hierarchy of databases, schemas, and tables to keep data easy to manage and find.

Common Mistakes

Trying to access a table without setting the correct database and schema first.

Snowflake commands depend on the current database and schema context, so the table won't be found if these are not set.

Always run USE DATABASE and USE SCHEMA commands before working with tables.

Confusing schemas with databases and trying to create a schema at the account level.

Schemas must be created inside a database, not directly at the account level.

Create schemas only after selecting or creating a database.

Summary

Snowflake organizes data in a hierarchy: databases contain schemas, and schemas contain tables.

Use SHOW and USE commands to navigate this hierarchy and work with data.

Setting the correct database and schema context is essential before accessing tables.