Skip to main content

๐Ÿงฑ Introduction to Tables in Snowflake

A table in Snowflake is a structured object used to store rows of data. Tables are the foundation of any data warehouse or data analytics system. They help organize data into columns and rows so it can be queried, filtered, joined, and aggregated using SQL.


๐Ÿงฐ Why Use Tables in Snowflake?โ€‹

  • ๐Ÿ”Ž To store structured data in an optimized, compressed format.
  • ๐Ÿง  To run SQL queries for data analysis and transformation.
  • ๐Ÿ”„ To load, transform, and persist data for reporting and ML use cases.
  • โœ… To take advantage of features like time travel, fail-safe, cloning, and zero-copy restore.

๐Ÿงฑ Where Is the Data Stored?โ€‹

Snowflake separates storage from compute:

  • ๐Ÿ“ฆ Storage Layer: Your table data is stored in cloud object storage (like AWS S3, Azure Blob, or GCP) in compressed columnar format.
  • โš™๏ธ Compute Layer: When you query a table, data is pulled into temporary memory (RAM) in the virtual warehouse to process the query.
  • โ˜๏ธ Cloud Services Layer: Manages metadata, security, and optimization.

๐Ÿง  This architecture lets Snowflake scale, optimize, and protect your data automatically.


๐Ÿงพ Common Use Cases for Tablesโ€‹

Use CaseDescription
๐ŸŒ Data warehousingStore fact and dimension tables
๐Ÿ” ELT/ETL stagingHold temporary or intermediate processed data
๐Ÿงช Ad hoc analysisPerform testing, filtering, sampling on raw data
๐Ÿ“ฆ External data accessQuery data stored in S3, Azure Blob, or GCS
๐ŸงŠ Lakehouse analyticsUse open formats (like Iceberg) across engines

๐Ÿ“‚ Types of Tables in Snowflakeโ€‹

Snowflake offers five types of tables, depending on how long you need to keep the data, whether you want backups, and where the data resides.

1. ๐Ÿ›๏ธ Permanent Tableโ€‹

  • Default type โ€” data is stored until manually dropped.
  • Supports:
    • โœ… Time Travel (up to 90 days)
    • โœ… Fail-safe (7 days backup)
    • โœ… Cloning, compression, and optimization
    • ๐Ÿ’ก Use for production and long-term storage.

CREATE TABLE employees (
emp_id INT,
name STRING,
department STRING
);

FeatureDetails
PersistencePermanent
Time Travelโœ… Yes (1 to 90 days)
Fail-safeโœ… Yes (7 days extra for recovery)
CostRegular storage + Fail-safe cost

2. โšก Transient Tableโ€‹

  • Cheaper than permanent; no Fail-safe.
  • โœ… Supports Time Travel (short duration).
  • ๐Ÿ’ก Use for intermediate or staging data in ELT.

3. ๐Ÿ•’ Temporary Tableโ€‹

  • Exists only during the session.
  • โŒ No Time Travel or Fail-safe.
  • ๐Ÿ’ก Use for session-specific or scratchpad work.

4. ๐Ÿ“ฆ External Tableโ€‹

  • References data outside Snowflake (e.g., S3, Blob, GCS).
  • Useful for querying files without loading them.
  • ๐Ÿ’ก Use to access raw files like CSV, JSON, Parquet.

5. ๐ŸงŠ Iceberg Tableโ€‹

  • Based on Apache Iceberg open table format.
  • Allows schema evolution, versioning, and cross-platform access.
  • ๐Ÿ’ก Use for modern data lakehouse and multi-engine scenarios.

๐Ÿ“š Comparison Tableโ€‹

Table TypeStorage LocationTime TravelFail-safeAuto DroppedExternal Data
๐Ÿ›๏ธ PermanentInternal (Snowflake)โœ… Yesโœ… YesโŒ NoโŒ No
โšก TransientInternalโœ… ShortโŒ NoโŒ NoโŒ No
๐Ÿ•’ TemporaryInternalโŒ NoโŒ Noโœ… YesโŒ No
๐Ÿ“ฆ ExternalExternal (e.g., S3)โŒ NoโŒ NoโŒ Noโœ… Yes
๐ŸงŠ IcebergInternal/Externalโœ… Yesโœ… YesโŒ Noโœ… Optional

