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

  1. Plan where a new table belongs
  2. Is it a new environment or domain? Yes: CREATE DATABASE. No: Use an existing database.
  3. Is it a new layer or subject area? Yes: CREATE SCHEMA (managed access if needed). No: Use an existing schema.
  4. Create the object with its fully qualified name
  5. 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

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic