Snowflake SQL skill for Codex

Done well means: queries prune micro-partitions instead of scanning tables, warehouses are sized to the workload and suspend quickly, every warehouse has a resource monitor, and cost claims are backed by ACCOUNT_USAGE numbers rather than intuition.

Before changing anything

1. Read AGENTS.md and find how SQL runs: dbt (profiles.yml type snowflake), Snowflake CLI (snow sql -q), SnowSQL, or a Python connector. Queries need network access to the account; in a sandbox, ask for approval or ask the user to run them.
2. Note the role and warehouse in use (select current_role(), current_warehouse();). Many cost and metadata views need a role with access to the SNOWFLAKE database.
3. Any ALTER WAREHOUSE, resource monitor change, DROP, TRUNCATE, or CREATE OR REPLACE on shared objects needs explicit user confirmation; state the object and the effect.

Diagnose a slow query

Get the query ID and open its profile (Snowsight Query Profile, or select * from table(get_query_operator_stats('<query_id>'));). Read it in this order:

Profile signal	Meaning	Fix
Partitions scanned close to partitions total	No pruning	Filter on the column the table is naturally ordered or clustered by; avoid wrapping it in functions
Bytes spilled to local storage	Warehouse memory too small for this operator	Reduce data earlier, or one size up
Bytes spilled to remote storage	Severe memory pressure	Rewrite (pre-aggregate, filter first) and size up; remote spill is very slow
Join output rows >> input rows	Exploding join (duplicate keys)	Fix join keys or dedupe before joining
Most time in “Remote Disk I/O” on scans	Reading too much	Select fewer columns; prune
Queued time high, execution short	Warehouse overloaded by concurrency	Multi-cluster warehouse or separate warehouse

Rewrite patterns that help pruning:

-- prunes: direct range on the column
where event_ts >= '2025-09-01' and event_ts < '2025-09-02'
-- may prune poorly: function on the column (check partitions scanned in the profile)
where to_date(event_ts) = '2025-09-01'

-- dedupe with QUALIFY instead of a self-join
select * from raw.orders
qualify row_number() over (partition by order_id order by updated_at desc) = 1;

Warehouses

create warehouse if not exists transform_wh
  warehouse_size = 'MEDIUM'
  auto_suspend = 60            -- seconds; 60 is a good default for batch and BI
  auto_resume = true
  initially_suspended = true
  statement_timeout_in_seconds = 3600;

-- concurrency, not speed: scale out for many simultaneous BI queries (Enterprise edition+)
alter warehouse bi_wh set min_cluster_count = 1 max_cluster_count = 4 scaling_policy = 'STANDARD';

Workload	Guidance
Single big transformation slow, spilling	Size up one step at a time; each size doubles credits per hour, often roughly halves runtime when the query parallelises
Many concurrent small queries queueing	Multi-cluster, not a bigger size
Mixed ELT and BI on one warehouse	Separate warehouses so costs and contention are attributable
Tiny queries all day keep warehouse awake	Lower auto_suspend; batch the polling job

Billing is per second with a 60-second minimum each time a warehouse resumes.

Resource monitors

create resource monitor transform_monthly with
  credit_quota = 500
  frequency = monthly
  start_timestamp = immediately
  triggers on 75 percent do notify
           on 100 percent do suspend
           on 110 percent do suspend_immediate;
alter warehouse transform_wh set resource_monitor = transform_monthly;

Creating account-level monitors needs ACCOUNTADMIN. suspend_immediate cancels running queries; agree on it with the user.

Where the credits go

SNOWFLAKE.ACCOUNT_USAGE views lag by up to a few hours (45 minutes to 3 hours depending on the view).

-- credits per warehouse, last 30 days
select warehouse_name, sum(credits_used) as credits
from snowflake.account_usage.warehouse_metering_history
where start_time >= dateadd(day, -30, current_timestamp())
group by 1 order by 2 desc;

-- most expensive query patterns (compute credits attributed per query)
select q.query_parameterized_hash, any_value(q.query_text) as sample,
       count(*) as runs, sum(a.credits_attributed_compute) as credits
from snowflake.account_usage.query_attribution_history a
join snowflake.account_usage.query_history q using (query_id)
where a.start_time >= dateadd(day, -7, current_timestamp())
group by 1 order by credits desc limit 20;

Also check serverless features (automatic clustering, search optimization, materialized view refresh, Snowpipe) in metering_daily_history by service_type; they bill outside warehouses.

Clustering and search optimization

• Natural ingestion order (by load date) already clusters time-series tables. Add a clustering key only for large tables (roughly 1 TB+ or many thousands of micro-partitions) whose main filters do not align with load order.
• Check first: select system$clustering_information('analytics.events', '(event_date, tenant_id)'); and look at average_depth.
• alter table analytics.events cluster by (event_date, tenant_id); starts automatic clustering, which costs serverless credits continuously on high-churn tables. Confirm with the user.
• Point lookups on high-cardinality columns (IDs, emails) on big tables: alter table ... add search optimization on equality(customer_id);.

Loading and merging

copy into raw.orders
from @raw_stage/orders/2025/09/
file_format = (type = parquet)
match_by_column_name = case_insensitive
on_error = abort_statement;          -- or 'continue' with a reject review, never silently

merge into analytics.orders t
using (select * from raw.orders
       qualify row_number() over (partition by order_id order by updated_at desc) = 1) s
on t.order_id = s.order_id
when matched and s.updated_at > t.updated_at then update set status = s.status, updated_at = s.updated_at
when not matched then insert (order_id, status, updated_at) values (s.order_id, s.status, s.updated_at);

COPY INTO skips files it already loaded (64 days of load metadata), which makes reruns safe; force = true defeats that and duplicates data.

Safety nets

• Time travel: select * from analytics.orders at(offset => -60*30); and undrop table. Retention depends on edition and data_retention_time_in_days.
• Zero-copy clone for testing changes: create table analytics.orders_dev clone analytics.orders; Clones are cheap until data diverges.

Snowpark basics

from snowflake.snowpark import Session
from snowflake.snowpark.functions import col, sum as sum_

session = Session.builder.configs({"connection_name": "default"}).create()  # ~/.snowflake/connections.toml
df = (session.table("analytics.orders")
      .filter(col("order_date") >= "2025-09-01")
      .group_by("country")
      .agg(sum_("amount").alias("revenue")))
df.show()                       # lazy until an action; executes as SQL in the warehouse
rows = df.collect()             # small results only; to_pandas() pulls data to the client

Push work into Snowpark DataFrame operations; pulling a large table with to_pandas() moves the compute to the client and pays egress time.

Gotchas

• select * on wide tables reads every column; Snowflake is columnar, so name columns.
• Result cache returns identical query results for 24 hours if data has not changed; disable with alter session set use_cached_result = false when benchmarking.
• Warm-cache timings flatter changes; compare cold runs or several runs.
• Unquoted identifiers are uppercased; "orderId" and orderid are different columns.
• create or replace table drops grants and time-travel continuity of the old object unless copy grants is used.

Report template

**Query:** daily_revenue (query id 01b8...)
**Before:** 6m 12s on MEDIUM, 48,210 of 48,900 partitions scanned, 22 GB remote spill
**Changes:** range filter on event_ts instead of to_date(); pre-aggregate before join; dedupe with QUALIFY
**After:** 41 s on MEDIUM, 1,140 partitions scanned, no spill (cold run)
**Cost:** approx 0.9 credits/day saved; transform_wh auto_suspend 600 -> 60 (pending your approval)