Blog

Building Data Pipelines That Feed AI Features Without Breaking the Bill
When the consumer at the end of a pipeline is a language model rather than a dashboard, the expensive step moves from the transform to the last hop, and every row you push through it costs money. This article covers the pipeline shape we keep returning to, a four-question test for choosing batch, event-driven or request-path inference, and the three cost levers that matter, in the order they matter.

Photo: Derrick Coetzee, “Front of server racks at NERSC”, Wikimedia Commons, CC0 1.0 public
domain dedication

Most teams adding an AI feature to an existing product start by asking which model to use, or whether they need a streaming platform. Both matter far less to the bill than a duller question.

How many model calls does one business event cause, and how many of them could have waited, or not happened at all?

A pipeline built around that question stays predictable. A pipeline that treats the model as one more sink, like a reporting table, tends to produce the invoice that gets the feature switched off.

The Consumer Changed, the Pipeline Did Not

A classic analytics pipeline ends in a dashboard. The transform is the heavy step, the output is an aggregate, and reprocessing a day of data is cheap because warehouse compute is cheap per row. Staleness of a few hours is usually fine.

A pipeline that feeds an AI feature inverts most of that:
- The expensive step is the last one. Warehouse SQL over a few hundred thousand rows costs little. Sending those same rows to a model provider is billed per token, per row.
- The output is per record, not aggregated. A product description, a ticket category, a summary for one report. There is no “roll it up” shortcut.
- Reprocessing is no longer free. A backfill that is harmless for a dashboard can be the most expensive job of the month when a model sits at the end of it.
- Idempotency becomes a cost control, not just a correctness property. A retry that re-sends a row is a second charge.
The practical consequence: design the pipeline so the model sees as few rows as possible, each as small as possible, and never the same unchanged row twice.

The Pipeline Shape We Keep Coming Back To

Across the AI retrofits we have shipped into products that were already in production, from ticket triage on a marketplace to drafting product copy and summarising reports for internal reviewers, the architecture has settled into four layers.

The operational store stays the write path, the warehouse prepares a model input contract, and a worker only calls the model for rows whose input actually changed.

Figure 1: The pipeline shape. The operational store stays the write path, the warehouse prepares a model input contract, and a worker only calls the model for rows whose input actually changed.

Our examples use BigQuery and a Node.js worker, because that is where most of this work runs. The shape maps directly onto AWS: object storage plus Athena or Redshift for the warehouse layer, and a scheduled container task for the worker.

Layer 1: Ingestion, Kept Deliberately Boring

The operational database stays the write path. The relevant tables are exported, by a managed streaming export or a scheduled one, into append-only raw tables in the warehouse.

This is the same additive pattern we use for any workload that outgrows the operational database: keep writes where they are and build the new read path elsewhere. The AI feature can then be removed without touching the product’s core data.

Layer 2: Transformation Produces a Model Input Contract

The most useful artifact in the whole pipeline is a single view that defines exactly what the model is allowed to see, in exactly the shape the prompt reads. Nothing else is sent.
```
-- One row per record the model may see, in the shape the prompt reads.
CREATE OR REPLACE VIEW ai.product_copy_input AS
SELECT
  p.product_id,
  p.title,
  p.category,
  p.origin,
  p.unit,
  -- Hash only the fields the prompt uses. Price and stock are excluded on purpose.
  TO_HEX(SHA256(TO_JSON_STRING(STRUCT(p.title, p.category, p.origin, p.unit)))) AS input_hash
FROM raw.products_latest AS p
WHERE p.status = 'unpublished';
```
Three things happen in that view. Fields are trimmed to the handful the prompt needs. Anything that must not leave your infrastructure is removed or replaced with a placeholder here, in SQL you can review, not in application code scattered across services. And input_hash gives every row a content-based identity the next layer uses to skip work.

On a feature that read health-related customer records, replacing a full record with a hand-picked field set cut prompt tokens by more than half, and reduced what left our infrastructure at all. That one change beat every model-pricing decision on the same feature.

Layer 3: The Inference Worker Is a Pipeline Stage

The model call belongs in a worker that behaves like any other pipeline stage: it selects its inputs, processes them in batches, validates its outputs, and records what it did.
```
interface ModelInput { productId: string; inputHash: string; prompt: string }

async function runNightlyDrafts(cap: SpendCap): Promise<void> {
  // Only rows whose input hash has never been sent to the model
  const rows: ModelInput[] = await warehouse.query(`
    SELECT i.* FROM ai.product_copy_input AS i
    LEFT JOIN ai.processed_inputs AS d USING (product_id, input_hash)
    WHERE d.product_id IS NULL`);

  for (const batch of chunk(rows, 50)) {
    if (!cap.allows(estimateTokens(batch))) break; // stop quietly, the app keeps its fallback
    const results = await provider.generate(batch);
    const valid = results.filter(isValidDraft); // schema check, invalid output is dropped
    await sidecar.upsert(valid); // keyed by product_id and input_hash
    await warehouse.insert('ai.processed_inputs', results.map(toLedgerRow)); // failures too, or they are re-sent nightly
    cap.record(results);
  }
}
```
The output never overwrites the product’s own fields. It lands in a sidecar table keyed by record ID and input hash, and the application reads it when a valid row exists.

When none exists, because the worker has not run, the cap was hit or validation failed, the application renders what it rendered before the feature existed. Removing the feature means deleting a table, not reversing a migration.

Batch, Event-Driven or Request Path: A Four-Question Test

Most “batch versus streaming” debates about AI features are really a question about who is waiting. We run every feature through four questions, in order, and stop at the first one that gives a clear answer.

Figure 2: The four-question test. Each question is an exit; most features leave at question one or two.

1. Can the Output Be Computed Before Anyone Asks for It?

If yes, it is a scheduled batch job, full stop. Latency is free, batch pricing from providers applies, and the input hash means the nightly run only touches rows that changed.

Product description drafting is our clearest example: retailers submit products during the day, drafts appear overnight in a draft field, and a person approves before anything is published.

2. Does a Person Wait on Screen for the Result?

If nobody is watching, it is event-triggered and asynchronous: a post-write trigger puts the record on a queue, a worker calls the model, and the result lands later.

Support ticket triage works this way. The ticket is saved first, the customer sees no added latency, and the suggested category appears for the ops team a few seconds later. If the call fails or exceeds its timeout, the ticket goes to the default queue as it always did.

For most AI features, this is what “streaming” means: a trigger and a queue. A dedicated event streaming platform earns its place when many independent consumers need the same event history, which one AI feature rarely justifies.

3. Did the Person Explicitly Ask for It?

If a user clicked “tidy up this description”, it is a user-triggered call. Seconds are acceptable because they asked and are watching a loading state. It still needs a cancel path and the original content preserved.

4. Can the Page Render Acceptably If the Call Is Skipped?

Only now do we consider the request path, and only with a hard timeout well under the page’s existing latency budget and a deterministic fallback that renders the pre-AI experience.

If the page cannot render without the model’s answer, the feature is not ready for that path. Precompute it, or do not ship it there.

Where the Bill Actually Comes From

The monthly cost of an AI feature is roughly:

calls per business event × tokens per call × event volume

The price per token is the factor people argue about, and it is the one we touch last.

Across our retrofits, per-call cost varied by two orders of magnitude between features, and the levers that moved it were, in order of impact:
1. Trim the input. Covered in layer 2. Fewer fields, fewer tokens, less data leaving your systems.
2. Key the cache on content, not identity. See below.
3. Switch models, but only with an eval set. A smaller model is cheaper per call, but without a fixed set of real inputs and assertions to prove quality held, a model switch is a guess with a saving attached.
Key the Cache on Content, Not Identity

An expensive mistake we have made ourselves is deciding whether to regenerate based on the record: its ID plus an updated_at timestamp. Records change constantly for reasons the model does not care about.

Figure 3: An illustrative sequence of changes to one product. Keyed on identity, every change triggers a model call. Keyed on a hash of the fields the prompt reads, only the changes the model would notice do.

Keyed on identity, every change triggers a model call. Keyed on a hash of the fields the prompt reads, only the changes the model would notice do.

On a marketplace feature that generates copy once per product version and serves it thousands of times, moving the cache key from the product ID to a hash of the normalised attribute set meant regeneration only happened when attributes actually changed.

Monthly spend on that feature dropped to a fraction of its launch figure, with no change to the model or the prompt.

Put the Spend Cap in the Pipeline

Provider dashboards can alert you, but they cannot make your product degrade gracefully.

We write a hard ceiling into the worker itself, as in the snippet above. When it is reached, the worker stops and the application falls back, so an overspend becomes a quiet degradation someone reviews in the morning rather than an invoice discovered at month end.

Where Managed Services Stop Being Worth It

The pitch for a managed ETL connector, a distributed compute cluster, or a dedicated vector database is usually made as if the data volume were the hard part. For most AI features inside an existing product, it is not.

The volume that matters is bounded by business events: products listed, tickets opened, reports produced.

Our rules of thumb:
- Managed connectors earn their fee for SaaS sources you do not control and whose APIs change under you. For your own operational database, a native export into the warehouse is usually simpler and cheaper.
- Distributed compute such as Spark earns its place when the transform itself is the heavy step: preparing very large corpora, non-SQL processing at scale, or feeding self-hosted models. When the transform fits in warehouse SQL and the expensive step is a rate-limited API call, a cluster adds operational weight without shortening the part that is slow.
- Serverless functions suit short triggers. Long batch runs belong in a container runtime with no execution time ceiling.
- A dedicated vector store is worth it once the corpus and query volume outgrow what your existing database or warehouse can serve. For a staff-only internal search over a modest document set, it is often one more system to secure and keep in sync.
The trade-off we accept is a pipeline that looks unimpressive on an architecture slide. In return, fewer systems hold copies of the data, which matters when a deletion request has to reach every copy.

When a Pipeline Is the Wrong Answer

Not every AI feature needs one.

If the feature is user-triggered, operates on content already on screen, and runs a few times a day per user, a direct call with a timeout and a preserved original is simpler and cheaper than any pipeline.

A pipeline also cannot fix missing rules. We once scoped automatic routing of support messages against categories that existed in a dropdown, while the real routing logic lived in one person’s head and contradicted it.

No amount of data engineering helps there. If the rules cannot be written down before work starts, write them down first.

What to Do on Monday

Pick your most expensive AI feature and write down three numbers:
1. Model calls per business event.
2. Average input tokens per call.
3. How many of last month’s calls processed an input identical to one already processed.
Then add an input hash to the view that feeds it.

If that third number is not close to zero, the hash will pay for itself before you touch the model.
September 17, 2026

Snowflake Cortex AI Token Usage Monitoring: The Complete Guide

Somewhere on your team, an AI_CLASSIFY job is running on a table larger than anyone realised. Or a Cortex Agent is looping through a multi-step workflow that seemed cheap in testing. Or a developer left a search service indexed and running in a dev environment that nobody is querying anymore. None of these will trigger your existing resource monitors. All of them will show up on your AI Credits bill.

If you’ve already read our piece on where the hidden Cortex AI token costs live, you know what you’re paying for. This article is about building the monitoring stack that catches those costs in real time — before they land on the invoice. That means three ACCOUNT_USAGE views, three automation patterns, and a clear understanding of what each one covers and what it misses.

TL;DR

The primary monitoring view for AI SQL functions is SNOWFLAKE.ACCOUNT_USAGE.CORTEX_AI_FUNCTIONS_USAGE_HISTORY — generally available since March 2026, with latency as low as 2 minutes and a maximum of 5 minutes. Use it as your canonical source; do not sum it with the older CORTEX_AISQL_USAGE_HISTORY or you will double-count.
Cortex Agents have their own view: CORTEX_AGENT_USAGE_HISTORY (GA Feb 25 2026). Each row is one agent request, with aggregated credits plus granular sub-call detail for every tool the agent invoked.
Deep observability — traces, spans, conversation threads — lives in SNOWFLAKE.LOCAL.AI_OBSERVABILITY_EVENTS. Cortex Search writes here only when REQUEST_LOGGING is enabled on the service. Built-in AI SQL functions do not write to this table.
Three automation patterns: account-level monthly spend alerts via Snowflake Alerts, per-user monthly limits enforced by hourly Tasks that auto-revoke and auto-restore access, and runaway query cancellation via SYSTEM$CANCEL_QUERY.
The prerequisite for per-user limits is revoking SNOWFLAKE.CORTEX_USER from the PUBLIC role. Without that step, users can bypass all per-user controls by switching to any other role that still carries the database role.
Resource Monitors still do not cover AI Credits. You must build Cortex-specific alerting separately against the usage history views.

The Three Views and What Each One Covers

The first thing to get clear is the view taxonomy. Snowflake has iterated on this several times since Cortex launched, and the current state as of mid-2026 is three distinct views with distinct coverage. Using the wrong one doesn’t produce an error — it just produces incomplete data.

View	Covers	Latency	Available Since
`CORTEX_AI_FUNCTIONS_USAGE_HISTORY`	All AI SQL functions: AI_COMPLETE, AI_CLASSIFY, AI_SUMMARIZE, AI_SENTIMENT, AI_TRANSLATE, AI_FILTER, AI_EXTRACT, AI_PARSE_DOCUMENT, AI_AGG, AI_EMBED_TEXT	2–5 min	Nov 17 2025
`CORTEX_AGENT_USAGE_HISTORY`	Cortex Agents invoked via the Agent API or CoWork. One row per agent request, includes per-tool sub-call breakdown	Near real-time	Feb 25 2026 (GA)
`AI_OBSERVABILITY_EVENTS` (SNOWFLAKE.LOCAL)	Agent traces and spans; Cortex Search request logs (if REQUEST_LOGGING enabled); CoCo spans for every prompt	Varies by service	Rolling
`CORTEX_AISQL_USAGE_HISTORY`	Older view, still present. Overlaps with CORTEX_AI_FUNCTIONS_USAGE_HISTORY. Do not sum both.	Use new view instead	Legacy
`CORTEX_SEARCH_SERVING_USAGE_HISTORY`	Cortex Search serving compute (the continuous GB/month charge)	Account Usage latency	On GA

One critical note on AI_OBSERVABILITY_EVENTS: Snowflake’s AI Observability docs are explicit that built-in AI SQL functions like AI_COMPLETE and AI_CLASSIFY do not write traces to this table. Monitor those with CORTEX_AI_FUNCTIONS_USAGE_HISTORY. The observability table is for agents, CoCo prompts, and search requests — where you need conversation-level detail, not just credit aggregates.

Basic Usage Monitoring Queries

