1. Permanent Table:
Use Case:
Storing customer data for long-term use.
-- Create table
CREATE OR REPLACE TABLE customers (
customer_id INT,
name STRING,
email STRING,
signup_date DATE
);
-- Insert data
INSERT INTO customers VALUES
(1, 'Alice', 'alice@example.com', '2024-01-15'),
(2, 'Bob', 'bob@example.com', '2024-02-20');
-- Query with Time
Travel (retrieve data as of 1 hour ago)
SELECT * FROM customers AT(OFFSET => -3600);
2. Temporary Table
Use Case: Storing intermediate results during a session.
-- Create temp table for active users
CREATE TEMP TABLE active_users AS
SELECT * FROM customers
WHERE signup_date >= '2024-01-01';
-- Query temp table (only visible in this session)
SELECT * FROM active_users;
-- No need to drop; vanishes when session ends.
3. Transient Table
Use
Case: Staging data for ETL (no Fail-safe needed).
-- Create transient table
CREATE TRANSIENT TABLE raw_logs (
log_id INT,
event_time TIMESTAMP,
event_data VARIANT);
-- Load data (e.g., from a stage)
COPY INTO raw_logs FROM @my_stage/logs/;
4. External Table
Use Case: Querying Parquet files in S3 without loading.
-- Create external stage (if not exists)
CREATE STAGE my_s3_stage
URL = 's3://my-bucket/path/'
CREDENTIALS = (AWS_KEY_ID = '...' AWS_SECRET_KEY = '...');
-- Create external table for Parquet files
CREATE EXTERNAL TABLE sales_external
LOCATION = @my_s3_stage/sales/
FILE_FORMAT = (TYPE = PARQUET);
-- Query directly
SELECT * FROM sales_external WHERE region = 'EMEA';
5. Dynamic Table
Use
Case:
Auto-refreshing aggregated sales data.
-- Source table (orders)
CREATE TABLE orders (
order_id INT,
customer_id INT,
amount NUMBER(10,2),
order_date DATE
);
-- Dynamic table (materialized view)
CREATE DYNAMIC TABLE daily_sales
TARGET_LAG = '5 MINUTES' -- Refresh every 5 mins
AS
SELECT
order_date,
SUM(amount) AS total_sales
FROM orders
GROUP BY order_date;
-- Query (always up-to-date)
SELECT * FROM daily_sales;
6. Iceberg Table
Use Case: Interoperability with data lakes.
-- Create external volume (if not exists)
CREATE EXTERNAL VOLUME iceberg_vol
STORAGE_LOCATIONS = (
(
NAME = 'my-iceberg-bucket'
STORAGE_PROVIDER = 'S3'
STORAGE_BASE_URL = 's3://my-iceberg-bucket/'
)
);
-- Create Iceberg table
CREATE ICEBERG TABLE customer_iceberg
CATALOG = 'my_catalog'
EXTERNAL_VOLUME = 'iceberg_vol'
AS SELECT * FROM customers;
-- Query (supports Iceberg features like time travel)
SELECT * FROM customer_iceberg VERSION AS OF '2024-05-01';
Key Operations Across All Tables
-- View table metadata
SHOW TABLES LIKE '%customers%';
-- Clone a table (zero-copy)
CREATE TABLE customers_dev CLONE customers;
-- Drop a table
DROP TABLE customers_dev;
Advanced
Snowflake Table Examples with Real-World Scenarios
1.
Permanent Table with
Advanced Features
Use Case: Enterprise customer data warehouse with change tracking
-- Create a permanent table with clustering and comment CREATE TABLE customers (
customer_id INT AUTOINCREMENT START 1 INCREMENT 1,
name STRING NOT NULL,
email STRING UNIQUE,
signup_date DATE DEFAULT CURRENT_DATE(),
lifetime_value NUMBER(38,2),
preferences VARIANT,
created_at TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP(),
updated_at TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP(),
COMMENT = 'Master customer dimension table'
) CLUSTER BY (date_trunc('MONTH', signup_date));
-- Add a tag for PII classification
ALTER TABLE customers SET TAG security.pii_level = 'high';
-- Create a stream for change tracking
CREATE STREAM customer_changes ON TABLE customers;
-- Create a task to process changes
CREATE TASK process_customer_updates
WAREHOUSE = compute_wh
SCHEDULE = '5 MINUTE'
AS
MERGE INTO customer_archive t
USING (SELECT * FROM customer_changes) s
ON t.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET t.updated_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN INSERT (customer_id, name, email)
VALUES (s.customer_id, s.name, s.email);
