Snowflake Observability

Overview

Set up comprehensive observability for Snowflake using built-in ACCOUNT_USAGE views, Snowflake Alerts, and integration with external monitoring systems.

Prerequisites

• Role with access to SNOWFLAKE.ACCOUNT_USAGE (ACCOUNTADMIN or granted)
• Notification integration configured for alerts
• Optional: Prometheus/Grafana or Datadog for external dashboards

Instructions

Step 1: Key Monitoring Queries

-- === QUERY PERFORMANCE ===
-- Average query time by warehouse (last 7 days)
SELECT warehouse_name,
       COUNT(*) AS query_count,
       ROUND(AVG(total_elapsed_time) / 1000, 1) AS avg_seconds,
       ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY total_elapsed_time) / 1000, 1) AS p95_seconds,
       ROUND(PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY total_elapsed_time) / 1000, 1) AS p99_seconds
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(days, -7, CURRENT_TIMESTAMP())
  AND execution_status = 'SUCCESS'
  AND query_type = 'SELECT'
GROUP BY warehouse_name
ORDER BY avg_seconds DESC;

-- === ERROR RATE ===
-- Error rate by hour
SELECT DATE_TRUNC('hour', start_time) AS hour,
       COUNT_IF(execution_status = 'SUCCESS') AS success,
       COUNT_IF(execution_status = 'FAIL') AS failures,
       ROUND(COUNT_IF(execution_status = 'FAIL') * 100.0 /
             NULLIF(COUNT(*), 0), 2) AS error_rate_pct
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(hours, -24, CURRENT_TIMESTAMP())
GROUP BY hour
ORDER BY hour;

-- === CREDIT CONSUMPTION ===
-- Hourly credit usage
SELECT DATE_TRUNC('hour', start_time) AS hour,
       warehouse_name,
       SUM(credits_used) AS credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD(hours, -24, CURRENT_TIMESTAMP())
GROUP BY hour, warehouse_name
ORDER BY hour DESC, credits DESC;

-- === STORAGE GROWTH ===
-- Daily storage trend
SELECT usage_date,
       ROUND(storage_bytes / 1e12, 3) AS storage_tb,
       ROUND(stage_bytes / 1e12, 3) AS stage_tb,
       ROUND(failsafe_bytes / 1e12, 3) AS failsafe_tb
FROM SNOWFLAKE.ACCOUNT_USAGE.STORAGE_USAGE
WHERE usage_date >= DATEADD(days, -30, CURRENT_DATE())
ORDER BY usage_date;

Step 2: Built-in Snowflake Alerts