These are your daily driver queries. Run them on a schedule or wire them into a BI dashboard. The official Snowflake cost management docs provide the canonical versions of these patterns — reproduced here with explanatory context.

Daily credit burn by function and model

This is your first view into where tokens are actually going. Sort by ai_credits DESC and your most expensive function-model combination usually jumps out immediately.

-- Daily credit consumption by function and model — last 30 days
-- Canonical source for AI SQL functions
SELECT
  DATE_TRUNC('day', start_time)   AS usage_day,
  function_name,
  model_name,
  SUM(credits)                    AS ai_credits,
  SUM(input_tokens)               AS input_tokens,
  SUM(output_tokens)              AS output_tokens,
  COUNT(DISTINCT query_id)        AS distinct_queries
FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_AI_FUNCTIONS_USAGE_HISTORY
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY 1, 2, 3
ORDER BY usage_day DESC, ai_credits DESC;

Monthly spend by user

Join to USERS to get email and default role — makes it far easier to follow up with a specific person when their consumption spikes.

-- Monthly credit consumption by user — last 3 months
SELECT
  DATE_TRUNC('month', h.start_time)  AS usage_month,
  u.name                             AS user_name,
  u.email,
  u.default_role,
  SUM(h.credits)                     AS ai_credits,
  COUNT(DISTINCT h.query_id)         AS distinct_queries
FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_AI_FUNCTIONS_USAGE_HISTORY h
JOIN SNOWFLAKE.ACCOUNT_USAGE.USERS u ON h.user_id = u.user_id
WHERE h.start_time >= DATEADD('month', -3, CURRENT_TIMESTAMP())
GROUP BY 1, 2, 3, 4
ORDER BY usage_month DESC, ai_credits DESC;

Cortex Agent attribution

For agent workloads, use CORTEX_AGENT_USAGE_HISTORY separately. Each row covers one agent request and includes granular sub-call detail — you can see exactly which tool leg (Analyst, Search, SQL) consumed the most credits within each request.

-- Agent credit attribution by agent and user — last 30 days
SELECT
  DATE_TRUNC('day', start_time)  AS usage_day,
  agent_id,
  user_id,
  SUM(total_credits)             AS ai_credits,
  COUNT(request_id)              AS requests,
  SUM(input_tokens)              AS input_tokens,
  SUM(output_tokens)             AS output_tokens
FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_AGENT_USAGE_HISTORY
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY 1, 2, 3
ORDER BY usage_day DESC, ai_credits DESC;

If you’ve connected AI agents to Snowflake through an MCP server, the agent requests still flow through the same Cortex infrastructure and appear in this view — you don’t need a separate monitoring path for MCP-invoked agents.

Automation Pattern 1: Account-Level Monthly Spend Alert

Resource Monitors don’t cover AI Credits. That means you need a separate alerting mechanism. Snowflake Alerts — the native scheduled condition-check object — are the right tool. The pattern is: a NOTIFICATION INTEGRATION wired to email recipients, an Alert that fires hourly against the usage view, and a stored procedure that sends the email and prevents duplicate alerts within a calendar month.

The key implementation detail from Snowflake’s docs: the alert tracks an AI_FUNCTIONS_ALERT_STATE table to ensure only one email fires per calendar month per alert name. Without that guard, a threshold breach at 9 AM would send 15 hourly emails by midnight. The stored procedure checks the state table first, inserts a record if none exists for the current month, then sends the notification.

Email delivery prerequisite: For SYSTEM$SEND_EMAIL to work, every recipient address must satisfy three conditions simultaneously: listed in ALLOWED_RECIPIENTS on the notification integration, used as the to_email argument in the procedure body, and set as the verified EMAIL field on a Snowflake user in the account. Missing any one of the three produces a generic “not allowed” error with no indication of which condition failed.

-- Minimal alert setup — replace 1000 with your actual threshold
CREATE OR REPLACE NOTIFICATION INTEGRATION ai_cost_alerts
  TYPE = EMAIL
  ENABLED = TRUE
  ALLOWED_RECIPIENTS = ('[email protected]');

-- Alert: fires every hour if monthly spend exceeds threshold
CREATE OR REPLACE ALERT ai_functions_monthly_spend_alert
  WAREHOUSE = 
  SCHEDULE = 'USING CRON 0 * * * * UTC'
  IF (EXISTS (
    SELECT 1
    FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_AI_FUNCTIONS_USAGE_HISTORY
    WHERE start_time >= DATE_TRUNC('month', CURRENT_TIMESTAMP())
    HAVING SUM(credits) > 1000  -- adjust threshold
  ))
  THEN
    CALL SEND_MONTHLY_SPEND_ALERT(1000);

ALTER ALERT ai_functions_monthly_spend_alert RESUME;

Automation Pattern 2: Per-User Monthly Spending Limits

Account-level alerts tell you the house is on fire. Per-user limits prevent any single user from starting it. The implementation uses a role gate: access to Cortex AI functions flows through a dedicated AI_FUNCTIONS_USER_ROLE, and an hourly Task revokes that role from any user who exceeds their monthly credit budget. A separate monthly Task restores it on the first of each month.

The critical prerequisite, which the docs call out explicitly: revoke SNOWFLAKE.CORTEX_USER from the PUBLIC role before setting any per-user limits. By default, every user in a Snowflake account has access to Cortex AI through PUBLIC. If you don’t close that hole first, a user who hits their limit on AI_FUNCTIONS_USER_ROLE can simply switch to any other role that still carries the database role — and the hourly revocation does nothing.

-- Step 1: Close the PUBLIC role bypass (run as ACCOUNTADMIN)
USE ROLE ACCOUNTADMIN;
REVOKE DATABASE ROLE SNOWFLAKE.CORTEX_USER FROM ROLE PUBLIC;

-- Audit: confirm no other roles carry it unexpectedly
SHOW GRANTS OF DATABASE ROLE SNOWFLAKE.CORTEX_USER;

-- Step 2: Create the gated access role
CREATE ROLE IF NOT EXISTS AI_FUNCTIONS_USER_ROLE;
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO ROLE AI_FUNCTIONS_USER_ROLE;

-- Step 3: Grant access to specific users with individual credit limits
-- (See full GRANT_AI_FUNCTIONS_ACCESS procedure in Snowflake docs)
CALL GRANT_AI_FUNCTIONS_ACCESS('alice_analyst', 1000);  -- 1000 AI Credits/month
CALL GRANT_AI_FUNCTIONS_ACCESS('bob_engineer',  2000);  -- 2000 AI Credits/month

The access control table (AI_FUNCTIONS_ACCESS_CONTROL) tracks each user’s monthly limit, active status, revocation timestamp, and revocation reason. When the hourly MONITOR_AI_FUNCTIONS_SPENDING task runs, it joins the table against CORTEX_AI_FUNCTIONS_USAGE_HISTORY, finds users who have exceeded their limit for the current month, and calls REVOKE ROLE AI_FUNCTIONS_USER_ROLE FROM USER <name> for each. On the first of the next month, MONTHLY_AI_FUNCTIONS_ACCESS_REFRESH restores the role to everyone in the table — no manual intervention needed.

Long-running query exemption: If some users legitimately need to run extended Cortex jobs, create a separate AI_FUNCTIONS_USER_LONG_RUNNING_ROLE and add a NOT ARRAY_CONTAINS check in the revocation procedure’s HAVING clause to exclude queries run under that role from cancellation. Users adopt it explicitly when they need it, keeping the default enforcement tight.

Automation Pattern 3: Runaway Query Detection and Cancellation

The third loop is the most operationally immediate. Runaway queries — AI function calls on unexpectedly large tables, or agents caught in loops — can accumulate significant credits in a single hour. The detection pattern works because CORTEX_AI_FUNCTIONS_USAGE_HISTORY splits usage into one-hour windows and includes an IS_COMPLETED flag. A still-running query across multiple hourly windows has all its rows with IS_COMPLETED = FALSE. Aggregate credits by QUERY_ID, check that no row is completed, and if the sum exceeds your threshold — cancel it.

-- Core detection CTE — finds running queries that have already exceeded the threshold
WITH query_credits AS (
  SELECT
    h.query_id,
    ANY_VALUE(h.user_id)        AS user_id,
    SUM(h.credits)              AS total_credits,
    MIN(h.start_time)           AS first_seen,
    BOOLOR_AGG(h.is_completed)  AS any_completed
  FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_AI_FUNCTIONS_USAGE_HISTORY h
  WHERE h.start_time >= DATEADD('hour', -48, CURRENT_TIMESTAMP())
  GROUP BY h.query_id
  HAVING SUM(h.credits) > 50          -- your credit threshold
     AND BOOLOR_AGG(h.is_completed) = FALSE  -- still running
)
SELECT qc.query_id, u.name AS user_name, qc.total_credits, qc.first_seen
FROM query_credits qc
LEFT JOIN SNOWFLAKE.ACCOUNT_USAGE.USERS u ON qc.user_id = u.user_id;

The full implementation in Snowflake’s official cost management docs wraps this into a stored procedure that calls SYSTEM$CANCEL_QUERY for each hit, handles cancellation failures gracefully (logging them as CANCEL FAILED), and sends an email alert with the query ID, user, functions invoked, credits consumed, and warehouse ID. One important note from those docs: cancelling a query stops further accumulation but does not refund credits already billed up to the cancellation point. Early detection is everything.

The Gotchas

Summing CORTEX_AISQL_USAGE_HISTORY and CORTEX_AI_FUNCTIONS_USAGE_HISTORY together double-counts.The older view still exists and returns data. Both cover AI SQL functions. Pick one and discard the other. The newer CORTEX_AI_FUNCTIONS_USAGE_HISTORY is the canonical source — it also covers AI_PARSE_DOCUMENT, which the older view misses.

The CORTEX_AGENT_USAGE_HISTORY view does not break out MCP-specific metadata.Agents invoked via the MCP server appear in this view, but the METADATA column contains interface and role context that varies by invocation path. If you need to distinguish MCP-sourced agent calls from direct API calls, parse the METADATA column and filter by interface type. The view’s REQUEST_ID is your correlation key for tying a row to a specific conversation turn.

Cortex Search’s serving compute does not appear in CORTEX_AI_FUNCTIONS_USAGE_HISTORY.The continuous GB/month idle charge for Cortex Search is a separate billing meter in CORTEX_SEARCH_SERVING_USAGE_HISTORY. If your monitoring queries only touch the functions view, you have a blind spot on one of the most surprising cost items in the Cortex stack. Add a separate daily roll-up query against the search serving view and alert separately.

The 5-minute latency means the hourly Task and Alert windows have a gap.The usage view has up to 5 minutes of latency. An hourly Task that fires at :00 will not see credits consumed at :58. For runaway detection this is mostly fine — you’re looking for hours of accumulation, not minutes. For per-user limits on very tight budgets, factor this in: a user who hits their limit at 11:58 PM may run one more minute before the midnight Task catches them.

QUERY_TAG is your best cost attribution tool — but only if you set it.CORTEX_AI_FUNCTIONS_USAGE_HISTORY includes a QUERY_TAG column. If teams set ALTER SESSION SET QUERY_TAG = 'project:data-quality team:analytics' before their Cortex calls, you can group spend by project or team in your monitoring queries without any schema changes. Without it, you’re attributing by user alone, which falls apart when service accounts or shared roles invoke the functions.

The One Principle

“Build your Cortex monitoring stack before you scale usage, not after the first surprise bill. The views exist, the alert patterns are documented — the only cost is an afternoon of setup.”

FAQ

Do Snowflake Resource Monitors cover Cortex AI Credits?

No. Resource Monitors only track Platform Credits consumed by virtual warehouses. Cortex AI Credits are a separate billing currency and require separate monitoring via CORTEX_AI_FUNCTIONS_USAGE_HISTORY and Snowflake Alerts. This is the most common gap in Cortex cost governance — teams assume their existing resource monitors will catch AI overage, and they don’t.

Which view should I use to monitor all Cortex AI costs in one place?

No single view covers everything. Use CORTEX_AI_FUNCTIONS_USAGE_HISTORY for AI SQL functions, CORTEX_AGENT_USAGE_HISTORY for agent workloads, and CORTEX_SEARCH_SERVING_USAGE_HISTORY for the Search idle serving charge. Join or union them in a dashboard for a complete picture, but never sum the older CORTEX_AISQL_USAGE_HISTORY alongside the newer functions view — that creates double-counting.

How do I set per-user spending limits for Cortex AI?

The approach is role-based: revoke SNOWFLAKE.CORTEX_USER from the PUBLIC role, create a dedicated AI_FUNCTIONS_USER_ROLE, and grant it only to users you’ve provisioned in an access control table with individual monthly credit limits. An hourly Snowflake Task then queries CORTEX_AI_FUNCTIONS_USAGE_HISTORY, identifies users who have exceeded their limit, and revokes the role automatically. A second monthly Task restores access on the first of each month.

Can I cancel a runaway Cortex AI query automatically?

Yes, using SYSTEM$CANCEL_QUERY called from a stored procedure that an hourly Task triggers. The detection logic aggregates credits by query ID across hourly windows in CORTEX_AI_FUNCTIONS_USAGE_HISTORY and checks that BOOLOR_AGG(is_completed) = FALSE — confirming the query is still running. Cancellation stops further accumulation but does not refund credits already consumed up to that point.

How do I monitor Cortex Agents specifically?

Use SNOWFLAKE.ACCOUNT_USAGE.CORTEX_AGENT_USAGE_HISTORY, which went GA on February 25 2026. Each row represents one agent request and includes both aggregated credit totals and granular sub-call detail for every tool the agent invoked (Analyst, Search, SQL). For conversation-level traces and spans, query SNOWFLAKE.LOCAL.AI_OBSERVABILITY_EVENTS using the request ID as the correlation key.

What is QUERY_TAG and why does it matter for Cortex monitoring?

QUERY_TAG is a session-level metadata field that appears in CORTEX_AI_FUNCTIONS_USAGE_HISTORY. When your pipelines set it with ALTER SESSION SET QUERY_TAG = 'project:X team:Y' before Cortex calls, you can group token spend by project, team, or feature in your monitoring queries without any schema changes. Without it, you’re limited to attributing costs by user ID, which breaks down for service accounts and shared roles.

September 16, 2026

Using MCP Servers with Snowflake: A Practitioner’s Guide
Your data team ships a Cortex-powered analytics agent. Works beautifully. Then the platform team wants to plug in Cursor. The ML team asks about GPT-4o. A product manager hears about Claude Desktop and sends a Slack message. Suddenly you’re the person maintaining four different Snowflake connectors, each with its own auth token, its own privilege model, and its own way of quietly breaking on a Tuesday morning.

