Databases and Schemas

Overview

Databases and schemas logically organise data within a Snowflake account. A database is a logical grouping of schemas; a schema is a logical grouping of database objects — tables, views, streams, pipes, stored procedures.

Every Snowflake object lives inside a logical container, and the outermost container is always the account. Each database belongs to exactly one account.

The containment hierarchy

text

ACCOUNT                      ── the top-level container
 ├─ users                     ── who logs in
 ├─ roles                     ── privileges are granted to these
 ├─ warehouses                ── the compute that runs a statement
 ├─ resource monitors         ── e.g. credits consumed
 └─ databases
     └─ schemas
         ├─ tables
         ├─ views
         ├─ stored procedures
         ├─ streams
         ├─ pipes
         ├─ file formats
         └─ stages

read it as: nothing exists loose. A table needs a schema,
a schema needs a database, a database needs the account.

Warehouses and resource monitors sit beside the databases rather than inside them — which is the separation of compute from storage showing up in the object model. A warehouse is a resource with CPU and memory for executing SQL; a resource monitor is an object you create to watch consumption, such as how many credits have been spent.

Figure 5 — Logical Data OrganizationOpen full size ↗

Access is granted to roles, never to users

Every object is individually securable. A user performs an operation on an object through privileges, and those privileges are granted to roles — the roles are then granted to users.

text

privilege ──granted to──▶ role ──granted to──▶ user

So an organisation with many users creates roles for the levels of access it wants to hand out, and assigns those. Nothing is granted to a person directly.

What a new account already contains

Two databases arrive with the account, and you can neither modify nor drop them:

Database	What it is
`SNOWFLAKE`	Snowflake's own database, supplied with every account
`SNOWFLAKE_SAMPLE_DATA`	Sample datasets, also supplied and also read-only

And every database you create arrives with two schemas of its own:

Schema	What it is
`INFORMATION_SCHEMA`	The metadata for every object in that database — tables, views, streams, tasks, and their columns. This is what a catalogue scan reads.
`PUBLIC`	The default schema. An object created with no schema named lands here.

Creating them in the console

In the Snowflake console, under Data, the databases are listed — including the two defaults. Then:

Click + Database, give it a name, optionally a comment, and create it.
Open the new database and click + Schema to add one beside the two defaults.

The list does not always refresh on its own — reload the page and the new object appears.

Creating them in SQL

The other route is DDL in a worksheet: Projects → Worksheets → a new worksheet.

Before any DDL runs you have to set the context: which role the object is created under, and which warehouse the statement runs on. Either pick them from the worksheet's selectors, or state them:

sql

USE ROLE ACCOUNTADMIN;
USE WAREHOUSE COMPUTE_WH;

CREATE OR REPLACE DATABASE MY_DB;

-- The database context is now MY_DB, so the schema needs its
-- bare name. Qualifying it as MY_DB.MY_SCHEMA also works, but
-- is redundant once the context is set.
CREATE OR REPLACE SCHEMA MY_SCHEMA;

Good to know: the role matters as much as the name. An object is created by the active role, so running the same DDL under a different role produces an object a different set of people can see.

Everything else — tables, views, pipes, streams, tasks — is then created inside that schema.

Why this matters to a migration

The hierarchy is not just organisational — three parts of it are load-bearing for this platform.

The fact	Where it bites	What happens
`PUBLIC` is the default schema	The assessment resolves a target schema when the connection does not name one.	It resolves to PUBLIC for Snowflake — uppercase — against lowercase public for PostgreSQL.
Unquoted names fold to upper case	The load writes into a database, schema and table by name.	A run once loaded into AGENT_OFFICE while the DDL had created "agent_office" — a different object, so every load failed.
`INFORMATION_SCHEMA` holds the metadata	Discovery enumerates objects from the catalogue rather than by guessing.	It is also why PostgreSQL's own information_schema.routines lumping functions with procedures has to be split apart by the scan.

The middle row is the one that cost a real run. Snowflake's convention is upper case; the mapping's spelling is the requirement, and the two are not the same thing.

Two slips in the source

Recorded because a reader following the walkthrough will hit both.

The narration says you "cannot have multiple databases in the same account". That is a garbled reading of the slide, which says each database belongs to a single account — the constraint runs the other way. An account holds as many databases as you like; a database cannot be shared between accounts.
The narration says to "log into your AWS console". It means the Snowflake console — Snowsight. Snowflake runs on AWS, GCP or Azure, but you never reach it through the cloud provider's console.

Where this page comes from

The hierarchy, the defaults and the two creation routes are Snowflake's own material, from an introductory walkthrough of them — with the two misstatements above corrected rather than repeated.

The migration section is this platform's own: the schema resolution comes from preparation_node.py and the case incident from app/ai/etl/dialect_rules.py.