Fix: Snowflake Not Working — Warehouse Suspend, COPY INTO, Roles, Time Travel, and Snowpipe

The Error

You run a query and the warehouse takes 30 seconds to wake up:

SELECT count() FROM events;
-- Query result: 1000 ms
-- Total elapsed: 32 s   (warehouse cold start)

Or COPY INTO from S3 fails with permission errors:

SQL Compilation error: Failed to access remote file: 
access denied. Please check your credentials.

Or a user grants you access but you still can’t query:

SQL access control error: Insufficient privileges to operate on schema 'ANALYTICS'.

Or your billing dashboard shows costs growing without active queries:

Compute usage last 7 days: 250 credits (≈$500)

Why This Happens

Snowflake separates compute (warehouses) from storage. Most issues map to:

• Warehouse lifecycle. Warehouses cost credits while running, even if no queries. Auto-suspend (default 10 minutes) stops them when idle. Cold start happens when the first query wakes them up.
• Role-based access. Snowflake’s RBAC has roles that own schemas, grants between roles, and a “current role” per session. Granting a privilege without USE ROLE doesn’t help.
• Stage configuration. COPY INTO reads from a Snowflake stage, which can be internal (Snowflake-managed) or external (S3/Azure/GCS). Each external stage needs the right credentials and storage integration.
• Time Travel and Fail-safe. Snowflake retains historical data for the duration of Time Travel (default 1 day, up to 90 in Enterprise edition) + 7-day Fail-safe. Both add to storage cost.

Fix 1: Configure Warehouse Auto-Suspend

CREATE WAREHOUSE my_wh
  WAREHOUSE_SIZE = SMALL
  AUTO_SUSPEND = 60       -- Suspend after 60 seconds idle
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE
  STATEMENT_TIMEOUT_IN_SECONDS = 3600;

AUTO_SUSPEND = 60 is more aggressive than the 10-minute default — for sporadic workloads, this saves significant credits. For dashboards with frequent queries (every minute), the 60-second cycle adds many warm-ups; bump to 300 (5 minutes).

To see warehouse status:

SHOW WAREHOUSES;

To resume manually if auto-resume is off:

ALTER WAREHOUSE my_wh RESUME;

To suspend manually (free up credits immediately):

ALTER WAREHOUSE my_wh SUSPEND;

Pro Tip: For BI workloads, use a smaller warehouse with multi-cluster scaling:

CREATE WAREHOUSE bi_wh
  WAREHOUSE_SIZE = MEDIUM
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 3
  SCALING_POLICY = STANDARD;

Multi-cluster scaling adds clusters when concurrent queries exceed a threshold. Cheaper than always running a LARGE warehouse.

Fix 2: Account URL and Connection

Your Snowflake account URL has a specific format:

<account_locator>.<region>.<cloud>.snowflakecomputing.com

# Examples:
abc12345.us-east-1.aws.snowflakecomputing.com
xyz67890.east-us-2.azure.snowflakecomputing.com
def54321.us-central1.gcp.snowflakecomputing.com

Find yours in the Snowflake Console URL bar after login.

For snowflake-connector-python:

import snowflake.connector

conn = snowflake.connector.connect(
    account="abc12345",   # Just the locator, not the full URL
    user="my_user",
    password="...",
    warehouse="MY_WH",
    database="MY_DB",
    schema="ANALYTICS",
    role="MY_ROLE",
)

For key-pair authentication (recommended over passwords):

from cryptography.hazmat.primitives import serialization

with open("rsa_key.p8", "rb") as f:
    p_key = serialization.load_pem_private_key(f.read(), password=None)

pkb = p_key.private_bytes(
    encoding=serialization.Encoding.DER,
    format=serialization.PrivateFormat.PKCS8,
    encryption_algorithm=serialization.NoEncryption(),
)

conn = snowflake.connector.connect(
    account="abc12345",
    user="my_user",
    private_key=pkb,
    warehouse="MY_WH",
    role="MY_ROLE",
)

Set the user’s public key in Snowflake:

ALTER USER my_user SET RSA_PUBLIC_KEY = '<base64-public-key>';

Key-pair auth is more secure than passwords and works with key rotation.

Common Mistake: Including the full URL (abc12345.us-east-1.aws.snowflakecomputing.com) as the account parameter. Use just the locator (abc12345) plus optional region parameter:

conn = snowflake.connector.connect(
    account="abc12345",
    region="us-east-1.aws",  # If outside the default region
    ...
)

Fix 3: Role-Based Access Control

Snowflake’s RBAC is more involved than typical databases:

-- Create a role:
CREATE ROLE analyst;

-- Grant warehouse usage:
GRANT USAGE ON WAREHOUSE my_wh TO ROLE analyst;

-- Grant database access:
GRANT USAGE ON DATABASE my_db TO ROLE analyst;
GRANT USAGE ON SCHEMA my_db.analytics TO ROLE analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA my_db.analytics TO ROLE analyst;
GRANT SELECT ON FUTURE TABLES IN SCHEMA my_db.analytics TO ROLE analyst;