The Snowflake-managed MCP server is the solution to that maintenance sprawl. Generally available since November 2025, it gives every AI client — Claude, Cursor, ChatGPT, any LangChain agent — one governed, OAuth-secured endpoint into your Snowflake account. You define which tools are visible, which roles can invoke them, and the MCP server enforces that contract for every client simultaneously. No custom connectors. No separately rotated tokens. No over-privileged service accounts.

This guide covers the architecture, the full setup sequence, the tool types you can expose, and — more importantly — the gotchas that aren’t in the quickstart.

TL;DR
- → The Snowflake-managed MCP server is a first-class Snowflake object (CREATE MCP SERVER) that exposes Cortex Analyst, Cortex Search, Cortex Agents, SQL execution, and custom UDFs/stored procedures as MCP-callable tools through a single HTTPS endpoint.
- → It implements MCP spec revision 2025-11-25 and as of August 20, 2026, returns tools/call responses as a Server-Sent Events (SSE) stream — your client must send Accept: application/json, text/event-stream.
- → Authentication uses Snowflake OAuth by default; you can bind to an external IdP (Okta, Entra ID) by setting OAUTH_AUTHORIZATION_SERVER at the schema, database, or account level.
- → USAGE on the MCP server is not the same as access to its tools. Each tool requires its own privilege grant — USAGE on the Agent, SELECT on the Semantic View, USAGE on the Search Service, etc.
- → Claude and ChatGPT always request session:role:all, which maps to the user’s DEFAULT_ROLE — set that role explicitly and ensure the user has a DEFAULT_WAREHOUSE set, or the session will fail to initialize.
- → Each MCP server supports a maximum of 50 tools; responses are truncated at 250 KB; and MCP server objects are not replicated in failover groups — recreate them on the secondary account manually.
- → There is no separate billing line for the MCP server itself — you pay the underlying Cortex AI token costs and warehouse compute that the tools trigger.
What the MCP Server Actually Is

Model Context Protocol is an open standard for how AI clients discover and invoke tools on external systems. Think of it as the HTTP of agent integrations: one protocol that every compliant client understands, instead of bespoke connectors for every combination of agent and data source. Every major AI IDE (Cursor, Windsurf), every frontier model host (Claude, GPT-4o), and a growing ecosystem of agent frameworks already speak MCP natively.

The Snowflake-managed MCP server sits inside your Snowflake account as a native database object — not external middleware you run and scale yourself. Snowflake hosts it, routes requests through your existing RBAC policies, and wires it to your Cortex resources. When a client connects, it gets a tool list scoped to whatever the connecting user’s role is allowed to see. When it calls a tool, Snowflake enforces the same governance controls as any other query against that resource.

The contrast with the old approach is stark. If you previously connected Claude Desktop to Snowflake via a custom Python script, and then wanted Cursor to have access, you’d write a second connector — different auth mechanism, different privilege model, a second thing to break. The MCP server collapses all of that into one object you configure once.

The Five Tool Types You Can Expose

The MCP server spec lists five tool types, and choosing the right one for each use case is non-obvious. Here’s what each actually does and when to reach for it.

CORTEX_AGENT_RUN — the recommended default

Snowflake’s own documentation is explicit: for business data applications that need governed orchestration, expose a Cortex Agent as the client-facing tool, not Cortex Analyst or Cortex Search directly. The agent orchestrates sub-tools internally, the external MCP client sends one message and gets one response, and you configure the agent’s resource access once in the agent definition rather than per-tool in every MCP server spec. The response payload includes intermediate reasoning traces, tool calls, and citations — which can exceed 200 KB for agent calls with large search results. Use max_results on the agent’s search resources to keep payloads sane.

CORTEX_ANALYST_MESSAGE — natural language to SQL

Directly exposes a Cortex Analyst semantic view. The client sends a natural language question, Analyst generates a SQL statement, and that SQL is returned to the client (not executed). The client then decides what to do with the SQL. This is the right tool when the MCP client has its own execution layer, or when you want the human to review the generated SQL before it runs. If you want Analyst results without a round trip, use a Cortex Agent with an Analyst tool configured internally.

CORTEX_SEARCH_SERVICE_QUERY — vector search over docs

Exposes a Cortex Search service. The client passes a query string and optional column filters; the search service returns ranked results. This is the RAG retrieval leg — pair it with an agent or with the client’s own synthesis layer. If you’ve already built a Cortex Search service for a RAG pipeline, adding it to an MCP server is one additional block in the spec YAML.

SYSTEM_EXECUTE_SQL — raw SQL execution

The most powerful and most dangerous tool type. The client passes arbitrary SQL, and Snowflake executes it. Set read_only: true in the config unless you genuinely need writes, and always set a query_timeout. If you expose this tool directly without a Cortex Agent in front of it, your governance boundary is the MCP client’s prompt discipline — which is not a governance boundary at all. Treat this as an escape hatch for internal tooling, not a default for agent access.

GENERIC — UDFs and stored procedures

Wraps any Python UDF or stored procedure as an MCP-callable tool. You define an input_schema in JSON Schema format, and the MCP client passes arguments that Snowflake validates before execution. This is where custom domain logic — a pricing calculator, a compliance checker, a data quality scorer — becomes available to any AI client without duplicating the logic into a prompt or a custom API endpoint.

Setup: From Zero to Working Connection

The full sequence is four steps: create the OAuth security integration, create the MCP server object, grant privileges, and connect the client. The OAuth step is where most teams get tripped up, so it gets most of the space below.

Step 1 — Create the OAuth security integration
```
CREATE OR REPLACE SECURITY INTEGRATION snowflake_mcp_oauth
  TYPE = OAUTH
  OAUTH_CLIENT = CUSTOM
  ENABLED = TRUE
  OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
  -- Claude.ai uses this callback; Claude Desktop uses a localhost URI
  OAUTH_REDIRECT_URI = 'https://claude.ai/api/mcp/auth_callback'
  OAUTH_USE_SECONDARY_ROLES = NONE     -- recommended for MCP
  ALLOWED_ROLES_LIST = ('mcp_access_role');

-- Retrieve the client ID and secret for client configuration
SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('SNOWFLAKE_MCP_OAUTH');
```
The OAUTH_USE_SECONDARY_ROLES = NONE setting is Snowflake’s explicit recommendation for MCP. With IMPLICIT, the session inherits the user’s default secondary roles, which can silently grant broader access than you intended. Keep it NONE and scope the mcp_access_role exactly to what the agent needs.

Step 2 — Create the MCP server object
```
-- Recommended pattern: expose a Cortex Agent as the single client-facing tool
CREATE OR REPLACE MCP SERVER analytics_db.agents_schema.business_mcp
  FROM SPECIFICATION $$
  tools:
    - title: "Business Data Agent"
      name: "business_data_agent"
      type: "CORTEX_AGENT_RUN"
      identifier: "analytics_db.agents_schema.business_agent"
      description: "Answers questions about revenue, customers, and products
                    using governed Snowflake data. Use for any structured
                    business data query."
  $$;

-- Check it's there
DESCRIBE MCP SERVER analytics_db.agents_schema.business_mcp;
```
The description field is not documentation — it’s how the MCP client decides which tool to invoke when multiple tools are listed. Make it specific and domain-scoped. “Answers questions about data” is noise. “Answers questions about Q4 revenue by region using the finance semantic view” is signal.

Step 3 — Grant privileges
```
-- Role structure
CREATE ROLE mcp_access_role;
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_AGENT_USER TO ROLE mcp_access_role;

-- Warehouse and schema access
GRANT USAGE ON WAREHOUSE analytics_wh       TO ROLE mcp_access_role;
GRANT USAGE ON DATABASE analytics_db        TO ROLE mcp_access_role;
GRANT USAGE ON SCHEMA analytics_db.agents_schema TO ROLE mcp_access_role;

-- MCP server itself
GRANT USAGE ON MCP SERVER analytics_db.agents_schema.business_mcp
  TO ROLE mcp_access_role;

-- The Agent the server exposes
GRANT USAGE ON AGENT analytics_db.agents_schema.business_agent
  TO ROLE mcp_access_role;

-- Resources the agent uses internally
GRANT SELECT ON SEMANTIC VIEW analytics_db.finance_schema.revenue_semantic
  TO ROLE mcp_access_role;
GRANT USAGE ON CORTEX SEARCH SERVICE analytics_db.docs_schema.product_docs
  TO ROLE mcp_access_role;

-- Assign to users and set defaults
GRANT ROLE mcp_access_role TO USER analyst_user;
ALTER USER analyst_user
  SET DEFAULT_ROLE = 'mcp_access_role'
      DEFAULT_WAREHOUSE = 'analytics_wh';
```
Step 4 — Connect the client

Every MCP client takes the same endpoint format:
```
https://<account_url>/api/v2/databases/analytics_db/schemas/agents_schema/mcp-servers/business_mcp
```
For Claude Desktop or Claude.ai, navigate to Settings → Connectors → Add custom connector, paste the URL, add the client ID and secret from the security integration, and complete the OAuth flow. For Cursor, add the block to your MCP config JSON and sign in via the MCP settings panel. For any HTTP-based client, include Accept: application/json, text/event-stream in the tools/call request header — the server has streamed SSE responses since August 20, 2026, and clients that send only application/json will get unexpected responses.

The Gotchas Nobody Warns You About

USAGE on the MCP server does not grant access to the tools inside it.The MCP server has its own access layer and each tool has its own separate privilege layer. A role with USAGE on the MCP server can connect and discover the tool list — but invoking a tool without the appropriate underlying grant returns an authorization error. This surprises every team the first time. Audit: SHOW GRANTS ON MCP SERVER <name> will not show you tool-level grants. You have to check each underlying object separately.

Underscores in your account hostname will silently break client connections.Snowflake’s own documentation flags this: use hyphens (-) instead of underscores (_) in account hostnames when configuring MCP clients. Older Snowflake account identifiers often use underscores. The error this produces is a generic connection failure, not an informative message about the hostname format. Check the account URL first if a client refuses to connect after OAuth completes.

Claude and ChatGPT always request session:role:all, regardless of your OAUTH_SCOPES_SUPPORTED setting.That scope resolves to the user’s DEFAULT_ROLE. If you haven’t explicitly set DEFAULT_ROLE to the mcp_access_role — or if the user has no DEFAULT_WAREHOUSE set — the session fails to initialize and the error is “session initialization failed,” which tells you nothing useful. Fix both before debugging anything else.

Agent tool responses can easily exceed 200 KB.When an agent uses Cortex Search, the response includes intermediate steps: reasoning traces, search results, citations. Large result sets push the payload well above 200 KB. The 250 KB truncation limit is enforced by the MCP server, so you may get partial responses without a clear error. Mitigate by setting max_results in the agent’s search tool configuration to something in the range of 3–5 for conversational agents.

Agent loops through MCP can hit the 10-invocation recursion limit.If an external client calls a Cortex Agent through MCP, and that agent invokes another MCP server that calls back into a Cortex Agent, you have a recursive loop. Snowflake enforces a hard limit of 10 invocations and then errors. This is more common than you’d think once teams start chaining agents — especially if an agent orchestration pattern grows organically from a single-agent prototype.

Network policies block MCP client IP ranges, not the end user’s IP.Remote MCP clients like Claude.ai and ChatGPT connect from their provider’s infrastructure, not from the end user’s browser. If your Snowflake account has network policies enabled and the MCP client’s outbound IP range isn’t in the allow list, the OAuth token request returns error: invalid_client — the same error as a bad client secret. Check the network policy before assuming authentication misconfiguration. Anthropic publishes Claude’s outbound IP addresses; other providers do the same.

What the MCP Server Doesn’t Do (Yet)

The Snowflake MCP server currently supports only tool capabilities from the MCP protocol. Resources, prompts, roots, notifications, version negotiation, lifecycle phases, and sampling are not supported. This matters if you’re comparing it against other MCP server implementations — some support resource subscriptions or prompt templates. Snowflake’s managed implementation is production-grade on the tools axis but doesn’t yet surface the broader protocol surface.

MCP server objects are also not replicated in failover groups. OAuth security integrations are replicated, but the MCP server definition itself lives only on the account where it was created. If you’re running a multi-account setup with failover configured, you’ll need to recreate MCP server objects on the secondary account as part of your DR runbook — this is an easy thing to forget until you need it.

For teams evaluating the Snowflake-managed approach against the self-hosted Snowflake Labs MCP server: the managed version handles infrastructure and OAuth for you, but you trade infrastructure control for that convenience. The self-hosted option is worth considering if you need full control over authentication flows, custom middleware, or deployment in environments where Snowflake’s hosted endpoint doesn’t satisfy data residency requirements.

The One Principle

“Configure the agent, not the connector. The MCP server is governance infrastructure — define it once, scope it tightly, and let every AI client inherit the same rules rather than building a new integration surface for each one.”

Related reading: MCP explained at three levels · Governing AI agents in Snowflake · Building RAG with Cortex Search · What actually works when building AI agents · Cortex Code and dbt optimization · AI coding agents and pipeline security · Snowflake MCP server docs (official)
September 15, 2026
From ETL Pipelines to Data Products: Designing Reusable Data Infrastructure for Enterprise AI
Enterprise data platforms often begin with a simple objective: move data from operational systems into a place where it can be analyzed. Over time, however, the number of data sources, consumers, and business requirements grows. A pipeline originally created for one dashboard becomes useful to another team. A transformation developed for a reporting workload is recreated for an application. An AI team builds yet another pipeline because the existing data was not structured for its requirements.

The problem is not that organizations have too few pipelines. In many mature environments, they have too many pipelines performing overlapping work.

This creates a different challenge for data engineering: how do we build data infrastructure that can be reused across analytics, applications, and AI without turning every new requirement into another independent pipeline?

One answer is to move from thinking primarily about ETL pipelines toward thinking about data products.

A data product is not simply a table in a warehouse or a dataset stored in a lake. It is a reusable data asset with defined meaning, ownership, quality expectations, metadata, lineage, and consumers. The objective is to make the data useful beyond the specific pipeline that originally produced it.

The Problem with Use-Case-Specific Pipelines

Traditional ETL architectures are often organized around downstream requirements. A team receives a request for a report, builds an extraction and transformation process, and produces the required dataset. Another team later needs similar information and creates another pipeline because its requirements are slightly different.

At first, this approach is reasonable. The system is small, the requirements are clear, and the fastest solution is often to build exactly what is needed.

