Databases and schemas in Snowflake - Commands & Configuration

Introduction

When you store data in Snowflake, you organize it into databases and schemas. This helps keep your data tidy and easy to find, like folders and subfolders on your computer.

When you want to separate data for different projects or teams within the same Snowflake account.

When you need to control access to specific sets of data by grouping tables inside schemas.

When you want to organize your data logically to make it easier to manage and query.

When you are setting up a new environment for development, testing, or production and want clear separation.

When you want to avoid mixing data from different sources or applications.

Commands

This command creates a new database named 'example_db' to hold your data collections.

Terminal

CREATE DATABASE example_db;

Expected Output

SQL executed successfully.

This creates a schema called 'sales_schema' inside the 'example_db' database to organize related tables.

Terminal

CREATE SCHEMA example_db.sales_schema;

Expected Output

SQL executed successfully.

This command lists databases matching 'example_db' to confirm it was created.

Terminal

SHOW DATABASES LIKE 'example_db';

Expected Output

name       | created_on          | owner  

example_db | 2024-06-01 12:00:00 | ACCOUNTADMIN

This lists schemas inside 'example_db' matching 'sales_schema' to verify its creation.

Terminal

SHOW SCHEMAS IN DATABASE example_db LIKE 'sales_schema';

Expected Output

name         | database_name | created_on          | owner  

sales_schema | EXAMPLE_DB   | 2024-06-01 12:01:00 | ACCOUNTADMIN

Key Concept

If you remember nothing else from this pattern, remember: databases hold schemas, and schemas hold tables to keep your data organized.

Common Mistakes

Trying to create a schema without specifying the database name.

Snowflake needs to know which database the schema belongs to, or it will create it in the current database which might not be intended.

Always specify the database name before the schema, like CREATE SCHEMA example_db.sales_schema;

Assuming schemas are the same as databases.

Schemas are containers inside databases, not separate databases themselves, so mixing them up can cause confusion in data organization.

Understand that databases are top-level containers and schemas are subdivisions inside them.

Summary

Create a database to hold your data collections.

Create schemas inside the database to organize tables logically.

Use SHOW commands to verify your databases and schemas exist.