Snowflake Common Errors

Overview

Quick reference for the most common Snowflake error codes, SQL compilation errors, and driver issues with real solutions.

Error Reference

002003 (42S02): Object Does Not Exist

SQL compilation error: Object 'MY_DB.MY_SCHEMA.USERS' does not exist or not authorized.

Causes: Table doesn't exist, wrong database/schema context, or role lacks privileges.

Solutions:

-- Check current context
SELECT CURRENT_DATABASE(), CURRENT_SCHEMA(), CURRENT_ROLE();

-- Verify object exists
SHOW TABLES LIKE 'USERS' IN SCHEMA MY_DB.MY_SCHEMA;

-- Grant access if needed
GRANT SELECT ON TABLE MY_DB.MY_SCHEMA.USERS TO ROLE MY_ROLE;

-- Use fully-qualified names to avoid context issues
SELECT * FROM MY_DB.MY_SCHEMA.USERS;

000606: No Active Warehouse

SQL execution error: No active warehouse selected in the current session.

Solutions:

-- Set warehouse for session
USE WAREHOUSE COMPUTE_WH;

-- Or set in connection config
-- warehouse: 'COMPUTE_WH' in createConnection()

-- Check warehouse state
SHOW WAREHOUSES LIKE 'COMPUTE_WH';
-- If SUSPENDED, it auto-resumes if AUTO_RESUME = TRUE

390100: Incorrect Username or Password

Incorrect username or password was specified.

Solutions:

# Verify credentials are set
echo $SNOWFLAKE_ACCOUNT  # Should be 'orgname-accountname'
echo $SNOWFLAKE_USER

# Test with SnowSQL
snowsql -a $SNOWFLAKE_ACCOUNT -u $SNOWFLAKE_USER

# Check account format — common mistake:
# Wrong: myaccount.us-east-1.snowflakecomputing.com
# Right: myorg-myaccount

390144: JWT Token Invalid (Key Pair Auth)

JWT token is invalid.

Solutions:

# Verify public key is assigned
# Run in Snowflake:
# DESC USER my_user;
# Check RSA_PUBLIC_KEY column

# Regenerate if needed
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

# Re-assign (remove headers/newlines from pub key first)
# ALTER USER my_user SET RSA_PUBLIC_KEY='MIIBIj...';

001003: SQL Compilation Error

SQL compilation error: syntax error line X at position Y unexpected 'TOKEN'.

Common causes:

-- Missing semicolons in multi-statement mode
-- Wrong: SELECT 1 SELECT 2
-- Right: SELECT 1; SELECT 2;

-- Reserved word used as identifier
-- Wrong: SELECT order FROM orders
-- Right: SELECT "order" FROM orders

-- Wrong function syntax
-- Wrong: DATEADD('day', 1, col)
-- Right: DATEADD(day, 1, col)  -- no quo

Snowflake Core Workflow A — Data Loading

Overview

Primary data loading workflow: stages, file formats, COPY INTO, and Snowpipe for continuous ingestion.

Prerequisites

• Completed snowflake-install-auth setup
• Target table created in Snowflake
• Source data in S3, GCS, Azure Blob, or local files
• Role with CREATE STAGE and USAGE on warehouse

Instructions

Step 1: Create a File Format

-- CSV format
CREATE OR REPLACE FILE FORMAT my_csv_format
  TYPE = 'CSV'
  FIELD_DELIMITER = ','
  SKIP_HEADER = 1
  NULL_IF = ('NULL', 'null', '')
  EMPTY_FIELD_AS_NULL = TRUE
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE;

-- JSON format (for semi-structured data)
CREATE OR REPLACE FILE FORMAT my_json_format
  TYPE = 'JSON'
  STRIP_OUTER_ARRAY = TRUE
  IGNORE_UTF8_ERRORS = TRUE;

-- Parquet format
CREATE OR REPLACE FILE FORMAT my_parquet_format
  TYPE = 'PARQUET'
  SNAPPY_COMPRESSION = TRUE;

Step 2: Create a Stage

-- External stage (S3)
CREATE OR REPLACE STAGE my_s3_stage
  STORAGE_INTEGRATION = my_s3_integration
  URL = 's3://my-bucket/data/'
  FILE_FORMAT = my_csv_format;