The difficulty appears as the organization grows.

Multiple pipelines may independently extract the same source data, apply similar business rules, and create slightly different versions of the same business entity. One pipeline may define an active customer differently from another. One dashboard may calculate revenue using one transformation while another application uses a different version.

Eventually, the organization has a collection of pipelines that individually work but collectively create a difficult data environment.

The goal of a data product approach is not to eliminate pipelines. Pipelines remain essential. The change is in what the pipeline is designed to produce.

Instead of building a pipeline exclusively for one downstream consumer, the pipeline can contribute to a reusable data asset with clearly defined characteristics.

Figure 1. The evolution from use-case-specific ETL pipelines toward reusable data products.

From Pipelines to Data Products

The distinction is subtle but important.

A pipeline describes how data moves and changes.

A data product describes what trusted data is made available for others to use.

For example, an organization may have customer, order, inventory, or product information arriving from multiple operational systems. Instead of creating separate transformations for every consumer, the platform can produce a curated data product representing a well defined business concept.

That product should answer basic questions before another team consumes it:
- What does this data represent?
- Who owns it?
- How frequently is it updated?
- What quality expectations does it have?
- What transformations have been applied?
- Where did the data originate?
- Which downstream systems depend on it?
- How should consumers interpret important fields?
This turns the dataset from an anonymous technical output into something that other teams can confidently build upon.

The distinction becomes particularly important when AI systems enter the architecture. AI applications need access to enterprise information, but simply exposing more raw data does not necessarily produce better results. The data must have consistent meaning, appropriate granularity, and enough context for the consuming system to use it correctly.

Designing the Architecture for Reuse

A reusable data architecture does not require one enormous centralized pipeline. Instead, it separates concerns while establishing clear interfaces between layers.

A typical architecture can begin with operational databases, APIs, files, event streams, and other enterprise sources. An ingestion layer brings that information into the platform, where raw data can be preserved before transformation.

Transformation and quality processes then produce curated datasets. The important difference is that these curated datasets are designed as reusable products rather than temporary outputs for one report.

Figure 2. A reusable data product architecture separates ingestion, transformation, quality, governance, and consumption.

The architecture can support multiple consumers from the same trusted data product.

Analytics teams may use it for dashboards and reporting. Applications may consume it through APIs or services. Data scientists may use it for machine learning workflows. AI systems may use it as part of retrieval, contextualization, or decision support workflows.

This does not mean every consumer receives exactly the same representation. Different consumers may require different interfaces or derived views. The important principle is that core business logic should not be unnecessarily duplicated.

Data Contracts Make Reuse Possible

Reusability becomes difficult when consumers do not know what they can rely on.

A data contract provides an explicit agreement between data producers and consumers about the expected characteristics of a data asset. At the simplest level, this can include schema and data types. In a mature environment, the contract can go further.

It can define expected semantics, ownership, freshness, acceptable values, compatibility expectations, and changes that require communication.

Consider a field called status.

From a technical perspective, a string is a perfectly valid datatype. But what does the string mean?

Does active mean an account is currently usable? Does it mean the customer has purchased something recently? Does it mean a subscription is paid?

Schema validation cannot answer that question.

For reusable data products, semantic consistency is as important as structural consistency.

A contract therefore becomes a mechanism for protecting consumers from unexpected changes while giving producers a clear responsibility for maintaining the data they publish.

Quality Is Part of the Product

Data quality should not be treated as a final step performed after a pipeline has been built.

If a dataset is intended to become a reusable data product, quality is part of the product itself.

Different products will require different checks, but common considerations include completeness, validity, uniqueness, consistency, and freshness.

For example, a product containing transactional information might need to detect duplicate records. A product supporting operational decisions might require strict freshness expectations. A product used for historical analysis may tolerate delayed updates but require strong consistency over time.

The important point is that quality expectations should be explicit and measurable.

This also changes how data engineers think about failures. Instead of asking only whether a pipeline completed successfully, engineers can ask whether the resulting data product continues to meet its defined expectations.

Metadata and Lineage Are Not Optional Extras

When organizations have hundreds of datasets, discovering what a dataset means can become as difficult as producing it.

Metadata helps answer questions such as where a dataset came from, what its fields represent, how frequently it changes, and who is responsible for it.

Lineage provides another important dimension: understanding how data moved through the system and which upstream sources contributed to the final product.

This becomes especially valuable when something changes.

If a source field is modified, engineers should be able to determine which transformations and downstream consumers may be affected. Without lineage, that investigation can become a manual search across pipelines and documentation.

For data products to remain reusable, discoverability and explainability need to be designed alongside the data itself.

One Data Product, Multiple Consumers

A major advantage of this approach is that the same trusted data foundation can support different types of workloads.

Figure 3. A reusable data product can support analytics, applications, and AI workloads without duplicating core transformation logic.

Consider a curated product representing a business entity such as a product, customer, transaction, or inventory position.

An analytics team might use it to create operational dashboards. An application might use the same information to support a workflow. An AI system might use it to provide context to an agent or model.

The consumers are different, but the underlying business definitions do not need to be reinvented each time.

This is where the concept becomes particularly powerful for enterprise AI.

Making Data Products Useful for AI

AI systems introduce a new category of data consumer.

Traditional analytical workloads often operate through structured queries and predefined metrics. AI applications may need to retrieve information dynamically, combine multiple pieces of context, interpret relationships, and use that information as part of an inference or action.

That places additional demands on the underlying data.

AI systems benefit from data that is:
- semantically consistent
- sufficiently granular
- appropriately contextualized
- discoverable
- governed
- fresh enough for the intended use case
- accessible through reliable interfaces
This does not mean every data product needs to be redesigned specifically for AI.

Instead, organizations should build reusable data foundations that can support AI as one of several consumers.

That distinction helps prevent a common architectural mistake: creating an entirely separate data ecosystem every time a new AI initiative appears.

Avoiding the “One Pipeline Per Use Case” Trap

The answer is not to centralize every transformation into one massive pipeline.

Over centralization can create its own problems. A change made for one consumer can unexpectedly affect many others. Teams may also become dependent on a central group for every modification.

A better approach is to identify which data assets and transformations are genuinely reusable.

Common business entities and shared definitions are strong candidates for reusable products. Highly specialized analytical logic may remain closer to the consuming workload.

The architectural question should therefore be:

What should be shared, and what should remain specific to the consumer?

Good data engineering is not about maximizing reuse at any cost. It is about finding the right boundaries.

Practical Principles for Building Reusable Data Infrastructure

Organizations beginning this transition can start with a few practical principles.

Build once, consume many times.
When multiple teams repeatedly implement the same business logic, investigate whether the underlying data should become a reusable product.

Define ownership early.
A reusable dataset without clear ownership eventually becomes nobody’s responsibility.

Treat metadata as part of the product.
Documentation, definitions, lineage, and discoverability are not administrative additions. They determine whether another team can actually use the data.

Make quality measurable.
Define expectations around freshness, completeness, validity, and other characteristics that matter to the product’s consumers.

Design for change.
Schemas, business rules, and upstream systems will evolve. Data products should have clear compatibility and change management practices.

Separate shared data from consumer specific logic.
Not every transformation needs to be centralized. Reuse the parts that represent stable, broadly useful business concepts while allowing downstream teams to build specialized views.

Conclusion

The evolution from ETL pipelines to data products is not about replacing one technology with another. It is a shift in how organizations think about the outputs of data engineering.

A pipeline can successfully move data from one system to another and still create little long term value if every downstream consumer must interpret, validate, and transform that data independently.

A data product takes a different approach. It treats trusted data as a reusable enterprise capability with defined meaning, quality expectations, ownership, metadata, and lineage.

That approach becomes increasingly important as organizations add AI systems to their technology landscape. AI does not eliminate the need for sound data infrastructure. It increases the number of ways that trusted enterprise data can be consumed.

The mature data platform, therefore, is not simply a collection of pipelines.

It is an ecosystem of reliable data products that allows analytics, applications, and AI systems to build on the same trusted foundation.
August 26, 2026
ETL vs. ELT vs. Reverse ETL: A Practitioner’s Framework for Choosing the Right Pattern for a Given Workload

As a data engineer, you’ve likely been in a design review where someone says at the very end, “We should just do ELT for everything!”. You are a data engineer, and you’ve probably been in a design review where you heard someone say at the end, “We should just do ELT for everything!”. Or you’ve inherited a package in an old 10-year-old version of SSIS that you would have to pick apart row-by-row in a painful manner, and the business is wondering why the “modern” warehouse team can’t simply replace it overnight. The industry has morphed ETL, ELT and now Reverse ETL into tribal identities: pick one, fight about it in slack threads, ship. But that’s backwards. The pattern is not the personality, it is a means to an engineering end.

The reality, though, is that ETL, ELT, and Reverse ETL are not mutually exclusive approaches — they are three different kinds of pipelines that are tackling three different problems and most production data pipelines require all three to be running concurrently. Don’t identify a winner. It’s to align the pattern with the limitations of the workload: data sensitivity, complexity of transformation, latency requirements, and the most cost-efficient place to execute the workload.

The Problem Deep Dive

All three patterns use the same three verbs—extract, load, transform—but in different orders: that’s why it’s a bit of a muddle.

ETL (Extract, Transform, Load): ETL is the process of moving data into a staging area, where it is transformed in some way outside of the target system before it is loaded into the clean data in the target system. This was the standard practice for decades as target systems or data warehouses, particularly transactional databases, would be costly to compute on and required to be buffered from raw and dirty data. This pattern has become the backbone of careers such as those of tools like SSIS, Informatica, and Talend.

ELT (Extract, Load, Transform) first loads raw data into the destination, and then transforms it-while-stored using compute functions on the destination. It really only became dominant when cloud warehouses (Snowflake, BigQuery, Redshift, Azure Synapse) separated storage from compute and started to offer cheap ways to store raw data and transform it later, incrementally, using tools like dbt.

Reverse ETL moves the already transformed, already modeled data out of the warehouse and into operational systems for business teams to take action: Salesforce, HubSpot, Braze (an ad platform). It’s more of a third point on the same spectrum; it’s the vehicle for the other two, which is a problem that neither ETL nor ELT were intended to solve—getting warehouse truth into the tools where other things and humans are happening.

The sticking point is that teams find themselves using these as “short cuts” rather than as decisions based on the workload:

· Failure of compliance due to ELT by-default. A healthcare team loads raw PII into a warehouse without masking or tokenization as that is the way modern data stacks work.A healthcare or fintech team places the raw PII in a warehouse without masking or tokenization first. Now, without the mask, SSNs or PHI are stored in raw schemas that are accessible to half the analytics org and the cost of the compliance retrofit outweighs the transform step. This is the same for ETL’s pre-load transformation; scrub before, don’t scrub after it lands.

· Runaway warehouse costs due to misusing ELT. A team pushes a full nightly extract of a 500-million-row transactional table into Snowflake, and uses dbt models to re-scan the data on every run, rather than incremental models. The warehouse bill expands due to the compute moving from a dedicated ETL server to the meterized cloud credits, without any one looking at the meter.

· Operational data that has been stale due to the lack of Reverse ETL. A customer lifetime value model is created in the marketing team’s warehouse, but not piped back to the CRM. The warehouse model has no way to get back out of Salesforce, so the Sales reps still see the raw purchase counts. The insight is there, but it isn’t there where the person needs it at decision time.

· ETL (Row-by-row) instead of ELT (Set-based). Some classic SSIS or legacy on-prem pipelines where they were changing records one by one inside the pipeline, and the same transformation can be written as a simple set-based SQL statement in the target warehouse and run in a fraction of the time.

All these are the correct tools for the wrong jobs — but not bad tools!

The Solution: A Decision Framework

Ask 4 questions about each workload, instead of “which pattern do we standardize on?”. In reality, all three patterns are implemented together on various pipelines on most platforms.

1. Is the data required to be scrubbed, masked, or filtered before it reaches any place it can be queried? If yes — PII, PHI, cardholder data, anything under GDPR/HIPAA/PCI scope — transform before load. This remains ETL’s main reason to be, regardless of the ELT fashions. Mask/tokenize in the extraction layer (Azure Data Factory data flows, or a simple Python/SQL Server SSIS step), meaning that any raw sensitive values never reach the raw schema of the warehouse. For those on the Microsoft stack, this is the typical best case scenario for maintaining ADF or SSIS in the mix in an otherwise ELT-focused Azure Synapse or Fabric pipeline, instead of removing it from the mix because dbt is cool.

2. Does the transformation require a lot of steps and iterations and needs to be versioned, tested, re-run by analysts? If yes, use ELT. Bring in raw (or lightly scrubbed) data to the warehouse and leave the work of in-place modeling to dbt, stored procedures or Synapse/Databricks notebooks. You have version-controlled transformation logic, built-in automated testing, lineage graphs, and can rebuild history without re-extracting from a fragile source system when someone discovers a bug in the transformation.

The most important decision when creating a model that causes cost blowouts is whether to run it incrementally (only the new or changed rows since the last run) or not. The key for teams transitioning from SQL Server/SSIS to a dbt-style ELT mindset is to get this concept in their heads early: a transformation is no longer “a step in a pipeline,” it’s a “materialized view” that needs to be refreshed, and it’s that refresh strategy where most of the warehouse bill lives or dies.

3. What is the latency requirement and is it variable with each hop? Batch analytics (nightly board reporting) does not require ELT’s load then transform lag. Extracting and initial transforming typically occur within a streaming layer (Kafka/Event Hubs + stream processing) before anything enters a warehouse, or in the context of ETL, transform early.

4. Is there a need for this insight to act upon outside of the warehouse for a human or downstream system(s)? Whether the data pipeline was ETL or ELT, without Reverse ETL the value of the data is lost once it’s returned. Tools such as Census, Hightouch, or a scheduled Azure Function or Logic App that read from a warehouse view and write to an API fill in the gap. Model it once, sync it wherever it needs to act it out. What this typically involves in practice is creating a single, well-governed model in the warehouse, e.g. a “customer health” or “customer segment” view, which categorizes each customer as “at-risk”, “high-value” or “standard” according to recency and lifetime spend, and then letting a Reverse ETL tool match that segment field with a custom field in the CRM, following a set schedule. Never again will any engineer have to create a CSV to Salesforce, and a sales rep’s view of the segment is always up to date with the warehouse model that powers it.

In reality: streaming or batch extraction with PII scrubbed at the source (ETL) → raw but safe data deposited in the warehouse (ELT) → curated marts synced back to operational tools (Reverse ETL). Three patterns, one pipeline, for each one of them it is really good at doing.

