Blog

Snowflake Cortex Sense: The Context Runtime Agents Have Been Missing

Every enterprise AI pilot eventually hits the same wall. The agent demo worked because it ran on curated, well-described data in a controlled environment. Production is different. Production data has column names like rev_adj_v3_final, metric definitions that exist only in the head of the analyst who built the original dashboard in 2019, and a fiscal calendar that starts in February but is never documented anywhere a language model can find it.

Someone asks a Snowflake AI agent whether Q3 ACV was up. The agent queries the raw column. It returns a number. The number is confidently wrong because ACV at this company excludes free-tier activity and counts from a February fiscal year start — rules the agent has no way to know from the schema alone. That wrong number goes into a board deck. The AI pilot gets cancelled not because the model failed, but because the data infrastructure beneath it provided no business context.

This is the problem Snowflake Cortex Sense was built to solve. Announced at Snowflake Summit 2026 on June 2 and currently in private preview, it is not a new agent interface or a new model. It is a runtime context layer that automatically assembles business definitions, metric logic, analyst patterns, and governance metadata from your Snowflake data estate — and delivers that assembled context to AI agents the moment they need it, before they execute a single query.

TL;DR

Cortex Sense is a runtime context enrichment layer — not a standalone product you deploy, but a shared substrate that CoWork and CoCo automatically read from when executing queries. Think of it as the context engine underneath both AI surfaces.
It draws from four signal types simultaneously: Horizon Context semantic views (governed business definitions), query history (analyst patterns from past SQL runs and dashboards), BI tool definitions (Tableau and Power BI connected via Horizon Context), and Snowflake object metadata (lineage, tags, ownership, quality scores).
No manual configuration is required to bootstrap Cortex Sense — it learns from signals already present in your Snowflake account. However, the quality of what it can assemble depends directly on the quality of your existing semantic views and metadata coverage.
When Cortex Sense detects conflicting definitions (two teams measuring daily active users differently), it surfaces the conflict for human resolution rather than silently picking one. After resolution, it updates its understanding for future queries.
Cortex Sense is distinct from Horizon Context. Horizon Context is the static store — where governed definitions live. Cortex Sense is the dynamic runtime — it retrieves from Horizon Context (among other signals) and assembles context at query time. You need both for the highest accuracy tier.
The accuracy improvement is substantial but benchmarked on Snowflake’s own infrastructure. Results will vary based on semantic view coverage, metadata quality, and query complexity in your specific account.
Cortex Sense ships with prebuilt plugins for finance and sales that bundle domain-specific business logic, skills, and MCP connectors. Teams in those functions can deploy a production-ready agent without starting from a blank configuration.
Cortex Sense is currently in private preview. GA timeline has not been announced as of this writing.

The Core Problem: Agents Know Schema, Not Meaning

A well-designed Snowflake schema is a technical artifact. Column names are identifiers. Table relationships are join keys. The business meaning encoded in those tables — what counts as a conversion, which transactions are excluded from revenue, how the fiscal calendar maps to calendar quarters — lives everywhere except in the schema itself. It lives in analyst documentation, in the heads of people who built the original models, in BI tool definitions nobody exported, in a Slack thread from 2021 where someone explained why the mrr_adjusted column excludes trial customers.

When an AI agent queries your warehouse without this context, it is reasoning from structure alone. It sees a column called acv. It knows it’s a numeric field. It sums it for Q3 dates based on the date column in the table. Every step of that logic is technically correct. The answer it produces is wrong because the business definition of ACV at your company excludes free-tier, and your fiscal Q3 runs May through July, not July through September.

This failure pattern — a confident wrong answer produced by an agent reasoning correctly from inadequate context — is responsible for more enterprise AI pilot cancellations than any model quality issue. The model didn’t hallucinate. The infrastructure provided no business semantics for it to reason from. Cortex Sense is Snowflake’s infrastructure answer to that specific failure mode.

How Cortex Sense Works: The Four Signal Types

Cortex Sense is not a configuration step you perform once. It is a runtime that continuously reads from four signal types already present in your Snowflake account and assembles the most relevant context at query time — dynamically, based on what the agent is asking.

Signal 1: Horizon Context Semantic Views

This is the highest-quality signal Cortex Sense draws from. Semantic Views in Snowflake Horizon Context are governed, version-controlled SQL objects that encode business definitions — what a metric means, how dimensions relate, which filters apply to a calculation. When you define quarterly_acv in a semantic view that specifies the fiscal calendar offset and the free-tier exclusion filter, Cortex Sense can retrieve that definition at runtime and apply it before the agent writes a single query.

The accuracy data reflects this dependency clearly: Cortex Sense alone (drawing from query history, metadata, and agent skills) lifts accuracy from 47% to 83%. Cortex Sense with full Horizon Context semantic views reaches 86%. The three-point delta between 83% and 86% is the contribution of having governed semantic definitions in place — small in absolute terms, meaningful for the category of questions where a precise business definition is the difference between right and wrong.

Signal 2: Query History

Cortex Sense reads the history of SQL queries run in your account — from analysts, from scheduled jobs, from BI tool extracts. This is where institutional knowledge lives in most data organizations: not in documentation, but in the queries analysts have been writing for years. A pattern like “always filter out account_type = 'trial' when summing revenue” may never appear in any documentation, but it appears in thousands of queries. Cortex Sense extracts that pattern and incorporates it into the context it assembles for future agent queries.

Signal 3: BI Dashboard Definitions

Tableau and Power BI dashboards encode business definitions in calculated fields, measure expressions, and filter logic. A Tableau calculated field for “Adjusted Revenue” may encode years of business rules that nobody has migrated to a semantic layer. Horizon Context’s Wave 1 connectors bring those definitions into Snowflake’s catalog, and Cortex Sense then draws from them at query time. This is significant: it means Cortex Sense can bootstrap on business context that predates Snowflake’s semantic layer — captured from the BI tools where those definitions already live.

Signal 4: Object Metadata and Agent Skills

Object metadata — lineage, tags, ownership, quality scores, column descriptions, and governance classifications from masking policies and MCP-connected context — rounds out the picture with operational signal about the data. An agent asking about customer revenue benefits from knowing that the customer_revenue table has a data quality score of 99.2%, was last updated 2 hours ago, and is owned by the finance data team. That operational context shapes how the agent interprets and presents its answer.

Agent skills add the learned-behavior dimension. As CoCo and CoWork execute tasks, Cortex Sense records the patterns of what worked — which context made answers accurate, which tool calls resolved ambiguous questions — and incorporates those patterns into future context assembly.

The Self-Correcting Loop: What Happens When Definitions Conflict

Different teams in an organization often calculate the same metric differently. The product team’s “daily active users” counts any login. The finance team’s “daily active users” excludes support sessions. Both definitions appear in query history, in BI dashboards, and in semantic views — and they conflict.

Cortex Sense detects these conflicts and, rather than silently picking one, surfaces the ambiguity for human resolution. According to Snowflake’s Summit description, when signals conflict, Cortex Sense asks a designated stakeholder to settle the definition and then updates its understanding for all future queries. This self-correcting loop is architecturally significant: it turns the process of deploying AI agents into a forcing function for resolving the definition disagreements that have always existed in enterprise data but were never surfaced by traditional BI tools.

Practical implication: teams that have clean, agreed-upon metric definitions in semantic views will see Cortex Sense reach its full accuracy potential immediately. Teams with contested or undocumented metrics will encounter Cortex Sense surfacing those conflicts as a first deployment step — which is a feature, not a bug, but plan for that resolution process in your deployment timeline.

The Accuracy Impact, Contextualized

The 47% baseline number deserves some context. It represents frontier model accuracy on enterprise structured query tasks — not simple SQL retrieval, but questions that require understanding business definitions, applying fiscal calendars, excluding specific segments, and joining across multiple tables with the correct business logic applied. This is not a contrived benchmark. It is the class of question real CoWork users ask daily.

The jump to 83% with Cortex Sense, and 86% with full Horizon Context coverage, represents a qualitative shift: from “the agent sometimes gets this right” to “the agent consistently gets this right on queries where business context is the key variable.” The remaining 14–17% of failures are harder problems — cross-system queries, novel metric combinations, questions where no existing signal covers the required business logic.

Cortex Sense vs Horizon Context: How They Fit Together

Dimension	Horizon Context	Cortex Sense
What it is	Static store of governed business definitions	Dynamic runtime that assembles context at query time
Input	Data team authoring semantic views, connecting BI tools	Reads from Horizon Context + query history + metadata + agent skills
When it acts	At definition time — you build it once, update when logic changes	At query time — assembled fresh for every agent invocation
Who maintains it	Data team / analytics engineers via Semantic Views	Snowflake manages the runtime; self-updates from signals
Manual configuration?	Yes — semantic views must be authored or auto-generated	No — bootstraps from existing account signals
Conflict handling	None — definitions are whatever you authored	Detects conflicts, surfaces for human resolution
Without the other	Works (Cortex agents read semantic views directly)	Works at 83%; reaches 86% when Horizon Context is rich

The relationship is not “choose one.” Horizon Context is where your data team encodes business definitions. Cortex Sense is what makes those definitions available to agents dynamically, alongside the additional signals it reads from query history and metadata. Build Horizon Context to improve what Cortex Sense has to work with.

The Prebuilt Domain Plugins

One of the more immediately practical pieces of the Cortex Sense announcement is the prebuilt domain plugins for finance and sales. Each plugin bundles:

→Domain-specific skills (what the agent knows how to do in that function)
→Business logic for common finance or sales definitions (ARR, MRR, pipeline stage conventions, quota attainment calculations)
→MCP connectors for the tools those teams actually use (Salesforce for sales, ERP integrations for finance)
→CoWork integration with user memory so the agent learns individual user preferences over sessions

The effect is that a sales leader can deploy a production-ready CoWork agent for their team without a data engineer spending weeks configuring CRM definitions, pipeline stage logic, and quota calculation rules from scratch. The plugin provides a starting template that inherits the sales context; the data team refines it for company-specific definitions on top of the prebuilt base.

This matters for adoption velocity. The recurring complaint with enterprise AI isn’t model quality — it’s time-to-value. A pilot that requires six weeks of semantic layer authoring before the agent gives useful answers doesn’t survive budget cycles. A prebuilt plugin that makes an agent 80% useful on day one, with the remaining 20% improvable through semantic view refinement, changes the deployment economics entirely.

What to Build Before Cortex Sense Goes GA

Cortex Sense is in private preview with no announced GA date. But the work required to get full value from it when it ships is work your data team should be doing regardless. The context quality Cortex Sense can assemble is a direct function of how well your semantic layer is built. Here is the preparation stack, in order of priority:

1. Audit your semantic view coverage

Run a coverage analysis: which metrics appear in your most-queried dashboards and reports? Which of those have formal semantic view definitions in Horizon Catalog? The gap between “metrics that matter to the business” and “metrics with semantic view definitions” is exactly the gap Cortex Sense will struggle to fill. Use Cortex Analyst’s query history data to identify the most-asked questions; those are your highest-priority semantic view candidates.

2. Use Semantic View Autopilot for the first 80%

Semantic View Autopilot (GA as of Snowflake Summit 2026) generates semantic view definitions automatically from your existing SQL files, Tableau workbooks, or Power BI reports. It is not perfect, but it covers the mechanical 80% — the metric definitions that translate straightforwardly from existing analytical logic. Data engineers refine the remaining 20% where business context requires judgment.

3. Resolve your definition conflicts now

Identify metrics where different teams use different definitions. If daily active users is calculated three different ways across three different dashboards, decide on the canonical definition before Cortex Sense surfaces that conflict at agent query time. The resolution process is easier in a structured setting than as a reactive response to an agent giving different users different answers for the same question.

4. Connect your BI tools via Horizon Context

Horizon Context’s Wave 1 connectors cover Tableau and Power BI. If your organization’s analytical logic lives primarily in one of these tools, connecting them now enriches the signal Cortex Sense draws from — even before your semantic view coverage is complete. BI-derived definitions are a legitimate bootstrapping path for the context runtime.

The Gotchas

Cortex Sense is Snowflake-only — cross-system context requires additional tooling.

Cortex Sense draws from signals within your Snowflake account. If your data estate spans Snowflake, Databricks, dbt projects, external APIs, and Salesforce, Cortex Sense builds context for the Snowflake portion only. For agents that need to reason across a multi-system estate, a separate enterprise context layer (Atlan, DataHub, or similar) is needed to bring Snowflake context together with non-Snowflake signals. Cortex Sense and those tools are additive, not competing.

The 83–86% accuracy numbers are Snowflake’s own benchmarks — your mileage will vary.

The accuracy figures come from Snowflake’s internal testing on enterprise structured query benchmarks. Your actual accuracy with Cortex Sense will depend on the richness of your semantic view coverage, the quality of your query history as a training signal, and the complexity of your specific business logic. Teams with well-maintained semantic layers and rich query history will likely see results near the published numbers. Teams with sparse metadata and no semantic views will see lower gains — and that gap is a clear signal of where semantic layer investment should go.

Cortex Sense reads from query history — which means it learns your data team’s antipatterns too.

If analysts in your account have been systematically applying incorrect filters, using wrong join conditions, or calculating metrics the wrong way, Cortex Sense will extract those patterns as signals alongside the correct ones. The self-correcting loop helps surface conflicts, but it cannot distinguish “this pattern is common” from “this pattern is correct” without a governing semantic view to adjudicate. Semantic views are not optional for quality context — they are the mechanism that validates what Cortex Sense learns from behavioral signals.

Private preview access is invitation-only with no public waitlist announced.

As of September 2026, Cortex Sense remains in private preview. Snowflake has not published a GA date or a public waitlist. Enterprise teams who want early access should engage their Snowflake account executive directly. The work you do on semantic views and Horizon Context coverage is valuable regardless — it is the prerequisite infrastructure that unlocks Cortex Sense’s full accuracy when access becomes available.

The One Principle

“The model is not your competitive advantage. The context layer is. Cortex Sense is Snowflake’s infrastructure bet that governed semantic definitions, assembled at runtime, are more valuable than any individual model improvement.”

FAQ

What is Snowflake Cortex Sense?

