-- Basic permanent table creation (default table type)
CREATE TABLE customers (
    customer_id INT,
    customer_name VARCHAR(200),
    email VARCHAR(200),
    signup_date DATE,
    lifetime_value DECIMAL(10,2)
);

-- Permanent table with explicit type specification
CREATE OR REPLACE PERMANENT TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    order_amount DECIMAL(10,2),
    status VARCHAR(50)
);

-- Permanent table with extended Time Travel
CREATE TABLE transactions (
    transaction_id BIGINT,
    account_id INT,
    transaction_date TIMESTAMP,
    amount DECIMAL(15,2),
    transaction_type VARCHAR(50)
) DATA_RETENTION_TIME_IN_DAYS = 90;

-- Permanent table with clustering
CREATE TABLE sales_fact (
    sale_date DATE,
    product_id INT,
    customer_id INT,
    store_id INT,
    quantity INT,
    amount DECIMAL(10,2)
) CLUSTER BY (sale_date, product_id);

-- Customer dimension with full protection
CREATE OR REPLACE TABLE dim_customer (
    customer_key INT AUTOINCREMENT PRIMARY KEY,
    customer_id VARCHAR(50) NOT NULL,
    customer_name VARCHAR(200),
    email VARCHAR(200),
    segment VARCHAR(50),
    registration_date DATE,
    
    -- SCD Type 2 tracking
    effective_date DATE NOT NULL,
    expiration_date DATE NOT NULL,
    current_flag CHAR(1) NOT NULL DEFAULT 'Y',
    
    -- Audit columns
    created_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
    updated_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
) DATA_RETENTION_TIME_IN_DAYS = 30
COMMENT = 'Customer dimension with Type 2 slowly changing dimension tracking';

-- Sales fact table with clustering
CREATE OR REPLACE TABLE fact_sales (
    sale_id BIGINT AUTOINCREMENT PRIMARY KEY,
    sale_date DATE NOT NULL,
    customer_key INT NOT NULL,
    product_key INT NOT NULL,
    store_key INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    loaded_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
) CLUSTER BY (sale_date)
DATA_RETENTION_TIME_IN_DAYS = 90
COMMENT = 'Sales transactions fact table with 90-day Time Travel';

-- Basic temporary table
CREATE TEMPORARY TABLE session_data (
    session_id VARCHAR(100),
    user_id INT,
    page_views INT,
    session_duration INT
);

-- Temporary table with explicit retention
CREATE TEMP TABLE temp_calculations (
    calculation_id INT,
    result_value DECIMAL(15,4),
    calculation_timestamp TIMESTAMP
) DATA_RETENTION_TIME_IN_DAYS = 1;

-- Temporary table from query results
CREATE TEMPORARY TABLE temp_customer_summary AS
SELECT 
    customer_id,
    COUNT(*) as order_count,
    SUM(order_amount) as total_spent,
    MAX(order_date) as last_order_date
FROM orders
WHERE order_date >= DATEADD(year, -1, CURRENT_DATE())
GROUP BY customer_id;

-- Temporary table with clustering
CREATE TEMPORARY TABLE temp_large_dataset (
    date_key DATE,
    category VARCHAR(100),
    metric_value DECIMAL(15,2)
) CLUSTER BY (date_key);

-- ETL process using temporary tables for staging

-- Step 1: Extract and stage raw data
CREATE TEMPORARY TABLE temp_raw_orders AS
SELECT *
FROM external_stage.orders_csv
WHERE order_date >= CURRENT_DATE() - 7;

-- Step 2: Cleanse and validate
CREATE TEMPORARY TABLE temp_clean_orders AS
SELECT 
    TRIM(order_id) as order_id,
    UPPER(customer_id) as customer_id,
    order_date,
    CAST(NULLIF(order_amount, '') AS DECIMAL(10,2)) as order_amount,
    CASE 
        WHEN status IN ('COMPLETE', 'SHIPPED') THEN status
        ELSE 'UNKNOWN'
    END as status