Proof: What This Looks Like in Practice

The initial design on a retail insight app I was working on was ELT only – raw order and customer data was coming straight from Salesforce and the transactional database into Snowflake and all masking and transformation was being done downstream in dbt. Until a compliance audit brought up the fact that raw customer PII was accessible to any analytics use case with access to the warehouse, which also accessed some fields that never saw use in any analytics use case.

But it wasn’t about giving up on ELT. It was putting in a thin ETL step at extraction: an Azure Data Factory data flow that was stripping out PII fields before putting data into the raw schema, and everything else flowed directly through to dbt for modeling. Access to detokenized values was restricted to a few service accounts.

The measurable outcome: compliance gap was closed without any action on the 40+ dbt models that were already deployed, as the business logic that needed to be changed didn’t need to be moved. Compute cost was not impacted — the masking step did not cost anything in the warehouse compute, it did on the extraction layer. The team then integrated an hour-by-hour (via Hightouch) Reverse ETL sync to move the customer health segment view from the marketing platform into the CRM – removing a weekly manual CSV export performed by a marketing analyst every Monday. None of these three required giving up the other changes, and each pattern was used precisely where the compromises were warranted.

The Close

ETL, ELT, and Reverse ETL are not competing architectures for your loyalty, but three tools to address three different questions: what must be cleaned before it is put into the warehouse, what is less expensive to transform when compute resides there, and what must come out of the warehouse to have an impact? The ones who are burned are those who choose one pattern and apply all of their workloads through it.

The next time you are thinking of setting up a pipeline, don’t ask “are we an ETL shop or an ELT shop?”. For each workload: Does it need scrubbing before it lands? Is the transform complex enough to benefit from version control and incremental materialization? Does it need to be materialized at extraction time (latency) given that output needs to walk back out of the warehouse to do any good? When answering the four questions truthfully for each data flow, the correct pattern — typically multiple patterns — emerges spontaneously.

August 26, 2026
Data Engineering ROI: How to Justify Your Data Platform Investment to the Board

Every data engineering leader has sat through the same meeting: the platform work is done, the pipelines are stable, but defending its value to the board is difficult because it rarely appears on a single P&L line. Data platform budgets are often treated as discretionary IT spend, meaning they get cut first during a downturn. Getting ROI measurement right is what keeps your next initiative fundable.

Why Data Engineering ROI Is Hard to Measure (and Why It Matters)

Boards fund outcomes. Data engineering delivers infrastructure. That mismatch is the root of the problem.

A board can evaluate a sales tool by pipeline generated, or a marketing spend by cost-per-acquisition. Data platform investments don’t map that cleanly pipeline reliability, schema governance, and data quality improvements are foundational, meaning they enable other initiatives rather than generating value on their own. An Al model that improves fraud detection accuracy gets the credit; the governed, clean, well-lineaged data pipeline underneath it, without which the model wouldn’t have worked, gets none.

This isn’t just a communication problem. Left unaddressed, it becomes a funding problem. Data platform budgets get treated as discretionary IT spend, get cut first in a downturn, and then get blamed when the next Al initiative underperforms because the data underneath it was never solid. Getting ROI measurement right isn’t an exercise in optics, it’s what keeps the next initiative fundable.

Metrics That Actually Translate to Business Impact

The fix starts with picking metrics a board member without a data engineering background can actually interpret. A few that consistently translate well:

Data downtime cost avoided: Every hour a critical pipeline is down or serving bad data has a real cost delayed reporting, blocked decisions, or in regulated industries, compliance exposure. Tracking incidents avoided (or their reduced frequency after a platform investment) turns an abstract reliability improvement into a dollar figure.

Time-to-insight reduction: How long does it take from “we need this data” to “here’s the answer”? If that cycle shrinks from days to hours after a platform investment, that’s a directly measurable efficiency gain that maps to faster business decisions.

Engineering hours reclaimed from firefighting: A mature platform investment shows up as a shift in how engineers spend their time less time patching broken pipelines and chasing data quality issues, more time building new capabilities. That ratio, tracked before and after, is one of the cleanest ROI signals available.

Data quality incident rate: Fewer downstream errors caused by bad data, wrong numbers in a report, a broken dashboard, a flawed model input is a leading indicator of platform health that’s easy to track and easy to explain.

Cost-per-query or compute efficiency: For teams on modern cloud data stacks, tracking compute spend against query volume or data processed shows whether platform investments are actually improving unit economics, not just adding capability.

None of these require exotic instrumentation. Most are extractable from existing observability and cost-monitoring tools already in place. The work is in deciding which ones matter for a given business and tracking them consistently.

Connecting Data Initiatives to Business Outcomes

Metrics alone don’t make the case they need to be tied to a specific business decision or outcome of the platform investment enabled or unblocked.

The strongest version of this argument doesn’t say “we modernized our data stack.” It says: “faster, more reliable data pipelines cut our fraud review time from four hours to forty minutes,” or “consolidating our data sources let underwriting make decisions same-day instead of next-day.” Specific, traceable, and tied to something the board already understands the value of.

This only works if a baseline exists before the investment. Teams that skip measuring the “before” state lose the ability to prove improvement later, a gap worth closing at the start of any platform initiative, not after the fact when the board asks for numbers. A structured data-readiness assessment before a major platform investment is one of the more reliable ways to establish that baseline, since it forces a documented starting point across data quality, infrastructure, and governance maturity that the post-investment numbers can be measured against.

Framing matters too. An investment task built around “we need to modernize our data infrastructure” competes with every other infrastructure request in the budget cycle. An investment task built around “this unblocks same-day underwriting decisions” competes on the same terms as revenue-generating initiatives and tends to win more often.

Making the Case to the Board

When it’s time to present, resist the instinct to show everything. A board conversation isn’t the place for a full metrics dashboard, it’s the place for three or four numbers, chosen because they answer the two questions every board member is actually asking: why now, and what happens if we don’t.

“Why now” is answered by connecting the investment to a business pressure the board already recognizes regulatory deadlines, a competitor’s faster decision cycles, or a growth plan that the current data infrastructure can’t support. “What happens if we don’t” is answered by quantifying the cost of inaction: the downtime already being absorbed, the compliance exposure already being carried out, the engineering hours already being spent on maintenance instead of building.

This is a distinction we see play out constantly at Samta.ai, working with BFSI and regulated clients across Singapore. The teams that get board sign-off aren’t necessarily running the most technically impressive platforms, they’re the ones who walked into the room with a baseline, a business outcome, and a dollar figure attached to inaction.

A recent IDC-backed business value study on enterprise data platform investments found that organizations with mature data discovery and governance infrastructure consistently recovered platform costs through reduced analyst search time and fewer duplicate data efforts alone before counting any downstream Al or analytics gains. That’s the kind of framing that resonates with a board: cost recovery that doesn’t depend on a speculative future win.

August 25, 2026

Identifying Hidden Token Costs in Snowflake Cortex AI

The demo works. It always does. You call AI_CLASSIFY on a sample of 10,000 rows, the credits barely move, and someone in the room says “this is so much cheaper than sending data to an external API.” Three weeks later your first real workload hits production — a million rows, five label classes, a moderately verbose model — and the bill is three times what you modelled. Nobody touched the model. Nobody changed the prompt. The data volume was planned. What went wrong?

The short answer: Snowflake Cortex AI has three independent cost meters running in parallel, and two of them are nearly invisible until you go looking. The warehouse credit line your resource monitors watch? That’s only one of the three. The other two — AI token consumption and always-on serving compute — accumulate quietly in tables most engineers haven’t queried yet.

After the April 2026 introduction of AI Credits as a separate billing currency, the gap between what teams expect to pay and what actually lands on the invoice got wider, not narrower. This piece maps exactly where the hidden costs live, shows you the math on each one, and gives you the SQL to surface them before your finance team does.

TL;DR

Snowflake Cortex AI bills across three independent meters — warehouse compute, AI token consumption, and serving compute — and resource monitors only cover the first one.
Functions like AI_CLASSIFY, AI_SENTIMENT, and AI_SUMMARIZE silently inject a system prompt before your text, so the billed token count is always higher than the text you actually sent.
For AI_CLASSIFY, your label list is counted as input tokens for every single row processed, not once per call — a five-class classifier with verbose descriptions can multiply your expected token count by 2–4×.
Cortex Search charges a continuous serving-compute fee per GB of indexed data per month, regardless of whether any queries are running — a 70 GB corpus costs roughly $882/month at rest.
As of April 2026, AI Features bill in AI Credits ($2.00 global / $2.20 regional), which are separate from Platform Credits; the two currency types can coexist on the same bill and require different monitoring queries.
Query SNOWFLAKE.ACCOUNT_USAGE.CORTEX_FUNCTIONS_USAGE_HISTORY per function and model to find your real cost breakdown; do not try to sum multiple overlapping views or you will double-count.
Model selection is still the single largest cost lever — the same classification workload can differ by 10–60× in price depending on which model you choose.

Why the Demo Lied to You

The confusion starts with how Snowflake traditionally teaches cost intuition. For years, the mental model was: bigger warehouse = more credits = more cost. You learned to right-size warehouses, use auto-suspend, and watch the METERING_HISTORY view. That model works fine for compute-heavy SQL. It actively misleads you for Cortex AI.

When you run a Cortex AI function, the warehouse compute cost still applies — your VWH is active while the query runs, so those credits accumulate. But the AI token charges are separate, billed in a different currency against a different meter, and they show up in different Account Usage views. A demo on a SMALL warehouse processing 10,000 rows barely registers on either meter. A production run of one million rows with a frontier model is a completely different animal.

One well-documented real-world example: a team processed 1.18 billion records using Cortex Functions and received a single-query bill of nearly $5,000 — almost entirely from token costs, with minimal warehouse compute. Their resource monitors never triggered because resource monitors don’t watch the AI token meter. The bill simply appeared.

The April 2026 billing restructure added another wrinkle. Snowflake introduced AI Credits as a separate billing currency, flat-priced at $2.00 per credit for global routing or $2.20 for regional routing, independent of your Snowflake edition. This means an Enterprise customer and a Standard customer pay exactly the same rate for AI inference — but the two credit types appear as separate line items and require separate monitoring logic. If you built a cost dashboard before April 2026, it is almost certainly incomplete.

The Token Inflation You’re Not Accounting For

Most engineers assume “tokens billed = tokens in my text.” For AI_COMPLETE with a hand-written prompt that assumption is roughly correct. For the structured AI functions — AI_CLASSIFY, AI_SENTIMENT, AI_FILTER, AI_AGG, AI_SUMMARIZE, AI_TRANSLATE — it is wrong in ways the documentation buries in a footnote.

According to Snowflake’s official cost documentation, these functions add a system prompt to your input text before sending it to the model. The billed token count is therefore always higher than the number of tokens in the text you provide. You pay for the system prompt on every row. You have no visibility into how long that system prompt is. You cannot opt out.

For AI_CLASSIFY specifically, the hidden cost compounds further: your label list, descriptions, and examples are counted as input tokens for every record processed, not once per call. If you have five label classes with 30-word descriptions each, you’re paying for roughly 150 extra tokens on every single row. Run that against a million-row table and you’ve added 150 million tokens of cost that had nothing to do with your data.

The fix is to measure before you scale. Snowflake provides a AI_COUNT_TOKENS function that reports token counts without incurring LLM charges — use it on a sample to calibrate your label overhead before committing to a full-table run:

-- Estimate label overhead before running AI_CLASSIFY at scale
SELECT
  COUNT(*) AS sample_rows,
  AVG(SNOWFLAKE.CORTEX.AI_COUNT_TOKENS(
    'llama3.1-8b',
    your_text_column
  )) AS avg_text_tokens,
  -- Add your label string manually to see the combined token count
  AVG(SNOWFLAKE.CORTEX.AI_COUNT_TOKENS(
    'llama3.1-8b',
    your_text_column || ' CATEGORIES: positive, negative, neutral, urgent, spam'
  )) AS avg_with_labels_tokens
FROM your_table
LIMIT 5000;

The gap between avg_text_tokens and avg_with_labels_tokens is your label overhead per row. Multiply by row count and by the per-million-token rate for your chosen model to get a cost estimate before you fire the real query. This takes five minutes and can prevent a four-figure surprise.

The Two-Currency Problem

Before you can build a cost dashboard, you need to understand which features bill in which currency — because the monitoring SQL differs by type.

Cortex Feature	Credit Type	Billing Dimension	Primary Usage View
AI Functions (AI_COMPLETE, AI_CLASSIFY, AI_EMBED, etc.)	AI Credit	Per million tokens (input + output)	`CORTEX_FUNCTIONS_USAGE_HISTORY`
Cortex Agents	AI Credit	Per million tokens; additive across sub-calls	`CORTEX_AGENT_USAGE_HISTORY`
Cortex Search (serving)	AI Credit	Per GB indexed per month, continuous	`CORTEX_SEARCH_SERVING_USAGE_HISTORY`
Cortex Search (embedding)	AI Credit	Per token on insert/update	`CORTEX_SEARCH_SERVING_USAGE_HISTORY`
AI Parse Doc	AI Credit	Per 1,000 pages; each page = 970 tokens	`CORTEX_DOCUMENT_PROCESSING_USAGE_HISTORY`
Cortex Analyst API (standalone)	Platform Credit	Per 1,000 messages	`METERING_DAILY_HISTORY`
Cortex Fine-tuning	Platform Credit	Per compute job	`METERING_DAILY_HISTORY`
Virtual Warehouse (any query)	Platform Credit	Per second, 60-second minimum	`WAREHOUSE_METERING_HISTORY`

The important detail: Cortex AI Functions like AI_COMPLETE stack two meters simultaneously. You pay AI Credits for the tokens, and you pay Platform Credits for the warehouse time your query consumed. A query that takes 30 seconds on a MEDIUM warehouse and processes 500,000 tokens is billing on two completely separate ledgers. Neither one cancels the other. Snowflake’s recommendation is to use no larger than a MEDIUM warehouse for Cortex AI calls, because a larger warehouse doesn’t speed up token processing — it just burns more Platform Credits for the same result.

The Cortex Search Idle Tax

Cortex Search is architecturally different from the AI SQL functions. It’s a managed vector-search service: you create a search service over a table, Snowflake indexes it, and you query it via a REST call or through Cortex Agents. The billing model reflects this — and it’s the most surprising line item for teams that build and then deprioritize a search-based RAG feature.