๐Ÿ› ๏ธ SQL Examplesโ€‹

-- 1. Permanent Table
CREATE TABLE emp (
emp_id INT,
name STRING,
hire_date DATE
);

-- 2. Transient Table
CREATE TRANSIENT TABLE stage_data (
file_name STRING,
load_time TIMESTAMP
);

-- 3. Temporary Table
CREATE TEMPORARY TABLE temp_result AS
SELECT * FROM emp WHERE hire_date >= '2024-01-01';

-- 4. External Table
CREATE EXTERNAL TABLE ext_sales_data (
id INT,
amount FLOAT
)
WITH LOCATION = @my_s3_stage/sales_data
FILE_FORMAT = (TYPE = PARQUET);

-- 5. Iceberg Table (Managed)
CREATE ICEBERG TABLE iceberg_orders (
order_id INT,
customer_id STRING,
order_ts TIMESTAMP
);

๐Ÿง  Summary: When to Use What?โ€‹

ScenarioRecommended Table Type
Long-term transactional storagePermanent Table
Cost-efficient intermediate dataTransient Table
Temporary scratchpad during sessionTemporary Table
Query raw data without ingestionExternal Table
Open format lakehouse + versioningIceberg Table

๐Ÿ” Swapping Tables (Blue/Green Loads)โ€‹

Snowflake can exchange two tables in a single metadata operation, with no data copied. A common pattern is to build a fresh copy of a table, validate it, and then swap it into place so users never see a half-loaded table.

-- 1. Build the new version alongside production
CREATE OR REPLACE TABLE emp_new CLONE emp;
-- ... load / transform emp_new and validate it ...

-- 2. Swap instantly: emp now holds the new data, emp_new holds the old
ALTER TABLE emp SWAP WITH emp_new;

-- 3. Drop the old version once you're happy
DROP TABLE emp_new;

๐Ÿงฎ When You Run SELECT * FROM emp; โ€” What Happens?โ€‹

Let's assume emp is a permanent table in Snowflake.


โš™๏ธ Behind the Scenes: Step-by-Stepโ€‹

  1. ๐Ÿงฑ Storage Layer (Where your data is stored)โ€‹

    • All table data (emp in this case) is stored compressed and optimized in Snowflake-managed cloud storage (e.g., AWS S3, Azure Blob, or GCP).
    • Data is stored in micro-partitions โ€” think of them as efficient, columnar storage blocks.
  2. ๐Ÿ’ป Compute Layer (Virtual Warehouse)โ€‹

    • When you run SELECT * FROM emp;, Snowflake uses a virtual warehouse (compute engine) to process your query.
    • The warehouse reads the required micro-partitions from storage into temporary memory and processes them.
  3. ๐Ÿ“ค Result Returnedโ€‹

    • Snowflake fetches the data into the compute engine's memory, executes the query, and returns the result set to you.
    • The data does not get physically moved โ€” it stays in cloud storage, and only the required parts are scanned.

๐Ÿ“ Where Does the Data Come From?โ€‹

LayerDescriptionYour Example (SELECT * FROM emp;)
๐Ÿ—ƒ๏ธ Storage LayerSnowflake-managed storage (S3, Blob)Data read from micro-partitions
โš™๏ธ Compute LayerVirtual WarehouseQuery runs here, data is processed
๐Ÿ–ฅ๏ธ Client LayerSnowsight, CLI, or AppYou view the results here

๐ŸŽฏ Summaryโ€‹

  • The data lives in Snowflake-managed storage.
  • When you query it, Snowflake's compute engine fetches only the relevant partitions.
  • Your query runs in-memory inside the virtual warehouse.
  • Snowflake abstracts the complexity โ€” you never deal with files, folders, or storage formats.

๐Ÿ’ก Bonus Tip for Interview Useโ€‹

"In Snowflake, compute and storage are decoupled. My data sits in highly optimized cloud storage, and when I run a query, a virtual warehouse reads only the required partitions to return results efficiently."