FROM temp_raw_orders
WHERE order_id IS NOT NULL
    AND customer_id IS NOT NULL
    AND order_amount > 0;

-- Step 3: Enrich with dimension keys
CREATE TEMPORARY TABLE temp_enriched_orders AS
SELECT 
    o.order_id,
    d.date_key,
    c.customer_key,
    o.order_amount,
    o.status
FROM temp_clean_orders o
JOIN dim_date d ON o.order_date = d.full_date
JOIN dim_customer c ON o.customer_id = c.customer_id 
    AND c.current_flag = 'Y';

-- Step 4: Load into permanent fact table
INSERT INTO fact_orders
SELECT * FROM temp_enriched_orders;

-- Temporary tables automatically drop at session end

-- Basic transient table
CREATE TRANSIENT TABLE staging_data (
    record_id INT,
    raw_json VARIANT,
    loaded_timestamp TIMESTAMP
);

-- Transient table with 1-day Time Travel
CREATE TRANSIENT TABLE etl_staging (
    batch_id VARCHAR(50),
    source_system VARCHAR(100),
    record_count INT,
    status VARCHAR(50),
    process_timestamp TIMESTAMP
) DATA_RETENTION_TIME_IN_DAYS = 1;

-- Transient table from query
CREATE TRANSIENT TABLE derived_metrics AS
SELECT 
    metric_date,
    metric_name,
    metric_value,
    CURRENT_TIMESTAMP() as calculated_timestamp
FROM base_metrics
WHERE metric_date >= DATEADD(month, -3, CURRENT_DATE());

-- Transient table with clustering
CREATE TRANSIENT TABLE temp_analytics (
    event_date DATE,
    user_id INT,
    event_type VARCHAR(50),
    event_count INT
) CLUSTER BY (event_date);

-- Create transient staging schema
CREATE SCHEMA IF NOT EXISTS staging;

-- Transient tables for daily data loads
CREATE TRANSIENT TABLE staging.daily_sales (
    sale_id VARCHAR(50),
    sale_date DATE,
    product_id VARCHAR(50),
    customer_id VARCHAR(50),
    quantity INT,
    amount DECIMAL(10,2),
    raw_data VARIANT,
    loaded_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);

CREATE TRANSIENT TABLE staging.daily_inventory (
    snapshot_date DATE,
    product_id VARCHAR(50),
    warehouse_id VARCHAR(50),
    quantity_available INT,
    loaded_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);

-- Load staging from external stage
COPY INTO staging.daily_sales
FROM @external_stage/sales/
FILE_FORMAT = (TYPE = 'JSON')
ON_ERROR = 'CONTINUE';

-- Transform and load into permanent tables
INSERT INTO fact_sales (
    sale_date,
    product_key,
    customer_key,
    quantity,
    amount
)
SELECT 
    s.sale_date,
    p.product_key,
    c.customer_key,
    s.quantity,
    s.amount
FROM staging.daily_sales s
JOIN dim_product p ON s.product_id = p.product_id
JOIN dim_customer c ON s.customer_id = c.customer_id;

-- Truncate staging after successful load
TRUNCATE TABLE staging.daily_sales;

-- Create external stage pointing to S3
CREATE OR REPLACE STAGE external_s3_stage
URL = 's3://mybucket/data/'
CREDENTIALS = (AWS_KEY_ID = 'xxx' AWS_SECRET_KEY = 'xxx');

-- External table from Parquet files
CREATE OR REPLACE EXTERNAL TABLE ext_sales (
    sale_id INT AS (value:c1::INT),
    sale_date DATE AS (value:c2::DATE),
    customer_id INT AS (value:c3::INT),
    amount DECIMAL(10,2) AS (value:c4::DECIMAL(10,2))
)
LOCATION = @external_s3_stage/sales/
FILE_FORMAT = (TYPE = PARQUET)
AUTO_REFRESH = TRUE
REFRESH_ON_CREATE = TRUE;

