๐งฑ 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 Case | Description |
|---|---|
| ๐ Data warehousing | Store fact and dimension tables |
| ๐ ELT/ETL staging | Hold temporary or intermediate processed data |
| ๐งช Ad hoc analysis | Perform testing, filtering, sampling on raw data |
| ๐ฆ External data access | Query data stored in S3, Azure Blob, or GCS |
| ๐ง Lakehouse analytics | Use 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
);
| Feature | Details |
|---|---|
| Persistence | Permanent |
| Time Travel | โ Yes (1 to 90 days) |
| Fail-safe | โ Yes (7 days extra for recovery) |
| Cost | Regular 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 Type | Storage Location | Time Travel | Fail-safe | Auto Dropped | External Data |
|---|---|---|---|---|---|
| ๐๏ธ Permanent | Internal (Snowflake) | โ Yes | โ Yes | โ No | โ No |
| โก Transient | Internal | โ Short | โ No | โ No | โ No |
| ๐ Temporary | Internal | โ No | โ No | โ Yes | โ No |
| ๐ฆ External | External (e.g., S3) | โ No | โ No | โ No | โ Yes |
| ๐ง Iceberg | Internal/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?โ
| Scenario | Recommended Table Type |
|---|---|
| Long-term transactional storage | Permanent Table |
| Cost-efficient intermediate data | Transient Table |
| Temporary scratchpad during session | Temporary Table |
| Query raw data without ingestion | External Table |
| Open format lakehouse + versioning | Iceberg 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โ
-
๐งฑ Storage Layer (Where your data is stored)โ
- All table data (
empin 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.
- All table data (
-
๐ป 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.
- When you run
-
๐ค 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?โ
| Layer | Description | Your Example (SELECT * FROM emp;) |
|---|---|---|
| ๐๏ธ Storage Layer | Snowflake-managed storage (S3, Blob) | Data read from micro-partitions |
| โ๏ธ Compute Layer | Virtual Warehouse | Query runs here, data is processed |
| ๐ฅ๏ธ Client Layer | Snowsight, CLI, or App | You 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."