-- External stage (GCS)
CREATE OR REPLACE STAGE my_gcs_stage
  STORAGE_INTEGRATION = my_gcs_integration
  URL = 'gcs://my-bucket/data/'
  FILE_FORMAT = my_csv_format;

-- Internal stage (Snowflake-managed storage)
CREATE OR REPLACE STAGE my_internal_stage
  FILE_FORMAT = my_csv_format;

-- List files in stage
LIST @my_s3_stage;

Step 3: Upload Files to Internal Stage

# Using SnowSQL PUT command
snowsql -c prod -q "PUT file:///tmp/data/*.csv @my_internal_stage AUTO_COMPRESS=TRUE"

# Using Python connector
cursor.execute("PUT file:///tmp/data/users.csv @my_internal_stage AUTO_COMPRESS=TRUE")

Step 4: Load Data with COPY INTO

-- Basic COPY INTO from stage
COPY INTO my_db.my_schema.users
  FROM @my_s3_stage/users/
  FILE_FORMAT = my_csv_format
  ON_ERROR = 'CONTINUE'          -- Skip bad rows
  PURGE = TRUE;                  -- Delete files after load

-- COPY with column mapping
COPY INTO my_db.my_schema.orders (order_id, customer_id, amount, order_date)
  FROM (
    SELECT $1, $2, $3::FLOAT, $4::TIMESTAMP_NTZ
    FROM @my_s3_stage/orders/
  )
  FILE_FORMAT = my_csv_format;

-- Load JSON into VARIANT column
COPY INTO my_db.my_schema.raw_events
  FROM @my_s3_stage/events/
  FILE_FORMAT = my_json_format
  MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