-- Assign the role to a user:
GRANT ROLE analyst TO USER alice;

Three grant levels needed for read:

1. Warehouse USAGE — to run queries.
2. Database/schema USAGE — to see the objects.
3. Table SELECT — to read data.

Plus the user must USE ROLE analyst after login:

USE ROLE analyst;
USE WAREHOUSE my_wh;
USE DATABASE my_db;
USE SCHEMA analytics;
SELECT * FROM events;

Or set defaults on the user so they’re picked automatically:

ALTER USER alice SET 
  DEFAULT_ROLE = 'analyst',
  DEFAULT_WAREHOUSE = 'MY_WH',
  DEFAULT_NAMESPACE = 'MY_DB.ANALYTICS';

For granting access to future objects (anything created later):

GRANT SELECT ON FUTURE TABLES IN SCHEMA my_db.analytics TO ROLE analyst;

Without FUTURE, only existing tables are granted — new tables won’t be visible to the role.

Common Mistake: Granting SELECT to a user instead of a role. Snowflake’s pattern is grant-to-role, then assign-roles-to-users. Direct user grants are possible but lose maintainability.

Fix 4: COPY INTO From S3 / Azure / GCS

Three steps: storage integration → stage → COPY INTO.

For S3:

-- Step 1: Create storage integration (requires ACCOUNTADMIN role)
CREATE STORAGE INTEGRATION s3_int
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/snowflake-role'
  STORAGE_ALLOWED_LOCATIONS = ('s3://my-bucket/');

-- Step 2: Get the IAM user ARN Snowflake generated
DESC STORAGE INTEGRATION s3_int;
-- Use STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID to update the IAM role's trust policy in AWS.

-- Step 3: Create the stage
CREATE STAGE my_s3_stage
  STORAGE_INTEGRATION = s3_int
  URL = 's3://my-bucket/events/'
  FILE_FORMAT = (TYPE = JSON);

-- Step 4: Load data
COPY INTO events
FROM @my_s3_stage
PATTERN = '.*[.]json[.]gz'
ON_ERROR = 'CONTINUE';

The IAM role in AWS needs a trust policy referencing Snowflake’s IAM user (from step 2) — this is the AWS side that authorizes Snowflake to read your S3.

For Azure and GCS, the pattern is similar but uses STORAGE_PROVIDER = 'AZURE' or 'GCS'.

For inline credentials (less secure but simpler):

CREATE STAGE my_s3_stage
  URL = 's3://my-bucket/events/'
  CREDENTIALS = (AWS_KEY_ID = '...' AWS_SECRET_KEY = '...')
  FILE_FORMAT = (TYPE = JSON);

For production, always use storage integrations — credentials don’t leak in DDL output.

Common Mistake: Forgetting to update the IAM trust policy after DESC STORAGE INTEGRATION. Snowflake’s generated IAM user changes if you recreate the integration — re-update the trust policy.

Fix 5: Snowpipe for Continuous Ingestion

Snowpipe loads files automatically as they land in S3:

-- Create a pipe:
CREATE PIPE events_pipe
  AUTO_INGEST = TRUE
  AS COPY INTO events FROM @my_s3_stage;

-- Get the SQS queue ARN to configure S3 event notifications:
SHOW PIPES LIKE 'events_pipe';
-- Note the "notification_channel" value.

In AWS, configure S3 event notifications to publish to that SQS queue when objects are created in the prefix. Snowflake polls the queue and ingests new files.

Snowpipe is near real-time (~1 minute latency) but cheaper than streaming inserts. Use it for high-volume batch-style ingestion.

To monitor Snowpipe:

SELECT *
FROM TABLE(INFORMATION_SCHEMA.PIPE_USAGE_HISTORY(
  DATE_RANGE_START => DATEADD('day', -1, CURRENT_TIMESTAMP()),
  PIPE_NAME => 'events_pipe'
));

For failed files:

SELECT * FROM TABLE(VALIDATE_PIPE_LOAD(
  PIPE_NAME => 'events_pipe',
  START_TIME => DATEADD('hour', -24, CURRENT_TIMESTAMP())
));

Pro Tip: Snowpipe Streaming (announced 2022, generally available since) is faster (< 1 second) and even cheaper. For new pipelines, evaluate Snowpipe Streaming via the Snowflake Ingest SDK.

Fix 6: Time Travel and Fail-Safe

Snowflake retains historical data for DATA_RETENTION_TIME_IN_DAYS (Time Travel) + 7 days (Fail-safe).

To query historical data:

-- Read the table as of 5 minutes ago:
SELECT * FROM events AT(OFFSET => -300);

-- Read at a specific timestamp:
SELECT * FROM events AT(TIMESTAMP => '2026-05-19 10:00:00');