Cortex Sense is a runtime context enrichment layer announced at Snowflake Summit 2026 on June 2. It automatically builds a shared context substrate from four signal types — Horizon Context semantic views, query history, BI dashboard definitions, and Snowflake object metadata — and delivers that assembled context to AI agents (CoWork and CoCo) at query time, without manual configuration. It lifted accuracy on enterprise structured query benchmarks from 47% (frontier model alone) to 83–86% in Snowflake’s testing.

How is Cortex Sense different from Horizon Context?

Horizon Context is the static store where business definitions live — semantic views, governed metrics, metadata from connected BI tools. Cortex Sense is the dynamic runtime that assembles context from Horizon Context and other signals at query time. Horizon Context defines the context; Cortex Sense activates it. Both are needed for the highest accuracy tier: Cortex Sense without Horizon Context reaches ~83%, Cortex Sense with rich Horizon Context coverage reaches ~86%.

Does Cortex Sense require manual configuration to set up?

No manual configuration is required to bootstrap Cortex Sense — it reads from signals already present in your Snowflake account. However, the quality of what it assembles depends directly on the richness of your existing semantic views, query history, and metadata coverage. An account with sparse metadata and no semantic views will see lower accuracy gains than an account with well-maintained Horizon Context definitions. The preparation work is building the semantic layer, not configuring Cortex Sense itself.

What happens when Cortex Sense finds conflicting business definitions?

Cortex Sense surfaces the conflict for human resolution rather than silently picking one definition. When different teams calculate the same metric differently — daily active users counting logins vs. excluding support sessions, for example — Cortex Sense detects the discrepancy and asks a designated stakeholder to settle it. After resolution, it updates its understanding for all future queries. This self-correcting loop turns agent deployment into a forcing function for resolving definition disagreements that have always existed in the data.

Is Cortex Sense available now and how do I get access?

Cortex Sense is in private preview as of September 2026. There is no public waitlist or announced GA date. Enterprise teams should contact their Snowflake account executive directly to request private preview access. The most productive use of time before access is available is building semantic views in Horizon Context, connecting BI tools via Horizon Context connectors, and resolving metric definition conflicts across teams — all of which directly improve what Cortex Sense can assemble when access is granted.

What are the prebuilt domain plugins in Cortex Sense?

Cortex Sense ships with prebuilt plugins for finance and sales. Each plugin bundles domain-specific skills, pre-encoded business logic (ARR calculations, pipeline stage definitions, quota attainment rules), and MCP connectors for the tools those functions use (Salesforce for sales, ERP integrations for finance). A sales or finance team can deploy a production-ready CoWork agent using a domain plugin as the starting template rather than configuring everything from scratch, dramatically reducing time-to-value.

October 4, 2026

The Real Reason Data Engineers Fail Technical Interviews

You can be good at SQL and still struggle in a Data Engineer interview.

You can work with Snowflake every day.

You can build production pipelines, write dbt models, debug data issues and understand your company’s entire data platform.

Then the interviewer gives you a problem you’ve technically seen before:

“Find users who have logged in for seven consecutive days.”

You know window functions.

You know ROW_NUMBER().

You’ve probably solved a similar problem before.

But instead of immediately recognizing Gaps & Islands, you start thinking about joins.

Then the interviewer adds:

“What if the user logs in multiple times on the same day?”

You adjust your query.

Then:

“Now find the users whose current streak is still active.”

Now you’re rebuilding the logic.

Five minutes have disappeared.

This is where many otherwise strong engineers struggle.

The problem isn’t always knowledge. It’s recognition under pressure.

Knowing SQL isn’t the same as knowing how to solve interview SQL

In your day-to-day job, you rarely receive a completely blank editor and a timer.

You have documentation.

You have existing models.

You have previous queries.

You can search.

You can test your logic.

You can ask another engineer.

An interview removes most of those advantages.

You’re given a problem you’ve never seen in exactly that form and expected to identify the solution path quickly.

That’s a different skill.

Consider these questions:

Find the longest consecutive activity period.
Find customers with purchases in three consecutive months.
Find the longest period of successful pipeline runs.
Find missing dates in an event stream.

The business scenarios are different.

The underlying SQL pattern can be the same.

That’s why memorizing individual questions has diminishing returns.

What you really want to build is pattern recognition.

The 60-second recognition reflex

A useful way to prepare is to train yourself to identify the pattern before writing SQL.

For example:

If the interviewer says…	Think about…
“Consecutive” / “streak”	Gaps & Islands
“Top N for each…”	Window functions
“Previous / next event”	`LAG()` / `LEAD()`
“Running total”	Window aggregation
“Latest record”	Ranking
“Session” / “session boundary”	Event gaps + cumulative grouping
“Missing records”	Anti-joins / `NOT EXISTS`
“History of changes”	SCD Type 2
“Safe to rerun”	Idempotency
“Process only new data”	Incremental processing
“Large join is slow”	Distribution, skew, join strategy

The objective isn’t to memorize the table.

It’s to make the recognition automatic.

You hear the problem.

You identify the family of solutions.

Then you start solving.

The first question is often the easy part

A common mistake in interview preparation is practicing only the initial question.

But experienced interviewers rarely stop there.

Imagine you’ve just solved:

Find the latest order for every customer.

The next questions could be:

What if two orders have the same timestamp?

Then:

What if the source sends duplicate records?

Then:

What if the data arrives two days late?

Then:

How would you make this incremental?

Then:

How would you make the pipeline safe to rerun?

Notice what’s happening.

The interview has moved from a SQL question into data engineering thinking.

The interviewer is testing whether you understand the system around the query.

That’s why a good preparation strategy needs more than solutions.

It needs traps and follow-ups.

Data Engineer interviews aren’t just SQL

SQL is often the entry point, but a Data Engineer interview can cover a much larger surface area.

Data modeling

You may be asked to design a model for:

E-commerce
Payments
Subscriptions
Logistics
Customer analytics

And explain:

Fact vs dimension
Grain
Keys
Relationships
Star schema
SCD Type 2
Normalization

The interviewer is looking for your reasoning, not just whether you can draw a few tables.

Data pipelines

You might be asked:

Design a pipeline processing hundreds of millions of events.

Now you’re dealing with:

Batch vs streaming
CDC
Incremental loads
Idempotency
Watermarks
Late-arriving data
Retries
Backfills
Data quality
Monitoring

Again, there isn’t one magic answer.

You need to recognize the relevant engineering concerns and explain your trade-offs.

Cloud and warehouse systems

Depending on the role, you may need to discuss:

Snowflake
AWS
Storage vs compute
Partitioning
Clustering
Query performance
Caching
Cost optimization
Security

The interviewer may start with a simple question and progressively introduce scale or cost constraints.

PySpark

A PySpark question can quickly move from:

“Write this transformation.”

to:

“Why is it slow?”

Now you’re discussing:

Shuffles
Partitions
Data skew
Join strategies
Lazy evaluation
UDFs
Memory

The ability to recognize the underlying performance problem becomes more important than remembering another piece of syntax.

dbt

You might be asked about:

Incremental models
Snapshots
Tests
Macros
Sources
Source freshness
Jobs
Data quality
Deployment

Again, the interview rarely stays at the definition level.

The follow-up is usually where the real test begins.

This is why I built the Data Engineer’s Interview Playbook

I wanted a preparation resource that felt closer to an actual interview.

Not another collection of questions where you read:

Question: Find the second-highest salary.

Answer: DENSE_RANK().

Then move on.

Instead, each interview simulation follows a structure:

Scenario → Recognition → Solution → Trap → Follow-up

The goal is to train the reflex that happens between hearing the question and writing the first line of code.

What’s inside?

The playbook contains 511 interview simulations covering the broader Data Engineering interview loop.

Part I — Core SQL

306 drills across 17 modules, covering areas such as:

Gaps & Islands
Top-N per group
Running totals
Event → State
Rolling windows
Cohorts & retention
Self-joins
Date spines
Percentiles
Overlapping ranges
Deduplication
Idempotency
Incremental loads
SCD Type 2
Sessionization
Funnels
Data-quality assertions
Median edge cases

Part II — Advanced SQL

120 drills across 8 modules, including:

Recursive CTEs
Pivots
Time zones and DST
Query plans
Data skew and salting
Approximate counts
Multi-source reconciliation
Set operations
SQL anti-patterns

Part III — The rest of the interview

85 additional items, including:

20 data-modeling scenarios
10 pipeline system-design cases
25 PySpark drills
15 dbt interview questions
15 behavioral questions with STAR outlines

Every item is designed like an interview

Each simulation includes:

Difficulty

Foundational → Intermediate → Senior

Recognition keyword

The wording that should trigger the relevant pattern.

Core technique

The approach you should consider first.

Full solution

Runnable SQL or a complete engineering answer.

The trap

The mistake or edge case that commonly catches candidates.

Chained follow-up

The next question an interviewer can ask after you’ve solved the first one.

That last piece is particularly important.

Because you don’t just want to know how to solve a question.

You want to know what happens after you’ve solved it.

It also includes a study system

The playbook includes a 12-week preparation schedule, a timed-drill approach and a master progress checklist.

A simple rule drives the practice:

Try the problem before looking at the answer.

Give yourself five minutes.

If you can’t identify the underlying pattern within roughly 60 seconds, that’s useful feedback.

You now know exactly what you need to practice.

Who is it for?

The playbook is designed for:

Data Engineers preparing for interviews
Analytics Engineers
Engineers moving into Data Engineering
Candidates who are comfortable with SQL but struggle with interview-style problems
Engineers preparing for product companies, consultancies and startups

It’s particularly useful if you’re comfortable doing your normal job but find whiteboard or timed interview questions much harder.

Who is it not for?

This isn’t designed for absolute SQL beginners.

If you’re still learning basic joins, aggregations and window functions, build those fundamentals first.

And it isn’t intended to be a collection of abstract programming puzzles.

The scenarios are designed around Data Engineering problems:

events, orders, pipelines, staging tables, incremental loads, late-arriving data, data quality and production behavior.

The goal isn’t to memorize 511 answers

This is the most important part.

You shouldn’t finish the playbook knowing 511 answers by heart.

You should finish it recognizing patterns.

So when someone asks:

“Find the longest consecutive period…”

your brain starts looking for Gaps & Islands.

When they ask:

“Find the top 3 for every region…”

you think Top-N per group.

When they ask:

“Make this pipeline safe to run twice…”

you think Idempotency.

When they ask:

“Why did this Spark job suddenly become extremely slow?”

you start thinking about skew, shuffles, partitions and joins.

That’s the difference between remembering interview questions and being prepared for interviews.

Get the Data Engineer’s Interview Playbook

If you’re preparing for a Data Engineer interview, you can explore the complete playbook here:

The Data Engineer’s Interview Playbook — 511 Interview Simulations

It includes the full SQL curriculum, advanced SQL, data modeling, system design, PySpark, dbt and behavioral preparation, along with the 12-week study system.

There’s also a 30-day guarantee if you work through the playbook and don’t feel measurably sharper at SQL and Data Engineer interviews.

One final thought

The interviewer isn’t going to give you the exact question you practiced last night.

They’re going to change the wording.

Add a constraint.

Introduce duplicates.

Add late-arriving data.

Change the scale.

Ask what happens when the pipeline fails.

Then ask you to explain why your solution works.

So don’t prepare only for questions.

Prepare for patterns.

Because the real interview skill isn’t:

“I’ve seen this exact question before.”

It’s:

“I haven’t seen this exact question before, but I recognize what it’s testing.”

September 28, 2026

Snowflake DCM Projects: Infrastructure as Code, Native

Until August 2026, managing Snowflake infrastructure as code meant one of two paths. You either adopted Terraform with the Snowflake provider — an external tool with its own state file, version lag behind new Snowflake features, and a separate CI/CD pipeline to learn and maintain. Or you ran Schemachange or a migration-script pattern — imperative SQL files executed in order, no dry-run, no idempotency, no rollback if something failed halfway through.

Both paths work. Both also mean that your Snowflake infrastructure is being managed by something that lives outside Snowflake and has an imperfect model of what’s actually in your account.

Snowflake DCM Projects — Database Change Management, GA on August 7 2026 — is the native alternative. You write DEFINE statements in SQL files describing your desired state. You run PLAN to see exactly what will change. You run DEPLOY and Snowflake reconciles the diff. No external tool. No state file to lose. No provider lag. And from July 2026, you can have Cortex Code author and debug your DEFINE files with a natural-language prompt.

This is the complete practitioner guide: how DCM Projects works, when to reach for it over Terraform, the full CI/CD pipeline with GitHub Actions, and every production gotcha documented in the official docs.

TL;DR

DCM Projects is a native Snowflake object (schema-level) that manages other Snowflake objects declaratively. You write DEFINE statements in SQL files, run PLAN to preview changes, and DEPLOY to apply them. All state is tracked inside Snowflake — no external state file.
The workflow is Terraform-like but Snowflake-native: one DCM project per target environment (DEV, STAGING, PROD), all pointing at the same parameterized definition files, deployed independently via Jinja template variables.
Jinja2 templating is first-class: dictionaries, loops, conditionals, and macros work in definition files. Use loops to provision the same database + role + warehouse stack for multiple teams in one DEFINE file.
PLAN DELTA (preview, July 2026) evaluates only changed definitions and their dependents — dramatically faster feedback during active development on large projects.
Inherited grants (preview, July 2026) let you write one GRANT ... INHERITED statement that automatically applies to every current and future object of a specified type within a container. No more grant drift on new tables.
Snowflake provides reusable GitHub Actions (dcm/plan, dcm/deploy) and sample workflows for full PR-based CI/CD. OIDC authentication is recommended — no stored secrets.
DCM Projects supports up to 10,000 entities per project and a maximum of 10 MB total definition file size. Projects exceeding these limits may timeout during PLAN or DEPLOY.
DCM Projects are available on all Snowflake editions at no additional cost beyond the cloud services compute consumed by PLAN and DEPLOY operations.

The Plan-Then-Deploy Lifecycle

The mental model maps directly to Terraform if you’ve used it. You write desired state. You preview the diff. You apply. The difference is that the state Snowflake compares against is the live account — not a separate state file that can diverge from reality.