Cortex Search’s serving compute bills continuously per GB of indexed data per month, while the service is resumed — whether or not any queries are running. The Snowflake pricing documentation confirms this: “A running search service incurs costs even when it isn’t serving queries.” Based on the Service Consumption Table, the serving rate is 6.3 AI Credits per GB per month. At the global AI Credit price of $2.00, that’s $12.60 per GB per month, every month, at rest.

Run the math for a team that has multiple Cortex Search services:

Scenario	Indexed Data (GB)	AI Credits/mo	Cost/mo (global)
Single knowledge base (small)	20 GB	126 Cr	$252 / mo
Single knowledge base (medium)	70 GB	441 Cr	$882 / mo
5 domain services × 70 GB	350 GB	2,205 Cr	$4,410 / mo
Dev service (left running)	30 GB	189 Cr	$378 / mo (wasted)

The dev service row is where most teams first notice the problem. Someone spun up a search service in a development environment to prototype a chatbot, the project shifted priorities, and the service kept running. It doesn’t consume query tokens because nobody’s hitting it. It consumes serving compute because it exists. That’s $378/month for a service that produced zero output in that billing period.

The mitigation is straightforward: configure AUTO_SUSPEND on any search service that has predictable idle windows, and manually suspend development services when a feature is deprioritised. Snowflake Batch Search is an alternative for workloads that don’t need real-time retrieval — its serving compute runs only during the batch job, not continuously.

Cortex Agents: The Cost Multiplier Nobody Drew on the Whiteboard

Cortex Agents are billed per million tokens, in AI Credits, with rates determined by the underlying model. That sounds simple. The complication is that agents orchestrate multi-step workflows, and every step that invokes a sub-service generates its own token consumption. Snowflake’s official pricing docs state it directly: costs are additive across the underlying services the agent invokes.

A realistic agent loop might look like this: the agent receives a user question (input tokens), calls Cortex Search to retrieve context (embedding tokens + serving compute), calls Cortex Analyst to generate SQL (Analyst tokens), executes the SQL on a warehouse (Platform Credits), and then calls an LLM to formulate a final answer (more input + output tokens). Every hop generates its own consumption. The result visible to the user is a single response. The result visible to your billing dashboard is five separate line items, split across two credit types, spread across four different usage views.

Standard monitoring via CORTEX_FUNCTIONS_USAGE_HISTORY does not provide agent-specific breakdowns. To get token-level visibility per agent, you need to query SNOWFLAKE.LOCAL.AI_OBSERVABILITY_EVENTS — a system table that captures token counts, models used, timing, and execution context for each agent invocation. That table is not surfaced by default in the Snowflake UI; you have to query it directly.

-- Per-agent token cost attribution
-- Requires ACCOUNTADMIN or SNOWFLAKE_TELEMETRY privilege
SELECT
  agent_name,
  model_name,
  DATE_TRUNC('day', event_timestamp) AS event_day,
  SUM(input_tokens)                  AS total_input_tokens,
  SUM(output_tokens)                 AS total_output_tokens,
  SUM(input_tokens + output_tokens)  AS total_tokens
FROM SNOWFLAKE.LOCAL.AI_OBSERVABILITY_EVENTS
WHERE event_timestamp >= CURRENT_DATE - 30
GROUP BY 1, 2, 3
ORDER BY total_tokens DESC;

For AI SQL functions, your canonical daily monitoring query should look like this:

-- Cortex AI function cost by model and function — last 30 days
-- Use CORTEX_FUNCTIONS_USAGE_HISTORY as the single source; do NOT sum
-- across CORTEX_AISQL_USAGE_HISTORY and CORTEX_FUNCTIONS_USAGE_HISTORY together
SELECT
  DATE_TRUNC('day', start_time)   AS usage_day,
  function_name,
  model_name,
  SUM(input_tokens)               AS input_tokens,
  SUM(output_tokens)              AS output_tokens,
  SUM(credits_used)               AS ai_credits,
  ROUND(SUM(credits_used) * 2.00, 2) AS est_cost_usd
FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_FUNCTIONS_USAGE_HISTORY
WHERE start_time >= CURRENT_DATE - 30
GROUP BY 1, 2, 3
ORDER BY ai_credits DESC;

One critical warning from Snowflake’s own community documentation: the views CORTEX_AISQL_USAGE_HISTORY, CORTEX_FUNCTIONS_USAGE_HISTORY, and an incremental metering path all overlap. Summing them produces double-counts. Pick one canonical view per service type and reconcile totals against the matching service type in METERING_DAILY_HISTORY.

Non-Text Inputs: The Per-Page and Per-Second Trap

If your team is using Cortex for document intelligence — contract review, PDF extraction, audio transcription — the token model changes again. AI_PARSE_DOCUMENT and AI_EXTRACT bill by page rather than by text token: each page in a document counts as 970 tokens. A 50-page contract isn’t 50 pages of your text column — it’s 48,500 tokens before a single word of your prompt or the model’s output enters the meter.

Audio inputs bill at 50 tokens per second of audio. A one-hour customer support call is 180,000 audio tokens before output tokens are added. At frontier model rates, an hour of audio can cost more than a thousand-word document by a wide margin.

The implication for pipeline design: always pre-filter. Before sending a document to AI_EXTRACT, check page count. Before sending audio to a transcription function, check duration. For PDFs specifically, page-level sampling — sending only the pages likely to contain the target information — can reduce cost by 60–80% compared to sending the full document.

The Gotchas Nobody Warns You About

Your existing resource monitors don’t cover AI token spend.Resource monitors in Snowflake watch warehouse compute credits. They have no visibility into AI Credit consumption. A runaway AI_CLASSIFY job on a large table will not trigger your existing budget alerts. You need separate alerting built on CORTEX_FUNCTIONS_USAGE_HISTORY and wired to a Snowflake Task and notification integration.

The regional routing setting silently raises every AI bill by 10%.If your account has CORTEX_ENABLED_CROSS_REGION set to DISABLED or a specific regional setting for data residency, you’re paying $2.20 per AI Credit instead of $2.00. That’s a 10% tax on every token across every Cortex AI feature, and it’s an account-level parameter many teams set once during a compliance review and never revisit against their cost model.

Cortex Analyst through the standalone API still bills in Platform Credits, not AI Credits.If you’re calling Cortex Analyst via the REST API directly rather than through Cortex Agents, it bills per 1,000 messages at Platform Credit rates — which vary by your Snowflake edition. The same Analyst call made through a Cortex Agent costs in AI Credits. The same feature, two different billing regimes, depending on how you invoke it.

Materializing AI results is almost always cheaper than recomputing them.Teams building pipelines that call AI_CLASSIFY or AI_SENTIMENT inside a scheduled task often reprocess unchanged records on every run. The AI functions have no inherent awareness of which records changed since the last run. Join against your source table’s UPDATED_AT column, write results to a separate table, and only pass new or modified rows to the AI function. This pattern, applied consistently, can reduce ongoing AI Credit consumption by 50–90% for stable datasets.

The Cortex Guard security layer adds its own token cost on top of AI_COMPLETE.If you’re using Cortex Guard to filter model outputs for safety — which is sensible for user-facing applications — it bills separately from the underlying AI_COMPLETE call. The input token count for Cortex Guard is based on the number of tokens in AI_COMPLETE’s output. In other words, longer model responses cost more not once but twice: once when generated, and again when scanned by the guard.

The One Principle

“Treat Cortex AI cost engineering the same way you treat warehouse sizing — measure before you scale, not after. The token meter doesn’t have a circuit breaker unless you build one.”

August 10, 2026

Snowflake Time Travel vs. Fail-safe: What Gets Recovered and When

3:14 a.m., and a migration script hands off to DROP TABLE orders_staging; against what everyone on the team swore was a permanent table. It wasn’t. Somewhere in the last quarter it got recreated as TRANSIENT to shave storage costs, and nobody updated the runbook. The on-call engineer isn’t worried — Snowflake has Time Travel, Snowflake has Fail-safe, this is a solved problem. Except Fail-safe doesn’t apply to transient tables. Zero days. The table is gone the moment its one-day Time Travel window closes, and by the time anyone notices, it already has. Six hours of ingestion, rebuilt by hand from source, on a Saturday.

That’s the gap this article is about. Time Travel and Fail-safe get talked about together so often that people assume they’re one continuous safety net. They’re not the same feature, they don’t behave the same way, and the difference has real financial and recovery-time consequences that most teams only discover during an incident.

TL;DR

→ Time Travel lets you query, clone, or UNDROP historical data yourself for a retention window you configure — 0 to 1 day on Standard Edition, up to 90 days on Enterprise Edition and above, for permanent objects.
→ Fail-safe is a separate, fixed 7-day recovery period that starts after Time Travel expires, and it is not self-service — only Snowflake Support can pull data back from it, and only for permanent tables.
→ Transient and temporary tables carry zero Fail-safe days. They’re cheaper precisely because Snowflake gives up that protection.
→ Both features bill as storage: Time Travel data accrues at the normal storage rate, and a table with heavy daily updates on a long retention window can multiply its effective storage several times over.
→ Time Travel doesn’t create a second copy of your table — it retains the old micro-partitions that a write would otherwise discard, via Snowflake’s copy-on-write architecture.
→ UNDROP TABLE, AT, and BEFORE only work inside the Time Travel window. Once an object crosses into Fail-safe, none of those commands work anymore.

What Time Travel Actually Stores

Snowflake tables are stored as immutable micro-partitions — compressed, columnar chunks of roughly 50–500 MB of uncompressed data each. When you run an UPDATE or DELETE, Snowflake doesn’t rewrite rows in place. It writes new micro-partitions reflecting the change and stops referencing the old ones from the table’s current state. That’s the whole trick: Time Travel is Snowflake choosing not to immediately throw those old partitions away.

Snowflake doesn’t back up a table for Time Travel — it just delays discarding the partitions a write would otherwise drop.

Every table, schema, and database has a DATA_RETENTION_TIME_IN_DAYS parameter that controls how long those superseded partitions stick around before they’re eligible for permanent deletion. Retention is inherited: set it on a database and every schema and table created under it picks up the value unless overridden lower down. There’s also an account-level MIN_DATA_RETENTION_TIME_IN_DAYS floor — if it’s set, the effective retention for any object becomes whichever is larger, its own setting or the floor. That parameter is easy to forget exists and even easier to be surprised by months later.

Standard Edition accounts get 1 day of retention by default, and you can only turn it down to 0 — there’s no way to go longer without upgrading to Enterprise Edition. Enterprise and above allow up to 90 days for permanent databases, schemas, and tables, configurable per object.

Retention by Table Type — the Comparison Nobody Reads Until It’s Too Late

The gotcha in the opening story lives entirely in this table. Table type determines the retention ceiling independent of edition, and it determines whether Fail-safe exists at all.

Table type	Time Travel range	Fail-safe period	Notes
Permanent	0–1 day (Standard) · 0–90 days (Enterprise+)	7 days, fixed	The only table type with Fail-safe protection
Transient	0–1 day, on any edition	0 days	Capped at 1 day even on Enterprise — cannot be extended
Temporary	0–1 day, session-scoped	0 days	Dropped automatically when the session ends

Notice that transient tables don’t just lose Fail-safe — their Time Travel ceiling is capped at one day regardless of what edition you’re on or what the account default says. That’s the entire reason transient tables cost less to store: Snowflake is retaining less history for them, full stop.

Querying and Restoring Inside the Window

Time Travel is queryable directly in SQL, three ways: by timestamp, by relative offset, or by the query ID of the statement that changed the data.

-- Query a table as it existed at a specific timestamp
SELECT * FROM orders
AT (TIMESTAMP => '2026-07-28 09:00:00'::timestamp);

-- Query as it existed immediately before a specific statement ran
SELECT * FROM orders
BEFORE (STATEMENT => '8e5d0c1d-0073-4f57-8263-6e6bb1a2b1d4');

-- Restore an accidentally dropped table, in place
UNDROP TABLE orders_staging;

-- Clone a table's state from 6 hours ago into a new object,
-- useful for diffing without touching the live table
CREATE TABLE orders_audit_clone
CLONE orders AT (OFFSET => -60*60*6);

All four of those commands only work while the object — or the specific rows you’re targeting — is still inside its Time Travel window. Past that point, UNDROP returns an object-not-found error, not a graceful fallback into Fail-safe. This trips people up constantly: Fail-safe existing doesn’t mean these commands quietly keep working against it. They don’t.

Fail-safe: What It’s For, and What It Isn’t

Fail-safe is the part of this system most engineers get wrong, because the name suggests self-service safety and it’s the opposite. It’s a fixed, non-configurable 7-day period that begins the moment an object’s Time Travel retention expires, and it exists for Snowflake’s disaster-recovery purposes — not for routine “oops I dropped a table” moments. You cannot query it, clone from it, or run UNDROP against it. The only path back is a support ticket, and Snowflake is explicit that recovery through Fail-safe can take anywhere from hours to several days, positioning it as a last resort rather than a recovery SLA you can plan around.

Only permanent tables get Fail-safe. It’s a fixed 7-day buffer after Time Travel expires, and it’s not something you access yourself.

And critically, Fail-safe only exists for permanent tables. Look back at the comparison table above — transient and temporary objects get 0 days of it. If your team leans on transient tables for staging (a completely reasonable cost optimization, discussed in our zero-copy cloning guide), you’ve implicitly decided that a mistake on those tables gets exactly one day — the Time Travel window — before it’s unrecoverable at any price, support ticket included.

The Cost Math

Time Travel and Fail-safe both bill as storage, at your account’s normal per-terabyte rate. As of 2026, Snowflake’s published on-demand list price is around $23 per compressed terabyte per month for AWS US East, with regional variation — worth flagging because a lot of older guides still cite a $40/TB figure that Snowflake has since moved off of.

The part that surprises people isn’t the rate, it’s the multiplier. Time Travel storage isn’t billed once — it’s billed for the entire retention window, for every version of every changed row, not just until the next write overwrites it.

Illustrative numbers, not a live account screenshot — but the shape of the math holds: retention window × daily churn rate is the real cost driver, not table size alone.

A 100 GB table with 10% of its rows modified daily, sitting on a 90-day retention setting, accrues on the order of 10 GB of Time Travel history per day. Over the full window that’s roughly 900 GB — close to a 9x storage multiplier over the table’s own size, from one retention setting on one heavily-churned table. Multiply that across every staging and fact table on a 90-day account default and it stops being a rounding error.

This is also where the MIN_DATA_RETENTION_TIME_IN_DAYS parameter bites teams that think they’ve already optimized. Someone sets an individual table’s retention to 1 day to cut costs, but the account-level minimum is still 30 — the effective retention is the larger of the two, and the storage bill doesn’t move.