-- Read at a specific query ID:
SELECT * FROM events AT(STATEMENT => '01a23456-1234-5678-9012-...');

To restore a dropped table:

UNDROP TABLE events;

To set retention longer (Enterprise edition):

ALTER TABLE events SET DATA_RETENTION_TIME_IN_DAYS = 30;

Higher retention = more storage cost. Default is 1 day on Standard edition; up to 90 on Enterprise.

Common Mistake: Treating Time Travel as a backup. It’s not — it’s for accidental drop recovery and “what did this table look like yesterday” queries. Real backups (cross-region replication, exports to S3) are a separate concern.

For very large tables you don’t need to time-travel:

ALTER TABLE huge_logs SET DATA_RETENTION_TIME_IN_DAYS = 0;

0 disables Time Travel — saves storage but you can’t UNDROP.

Fix 7: Query Optimization

Snowflake’s EXPLAIN:

EXPLAIN SELECT * FROM events WHERE user_id = 42;
-- Shows the physical plan.

Or in the UI, click “Query Profile” after running a query for an interactive view.

Common optimizations:

• Clustering keys for very large tables:

  ALTER TABLE events CLUSTER BY (event_date, user_id);

  Snowflake reorganizes data in background. Significantly reduces pruned partitions for filter queries.

• Materialized views for repeated aggregations:

  CREATE MATERIALIZED VIEW daily_active_users AS
  SELECT DATE_TRUNC('day', event_time) AS day, COUNT(DISTINCT user_id) AS dau
  FROM events
  GROUP BY day;

• Result cache is automatic. Identical queries within 24 hours return from cache (free, instant). Cache busts when underlying data changes.

• Search optimization service for selective lookups:

  ALTER TABLE events ADD SEARCH OPTIMIZATION;

  Speeds up needle-in-haystack queries against large tables. Has a separate cost.

Pro Tip: Watch the Query Profile for “Bytes Scanned” and “Partitions Scanned.” If a query scans most of a table without good reason, add clustering or a partition column.

Fix 8: Cost Monitoring

Track credit usage:

SELECT
  warehouse_name,
  DATE_TRUNC('hour', start_time) AS hour,
  SUM(credits_used) AS credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time > DATEADD('day', -7, CURRENT_TIMESTAMP())
GROUP BY 1, 2
ORDER BY hour DESC;

For per-user credit attribution:

SELECT
  user_name,
  DATE_TRUNC('day', start_time) AS day,
  SUM(credits_used_cloud_services) AS credits_cs,
  COUNT(*) AS query_count
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time > DATEADD('day', -7, CURRENT_TIMESTAMP())
GROUP BY 1, 2
ORDER BY day DESC, credits_cs DESC;

SNOWFLAKE.ACCOUNT_USAGE is the historical view (24-hour delay). For real-time, use INFORMATION_SCHEMA per-database:

SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
WHERE start_time > DATEADD('hour', -1, CURRENT_TIMESTAMP());

To set up a resource monitor that auto-suspends warehouses if usage exceeds budget:

CREATE RESOURCE MONITOR monthly_budget
  WITH CREDIT_QUOTA = 100
  FREQUENCY = MONTHLY
  START_TIMESTAMP = IMMEDIATELY
  TRIGGERS
    ON 75 PERCENT DO NOTIFY
    ON 90 PERCENT DO SUSPEND
    ON 100 PERCENT DO SUSPEND_IMMEDIATE;

ALTER WAREHOUSE my_wh SET RESOURCE_MONITOR = monthly_budget;

This caps the warehouse at 100 credits per month — at 90%, queued queries finish then suspend; at 100%, kill in-flight queries.

Still Not Working?

A few less-obvious failures:

• Authentication token has expired. Tokens last 4 hours by default. Re-authenticate or use application parameter for longer-lived OAuth flows.
• Resource exceeded. Warehouse too small for the query. Resize: ALTER WAREHOUSE my_wh SET WAREHOUSE_SIZE = LARGE.
• Cannot upload file: failed to set permissions. PUT to internal stage failed. Check file isn’t open by another process; on Windows, antivirus sometimes interferes.
• Cache not returning expected data. Cache invalidates on table mutation. If a stale dashboard shows old data, the underlying table hasn’t changed — the cached result is correct.
• Snowflake Account is suspended. Trial expired or billing issue. Contact your account admin.
• Cross-region/cross-cloud reads are expensive. Egress fees apply. Replicate the data into your region if you query it often.
• Object name case sensitivity. Snowflake folds unquoted identifiers to UPPERCASE. SELECT * FROM events ≡ SELECT * FROM EVENTS. If you created "events" (with quotes), only "events" works.
• Object dependent ... cannot be dropped. A view depends on the table. Use DROP TABLE events CASCADE (with caution — drops dependents too) or drop the dependents first.

For related analytics database and cloud DW issues, see BigQuery not working, ClickHouse not working, DuckDB not working, and Postgres slow query.