-- External table from JSON files with partitioning
CREATE OR REPLACE EXTERNAL TABLE ext_events (
    event_id VARCHAR AS (value:event_id::VARCHAR),
    event_type VARCHAR AS (value:event_type::VARCHAR),
    user_id INT AS (value:user_id::INT),
    event_timestamp TIMESTAMP AS (value:timestamp::TIMESTAMP)
)
PARTITION BY (event_date DATE AS (value:timestamp::DATE))
LOCATION = @external_s3_stage/events/
FILE_FORMAT = (TYPE = JSON)
AUTO_REFRESH = TRUE;

-- External table from CSV with pattern matching
CREATE OR REPLACE EXTERNAL TABLE ext_logs (
    log_timestamp TIMESTAMP AS (value:c1::TIMESTAMP),
    log_level VARCHAR AS (value:c2::VARCHAR),
    message VARCHAR AS (value:c3::VARCHAR)
)
LOCATION = @external_s3_stage/logs/
PATTERN = '.*\.csv'
FILE_FORMAT = (
    TYPE = CSV
    SKIP_HEADER = 1
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'
)
AUTO_REFRESH = TRUE;

-- Query external table like any other table
SELECT 
    DATE_TRUNC('day', event_timestamp) as event_date,
    event_type,
    COUNT(*) as event_count
FROM ext_events
WHERE event_timestamp >= DATEADD(day, -7, CURRENT_TIMESTAMP())
GROUP BY event_date, event_type
ORDER BY event_date, event_count DESC;

-- Join external table with internal tables
SELECT 
    c.customer_name,
    e.event_type,
    COUNT(*) as event_count
FROM ext_events e
JOIN dim_customer c ON e.user_id = c.customer_id
WHERE e.event_timestamp >= CURRENT_DATE() - 30
GROUP BY c.customer_name, e.event_type;

-- Refresh external table metadata
ALTER EXTERNAL TABLE ext_sales REFRESH;

-- View external table files
SELECT * FROM TABLE(
    INFORMATION_SCHEMA.EXTERNAL_TABLE_FILES(
        TABLE_NAME => 'ext_sales'
    )
);

-- Hybrid architecture: recent data in Snowflake, historical in external storage

-- Internal table for recent data (fast access)
CREATE OR REPLACE TABLE sales_recent (
    sale_id INT,
    sale_date DATE,
    customer_id INT,
    amount DECIMAL(10,2)
) AS
SELECT * FROM source_table
WHERE sale_date >= DATEADD(year, -1, CURRENT_DATE());

-- External table for historical data (cost-effective storage)
CREATE OR REPLACE EXTERNAL TABLE sales_historical (
    sale_id INT AS (value:sale_id::INT),
    sale_date DATE AS (value:sale_date::DATE),
    customer_id INT AS (value:customer_id::INT),
    amount DECIMAL(10,2) AS (value:amount::DECIMAL(10,2))
)
LOCATION = @historical_stage/sales/
FILE_FORMAT = (TYPE = PARQUET);

-- Union view providing unified access
CREATE OR REPLACE VIEW sales_complete AS
SELECT * FROM sales_recent
UNION ALL
SELECT * FROM sales_historical;

-- Query unified view transparently
SELECT 
    YEAR(sale_date) as sale_year,
    COUNT(*) as sale_count,
    SUM(amount) as total_amount
FROM sales_complete
GROUP BY sale_year
ORDER BY sale_year;

START: What is the data's purpose and requirements?

├─ Is data needed only during current session?
│  └─ YES → Use TEMPORARY TABLE
│
├─ Is data in external storage (S3, Azure Blob, GCS)?
│  ├─ Will query frequently? → Consider loading to internal table
│  └─ Query occasionally? → Use EXTERNAL TABLE
│
├─ Is data critical requiring disaster recovery?
│  ├─ YES → Use PERMANENT TABLE
│  │   └─ How long need Time Travel? → Set DATA_RETENTION_TIME_IN_DAYS
│  │
│  └─ NO → Is data recreatable from sources?
│      ├─ YES → Use TRANSIENT TABLE (save 25-30% storage cost)
│      └─ NO → Use PERMANENT TABLE (protection justifies cost)
│
└─ Is cost optimization critical?
   └─ YES → Use TRANSIENT for staging, EXTERNAL for archives

