Skip to main content

2.2 Databases & Schemas

Databases and schemas are the foundational containers in Snowflake. Everything you build will live inside of them.

The Object Hierarchy​

Snowflake organises objects in a strict three-level hierarchy:

ACCOUNT
└── DATABASE (e.g. PROD_ANALYTICS_DB)
└── SCHEMA (e.g. SALES_MART)
├── Tables
├── Views
├── Stages, File Formats, Sequences
└── Streams, Tasks, Pipes, Functions, Procedures
  • A database is the top-level container for a project, domain, or environment.
  • A schema is a logical grouping inside a database.
  • Tables, views, and every other object always belong to exactly one schema.

Because of this, every object has a fully qualified name of the form DATABASE.SCHEMA.OBJECT.

Creating and Managing Databases​

Creating a database in Snowflake is practically instantaneous, regardless of the amount of data that will eventually live in it, because metadata operations are separated from actual storage.

-- Creating a standard database
CREATE DATABASE PROD_ANALYTICS_DB;

-- Creating a schema within that database
CREATE SCHEMA PROD_ANALYTICS_DB.SALES_MART;

Standard vs Transient Databases​

By default, all databases are Standard (Permanent). This means they are protected by Snowflake's powerful data protection features: Time Travel (up to 90 days) and Fail-safe (7 days of disaster recovery storage).

However, Time Travel and Fail-safe consume storage credits. If you have a database that only holds temporary development data or easily recreatable staging data, you should create it as a Transient Database.

-- Creating a transient database
CREATE TRANSIENT DATABASE DEV_SANDBOX_DB;
  • Transient Databases only have a maximum of 1 day of Time Travel and zero Fail-safe. This significantly reduces storage costs for data that doesn't need strict disaster recovery!

Schemas​

What is a Schema?​

A schema is a named container inside a database that groups related objects together. Think of the database as a building and schemas as the floors: each floor holds its own rooms (tables, views, stages…), and you can lock or open floors independently.

Schemas are used to:

  • Organise objects by purpose (raw data, cleaned data, reporting).
  • Separate teams or subject areas (SALES, FINANCE, HR).
  • Control access — privileges can be granted at the schema level.
  • Avoid name clashes — RAW.ORDERS and MART.ORDERS can co-exist.

Creating Schemas​

-- Option 1: fully qualified (works from anywhere)
CREATE SCHEMA PROD_ANALYTICS_DB.SALES_MART;

-- Option 2: set the database first, then create
USE DATABASE PROD_ANALYTICS_DB;
CREATE SCHEMA SALES_MART;

-- Safe re-runs in scripts
CREATE SCHEMA IF NOT EXISTS PROD_ANALYTICS_DB.SALES_MART;

-- Add a description for documentation
CREATE SCHEMA PROD_ANALYTICS_DB.FINANCE
COMMENT = 'Finance reporting objects';
caution

CREATE OR REPLACE SCHEMA drops the existing schema and every object inside it before recreating it. Prefer IF NOT EXISTS in deployment scripts unless you really intend to rebuild.

Built-in Schemas: PUBLIC and INFORMATION_SCHEMA​

Every new database automatically contains two schemas:

SchemaPurpose
PUBLICDefault, empty schema. Objects land here if you don't specify a schema.
INFORMATION_SCHEMARead-only, system-generated views describing the objects in the database.
CREATE DATABASE DEMO_DB;
SHOW SCHEMAS IN DATABASE DEMO_DB;
-- Returns: INFORMATION_SCHEMA, PUBLIC

-- List all tables in the database using INFORMATION_SCHEMA
SELECT table_schema, table_name, row_count
FROM DEMO_DB.INFORMATION_SCHEMA.TABLES
WHERE table_schema <> 'INFORMATION_SCHEMA';
tip

Avoid building real objects in PUBLIC. Create purpose-named schemas instead so ownership and access are clear.

Session Context and Fully Qualified Names​

Snowflake resolves object names using your current database and schema. You can either set the context or always use fully qualified names.

-- Set context for the session
USE DATABASE PROD_ANALYTICS_DB;
USE SCHEMA SALES_MART; -- or: USE SCHEMA PROD_ANALYTICS_DB.SALES_MART;

-- Check where you are
SELECT CURRENT_DATABASE(), CURRENT_SCHEMA();