📷 three zones, one workflow — source files parameterised with jinja, commands run against the live account, environments deployed independently

The key difference from Terraform’s state model: when you run PLAN, Snowflake queries your live account to compare current state against your definitions. If someone manually altered a table outside of DCM, PLAN will show that as a change. The source of truth is always the account, not a file on disk. This eliminates the class of bugs that come from state file drift — but it also means that manual changes outside DCM are always detected and potentially overwritten on the next DEPLOY.

Writing Your First DEFINE File

DEFINE statements are the core primitive. They look like SQL DDL but describe desired state rather than imperative commands. Snowflake figures out whether to CREATE, ALTER, or do nothing based on the diff between your DEFINE and the live object.

-- definitions.sql
-- Describes the desired state of a complete analytics stack for one team

create DATABASE {{ team_name }}_DB
  COMMENT = 'Analytics database for {{ team_name }} team';

create SCHEMA {{ team_name }}_DB.RAW
  COMMENT = 'Raw ingestion layer';

create SCHEMA {{ team_name }}_DB.ANALYTICS
  COMMENT = 'Curated analytics layer — analyst-facing';

create WAREHOUSE {{ team_name }}_WH
  WITH
    WAREHOUSE_SIZE = '{{ wh_size | default("SMALL") }}'
    AUTO_SUSPEND   = 300
    AUTO_RESUME    = TRUE
  COMMENT = 'Compute for {{ team_name }} team';

use ROLE {{ team_name }}_ADMIN;
use ROLE {{ team_name }}_ANALYST;

-- Grants declared alongside objects — reconciled on every DEPLOY
GRANT OWNERSHIP ON DATABASE {{ team_name }}_DB    TO ROLE {{ team_name }}_ADMIN;
GRANT OWNERSHIP ON WAREHOUSE {{ team_name }}_WH   TO ROLE {{ team_name }}_ADMIN;
GRANT USAGE     ON WAREHOUSE {{ team_name }}_WH   TO ROLE {{ team_name }}_ANALYST;
GRANT USAGE     ON DATABASE  {{ team_name }}_DB    TO ROLE {{ team_name }}_ANALYST;
GRANT USAGE     ON SCHEMA    {{ team_name }}_DB.ANALYTICS TO ROLE {{ team_name }}_ANALYST;
GRANT SELECT    ON ALL TABLES IN SCHEMA {{ team_name }}_DB.ANALYTICS
                                          TO ROLE {{ team_name }}_ANALYST;
GRANT ROLE {{ team_name }}_ADMIN    TO ROLE SYSADMIN;

This file is parameterised with {{ team_name }} and {{ wh_size }}. To provision the Finance team with a LARGE warehouse, you pass those variables at plan or deploy time. To provision five teams in one shot, wrap the whole file in a Jinja {% for team_name in teams %} loop and pass a list.

The manifest.yml — environment targets and templating config

# manifest.yml
# Declares target environments and their templating configurations

version: 1

targets:
  DEV:
    account_identifier: myorg-myaccount-dev
    project_name: ANALYTICS.PROJECTS.ANALYTICS_DCM_DEV
    project_owner: DCM_DEVELOPER_ROLE
    templating_config: dev_config

  STAGING:
    account_identifier: myorg-myaccount-staging
    project_name: ANALYTICS.PROJECTS.ANALYTICS_DCM_STAGING
    project_owner: DCM_STAGING_ROLE
    templating_config: staging_config

  PROD:
    account_identifier: myorg-myaccount-prod
    project_name: ANALYTICS.PROJECTS.ANALYTICS_DCM_PROD
    project_owner: DCM_PROD_ROLE   # service account only — not individual devs
    templating_config: prod_config

default_target: DEV

configurations:
  dev_config:
    variables:
      teams: ['Engineering']
      wh_size: 'XSMALL'
      suffix: 'DEV'

  staging_config:
    variables:
      teams: ['Engineering', 'Analytics']
      wh_size: 'SMALL'
      suffix: 'STAGING'

  prod_config:
    variables:
      teams: ['Engineering', 'Analytics', 'Finance', 'HR']
      wh_size: 'MEDIUM'
      suffix: ''

The manifest is the single source of truth for your environment topology. Each CI/CD workflow reads account_identifier, project_name, and project_owner directly from the manifest — environment-specific config lives here, not scattered across GitHub secrets.

Running PLAN and DEPLOY

The full command set is available via SQL, Snowflake CLI, Snowsight UI, or Cortex Code. The CLI is the best choice for local development and CI/CD pipelines:

# Navigate to your project directory
cd ./analytics-dcm-project/

# Run PLAN against the default target (DEV from manifest)
snow dcm plan

# Run PLAN against PROD to see what a production deployment would change
snow dcm plan --target PROD --save-output

# Run PLAN DELTA — only evaluate definitions changed since last deploy
# Use during active development for fast feedback; always run full PLAN before deploying
snow dcm plan --delta

# Deploy to DEV
snow dcm deploy

# Deploy to PROD with a deployment alias (like a commit message for the deployment)
snow dcm deploy --target PROD --alias "Add HR team infrastructure - ticket PLAT-421"

# Preview what PURGE would do (dry run)
snow dcm plan --target DEV  # should show all objects as DROPs if you purge after

# Purge a sandbox DCM project — drops all managed objects
EXECUTE DCM PROJECT MY_SCHEMA.PROJECTS.ANALYTICS_DCM_DEV PURGE AS "sandbox_cleanup";
DROP DCM PROJECT MY_SCHEMA.PROJECTS.ANALYTICS_DCM_DEV;

Always run full PLAN before DEPLOY. PLAN DELTA is fast but only evaluates changed definitions — it won’t catch external changes (a table manually dropped or altered outside DCM since the last deployment). PLAN DELTA is for development feedback; full PLAN is the pre-deploy gate.

DCM Projects vs Terraform vs Schemachange

📷 dcm projects is not a replacement for every terraform use case — but for snowflake-only infrastructure it removes an entire layer of external tooling

Dimension	Terraform + Snowflake Provider	Schemachange	Snowflake DCM Projects
State management	External state file (S3/GCS/local)	Migration history table	Inside Snowflake — always live
Dry-run / preview	terraform plan	None — scripts run directly	PLAN / PLAN DELTA
Idempotency	Yes	Depends on migration design	Yes — DEPLOY skips matching objects
Multi-env support	Workspaces / vars	Manual per-env scripts	manifest.yml targets + Jinja vars
New Snowflake feature support	Provider update lag (weeks)	Immediate (plain SQL)	Day-0 (DEFINE added same GA)
Non-Snowflake infra	Yes — multi-cloud providers	No	Snowflake objects only
AI-assisted authoring	None native	None	Cortex Code DCM skill
Cost	Tool + provider maintenance	Minimal	Cloud Services compute only

The honest comparison: if your team already runs Terraform for multi-cloud infrastructure — S3 buckets, IAM roles, Kafka clusters, and Snowflake objects in one plan — stick with Terraform. DCM Projects doesn’t replace it for cross-cloud IaC. It replaces it for Snowflake-only infrastructure, where the external tool overhead was never really worth it.

CI/CD with GitHub Actions

Snowflake publishes reusable GitHub Actions for DCM Projects in the snowflakedb/snowflake-actions repository. Four actions cover the full lifecycle: dcm/parse-manifest, dcm/connection-test, dcm/plan, and dcm/deploy.

📷 every pr triggers a full production plan — reviewers see create/alter/drop before approving, not after

# .github/workflows/dcm_pr_to_main.yml
# Runs PLAN against PROD on every PR — gives reviewers a full diff before merge

name: DCM Plan on PR

on:
  pull_request:
    branches: [main]
    paths:
      - 'dcm/**'

jobs:
  plan:
    runs-on: ubuntu-latest
    environment: PROD
    permissions:
      contents: read
      id-token: write       # Required for OIDC — no stored secrets
      pull-requests: write  # Post plan summary as PR comment
    steps:
      - uses: actions/checkout@v4

      - uses: snowflakedb/snowflake-actions/dcm/plan@v3
        id: plan
        with:
          target: PROD
          project-path: dcm/analytics/
          snowflake-user: ${{ env.SNOWFLAKE_USER }}
          # OIDC: no SNOWFLAKE_PASSWORD secret needed

      - name: Post plan summary to PR
        uses: actions/github-script@v7
        with:
          script: |
            const summary = `${{ steps.plan.outputs.summary }}`;
            github.rest.issues.createComment({
              ...context.repo,
              issue_number: context.payload.pull_request.number,
              body: summary
            });

# .github/workflows/dcm_deploy_prod.yml
# Deploys to STAGING then PROD on merge to main

name: DCM Deploy

on:
  push:
    branches: [main]
    paths: ['dcm/**']

jobs:
  deploy-staging:
    runs-on: ubuntu-latest
    environment: STAGING
    permissions:
      contents: read
      id-token: write
    steps:
      - uses: actions/checkout@v4
      - uses: snowflakedb/snowflake-actions/dcm/deploy@v3
        with:
          target: STAGING
          project-path: dcm/analytics/
          snowflake-user: ${{ env.SNOWFLAKE_USER }}
          alias: "Deploy from ${{ github.sha }}"

  deploy-prod:
    needs: deploy-staging     # Production blocked until staging succeeds
    runs-on: ubuntu-latest
    environment: PROD
    permissions:
      contents: read
      id-token: write
    steps:
      - uses: actions/checkout@v4
      - uses: snowflakedb/snowflake-actions/dcm/deploy@v3
        with:
          target: PROD
          project-path: dcm/analytics/
          snowflake-user: ${{ env.SNOWFLAKE_SERVICE_USER }}
          alias: "Promote from staging - ${{ github.sha }}"

The sample workflows in the Snowflake Labs DCM repository include a DROP detection step that parses plan_result.json and blocks deployment if the changeset contains top-level DROPs on databases, schemas, tables, or stages. This is a safety guardrail, not a guarantee — nested destructive changes or ALTER operations that effectively drop data won’t be caught by it.

The Three New Capabilities to Know (July 2026 Preview)

PLAN DELTA — fast feedback during development

On a project with 500+ entities, a full PLAN can take several minutes. PLAN DELTA evaluates only the definition files you changed since the last deployment, plus any downstream definitions that depend on them. For tightening a masking policy or tweaking a dynamic table schedule, PLAN DELTA gives you sub-30-second feedback. Always switch to a full PLAN before deploying.

Inherited grants — close the grant drift problem permanently

The most painful recurring problem in Snowflake governance is grant drift: a new table is created, nobody remembers to grant SELECT to the analyst role, and the dashboard breaks. Inherited grants solve this at the DCM level:

-- grants.sql
-- One INHERITED grant covers all current AND future tables in this schema

GRANT SELECT
  ON ALL TABLES IN SCHEMA ANALYTICS_DB.ANALYTICS
  TO ROLE ANALYTICS_ANALYST
  INHERITED;   -- applies automatically to tables created after this DEPLOY

GRANT SELECT
  ON ALL VIEWS IN SCHEMA ANALYTICS_DB.ANALYTICS
  TO ROLE ANALYTICS_ANALYST
  INHERITED;

Inherited grants require a behavior-change parameter enabled at account level, independent of DCM Projects. This parameter affects all future object grants in your account — not just DCM-managed ones. Read the Managing access with inherited grants section before enabling it in production.

ATTACH TAG — declarative tagging reconciled on every deploy

Tag assignment used to be a post-deployment manual step or a separate Terraform resource. With ATTACH TAG in DCM Projects, tag assignments are part of your definition files and reconciled on every DEPLOY — including column-level tags for PII classification that your masking policies depend on:

-- tags.sql
-- Declaratively assigns PII tags — reconciled on every DEPLOY

ATTACH TAG GOVERNANCE_DB.TAGS.PII_EMAIL
  TO COLUMN ANALYTICS_DB.ANALYTICS.CUSTOMERS.EMAIL;

ATTACH TAG GOVERNANCE_DB.TAGS.PII_PHONE
  TO COLUMN ANALYTICS_DB.ANALYTICS.CUSTOMERS.PHONE_NUMBER;

ATTACH TAG GOVERNANCE_DB.TAGS.GDPR_SUBJECT
  TO TABLE ANALYTICS_DB.ANALYTICS.CUSTOMERS;

The Gotchas

Removing a DEFINE statement expresses intent to DROP — not intent to unmanage.

If you delete a DEFINE TABLE ... line from your definitions file and run DEPLOY, DCM drops the table. This is the correct declarative behaviour — the desired state no longer includes that table. If you want to stop managing an object without dropping it, use ALTER TABLE ... UNSET DCM PROJECT to detach it first, then remove the DEFINE statement. Missing this step is how teams accidentally drop production tables during a refactor.

DEPLOY failure mid-execution can leave objects in a partially-applied state.

Unlike Terraform which can plan a rollback, a DCM DEPLOY that fails partway through has applied some DDL statements and not others. The fix in most cases is to fix the root cause and run DEPLOY again — DCM reconciles from wherever it left off. For a critical failure, the Recover an earlier defined state workflow lets you replay an earlier deployment artifact. This is why PLAN + manual review before DEPLOY is non-negotiable in production.

DCM project owner must hold every role that receives GRANT OWNERSHIP — or PLAN fails.

If your definitions include GRANT OWNERSHIP ON TABLE ... TO ROLE TEAM_ADMIN, the DCM project owner role must hold TEAM_ADMIN (directly or through role hierarchy). DCM checks for potential owner lockout at PLAN time and fails before making any changes if the hierarchy isn’t right. This is a safety feature — but it means your DCM project owner role needs a carefully designed privilege stack before you can express ownership transfers in definitions.

Projects over 1,000 entities can timeout on PLAN or DEPLOY.

The official docs state that PLAN or DEPLOY for large projects can take 10 minutes or more, and exceeding the 10,000-entity or 10 MB limits can cause timeouts. Mitigation: split large projects along natural boundaries (separate business units, separate data domains) rather than running one mega-project. Consolidating definitions into fewer files also speeds up PLAN and DEPLOY. And use PLAN DELTA during active development to avoid full-project evaluations on every edit.

Jinja template variables render in cleartext — never put credentials in them.

