Skip to main content

πŸ—‚οΈ 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​

CapabilityDescription
Logical isolationOrganize data by environment (DEV/QA/PROD), project, or domain
Time TravelQuery older versions of data
Fail-safeCritical 7-day recovery protection
Zero-Copy CloningFast environment creation without duplicating data
Secure Data SharingShare 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 CaseWhy Permanent?
Transactional historyMust be kept for audit for years
Sales FactsUsed for dashboards, regulatory reporting
Customer Master (DIM_CUSTOMER)Business-critical reference data
Payments or BillingCannot be lost; needs strong recovery
Regulatory data – GST, invoicesMust retain for 7–10 years

🧩 How Data Protection Works in Permanent DB

FeatureHow it works
Time TravelRestore dropped tables, recover data, query older versions
Fail-safe7 days extra recovery managed ONLY by Snowflake Support
CloningCreate 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 ComponentPermanentTransient
Base StorageSameSame
Time Travel (1 day sample)**+10%+10%
Fail-safe (7 days)**+15%0
TotalHigherLower

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

TypeTime TravelFail-safeEditableStorage CostUse Case
Standardβœ” Yesβœ” Yesβœ” YesHighProd workloads
Transientβœ” Limited❌ Noβœ” YesLowETL, staging, sandbox
Cloneβœ” Inheritedβœ” Inheritedβœ” YesVery lowDev/QA, snapshots
Shared❌ No❌ No❌ NoNoneExternal 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 DATABASE in 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. β„οΈπŸš€