-- These three statements refer to the same table
SELECT * FROM ORDERS; -- uses current context
SELECT * FROM SALES_MART.ORDERS; -- schema.table
SELECT * FROM PROD_ANALYTICS_DB.SALES_MART.ORDERS; -- fully qualified
tip

Use fully qualified names in views, tasks, stored procedures and pipelines. They don't depend on whatever context the session happens to have, so they never break when run by a different user or tool.

Transient Schemas​

Like databases, schemas can be transient. Tables created in a transient schema are transient by default (max 1 day Time Travel, no Fail-safe), which lowers storage cost for re-creatable data.

CREATE TRANSIENT SCHEMA PROD_ANALYTICS_DB.STAGING;

A common pattern is a permanent database with a transient STAGING schema for intermediate load data.

Managed Access Schemas​

In a regular schema, the owner of each object can grant access to it. In a managed access schema, only the schema owner (or a role with MANAGE GRANTS) can grant privileges on objects inside it. This centralises security decisions.

CREATE SCHEMA PROD_ANALYTICS_DB.FINANCE WITH MANAGED ACCESS;

-- Convert an existing schema
ALTER SCHEMA PROD_ANALYTICS_DB.SALES_MART ENABLE MANAGED ACCESS;

Use managed access for schemas holding sensitive data (finance, HR, PII).

Common Schema Design: Layered (Medallion) Pattern​

Data engineering projects usually split a database into schemas by processing stage:

CREATE DATABASE IF NOT EXISTS RETAIL_DB;

CREATE SCHEMA IF NOT EXISTS RETAIL_DB.RAW COMMENT = 'Landing zone: data as received';
CREATE TRANSIENT SCHEMA IF NOT EXISTS RETAIL_DB.STAGING COMMENT = 'Cleaned, typed, de-duplicated';
CREATE SCHEMA IF NOT EXISTS RETAIL_DB.MART COMMENT = 'Business-ready facts & dimensions';

-- Same table name, different stages
CREATE TABLE RETAIL_DB.RAW.ORDERS (raw_payload VARIANT, loaded_at TIMESTAMP_NTZ);
CREATE TABLE RETAIL_DB.STAGING.ORDERS (order_id INT, customer_id INT, amount NUMBER(10,2), order_date DATE);
CREATE TABLE RETAIL_DB.MART.FCT_ORDERS(order_id INT, customer_id INT, amount NUMBER(10,2), order_date DATE);
SchemaTypical contentsWho reads it
RAWFiles loaded as-is (often VARIANT)Data engineers
STAGINGCleaned, typed, joined dataData engineers, dbt models
MARTFacts, dimensions, aggregatesAnalysts, BI tools

Separating by schema lets you grant analysts SELECT on MART only:

GRANT USAGE ON DATABASE RETAIL_DB TO ROLE ANALYST;
GRANT USAGE ON SCHEMA RETAIL_DB.MART TO ROLE ANALYST;
GRANT SELECT ON ALL TABLES IN SCHEMA RETAIL_DB.MART TO ROLE ANALYST;
GRANT SELECT ON FUTURE TABLES IN SCHEMA RETAIL_DB.MART TO ROLE ANALYST;
note

A role needs USAGE on both the database and the schema before it can see any object inside them.

Managing Schemas​

-- List schemas
SHOW SCHEMAS IN DATABASE RETAIL_DB;

-- Inspect objects in a schema
SHOW TABLES IN SCHEMA RETAIL_DB.MART;

-- Rename or move a schema to another database
ALTER SCHEMA RETAIL_DB.MART RENAME TO RETAIL_DB.REPORTING;

-- Change Time Travel retention for everything in the schema
ALTER SCHEMA RETAIL_DB.RAW SET DATA_RETENTION_TIME_IN_DAYS = 7;

-- Drop and recover (within the Time Travel window)
DROP SCHEMA RETAIL_DB.STAGING;
UNDROP SCHEMA RETAIL_DB.STAGING;

Database vs Schema — Quick Comparison​

DatabaseSchema
LevelTop-level containerInside a database
HoldsSchemasTables, views, stages, tasks…
Typical splitEnvironment or domain (PROD_DB, DEV_DB)Layer or subject (RAW, MART, FINANCE)
Transient optionCREATE TRANSIENT DATABASECREATE TRANSIENT SCHEMA
Managed access—WITH MANAGED ACCESS
CloningCREATE DATABASE … CLONECREATE SCHEMA … CLONE