ποΈ Snowflake Databases β A Complete & Practical Overview
In Snowflake, databases and schemas help logically organize data across environments, teams, and projects. This section covers what a database is, types of databases, best practices, and essential SQL commands, all with clarity and real-world context.
π What is a Database in Snowflake?
A database is the highest-level logical container in Snowflake. It stores:
- Schemas
- Tables
- Views
- Stages
- Functions and other objects
Key Capabilities of a Snowflake Databaseβ
| Capability | Description |
|---|---|
| Logical isolation | Organize data by environment (DEV/QA/PROD), project, or domain |
| Time Travel | Query older versions of data |
| Fail-safe | Critical 7-day recovery protection |
| Zero-Copy Cloning | Fast environment creation without duplicating data |
| Secure Data Sharing | Share live data without copying |
π οΈ Types of Databases in Snowflake
Snowflake supports multiple database types for different purposes. Below is a clean, business-friendly explanation.
β Standard Database (Permanent)
A Standard (Permanent) Database in Snowflake is used for critical, long-term storage. It provides:
- β Full Time Travel (1β90 days depending on edition)
- β 7-day Fail-safe (Snowflake-managed recovery)
- β Data durability and long-term retention
It is mainly used in Production environments.
π¦ Example Scenario
Imagine you are building a Retail Data Warehouse. You need to store:
- Sales history (many years)
- Customer master
- Product master
- Financial transactions (important for audit)
This data must be kept permanently and cannot be lost.
So you create a Permanent Database:
π Example: Create Permanent Database
CREATE OR REPLACE DATABASE RETAIL_DW
DATA_RETENTION_TIME_IN_DAYS = 30; -- Time Travel
β This database now supports:
- 30 days of Time Travel
- 7 days Fail-safe
- Permanent storage
π Tables Inside This Permanent Database
1οΈβ£ Dimension Table (Permanent)β
CREATE OR REPLACE TABLE RETAIL_DW.DIM.CUSTOMER (
CUSTOMER_ID NUMBER,
NAME VARCHAR,
EMAIL VARCHAR,
CITY VARCHAR
);
2οΈβ£ Fact Table (Permanent)β
CREATE OR REPLACE TABLE RETAIL_DW.FACT.SALES (
SALE_ID NUMBER,
CUSTOMER_ID NUMBER,
PRODUCT_ID NUMBER,
SALE_DATE DATE,
AMOUNT NUMBER(10,2)
);
These tables:
β Store production history β Can be restored for up to 30 days Time Travel β Have Fail-safe for 7 days for disaster recovery
π Why Use a Permanent Database?
πΉ Real Example Use Casesβ
| Use Case | Why Permanent? |
|---|---|
| Transactional history | Must be kept for audit for years |
| Sales Facts | Used for dashboards, regulatory reporting |
| Customer Master (DIM_CUSTOMER) | Business-critical reference data |
| Payments or Billing | Cannot be lost; needs strong recovery |
| Regulatory data β GST, invoices | Must retain for 7β10 years |
π§© How Data Protection Works in Permanent DB
| Feature | How it works |
|---|---|
| Time Travel | Restore dropped tables, recover data, query older versions |
| Fail-safe | 7 days extra recovery managed ONLY by Snowflake Support |
| Cloning | Create full zero-copy clone of the DB instantly |
Example of Time Travel recovery:
SELECT * FROM EMP AT (OFFSET => -300);
(Reads data as of 5 mins ago)
π― Summary
A Permanent Database in Snowflake is ideal for:
- Production
- Financial reporting
- Historical facts
- Critical dimensions
- Regulatory and audit data
It provides both Time Travel and Fail-safe, giving the highest data safety.
2οΈβ£ Transient Database (Cost-Optimized)β
β No Fail-safe
β Limited Time Travel
β Lower storage cost
β Safe for non-critical data
Used for:
- Staging / landing layers
- Temporary or intermediate datasets
- Sandbox / experimentation
π° Why is it cheaper?β
Because Fail-safe is disabled, you only pay for base storage + minimal Time Travel.
| Cost Component | Permanent | Transient |
|---|---|---|
| Base Storage | Same | Same |
| Time Travel (1 day sample)** | +10% | +10% |
| Fail-safe (7 days)** | +15% | 0 |
| Total | Higher | Lower |
3οΈβ£ Cloned Database (Zero-Copy)β
Cloning creates a full database copy instantly using metadata pointers.
β No additional storage
β No duplication
β Only modified data consumes space
β Fastest way to create DEV/QA environments
Best For:
- Testing new code
- Data validation
- CI/CD pipelines
- Point-in-time snapshots
4οΈβ£ Shared Database (From Share)β
Created from a Snowflake Share:
- Read-only
- Always up to date
- Zero-copy access
- Provided by partners, internal teams, or Snowflake Marketplace
Best For:
- Consuming external datasets
- Inter-department data sharing
- Marketplace feeds (e.g., financial, weather, geospatial data)
π Quick Comparison Table
| Type | Time Travel | Fail-safe | Editable | Storage Cost | Use Case |
|---|---|---|---|---|---|
| Standard | β Yes | β Yes | β Yes | High | Prod workloads |
| Transient | β Limited | β No | β Yes | Low | ETL, staging, sandbox |
| Clone | β Inherited | β Inherited | β Yes | Very low | Dev/QA, snapshots |
| Shared | β No | β No | β No | None | External or cross-account data sharing |
π§ Best Practices for Databases
β Productionβ
Use Standard databases for all critical data.
β ETL / Staging Layersβ
Use Transient databases to reduce storage cost.
β Dev, QA, UATβ
Use Cloned databasesβinstant, consistent, efficient.
β Securityβ
- Grant USAGE at database & schema levels
- Follow least privilege principle
- Avoid
CREATE OR REPLACE DATABASEin PROD
β Naming Conventionsβ
Use clear prefixes for environments:
PROD_SALES_DB
DEV_ANALYTICS_DB
QA_ETL_DB
π Access Control (RBAC Essentials)
To access or create objects, roles must be granted specific privileges:
GRANT USAGE ON DATABASE my_db TO ROLE analyst;
GRANT USAGE ON SCHEMA my_db.public TO ROLE analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA my_db.public TO ROLE analyst;
π§Ύ Common SQL Commands
Create Databasesβ
CREATE DATABASE sales_db;
CREATE TRANSIENT DATABASE etl_stage_db;
CREATE DATABASE dev_db CLONE prod_db;
CREATE DATABASE vendor_data FROM SHARE vendor.acct.shared_data;
Use a Databaseβ
USE DATABASE sales_db;
Grantsβ
GRANT USAGE ON DATABASE sales_db TO ROLE bi_team;
GRANT CREATE TABLE ON SCHEMA sales_db.raw TO ROLE etl_role;
β Key Takeaways
-
Snowflake databases help organize and secure data across environments.
-
Choose the right type:
- Standard β Production & critical systems
- Transient β ETL, staging, sandbox
- Clone β Dev, QA, testing environments
- Shared β Marketplace / cross-account data
-
Zero-copy architecture brings speed, cost savings, and flexibility.
-
RBAC ensures proper access control and avoids accidental modifications.
-
Always design environments using best practices for scalability and governance.
By understanding and properly leveraging database types and schema structures, Snowflake users can build scalable, secure, and cost-efficient data environments. βοΈπ