-- Production fact table (permanent)
CREATE OR REPLACE TABLE fact_orders (
    order_key BIGINT,
    order_date DATE,
    customer_key INT,
    product_key INT,
    quantity INT,
    amount DECIMAL(10,2)
) CLUSTER BY (order_date)
DATA_RETENTION_TIME_IN_DAYS = 90;

-- Staging table (transient)
CREATE OR REPLACE TRANSIENT TABLE staging_orders (
    raw_data VARIANT,
    loaded_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
) DATA_RETENTION_TIME_IN_DAYS = 1;

-- Session analysis (temporary)
CREATE TEMPORARY TABLE temp_order_analysis AS
SELECT 
    customer_key,
    SUM(amount) as total_spent
FROM fact_orders
WHERE order_date >= CURRENT_DATE() - 365
GROUP BY customer_key;

-- Critical financial data: maximum retention
ALTER TABLE financial_transactions 
SET DATA_RETENTION_TIME_IN_DAYS = 90;

-- Standard operational data: moderate retention
ALTER TABLE user_activities 
SET DATA_RETENTION_TIME_IN_DAYS = 30;

-- High-volume logs: minimal retention
ALTER TABLE application_logs 
SET DATA_RETENTION_TIME_IN_DAYS = 7;

-- Staging data: minimal retention
ALTER TABLE staging_imports 
SET DATA_RETENTION_TIME_IN_DAYS = 1;

-- Cluster by frequently filtered columns
CREATE TABLE large_fact_table (
    transaction_date DATE,
    product_id INT,
    region VARCHAR(50),
    amount DECIMAL(15,2)
) CLUSTER BY (transaction_date, region);

-- Monitor clustering effectiveness
SELECT SYSTEM$CLUSTERING_INFORMATION('large_fact_table');

-- Recluster if needed (automatic in Snowflake)
ALTER TABLE large_fact_table RECLUSTER;

-- Efficient data types reduce storage
CREATE TABLE optimized_table (
    id INT,                          -- Use INT not BIGINT if values fit
    status VARCHAR(20),              -- Size appropriately, not VARCHAR(1000)
    amount DECIMAL(10,2),            -- Precise decimal not FLOAT
    description VARCHAR(500),        -- Reasonable size for text
    metadata VARIANT,                -- Use VARIANT for semi-structured
    created_date DATE,               -- Use DATE not TIMESTAMP when time unnecessary
    updated_timestamp TIMESTAMP      -- Use TIMESTAMP when time needed
);

-- Design queries to leverage clustering
SELECT *
FROM large_fact_table
WHERE transaction_date BETWEEN '2024-01-01' AND '2024-12-31'
    AND region = 'NORTHEAST';
-- Efficiently prunes micro-partitions based on clustering

-- Expensive aggregation as materialized view
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT 
    transaction_date,
    product_id,
    SUM(quantity) as total_quantity,
    SUM(amount) as total_amount
FROM large_fact_table
GROUP BY transaction_date, product_id;

-- Query materialized view (much faster)
SELECT * FROM mv_daily_sales
WHERE transaction_date = CURRENT_DATE();

-- Clone for development without storage cost
CREATE TABLE dev_orders CLONE prod_orders;

-- Clone at specific time
CREATE TABLE orders_yesterday CLONE orders
AT(TIMESTAMP => DATEADD(day, -1, CURRENT_TIMESTAMP()));

-- Clone for testing
CREATE TABLE test_customers CLONE customers;

-- Drop unused tables
DROP TABLE IF EXISTS old_staging_table;

-- Truncate instead of delete when possible
TRUNCATE TABLE staging_table;

-- Drop old temporary objects
SHOW OBJECTS IN SCHEMA staging;
-- Review and drop unnecessary objects

