-- Example 1: Create external volume
-- External volumes map to cloud storage locations with IAM credentials
-- Parameters:
--   STORAGE_PROVIDER: 'S3', 'AZURE', or 'GCS'
--   STORAGE_AWS_ROLE_ARN: IAM role ARN for S3 access
--   STORAGE_BASE_LOCATIONS: Array of S3 paths (one per Snowflake account)
--   ENCRYPTION_TYPE: Server-side encryption type
CREATE EXTERNAL VOLUME my_iceberg_volume
    STORAGE_PROVIDER = 'S3'
    STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/SnowflakeAccessRole'
    STORAGE_BASE_LOCATIONS = ('s3://my-bucket/iceberg-data')
    ENCRYPTION_TYPE = 'AES_SSE_S3'   -- Server-side encryption
    COMMENT = 'External volume for Iceberg tables';

-- Verify external volume
DESC EXTERNAL VOLUME my_iceberg_volume;
SHOW EXTERNAL VOLUMES;

-- Example 2: Create Iceberg table
-- Parameters:
--   CATALOG: 'snowflake' (native Iceberg catalog)
--   EXTERNAL_VOLUME: References the external volume created above
--   BASE_LOCATION: Subpath within the volume for this table's data
--   STORAGE_SERIALIZATION_FORMAT: 'PARQUET' (default) or 'ORC'
--   STORAGE_COMPRESSION_TYPE: 'ZSTD' (best compression) or 'SNAPPY' (fast)
CREATE ICEBERG TABLE sales_iceberg (
    order_id INTEGER,
    customer_id INTEGER,
    order_date DATE,
    amount DECIMAL(10,2),
    region VARCHAR(10)
)
CATALOG = 'snowflake'                          -- Snowflake-managed Iceberg catalog
EXTERNAL_VOLUME = 'my_iceberg_volume'          -- Cloud storage volume
BASE_LOCATION = 'sales'                        -- Subpath for this table
STORAGE_SERIALIZATION_FORMAT = 'PARQUET'       -- Data file format
STORAGE_COMPRESSION_TYPE = 'ZSTD'              -- Compression algorithm
COMMENT = 'Sales data in Iceberg format';

-- Example 3: Insert data into Iceberg table
-- Iceberg tables support standard DML: INSERT, UPDATE, DELETE, MERGE
INSERT INTO sales_iceberg 
SELECT * FROM raw_sales_data;

-- Example 4: Query Iceberg table with partition pruning
-- Iceberg metadata enables automatic partition pruning
SELECT 
    region,
    COUNT(*) as order_count,
    SUM(amount) as total_amount
FROM sales_iceberg
WHERE order_date >= '2024-01-01'   -- Partition pruning on date column
GROUP BY region;

-- Example 5: UPDATE operations on Iceberg
-- Iceberg supports row-level updates (unlike external tables)
UPDATE sales_iceberg 
SET amount = amount * 1.10    -- 10% price increase
WHERE region = 'US' AND order_date >= '2024-06-01';

-- Example 6: DELETE operations on Iceberg
DELETE FROM sales_iceberg 
WHERE order_date < '2023-01-01';   -- Remove old data

-- Example 7: MERGE (upsert) operations on Iceberg
MERGE INTO sales_iceberg t
USING staging_sales s
ON t.order_id = s.order_id
WHEN MATCHED THEN
    UPDATE SET 
        amount = s.amount,
        region = s.region,
        updated_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN
    INSERT (order_id, customer_id, order_date, amount, region)
    VALUES (s.order_id, s.customer_id, s.order_date, s.amount, s.region);

-- Example 8: Query with time travel
-- Iceberg supports time travel via snapshot IDs or timestamps
-- AT (TIMESTAMP): Point-in-time query
-- AT (OFFSET): N seconds ago
-- AT (STATEMENT): Before/after a specific query
SELECT * FROM sales_iceberg
AT (TIMESTAMP => '2024-01-15 10:00:00'::TIMESTAMP_TZ)
WHERE region = 'US';

-- Example 9: Time travel with snapshot offset
-- Query state 1 hour ago
SELECT * FROM sales_iceberg
AT (OFFSET => -3600)    -- -3600 seconds = 1 hour ago
WHERE region = 'US';

-- Example 10: Schema evolution
-- Add new column (existing data gets NULL for new column)
ALTER TABLE sales_iceberg ADD COLUMN country VARCHAR(10);