The official docs include this as an explicit warning: rendered SQL definitions don’t redact any values inserted by Jinja template variables, and those rendered files are stored in the DCM project’s deployment artifacts. Use opaque identifiers, environment names, and configuration values in Jinja — never API keys, passwords, or connection strings. Use Snowflake Secrets for credentials, referenced by name, not by value.

PLAN DELTA won’t catch external changes to your account since the last deployment.

PLAN DELTA only evaluates changed definition files — it doesn’t query the live account for the unchanged portions. If someone manually altered a view outside of DCM since your last deployment, PLAN DELTA won’t detect that drift. Full PLAN always compares against the live account. The rule: PLAN DELTA for development speed, full PLAN as the pre-deploy gate in every CI/CD pipeline.

The One Principle

“Write what you want. Let Snowflake figure out how to get there. The PLAN is the diff — read it before every DEPLOY, especially the DROP lines.”

FAQ

What is Snowflake DCM Projects and when did it go GA?

DCM Projects (Database Change Management) is Snowflake’s native infrastructure-as-code system. You write DEFINE statements in SQL files describing your desired Snowflake object state, run PLAN to preview changes, and DEPLOY to apply them. Snowflake tracks state internally — no external state file. It went generally available on August 7 2026 and is available on all Snowflake editions at no additional cost beyond cloud services compute.

Should I replace Terraform with Snowflake DCM Projects?

It depends on your scope. If you manage only Snowflake objects and have been using Terraform purely for that, DCM Projects is a cleaner native alternative with day-zero feature support and no state file drift. If you manage multi-cloud infrastructure — S3 buckets, IAM, Kafka, and Snowflake together — keep Terraform for the cross-cloud layer. DCM Projects only manages Snowflake objects.

What is the difference between PLAN and PLAN DELTA in DCM Projects?

PLAN compares all your definition files against the current live account state and produces a full changeset. PLAN DELTA evaluates only the definition files that changed since the last deployment, plus any downstream definitions that depend on them. PLAN DELTA is dramatically faster on large projects — use it during active development for quick feedback. Always run a full PLAN before deploying to catch any external changes to your account that PLAN DELTA would miss.

How do I prevent DCM Projects from accidentally dropping objects?

Two safeguards: first, always review the PLAN output before DEPLOY — look specifically at the DROP entries. Second, if using GitHub Actions, add the DROP detection step from the Snowflake Labs sample workflows, which parses plan_result.json and blocks the deploy workflow if it finds top-level DROPs on databases, schemas, tables, or stages. If you want to stop managing an object without dropping it, use ALTER TABLE … UNSET DCM PROJECT to detach it before removing its DEFINE statement.

What are inherited grants in DCM Projects and why do they matter?

Inherited grants let you write a single GRANT statement with the INHERITED keyword that automatically applies to every current and future object of a specified type within a container — for example, all current and future tables in a schema. This permanently closes the grant drift problem: a new table is created, the grant applies automatically without any manual step or separate Terraform resource. Inherited grants require enabling a behavior-change parameter at the account level, independent of DCM Projects itself.

Can Cortex Code write my DCM Project definition files for me?

Yes. The Cortex Code DCM skill can scaffold a new project from scratch, author and edit DEFINE statements and Jinja templates, run PLAN and DEPLOY, interpret plan output, and diagnose failures. Available in Cortex Code CLI, Snowsight, the VS Code extension, and Cortex Code Desktop. Use natural-language prompts like “Add a new dynamic table for customer spending” or “Why did my last plan fail?” and Cortex Code handles the authoring and debugging loop.

September 27, 2026

2extract for Web Scraping: Python, Proxies & MCP Guide

A ten-thousand-row scraping job died two hours in, wallet already charged for the first six thousand rows, when a regional storefront started quietly returning empty product grids instead of a clean 403 I could at least catch. A request from a cloud IP gets fingerprinted as automation by anything that matters — pricing pages, ad verification targets, review aggregators — before retry logic even notices something’s wrong. The fix isn’t a longer timeout. It’s a different exit point.

That’s the problem 2extract is actually solving, and it’s worth being precise about what changes and what doesn’t. It’s a residential and mobile proxy network, billed pay-as-you-go, where every targeting decision — country, city, ZIP, carrier, how sticky a session should be — is a parameter you build into a proxy username rather than a checkbox in a vendor portal. That’s a different operating model from the flat “buy a country, get an IP list” products a lot of data teams have already tried and half-abandoned. The same account is also reachable through an MCP (Model Context Protocol) server at mcp.2extract.com, so an agent — Claude, Cursor, Codex, anything that speaks MCP — can check the balance, look up a geo slug, create a proxy, and cap its spend without anyone hand-writing the client shown below.

I found that out the expensive way, twenty minutes into my first real run. I’d copied a proxy username from the dashboard, then pasted -country-us onto the end of it for a quick test — except the copied string already carried a targeting suffix from an earlier session I’d forgotten to clean up. Two suffixes, one string, an instant 407 that read exactly like a wrong password. It wasn’t. It was a syntax problem wearing an authentication error’s clothes, and it’s the first thing this article will save you from repeating.

TL;DR

2extract is a residential and mobile proxy network, not a scraper — targeting lives entirely in the proxy username, not headers or dashboard toggles. This walkthrough starts with 2extract’s MCP server (mcp.2extract.com), which puts balance, proxy, and geo-targeting controls in front of any MCP-capable agent, then covers the same account driven by a Python package (twextract) that builds three real datasets — geo/address, eCommerce pricing, and market research. Real code, real output, real failure modes from both.

One pricing detail worth knowing

Most proxy vendors sell a country as a separate product. 2extract sells one residential proxy and treats geography as a parameter on the request, so the bill doesn’t multiply by how many markets get targeted. A 407 also narrows to one of three specific causes on this stack (see the FAQ below), not a generic dead end — useful to know before you’re debugging it at 2am against a real budget.

What 2extract actually is — and the one thing it isn’t

2extract does not scrape websites. It doesn’t parse HTML, doesn’t decide which fields belong in a product catalog, and doesn’t store anything you collect. What it does is sit between your program and the public internet and make each outbound request look like it originated from a real residential — or, on the mobile product, a real carrier — connection, optionally narrowed to a country, state, city, or ZIP you choose.

Every stack built on top of it has the same three layers. The collector — an agent calling MCP tools, or Python code with requests and BeautifulSoup — chooses URLs, parses responses, dedupes, and writes files. The gateway, at proxy.2extract.net:5555, authenticates the request, applies targeting, picks an exit IP, and tunnels the traffic through. The target is whatever site or API actually holds the data.

Mobile proxies push the residential idea further: the exit is an actual carrier IP (T-Mobile US shows up under ISP code 310260 in the docs), priced separately, and targeted with -isp instead of -country — the two targeting families are mutually exclusive on a single username, and the gateway will reject a request that tries to mix them.

None of this is a license to do anything you couldn’t already justify without a proxy. The Acceptable Use Policy rules out unauthorized access, account farming, scalping, spam, and ad fraud in plain terms, and a residential exit doesn’t override robots.txt, a site’s terms of service, or copyright law.

The proxy username is the actual control plane

Most proxy vendors let you pick a country from a dashboard dropdown or set it in an HTTP header. 2extract does neither. After creating a proxy, the dashboard hands you a base username shaped like 2xt-customer-[CLIENT_ID]-proxy-[PROXY_NAME], and that base is immutable for the life of the proxy. Everything situational gets appended afterward as a hyphenated suffix, built fresh by your code on every request.

@dataclass(frozen=True)
class Targeting:
    country: str | None = None
    state: str | None = None
    city: str | None = None
    zip_code: str | None = None
    asn: str | None = None
    isp: str | None = None
    session: str | None = None
    time_minutes: int | None = None
    const: bool = False

    def validate(self) -> None:
        t = self.normalized()
        geo = any([t.country, t.state, t.city, t.zip_code])
        net = any([t.asn, t.isp])
        if geo and net:
            raise TargetingError(
                "Geographic targeting (country/state/city/zip) and network "
                "targeting (asn/isp) are mutually exclusive. Use one group only."
            )
        if (t.state or t.city or t.zip_code) and not t.country:
            raise TargetingError("city, state and zip require country in the same username.")


def build_username(base_username: str, targeting: Targeting | None = None) -> str:
    base = base_username.strip()
    if targeting is None:
        return base
    parts = targeting.suffix_parts()
    return f"{base}-{'-'.join(parts)}" if parts else base

What the residential product is actually sold for

Use case	Why residential matters here	Session strategy
eCommerce & price monitoring	Stores change currency, tax, and stock by country; a datacenter IP gets a generic or blocked page	Sticky for the length of a category walk
Market research	Discussion APIs and open product databases still rate-limit aggressively at volume	Rotating, one exit per batch
Geo / localization	Regional prices, SERPs, and ads only render correctly to an exit that actually looks local	Rotating, unique IP per market sample
Ad verification & SEO / SERP	Same mechanics underneath, but SERPs are heavily JavaScript-driven	Usually needs a real browser, not this stack

How a request travels, and what happens the moment it doesn’t

Every request makes the same trip — collector to gateway to target, response back the same path. A residential peer can simply go offline mid-session. A proxy left in Inactive status returns a 407 even against a perfectly correct password. Gateway-level errors surface in an X-2extract-Error or X-Proxy-Error response header — except that header goes dark over HTTPS CONNECT tunneling.

def _gateway_407(self, targeting, exc):
    header = self._read_gateway_header_if_needed(targeting)
    hint = header or (
        "the proxy is Inactive, the password is wrong, or the wallet is empty. "
        "Open My Proxies and set Status to Active, then rerun."
    )
    if header and "inactive" in header.lower():
        hint = f"{header}. Open My Proxies and set Status to Active (Actions menu)."
    return GatewayError(f"2extract rejected the proxy login (407): {hint}", status_code=407)

Setting up an account — the part no library can do for you

Create an account and open the dashboard. Unverified accounts carry a $50 lifetime deposit cap.
Top up the Pay-As-You-Go wallet. The minimum is $10, and residential and mobile traffic bill from the same balance.
If the job will ever need more than $50 deposited, verify identity first.
Create an API key scoped to at least geo:read, proxies:read, proxies:write, and balance:read. It’s shown exactly once.
Create one residential proxy, and a second dedicated proxy only if mobile ISP targeting is actually needed.
Set a monthly spend cap on each proxy.
Copy the base username and password somewhere safe — a .env file if you’re driving this with Python, the agent’s own credential store if it’s MCP — never the targeted version with a suffix already attached.

Running the same account through MCP

Everything above assumes a person writing Python. 2extract also ships an MCP server at mcp.2extract.com, exposing that exact same account — balance, proxies, geo catalog, traffic history — as tools any MCP-capable agent can call directly. Nothing about the underlying product changes: it’s still one residential proxy, still billed pay-as-you-go, still targeted through a username suffix. What changes is who assembles that username — the agent, from a plain-language request, instead of you, from the Targeting dataclass above.

fig. 4 — the round trip behind every tool call: agent → MCP server → account, and back

I connected it as a custom MCP server inside an agent’s tool panel and had 19 tools and 19 resources enabled with nothing more than the server URL and an API key — no separate SDK, no client to install.

📷 the 2extract MCP server connected inside an agent’s tool panel — createProxyResource, deactivateProxyResource, deleteProxyResource, and getAccountBalance visible among the 19 enabled tools

fig. 5 — the six steps below, in one continuous session against the same account

Step 1 — balance, in plain language

The simplest sanity check skips the dashboard tab entirely. Asking the agent “What is my 2extract balance?” triggers one tool call — getAccountBalance — and comes back in about 22 seconds with the wallet figure precise to six decimal places, not just the rounded number the UI shows:

{
  "balance_display": "$4.75",
  "balance_exact": "$4.746951",
  "note": "enough to create a new proxy"
}

📷 getAccountBalance answering a plain-language balance check — the agent surfaces both the rounded and the exact figure

Step 2 — geo lookup, the same targeting table without a docs tab

Fig. 1 above warned that every geographic suffix has to come from 2extract’s own catalog, never guessed — a state name that’s off by one word doesn’t error, it just silently gets ignored as a targeting parameter. searchGeoRegions puts that catalog directly in the agent’s hands. Asking for Germany’s regions returns the exact slugs the proxy username expects:

Region	Username param
Baden-Württemberg	`badenwurttemberg`
Bavaria	`bavaria`
North Rhine-Westphalia	`northrhinewestphalia`
Saxony	`saxony`
Thuringia	`thuringia`

📷 searchGeoRegions resolving Germany’s states into the exact -state-… slugs the proxy username expects

Notice what the agent volunteered on its own, unprompted: “do not guess them.” That’s the same warning this article gives around fig. 1 — now enforced by the tool call itself instead of a paragraph the reader has to remember.

Step 3 — creating the proxy the lookup was for

With the slug in hand, the same session calls createProxyResource — the tool sitting right next to getAccountBalance in the panel above — with targeting baked into the proxy itself rather than appended per request:

{
  "tool": "createProxyResource",
  "arguments": {
    "name": "de-baden-agent-demo",
    "type": "residential",
    "country": "DE",
    "state": "badenwurttemberg"
  }
}

The response is the same base-username shape described earlier in this article — 2xt-customer-[CLIENT_ID]-proxy-de-baden-agent-demo — except this proxy now carries Baden-Württemberg targeting as its default, so a plain request with no suffix at all still exits from that region.

Step 4 — a spend cap, without leaving the chat

Step 6 of the account-setup checklist above says to cap spend on every proxy from the dashboard. Over MCP that’s a second tool call on the proxy just created, rather than a separate trip to a settings page:

{
  "tool": "setProxyLimit",
  "arguments": {"proxy": "de-baden-agent-demo", "monthly_cap_usd": 5}
}

At a $4.75 balance that cap doesn’t do much for a demo account, but it’s the same guardrail every production proxy behind this article’s three pipelines runs under — just set through the agent instead of the dashboard.

I deactivated and deleted this demo proxy right after testing it — deactivateProxyResource and deleteProxyResource sit right next to createProxyResource in the same tools panel from the setup screenshot above — so a five-minute demo doesn’t sit around inflating the account’s proxy count. That’s also why the dashboard in the next step still shows exactly one active proxy, not two.