-- View table storage consumption
SELECT 
    table_name,
    active_bytes / (1024*1024*1024) as active_gb,
    time_travel_bytes / (1024*1024*1024) as time_travel_gb,
    failsafe_bytes / (1024*1024*1024) as failsafe_gb
FROM snowflake.account_usage.table_storage_metrics
WHERE table_catalog = 'YOUR_DATABASE'
ORDER BY active_bytes DESC
LIMIT 20;

-- Identify candidates for transient conversion
SELECT 
    table_name,
    failsafe_bytes / (1024*1024*1024) as failsafe_gb,
    failsafe_bytes * 0.25 as potential_monthly_savings
FROM snowflake.account_usage.table_storage_metrics
WHERE failsafe_bytes > 0
    AND table_name LIKE 'staging%' OR table_name LIKE 'temp%'
ORDER BY failsafe_bytes DESC;

-- Review and adjust retention for each table
SELECT 
    table_catalog,
    table_schema,
    table_name,
    retention_time,
    table_type
FROM snowflake.account_usage.tables
WHERE retention_time > 1
ORDER BY retention_time DESC;

-- Reduce retention where appropriate
ALTER TABLE non_critical_logs 
SET DATA_RETENTION_TIME_IN_DAYS = 1;

-- Monitor table growth
SELECT 
    table_name,
    row_count,
    bytes / (1024*1024*1024) as size_gb,
    ROW_NUMBER() OVER (ORDER BY bytes DESC) as size_rank
FROM snowflake.account_usage.tables
WHERE table_schema = 'PUBLIC'
    AND deleted IS NULL
ORDER BY bytes DESC
LIMIT 20;

-- Track query performance by table
SELECT 
    table_name,
    COUNT(*) as query_count,
    AVG(total_elapsed_time) / 1000 as avg_seconds,
    SUM(bytes_scanned) / (1024*1024*1024) as total_gb_scanned
FROM snowflake.account_usage.query_history q
JOIN snowflake.account_usage.access_history a 
    ON q.query_id = a.query_id
WHERE q.start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())
GROUP BY table_name
ORDER BY query_count DESC;

-- Create procedure for automatic staging cleanup
CREATE OR REPLACE PROCEDURE cleanup_old_staging()
RETURNS VARCHAR
LANGUAGE SQL
AS
$
BEGIN
    -- Truncate staging tables older than 7 days
    LET tables_cleaned := 0;
    
    FOR rec IN (
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_schema = 'STAGING'
            AND table_type = 'TRANSIENT'
    ) DO
        EXECUTE IMMEDIATE 'TRUNCATE TABLE staging.' || rec.table_name;
        tables_cleaned := tables_cleaned + 1;
    END FOR;
    
    RETURN 'Cleaned ' || tables_cleaned || ' staging tables';
END;
$;

-- Schedule cleanup task
CREATE OR REPLACE TASK cleanup_staging_task
    WAREHOUSE = maintenance_wh
    SCHEDULE = 'USING CRON 0 2 * * 0 America/Los_Angeles'  -- Weekly Sunday 2 AM
AS
    CALL cleanup_old_staging();

-- Enable task
ALTER TASK cleanup_staging_task RESUME;

-- Add meaningful comments to tables
COMMENT ON TABLE customers IS 
    'Customer master data with SCD Type 2 tracking. 
     Retention: 90 days Time Travel for audit compliance.
     Primary use: Customer analytics and reporting';

COMMENT ON TABLE staging_orders IS 
    'Transient staging for daily order imports.
     Data retained 1 day only - loaded to fact_orders nightly.
     Truncated after successful load';

-- Document columns
COMMENT ON COLUMN customers.customer_key IS 
    'Surrogate key - auto-incrementing integer';
    
COMMENT ON COLUMN customers.effective_date IS 
    'SCD Type 2 effective date - start of validity period';

-- Recent hot data (fast queries)
CREATE OR REPLACE TABLE sales_recent (
    sale_id BIGINT,
    sale_date DATE,
    customer_id INT,
    amount DECIMAL(10,2)
) CLUSTER BY (sale_date)
DATA_RETENTION_TIME_IN_DAYS = 30;

