Databases, Schemas, and the Warehouse That Isn't Storage

Snowflake's object hierarchy is ordinary until you hit the word 'warehouse' — which means compute, not storage. Here's the model, and the trap, cleared up.

Most of Snowflake’s object model will feel like coming home. There’s an account at the top, databases inside it, schemas inside those, and tables and views inside those. If you’ve used Postgres or any grown-up SQL database, you’ve seen this shape before and you can navigate it on instinct. And then you meet the word “warehouse,” reach for the instinct that says warehouse means where the data is kept, and walk straight into the one thing about Snowflake that trips up everyone.

The hierarchy

Storage in Snowflake nests four levels deep:

account
└── database
    └── schema
        └── table / view

An account is your whole Snowflake environment. Inside it you create databases, inside those schemas, and inside those the tables and views that hold and shape data. The full address of a table is all three levels joined by dots:

SELECT * FROM analytics.sales.orders;

One thing this little tree quietly omits: databases aren’t the only things that hang off the account. Warehouses live at the account level too, as siblings of databases rather than children of them — and so do roles, users, and resource monitors. That’s your first hint of the twist coming later in this post: the warehouse isn’t tucked inside a database because it was never storage in the first place. But hold that thought.

That’s database.schema.table — a fully-qualified name, and it works from anywhere regardless of what you’ve selected. Writing it out every time gets old fast, which is what USE is for. Setting a database and schema as your context lets you drop the prefixes:

USE DATABASE analytics;
USE SCHEMA sales;

SELECT * FROM orders;

Same query, resolved through context. orders becomes analytics.sales.orders because that’s where you’re standing. Fully-qualified names are how you reach across to something outside your current context; USE is how you avoid repeating yourself within it. You’ll use both constantly.

You’ve already been reading data through exactly this address. SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS is a database (SNOWFLAKE_SAMPLE_DATA), a schema (TPCH_SF1), and a table (ORDERS) — the same three-dot shape as anything you’ll build yourself. The sample data isn’t special; it’s an ordinary database that Snowflake put in your account for you, and it obeys the same rules as the ones you create.

What a schema actually holds

A schema is usually described as “the thing tables live in,” and that’s true but sells it short. A schema is the real namespace of Snowflake — the level where most objects are named and owned — and it holds a good deal more than tables and views. It’s worth knowing the full inventory now, because these names will keep surfacing and you’ll want to already know which drawer they’re in:

  • Tables — the rows and columns, in a few flavours we’ll get to in a moment.
  • Views — saved queries that look like tables. A plain view recomputes on every read; a materialized view stores its result and refreshes as the base table changes (a paid, Enterprise-and-up convenience for expensive, slow-changing queries).
  • Stages — named pointers to file storage, internal or in an S3/GCS/Azure bucket, that loading and unloading read through. You’ll meet these when you bring in your own data.
  • File formats — reusable descriptions of how a file is laid out (CSV delimiters, JSON, Parquet, compression) so you don’t respecify it on every load.
  • Sequences — number generators for surrogate keys.
  • Streams — change-tracking objects that record what rows were inserted, updated, or deleted in a table since you last looked; the backbone of incremental pipelines.
  • Tasks — scheduled or triggered units of SQL, often chained together and often driven by a stream.
  • Stored procedures and user-defined functions — your own logic, in SQL, JavaScript, Python, Java, or Scala, callable by name.

Streams, tasks, and the programmability objects each get their own chapter later; the point here is only that a schema is a container for all of these, not just tables. When you drop a schema, everything in this list goes with it.

Table types: three flavours, and why it costs you to not know

Even the word “table” hides a choice. When you write CREATE TABLE, you get a permanent table — the default, and the one you want for anything real. Permanent tables get the full safety net: Time Travel (query or restore an earlier version, up to 90 days on Enterprise and above) followed by Fail-safe (a further seven-day, Snowflake-managed recovery window you can’t query but Snowflake support can restore from). That safety costs storage, because Snowflake keeps the historical bytes around to make it possible.

A transient table (CREATE TRANSIENT TABLE) trades that net for a lower bill. It keeps at most one day of Time Travel and has no Fail-safe at all — which means you stop paying for those seven days of retained history. Use it for data you can rebuild: staging tables, scratch space, the reload-from-source layer of a pipeline. If it vanished, you’d shrug and re-run the load.