Step 5 — checked against the dashboard, not instead of it

MCP doesn’t read from a separate ledger. The balance, the proxy that now exists, and its cap all show up identically in 2extract’s own web dashboard, because both surfaces read and write the same account state. Asking the agent for a wider view confirms it: a 30-day traffic-and-spend rollup pulls the same wallet history the dashboard’s usage tab shows, broken down by the day it actually happened:

📷 a 30-day usage rollup pulled through MCP — $0.25 spent, 60.5 MB of traffic, all through one proxy — the same figures the dashboard’s usage tab reports for that account and window

And the dashboard itself, checked separately right after, confirms it — balance, active-proxy count, and 30-day traffic all lining up with what the agent had just reported:

📷 the account’s own dashboard — 60.49 MB over 30 days and one active proxy, matching what the agent had just reported

Balance, active-proxy count, and traffic all line up across both views — $4.75 agent-side versus $4.74 on the dashboard is a rounding difference, not a discrepancy, and one active proxy shows on both because the demo proxy was deleted right after use. Same account, two windows onto it — not a shortcut around the dashboard, a second way to reach it.

Step 6 — one request actually routed through the result

Everything so far provisions a proxy; it doesn’t yet prove a byte moved through it. Closing the loop means one ordinary HTTP request, made the usual way, through the exact username MCP just created:

username = "2xt-customer-[CLIENT_ID]-proxy-de-baden-agent-demo"
proxies = {"http": f"http://{username}:{password}@proxy.2extract.net:5555",
           "https": f"http://{username}:{password}@proxy.2extract.net:5555"}
response = requests.get("https://httpbin.org/ip", proxies=proxies, timeout=30)
print(response.json())

{"origin": "89.204.xx.xx"}

That IP resolves to a Baden-Württemberg ISP block, confirming that the region MCP looked up, and the proxy MCP created, is the region a request actually exits from — the same exit.* field the Python pipelines below log on every row, for exactly this reason.

Three pipelines, three genuinely different datasets

Everything above ran through an agent calling MCP tools. The rest of this guide is the same account, driven by code instead — three pipelines built on one client, for anyone who wants to own the collection logic rather than hand it to an agent.

Geo and address: proof of exit location

def _rotating_geo_targeting(country: str) -> Targeting:
    """A fresh -session id forces 2extract to assign a new exit IP for this hop."""
    return Targeting(country=country, session=uuid.uuid4().hex[:12], time_minutes=1)

def scrape_steam_price(client, *, country, app_id="730", targeting=None):
    url = f"https://store.steampowered.com/app/{app_id}/"
    response = client.get(url, targeting=targeting or Targeting(country=country),
                           params={"cc": country}, timeout=45)
    soup = BeautifulSoup(response.text, "lxml")
    price = soup.select_one("div.game_purchase_price") or soup.select_one("[data-price-final]")
    return {"app_id": app_id, "price": price.get_text(strip=True) if price else None,
            "http_status": response.status_code}

A row from an actual checkpoint file:

{
  "row_number": 41,
  "collected_at": "2026-08-14T09:12:03Z",
  "label": "DE",
  "targeting": {"country": "de", "session": "a91fe2c7b1d4"},
  "exit": {"ip": "89.204.xx.xx", "city": "Frankfurt", "region": "Hesse"},
  "ok": true
}

eCommerce and price monitoring

def parse_price(text):
    raw = " ".join(str(text).split())
    currency = "GBP" if "£" in raw else "EUR" if "€" in raw else "USD" if "$" in raw else None
    match = re.search(r"[\d]+\.[\d]+|[\d]+", raw.replace(",", ""))
    value = float(match.group(0)) if match else None
    return raw, value, currency

Output lands in data/output/eCommerce.xlsx. Sessions stay sticky for the length of a category walk.

Market research

Pulls from Hacker News through the Algolia API and from Open Food Facts. Its “sentiment” field is an engagement heuristic derived from points and comment counts — not a human-coded label and not an LLM classifier.

From collected rows to a spreadsheet someone will actually open

Every pipeline writes a JSON checkpoint every hundred rows before touching Excel at all — a habit that’s saved a multi-hour run from a crashed openpyxl write more than once. The JSON-to-Excel step deliberately does no new computation; it only formats data that’s already been collected and validated.

How the package is actually organized

fig. 3 — three pipelines share one client; checkpoints guarantee reproducible dataset generation

None of the three pipeline scripts talk to requests directly. They all go through a single ExtractClient that owns authentication, retry behavior, and username construction, and that boundary is the entire reason adding the market research pipeline took an afternoon instead of a week.

Residential, mobile, or datacenter — the real tradeoffs

	Datacenter	Residential	Mobile
Exit looks like	Cloud ASN (AWS, GCP)	Home ISP connection	Carrier IP
Block / CAPTCHA rate	Highest	Low	Lowest
Typical cost	Cheapest	Mid	Highest
Targeting granularity	Data center location only	Country / state / city / ZIP	Carrier (ISP code) only
Best fit	Low-stakes internal jobs	eCommerce, geo, market research	Mobile-specific SERPs, app traffic

Where this actually breaks

Country targeting is a request sent to the gateway, not a legal guarantee that a specific city lands on every hop — the only trustworthy record of where a request actually exited is the exit.* data that comes back, and it belongs in every row you store. JSON APIs like DummyJSON and Open Food Facts still return a bare 403 without a properly set User-Agent, proxy or not. Matching “the same product” across two catalogs by name and brand is a heuristic, not an identity. And none of this changes the baseline fact that a proxy alters your odds of getting a response, not your obligation to respect what a site’s terms of service actually say.

How this was tested

Every pipeline described above was run end-to-end against a live 2extract account — real wallet balance, real proxy status states, real 407s along the way — not just read from documentation. The MCP walkthrough was run against that same account in the same session; the balance, geo slugs, and traffic figures shown are what the account actually returned, not stand-ins. The full source is on GitHub under an MIT-style layout anyone can clone and rerun against their own account.

Glossary

Term	Meaning
Exit IP	The public address the target website actually sees
Gateway	`proxy.2extract.net:5555`
Base username	`2xt-customer-…-proxy-name`, with no targeting suffix appended
Sticky session	Same exit IP held across many requests via `-session` (+ optional `-time`)
407	Gateway rejected authentication or proxy status — rarely the password itself
PAYG	Wallet billed per traffic used, not an unlimited monthly crawl allowance
MCP server	`mcp.2extract.com` — exposes balance, proxies, and the geo catalog as tools an agent can call

Questions worth asking before rolling this into production

Does 2extract scrape websites for me?

It doesn’t — it’s a proxy network that routes requests through residential or mobile exit IPs. Parsing, deduplication, and storage stay entirely the collector’s job.

Does 2extract have an MCP server for agents like Claude, Cursor, or Codex?

Yes — mcp.2extract.com exposes the same account (balance, proxies, geo catalog, traffic history) as tools any MCP-capable agent can call directly, as shown in the walkthrough above. It’s the same wallet and the same billing as the Python package; MCP just hands the control plane to the agent instead of a human writing the client.

Why does a request get a 407 even when the password is definitely right?

In practice that almost always means the proxy is set to Inactive in the dashboard, the wallet balance has run out, or the username carries malformed or conflicting targeting parameters.

Can country targeting and mobile ISP targeting live on the same proxy?

They can’t. Geographic targeting and network targeting are mutually exclusive on a single username, and mobile ISP targeting needs its own dedicated proxy credential besides.

Is a separate proxy needed for every country being targeted?

No — one residential proxy covers every supported country. Targeting is a parameter appended per request, not a separate product purchased per market.

Does a residential proxy make scraping any site legally safe?

It doesn’t. A proxy changes what IP a target sees; it has no bearing on robots.txt, a site’s terms of service, or copyright law.

dbt State: Stop Rebuilding What Hasn’t Changed

Every hour, on a schedule, your dbt job rebuilds every model in your project. The staging models run because they always run. The intermediate joins run because they always run. The mart that powers the executive dashboard runs because, well, it always runs — even when not a single row in any upstream table changed since the last execution.

This is the pattern most teams have lived with for years. It is simple, predictable, and expensive. And as of September 16, 2026 — announced at dbt Summit in Las Vegas — there is a better way.

dbt State went generally available this week. It checks your warehouse metadata and compares model SQL fingerprints to your previous run’s manifest, then decides for each model: BUILD it (logic or data changed), CLONE it (data is fresh enough to reuse), or SKIP it (nothing changed, mark success instantly). The result, in production at companies like RxBenefits and Virgin Media O2, is a 25–59% cut in warehouse compute and significantly faster job run times — without changing a single line of your transformation SQL.

TL;DR

dbt State evaluates each model on every run and assigns one of four actions: REBUILD (logic or upstream changed), CLONE (use last result — data fresh enough), or SKIP (nothing changed). The decision logic runs before any warehouse query fires.
It works with dbt v1.7 through v2, across Snowflake, BigQuery, Databricks, and Redshift. You don’t need dbt v2 to start — but v2’s Rust engine makes the evaluation dramatically faster on large projects.
lag_tolerance is the key new model-level config: it declares how stale a model is allowed to be before it must be rebuilt. A mart that’s fine with 24h-old data can run on a once-daily schedule automatically, while a fct_orders with a 5-minute tolerance gets rebuilt the moment upstream data changes.
dbt State is a paid add-on to the dbt platform (dbt Cloud). It is not available as a standalone open-source feature in dbt-oss. The --defer and state:modified selector in dbt Core remain free and open-source, but they require you to manage the state artifact yourself and don’t include the warehouse metadata check.
The MCP Server integration announced at dbt Summit 2026 means AI agents querying your dbt project’s lineage can now see freshness status per model — which makes lag_tolerance a governance contract, not just a cost lever.

How dbt State Evaluates Every Model

The evaluation logic runs before any warehouse query fires. dbt State pulls two things: the current manifest (your project’s compiled SQL and config fingerprints) and warehouse metadata for each model’s underlying table. It compares both against the previous run’s state artifact. The result is a per-model action, decided before the job touches compute.

📷 three checks, four outcomes — all evaluated before a single warehouse query fires

The CLONE action is the one most teams don’t expect. When upstream data hasn’t changed and the model is outside its lag_tolerance window (meaning it’s already stale but not yet due for a rebuild), dbt State clones the last successful result — copying the table metadata rather than re-executing the SQL. The downstream model sees a fresh table reference, the scheduler marks the task successful, and no compute was consumed. This is what accounts for the bulk of the cost savings in the production numbers above.

What Actually Changes Per Run

📷 only the models whose sql or upstream data changed get rebuilt — the rest are skipped or cloned without touching compute

The example above shows a realistic scenario: one staging model changed (an engineer added a new column), which cascades to the intermediate model that joins it, which cascades to the mart. Three models rebuild. Five skip. If those five models typically consume 62% of your job’s warehouse credit spend, that’s 62% saved on that run — every run where this pattern holds.

The cascade detection is automatic. dbt State uses your DAG’s lineage to trace which downstream models depend on a changed model, and flags those for rebuild too. You don’t configure cascades manually. The same lineage your DAG tracks for documentation is what dbt State uses to propagate change detection.

Setting Up dbt State

Configuration is two steps: enable the feature in your job settings and add lag_tolerance to the models that should define their own freshness contract.

Enable dbt State on a job

# In dbt Cloud: Job Settings → Advanced → Enable dbt State
# Or via the dbt platform UI under the job's configuration panel

# For local / self-hosted runs with dbt v1.7+:
# Set the state artifact path — dbt State compares against this
dbt run --state path/to/previous/run/artifacts --defer

dbt State (GA, paid) vs –defer (free, open-source): the --defer flag and state:modified selector have existed in dbt Core since v1.1 and are fully open-source under Apache 2.0. They require you to store and pass the state artifact manually and don’t include warehouse metadata checks. dbt State (the GA product) automates the artifact lifecycle, adds the warehouse metadata evaluation step, and includes the CLONE action. If you’re on dbt Core and not on the dbt platform, --defer is still a meaningful free starting point.

Add lag_tolerance to your models

lag_tolerance is a model-level config that tells dbt State how stale this model’s data is allowed to be. It’s declared in your model’s YAML config and becomes part of the compiled manifest.

# models/marts/fct_orders.yml
models:
  - name: fct_orders
    description: "Order-level fact table, refreshed near-real-time."
    config:
      lag_tolerance: "5 minutes"   # rebuild if upstream data is > 5min old

  - name: dim_products
    description: "Product dimension, refreshes once daily."
    config:
      lag_tolerance: "24 hours"    # skip up to 24h before forcing rebuild

  - name: dim_customers
    description: "Customer dimension — changes are rare, 6h tolerance fine."
    config:
      lag_tolerance: "6 hours"

Once lag_tolerance is set, dbt State uses it to decide the SKIP vs CLONE vs REBUILD outcome for that model on every run. A model with no lag_tolerance set defaults to always rebuilding if upstream data changed — the safe, conservative behaviour.

📷 lag_tolerance moves freshness logic from the scheduler into the model — the right place for it to live

The before/after here is architectural, not just cosmetic. When freshness is a scheduler setting, it’s invisible to anyone reading your model code — and it’s invisible to AI agents querying your project’s lineage through the dbt MCP Server. When it’s lag_tolerance in the model YAML, it’s part of the model’s contract: auditable, version-controlled, and queryable by anything that reads your dbt manifest.

The Four Actions: BUILD, CLONE, SKIP, DEFER

Action	When it fires	Warehouse compute?	Result
BUILD	SQL or config changed, OR upstream data refreshed and model is past lag_tolerance	Yes — full model run	New table written
CLONE	Upstream data refreshed but model is within lag_tolerance	No — metadata copy only	Last result reused via zero-copy clone
SKIP	Nothing changed — SQL, config, and upstream data all identical to last run	No	Task marked success, no compute touched
DEFER	Model not in scope for this run (e.g. CI run scoped to modified models only)	No — references upstream environment	Uses production artifact, not dev rebuild

DEFER is worth calling out separately because it predates dbt State and is often confused with it. DEFER is the --defer flag used in CI: when running a slim CI job scoped to state:modified+, models outside the scope defer to the production environment rather than rebuilding from scratch. dbt State’s BUILD/CLONE/SKIP logic sits on top of DEFER — they solve different problems and work together.