-- Load last 90 days
INSERT INTO sales_recent
SELECT * FROM source_sales
WHERE sale_date >= DATEADD(day, -90, CURRENT_DATE());

-- Historical cold data (cost-effective)
CREATE OR REPLACE EXTERNAL TABLE sales_historical (
    sale_id BIGINT AS (value:sale_id::BIGINT),
    sale_date DATE AS (value:sale_date::DATE),
    customer_id INT AS (value:customer_id::INT),
    amount DECIMAL(10,2) AS (value:amount::DECIMAL(10,2))
)
PARTITION BY (sale_year INT AS (YEAR(value:sale_date::DATE)))
LOCATION = @archive_stage/sales/
FILE_FORMAT = (TYPE = PARQUET)
AUTO_REFRESH = TRUE;

-- Unified access view
CREATE OR REPLACE VIEW sales_complete AS
    SELECT 'RECENT' as data_source, * FROM sales_recent
    UNION ALL
    SELECT 'HISTORICAL' as data_source, * FROM sales_historical;

-- Automatic archival task
CREATE OR REPLACE TASK archive_old_sales
    WAREHOUSE = maintenance_wh
    SCHEDULE = 'USING CRON 0 3 * * * America/Los_Angeles'
AS
BEGIN
    -- Export old data to external stage
    COPY INTO @archive_stage/sales/
    FROM (
        SELECT * FROM sales_recent 
        WHERE sale_date < DATEADD(day, -90, CURRENT_DATE())
    )
    FILE_FORMAT = (TYPE = PARQUET)
    PARTITION BY (YEAR(sale_date));
    
    -- Remove archived data from internal table
    DELETE FROM sales_recent
    WHERE sale_date < DATEADD(day, -90, CURRENT_DATE());
    
    -- Refresh external table metadata
    ALTER EXTERNAL TABLE sales_historical REFRESH;
END;

-- Transient staging for incoming data
CREATE OR REPLACE TRANSIENT TABLE stg_customer (
    customer_id VARCHAR(50),
    customer_name VARCHAR(200),
    segment VARCHAR(50),
    address VARCHAR(200),
    city VARCHAR(100),
    state VARCHAR(50),
    loaded_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);

-- Permanent dimension with history
CREATE OR REPLACE TABLE dim_customer (
    customer_key INT AUTOINCREMENT PRIMARY KEY,
    customer_id VARCHAR(50) NOT NULL,
    customer_name VARCHAR(200),
    segment VARCHAR(50),
    address VARCHAR(200),
    city VARCHAR(100),
    state VARCHAR(50),
    effective_date DATE NOT NULL,
    expiration_date DATE NOT NULL,
    current_flag CHAR(1) NOT NULL DEFAULT 'Y',
    created_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
) DATA_RETENTION_TIME_IN_DAYS = 90;

-- SCD Type 2 processing procedure
CREATE OR REPLACE PROCEDURE process_customer_scd()
RETURNS VARCHAR
LANGUAGE SQL
AS
$
BEGIN
    -- Expire changed records
    UPDATE dim_customer d
    SET expiration_date = CURRENT_DATE() - 1,
        current_flag = 'N'
    FROM stg_customer s
    WHERE d.customer_id = s.customer_id
        AND d.current_flag = 'Y'
        AND (
            d.segment != s.segment
            OR d.address != s.address
            OR d.city != s.city
            OR d.state != s.state
        );
    
    -- Insert new versions for changed records
    INSERT INTO dim_customer (
        customer_id, customer_name, segment, 
        address, city, state,
        effective_date, expiration_date, current_flag
    )
    SELECT 
        s.customer_id, s.customer_name, s.segment,
        s.address, s.city, s.state,
        CURRENT_DATE(), '9999-12-31', 'Y'
    FROM stg_customer s
    WHERE EXISTS (
        SELECT 1 FROM dim_customer d
        WHERE d.customer_id = s.customer_id
            AND d.current_flag = 'N'
            AND d.expiration_date = CURRENT_DATE() - 1
    );
    
    -- Insert new customers
    INSERT INTO dim_customer (
        customer_id, customer_name, segment,
        address, city, state,
        effective_date, expiration_date, current_flag
    )
    SELECT 
        s.customer_id, s.customer_name, s.segment,
        s.address, s.city, s.state,
        CURRENT_DATE(), '9999-12-31', 'Y'
    FROM stg_customer s
    WHERE NOT EXISTS (
        SELECT 1 FROM dim_customer d
        WHERE d.customer_id = s.customer_id
    );
    
    -- Clear staging
    TRUNCATE TABLE stg_customer;
    
    RETURN 'SCD processing completed successfully';