The Gotchas Nobody Warns You About

Transient tables have zero Fail-safe, by design, not by oversight. That’s the trade you’re making every time you choose transient for cost savings. It’s a good trade for genuinely disposable staging data. It’s a bad surprise for anything that turns out to matter more than you thought.

Dropping and recreating a schema resets what its children inherit. If you drop a schema and recreate it with a different DATA_RETENTION_TIME_IN_DAYS, tables created afterward inherit the new value — but objects dropped under the old setting keep whatever retention was active at the time they were dropped, not the new one. It’s easy to assume a schema-level change is retroactive. It isn’t.

The account-level minimum silently overrides a lower table-level setting. As covered above — if you’re trying to cut Time Travel storage costs by lowering retention on specific tables and the number on your bill doesn’t move, check MIN_DATA_RETENTION_TIME_IN_DAYS before assuming the change didn’t take.

Cloning at a past timestamp quietly locks in stale data. CREATE TABLE ... CLONE x AT (...) is a zero-copy operation that materializes as a real object pointing at that historical state. It’s easy to leave one of these lying around after an investigation and forget it’s not tracking the live table anymore.

Fail-safe recovery is not a routine operation, and Snowflake treats it that way. There’s no dashboard, no self-service button, and no fixed turnaround time — it’s a support ticket that gets prioritized as the disaster-recovery mechanism it was designed to be, not an extension of your undo history.

The One Principle

Time Travel is a tool you use; Fail-safe is a safety net Snowflake uses on your behalf — design your retention and table types as if Fail-safe doesn’t exist, because for anything outside a permanent table, it doesn’t.

August 8, 2026

SQL Patterns That Actually Show Up in Interviews (All 10, With Code)
Nobody fails a SQL interview because they don’t know the syntax. They fail because they stare at a question about “users with a 5-day login streak” and don’t recognize it as the exact same problem as “continuous subscription periods” and “consecutive winning games” — three phrasings of one pattern with one trick. The engineers who breeze through SQL rounds aren’t faster typists or syntax savants. They’ve seen the patterns enough times that a novel-sounding question instantly collapses into “oh, that’s gaps-and-islands” — and then the SQL is the easy part.

There are about ten of these patterns, and they cover the overwhelming majority of what product companies actually ask. This is a tour of all ten — the keyword that gives each one away, the core trick, real SQL, and the follow-up the interviewer asks when you get the first version right. The goal isn’t to memorize ten queries; it’s to build the recognition reflex so that in the room, you spend your time on the interesting variation instead of rediscovering the base pattern from scratch. If you want the deeper argument for why this recognition skill matters more than raw syntax, I’ve made it in why senior engineers write SQL differently.

One thing worth pinning to the wall before we start — the logical execution order of a query, because half of “why doesn’t my WHERE see my alias” questions dissolve once you know it: FROM → JOIN → WHERE → GROUP BY → aggregates → HAVING → SELECT → ORDER BY.

The whole game in one table: interviewers rarely name the pattern, but the words they use give it away. Train yourself to hear the keyword and reach for the trick.

1. Gaps and islands (consecutive sequences)

Keywords: consecutive, streak, continuous, sessions, “5 days in a row.” This is the one that trips people up most, and the trick is almost magical once it clicks: subtract a row number from the ordered date. Consecutive dates produce the same constant; a gap shifts it, creating a new group.

Why it works: for consecutive days the row number grows in lockstep with the date, so date − rn is constant. The moment a day is skipped, the constant jumps — and that jump is your new streak boundary.
```
WITH distinct_logins AS (
  SELECT DISTINCT user_id, login_date FROM logins
),
numbered AS (
  SELECT user_id, login_date,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
  FROM distinct_logins
),
login_groups AS (
  SELECT user_id, login_date,
         DATE_SUB(login_date, INTERVAL rn DAY) AS grp
  FROM numbered
)
SELECT user_id,
       MIN(login_date) AS streak_start,
       MAX(login_date) AS streak_end,
       COUNT(*)        AS streak_length
FROM login_groups
GROUP BY user_id, grp
HAVING COUNT(*) >= 5
ORDER BY user_id;
```
The follow-up: “what if you need to detect the gaps themselves, not the streaks?” Switch to LAG() — compare each row to the previous login, flag where the difference isn’t 1 day, and cumulative-sum those flags into group IDs. Same idea, different vehicle. Note the DISTINCT up front: duplicate logins on the same day would silently break the row-number arithmetic.

2. Top-N per group

Keywords: top 3 per department, highest/lowest per group, latest record, first/last. The single most common window-function question. Partition by the group, order by the metric, filter on the rank.
```
WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;
```
The follow-up you must nail: “what if two people tie on salary?” That’s the interviewer probing whether you know the three ranking functions — and this is one of the most common trip-ups, so know it cold: ROW_NUMBER() gives 1,2,3 with an arbitrary tiebreaker; RANK() gives 1,1,3 (ties share a rank, next rank skips); DENSE_RANK() gives 1,1,2 (ties share, no gap). If the question is “top 3 salaries” and ties should all count, you want DENSE_RANK(), not ROW_NUMBER(). For the “latest record per user” variant, it’s the identical shape with ORDER BY created_at DESC and WHERE rn = 1.

3. Running totals / cumulative metrics

Keywords: running total, cumulative, so far, progressive. The frame does the work: SUM(x) OVER (ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).
```
WITH daily_revenue AS (
  SELECT DATE(created_at) AS order_date, SUM(amount) AS revenue
  FROM orders
  GROUP BY DATE(created_at)
)
SELECT order_date,
       SUM(revenue) OVER (
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM daily_revenue;
```
The follow-up: “now show each user’s running total and the global running total in the same query.” That tests whether you understand that two window functions can carry different PARTITION BY clauses side by side — a per-user frame partitioned by user_id, and a global frame with no partition — which usually means aggregating to user-day and day grains first, then combining.

4. Event → state transformation

Keywords: headcount over time, active subscriptions per day, “build the metric then track it.” Here the metric doesn’t exist in the data — you construct it from events. The classic is daily headcount: turn each hire into +1 and each termination into −1, then take a running sum over a date spine.
```
WITH RECURSIVE date_spine AS (
    SELECT MIN(hire_date) AS dt FROM employee
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM date_spine
    WHERE dt < CURRENT_DATE()
),
changes AS (
    SELECT hire_date AS dt, +1 AS delta FROM employee
    UNION ALL
    SELECT termination_date AS dt, -1 AS delta
    FROM employee WHERE termination_date IS NOT NULL
),
daily_change AS (
    SELECT dt, SUM(delta) AS tdelta FROM changes GROUP BY dt
)
SELECT d.dt,
       SUM(COALESCE(c.tdelta, 0)) OVER (
         ORDER BY d.dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS headcount
FROM date_spine d
LEFT JOIN daily_change c ON d.dt = c.dt
ORDER BY d.dt;
```
The “+1/−1 delta then running sum” trick generalizes to any state-from-events problem: concurrent sessions, active subscriptions, inventory on hand.

5. Rolling / moving windows

Keywords: 7-day moving average, 30-day active users, trailing metric. Same window machinery as running totals, but a bounded frame. The gotcha that separates candidates: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW is 7 rows, not 6.
```
SELECT sale_date, daily_sales,
       AVG(daily_sales) OVER (
         ORDER BY sale_date
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS moving_avg_7d
FROM daily_sales;
```
Window frames are worth truly internalizing, because half these patterns are just different frame boundaries over the same OVER() skeleton:

Every rolling metric is just a choice of frame boundaries relative to the current row. And the ROWS-vs-RANGE distinction at the bottom is a favorite senior-level probe — worth knowing why one is deterministic.

The ROWS-vs-RANGE trap: the default frame for SUM() OVER (ORDER BY dt) is actually RANGE, which groups all rows sharing the same ORDER BY value into one step — so two orders on the same date both get the day-end total, not their individual accumulation. ROWS treats each physical row independently. For running totals and time series, prefer ROWS to avoid unintended grouping on ties; being able to explain that difference unprompted signals real depth.

6. Cohort / retention analysis

Keywords: retention, cohort, week 0 / week 1, signup behavior. Ubiquitous at product companies. The shape is always: assign each user a cohort (their first-activity period), then measure activity by offset from that cohort.
```
WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('week', MIN(activity_date)) AS cohort_week
  FROM user_activity GROUP BY user_id
),
user_activities AS (
  SELECT a.user_id, c.cohort_week,
         DATE_TRUNC('week', a.activity_date) AS activity_week
  FROM user_activity a
  JOIN user_cohort c ON a.user_id = c.user_id
),
cohort_size AS (
  SELECT cohort_week, COUNT(DISTINCT user_id) AS total_users
  FROM user_cohort GROUP BY cohort_week
)
SELECT ua.cohort_week,
       DATEDIFF('week', ua.cohort_week, ua.activity_week) AS weeks_since_signup,
       COUNT(DISTINCT ua.user_id) AS active_users,
       COUNT(DISTINCT ua.user_id) * 1.0 / cs.total_users AS retention_rate
FROM user_activities ua
JOIN cohort_size cs ON ua.cohort_week = cs.cohort_week
GROUP BY ua.cohort_week, weeks_since_signup, cs.total_users
ORDER BY ua.cohort_week, weeks_since_signup;
```
The three-CTE structure — cohort assignment, activity-with-offset, cohort size for the denominator — is the reusable skeleton. Retention rate is just active-at-offset divided by the week-0 size.

7. Self-join logic

Keywords: compared to previous, more than their manager, bought A but not B. Any time you compare rows within the same table. The “A but not B” version is a clean anti-join:
```
SELECT DISTINCT a.user_id
FROM purchases a
LEFT JOIN purchases b
  ON a.user_id = b.user_id AND b.product = 'B'
WHERE a.product = 'A' AND b.user_id IS NULL;
```
The trap here is NULLs. The instinct is often WHERE user_id NOT IN (SELECT user_id FROM purchases WHERE product='B') — but if that subquery returns even one NULL, NOT IN yields zero rows, silently. The LEFT JOIN ... IS NULL anti-join above is immune. Many “compare to previous row” self-joins are also better expressed with LAG(), which is cheaper than a correlated subquery and reads more clearly.

8. Time-series expansion (date spine)

Keywords: daily trend, fill missing dates, continuous timeline, “even days with zero.” The fix for gaps in a report is to generate the complete calendar and LEFT JOIN your data onto it, so missing periods become explicit zeros instead of vanishing rows.
```
WITH RECURSIVE months AS (
    SELECT DATE_FORMAT(MIN(hire_date), '%Y-%m-01') AS month_start FROM employees
    UNION ALL
    SELECT DATE_ADD(month_start, INTERVAL 1 MONTH) FROM months
    WHERE month_start < DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')
)
SELECT m.month_start, COALESCE(SUM(x.metric), 0) AS metric
FROM months m
LEFT JOIN some_table x ON DATE_FORMAT(x.dt, '%Y-%m-01') = m.month_start
GROUP BY m.month_start
ORDER BY m.month_start;
```
The production aside worth saying out loud: recursive date spines that recompute headcount by rescanning all employees every month are fine in an interview but expensive at scale. Mentioning that you’d back this with a precomputed monthly_headcount snapshot table in production — built by an incremental pipeline rather than recomputed each run — is exactly the kind of comment that separates a senior candidate, and it ties directly to not reprocessing what didn’t change.

9. Percentiles / distribution

Keywords: top 10%, percentile, ranking distribution. This is where NTILE(), PERCENT_RANK(), and DENSE_RANK() live. “Top 10% of employees by rating” has a naive form and a fair form, and the interviewer usually wants the fair one:
```
-- Fair version: handles ties at the cutoff correctly
WITH latest_rating AS (
  SELECT emp_id, rating,
         ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY review_date DESC) AS rnk
  FROM performance_reviews
),
ranked AS (
  SELECT *,
         DENSE_RANK() OVER (ORDER BY rating DESC) AS bucket,
         COUNT(*)     OVER () AS total_count
  FROM latest_rating WHERE rnk = 1
)
SELECT * FROM ranked WHERE bucket <= CEIL(0.10 * total_count);
```
NTILE(10) forces exactly ten equal-sized buckets (take bucket 1); the DENSE_RANK approach is fairer when many people share the boundary rating. The killer follow-up is point-in-time correctness: “employees change departments over time — attribute each rating to the department they were in then.” That forces a slowly-changing-dimension join on a date range (start_date <= review_date < end_date) instead of a naive join to the employee’s current department — and getting that right is a strong signal.

10. Detect overlapping date ranges

Keywords: booking conflicts, overlapping subscriptions, double-booked, schedule clash. The elegant move is to reason about when ranges don’t overlap, then invert. Two ranges miss each other only if one ends before the other starts:
```
-- Non-overlap:  a.end < b.start  OR  b.end < a.start
-- Invert (De Morgan) -> the standard overlap condition:
SELECT s1.customer_id,
       s1.subscription_id AS sub_1,
       s2.subscription_id AS sub_2
FROM subscriptions s1
JOIN subscriptions s2
  ON s1.customer_id = s2.customer_id
 AND s1.subscription_id < s2.subscription_id   -- avoid self- and duplicate pairs
 AND s1.end_date >= s2.start_date
 AND s2.end_date >= s1.start_date;
```
Two details interviewers check: the s1.id < s2.id condition (so you don’t compare a row to itself or count each pair twice), and that you derived the overlap condition rather than memorized it — walking through the two non-overlap cases and applying De Morgan’s law is the move that earns the nod.

Putting it together: the combined question

Real interviews often stack two or three patterns. “Monthly revenue for completed 2025 orders, with the change vs the previous month, showing all 12 months even when revenue is zero” is three patterns at once: conditional filtering, a date spine (all 12 months), and LAG() for the month-over-month delta — over a base that first aggregates order_items to order grain before summing to month grain. If you can see it as “aggregate → spine → LAG” instead of one intimidating blob, you’ve already won. The recognition reflex is the whole skill; the syntax is just spelling.

The gotchas nobody warns you about

NOT IN with a NULL returns nothing. One NULL in the subquery and NOT IN silently yields zero rows. Use NOT EXISTS or a LEFT JOIN ... IS NULL anti-join for exclusions, every time.

ROWS ≠ RANGE, and the default is RANGE. SUM() OVER (ORDER BY dt) groups tied values into one step. For a true row-by-row running total, spell out ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

“6 PRECEDING” is a 7-row window. Off-by-one on frame bounds silently produces the wrong average. Count the current row.

