Why AI workloads need their own tuning playbook
Snowflake builds AI into several layers of the platform. Cortex AI functions call large language models from SQL, Cortex Search handles retrieval for chat and search apps, Snowpark runs Python for feature engineering and model training, and Cortex Code, Snowflake’s coding agent, helps engineers build and validate the pipelines themselves.
Much of the standard tuning playbook still applies. BI queries, ELT pipelines and data science jobs all run on warehouse compute, so their cost comes down to how much data they scan and how long the warehouse runs. AI workloads add a second meter: the model. Cortex AI functions bill by the token, so what matters most is how many rows reach the model, how much text each row carries, and whether results get reused instead of recomputed.
That splits AI work on Snowflake into five kinds:
- LLM functions in SQL: classifying, summarizing, extracting and translating text in place
- Embeddings and retrieval: vector columns and Cortex Search services feeding RAG apps and agents
- Feature pipelines: the transformations that turn raw events into model-ready tables
- Training and scoring: Python and ML jobs running in Snowpark or on Snowflake’s container runtime
- Agent-assisted engineering: Cortex Code building, validating and troubleshooting pipelines and models
Right-size compute for each workload
A common mistake is running Cortex functions on a big warehouse. Snowflake’s guidance is to stay at MEDIUM or smaller, because a larger warehouse doesn’t make them faster. Inference runs on Snowflake’s side and bills by the token; the warehouse mostly waits, and you pay for every second it does.
The rest of the stack does care about compute:
| Workload | Warehouse type | Where to start | Why |
|---|---|---|---|
| LLM functions (AI_COMPLETE, AI_CLASSIFY, AI_EXTRACT) | Standard | XSMALL to MEDIUM | Inference is serverless; bigger warehouses add idle cost |
| Cortex Search indexing and refresh | Standard, dedicated per service | XSMALL to MEDIUM | Snowflake recommends no larger than MEDIUM |
| Feature pipelines (MERGE, UPDATE, large scans) | Gen2 standard | Size to data volume | Faster DML and scans on newer hardware |
| Python training and batch scoring | Snowpark-optimized | MEDIUM, then tune memory | More memory per node for in-memory model work |
| Cortex Code sessions | Standard, dedicated | XSMALL or SMALL, size up per job | Isolates exploratory queries and makes agent compute visible |
| Existing BI, ELT and data science jobs | Keep separate | As today | Stops a long AI batch from delaying dashboards and pipelines |
Gen2 is now the default for new standard warehouses in supported regions, and Snowflake reports up to 5.5x faster DML comparing February 2025 to February 2026, which matters most for CDC-heavy feature tables. It also costs more per credit than Gen1 (rates are in the Service Consumption Table), so a warehouse that exists only to call AI_CLASSIFY gains little from it.
If you’d rather not size warehouses at all, Adaptive Warehouses replace size, multi-cluster and query acceleration settings with an account-level compute pool. They reached general availability on AWS in June 2026, with some Azure and GCP regions since, and need Enterprise Edition or higher. Snowflake’s June 2026 GA announcement claimed 1.2x better price-performance than Gen2. That’s throughput per credit, not a smaller bill, so test on your own workload first.
One trap: as of the 2026_03 bundle, new Gen2 warehouses enable the Query Acceleration Service by default, and the pending 2026_06 bundle extends that to all new standard warehouses and raises the default maximum scale factor from 2 to 8. The Gen2 docs say converting an existing warehouse doesn’t enable it, so two warehouses with identical settings can bill differently. Check enable_query_acceleration and query_acceleration_max_scale_factor in SHOW WAREHOUSES before comparing them.
Lay out data so the model sees less of it
Every row you prune before inference is a row you don’t pay tokens on, so clustering and pruning save tokens as well as scan time. Most AI jobs run on new or changed records. If the source table is clustered, or naturally loaded, by that date column, Snowflake skips most micro-partitions before a single token is counted. Compare partitions scanned to partitions total in the query profile; a ratio near 1.0 on a job that should touch 2% of the table is the first thing to fix.
Four more habits:
- Put long text in a narrow table. A side table of claim_id, load_date and notes is cheaper to scan than a 60-column claims table with one free-text column, and easier to process incrementally.
- Use search optimization for point lookups on entity IDs, which feature retrieval and RAG metadata joins often need.
- Track what’s already been processed with a stream or a processed_at column, so the next run doesn’t re-read everything.
- Clean text in SQL before it reaches the model. Strip HTML, signatures, disclaimers and quoted reply chains, which can double the length of a support email. Regex runs on warehouse time; the model bills for every word it reads.
Tune Cortex AI functions for tokens, not seconds
Cortex AI functions bill by the token, so the target is simple: send fewer tokens to cheaper models, and never pay for the same answer twice. Generative functions like AI_COMPLETE, AI_CLASSIFY and AI_FILTER bill both input and output tokens, and several wrap your text in a hidden prompt, so the billed count runs higher than the text you send.
- Filter with plain SQL first. Date, status and category filters are nearly free; put them next to the AI predicate so the model only sees rows that survive. Snowflake’s optimizer now weighs inference cost when ordering predicates, and AI_FILTER has a built-in optimization that Snowflake says cuts token use by up to 60% on qualifying queries.
- Match the model to the task. Sentiment, routing and yes/no checks rarely need a frontier model. Measure accuracy on a labeled sample of 200 to 500 rows and move up only if the small model misses.
- Keep prompts and labels short. AI_CLASSIFY bills its labels, descriptions and examples as input tokens on every row, not once per call.
- Deduplicate before inference. Hash the cleaned text and run the model once per distinct value.
- Persist the output. Write results to a table keyed by that hash and join to it next time. An AI function inside a view reruns inference on every query.
Here’s the pattern for a daily ticket-routing job:
INSERT INTO ticket_labels (text_hash, label, labeled_at)
WITH new_text AS (
SELECT DISTINCT SHA2(t.clean_body) AS text_hash, t.clean_body
FROM tickets_clean t
WHERE t.load_date >= DATEADD(day, -1, CURRENT_DATE())
AND NOT EXISTS (
SELECT 1 FROM ticket_labels l
WHERE l.text_hash = SHA2(t.clean_body)
)
)
SELECT
text_hash,
AI_CLASSIFY(clean_body, ['billing', 'outage', 'feature request', 'other']):labels[0]::STRING,
CURRENT_TIMESTAMP()
FROM new_text;
Before a full backfill, price it. AI_COUNT_TOKENS runs on warehouse compute only and tells you how many tokens a sample will consume; multiply by row count and the model’s rate in the Service Consumption Table.
Embed once, refresh only what changed
An embedding of unchanged text never needs recomputing, and AI_EMBED bills input tokens only. If you manage vectors yourself, store them in a VECTOR column next to a hash of the source chunk and re-embed only when the hash changes. Chunk size is a real tradeoff: smaller chunks retrieve more precisely but multiply row counts. Pick a size, test on 50 real questions, and leave it alone.
Cortex Search handles most of this. Three settings matter:
- Define a primary key. Services with primary keys refresh only the rows that changed. Primary key columns must be TEXT, so cast numeric IDs in the source query, and incremental refresh also needs change tracking on the source objects.
- Set TARGET_LAG to what the business needs. A policy library that changes weekly doesn’t need a 1-minute lag, and lower lag drives warehouse cost up.
- Suspend what nobody queries. Serving and indexing suspend independently, and a suspended service stops accruing serving cost. Set AUTO_SUSPEND on dev and test services.
-- doc_id must be a TEXT column; cast numeric IDs in the service's source query
ALTER CORTEX SEARCH SERVICE support_kb SET PRIMARY KEY = (doc_id);
ALTER CORTEX SEARCH SERVICE support_kb SET TARGET_LAG = '1 day';
ALTER CORTEX SEARCH SERVICE support_kb_dev SET AUTO_SUSPEND = 1800;
ALTER CORTEX SEARCH SERVICE support_kb_dev SUSPEND SERVING;
Use a dedicated warehouse per service, no larger than MEDIUM. Snowflake’s cost page allows LARGE for some services, but its overview recommends MEDIUM as the ceiling.
Build feature and training pipelines incrementally
Start new feature pipelines on dynamic tables. Snowflake handles dependency order, incremental mode computes only the changed rows, and Cortex AI functions work in the SELECT clause, so enrichment runs on new rows as they arrive.
Two kinds of rebuild can hit these tables. Recreating a table with CREATE OR REPLACE, or changing its definition, reinitializes it in any refresh mode; on a table that calls AI_COMPLETE across 30 million rows, that re-runs inference on every row. Separately, a large upstream change such as an INSERT OVERWRITE or a bulk delete can make an incremental refresh cost more than a rebuild. Three protections:
- Keep AI enrichment in its own dynamic table, separate from feature logic that changes often, and modify it with CREATE OR ALTER rather than CREATE OR REPLACE. Snowflake’s docs say most schema changes then apply incrementally without reinitialization.
- Set INITIALIZATION_WAREHOUSE to a larger warehouse for the first build and WAREHOUSE to a small one for steady-state refresh.
- Set REFRESH_MODE = ADAPTIVE explicitly (generally available since July 2026). It covers the upstream case: it reinitializes only when a bulk change makes that cheaper, and the docs say the heuristic usually skips reinitialization for definitions that call Cortex AI functions. Under AUTO, an unsupported construct instead silently falls back to full refresh every cycle; under ADAPTIVE or INCREMENTAL, creation fails with a compilation error, which is the cheaper way to find out.
If some rows will never change, a frozen region (FROZEN WHERE) marks them so a reinitialization has less to reprocess.
For training and scoring, Snowpark-optimized warehouses suit single-node Python work that needs memory: scikit-learn fits, pandas-heavy prep, UDF scoring. The Container Runtime on compute pools is the better fit for distributed or GPU training, with distributed XGBoost, LightGBM and PyTorch and ML Jobs to schedule it from tasks. Either way, push joins and aggregations into SQL before data reaches Python.
Keep agent-assisted engineering efficient
Cortex Code (CoCo) is Snowflake’s coding agent, available as a CLI, a desktop app and in Snowsight. Engineers use it to build ingestion pipes, validate dbt models against a source-to-target mapping, profile and reconcile tables, trace duplicate loads and review models for performance. It bills on token consumption, and the warehouse compute its queries use bills separately, so it has the same two meters as everything above. The warehouse side is the easier one to miss: an agent asked to confirm a grain will often scan full history, try several variations, and profile every column.
- Give the agent its own small warehouse. That keeps exploratory queries away from pipelines and puts agent compute on its own line in QUERY_HISTORY.
- Point it at development schemas, zero-copy clones and sampled tables, and prove fixes on a clone before production.
- Scope the role. Keep write and delete privileges out of the role it explores with, and grant them deliberately for remediation.
- Ask for bounded checks first: the latest few loads or a date window, with row counts reported before anything expensive runs.
The SNOWFLAKE_COCO_USAGE_HISTORY view records every request across CLI, Desktop and Snowsight by user and interface, with per-model detail in nested columns and up to an hour of latency.
SELECT
DATE_TRUNC('week', usage_time) AS usage_week,
user_name,
interface,
SUM(token_credits) AS credits,
COUNT(*) AS requests
FROM SNOWFLAKE.ACCOUNT_USAGE.SNOWFLAKE_COCO_USAGE_HISTORY
WHERE usage_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY 1, 2, 3
ORDER BY usage_week DESC, credits DESC;
The agent can help with tuning too. It can pull clustering cost from AUTOMATIC_CLUSTERING_HISTORY, read query profiles and propose model changes, and its bundled cost-intelligence skill answers questions about its own usage. Check each finding against how the model is actually built before changing anything.
Work from specs, not chat history
Spec-driven development gives the agent written requirements to build and check against. Frameworks like OpenSpec give each change a folder with a proposal, tasks and spec deltas agreed before code is written; data teams already have a version of this in the source-to-target mapping. For performance, the point is that the spec, not the agent’s defaults, decides how a model is built and how much validation it gets. Put grain, materialization, clustering key, warehouse, a runtime target and the handling of any AI columns in the spec, and write acceptance scenarios as bounded checks:
### Requirement: fct_claims_daily build
- Grain: one row per claim_id and snapshot_date
- Materialization: incremental, unique_key (claim_id, snapshot_date)
- Clustering: snapshot_date
- Warehouse: TRANSFORM_S; daily run completes in under 10 minutes
- AI column: claim_category via AI_CLASSIFY, persisted in claim_labels, never computed in a view
#### Scenario: daily incremental load
- WHEN yesterday's source rows land in RAW
- THEN only new or changed claims are merged
- AND the grain test returns zero duplicate keys
- AND no rows are sent to AI_CLASSIFY that already have a label
Two details keep specs cheap. CoCo supports AGENTS.md and the CLI loads it into every session, so keep that file to conventions and pointers and put the detail in spec files and skills that load only when needed. OpenSpec relies on an AGENTS.md stub for assistants without built-in commands, so it should work with CoCo; test it on a small change. And scope each change to the models it touches, which matters most for dynamic tables with AI columns.
Watch the right numbers and set guardrails
The CORTEX_AI_FUNCTIONS_USAGE_HISTORY view breaks token spend down by query, function, model and user, with about five minutes of latency. A daily rollup by function and model is the first dashboard to build:
SELECT
DATE_TRUNC('day', start_time) AS usage_date,
function_name,
model_name,
query_tag,
SUM(credits) AS credits,
COUNT(DISTINCT query_id) AS queries
FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_AI_FUNCTIONS_USAGE_HISTORY
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY 1, 2, 3, 4
ORDER BY usage_date DESC, credits DESC;
The query_tag column only helps if people set it. Make ALTER SESSION SET QUERY_TAG part of every AI job and dbt model that calls a Cortex function.
Warehouse resource monitors don’t cap token spend, so guardrails for AI functions are built from alerts and tasks. Snowflake’s docs walk through three: an account-level alert when monthly AI credits pass a threshold; per-user limits, which means revoking SNOWFLAKE.CORTEX_USER from PUBLIC and granting it through a role an hourly task can revoke; and runaway query cancellation, which cancels any running query past a credit limit (credits already spent aren’t refunded).
On the warehouse side, the query profile still tells you most of what you need. If the AI operator dominates and the scan is small, stop resizing the warehouse and go back to the token habits above.
Where to start this week
Run the CORTEX_AI_FUNCTIONS_USAGE_HISTORY query above, sort by credits, then work through these:
- Move any Cortex function calls running on a warehouse larger than MEDIUM down to MEDIUM or smaller
- Add a plain SQL filter and a dedup step in front of the most expensive AI function query
- Replace any AI function inside a view with a persisted results table
- Add primary keys to Cortex Search services and set AUTO_SUSPEND on anything in dev
- Split AI enrichment out of feature dynamic tables that change often
- Give Cortex Code its own warehouse and review SNOWFLAKE_COCO_USAGE_HISTORY
Sources & Further Reading
- Cost considerations for Cortex AI Functions, Snowflake docs
- Managing Cortex AI Function costs with Account Usage, Snowflake docs
- AI_FILTER, Snowflake docs
- Optimizing Query Execution in Cortex AISQL, Snowflake engineering blog
- Cortex Search overview and Understanding cost for Cortex Search Services, Snowflake docs
- CREATE CORTEX SEARCH SERVICE and ALTER CORTEX SEARCH SERVICE, Snowflake docs
- Snowflake generation 2 standard warehouses, Gen2 as default (2026_03 bundle) and QAS by default for all warehouse types (2026_06 bundle), Snowflake docs
- Adaptive Compute and Adaptive Compute general availability, Snowflake docs; Snowflake Adaptive Compute Is Now Generally Available, Snowflake blog
- Snowflake SQL Performance Improvements 2026, Snowflake engineering blog
- Snowflake Service Consumption Table, Snowflake
- Supported queries for dynamic tables, Dynamic table refresh modes, Modify dynamic tables and ADAPTIVE refresh mode GA, Snowflake docs
- Snowflake Container Runtime and Snowflake ML Jobs, Snowflake docs
- Overview of Snowflake CoCo, Cortex Code CLI extensibility and SNOWFLAKE_COCO_USAGE_HISTORY view, Snowflake docs
- OpenSpec, Fission AI on GitHub
- Snowflake Cortex Code 101, Flexera