END;
$;

-- Hot tier: Very recent, frequently accessed (permanent, short retention)
CREATE OR REPLACE TABLE metrics_hot (
    metric_timestamp TIMESTAMP,
    metric_name VARCHAR(100),
    metric_value DECIMAL(15,4),
    tags VARIANT
) CLUSTER BY (DATE_TRUNC('hour', metric_timestamp))
DATA_RETENTION_TIME_IN_DAYS = 7;

-- Warm tier: Recent, occasionally accessed (transient)
CREATE OR REPLACE TRANSIENT TABLE metrics_warm (
    metric_date DATE,
    metric_hour INT,
    metric_name VARCHAR(100),
    metric_value DECIMAL(15,4),
    tags VARIANT
) CLUSTER BY (metric_date)
DATA_RETENTION_TIME_IN_DAYS = 1;

-- Cold tier: Historical, rarely accessed (external)
CREATE OR REPLACE EXTERNAL TABLE metrics_cold (
    metric_date DATE AS (value:metric_date::DATE),
    metric_name VARCHAR AS (value:metric_name::VARCHAR),
    metric_value DECIMAL(15,4) AS (value:metric_value::DECIMAL(15,4)),
    tags VARIANT AS (value:tags::VARIANT)
)
PARTITION BY (metric_year INT AS (YEAR(value:metric_date::DATE)))
LOCATION = @metrics_archive/
FILE_FORMAT = (TYPE = PARQUET);

-- Unified access view
CREATE OR REPLACE VIEW metrics_all AS
    SELECT 'HOT' as tier, 
           metric_timestamp, 
           metric_name, 
           metric_value, 
           tags
    FROM metrics_hot
    UNION ALL
    SELECT 'WARM' as tier,
           metric_date::TIMESTAMP as metric_timestamp,
           metric_name,
           metric_value,
           tags
    FROM metrics_warm
    UNION ALL
    SELECT 'COLD' as tier,
           metric_date::TIMESTAMP as metric_timestamp,
           metric_name,
           metric_value,
           tags
    FROM metrics_cold;

-- Automatic tiering task
CREATE OR REPLACE TASK tier_metrics
    WAREHOUSE = maintenance_wh
    SCHEDULE = 'USING CRON 0 1 * * * America/Los_Angeles'
AS
BEGIN
    -- Move hot to warm (aggregate hourly)
    INSERT INTO metrics_warm
    SELECT 
        DATE_TRUNC('day', metric_timestamp) as metric_date,
        HOUR(metric_timestamp) as metric_hour,
        metric_name,
        AVG(metric_value) as metric_value,
        OBJECT_AGG(tag_key, tag_value) as tags
    FROM metrics_hot
    WHERE metric_timestamp < DATEADD(day, -1, CURRENT_TIMESTAMP())
    GROUP BY metric_date, metric_hour, metric_name;
    
    -- Delete from hot
    DELETE FROM metrics_hot
    WHERE metric_timestamp < DATEADD(day, -1, CURRENT_TIMESTAMP());
    
    -- Move warm to cold (aggregate daily)
    COPY INTO @metrics_archive/
    FROM (
        SELECT 
            metric_date,
            metric_name,
            AVG(metric_value) as metric_value,
            tags
        FROM metrics_warm
        WHERE metric_date < DATEADD(day, -30, CURRENT_DATE())
        GROUP BY metric_date, metric_name, tags
    )
    FILE_FORMAT = (TYPE = PARQUET)
    PARTITION BY (YEAR(metric_date));
    
    -- Delete from warm
    DELETE FROM metrics_warm
    WHERE metric_date < DATEADD(day, -30, CURRENT_DATE());
    
    -- Refresh external table
    ALTER EXTERNAL TABLE metrics_cold REFRESH;
