Snowflake Data Cloud Platform Fundamentals

Snowflake: The Elastic Data Cloud

Snowflake is a cloud-native data platform that separates compute from storage, enabling independent scaling of each resource layer.

Why Snowflake Matters

Traditional Warehouses:

• Require vertical scaling (bigger machines)
• Capacity planning is complex
• Limited elasticity

Snowflake's Innovation:

• Horizontal elastic scaling through virtual warehouses
• Pause, resume, and resize in seconds
• Near-infinite elasticity
• Eliminates capacity planning

Key Insight: Snowflake fundamentally changes how data teams manage analytical workloads by introducing horizontal elastic scaling through virtual warehouses that can be paused, resumed, and resized in seconds.

Architecture Overview

Snowflake 3-Layer Architecture

Compute-Storage Separation

Compute-Storage Separation

-- Create a virtual warehouse with auto-suspend
CREATE WAREHOUSE analytics_wh
    WAREHOUSE_SIZE = 'medium'
    AUTO_SUSPEND = 300          -- Suspend after 5 minutes of inactivity
    AUTO_RESUME = TRUE          -- Resume automatically on query
    MIN_CLUSTER_COUNT = 1
    MAX_CLUSTER_COUNT = 4       -- Multi-cluster scaling
    SCALING_POLICY = 'ECONOMY'  -- Cost-optimized scaling
    INITIALLY_SUSPENDED = FALSE;

-- Create warehouse for ad-hoc queries (smaller, faster)
CREATE WAREHOUSE adhoc_wh
    WAREHOUSE_SIZE = 'x-small'
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE
    MIN_CLUSTER_COUNT = 1
    MAX_CLUSTER_COUNT = 1;

-- Monitor warehouse utilization
SELECT
    warehouse_name,
    warehouse_size,
    SUM(credits_used)           AS total_credits,
    SUM(query_count)            AS total_queries,
    ROUND(SUM(credits_used) / NULLIF(SUM(query_count), 0), 4) AS credits_per_query,
    AVG(avg_queue_time_ms)      AS avg_queue_ms,
    AVG(avg_execution_time_ms)  AS avg_exec_ms
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_date >= DATEADD(day, -30, CURRENT_DATE())
GROUP BY warehouse_name, warehouse_size
ORDER BY total_credits DESC;

Micro-Partitions and Pruning

-- Query pruning metadata
SELECT
    partition_id,
    row_count,
    row_start_byte,
    row_end_byte,
    column_name,
    min_value,
    max_value
FROM TABLE(GET_METADATA_FOR_POSITIONAL_BLOCK('fact_sales'))
LIMIT 50;

-- Use clustering key for better pruning
CREATE TABLE fact_sales (
    sale_key        BIGINT,
    customer_key    INT,
    product_key     INT,
    sale_date       DATE,
    net_amount      DECIMAL(14,2)
)
CLUSTER BY (sale_date);  -- Micro-partitions sorted by sale_date

-- Check clustering depth and overlap
SELECT
    system$clustering_depth('fact_sales', '(sale_date)'),
    system$clustering_ratio('fact_sales', '(sale_date)'),
    system$clustering_information('fact_sales', '(sale_date)');

Time Travel and Zero-Copy Cloning

-- Query data as it was 2 hours ago
SELECT * FROM fact_sales AT (OFFSET => -7200);

-- Query data at a specific timestamp
SELECT * FROM fact_sales AT (TIMESTAMP => '2025-01-15 10:30:00'::TIMESTAMP_TZ);

-- Query data before a specific statement
SELECT * FROM fact_sales BEFORE (STATEMENT => 'abc123');

-- Zero-Copy Clone (instant, metadata-only)
CREATE TABLE fact_sales_staging CLONE fact_sales;

-- Clone with data freshness control
CREATE TABLE fact_sales_jan CLONE fact_sales
    AT (TIMESTAMP => '2025-01-31 23:59:59'::TIMESTAMP_TZ);

-- Drop and restore using Time Travel
DROP TABLE fact_sales;
UNDROP TABLE fact_sales;

-- Cross-region clone
CREATE TABLE fact_sales_dr CLONE snowflake_db.fact_sales
    AT (TIMESTAMP => '2025-01-15 00:00:00'::TIMESTAMP_TZ);

Data Sharing and Snowflake Marketplace