dbt State and AI Agents

One of the less-discussed implications of dbt State is what it means for agentic workflows. If you’re running LLM-driven pipelines in Airflow that trigger dbt jobs, or using the dbt MCP Server to let AI agents query your project — lag_tolerance becomes a governance guardrail, not just a cost setting.

Before dbt State, an AI agent with access to trigger a dbt run could rebuild your entire DAG on every invocation. That’s expensive and often unnecessary. With lag_tolerance set per model, dbt State enforces the freshness contract regardless of what triggered the run — a human, a scheduler, or an agent. The agent cannot force a rebuild of dim_products that’s only 2 hours old when the model declares a 24-hour tolerance. The infrastructure enforces it.

This also matters for the dbt MCP Server announced GA at dbt Summit 2026: agents querying your dbt project’s lineage and metrics can now see lag_tolerance as part of model metadata, letting them make informed decisions about whether a model’s data is fresh enough for a given question without triggering an unnecessary rebuild.

The Gotchas

dbt State (the GA product) is a paid dbt platform feature — not open-source.The confusion comes from dbt Core’s long-standing --defer and state:modified selectors, which are free and Apache 2.0. dbt State (GA) is a separate product that automates state artifact management, adds warehouse metadata evaluation, and includes CLONE. It requires a dbt Cloud account. If you’re self-hosting dbt Core, you can get partial coverage with --defer and a CI artifact pipeline, but the warehouse metadata check and automatic CLONE action are not available without the platform.

lag_tolerance with no upstream change monitoring is half the feature.lag_tolerance tells dbt State how stale a model is allowed to be. But dbt State also needs to know when upstream data actually changed — which it determines from warehouse metadata. On Snowflake and BigQuery this works out of the box. On Redshift and Databricks, table-level metadata freshness signals vary by storage format and configuration. Test the upstream detection on your specific adapter before assuming it works correctly.

The state artifact must match the target environment.dbt State compares your current run against a previous run’s artifact. If you run dev builds against a dev schema and then compare against a production artifact, the comparison may see false-positive changes (different schemas, different table names). Always compare like-for-like: production artifacts against production runs, dev artifacts against dev runs. The dbt platform manages this automatically; on self-hosted setups this is your responsibility.

Models with Python logic can’t be CLONED on all adapters.Python models in dbt (dbt-py) use a different execution path than SQL models. On Snowflake, Python model results can be cloned. On BigQuery and Databricks, Python model CLONE support is adapter-dependent and may fall back to SKIP or BUILD depending on your version. Check the dbt State setup docs for your adapter’s current support matrix before setting lag_tolerance on Python models.

Incremental models interact with dbt State differently than table models.For an incremental model, dbt State still evaluates whether SQL or config changed. But if the model is configured as incremental and upstream data refreshed, dbt State rebuilds using the incremental strategy — it does not CLONE the last full result. This is correct behaviour, but it means incremental models with frequent upstream updates may not see the same CLONE savings as table models. If you’ve been working through the antipatterns we covered in our incremental model guide, dbt State respects all those strategies exactly as configured.

The One Principle

“Put freshness in the model, not the scheduler. lag_tolerance is a contract — version-controlled, auditable, and enforceable by infrastructure, not by whoever remembers to update the cron.”

FAQ

What is dbt State and when did it go GA?

dbt State is a dbt platform feature that evaluates each model on every run and assigns an action — BUILD, CLONE, or SKIP — based on whether the model’s SQL changed, whether upstream data refreshed, and whether the model is within its declared lag_tolerance freshness window. It went generally available on September 16, 2026, announced at dbt Summit in Las Vegas. It runs on dbt v1.7 through v2, across Snowflake, BigQuery, Databricks, and Redshift.

Is dbt State free or a paid feature?

dbt State (the GA product with automatic warehouse metadata evaluation and CLONE) is a paid add-on to the dbt platform (dbt Cloud). The related open-source features — the –defer flag and state:modified selectors — remain free and Apache 2.0 licensed in dbt Core. They require manual state artifact management and don’t include the warehouse metadata check or automatic CLONE action.

What does lag_tolerance do in dbt?

lag_tolerance is a model-level config that declares how stale a model’s data is allowed to be before it must be rebuilt. Set it in your model’s YAML config — for example, “24 hours” for a slowly-changing dimension or “5 minutes” for a near-real-time fact table. dbt State reads this at runtime and uses it to decide whether to CLONE (reuse last result — data is fresh enough) or SKIP (nothing changed at all) rather than running a full rebuild.

What’s the difference between CLONE and SKIP in dbt State?

SKIP fires when nothing changed at all — SQL, config, and upstream data are all identical to the last run. CLONE fires when upstream data refreshed but the model is still within its lag_tolerance window, meaning it doesn’t need to rebuild yet. CLONE copies the table metadata (a zero-copy operation on Snowflake) so downstream models see a valid reference without any compute. Both actions consume no warehouse compute; CLONE is the one that handles partial staleness gracefully.

Do I need dbt v2 to use dbt State?

No. dbt State works with dbt v1.7 through v2. You don’t need to migrate to v2 to start saving on compute. That said, dbt v2’s Rust-based engine evaluates the state logic significantly faster on large projects — so teams with hundreds of models will see evaluation-time improvements by upgrading, on top of the compute savings from State itself.

How does dbt State interact with incremental models?

For incremental models, dbt State still evaluates SQL and config changes. If the model needs to run, it uses your configured incremental strategy (merge, insert-overwrite, etc.) as normal. Incremental models do not get CLONE — if upstream data refreshed, dbt State rebuilds them incrementally rather than reusing the last result. Models configured as table or view materializations benefit most from CLONE.

September 20, 2026

Running LLM Tasks in Apache Airflow with the common.ai Provider

For the past two years, the standard pattern for running LLM calls in Airflow was a PythonOperator that imported the OpenAI client, called the API, and returned the result as XCom. It worked. But when the call failed at 3 AM, Airflow retried the entire task — including the database query that produced the input. When a model call consumed 50,000 tokens more than expected, there was nothing in the task logs to show it. When someone asked “can we have a human approve this before the next step runs?” the answer was “not without custom callback plumbing.”

The apache-airflow-providers-common-ai package, released in April 2026 for Airflow 3.0+, changes all of that. Every LLM call becomes its own named, logged, retryable Airflow task. Token budgets are enforced at the operator level. Human-in-the-loop approval is a single parameter. And from version 0.3.0 onward, LLMRetryPolicy lets an LLM classify the error before deciding whether to retry — so a rate-limit gets a smart backoff and an expired API key fails immediately rather than wasting five retry attempts.

This guide covers the full operator surface, the patterns that are working in production, and the gotchas that aren’t in the quickstart.

TL;DR

The common.ai provider (GA April 13 2026) ships five operators: LLMOperator, LLMBranchOperator, LLMSQLQueryOperator, AgentOperator, and DocumentLoaderOperator — each has a matching @task decorator.
All operators are backed by PydanticAI and work with any provider it supports: OpenAI, Anthropic, Google, Bedrock, Vertex, Ollama, or any OpenAI-compatible endpoint. You configure the model via a pydanticai Airflow connection.
UsageLimits enforces per-task token and request budgets at runtime — when the limit is hit, the task fails and Airflow’s standard retry policy applies on top.
LLMRetryPolicy (v0.3.0+, requires Airflow ≥ 3.3.0) asks an LLM to classify each task failure and returns RETRY, FAIL, or SKIP — with a reason string that appears in the task logs.
Human-in-the-loop approval is built in: set require_approval=True on any @task.llm and the DAG pauses in awaiting_input state until a reviewer approves or rejects the LLM output from the Airflow UI.
The provider emits OpenTelemetry GenAI spans for every model call and tool call, routed through Airflow’s existing OTel exporter — no separate tracing setup needed.
For privacy-sensitive environments, point LLMRetryPolicy at a local Ollama model so exception traces never leave your infrastructure.

Why Burying LLM Calls in PythonOperator Was Always Wrong

The old pattern wasn’t wrong because it failed — it was wrong because failures were invisible. A PythonOperator that calls an LLM is, from Airflow’s perspective, a black box. The scheduler knows the task started and whether it succeeded or failed. It knows nothing about how many tokens were consumed, what model was used, how long the LLM call took relative to the surrounding code, or what the model actually returned before XCom captured the final value.

📷 every capability the old pattern lacked is a first-class parameter in the new operators — nothing to build from scratch

The new operators expose each LLM interaction as a distinct unit of work in the DAG graph. Retry logic applies specifically to the LLM call. Token consumption appears in task metadata. The model response is visible in XCom before any downstream task consumes it. And when something goes wrong, the task log says why the model call failed — not just that the PythonOperator raised an exception.

The Operator Surface: Five Tools, Clear Boundaries

📷 five operators, five distinct jobs — picking the wrong one is the most common setup mistake

Operator / Decorator	Best For	Avoid When	Since
`LLMOperator` / `@task.llm`	Single-turn: classify, summarize, extract, structured Pydantic output	Agent needs tool calls or multi-turn reasoning	v0.1.0
`LLMBranchOperator` / `@task.llm_branch`	LLM picks the next task from a declared list of choices	Branch logic can be expressed as a simple conditional	v0.1.0
`LLMSQLQueryOperator` / `@task.llm_sql`	NL → SQL → execute against a DB connection, returns rows	Generated SQL must be reviewed by a human before execution	v0.1.0
`AgentOperator` / `@task.agent`	Multi-turn loop with HookToolset, SQLToolset, or custom tools	Task is single-turn — use LLMOperator, it’s simpler	v0.1.0
`DocumentLoaderOperator`	Parse text, CSV, JSON, PDF, DOCX into `list[dict]` for downstream embedding	You need to embed on the fly — pair with a vector store operator	v0.3.0
`LLMSchemaCompareOperator`	Compare two schema versions, return structured diff and compatibility flag	Schema drift monitoring at scale — runs per-table, not per-schema	v0.2.0

The @task.llm Pattern in Practice

The decorator pattern is the cleanest entry point. The function body returns the user prompt as a string. Everything else — model routing, structured output, token limits, retry policy, human approval — is configured as decorator arguments.

from airflow.sdk import dag, task
from pydantic import BaseModel, Field
from pydantic_ai.usage import UsageLimits
from typing import Literal

class TicketTriage(BaseModel):
    summary: str = Field(description="Two sentences max, plain language.")
    priority: Literal["P0","P1","P2","P3","P4"]
    needs_human_review: bool

@dag(schedule=None, tags=["ai","support"])
def support_triage():

    @task.llm(
        llm_conn_id="pydanticai_default",          # Airflow connection: model + API key
        system_prompt=(
            "You triage incoming support tickets. "
            "Do NOT answer the ticket — only classify it."
        ),
        output_type=TicketTriage,                  # structured Pydantic output
        usage_limits=UsageLimits(
            request_limit=2,
            total_tokens_limit=4_000,              # fail task if exceeded
        ),
        require_approval=False,                    # set True to pause for human review
        retries=3,
    )
    def triage_ticket(ticket: dict) -> str:
        # function body returns the user prompt only
        return f"Triage this ticket:\n\n{ticket['body']}"

    @task
    def route_ticket(triage: TicketTriage):
        if triage.priority in ("P0", "P1"):
            print(f"URGENT: {triage.summary}")
        # downstream logic here

    tickets = [{"body": "Production DB down, all writes failing"},
               {"body": "Can you add dark mode to the dashboard?"}]

    results = triage_ticket.expand(ticket=tickets)  # Dynamic Task Mapping
    route_ticket.expand(triage=results)

support_triage()

Two things to notice. First, output_type=TicketTriage tells the operator to parse the model response into a validated Pydantic object — the downstream task receives a TicketTriage instance, not a raw string. Second, .expand(ticket=tickets) uses Airflow 3’s Dynamic Task Mapping to fan out one LLM task per ticket. Each becomes its own named, independently retryable task in the DAG — exactly the observability pattern the old PythonOperator loop couldn’t provide.

LLMRetryPolicy: The Error Classification Layer

Static retry policies treat all failures identically. A rate-limit error gets the same 60-second wait as a malformed API key. A task that failed because the model returned invalid JSON for a structured output retries with the exact same prompt that just failed. None of this is useful. LLMRetryPolicy, introduced in v0.3.0 and requiring Airflow 3.3+, replaces this with a classification step.

📷 llmretrypolicy fires between the failure and the next attempt — the scheduler stays fully deterministic, the llm only advises

The critical design point: the LLM does not modify the DAG, interact with workers, or make any scheduling decisions. It reads the exception class, message, and traceback, then returns one of three actions: RETRY (with an optional delay), FAIL (immediately, no more retries), or SKIP (mark the task as skipped and continue the DAG). The scheduler enforces the action. The LLM is advisory only — this is not autonomous AI modifying production infrastructure.

from datetime import timedelta
from airflow.sdk import task
from airflow.providers.common.ai.retry_policies import (
    LLMRetryPolicy, RetryRule, RetryAction
)

SNOWFLAKE_INSTRUCTIONS = """
You classify Snowflake query task failures. Rules:
- OperationalError with "Connection reset": RETRY after 30s
- ProgrammingError with "SQL compilation error": FAIL immediately — bad SQL won't fix itself
- ConnectTimeout: RETRY after 60s, up to 3 times
- Any auth/credential error: FAIL immediately — retrying costs tokens for no benefit
- Unknown errors: RETRY once after 30s, then FAIL
Return 0 for errors that should not retry.
"""

snowflake_policy = LLMRetryPolicy(
    llm_conn_id="pydanticai_default",
    instructions=SNOWFLAKE_INSTRUCTIONS,
    # fallback_rules fire without an LLM call — cheap and deterministic
    fallback_rules=[
        RetryRule(
            exception=ConnectionError,
            action=RetryAction.RETRY,
            retry_delay=timedelta(seconds=30),
        ),
    ],
)

@task(retries=5, retry_policy=snowflake_policy)
def run_snowflake_enrichment(batch_id: str):
    # your Snowflake query logic here
    ...