ROW_NUMBER vs RANK vs DENSE_RANK is a tie question in disguise. When the interviewer says “what if they tie,” they’re testing which one you’d swap to. Have the 1,2,3 / 1,1,3 / 1,1,2 distinction ready.

Wrapping a date column in a function kills index/pruning use. Prefer hire_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH) over TIMESTAMPDIFF(MONTH, hire_date, ...) <= 6 — same logic, but the range predicate can use metadata the function form throws away.

The one principle

You don’t pass a SQL interview by knowing more syntax — you pass it by recognizing that the strange-sounding question in front of you is one of about ten patterns you’ve already solved a dozen times. Learn the keyword that gives each pattern away, learn the one core trick behind it, and practice the recognition until it’s instant. Then the interview stops being a memory test and becomes what it should be: a conversation about the interesting variation, conducted in a language you already speak fluently.

Related reading: Why senior engineers write SQL differently · Why SQL is still the most valuable skill · Don’t recompute what didn’t change · Making these queries fast in production
August 7, 2026
Optimizing dbt Models for Modern Warehouses: An Author & Reviewer’s Guide
Two dbt models can be byte-for-byte identical in output, pass every test, and read cleanly in review — and one of them costs $50 a month while the other costs $5,000. The compiler already guarantees the SQL is correct. What it can’t tell you is how much data moved through the model to produce that correct answer, and that number is the entire ballgame. The trap is that a code review reads a model like prose — do the joins make sense, are the columns right — when the thing that actually determines the bill is invisible in the text and only shows up in the query profile.

So this guide is about reading the profile, not just the SQL. The single skill that separates engineers who write cheap models from ones who write expensive ones is the ability to look at a query profile and see where data volume balloons or lingers — then trace that back to the one line of SQL responsible. Everything below hangs off one mental model, and I’ll show you what each fix looks like in an actual profile, because “trust me, it’s faster” is worth a lot less than “here’s the partition count before and after.”

The one mental model: how much data survives each stage?

Before any checklist, ask one question of every model: how much data survives each stage of this query? A healthy model’s volume shrinks, roughly monotonically, from raw input to final output. A broken one has a stage where volume grows — or stays huge longer than it needs to — and that stage is almost always where your money goes.

Left: volume shrinks stage by stage — a well-shaped query. Right: the join ran before the filter, producing 12 TB of intermediate data from a 5 TB input. The SQL is syntactically perfect and still ruinous.

Every tactic in this post is a specific instance of that one idea: find the stage where volume grows or stays too big for too long, and fix that stage. That’s it. The rest is knowing the four places it usually happens and what each looks like in the profile.

Why this matters more on modern warehouses

Snowflake, BigQuery, Redshift, and Databricks all ship genuinely capable optimizers. They handle predicate pushdown, join reordering, and parallel execution for you, which means the physical tuning you’d have obsessed over on a 2005-era database — index hints, manual join order, rewriting for a specific plan — mostly doesn’t apply. The engine owns that layer now. If you want the mechanics of how that execution layer actually works, I covered it in what really happens when you run a query.

What the optimizer can’t fix is a logical mistake: joining before filtering, reading columns you don’t need, recomputing the same aggregation five times, or choosing ROW_NUMBER() when MAX() would do. Those decisions are baked into the SQL, and no optimizer can rewrite your intent. That’s why the highest-leverage review comments are almost never about syntax — they’re about which stage of the volume curve a change affects.

1. Read less data

The biggest lever, and the first thing to check. On a columnar warehouse, unused columns cost real I/O even though the query works either way:
```
-- Bad: pulls every column off a 200-column table
SELECT * FROM customers

-- Better: only what's used downstream
SELECT customer_id, country FROM customers
```
The one that quietly defeats people is partition pruning, because the query looks filtered but is structured so the engine can’t use the filter. Wrapping the filtered column in a function is the classic killer:
```
-- Bad: the function hides order_date from the optimizer
WHERE YEAR(order_date) = 2026

-- Good: a plain range predicate the engine can prune on
WHERE order_date >= '2026-01-01'
  AND order_date <  '2027-01-01'
```
This is the single highest-value fix in the whole post, and it’s the one worth seeing rather than taking on faith

The profile tells the story the SQL hides: the function-wrapped predicate scanned all 512 partitions (1.42 TB, 94s); the range predicate pruned to 3 partitions (9 GB, 3.4s). Same result, same rows out — a ~150x difference in data read.

When a filter or join predicate isn’t reducing data the way you’d expect, check for a hidden function first — CAST(date AS DATE), UPPER(email), COALESCE(col, 0) all do the same damage. This is exactly why Snowflake maintains min/max metadata per micro-partition, and why a function over the column throws that metadata away; I unpacked that storage mechanism in how Snowflake stores data internally.

2. Join wisely

Joins are where a well-behaved query most often turns into a runaway one. The single question worth asking on every join in a review: is this actually the cardinality I think it is? A join you assumed was 1:1 becomes a many-to-many explosion the moment a source table has duplicate keys — 100M orders against 500M clicks on customer_id can produce tens of thousands of rows per customer, and it’s invisible until someone notices the output count is absurd.

The highest-value structural fix is to aggregate before you join, not after:
```
-- Bad: join the full 800M-row payments table, then aggregate
SELECT o.customer_id, SUM(p.amount)
FROM orders o
JOIN payments p ON o.customer_id = p.customer_id
GROUP BY o.customer_id

-- Better: reduce payments to 10M rows first, then join
WITH payments_agg AS (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM payments
    GROUP BY customer_id
)
SELECT o.customer_id, pa.total_amount
FROM orders o
JOIN payments_agg pa ON o.customer_id = pa.customer_id
```
Same result — but the join now processes 10M rows instead of 800M, because the reduction happened before the join instead of after. The profile makes the difference impossible to miss — watch the row count going into the join, and the spill:

Aggregating first shrinks the join’s input from 800M rows to 10M — which also eliminates the disk spill that was quietly dominating the runtime. Same output, ~11x faster.

Two more join checks worth a glance: watch for skew (one dominant key value — a 90%-US country column, or a flood of NULLs — creates wildly unbalanced work even when the total row count looks fine), and verify every join has a real predicate (a missing condition turns a join into a cartesian product, where row counts don’t grow, they multiply).

3. Don’t recompute what you already computed

Is the same large table scanned more than once? If two CTEs both pull from big_table, ask whether one pass can derive both results. Is an expensive expression — a long CASE block, a repeated subquery — computed several times instead of once in a CTE? And watch for SELECT DISTINCT used as a band-aid: it’s very often papering over a join producing duplicate rows it shouldn’t. If a model “suddenly needs” DISTINCT, that’s a prompt to find the join that changed, not to accept the DISTINCT as the fix.

4. Reduce before expensive operations — and question the tool itself

Push filters ahead of window functions: running ROW_NUMBER() over 5 billion rows when the same logic could run over 100 million after an earlier filter is a common, easy-to-miss cost. But the most valuable and most overlooked review question isn’t about tuning what’s there — it’s whether the approach itself is right:
```
-- Heavier than needed: full partition + sort to get "latest"
SELECT * FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY customer_id
                              ORDER BY order_date DESC) AS rn
    FROM orders
) WHERE rn = 1

-- Often cheaper: if you only need the date, not the whole row
SELECT customer_id, MAX(order_date) AS latest_order_date
FROM orders
GROUP BY customer_id
```
If the goal is genuinely “the latest order date per customer,” MAX() with a GROUP BY does far less work than a full partitioned sort. ROW_NUMBER() earns its keep only when you need the entire row at the latest timestamp — a surprising number of “slow query” tickets are really “wrong tool for the job” tickets. In the profile, the tell is the WindowFunction node sorting billions of rows and spilling to disk, when the aggregate version never sorts at all:

The window function sorts all 5 billion rows and spills 210 GB to disk; MAX() never sorts. Same answer, and it also runs on a smaller warehouse — an ~8x time win on top of a halved credit rate.

For the reproducible-dedup case where you do need the whole row, the deterministic QUALIFY pattern is the right tool, which I covered in why senior engineers write SQL differently.

5. Materialization and recomputation — the dbt-specific one

This is where a lot of warehouse spend hides in plain sight, and it’s two questions. First: is this the right materialization? A very common finding is a model that’s been a plain table since day one, fully recomputed on every run, long after it grew large enough that full rebuilds stopped being free.

Left: the materialization decision, which is really a “how often does this change vs how expensive is it to rebuild” question. Right: who should catch each class of issue — push everything mechanical left toward the author and CI.

Second: is there a missed incremental opportunity? If a model recomputes five years of history every run when only yesterday’s data changed, that’s usually the single highest-value fix available — often bigger than every tactic above combined. The question to ask: does this model’s WHERE clause know about is_incremental(), or is it silently doing a full rebuild every time? The profile for a full-rebuild model is unmistakable — it scans the entire history on every run:

The full-rebuild model reprocesses 3.2 billion rows every single run to change a sliver of data; the incremental version scans one pruned day and merges 1.8M rows. This is where the order-of-magnitude cost wins usually hide.

The same “don’t reprocess what didn’t change” discipline is the whole premise of dbt state-based selection at the project level.

Who should catch each of these

Treating this whole list as “what the reviewer checks” is the wrong default — it makes review slow and contentious. Three owners share it. The author, before opening the MR, catches anything mechanically verifiable by running the query and looking at the output: SELECT *, an unfiltered scan, a repeated CTE. The CI pipeline catches what can be automated: pruning regressions, row-count guards, lint rules. The reviewer is left with what only a human can see — cross-model blast radius (“this join also feeds the finance mart”), organizational memory (“we already know this key is skewed”), and whether the approach fits the business need. The goal over time is to shrink the reviewer’s column: every item that graduates from “reviewer catches it” to “CI catches it” is a permanent win.

The gotchas nobody warns you about

A function on a filtered column silently disables pruning. YEAR(order_date), CAST, UPPER, COALESCE on the predicate column all throw away the partition metadata. The query looks filtered; the profile shows every partition scanned.

DISTINCT is usually a symptom, not a fix. If a model started needing SELECT DISTINCT, a join is producing duplicates it shouldn’t. Fix the join; don’t dedupe the mess.

The profile, not the SQL, tells you the truth. Two models with identical output can differ 100x in bytes scanned. If you’re optimizing without reading the profile, you’re guessing — check partitions scanned and bytes scanned before and after every change.

Full-rebuild tables are the biggest silent cost. A table materialization recomputing history every run often dwarfs every other inefficiency combined. Check materialization strategy before micro-optimizing the SQL.

ROW_NUMBER() is frequently the wrong tool. If you only need an aggregate, not the whole row, a GROUP BY is cheaper than a partitioned sort. Confirm the requirement before defaulting to a window function.

The one principle

Writing an efficient dbt model isn’t about SQL syntax — the compiler already guarantees correctness — it’s about mentally tracing the volume of data at each stage and asking whether that stage makes the data smaller or just makes more work for the next one. Modern warehouses optimize the physical layer for you; they can’t decide to aggregate before joining or reach for MAX() instead of ROW_NUMBER(). Those are logical choices made in the SQL, and the further upstream you catch them — author self-check, then CI, then reviewer — the cheaper they are. Learn to read the profile, and the order-of-magnitude wins stop being luck.

Related reading: What really happens when you run a query · Micro-partitions and why pruning works · Why senior engineers write SQL differently · Stop recomputing unchanged models · Snowflake query profile docs
August 6, 2026

Blog

The Consumer Changed, the Pipeline Did Not

The Pipeline Shape We Keep Coming Back To

Layer 1: Ingestion, Kept Deliberately Boring

Layer 2: Transformation Produces a Model Input Contract

Layer 3: The Inference Worker Is a Pipeline Stage

Batch, Event-Driven or Request Path: A Four-Question Test

1. Can the Output Be Computed Before Anyone Asks for It?

2. Does a Person Wait on Screen for the Result?

3. Did the Person Explicitly Ask for It?

4. Can the Page Render Acceptably If the Call Is Skipped?

Where the Bill Actually Comes From

Key the Cache on Content, Not Identity

Put the Spend Cap in the Pipeline

Where Managed Services Stop Being Worth It

When a Pipeline Is the Wrong Answer

What to Do on Monday

The Three Views and What Each One Covers

Basic Usage Monitoring Queries

Daily credit burn by function and model

Monthly spend by user

Cortex Agent attribution

Automation Pattern 1: Account-Level Monthly Spend Alert

Automation Pattern 2: Per-User Monthly Spending Limits

Automation Pattern 3: Runaway Query Detection and Cancellation

The Gotchas

FAQ

What the MCP Server Actually Is

The Five Tool Types You Can Expose

CORTEX_AGENT_RUN — the recommended default

CORTEX_ANALYST_MESSAGE — natural language to SQL

CORTEX_SEARCH_SERVICE_QUERY — vector search over docs

SYSTEM_EXECUTE_SQL — raw SQL execution

GENERIC — UDFs and stored procedures

Setup: From Zero to Working Connection

Step 1 — Create the OAuth security integration

Step 2 — Create the MCP server object

Step 3 — Grant privileges

Step 4 — Connect the client

The Gotchas Nobody Warns You About

What the MCP Server Doesn’t Do (Yet)

The Problem Deep Dive

The Solution: A Decision Framework

The Close

Why the Demo Lied to You

The Token Inflation You’re Not Accounting For

The Two-Currency Problem

The Cortex Search Idle Tax

Cortex Agents: The Cost Multiplier Nobody Drew on the Whiteboard

Non-Text Inputs: The Per-Page and Per-Second Trap

The Gotchas Nobody Warns You About

TL;DR

What Time Travel Actually Stores

Retention by Table Type — the Comparison Nobody Reads Until It’s Too Late

Querying and Restoring Inside the Window

Fail-safe: What It’s For, and What It Isn’t

The Cost Math

The Gotchas Nobody Warns You About

The One Principle

1. Gaps and islands (consecutive sequences)

2. Top-N per group

3. Running totals / cumulative metrics

4. Event → state transformation

5. Rolling / moving windows

6. Cohort / retention analysis

7. Self-join logic

8. Time-series expansion (date spine)

9. Percentiles / distribution

10. Detect overlapping date ranges

Putting it together: the combined question

The gotchas nobody warns you about

The one principle

The one mental model: how much data survives each stage?

Why this matters more on modern warehouses

1. Read less data

2. Join wisely

3. Don’t recompute what you already computed

4. Reduce before expensive operations — and question the tool itself

5. Materialization and recomputation — the dbt-specific one

Who should catch each of these