A temporary table (CREATE TEMPORARY TABLE) is narrower still: it exists only for your current session and disappears the moment you disconnect, invisible to everyone else the whole time. It’s for the intermediate result you need for the next three queries and never again. It also has no Fail-safe.

Here’s the part that bites newcomers: transience is inherited. Create a transient database or a transient schema and every table inside it is transient by default, whether or not you said so — so an entire environment can quietly have no Fail-safe because someone typed CREATE TRANSIENT SCHEMA once. That’s usually what you want for a dev or scratch environment, but it’s a decision you should make on purpose. The full matrix of table types — permanent, transient, temporary, plus external, Iceberg, and dynamic tables — and how each interacts with Time Travel and Fail-safe is the subject of its own chapter; for now, just know the default is permanent, and that the word TRANSIENT in front of a DATABASE or SCHEMA changes the cost profile of everything you’ll ever put in it.

Creating storage, and who ends up owning it

You create storage with the CREATE verb, top-down:

CREATE DATABASE analytics;
CREATE SCHEMA analytics.sales;

That’s a database and a schema inside it — a place for tables to live, holding no compute of any kind. Two things happen at creation that you should be able to see coming.

First, ownership. The role you’re using when you run CREATE becomes the object’s owner, and the owner has every privilege on it plus the right to hand those privileges to others. This is why the role you had active matters: create a database as SYSADMIN and SYSADMIN owns it; create it as some personal role and you may find your teammates locked out of a thing you didn’t mean to make private. Roles are their own chapter, but the reflex to build now is notice which role is active before you create things.

Second, in a normal schema, the owner of each object controls grants to that object — so whoever creates a table can also decide who reads it. Sometimes that’s exactly the delegation you want; sometimes it’s a governance headache, because access decisions end up scattered across whoever happened to create what. That’s the problem managed-access schemas solve.

Managed-access schemas

Create a schema with one extra clause:

CREATE SCHEMA analytics.sales WITH MANAGED ACCESS;

In a managed-access schema, object owners lose the ability to grant access to their own objects. Only the schema owner (and roles holding the global MANAGE GRANTS privilege) can grant privileges on anything inside it. Ownership still means you can alter and drop your table — but who gets to read it is decided in one place, by the schema owner, not by each object’s creator. For a team that cares about governance, that single clause turns “access is wherever someone left it” into “access is decided here,” and it’s far easier to reason about a schema where one role holds the grant pen. You can see whether a schema is managed in the output of SHOW SCHEMAS — there’s an is_managed_access column.

Finding your way around

Once objects exist, three tools tell you what’s there. They overlap on purpose — different situations want different ones.

SHOW lists objects and is the quickest look around:

SHOW DATABASES;
SHOW SCHEMAS IN DATABASE analytics;
SHOW TABLES IN SCHEMA analytics.sales;
SHOW WAREHOUSES;

SHOW reads live metadata, needs no running warehouse, and returns one row per object with handy columns — created-on, owner, row count, bytes, the managed-access flag. It’s what you reach for when the question is “what have I got?”

DESCRIBE (or DESC) zooms into one object’s shape:

DESCRIBE TABLE analytics.sales.orders;

That gives you the column names, types, nullability, and defaults — the answer to “what does this table actually look like?” It works on more than tables: DESCRIBE VIEW, DESCRIBE WAREHOUSE, DESCRIBE SCHEMA, and so on.

INFORMATION_SCHEMA is the queryable, SQL-standard version of the same knowledge. Every database has its own, a read-only schema full of views and table functions describing that database’s contents:

SELECT table_name, row_count, bytes
FROM analytics.information_schema.tables
WHERE table_schema = 'SALES'
ORDER BY bytes DESC;

The sample data answers the same way. Point the same query at it and you get the catalogue of the TPC-H schema you’ve been reading from:

SELECT table_name, row_count
FROM snowflake_sample_data.information_schema.tables
WHERE table_schema = 'TPCH_SF1'
ORDER BY row_count DESC;

That’s how you’d discover that LINEITEM dwarfs ORDERS, which dwarfs CUSTOMER — useful reconnaissance before you write a join that has to grind through six million rows. Because it’s ordinary SQL, you can filter, join, aggregate, and script against it — which SHOW can’t do as cleanly. The rule of thumb: SHOW and DESCRIBE for eyeballing, INFORMATION_SCHEMA when you need to compute over the catalog (for account-wide metadata there’s also the shared SNOWFLAKE.ACCOUNT_USAGE schema, which we’ll meet in the admin chapter). Note the filter above says 'SALES', uppercase — that’s not an accident, and the next-but-one section explains why.

