Snowflake – Putting It in Production

Everything so far has been about making Snowflake do something. This lesson is about the gap between a warehouse that works and one other people depend on — environments, deployment, service accounts, monitoring, and the failure modes worth having an answer for before they happen.

Environments

Because databases in Snowflake are namespaces rather than servers, the cheapest and most common split is one account, three databases:

CREATE DATABASE analytics_prod DATA_RETENTION_TIME_IN_DAYS = 30;
CREATE DATABASE analytics_stg  DATA_RETENTION_TIME_IN_DAYS = 1;
CREATE DATABASE analytics_dev  DATA_RETENTION_TIME_IN_DAYS = 1;

-- Development against real data, instantly, at no storage cost.
CREATE OR REPLACE DATABASE analytics_dev CLONE analytics_prod;

That clone is the argument for this layout. Every engineer can have production data to develop against, refreshed on demand, with no possibility of touching production — which is a much stronger guarantee than "be careful" and much cheaper than a second copy of the data.

Separate accounts per environment are the stronger isolation: separate credentials, separate billing, no chance of a script pointed at the wrong database. The cost is that cloning across accounts is not possible, so refreshing dev from prod becomes a real data movement. Use separate accounts when regulation or a security review requires it, and separate databases otherwise.

Either way, make the environment explicit in every connection rather than relying on a default:

-- Every script starts here. No exceptions, no defaults.
USE ROLE      data_engineer;
USE WAREHOUSE loading_wh;
USE DATABASE  analytics_prod;
USE SCHEMA    marts;

DDL belongs in git

The single biggest difference between a warehouse that is maintainable and one that is not is whether its objects were created by a person in a worksheet or by a script in a repository. Aim for a state where dropping every object and re-running the repository reproduces the warehouse.

warehouse/
  migrations/
    V001__create_databases.sql
    V002__marts_schema.sql
    V003__roles_and_grants.sql
    V004__add_region_to_fact_sales.sql
  scripts/
    deploy.sh
  tests/
    row_counts.sql

Two properties make migrations safe to re-run, which is what CI needs:

• Idempotence. CREATE … IF NOT EXISTS for anything holding data; CREATE OR REPLACE for views, procedures, tasks and file formats, which hold none.
• No CREATE OR REPLACE TABLE in a migration. It silently discards the data. ALTER TABLE … ADD COLUMN is the change you meant.

A deployment is then a script, and it runs the same way on every environment:

#!/usr/bin/env bash
set -euo pipefail

ENVIRONMENT="${1:?usage: deploy.sh <dev|stg|prod>}"

