Snowflake

-- Create auto-scaling warehouse for analytics workloads
CREATE WAREHOUSE analytics_wh WITH
    WAREHOUSE_SIZE = 'LARGE'
    AUTO_SUSPEND = 60                    -- Auto-suspend after 1 minute
    AUTO_RESUME = TRUE                   -- Auto-resume on query
    MIN_CLUSTER_COUNT = 1                -- Minimum clusters
    MAX_CLUSTER_COUNT = 10               -- Scale to 10 clusters
    SCALING_POLICY = 'STANDARD'          -- Auto-scale based on queue
    COMMENT = 'Analytics warehouse with auto-scaling';

-- ETL warehouse with cost controls
CREATE WAREHOUSE etl_wh WITH
    WAREHOUSE_SIZE = 'X-LARGE'
    AUTO_SUSPEND = 300                   -- 5-minute suspend for batch jobs
    RESOURCE_MONITOR = 'monthly_budget'
    INITIALLY_SUSPENDED = TRUE;

-- Resource monitor for cost governance
CREATE RESOURCE MONITOR monthly_budget WITH
    CREDIT_QUOTA = 1000                  -- 1000 credit monthly limit
    FREQUENCY = MONTHLY
    START_TIMESTAMP = CURRENT_TIMESTAMP()
    TRIGGERS ON 75 PERCENT DO NOTIFY
             ON 90 PERCENT DO SUSPEND
             ON 100 PERCENT DO SUSPEND_IMMEDIATE;

-- Query historical data using Time Travel
SELECT * FROM orders 
    AT(TIMESTAMP => '2024-01-15 14:30:00'::timestamp)
WHERE order_date = '2024-01-15';

-- Zero-copy clone for development environment
CREATE DATABASE dev_analytics CLONE prod_analytics
    COMMENT = 'Development copy of production analytics';

-- Clone to specific point in time
CREATE TABLE customer_backup CLONE customers
    AT(OFFSET => -3600);  -- 1 hour ago (in seconds)

-- Undrop accidentally dropped objects
UNDROP TABLE customers;
UNDROP DATABASE analytics;

-- Streams and Tasks for real-time processing
CREATE STREAM order_changes ON TABLE raw.orders;

CREATE TASK process_orders
    WAREHOUSE = etl_wh
    SCHEDULE = '5 MINUTE'
    WHEN SYSTEM$STREAM_HAS_DATA('order_changes')
AS
    INSERT INTO analytics.order_summary
    SELECT order_date, COUNT(*), SUM(total)
    FROM order_changes
    WHERE metadata$action = 'INSERT'
    GROUP BY order_date;

-- Start the task
ALTER TASK process_orders RESUME;