The database is also the unit of the big operations

One more reason the database level matters: several of Snowflake’s most distinctive features operate on whole databases (or objects within them), and knowing that shapes how you organize things. Zero-copy cloning — making an instant, storage-free copy of a database or schema — is a database-level trick we’ll use in the Time Travel and cloning chapter. Replication copies a database to another account or region for resilience. Shares expose selected objects to other Snowflake accounts without moving data, the basis of the data-sharing and Marketplace chapter. You don’t need any of this yet. Just file away that the database isn’t only a naming bucket — it’s the natural boundary for cloning, replicating, and sharing, so a sensible database layout pays off later.

The trap: a warehouse is compute

Here is the sentence to tattoo somewhere: in Snowflake, a warehouse is not where data lives. It’s not in the hierarchy above. It’s not a container, not a folder, not storage. A Snowflake warehouse is a chunk of compute — a cluster of CPUs and memory that runs your queries. The data lives in databases, schemas, and tables. The warehouse is the engine that reads them.

The word is doing damage because everywhere else in the industry, “data warehouse” means the place the data is warehoused. Snowflake overloads the term: the product is a data warehouse in that usual sense, but a warehouse object inside it means compute and nothing else. Once you separate the two meanings, the confusion evaporates. Storage is the noun — the tables. The warehouse is the verb — the running.

So every query has two independent targets. It reads from a storage target (a table, addressed by database.schema.table) and it runs on a compute target (a warehouse, selected by context). Neither implies the other. The same table can be read by ten different warehouses; the same warehouse can read a thousand different tables. They’re chosen separately because they are separate — this is the storage-and-compute split from the first post, showing up as two different kinds of object you point a single query at.

Creating a warehouse, the full knobs

The warehouse we made earlier was deliberately bare. Here’s the same CREATE with the parameters worth understanding on day one:

CREATE WAREHOUSE dev_wh
  WAREHOUSE_SIZE       = 'XSMALL'
  MIN_CLUSTER_COUNT    = 1
  MAX_CLUSTER_COUNT    = 1
  SCALING_POLICY       = 'STANDARD'
  AUTO_SUSPEND         = 60
  AUTO_RESUME          = TRUE
  INITIALLY_SUSPENDED  = TRUE
  COMMENT              = 'Dev + learning warehouse';

Read it top to bottom:

  • WAREHOUSE_SIZE is the power of a single cluster, on a t-shirt scale that doubles at each step: XSMALL, SMALL, MEDIUM, LARGE, XLARGE, and on up through 2XLARGE to 6XLARGE. Each step up doubles the compute — and doubles the per-hour credit burn — but a query that fits in cache can finish more than twice as fast, so a bigger warehouse is often cheaper per query even though it costs more per second. XSMALL is the right default for learning and light work, and you can resize a warehouse later with a single ALTER without touching anything that reads through it.
  • MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT turn on multi-cluster warehouses. Size handles a query that’s big; clusters handle queries that are many. Set the max above one and Snowflake spins up extra same-sized clusters when concurrent queries start queuing, then spins them down when the rush passes — automatic horizontal scaling for lots of simultaneous users. Leaving both at 1 (as here) gives you a plain single-cluster warehouse. Multi-cluster is an Enterprise-edition-and-up feature; on Standard, these parameters simply aren’t available to raise.
  • SCALING_POLICY governs how eagerly those extra clusters start. STANDARD favours performance — add capacity fast to avoid queuing. ECONOMY favours cost — make the running clusters work harder before paying for another. It only matters once MAX_CLUSTER_COUNT is above one.
  • AUTO_SUSPEND is the idle timeout in seconds. After this long with nothing to run, the warehouse powers down and stops billing. Sixty seconds is a reasonable learning default; production values are a balance we’ll weigh later.
  • AUTO_RESUME brings it back the instant a query arrives, so a suspended warehouse is invisible in practice — the first query after a nap just waits a second or two for the lights to come on.
  • INITIALLY_SUSPENDED = TRUE creates it already asleep, so it doesn’t start billing the moment the CREATE runs while you’re still typing. A small, good habit.
  • WAREHOUSE_TYPE we left at its default, STANDARD. Setting it to 'SNOWPARK-OPTIMIZED' gives you nodes with much more memory per node, for memory-hungry Snowpark / machine-learning workloads. You won’t need it for SQL analytics, but it’s there.
  • COMMENT is free-text documentation that shows up in SHOW WAREHOUSES. On a real account with a dozen warehouses, a one-line note on why this one exists is worth the eight seconds it takes to write.

