Snowflake Advanced

Snowflake Advanced Features

-- Snowflake architecture
-- Virtual Warehouse: compute (auto-suspend, auto-resume)
-- Storage: separate from compute (pay per TB stored)
-- Multi-cluster: auto-scale for concurrent users

-- Create and size warehouses
CREATE WAREHOUSE analytics_wh
    WAREHOUSE_SIZE = 'MEDIUM'       -- S,M,L,XL,2XL,3XL,4XL
    AUTO_SUSPEND   = 60             -- seconds idle before suspend
    AUTO_RESUME    = TRUE
    MAX_CLUSTER_COUNT = 3           -- scale out for concurrency
    MIN_CLUSTER_COUNT = 1;

-- Time travel (query historical data)
SELECT * FROM orders AT (TIMESTAMP => '2025-01-01 00:00:00'::TIMESTAMP);
SELECT * FROM orders BEFORE (STATEMENT => 'query-id-abc123');

-- Zero-copy cloning (instant, no storage cost)
CREATE DATABASE dev CLONE prod;
CREATE TABLE orders_backup CLONE orders;

-- Dynamic data masking (column-level security)
CREATE MASKING POLICY email_mask AS (val STRING) RETURNS STRING ->
    CASE WHEN CURRENT_ROLE() IN ('ANALYST') THEN val
         ELSE REGEXP_REPLACE(val, '.+@', '*****@')
    END;
ALTER TABLE customers MODIFY COLUMN email SET MASKING POLICY email_mask;

-- Clustering keys (replace partitioning)
ALTER TABLE orders CLUSTER BY (TO_DATE(created_at));