-- Create a share
CREATE SHARE company_analytics_share;
GRANT USAGE ON DATABASE analytics_db TO SHARE company_analytics_share;
GRANT USAGE ON SCHEMA analytics_db.public TO SHARE company_analytics_share;
GRANT SELECT ON TABLE analytics_db.public.fact_daily_metrics TO SHARE company_analytics_share;

-- Add consumer account
ALTER SHARE company_analytics_share ADD ACCOUNTS = consumer_account_id;

-- Create reader account for external sharing
CREATE ACCOUNT reader_acct
    ADMIN_NAME = 'admin'
    ADMIN_PASSWORD = 'secure_password'
    EMAIL = 'reader@company.com'
    TYPE = READER;

-- List shares available to this account
SHOW SHARES;
SELECT * FROM INFORMATION_SCHEMA.SHARES;

Multi-Cluster Auto-Scaling

-- Monitor multi-cluster scaling events
SELECT
    warehouse_name,
    start_time,
    end_time,
    cluster_number,
    credits_used,
    queries_executed
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_LOAD_HISTORY
WHERE warehouse_name = 'analytics_wh'
  AND start_date >= DATEADD(day, -7, CURRENT_DATE())
ORDER BY start_time DESC;

-- Query to check if scaling is needed
SELECT
    warehouse_name,
    AVG(available) AS avg_available,
    AVG(queued)    AS avg_queued,
    CASE
        WHEN AVG(queued) > 8 THEN 'SCALE UP NEEDED'
        WHEN AVG(available) > 12 THEN 'SCALE DOWN OPPORTUNITY'
        ELSE 'ADEQUATE'
    END AS scaling_recommendation
FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_LOAD_HISTORY(
    TIME_RANGE_START => DATEADD(day, -7, CURRENT_DATE()),
    TIME_RANGE_END => CURRENT_DATE()
))
GROUP BY warehouse_name;

Snowflake Editions and Pricing

-- Monitor credit consumption by warehouse
SELECT
    warehouse_name,
    SUM(credits_used) AS total_credits,
    SUM(credits_used) * 3 AS estimated_cost_usd,
    SUM(query_count) AS total_queries,
    ROUND(SUM(credits_used) * 3.0 / NULLIF(SUM(query_count), 0), 4) AS cost_per_query
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_date >= DATEADD(day, -30, CURRENT_DATE())
GROUP BY warehouse_name
ORDER BY total_credits DESC;

-- Monitor storage consumption
SELECT
    table_schema,
    SUM(active_bytes) / 1024 / 1024 / 1024 AS active_storage_gb,
    SUM(time_travel_bytes) / 1024 / 1024 / 1024 AS time_travel_gb,
    SUM(fail_safe_bytes) / 1024 / 1024 / 1024 AS fail_safe_gb
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS
WHERE deleted = 0
GROUP BY table_schema
ORDER BY active_storage_gb DESC;

-- Cost optimization: Identify expensive queries
SELECT
    query_id,
    query_text,
    user_name,
    warehouse_name,
    credits_used,
    ROUND(credits_used * 3, 4) AS cost_usd,
    bytes_scanned / 1024 / 1024 / 1024 AS gb_scanned,
    execution_time_ms
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(day, -7, CURRENT_DATE())
  AND credits_used > 1
ORDER BY credits_used DESC
LIMIT 20;

Key Concepts Summary

Performance Metrics

10 Best Practices

1. Right-size virtual warehouses — start small, monitor queue times, scale only when needed
2. Use AUTO_SUSPEND aggressively — set 60-300 seconds to avoid paying for idle compute
3. Implement clustering keys on frequently filtered columns to maximize micro-partition pruning
4. Leverage result caching — identical queries within 24 hours return cached results instantly
5. Use Zero-Copy Clones for dev/test instead of full data copies
6. Enable Time Travel retention at the Enterprise level for compliance and debugging
7. Monitor warehouse utilization via ACCOUNT_USAGE views and adjust sizes weekly
8. Use Snowpipe for continuous ingestion rather than bulk COPY for near-real-time data
9. Separate warehouses by workload — ETL, ad-hoc, and BI should use dedicated warehouses
10. Set up alerts for queue depth > 10 and credit consumption anomalies

See Also

• Snowflake Advanced — Streams, Tasks, Dynamic Tables, and Snowpipe
• Data Warehouse Concepts — Star schema, snowflake schema, and dimensional modeling
• BigQuery Fundamentals — Google's serverless data warehouse alternative
• Redshift Fundamentals — Amazon's MPP data warehouse
• dbt Fundamentals — SQL-first transformation layer
• Cost Optimization — Managing data platform expenses across warehouses