for file in migrations/*.sql; do
  echo "applying ${file}"
  snow sql --connection "$ENVIRONMENT" -f "$file"
done

snow sql --connection "$ENVIRONMENT" -f tests/row_counts.sql

name: deploy-warehouse
on:
  push:
    branches: [main]

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - run: pip install snowflake-cli
      - name: Deploy to prod
        env:
          SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}
          SNOWFLAKE_USER: ETL_SERVICE
          SNOWFLAKE_PRIVATE_KEY_RAW: ${{ secrets.SNOWFLAKE_PRIVATE_KEY }}
        run: ./scripts/deploy.sh prod

If a transformation tool is already in the picture, let it own the modelling layer and keep these migrations for what it does not manage — databases, warehouses, roles, grants, tasks and pipes. Splitting on that line avoids two systems both believing they own a table.

Service accounts and network policies

Lesson 14 covered the shape; the production checklist is short:

• One service account per service, with TYPE = SERVICE and a comment naming the owning team.
• Key-pair authentication. Rotate keys on a schedule you actually keep — Snowflake supports two keys at once (RSA_PUBLIC_KEY and RSA_PUBLIC_KEY_2) precisely so rotation does not need downtime.
• A network policy restricting each service account to your infrastructure's addresses.
• The private key from a secrets manager at runtime — never in the repository, never baked into an image.
• MFA on every human user, and ACCOUNTADMIN on as few of them as possible.

-- Rotation without downtime: add the new key, cut over, remove the old.
ALTER USER etl_service SET RSA_PUBLIC_KEY_2 = 'MIIBIjANBgkqh...new...';
-- ... deploy clients using the new key, confirm logins succeed ...
ALTER USER etl_service UNSET RSA_PUBLIC_KEY;

Monitoring

Four things are worth alerting on, and all four are queries you already have from earlier lessons. Snowflake's own ALERT object can run them on a schedule without an external scheduler:

CREATE OR REPLACE ALERT failed_tasks_alert
  WAREHOUSE = loading_wh
  SCHEDULE  = '30 MINUTE'
  IF (EXISTS (
    SELECT 1
    FROM   TABLE(information_schema.task_history(
             SCHEDULED_TIME_RANGE_START => DATEADD('hour', -1, CURRENT_TIMESTAMP())))
    WHERE  state = 'FAILED'))
  THEN CALL SYSTEM$SEND_EMAIL(
    'ops_notifications',
    'data-team@example.com',
    'Snowflake: a scheduled task failed',
    'Check task_history in the last hour.');

ALTER ALERT failed_tasks_alert RESUME;   -- created suspended, like tasks

That last one is the one people leave out and then wish they had. Every other check tells you a component failed; a freshness check tells you the outcome is wrong, which catches the failures nobody anticipated — including the pipeline that succeeded while loading zero rows.

-- The check that catches what the others miss.
SELECT 'fact_orders' AS table_name,
       MAX(loaded_at) AS last_loaded,
       DATEDIFF('hour', MAX(loaded_at), CURRENT_TIMESTAMP()) AS hours_stale
FROM   analytics_prod.marts.fact_orders
HAVING hours_stale > 6;

Testing data, not just code

A deployment that applies cleanly can still leave the warehouse wrong, because the thing that broke is the data rather than the DDL. A handful of assertions run after every load catches most of it, and they are ordinary SQL — no framework required.

Four checks cover most real failures:

• Row count in a plausible range. Zero rows is the classic silent failure; ten times yesterday's count is the other one.
• Uniqueness on keys. Snowflake enforces none of it (lesson 5), so the only place a duplicate primary key gets caught is here.
• No unexpected nulls in the columns downstream consumers join on.
• Referential integrity against the dimension tables, for the same reason.

-- tests/row_counts.sql — returns rows ONLY when something is wrong,
-- so an empty result means the deployment is good.
SELECT 'fact_orders is empty' AS failure
FROM   analytics_prod.marts.fact_orders
HAVING COUNT(*) = 0

UNION ALL
SELECT 'duplicate order_id: ' || order_id
FROM   analytics_prod.marts.fact_orders
GROUP  BY order_id HAVING COUNT(*) > 1

UNION ALL
SELECT 'null customer_id on ' || COUNT(*) || ' rows'
FROM   analytics_prod.marts.fact_orders
WHERE  customer_id IS NULL
HAVING COUNT(*) > 0

UNION ALL
SELECT 'orphan customer_id: ' || f.customer_id
FROM   analytics_prod.marts.fact_orders f
LEFT   JOIN analytics_prod.marts.dim_customer d ON d.customer_id = f.customer_id
WHERE  d.customer_id IS NULL
GROUP  BY f.customer_id;

The convention that makes this work in CI is the one above: a passing test returns no rows. Then the deploy script fails the build if the result set is non-empty, and each row is its own readable error message.

Failure modes worth an answer

Before you call it production

• Every warehouse has AUTO_SUSPEND, a STATEMENT_TIMEOUT_IN_SECONDS that is not the two-day default, and a resource monitor.
• Nothing runs as ACCOUNTADMIN. Service accounts use key pairs and network policies.
• Future grants are set on every schema, so tomorrow's table is readable without a ticket.
• Retention is deliberate: long on sources of truth, zero on anything rebuildable, and rebuildable tables are transient.
• Every object was created by a script in a repository, and CI can re-run it.
• The four monitors above exist and have alerted at least once in a drill.
• Somebody other than the author has restored a table from Time Travel, on purpose, to prove the runbook works.

That last one is the item most often skipped and the only one that proves any of the others. A recovery procedure nobody has executed is a hypothesis.

That is the track. Back to the introduction for the full lesson index.