END;

-- WRONG: Permanent table for staging
CREATE TABLE staging_data (...);  -- Wastes money on Fail-safe

-- RIGHT: Transient table for staging
CREATE TRANSIENT TABLE staging_data (...);  -- No Fail-safe needed

-- WRONG: Permanent for derived tables
CREATE TABLE daily_aggregates AS 
SELECT ...;  -- Expensive for recreatable data

-- RIGHT: Transient for derived tables
CREATE TRANSIENT TABLE daily_aggregates AS
SELECT ...;  -- Can recreate from source if needed

-- WRONG: 90 days retention on high-volume staging
CREATE TABLE staging_events (...)
DATA_RETENTION_TIME_IN_DAYS = 90;  -- Expensive for staging

-- RIGHT: Minimal retention for staging
CREATE TRANSIENT TABLE staging_events (...)
DATA_RETENTION_TIME_IN_DAYS = 1;  -- Appropriate for staging

-- RIGHT: Targeted retention based on requirements
CREATE TABLE financial_transactions (...)
DATA_RETENTION_TIME_IN_DAYS = 90;  -- Justified for compliance

CREATE TABLE application_logs (...)
DATA_RETENTION_TIME_IN_DAYS = 7;  -- Sufficient for logs

-- WRONG: Load all historical data internally
CREATE TABLE sales_10_years (...);
COPY INTO sales_10_years FROM @historical_stage;
-- Expensive storage for rarely accessed data

-- RIGHT: Use external table for archives
CREATE EXTERNAL TABLE sales_historical (...)
LOCATION = @historical_stage/
FILE_FORMAT = (TYPE = PARQUET);
-- Query when needed, minimal storage cost

-- WRONG: No clustering on large fact table
CREATE TABLE fact_sales (
    sale_date DATE,
    product_id INT,
    customer_id INT,
    amount DECIMAL(10,2)
);  -- Poor query performance on date filters

-- RIGHT: Cluster by frequently filtered columns
CREATE TABLE fact_sales (
    sale_date DATE,
    product_id INT,
    customer_id INT,
    amount DECIMAL(10,2)
) CLUSTER BY (sale_date);  -- Efficient date filtering

-- Monitor clustering effectiveness
SELECT SYSTEM$CLUSTERING_INFORMATION('fact_sales');

-- Implement regular cleanup
CREATE OR REPLACE PROCEDURE cleanup_old_objects()
RETURNS VARCHAR
LANGUAGE SQL
AS
$
BEGIN
    -- Drop old temporary tables (shouldn't persist but cleanup anyway)
    FOR rec IN (
        SELECT table_name
        FROM information_schema.tables
        WHERE table_type = 'TEMPORARY'
            AND created < DATEADD(day, -1, CURRENT_TIMESTAMP())
    ) DO
        EXECUTE IMMEDIATE 'DROP TABLE IF EXISTS ' || rec.table_name;
    END FOR;
    
    -- Drop unused transient tables
    FOR rec IN (
        SELECT table_name
        FROM information_schema.tables t
        WHERE table_type = 'TRANSIENT'
            AND table_name LIKE 'tmp_%'
            AND NOT EXISTS (
                SELECT 1 
                FROM snowflake.account_usage.query_history q
                WHERE q.start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())
                    AND q.query_text ILIKE '%' || t.table_name || '%'
            )
    ) DO
        EXECUTE IMMEDIATE 'DROP TABLE IF EXISTS ' || rec.table_name;
    END FOR;
    
    RETURN 'Cleanup completed';
END;
$;