-- Check COPY history
SELECT * FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(
  TABLE_NAME => 'USERS',
  START_TIME => DATEADD(hours, -24, CURRENT_TIMESTAM

Snowflake Core Workflow B — Data Transformation

Overview

Build ELT pipelines using streams (change data capture), tasks (scheduling), and dynamic tables (declarative transforms).

Prerequisites

• Data loaded into Snowflake (via snowflake-core-workflow-a)
• Understanding of ELT vs ETL patterns
• Role with CREATE TASK, CREATE STREAM privileges

Instructions

Step 1: Create a Stream for Change Data Capture

-- Track changes on the raw orders table
CREATE OR REPLACE STREAM orders_stream ON TABLE raw_orders
  APPEND_ONLY = FALSE;

-- Append-only stream (lighter weight, inserts only)
CREATE OR REPLACE STREAM events_stream ON TABLE raw_events
  APPEND_ONLY = TRUE;

-- Check what's changed since last consumption
SELECT * FROM orders_stream;
-- METADATA$ACTION = 'INSERT' | 'DELETE'
-- METADATA$ISUPDATE = TRUE if row is part of an UPDATE
-- METADATA$ROW_ID = unique row identifier

Step 2: Create a Task to Process Stream Data

-- Transform task runs when stream has data
CREATE OR REPLACE TASK transform_orders
  WAREHOUSE = TRANSFORM_WH
  SCHEDULE = '5 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('orders_stream')
AS
  MERGE INTO dim_orders AS target
  USING (
    SELECT
      order_id,
      customer_id,
      amount::DECIMAL(12,2) AS amount,
      order_date::TIMESTAMP_NTZ AS order_date,
      CASE
        WHEN amount >= 1000 THEN 'high_value'
        WHEN amount >= 100 THEN 'medium_value'
        ELSE 'standard'
      END AS order_tier,
      CURRENT_TIMESTAMP() AS processed_at
    FROM orders_stream
    WHERE METADATA$ACTION = 'INSERT'
  ) AS source
  ON target.order_id = source.order_id
  WHEN MATCHED THEN UPDATE SET
    target.amount = source.amount,
    target.order_tier = source.order_tier,
    target.processed_at = source.processed_at
  WHEN NOT MATCHED THEN INSERT
    (order_id, customer_id, amount, order_date, order_tier, processed_at)
  VALUES
    (source.order_id, source.customer_id, source.amount,
     source.order_date, source.order_tier, source.processed_at);

-- Enable the task
ALTER TASK transform_orders RESUME;

Step 3: Build a Task DAG (Directed Acyclic Graph)

-- Root task: aggregate daily metrics
CREATE OR REPLACE TASK daily_metrics_root
  WAREHOUSE = TRANSFORM_WH
  SCHEDULE = 'USING CRON 0 6 * * * America/New_York'
AS
  INSERT INTO daily_order_metrics
  SELECT
    CURRENT_DATE() - 1 AS metric_date,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value,
    COUNT(DISTINCT customer_id) AS unique_customers
  FROM dim_orders
  WHERE order_date >= CURRENT_DATE() - 1
    AND order_date < CURRENT_DATE();

-- Child task: runs after root completes
CREATE OR REPLACE TASK update_cust

Snowflake Cost Tuning

Overview

Optimize Snowflake costs through resource monitors, warehouse right-sizing, auto-suspend tuning, and credit consumption analysis.

Snowflake Pricing Model

Credit rates by warehouse size:

Instructions

Step 1: Analyze Current Credit Consumption

-- Credits by warehouse (last 30 days)
SELECT warehouse_name,
       SUM(credits_used) AS total_credits,
       SUM(credits_used_compute) AS compute_credits,
       SUM(credits_used_cloud_services) AS cloud_credits,
       ROUND(SUM(credits_used) * 3.0, 2) AS est_cost_usd  -- ~$3/credit standard
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD(days, -30, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
ORDER BY total_credits DESC;

-- Daily credit trend
SELECT DATE_TRUNC('day', start_time) AS day,
       SUM(credits_used) AS credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD(days, -30, CURRENT_TIMESTAMP())
GROUP BY day
ORDER BY day;

-- Idle warehouse time (credits wasted while no queries running)
SELECT warehouse_name,
       SUM(credits_used) AS total_credits,
       COUNT(DISTINCT query_id) AS queries,
       CASE WHEN COUNT(DISTINCT query_id) = 0 THEN SUM(credits_used)
            ELSE 0 END AS idle_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY w
LEFT JOIN SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
  ON w.warehouse_name = q.warehouse_name
  AND DATE_TRUNC('hour', w.start_time) = DATE_TRUNC('hour', q.start_time)
WHERE w.start_time >= DATEADD(days, -7, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
ORDER BY idle_credits DESC;

Step 2: Set Up Resource Monitors

-- Account-level resource monitor
CREATE OR REPLACE RESOURCE MONITOR account_monthly
  WITH CREDIT_QUOTA = 5000
  FREQUENCY = MONTHLY
  START_TIMESTAMP = IMMEDIATELY
  TRIGGERS
    ON 50 PERCE

Snowflake Data Handling

Overview

Implement data governance in Snowflake using column-level masking policies, row access policies, object tagging, and data classification for GDPR/CCPA compliance.

Prerequisites

• Enterprise Edition or higher (for masking and row access policies)
• SECURITYADMIN or ACCOUNTADMIN role
• Understanding of GDPR/CCPA data subject rights

Instructions

Step 1: Data Classification with Tags

-- Create tag taxonomy
CREATE TAG IF NOT EXISTS pii_type
  ALLOWED_VALUES 'email', 'phone', 'ssn', 'name', 'address';

CREATE TAG IF NOT EXISTS data_sensitivity
  ALLOWED_VALUES 'public', 'internal', 'confidential', 'restricted';

-- Apply tags to columns
ALTER TABLE users MODIFY COLUMN email SET TAG pii_type = 'email';
ALTER TABLE users MODIFY COLUMN phone SET TAG pii_type = 'phone';
ALTER TABLE users MODIFY COLUMN name SET TAG pii_type = 'name';
ALTER TABLE users MODIFY COLUMN email SET TAG data_sensitivity = 'confidential';

-- Find all tagged columns
SELECT * FROM TABLE(INFORMATION_SCHEMA.TAG_REFERENCES(
  'users', 'TABLE'
));

-- Discover PII with Snowflake's automatic classification (Enterprise+)
SELECT *
FROM TABLE(
  INFORMATION_SCHEMA.EXTRACT_SEMANTIC_CATEGORIES('users')
);

Step 2: Column-Level Masking Policies

-- Dynamic masking — shows real data to privileged roles, masked to others
CREATE OR REPLACE MASKING POLICY email_mask AS (val STRING)
  RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('DATA_ENGINEER', 'SYSADMIN') THEN val
    WHEN CURRENT_ROLE() = 'DATA_ANALYST' THEN
      REGEXP_REPLACE(val, '.+@', '***@')  -- Show domain only
    ELSE '***MASKED***'
  END;

CREATE OR REPLACE MASKING POLICY phone_mask AS (val STRING)
  RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('DATA_ENGINEER', 'SYSADMIN') THEN val
    ELSE CONCAT('***-***-', RIGHT(val, 4))  -- Show last 4 digits
  END;

CREATE OR REPLACE MASKING POLICY ssn_mask AS (val STRING)
  RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('SYSADMIN') THEN val
    ELSE '***-**-' || RIGHT(val, 4)
  END;

-- Apply masking policies to columns
ALTER TABLE users MODIFY COLUMN email SET MASKING POLICY email_mask;
ALTER TABLE users MODIFY COLUMN phone SET MASKING POLICY phone_mask;

-- Tag-based masking (apply policy to all columns with a tag)
ALTER TAG pii_type SET MASKING POLICY email_mask;
-- Now ALL columns tagged pii_type='email' are automatically masked

Step 3: Row Access Policies

-- Row-level security — users only see their own department's data
CREATE OR REPLACE ROW ACCESS POLICY department_access AS (department_col 

Snowflake Debug Bundle

Overview

Collect diagnostic information from Snowflake's ACCOUNTUSAGE views, QUERYHISTORY, and driver logs for support tickets and troubleshooting.

Prerequisites

• Role with access to SNOWFLAKE.ACCOUNT_USAGE schema (typically ACCOUNTADMIN)
• Access to application logs
• Permission to collect environment info

Instructions

Step 1: Query-Level Diagnostics

-- Find the problematic query by ID
SELECT query_id, query_text, execution_status, error_code, error_message,
       start_time, end_time, total_elapsed_time / 1000 AS elapsed_seconds,
       bytes_scanned, rows_produced, compilation_time, execution_time,
       warehouse_name, warehouse_size
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE query_id = '<paste-query-id-here>';

-- Recent failed queries
SELECT query_id, query_text, error_code, error_message,
       start_time, user_name, role_name, warehouse_name
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE execution_status = 'FAIL'
  AND start_time >= DATEADD(hours, -24, CURRENT_TIMESTAMP())
ORDER BY start_time DESC
LIMIT 20;

-- Slow queries (> 60 seconds)
SELECT query_id, query_text, total_elapsed_time / 1000 AS seconds,
       bytes_scanned / 1e9 AS gb_scanned, partitions_scanned, partitions_total,
       warehouse_name, warehouse_size
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE total_elapsed_time > 60000
  AND start_time >= DATEADD(hours, -24, CURRENT_TIMESTAMP())
ORDER BY total_elapsed_time DESC
LIMIT 10;

Step 2: Connection and Session Diagnostics

-- Active sessions
SELECT session_id, user_name, created_on,
       client_application_id, client_environment
FROM TABLE(INFORMATION_SCHEMA.SESSIONS())
ORDER BY created_on DESC;

-- Login history (auth failures)
SELECT event_timestamp, user_name, client_ip, reported_client_type,
       error_code, error_message, is_success
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
WHERE event_timestamp >= DATEADD(hours, -24, CURRENT_TIMESTAMP())
  AND is_success = 'NO'
ORDER BY event_timestamp DESC;

Step 3: Warehouse and Resource Diagnostics

-- Warehouse load (queued queries = undersized)
SELECT warehouse_name, start_time,
       avg_running, avg_queued_load, avg_queued_provisioning, avg_blocked
FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_LOAD_HISTORY(
  DATE_RANGE_START => DATEADD(hours, -4, CURRENT_TIMESTAMP())
))
ORDER BY start_time DESC;

-- Credit consumption by warehouse
SELECT warehouse_name, SUM(credits_used) AS credits,
       SUM(credits_used_compute) AS compute_credits,
       SUM(credits_used_cloud_services) AS cloud_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD(days, -7, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
ORDER BY credits DESC;