Snowflake Databases Schemas and Tables
Every piece of data in Snowflake lives inside a three-level hierarchy: a database contains schemas, and schemas contain tables, views, stages, and other objects. Understanding this hierarchy and knowing how to create, organise, and manage these objects is fundamental to everything else you do in Snowflake. This topic walks through each level with hands-on SQL examples and real-world analogies.
The Three-Level Hierarchy: A Filing Cabinet Analogy
Think of a filing cabinet. The cabinet itself is your database — a top-level container that holds everything related to a specific application or department. Each drawer in the cabinet is a schema — a logical grouping of related objects. Each folder inside a drawer is a table — the actual place where data rows live.
SNOWFLAKE OBJECT HIERARCHY ============================ ACCOUNT (your Snowflake account) │ ├── DATABASE: RETAIL_DB │ ├── SCHEMA: RAW_DATA │ │ ├── TABLE: ORDERS_RAW │ │ ├── TABLE: CUSTOMERS_RAW │ │ └── STAGE: S3_LANDING_ZONE │ │ │ ├── SCHEMA: ANALYTICS │ │ ├── TABLE: FACT_SALES │ │ ├── TABLE: DIM_CUSTOMER │ │ └── VIEW: VW_MONTHLY_REVENUE │ │ │ └── SCHEMA: REPORTS │ └── VIEW: VW_EXEC_DASHBOARD │ ├── DATABASE: HR_DB │ └── SCHEMA: PUBLIC │ ├── TABLE: EMPLOYEES │ └── TABLE: DEPARTMENTS │ └── DATABASE: SHARED_ANALYTICS (read-only, shared by partner)
Creating a Database
A database is the top-level container. Create one with the CREATE DATABASE command. You need the SYSADMIN or ACCOUNTADMIN role to create databases.
-- Create a database for a retail analytics project CREATE DATABASE RETAIL_DB; -- Create a database with a comment for documentation CREATE DATABASE HR_DB COMMENT = 'Human Resources data warehouse — owned by HR team'; -- Create a database that automatically drops after 1 day (useful for testing) CREATE TRANSIENT DATABASE TEMP_SANDBOX DATA_RETENTION_TIME_IN_DAYS = 0;
When you create a database, Snowflake automatically creates two schemas inside it: PUBLIC and INFORMATION_SCHEMA. PUBLIC is where you start creating tables if you do not create custom schemas. INFORMATION_SCHEMA is a read-only system schema that contains metadata views about everything in the database — tables, columns, users, grants, and more.
Database Types
DATABASE TYPE DESCRIPTION USE WHEN ------------- ----------- --------- Standard Full Time Travel + Fail-Safe Production data Transient Time Travel only, no Fail-Safe Staging/temp data (cheaper) Standard storage cost = higher (Fail-Safe copies retained for 7 days) Transient storage cost = lower (no Fail-Safe retention)
Creating and Using Schemas
Schemas organise objects within a database. A good schema design separates data by its stage in the pipeline (raw, cleaned, final), by business domain (sales, hr, finance), or by access level (internal, shared, public).
-- Switch to the database first USE DATABASE RETAIL_DB; -- Create schemas for a typical data pipeline CREATE SCHEMA RAW_DATA COMMENT = 'Landing zone for raw incoming data — no transformations'; CREATE SCHEMA STAGING COMMENT = 'Cleaned and validated data — intermediate processing'; CREATE SCHEMA ANALYTICS COMMENT = 'Final analytical tables and views for BI tools'; CREATE SCHEMA REPORTS COMMENT = 'Views and aggregates for executive dashboards'; -- Transient schema (no Fail-Safe, lower cost for temporary work) CREATE TRANSIENT SCHEMA SANDBOX DATA_RETENTION_TIME_IN_DAYS = 1;
To run SQL against objects in a specific schema, set both the database and schema in your context, or use fully qualified names in the format database.schema.table.
-- Option 1: Set context (affects all subsequent statements) USE DATABASE RETAIL_DB; USE SCHEMA ANALYTICS; SELECT * FROM FACT_SALES; -- Snowflake knows to look in RETAIL_DB.ANALYTICS -- Option 2: Fully qualified name (explicit, always works regardless of context) SELECT * FROM RETAIL_DB.ANALYTICS.FACT_SALES;
Creating Tables
Tables are the core data containers in Snowflake. A table holds rows of data organised into named, typed columns. Snowflake supports standard SQL data types and some special types for handling JSON, XML, and other semi-structured data.
Basic Table Creation
USE DATABASE RETAIL_DB;
USE SCHEMA ANALYTICS;
CREATE TABLE CUSTOMERS (
customer_id NUMBER(10, 0) NOT NULL,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
email VARCHAR(255),
signup_date DATE,
is_active BOOLEAN DEFAULT TRUE,
annual_spend DECIMAL(12, 2),
created_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);
Snowflake Data Types Reference
CATEGORY DATA TYPE DESCRIPTION EXAMPLE
-------- --------- ----------- -------
Numbers NUMBER(p, s) Fixed precision decimal NUMBER(10,2) for money
INT / INTEGER Whole numbers 42
FLOAT / DOUBLE Floating point 3.14159
BIGINT Large whole numbers 1234567890
Text VARCHAR(n) Variable text up to n chars VARCHAR(255)
STRING Alias for VARCHAR(16MB) Any text
CHAR(n) Fixed-length text CHAR(2) for state codes
TEXT Alias for VARCHAR(16MB)
Date/Time DATE Calendar date 2024-06-15
TIME Time of day 14:30:00
TIMESTAMP_NTZ Date+time, no timezone 2024-06-15 14:30:00
TIMESTAMP_TZ Date+time with timezone 2024-06-15 14:30:00+05:30
TIMESTAMP_LTZ Date+time, local timezone (uses session timezone)
Boolean BOOLEAN TRUE or FALSE TRUE
Semi-struct. VARIANT JSON, XML, or any value {"name":"Alice","age":30}
ARRAY Ordered list ["a","b","c"]
OBJECT Key-value pairs {"key": "value"}
Binary BINARY(n) Raw binary data Image bytes
Table Types in Snowflake
TABLE TYPE TIME TRAVEL FAIL-SAFE USE WHEN ---------- ----------- --------- -------- Permanent Yes (0-90d) Yes (7d) Production data Transient Yes (0-1d) No Staging/intermediate data Temporary Yes (0-1d) No Session-only scratch data External No No Data stays in S3/cloud storage Dynamic Yes Yes Auto-refreshed from query
Temporary tables exist only for the duration of your current session. They vanish automatically when you disconnect. They are useful for storing intermediate calculation results without cluttering your schema with permanent objects.
Creating Tables From Queries: CTAS
CREATE TABLE AS SELECT (CTAS) creates a new table and populates it with the results of a query in one step. This is one of the most-used patterns in data engineering.
-- Create a summary table from existing raw data
CREATE TABLE ANALYTICS.MONTHLY_SALES AS
SELECT
DATE_TRUNC('MONTH', order_date) AS sale_month,
product_category,
SUM(amount) AS total_sales,
COUNT(DISTINCT customer_id) AS unique_customers
FROM RAW_DATA.ORDERS_RAW
WHERE status = 'COMPLETED'
GROUP BY 1, 2;
-- Result: MONTHLY_SALES table created with 4 columns and populated with aggregated data
-- Column data types are inferred automatically from the query results
Cloning: Instant Zero-Copy Copies
Snowflake's zero-copy cloning feature creates an instant copy of any table, schema, or database without duplicating the underlying data files. The clone shares the original's micro-partitions until rows in either copy are modified. This is one of Snowflake's most powerful and unique features.
ZERO-COPY CLONE DIAGRAM ======================== ORIGINAL TABLE: ORDERS (1TB of data in storage) │ ├── Storage before clone: 1TB (100 micro-partitions shared) │ CLONE: ORDERS_BACKUP ├── Created in: <1 second ├── Storage used by clone: 0 bytes initially ├── Both tables point to same 100 micro-partitions │ After you UPDATE 5 rows in ORDERS: │ ORDERS: 5 new micro-partitions written (modified data) │ + 95 original partitions (still shared with ORDERS_BACKUP) │ ORDERS_BACKUP: 95 original partitions (unchanged, shared) │ + 0 new partitions (untouched) │ Storage now: original 1TB + only the 5 new partitions (tiny additional cost)
-- Clone a table (instant, no data copy) CREATE TABLE ORDERS_BACKUP CLONE ORDERS; -- Clone an entire schema CREATE SCHEMA ANALYTICS_TEST CLONE ANALYTICS; -- Clone an entire database (creates exact copy for testing) CREATE DATABASE RETAIL_DB_DEV CLONE RETAIL_DB; -- Clone a table at a point in time (Time Travel clone) CREATE TABLE ORDERS_YESTERDAY CLONE ORDERS AT (OFFSET => -86400); -- 86400 seconds = 24 hours ago
Cloning is the fastest way to create test environments. Clone your production database into a development database in seconds. Developers experiment freely, knowing the original production data is untouched.
Dropping, Undropping, and Renaming Objects
-- Rename a table ALTER TABLE CUSTOMERS RENAME TO CUSTOMERS_V2; -- Add a new column to an existing table ALTER TABLE CUSTOMERS ADD COLUMN phone_number VARCHAR(20); -- Change a column data type (limited in Snowflake — only widening allowed) ALTER TABLE CUSTOMERS MODIFY COLUMN last_name VARCHAR(200); -- Drop a table (moves to Dropped Objects for Time Travel recovery period) DROP TABLE ORDERS_BACKUP; -- Recover a dropped table (within the Time Travel retention period) UNDROP TABLE ORDERS_BACKUP; -- Drop a schema (drops all objects inside it too) DROP SCHEMA SANDBOX; -- Drop a database DROP DATABASE TEMP_SANDBOX;
Snowflake's UNDROP command is a safety net for accidental drops. When you drop a table, Snowflake does not immediately delete the data — it retains it for the Time Travel period (1 day for transient objects, up to 90 days for permanent objects on Enterprise edition). During that window, UNDROP recovers the object fully. After the window closes, the data is permanently gone.
Viewing Existing Objects
-- List all databases you can access SHOW DATABASES; -- List schemas in the current database SHOW SCHEMAS; -- List all tables in the current schema SHOW TABLES; -- List all tables across all schemas in the current database SHOW TABLES IN DATABASE RETAIL_DB; -- Describe a table's column structure DESCRIBE TABLE CUSTOMERS; -- Get detailed metadata about a table SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CUSTOMERS' AND TABLE_SCHEMA = 'ANALYTICS';
Best Practices for Naming and Organising Objects
Consistent naming conventions save hours of confusion across large teams. Apply these conventions from the start of every project.
OBJECT CONVENTION EXAMPLE ------ ---------- ------- Databases UPPERCASE, underscore RETAIL_DB, HR_PROD, ANALYTICS_DEV Schemas UPPERCASE, underscore RAW_DATA, STAGING, ANALYTICS Tables (raw) Suffix _RAW ORDERS_RAW, EVENTS_RAW Tables (dim) Prefix DIM_ DIM_CUSTOMER, DIM_PRODUCT Tables (fact) Prefix FACT_ FACT_SALES, FACT_EVENTS Views Prefix VW_ VW_MONTHLY_REVENUE Stages Suffix _STAGE S3_ORDERS_STAGE
Key Points
- Snowflake uses a three-level hierarchy: Account → Database → Schema → Table/View/Stage
- Databases and schemas are containers for organisation; data lives in tables, views, and stages
- Snowflake supports Permanent, Transient, Temporary, External, and Dynamic table types — choose based on cost and retention needs
- CTAS (CREATE TABLE AS SELECT) creates and populates a table from a query in one step
- Zero-copy cloning creates instant copies of tables, schemas, or databases without duplicating storage — ideal for dev/test environments
- UNDROP recovers accidentally dropped objects within the Time Travel retention period
- Use the INFORMATION_SCHEMA views to query metadata about any object in your database programmatically