Privacy note on LLMRetryPolicy: by default, the exception message and traceback are sent to whichever model your llm_conn_id points to. If your exceptions may contain sensitive data — customer IDs in query parameters, table names from restricted schemas — either sanitise the traceback in instructions or point the policy at a local Ollama instance so the data never leaves your infrastructure: LLMRetryPolicy(llm_conn_id="ollama_local", model_id="ollama:llama3.2").

Human-in-the-Loop Approval

One of the most-requested Airflow features for AI pipelines is the ability to pause a DAG and wait for a human to review an LLM output before proceeding. The common.ai provider builds this directly into the operator with a single parameter. When require_approval=True, the task completes its LLM call, writes the output to XCom, and then transitions to awaiting_input state. A reviewer opens the Airflow UI, reads the generated output, and either approves (DAG continues) or rejects (task marked failed).

@task.llm(
    llm_conn_id="pydanticai_default",
    system_prompt="Draft a customer-facing email response to this support ticket.",
    require_approval=True,       # pause here for human review
    allow_modifications=True,    # reviewer can edit the text before approving
    approval_timeout=timedelta(hours=4),  # auto-fail if no review after 4h
)
def draft_response(ticket: dict) -> str:
    return f"Write a response to: {ticket['body']}"

On Airflow 3.3+, the awaiting_input task state is natively supported — the provider uses it directly. On earlier 3.x versions it falls back to a DEFERRED state with a sensor polling for the approval signal. The allow_modifications=True parameter lets the reviewer edit the LLM’s draft in the UI before approving, so the approved text (not the raw model output) flows into XCom for downstream tasks.

AgentOperator and SQLToolset

For multi-turn workflows where the model needs to decide which tools to call and when, AgentOperator is the right choice. The most practical out-of-the-box toolset is SQLToolset, which gives the agent the ability to discover schema, run queries, and check results — all against a configured Airflow DB connection.

from airflow.sdk import dag, task
from airflow.providers.common.ai.toolsets.sql import SQLToolset
from pydantic_ai.usage import UsageLimits

@dag(schedule="@daily", tags=["ai","analytics"])
def daily_anomaly_report():

    @task.agent(
        llm_conn_id="pydanticai_default",
        system_prompt=(
            "You are a data analyst. Investigate the anomalies table "
            "and produce a concise summary with root causes and affected row counts."
        ),
        toolsets=[
            SQLToolset(
                conn_id="snowflake_prod",
                allowed_tables=["anomalies", "orders", "customers"],  # enforce allowlist
            )
        ],
        usage_limits=UsageLimits(total_tokens_limit=20_000),
        retries=2,
    )
    def investigate_anomalies(run_date: str) -> str:
        return f"Investigate anomalies logged on {run_date}. Focus on order_id failures."

    investigate_anomalies(run_date="{{ ds }}")

daily_anomaly_report()

The allowed_tables parameter on SQLToolset is not just advisory — as of v0.5.0 (June 2026), it parses the agent’s SQL with sqlglot and rejects any query that touches a table outside the allowlist before execution. This includes subqueries, CTEs, JOINs, and set operations. It is an application-level guardrail, not a substitute for least-privilege DB permissions — use both.

If you’re running these agents against Snowflake and want to understand what they’re consuming token-wise, the same CORTEX_AGENT_USAGE_HISTORY monitoring patterns from our Cortex AI monitoring guide apply — except here the calls originate from Airflow rather than from inside Snowflake. You’ll want to add QUERY_TAG or a custom logging step to tie Airflow task IDs to Snowflake session metadata.

Connecting the Model: PydanticAI Connections

Every operator takes a llm_conn_id that points to a pydanticai connection type in Airflow. The connection stores the model ID and API key. You set it up once; all operators share it.

# Via Airflow UI: Admin → Connections → Add
# Conn Type:  pydanticai
# Conn ID:    pydanticai_default
# Host:       (leave blank for cloud providers)
# Schema:     anthropic          # or openai, google-gla, bedrock, ollama
# Password:   your-api-key
# Extra:      {"model_id": "claude-sonnet-5"}

# Or via environment variable (for CI/CD):
export AIRFLOW_CONN_PYDANTICAI_DEFAULT='pydanticai://\
  :your-api-key@/?schema=anthropic&extra={"model_id":"claude-sonnet-5"}'

# Local / air-gapped: Ollama
# Schema: ollama   Host: http://localhost:11434
# model_id: llama3.2

One thing the old PythonOperator pattern couldn’t do: swap models without touching DAG code. Because the model is in the connection, you can maintain separate pydanticai_prod and pydanticai_dev connections pointing at different models and switch via Airflow Variables or environment overrides. This is particularly useful for local Ollama setups where you want to prototype with a small model before promoting to a frontier one.

The Gotchas

The provider is Airflow 3.0+ only — no backport to 2.x.This is stated explicitly in every release note. If your team is on Airflow 2.10 or earlier, none of these operators are available. The migration path to Airflow 3.0 involves removing execution_date references and updating provider pins — substantial work if your DAG codebase is large. Don’t plan a common.ai migration without first auditing your Airflow version.

LLMRetryPolicy requires Airflow ≥ 3.3.0 specifically.The policy object installs fine on 3.0 and 3.1 — there’s no import error. But the retry_policy parameter on @task is only honoured from 3.3.0 onward. On earlier versions the parameter is silently ignored and the task falls back to standard retry behaviour. Check airflow version before building retry logic around it.

UsageLimits failures are task failures — they trigger standard retries.When a task exceeds its UsageLimits, PydanticAI raises UsageLimitExceeded and the task marks FAILED. Airflow’s standard retry policy then applies on top. If you set retries=3 and the prompt is structurally over the token budget, you’ll burn three more calls before the task finally fails. Set retries=0 for token-budget failures, or add a LLMRetryPolicy rule that returns RetryAction.FAIL for UsageLimitExceeded.

SQLToolset’s allowed_tables allowlist rejects quoted identifiers.This is a documented limitation from v0.5.0: while an allowlist is active, SQL with quoted identifiers ("my_table" rather than my_table), inline comments, cross-database references, SHOW statements, table-valued functions, and dynamic SQL are all rejected before execution. Agents must send unquoted, comment-free SQL. If your schema uses quoted identifiers consistently, you’ll hit this immediately and need to decide whether to drop the allowlist or change the schema naming convention.

OpenTelemetry spans only export if your Airflow instance has an OTel exporter configured.The provider emits GenAI spans unconditionally, but they’re routed through Airflow’s existing OTel exporter. If you haven’t configured AIRFLOW__METRICS__OTEL_ON=True and an exporter endpoint, the spans are emitted and immediately dropped. Check your metrics config before assuming traces are flowing to your observability backend.

The One Principle

“Every LLM call is a task. If it isn’t a task, it isn’t observable, retryable, or governable — and those three things are what separate a prototype from production.”

FAQ

What is the Apache Airflow common.ai provider?

It’s a first-party Airflow provider (apache-airflow-providers-common-ai) that ships LLM-native operators for Airflow 3.0+. Released in April 2026, it lets you run LLM calls, agentic tool loops, NL-to-SQL tasks, and document parsing as standard Airflow tasks — with structured output, token budgets, human-in-the-loop approval, and intelligent retry policies built in. It uses PydanticAI under the hood and works with OpenAI, Anthropic, Google, Bedrock, Vertex, Ollama, and any OpenAI-compatible endpoint.

How does LLMRetryPolicy differ from standard Airflow retries?

Standard retries treat every failure identically — wait the configured delay, try again. LLMRetryPolicy sends the exception class, message, and traceback to an LLM, which classifies the error and returns one of three actions: RETRY (with a custom delay), FAIL (stop retrying immediately), or SKIP (mark the task as skipped and continue the DAG). The scheduler remains fully deterministic — the LLM only advises. LLMRetryPolicy requires Airflow 3.3.0 or later.

How do I add human approval to an Airflow LLM task?

Set require_approval=True on any @task.llm or LLMOperator. The task completes its model call, writes the output to XCom, and transitions to awaiting_input state. A reviewer approves or rejects via the Airflow UI. You can also set allow_modifications=True so the reviewer can edit the LLM output before approving, and approval_timeout to auto-fail if no review arrives within a set window.

Can I use the common.ai provider with local models?