Now pair compute with storage — pick your engine, then read your data:

USE WAREHOUSE dev_wh;

SELECT count(*) FROM analytics.sales.orders;

The USE WAREHOUSE line chooses the engine; the SELECT names the data. Change one and the other is unaffected. Point the query at a different table and dev_wh still runs it. Swap in a bigger warehouse and the table doesn’t notice. Sizing, credits, and the arithmetic of when a bigger warehouse is the cheaper choice get a whole chapter of their own — Warehouse Sizes, Auto-Suspend, and the Credit Meter; here we’re just naming the knobs so the syntax isn’t a surprise.

The bug that gets everyone: identifier case

Now that you’re creating objects, here’s the single most common beginner DDL mistake, and it’s about capital letters.

When you write an identifier without quotes, Snowflake folds it to UPPERCASE before storing it. These three statements all create the same table, named ORDERS:

CREATE TABLE orders (...);
CREATE TABLE Orders (...);
CREATE TABLE ORDERS (...);

And because reads fold the same way, SELECT * FROM orders, FROM Orders, and FROM ORDERS all find it. This is why you can type lowercase all day and it just works, and it’s why the metadata views hold 'ORDERS' and 'SALES' in uppercase — the earlier INFORMATION_SCHEMA filter used WHERE table_schema = 'SALES' for exactly this reason. The stored name is uppercase even though you never typed it that way.

When you wrap an identifier in double quotes, all of that stops. A quoted identifier is stored exactly as written, case and all, and from then on you must quote it and match the case every time:

CREATE TABLE "Orders" (...);      -- stored as  Orders

SELECT * FROM Orders;             -- ERROR: resolves to ORDERS, which doesn't exist
SELECT * FROM "Orders";           -- works
SELECT * FROM "orders";           -- ERROR: that's a different name again

The trap springs when a tool — a BI connector, an ORM, a loader — quotes every identifier it emits, creating "Orders" and "customerKey" with the case preserved, and then you sit in Snowsight typing unquoted SQL that folds to uppercase and can’t find any of it. The fix is a rule you can adopt from your very first CREATE: don’t quote identifiers unless you have to, and stay consistent. Let everything fold to uppercase and you never think about case again. Reserve double quotes for the rare name that genuinely needs a space or a reserved word — and when you do quote one, know that you’ve signed up to quote it forever.

Why the split earns its keep

Keeping compute out of the hierarchy isn’t pedantry — it’s what makes the platform bend to your workload.

Because a warehouse is just compute you name and size, you size it to the job. A dashboard that scans a few million rows runs fine on an XSMALL; a monthly rebuild that grinds through billions gets a large one for the twenty minutes it runs, then goes away. You’re not sizing one server for the worst case and paying for it always.

Because it suspends when idle, an unused warehouse costs nothing. Compute is billed by the second while running and zero while suspended, so a warehouse you touch twice a day is nearly free the rest of the time.

And because storage is shared, you give each team its own warehouse over the same tables. Analysts on one, the nightly load on another, a data scientist on a third — all reading the identical analytics.sales.orders, none slowing the others down, because they share bytes but not CPUs. That’s the payoff of refusing to let “warehouse” mean “storage”: you can multiply the engines without ever copying the data.

Final thoughts

The object model is easy right up to the word that isn’t. Databases, schemas, tables — you already knew those, though a schema holds more than you might have guessed, and even “table” hides a permanent-versus-transient choice that quietly sets your bill. The single idea worth carrying forward is that a warehouse sits outside that tree entirely, as compute you attach to a query rather than a place a query lives. Get that boundary firm, keep your identifiers unquoted and uppercase, and you’ve internalized the thing Snowflake is actually built around; the rest of the platform is just details hanging off a split you now see clearly.

Next: Querying the Sample Data You Already Have

Comments