-- Alert: High error rate
CREATE OR REPLACE ALERT high_error_rate_alert
  WAREHOUSE = ANALYTICS_WH
  SCHEDULE = '15 MINUTE'
  IF (EXISTS (
    SELECT 1
    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
    WHERE start_time >= DATEADD(minutes, -15, CURRENT_TIMESTAMP())
    GROUP BY ALL
    HAVING COUNT_IF(execution_status = 'FAIL') * 100.0 / COUNT(*) > 5
  ))
  THEN
    CALL SYSTEM$SEND_EMAIL(
      'ops_notifications',
      'oncall@company.com',
      'Snowflake: Error rate > 5%'

Snowflake Performance Tuning

Overview

Optimize Snowflake query performance using clustering keys, materialized views, result caching, query profiling, and warehouse tuning.

Prerequisites

• Access to SNOWFLAKE.ACCOUNTUSAGE.QUERYHISTORY
• Understanding of micro-partitions and pruning
• Role with MONITOR privilege on warehouses

Instructions

Step 1: Identify Slow Queries

-- Top 20 slowest queries in last 24 hours
SELECT query_id, query_text, total_elapsed_time / 1000 AS seconds,
       bytes_scanned / 1e9 AS gb_scanned,
       partitions_scanned, partitions_total,
       ROUND(partitions_scanned / NULLIF(partitions_total, 0) * 100, 1) AS pct_scanned,
       warehouse_name, warehouse_size
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE execution_status = 'SUCCESS'
  AND start_time >= DATEADD(hours, -24, CURRENT_TIMESTAMP())
  AND query_type = 'SELECT'
ORDER BY total_elapsed_time DESC
LIMIT 20;

-- Queries scanning too many partitions (poor pruning)
SELECT query_id, query_text,
       partitions_scanned, partitions_total,
       bytes_scanned / 1e9 AS gb_scanned
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE partitions_scanned > partitions_total * 0.5
  AND partitions_total > 100
  AND start_time >= DATEADD(hours, -24, CURRENT_TIMESTAMP())
ORDER BY partitions_scanned DESC
LIMIT 10;

Step 2: Add Clustering Keys

-- Clustering improves pruning for large tables (> 1TB)
-- Choose columns used in WHERE and JOIN clauses

-- Cluster by date (most common filter)
ALTER TABLE orders CLUSTER BY (order_date);

-- Multi-column clustering
ALTER TABLE events CLUSTER BY (event_date, event_type);

-- Check clustering depth (lower = better)
SELECT SYSTEM$CLUSTERING_INFORMATION('orders', '(order_date)');

-- Monitor automatic reclustering
SELECT table_name, num_rows, bytes,
       SYSTEM$CLUSTERING_DEPTH('orders') AS clustering_depth
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'ORDERS';

Step 3: Use Materialized Views

-- Pre-compute expensive aggregations
CREATE OR REPLACE MATERIALIZED VIEW daily_revenue_mv
  CLUSTER BY (metric_date)
AS
  SELECT
    DATE_TRUNC('day', order_date) AS metric_date,
    COUNT(*) AS order_count,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value
  FROM orders
  GROUP BY DATE_TRUNC('day', order_date);

-- Query the MV instead of base table — automatic rewrite may also apply
SELECT * FROM daily_revenue_mv
WHERE metric_date >= DATEADD(days, -30, CURRENT_DATE());

-- Check MV freshness
SELECT name, is_secure, text, refresh_on
FROM INFORMATION_SCHEMA.MATERIALIZED_VIEWS
WHERE name = 'DAILY_REVENUE_MV';

Step 4: Leverage Result Caching

-- Result

Snowflake Policy & Guardrails

Overview

Automated policy enforcement and governance guardrails using Snowflake-native features: network rules, authentication policies, session policies, and object-level governance.

Instructions

Step 1: Network Rules and Policies

-- Network rules (more granular than legacy network policies)
CREATE OR REPLACE NETWORK RULE corp_vpn_rule
  TYPE = IPV4
  MODE = INGRESS
  VALUE_LIST = ('203.0.113.0/24', '198.51.100.0/24');

CREATE OR REPLACE NETWORK RULE cloud_services_rule
  TYPE = HOST_PORT
  MODE = EGRESS
  VALUE_LIST = ('api.company.com:443', 'events.company.com:443');

-- Create network policy using rules
CREATE OR REPLACE NETWORK POLICY prod_network_policy
  ALLOWED_NETWORK_RULE_LIST = (corp_vpn_rule)
  BLOCKED_NETWORK_RULE_LIST = ();

-- Apply at account level
ALTER ACCOUNT SET NETWORK_POLICY = prod_network_policy;

-- Or per-user (service accounts can have different rules)
ALTER USER svc_etl SET NETWORK_POLICY = prod_network_policy;

Step 2: Authentication Policies

-- Require MFA for interactive users
CREATE OR REPLACE AUTHENTICATION POLICY interactive_auth
  MFA_AUTHENTICATION_METHODS = ('TOTP')
  CLIENT_TYPES = ('SNOWFLAKE_UI', 'SNOWSQL')
  SECURITY_INTEGRATIONS = ('saml_sso');

-- Service accounts: key pair only, no password
CREATE OR REPLACE AUTHENTICATION POLICY service_auth
  AUTHENTICATION_METHODS = ('KEYPAIR')
  CLIENT_TYPES = ('SNOWFLAKE_DRIVER')
  MFA_AUTHENTICATION_METHODS = ();

-- Apply policies
ALTER USER analyst_user SET AUTHENTICATION POLICY = interactive_auth;
ALTER USER svc_etl SET AUTHENTICATION POLICY = service_auth;

Step 3: Session Policies

-- Enforce session timeout and idle limits
CREATE OR REPLACE SESSION POLICY prod_session_policy
  SESSION_IDLE_TIMEOUT_MINS = 30
  SESSION_UI_IDLE_TIMEOUT_MINS = 15;

-- Apply to account
ALTER ACCOUNT SET SESSION POLICY = prod_session_policy;

Step 4: Statement-Level Guardrails

-- Prevent runaway queries
ALTER WAREHOUSE PROD_WH SET
  STATEMENT_TIMEOUT_IN_SECONDS = 3600,          -- 1 hour max
  STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 600;     -- 10 min max queue

-- Prevent accidental full table operations
-- Use row access policies + stored procedures instead of raw access

-- Example: Safe delete procedure with audit
CREATE OR REPLACE PROCEDURE safe_delete(
  table_name VARCHAR, where_clause VARCHAR, max_rows INTEGER DEFAULT 10000
)
  RETURNS VARCHAR
  LANGUAGE SQL
AS
$$
BEGIN
  -- Count affected rows first
  LET count_sql VARCHAR := 'SELECT COUNT(*) FROM ' || :table_name || ' WHERE ' || :where_clause;
  LET affected_rows INTEGER;
  EXECUTE IMMEDIATE :count_sql INTO :affected_rows;

  IF (:affected_rows > :max_rows) THEN
    RETURN 'BLO

Snowflake Production Checklist

Overview

Complete checklist for deploying Snowflake data pipelines and integrations to production.

Prerequisites

• Staging environment validated
• Production Snowflake account configured
• Resource monitors in place
• Monitoring infrastructure ready

Pre-Deployment Checklist

Authentication & Secrets

• [ ] Service accounts use key pair auth (not password)
• [ ] Private keys stored in secret manager (not files/env vars)
• [ ] Key rotation procedure documented and tested
• [ ] Network policy applied to production account
• [ ] Connection parameters use production account identifier

Warehouse Configuration

• [ ] Production warehouses created with appropriate sizing
• [ ] Auto-suspend configured (60-300s based on workload)
• [ ] Auto-resume enabled
• [ ] Resource monitors with credit quotas and alerts
• [ ] Separate warehouses for ETL, analytics, and dashboard workloads

-- Production warehouse setup
CREATE WAREHOUSE IF NOT EXISTS PROD_ETL_WH
  WAREHOUSE_SIZE = 'LARGE'
  AUTO_SUSPEND = 120
  AUTO_RESUME = TRUE;

CREATE WAREHOUSE IF NOT EXISTS PROD_ANALYTICS_WH
  WAREHOUSE_SIZE = 'MEDIUM'
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 3
  SCALING_POLICY = 'STANDARD'
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE;

-- Resource monitor with alerts
CREATE OR REPLACE RESOURCE MONITOR prod_monitor
  WITH CREDIT_QUOTA = 1000
  FREQUENCY = MONTHLY
  START_TIMESTAMP = IMMEDIATELY
  TRIGGERS
    ON 75 PERCENT DO NOTIFY
    ON 90 PERCENT DO NOTIFY
    ON 100 PERCENT DO SUSPEND
    ON 110 PERCENT DO SUSPEND_IMMEDIATE;

ALTER WAREHOUSE PROD_ETL_WH SET RESOURCE_MONITOR = prod_monitor;
ALTER WAREHOUSE PROD_ANALYTICS_WH SET RESOURCE_MONITOR = prod_monitor;

Data Pipeline Readiness

• [ ] All tasks resumed and running on schedule
• [ ] Streams not stale (check with SHOW STREAMS)
• [ ] Snowpipe notifications configured and verified
• [ ] COPY INTO error handling set (ONERROR = 'CONTINUE' or 'SKIPFILE')
• [ ] Data retention set appropriately (DATARETENTIONTIMEINDAYS)

Query & Performance

• [ ] Critical queries tested at production data volume
• [ ] Clustering keys set on large tables (>1TB)
• [ ] Statement timeout configured per warehouse
• [ ] Result caching enabled (USECACHEDRESULT = TRUE)

-- Set statement timeout for production
ALTER WAREHOUSE PROD_ETL_WH SET STATEMENT_TIMEOUT_IN_SECONDS = 3600;
ALTER WAREHOUSE PROD_ANALYTICS_WH SET STATEMENT_TIMEOUT_IN_SECONDS = 600;

-- Enable query result caching (default is ON)
ALTER ACCOUNT SET USE_CACHED_RESULT = TRUE;
<

Snowflake Reference Architecture

Overview

Production-ready Snowflake architecture using the medallion pattern (bronze/silver/gold), role-based access, and workload-isolated warehouses.

Architecture Overview

                    ┌──────────────────────┐
                    │   Data Sources        │
                    │ (S3, APIs, DBs, SaaS) │
                    └──────────┬───────────┘
                               │
                    ┌──────────▼───────────┐
                    │   BRONZE (Raw)        │
                    │   Snowpipe / COPY     │
                    │   VARIANT columns     │
                    └──────────┬───────────┘
                               │ Streams + Tasks
                    ┌──────────▼───────────┐
                    │   SILVER (Cleansed)   │
                    │   Typed columns       │
                    │   Deduped, validated   │
                    └──────────┬───────────┘
                               │ Dynamic Tables
                    ┌──────────▼───────────┐
                    │   GOLD (Business)     │
                    │   Aggregated          │
                    │   Analytics-ready     │
                    └──────────┬───────────┘
                               │
                    ┌──────────▼───────────┐
                    │   Consumers           │
                    │   BI tools, APIs,     │
                    │   Data Sharing        │
                    └──────────────────────┘

Database Layout

-- One database per environment, schemas per layer
CREATE DATABASE PROD_DW;

-- Bronze: Raw ingested data (append-only, VARIANT columns)
CREATE SCHEMA PROD_DW.BRONZE;

-- Silver: Cleansed, typed, deduplicated
CREATE SCHEMA PROD_DW.SILVER;

-- Gold: Business-level aggregations and dimensions
CREATE SCHEMA PROD_DW.GOLD;

-- Staging: Temporary tables for ETL processing
CREATE SCHEMA PROD_DW.STAGING;

-- Utility: Stored procedures, UDFs, file formats
CREATE SCHEMA PROD_DW.UTILITY;

Instructions

Step 1: Bronze Layer (Raw Ingestion)

-- Store raw data as VARIANT for schema-on-read
CREATE TABLE PROD_DW.BRONZE.RAW_EVENTS (
    ingestion_time TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
    source_file VARCHAR(500),
    raw_data VARIANT
);

-- File format for JSON ingestion
CREATE FILE FORMAT PROD_DW.UTILITY.JSON_INGEST
  TYPE = 'JSON' STRIP_OUTER_ARRAY = TRUE;

-- Stage for S3 source
CREATE STAGE PROD_DW.UTILITY.S3_EVENTS_STAGE
  STORAGE_INTEGRATION = s3_integration
  URL = 's3://data-lake/events/'
  FILE_FORMAT = PROD_DW.UTILITY.JSON_INGEST;

-- Snowpipe for continuous ingestion
CREATE PIPE PROD_DW.BRONZE.EVENTS_PIPE
  AUTO_INGEST = TRUE
AS
  COPY INTO PROD_DW.BRONZE.RAW_EVENTS (source_file, raw_data)
  FROM (SELECT METADATA$FILENAME, $1 FROM @PROD_DW.UTILITY.S3_EVENTS_STAGE);

Step 2: Silver Layer (Cleansing)