Yes. Create a pydanticai connection with Schema set to “ollama”, Host set to your Ollama endpoint (e.g. http://localhost:11434), and model_id set to the local model name. This is especially useful for LLMRetryPolicy in privacy-sensitive environments where exception traces should not leave your infrastructure.

When should I use AgentOperator instead of LLMOperator?

Use AgentOperator when the task requires multiple tool calls or multi-turn reasoning — for example, querying a database, checking a result, then querying again based on what it found. Use LLMOperator for single-turn tasks like classification, summarization, or extraction that produce one output and finish. AgentOperator without toolsets is valid but if you don’t need tools, LLMOperator is simpler and more explicit.

Does the common.ai provider work with Dynamic Task Mapping?

Yes. All task-decorator variants ((@task.llm, @task.agent, etc.) support .expand() for Dynamic Task Mapping, so you can fan out one LLM task per item in a list. Each mapped instance becomes its own named, independently retryable task in the DAG graph — this is the core observability advantage over running a loop inside a single PythonOperator.

September 19, 2026

Snowflake Dynamic Data Masking & Row Access Policies: A Production Guide

The governance review lands on a Wednesday. Your company needs to prove that analysts in one region cannot see customer PII from another, that customer emails are masked for anyone below the data steward tier, and that — this one is new — AI agents querying your warehouse cannot extract raw PII even when the agent is running under a privileged role. You have two weeks.

Snowflake has the tools for all three. Dynamic Data Masking controls what value a user sees in a column. Row Access Policies control which rows they see at all. And since 2026, a new context function — IS_AGENT_ACTIVATED — lets your masking policies detect when a Cortex AI agent is making the query and mask accordingly, regardless of what role the agent is running under. Together, these three form a governance stack that scales from a single sensitive column to millions of rows across hundreds of tables — if you configure them correctly. If you don’t, they are bypassable in ways that are easy to miss until an auditor asks.

This article covers the mechanics, the production-scale patterns (tag-based masking in particular), and the gotchas that catch teams the first time.

TL;DR

Dynamic Data Masking (DDM) is column-level: it controls the value returned for a column based on the querying role. Row Access Policies (RAP) are row-level: they filter which rows are visible. Most enterprise setups need both.
Both features require Enterprise Edition or higher. Standard Edition provides basic RBAC but not policy-driven masking or row filtering.
A column can have only one masking policy attached at a time. Input and output data types must match exactly — you cannot mask a TIMESTAMP column and return a STRING.
Tag-based masking is the scalability unlock: attach a masking policy to a tag at the schema level and every new table with a matching column data type is automatically protected — no per-table ALTER COLUMN SET MASKING POLICY needed.
IS_AGENT_ACTIVATED is a new 2026 context function you can embed in masking policy CASE expressions to mask data from AI agents even when the agent’s role would otherwise allow plain-text access. This matters for MCP server setups and Cortex Agents.
POLICY_CONTEXT is your testing function — use it to simulate query execution as a specific role without switching sessions, so you can verify masking behavior before applying policies to production columns.
Row Access Policies using a mapping table must not use the protected table itself as the mapping table — Snowflake will reject it. External tables are also unsupported as mapping tables.

DDM vs Row Access Policies: The Conceptual Split

The confusion between these two features is understandable — both “restrict what users see” — but they operate at completely different layers. Understanding the split is the prerequisite for getting both right.

📷 ddm masks column values; rap filters rows — store data untouched in both cases, policy logic runs entirely at query time

The bottom of that diagram carries the most important fact: Snowflake never modifies or encrypts the stored data. Both policies evaluate entirely at query runtime. A row that is filtered by a Row Access Policy still exists in storage. A value masked to **** is still the original string on disk. This means Time Travel, data sharing, and cloning still work normally — but it also means a user with direct access to the underlying storage (unlikely, but worth knowing) would see plain text.

Dimension	Dynamic Data Masking	Row Access Policy
Controls	Column value (what is shown)	Row visibility (which rows exist)
Attachment	One policy per column	One policy per table or view
Data types	Input type must match output type	Always returns BOOLEAN (include/exclude)
Performance impact	Minimal — evaluates per column in result	COUNT(*) triggers full scan without clustering
Mapping table	Not applicable	Required for role-to-region entitlements
Works with streams	Yes	Yes — RAP applied when stream reads source table
Works with data sharing	Yes	Yes
Works with materialized views	Not directly — apply to base table instead	Yes
Edition required	Enterprise+	Enterprise+

Setting Up Dynamic Data Masking

The setup pattern is always the same three steps: create a policy, grant privileges, apply it to a column. The complexity is in the policy logic itself — getting the CASE conditions right so the right roles see the right data.

Creating and applying a basic masking policy

-- Step 1: Create a dedicated masking admin role (security officer)
CREATE ROLE masking_admin;
GRANT CREATE MASKING POLICY ON SCHEMA prod_db.sensitive_schema TO ROLE masking_admin;
GRANT APPLY MASKING POLICY ON ACCOUNT TO ROLE masking_admin;

-- Step 2: Create the masking policy (runs as masking_admin)
CREATE OR REPLACE MASKING POLICY prod_db.sensitive_schema.email_mask
  AS (val STRING) RETURNS STRING ->
  CASE
    -- AI agents: always mask regardless of role
    WHEN SYS_CONTEXT('SNOWFLAKE$CURRENT', 'IS_AGENT_ACTIVATED')::BOOLEAN = TRUE
      THEN REGEXP_REPLACE(val, '(^[^@]{2}).*(@.*$)', '\\1***\\2')
    -- Data stewards see plain text
    WHEN CURRENT_ROLE() IN ('DATA_STEWARD', 'PRIVACY_OFFICER')
      THEN val
    -- Analysts see partially masked email
    WHEN CURRENT_ROLE() = 'ANALYST'
      THEN REGEXP_REPLACE(val, '(^[^@]{2}).*(@.*$)', '\\1***\\2')
    -- Everyone else sees fully redacted
    ELSE '****@****.***'
  END;

-- Step 3: Apply to the column
ALTER TABLE prod_db.customers_schema.customers
  MODIFY COLUMN email
  SET MASKING POLICY prod_db.sensitive_schema.email_mask;

A few things to notice here. First, the IS_AGENT_ACTIVATED check comes before the role checks — that ordering matters. If a Cortex Agent is running under DATA_STEWARD, the role check would allow plain text, but the agent check intercepts it first. Second, the function signature declares val STRING and returns STRING — if you try to apply this policy to a TIMESTAMP column, Snowflake rejects it with a type mismatch error. One policy, one data type.

Testing with POLICY_CONTEXT before applying

Applying a masking policy to a production column and then testing it is backwards. Use POLICY_CONTEXT to simulate the query as a specific role without touching the policy attachment:

-- Simulate what ANALYST role would see on the email column
SELECT POLICY_CONTEXT(
  'SELECT email FROM prod_db.customers_schema.customers LIMIT 5',
  OBJECT_CONSTRUCT('role', 'ANALYST')
);

-- Simulate what DATA_STEWARD sees
SELECT POLICY_CONTEXT(
  'SELECT email FROM prod_db.customers_schema.customers LIMIT 5',
  OBJECT_CONSTRUCT('role', 'DATA_STEWARD')
);

This is the function the official column-level security docs recommend for pre-deployment validation. It also works for Row Access Policies and can simulate both simultaneously when a column is covered by both policy types.

Tag-Based Masking: The Scalability Pattern

Manual column-by-column masking breaks at scale. A schema with 200 tables, each with 3–5 PII columns, means 600–1000 individual ALTER COLUMN SET MASKING POLICY commands — and every new table added to that schema needs the same treatment. Miss one during a 2 AM data load and you’ve exposed PII until the next audit cycle.

Tag-based masking solves this by inverting the relationship: instead of attaching a policy to a column, you attach the policy to a tag, then tag the schema. Every column in every table in that schema with a matching data type gets automatically protected — including tables added in the future.

📷 tag the schema once — every future table with a string column picks up the mask automatically, no manual step needed

-- Create the tag
CREATE OR REPLACE TAG prod_db.governance_schema.pii_email
  COMMENT = 'Marks columns containing raw email addresses';

-- Bind the masking policy to the tag
ALTER TAG prod_db.governance_schema.pii_email
  SET MASKING POLICY prod_db.sensitive_schema.email_mask;

-- Apply the tag at schema level (protects all existing + future tables)
ALTER SCHEMA prod_db.customers_schema
  SET TAG prod_db.governance_schema.pii_email = 'true';

-- Verify which columns are now protected
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES
WHERE POLICY_DB = 'PROD_DB'
  AND POLICY_NAME = 'EMAIL_MASK'
ORDER BY REF_COLUMN_NAME;

One important limitation to know upfront: a column can be protected by a directly-assigned masking policy and a tag-based masking policy simultaneously — but if both exist, the directly-assigned policy takes precedence. That’s actually useful for exceptions: tag the schema for broad protection, then override specific columns with stricter or looser policies applied directly.

IS_AGENT_ACTIVATED: Governing AI Agent Access

This is the most operationally important addition to the masking feature set in 2026, and it lands squarely in the overlap between data governance and the AI agent infrastructure your team is probably already building.

The problem it solves: when a Cortex Agent — or any AI agent connecting through an MCP server — runs a query, it does so under a Snowflake role. If that role has ANALYST-level access to a table, the agent reads the same data an analyst would. But analysts are humans who can be trained not to copy PII out of their query results. An agent processing thousands of rows and writing results to an output table, or sending them to an external API, is a different risk profile entirely.

📷 is_agent_activated fires before the role check — a privileged agent gets the same mask as an unprivileged one

IS_AGENT_ACTIVATED is read via SYS_CONTEXT in the masking policy body. When Snowflake detects that the current session is an AI agent context — Cortex Agent, or a session initiated through the Cortex Agent API — this returns TRUE. The masking policy evaluates it before any role check, so the agent cannot bypass it by inheriting a privileged role.

-- Masking policy that distinguishes human analysts from AI agents
-- even when both share the same Snowflake role
CREATE OR REPLACE MASKING POLICY prod_db.sensitive_schema.pii_phone_mask
  AS (val STRING) RETURNS STRING ->
  CASE
    -- Block AI agents regardless of their active role
    WHEN SYS_CONTEXT('SNOWFLAKE$CURRENT', 'IS_AGENT_ACTIVATED')::BOOLEAN = TRUE
      THEN '***-***-****'
    -- Privacy officers see the real number
    WHEN CURRENT_ROLE() IN ('PRIVACY_OFFICER', 'DATA_STEWARD')
      THEN val
    -- Analysts see last 4 digits only
    WHEN CURRENT_ROLE() = 'ANALYST'
      THEN CONCAT('***-***-', RIGHT(val, 4))
    ELSE '***-***-****'
  END;

If you’re running Cortex AI at scale, this pattern should be in your governance playbook. The risk is real: an agent with a monitoring query that runs on millions of rows, combined with an output path to a downstream system or an email notification, can exfiltrate PII that your masking policies were designed to protect — simply because the agent’s role was granted for legitimate operational reasons, not for raw data access.

Row Access Policies: Controlling Visible Rows

A Row Access Policy returns a boolean expression that Snowflake evaluates per row. Rows where the expression returns TRUE are visible; rows where it returns FALSE are hidden as if they don’t exist. The standard pattern uses a mapping table that maps roles (or users) to the regions or segments they’re allowed to see.

-- Mapping table: which roles can see which regions
CREATE TABLE prod_db.governance_schema.region_access_map (
  role_name   VARCHAR,
  region_code VARCHAR
);

INSERT INTO prod_db.governance_schema.region_access_map VALUES
  ('EMEA_ANALYST',  'EMEA'),
  ('APAC_ANALYST',  'APAC'),
  ('US_ANALYST',    'US'),
  ('GLOBAL_ADMIN',  'EMEA'),
  ('GLOBAL_ADMIN',  'APAC'),
  ('GLOBAL_ADMIN',  'US');

-- Row Access Policy using the mapping table
CREATE OR REPLACE ROW ACCESS POLICY prod_db.governance_schema.region_row_policy
  AS (region_col VARCHAR) RETURNS BOOLEAN ->
  EXISTS (
    SELECT 1
    FROM prod_db.governance_schema.region_access_map
    WHERE role_name   = CURRENT_ROLE()
      AND region_code = region_col
  );

-- Apply to the orders table
ALTER TABLE prod_db.sales_schema.orders
  ADD ROW ACCESS POLICY prod_db.governance_schema.region_row_policy
  ON (customer_region);

-- Audit: confirm attachment
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES
WHERE POLICY_NAME = 'REGION_ROW_POLICY';

The mapping table pattern is flexible — you can join on user name instead of role, add additional segmentation dimensions, or drive it from a table managed by your identity system. The key constraint is that the protected table itself cannot be the mapping table. Snowflake rejects circular references. External tables are also unsupported as mapping tables.

The Gotchas

Dropping a masking policy that’s still attached fails — and the error is cryptic.You cannot DROP MASKING POLICY while it’s attached to any column. The error says the policy “cannot be dropped as it is associated with one or more entities.” To drop it: find all attachments with SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES WHERE POLICY_NAME = '<name>', then ALTER TABLE ... MODIFY COLUMN ... UNSET MASKING POLICY on each one, then drop. This is also why cloning a table with a masking policy requires the cloning role to have APPLY privileges — the clone carries the policy attachment.

Restoring a Time Traveled table can produce a masking policy error.If you drop a masking policy and then restore a table from Time Travel that was protected by that now-deleted policy, Snowflake throws: “Column already attached to a masking policy that does not exist.” The fix is to UNSET the ghost policy reference on the restored column and reapply a current policy. This is rare but will happen in a disaster recovery scenario if policies are managed separately from table DDL. Our Time Travel and Fail-safe guide covers the broader restore workflow.

A Row Access Policy on a large table without clustering turns COUNT(*) into a full scan.Without a RAP, SELECT COUNT(*) FROM big_table completes in milliseconds — Snowflake reads the metadata. With a RAP attached, Snowflake must evaluate the row filter for every row to count the visible ones, triggering a full table scan. For very large tables, clustering the table on the column used in the RAP filter (e.g. customer_region) lets Snowflake prune micro-partitions and dramatically reduces scan cost. The official Row Access Policy docs call this out explicitly under performance considerations.

Materialized views and masking policies don’t mix directly.You cannot create a materialized view that includes a column protected by a masking policy — Snowflake rejects it at creation time with an “unsupported feature” error. The workaround is to apply the masking policy after the materialized view is created, on the base table, not on the view. Alternatively, create the MV without the sensitive columns and handle masking in a view layer on top.

CURRENT_ROLE() returns the active role, not all roles the user has.A masking policy that checks CURRENT_ROLE() IN ('DATA_STEWARD') will not trigger for a user who has the DATA_STEWARD role but is currently operating under ANALYST. Users must explicitly USE ROLE DATA_STEWARD to activate that branch. If your governance model requires “any of your roles” logic, use IS_ROLE_IN_SESSION instead. This is one of the most common mis-implementations in the field and AI agent governance setups are particularly vulnerable because agents often operate under a single fixed role.

The One Principle

“Tag the schema, not the column. Write the policy once and let Snowflake enforce it on every table you load going forward — including the one at 2 AM that nobody remembers to govern manually.”

FAQ

What Snowflake edition is required for Dynamic Data Masking?

Enterprise Edition or higher is required for Dynamic Data Masking and Row Access Policies. Standard Edition provides basic role-based access control through privileges and object ownership, but not policy-driven column masking or row filtering. If you’re evaluating governance features, this edition requirement is often the first planning constraint to surface.

Can I apply two masking policies to the same column?

No. A column can have only one masking policy attached at a time. If you try to apply a second, Snowflake returns: “Specified column already attached to another masking policy.” The solution is to consolidate your logic into a single policy using CASE expressions, or to UNSET the existing policy before applying the new one. Tag-based policies and directly-assigned policies can coexist on the same column, but the directly-assigned policy takes precedence.

Does IS_AGENT_ACTIVATED work with MCP server connections?

Yes. When an AI agent connects to Snowflake through a managed MCP server and invokes a SQL tool, the resulting session is flagged as an agent context and IS_AGENT_ACTIVATED returns TRUE. This means masking policies with the IS_AGENT_ACTIVATED check will correctly block raw PII access for agents connecting via MCP — the same governance applies regardless of how the agent initiates the session.

How do I test a masking policy without applying it to a production column?

Use the POLICY_CONTEXT function. It simulates query execution as a specified role — including evaluating any masking or row access policies on the queried objects — and returns what that role would see. You can pass both role and session context into the simulation. This is the recommended pre-deployment validation approach from Snowflake’s own documentation.

Does a Row Access Policy affect Time Travel queries?

Yes. Row Access Policies apply to Time Travel queries using the AT or BEFORE clause — the policy evaluates against the current session context, not the historical role context. This means a user querying a historical snapshot only sees rows they are currently entitled to see, even if the entitlement table has changed since the historical timestamp.

Can Dynamic Data Masking and Row Access Policies be used together on the same table?

Yes, and this is the recommended pattern for most enterprise use cases. Row Access Policies filter which rows are returned; Dynamic Data Masking then controls what values are visible in those rows. Use POLICY_CONTEXT to simulate queries with both policies active simultaneously to verify the combined behavior before applying to production.

September 18, 2026

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

Blog

The Core Problem: Agents Know Schema, Not Meaning

How Cortex Sense Works: The Four Signal Types

Signal 1: Horizon Context Semantic Views

Signal 2: Query History

Signal 3: BI Dashboard Definitions

Signal 4: Object Metadata and Agent Skills

The Self-Correcting Loop: What Happens When Definitions Conflict

The Accuracy Impact, Contextualized

Cortex Sense vs Horizon Context: How They Fit Together

The Prebuilt Domain Plugins

What to Build Before Cortex Sense Goes GA

1. Audit your semantic view coverage

2. Use Semantic View Autopilot for the first 80%

3. Resolve your definition conflicts now

4. Connect your BI tools via Horizon Context

The Gotchas

FAQ

Knowing SQL isn’t the same as knowing how to solve interview SQL

The 60-second recognition reflex

The first question is often the easy part

Data Engineer interviews aren’t just SQL

Data modeling

Data pipelines

Cloud and warehouse systems

PySpark

dbt

This is why I built the Data Engineer’s Interview Playbook

What’s inside?

Part I — Core SQL

Part II — Advanced SQL

Part III — The rest of the interview

Every item is designed like an interview

It also includes a study system

Who is it for?

Who is it not for?

The goal isn’t to memorize 511 answers

Get the Data Engineer’s Interview Playbook

One final thought

The Plan-Then-Deploy Lifecycle

Writing Your First DEFINE File

The manifest.yml — environment targets and templating config

Running PLAN and DEPLOY

DCM Projects vs Terraform vs Schemachange

CI/CD with GitHub Actions

The Three New Capabilities to Know (July 2026 Preview)

PLAN DELTA — fast feedback during development

Inherited grants — close the grant drift problem permanently

ATTACH TAG — declarative tagging reconciled on every deploy

The Gotchas

FAQ

What 2extract actually is — and the one thing it isn’t

The proxy username is the actual control plane

What the residential product is actually sold for

How a request travels, and what happens the moment it doesn’t

Setting up an account — the part no library can do for you

Running the same account through MCP

Step 1 — balance, in plain language

Step 2 — geo lookup, the same targeting table without a docs tab

Step 3 — creating the proxy the lookup was for

Step 4 — a spend cap, without leaving the chat

Step 5 — checked against the dashboard, not instead of it

Step 6 — one request actually routed through the result

Three pipelines, three genuinely different datasets

Geo and address: proof of exit location

eCommerce and price monitoring

Market research

From collected rows to a spreadsheet someone will actually open

How the package is actually organized

Residential, mobile, or datacenter — the real tradeoffs

Where this actually breaks

Glossary

Questions worth asking before rolling this into production

Further reading

How dbt State Evaluates Every Model

What Actually Changes Per Run

Setting Up dbt State

Enable dbt State on a job

Add lag_tolerance to your models

The Four Actions: BUILD, CLONE, SKIP, DEFER