-- Rename column (metadata-only operation, no data rewrite)
ALTER TABLE sales_iceberg RENAME COLUMN region TO sales_region;

-- Drop column (metadata-only operation)
ALTER TABLE sales_iceberg DROP COLUMN deprecated_column;

-- Change column data type (if compatible)
ALTER TABLE sales_iceberg ALTER COLUMN amount SET DATA TYPE DECIMAL(12,2);

-- Example 11: Create Iceberg table from existing data (CTAS)
CREATE OR REPLACE ICEBERG TABLE sales_iceberg_v2
CATALOG = 'snowflake'
EXTERNAL_VOLUME = 'my_iceberg_volume'
BASE_LOCATION = 'sales_v2'
STORAGE_SERIALIZATION_FORMAT = 'PARQUET'
AS SELECT 
    order_id,
    customer_id,
    order_date,
    amount,
    region,
    CURRENT_TIMESTAMP() AS created_at
FROM raw_sales_data
WHERE order_date >= '2024-01-01';

-- Example 12: Create hybrid table
-- Hybrid tables support both OLAP (analytics) and OLTP (transactional) workloads
-- PRIMARY KEY: Enforces uniqueness and enables fast single-row lookups
-- CLUSTER BY: Optimizes data layout for frequent query patterns
CREATE HYBRID TABLE orders_hybrid (
    order_id INTEGER PRIMARY KEY,            -- Unique constraint enforced
    customer_id INTEGER NOT NULL,            -- Not null constraint
    order_date DATE NOT NULL,
    amount DECIMAL(10,2),
    status VARCHAR(20) DEFAULT 'PENDING',   -- Default value for new rows
    created_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
)
CLUSTER BY (order_date, status)             -- Cluster key for query optimization
COMMENT = 'Hybrid table for order management';

-- Example 13: Single-row operations on hybrid table
-- Insert: Fast primary key lookup, ACID transaction
INSERT INTO orders_hybrid (order_id, customer_id, order_date, amount, status)
VALUES (1, 101, '2024-01-15', 99.99, 'PENDING');

-- Update: Primary key lookup (no full table scan)
UPDATE orders_hybrid 
SET status = 'SHIPPED', updated_at = CURRENT_TIMESTAMP()
WHERE order_id = 1;

-- Delete: Primary key lookup
DELETE FROM orders_hybrid WHERE order_id = 1;

-- Example 14: Upsert operation on hybrid table
MERGE INTO orders_hybrid t
USING staging_orders s
ON t.order_id = s.order_id
WHEN MATCHED THEN
    UPDATE SET 
        amount = s.amount,
        status = s.status,
        updated_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN
    INSERT (order_id, customer_id, order_date, amount, status)
    VALUES (s.order_id, s.customer_id, s.order_date, s.amount, s.status);

-- Example 15: Bulk operations on hybrid table
-- Hybrid tables support efficient bulk operations alongside single-row DML
INSERT INTO orders_hybrid
SELECT * FROM staging_orders_bulk;

-- Bulk update
UPDATE orders_hybrid 
SET status = 'ARCHIVED'
WHERE order_date < '2023-01-01';

-- Example 16: Monitor Iceberg table metadata
SELECT *
FROM TABLE(INFORMATION_SCHEMA.ICEBERG_TABLES())
WHERE table_name = 'SALES_ICEBERG';

-- Example 17: Query Iceberg table metadata
-- View snapshots, partitions, and data files
SELECT * FROM TABLE(INFORMATION_SCHEMA.ICEBERG_TABLES()) 
WHERE table_name = 'SALES_ICEBERG';

-- Example 18: Monitor hybrid table performance
-- Check primary key constraint violations
SELECT * FROM TABLE(INFORMATION_SCHEMA.TABLE_CONSTRAINTS())
WHERE table_name = 'ORDERS_HYBRID' 
AND constraint_type = 'PRIMARY KEY';

-- Example 19: View Iceberg table storage statistics
SELECT 
    table_name,
    storage_location,
    total_rows,
    total_bytes
FROM TABLE(INFORMATION_SCHEMA.ICEBERG_TABLES())
WHERE table_name = 'SALES_ICEBERG';

-- Example 20: Compare Iceberg vs regular table performance
EXPLAIN SELECT * FROM sales_iceberg WHERE order_date = '2024-01-15';
EXPLAIN SELECT * FROM regular_sales WHERE order_date = '2024-01-15';