Databases, Schemas & Sequences
Snowflake · Databases, Schemas & Objects
Theory
Snowflake organises objects in a hierarchy: an account contains databases, a database contains schemas, and a schema contains objects such as tables, views, stages, file formats, sequences, tasks and functions. Every object has a fully qualified name, database.schema.object, for example company_dw.curated.orders. USE DATABASE and USE SCHEMA set the session's defaults so you can write just orders.
A sequence is a schema object that hands out unique numbers, read with order_seq.NEXTVAL, often used as a column default for surrogate keys. Values are unique but can have gaps, so do not rely on them being consecutive. Schemas can also be created as MANAGED ACCESS, where only the schema owner (or a role with MANAGE GRANTS) can grant privileges on objects inside.
Why we use it
A clear namespace separates layers (raw, staging, curated) and environments, makes grants easier to manage at database or schema level, and avoids name clashes between teams.
When to use it
Use databases for big boundaries such as environments or domains, and schemas for layers or subject areas inside them. Use fully qualified names in scripts and pipelines. Use sequences or AUTOINCREMENT/IDENTITY when you need surrogate keys.
Creating Databases & Schemas
CREATE DATABASE and CREATE SCHEMA build the two top levels of the namespace; every table, view, or stage you create afterward lives inside exactly one schema, referenced as database.schema.object.
Fully-Qualified Names
Because names can collide across schemas, queries can always use the fully-qualified form (db.schema.table) to be explicit, or rely on the session's current database/schema context set with USE DATABASE and USE SCHEMA.
Sequences for Unique IDs
CREATE SEQUENCE defines an auto-incrementing counter you call with seq.NEXTVAL — useful for generating surrogate keys without the row-locking behavior a traditional auto-increment column would need.
How it works
- Plan where a new table belongs
- Is it a new environment or domain? Yes: CREATE DATABASE. No: Use an existing database.
- Is it a new layer or subject area? Yes: CREATE SCHEMA (managed access if needed). No: Use an existing schema.
- Create the object with its fully qualified name
- Grant access at the schema or database level
Example
CREATE DATABASE IF NOT EXISTS company_dw;
CREATE SCHEMA company_dw.raw;
CREATE SCHEMA company_dw.staging;
CREATE SCHEMA company_dw.curated;
CREATE TRANSIENT SCHEMA company_dw.temp_work;
CREATE SEQUENCE company_dw.curated.order_seq START = 10000 INCREMENT = 1;
SELECT company_dw.curated.order_seq.NEXTVAL;
SHOW SCHEMAS IN DATABASE company_dw;Key points
- Every object lives at database.schema.object
- USE DATABASE and USE SCHEMA set session defaults
- Sequences give unique numbers but can have gaps
- Managed access schemas centralise grant control with the schema owner
- Transient databases and schemas make their tables transient