Databases, Schemas, and Warehouses

Analogy categoryAnalogies

Snowflake organizes data using a strict hierarchy: an account contains one or more databases, each database contains one or more schemas, and each schema contains objects like tables, views, and stored procedures — while virtual warehouses sit entirely outside this hierarchy as the independent compute engines that query it. Understanding this separation between the object hierarchy (what holds your data) and warehouses (what processes it) is fundamental to using Snowflake correctly.

🏏

Cricket analogy: Like a cricket board's filing structure — country association, state association, district club, individual team roster — existing completely separately from the stadium and ground staff that actually host the matches; the org chart and the venue are different things.

Object Hierarchy: Account > Database > Schema > Table

A fully qualified table reference in Snowflake looks like database.schema.table, for example SALES_DB.PUBLIC.ORDERS, and every database automatically comes with a default PUBLIC schema plus a read-only INFORMATION_SCHEMA for metadata queries. Organizations typically create separate databases per environment or domain (e.g., RAW_DB, ANALYTICS_DB) and separate schemas per team or subject area within them (e.g., MARKETING, FINANCE), which keeps permissions and naming clean as the account grows.

🏏

Cricket analogy: Like referring to a player unambiguously as 'India.MensTeam.KLRahul' rather than just 'Rahul,' since multiple teams might have a player of that name — Snowflake's database.schema.table naming avoids the same ambiguity.

sql

CREATE DATABASE IF NOT EXISTS ANALYTICS_DB;
CREATE SCHEMA IF NOT EXISTS ANALYTICS_DB.FINANCE;

CREATE TABLE ANALYTICS_DB.FINANCE.MONTHLY_REVENUE (
    region STRING,
    month DATE,
    revenue NUMBER(12,2)
);

SELECT region, SUM(revenue) AS total_revenue
FROM ANALYTICS_DB.FINANCE.MONTHLY_REVENUE
GROUP BY region
ORDER BY total_revenue DESC;

Virtual Warehouses as Compute

A warehouse is not part of the database/schema hierarchy at all — it's a named compute resource you create with CREATE WAREHOUSE, and any user with the right role can point any warehouse at any database they have access to. This means the same ANALYTICS_DB.FINANCE schema might be queried by a BI_WH warehouse for dashboards and an ETL_WH warehouse for nightly loads, with completely independent billing and performance for each.

🏏

Cricket analogy: Like two completely different broadcast crews — one for the Hindi commentary feed, one for the English feed — both filming the exact same match on the same ground independently, without interfering with each other's cameras.

Dropping a schema or database is destructive and, outside of Time Travel's retention window (1 day on Standard edition by default), unrecoverable — always double-check the fully qualified name before running DROP SCHEMA or DROP DATABASE in a production account.

Sizing and Scaling Warehouses

Warehouse size (X-Small through 6X-Large) controls how much compute is available per query, while multi-cluster warehouses (an Enterprise-edition feature) let a single warehouse automatically add more clusters of the same size to handle a spike in concurrent users, then scale back down — solving concurrency scaling separately from query-speed scaling, which single-cluster resizing alone cannot do.

🏏

Cricket analogy: Like adding more nets (multi-cluster) so twenty batters can practice at once during a big camp, versus upgrading a single net's bowling machine to fire faster deliveries (single-warehouse resize) — one solves crowding, the other solves intensity.

Snowflake's object hierarchy is account > database > schema > table/view/procedure.
Fully qualified names follow the pattern database.schema.table, e.g., SALES_DB.PUBLIC.ORDERS.
Every database includes a default PUBLIC schema and a read-only INFORMATION_SCHEMA.
Virtual warehouses exist outside the object hierarchy as independent, named compute resources.
Multiple warehouses can query the same database/schema simultaneously with separate billing.
Multi-cluster warehouses (Enterprise+) scale concurrency; warehouse resizing scales per-query speed.
Dropping a schema or database is destructive and only recoverable within the Time Travel window.

Practice what you learned

1. What is Snowflake's object hierarchy, from top to bottom?

2. What does a fully qualified table name look like?

3. Where do virtual warehouses sit in relation to the database/schema hierarchy?

4. What Enterprise-edition feature helps with concurrency spikes rather than raw query speed?

5. What schema does every new database include automatically?

Was this page helpful?

Setting Up a Snowflake Account

Your First Snowflake Query

Related Study Notes in Programming

Browse all study notes

Programming

Apache Spark Study Notes

Programming · 30 topics

Programming

Apache Flink Study Notes

Programming · 30 topics

Programming

Hadoop Study Notes

Programming · 30 topics

Programming

Apache Airflow Study Notes

Programming · 30 topics

Programming

dbt (Data Build Tool) Study Notes

Programming · 30 topics

Programming

PowerShell Study Notes

Programming · 30 topics

Topics covered

#Programming#SnowflakeStudyNotes#DatabasesSchemasAndWarehouses#Databases#Schemas#Warehouses#Object#SQL#StudyNotes#SkillVeris

Databases, Schemas, and Warehouses