Category: Snowflake

Dive deep into the Snowflake Data Cloud. Guides on building a modern cloud data warehouse, data sharing, performance optimization, and leveraging advanced features like Snowpipe and Streams.

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

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

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

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

Identifying Hidden Token Costs in Snowflake Cortex AI

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

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

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

TL;DR

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

Why the Demo Lied to You

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

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

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

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

The Token Inflation You’re Not Accounting For

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

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

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

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

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

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

The Two-Currency Problem

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

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

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

The Cortex Search Idle Tax

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

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

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

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

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

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

Cortex Agents: The Cost Multiplier Nobody Drew on the Whiteboard

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

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

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

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

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

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

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

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

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

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

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

The Gotchas Nobody Warns You About

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

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

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

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

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

The One Principle

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

August 10, 2026

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

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

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

TL;DR

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

What Time Travel Actually Stores

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

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

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

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

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

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

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

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

Querying and Restoring Inside the Window

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

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

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

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

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

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

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

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

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

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

The Cost Math

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

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

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

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

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

The Gotchas Nobody Warns You About

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

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

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

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

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

The One Principle

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

August 8, 2026

The Medallion Architecture, Reconsidered: What It Solved and Where It Cracks
Almost every data team says it’s “doing medallion architecture.” Look under the hood and most of them aren’t — they have a Bronze layer that’s a dumping ground, a Silver layer that’s Bronze with nicer column names, and a Gold layer business users technically have access to but can’t actually use. That gap between the tidy Bronze → Silver → Gold diagram in Confluence and the thing that pages someone at 3 a.m. isn’t a sign the teams are sloppy. It’s a sign the pattern itself has load-bearing cracks that only show up at scale.

To be clear up front: medallion is not bad. It solved a genuine problem, and for a lot of teams it’s still the right default. But it’s now old enough, and deployed widely enough, that the failure modes are well documented — and a wave of 2025–2026 writing (including Adam Bellemare’s widely-shared “The End of the Bronze Age”) has moved from “here’s how to do medallion” to “here’s where medallion breaks and what comes next.” This is a practitioner’s tour of both halves: what the pattern actually solved, the specific places it cracks, and the shift-left / data-product thinking that’s emerging as the alternative — without pretending the alternative is free.

TL;DR
- → Medallion (Bronze/Silver/Gold) solved a real problem: it gave data-lake chaos a legible, staged structure with progressive quality guarantees and clear replay points.
- → Its core weakness is that it’s a multi-hop pull architecture — the consumer owns data access, and cleaning happens repeatedly downstream instead of once at the source.
- → Every hop re-reads, re-processes, and re-writes the same data, so you pay storage and compute for the same record two or three times over.
- → The Bronze layer is fragile: it’s tightly coupled to source schemas, so an upstream column rename can silently break everything downstream.
- → In practice, Silver often collapses into “Bronze with better names,” and Gold tables ship that no one can actually consume — the layers stop earning their keep.
- → The emerging alternative is shift-left: clean and contract the data once, near the source, as a reusable data product serving both analytical and operational consumers.
- → This isn’t a migration you rush. Medallion is still fine for many teams; shift-left trades pipeline cost for organizational and contract discipline you have to actually be able to sustain.
What medallion actually solved

Before piling on, give the pattern its due, because the reasons it won are the reasons it’s still everywhere. Data lakes started as swamps: raw files dumped into object storage with no structure, no quality guarantees, and no obvious place for any given transformation to live. Medallion imposed a legible order on that chaos. Bronze is the raw landing zone, a faithful mirror of the source. Silver is cleaned, deduplicated, conformed data organized around business entities. Gold is denormalized, read-optimized, application-aligned output. Three layers, quality rising left to right, each with a clear job.

That structure bought three real things. It gave teams a shared vocabulary — “is this a Silver table?” is a meaningful question. It created natural replay points — when something breaks, you can reprocess from Bronze rather than re-ingesting from the source. And it mapped cleanly onto the tooling, which is exactly why I’ve recommended a version of it for organizing transformation work in structuring dbt projects into staging, intermediate, and mart layers. None of that value evaporates because the pattern has limits. The point isn’t that medallion is wrong; it’s that its assumptions stop holding as scale and consumer count grow.

Where it cracks, crack #1: you pay for the same data three times

The most concrete flaw is cost, and it’s structural, not incidental. Medallion is a multi-hop architecture: to get from raw to usable, the same data is copied and reprocessed at each layer. Populating Bronze means reading and writing the data once. Producing Silver means reading Bronze, transforming, and writing again. Gold reads Silver and writes a third time. Each hop incurs its own storage, network, and compute bill — for what is, fundamentally, the same record getting progressively reshaped.

The multi-hop tax, animated: one logical record gets re-read, re-processed, and re-written at every medallion layer — you’re billed for storage and compute once per hop, not once per record.

On a small pipeline this is invisible. On a wide table with billions of rows and a short SLA, the triple-write becomes a line item someone in finance eventually circles in red. It compounds, too: an unsure consumer who can’t tell which layer to trust often just builds their own pipeline from the source, adding a fourth and fifth copy. The pattern that was supposed to reduce duplication quietly manufactures it. This is the same immutability-and-rewrite economics I dug into for how Snowflake stores data internally — every materialization is a real, billed rewrite, and medallion mandates three of them by design.

Crack #2: the Bronze layer is brittle by construction

Bronze is defined as a near-mirror of the source, which means it’s tightly coupled to the source’s schema — and tight coupling to something you don’t control is fragility by another name. When an upstream team renames a column, changes a type, or restructures a table, the Bronze ingestion and every transformation layered on top of it can break. The consumer, who owns the pull, absorbs all of that pain without any ownership or influence over the source model. It’s a reactive posture: you’re perpetually reacting to changes made by people who have no reason to warn you.

This is precisely the failure I walked through in how one renamed column kills a pipeline, and medallion structurally guarantees you’ll keep hitting it, because it puts the cleaning burden downstream of the schema you don’t own. The layers also have a way of quietly degrading: under deadline pressure, Silver becomes “Bronze with renamed columns and a dedupe,” and Gold becomes a table that technically exists but that no analyst can actually build a report from. When that happens, you’re paying the three-copy cost without getting the quality-progression benefit the copies were supposed to buy.

Crack #3: nothing gets reused for operational workloads

Medallion lives in the analytical world. The cleaning, standardizing, and modeling work all happens inside the analytics stack, processed by periodic batch jobs. That work is invisible and unusable to operational systems, which need low-latency access and can’t wait on a nightly batch. So operational teams build their own separate path to the same source data — duplicating the standardization logic, and widening the very operational-analytical divide the platform was supposed to bridge. You end up doing the same “what does a valid customer address look like” work twice, in two stacks, with two subtly different answers.

The emerging alternative: shift left

The through-line of every crack above is the same: cleaning happens repeatedly, downstream, owned by consumers who don’t control the source. Shift-left inverts that. Instead of each consumer pulling raw data and re-cleaning it, you do the cleaning and standardization once, as close to the source as possible, and publish the result as a reusable data product with an explicit contract.

The shift-left move: take the cleaning work you were doing in Bronze/Silver and do it once at the source as a contracted data product, reused by both analytical and operational consumers instead of re-copied down a chain.

Two ideas make this work. A data product is data published with the same care as any other product — owned, documented, discoverable, with a named owner who sits on the team that produces the source. A data contract is the formal agreement about that product’s schema, its evolution rules, and its SLAs, acting as a stable-but-evolvable API and a barrier between the producer’s internal model and everyone downstream. Cleaning once at the source kills the triple-copy cost, the contract kills the brittle-coupling problem (schema changes now go through an agreed evolution process instead of silently breaking you), and publishing the product in both streaming and table modes lets a single investment serve operational and analytical consumers at once. Open table formats like Apache Iceberg are a big part of why this is newly practical — you can materialize a table from a stream without making yet another copy, the same open-format shift I covered in the native-tables-to-Iceberg migration piece.

So should you rip out medallion? Almost certainly not yet

Here’s the honest counterweight, because the shift-left literature can read like a sales pitch. Shift-left doesn’t delete the work — it relocates it, and relocation has a cost the diagrams hide. Cleaning at the source means the source team now owns data-product responsibilities they may not want, staff for, or be organizationally incentivized to do. Data contracts require negotiation, governance, and social buy-in across teams that historically didn’t talk. For a legacy source you can’t modify, or an org where the producing team won’t cooperate, a full shift-left is simply not available, and you’ll end up doing the cleaning outside the source anyway — which looks a lot like Bronze with extra steps.

The realistic path is incremental: shift one high-value, high-pain dataset left, prove the contract model works socially and technically, and expand from there — while the rest of your medallion pipelines keep running. Medallion remains a perfectly good default for a single team with a manageable number of sources and consumers. The cracks matter most when you have many consumers, many sources, and a cost or trust problem that’s already biting. Match the architecture to that reality, not to whichever pattern is winning the current news cycle.

The gotchas nobody warns you about

Silver quietly becomes Bronze-with-better-names. If your Silver layer only renames columns and dedupes, you’re paying a full extra copy for cosmetic changes. Silver has to add real modeling and conformance or it isn’t earning its cost.

Consumer-owned pipelines multiply behind your back. When people can’t tell which layer to trust, they build their own path from the source. Every one of those is another copy and another maintenance burden you’ll inherit later.

Shift-left is an org change wearing an architecture costume. The hard part isn’t the streams or Iceberg tables — it’s convincing the source team to own a data product and honor a contract. If that social change isn’t real, the technical change won’t stick.

“We do medallion” is often aspirational. Audit what your layers actually contain before defending or replacing them. Many teams are debating a pattern they haven’t truly implemented.

Don’t confuse a data contract with a schema file. A contract includes evolution rules, ownership, and SLAs — who gets paged, and how the schema is allowed to change. A bare Avro or Parquet schema with none of that is documentation, not a contract.

The one principle

Medallion’s cracks all trace back to one root cause — it cleans data repeatedly, downstream, owned by whoever consumes it — and every serious alternative is really an argument about moving that work upstream to whoever produces it. Bronze/Silver/Gold isn’t a mistake to be ashamed of; it’s a pattern whose assumptions you should now hold consciously instead of by default. Know which crack is actually costing you — copies, brittleness, or duplicated operational work — and shift left exactly as far as your organization can sustain. The goal was never medallion, and it was never shift-left. It was relevant, trustworthy data at a cost you can defend.

Related reading: How one renamed column kills a pipeline · Structuring dbt projects into layers · Why every materialization is a real, billed rewrite · FDN vs open Iceberg tables · The End of the Bronze Age (InfoQ) · Apache Iceberg
August 2, 2026
How Snowflake Stores Data Internally: Micro-Partitions, FDN & Pruning
A team I worked with ran a nightly job that did something completely reasonable: it updated a single status flag on rows in a 2 TB orders table. One column. A few million rows a night. Harmless. Three months later their storage bill had nearly quadrupled, and no one had loaded any new data. The culprit wasn’t the update itself — it was a fact about Snowflake’s storage engine that almost nobody internalizes until it bites them: you cannot change a row in place. That one-column update was silently rewriting entire micro-partitions and stockpiling the old versions in Time Travel and Fail-safe, and they were paying to store every generation.

There’s a popular assumption that Snowflake just stores compressed Parquet files behind the scenes. It doesn’t. And the real design isn’t trivia — it’s the thing that explains why your queries prune well or scan everything, why a tiny update can be expensive, and why your storage bill has line items you never created directly. This is the storage layer, specifically — not the “cloud services” box everyone waves at in architecture diagrams. If you want the compute-and-query side, I’ve covered what really happens when you run a query separately; this article is about what’s actually sitting on disk, and why it dictates so much of your day.

TL;DR
- → Snowflake does not store Parquet — it stores its own proprietary columnar format (widely called FDN) in files called micro-partitions on cloud object storage.
- → Each micro-partition holds 50–500 MB of uncompressed data, stored column-by-column and compressed per column, with a metadata header of per-column min/max, distinct counts, and null counts.
- → That metadata — not the data — lives in FoundationDB and drives pruning: the optimizer skips whole micro-partitions by reading statistics, never touching the files.
- → Micro-partitions are immutable; every UPDATE, DELETE, or MERGE rewrites whole partitions rather than editing rows in place.
- → Immutability is why Time Travel, zero-copy cloning, and Fail-safe exist — and why churny small DML quietly inflates storage.
- → Clustering (natural by load order, or a defined key) controls how well pruning works; you can measure it with SYSTEM$CLUSTERING_INFORMATION.
- → You don’t tune Snowflake storage directly — you influence it by how you load, update, and cluster data.
The three layers, and why storage is the interesting one

Snowflake separates into three layers: cloud services (the brain — metadata, security, the optimizer), compute (virtual warehouses that run queries), and storage (the data itself). The famous selling point is that compute and storage are decoupled, which is why you can resize a warehouse without touching data and why the warehouse cache behaves the way it does. But the layer that quietly determines your costs and query speed is the bottom one — and it’s the one people understand the least.

What’s actually on disk: micro-partitions, not Parquet

When you load data into a table, Snowflake automatically slices it into micro-partitions. Per Snowflake’s own documentation, each one holds between 50 MB and 500 MB of uncompressed data (smaller on disk, since it’s always compressed), and the rows in it are stored in a columnar layout — each column laid out and compressed independently, with Snowflake picking the best compression scheme per column. A large table isn’t a handful of big partitions; it can be millions of these small, uniform files.

The file format is proprietary and closed — commonly referred to as FDN (“Flocon de Neige,” French for snowflake). It is emphatically not Parquet, even though both are columnar and compressed. Why build a custom format instead of reusing an open one? Because the format is co-designed with the metadata and pruning system, and that tight coupling is where Snowflake’s speed comes from. The anatomy diagram above is the mental model to hold: a header of statistics, then column chunks.

The metadata is the magic (and it lives in FoundationDB)

Here’s the part that reframes everything. For every micro-partition, Snowflake records metadata — the range of values (min and max) for each column, the number of distinct values, null counts, and more. That metadata is not stored in the file with the data; it lives in Snowflake’s metadata store, which Snowflake has publicly described as being built on FoundationDB, a distributed key-value store. The logical table you query is really a set of pointers, held in that metadata store, mapping to the physical FDN files in object storage.

This separation is what makes pruning possible. When you filter on a column, the optimizer consults the min/max metadata for each micro-partition and skips any whose range can’t possibly contain a match — without reading the file at all.

Pruning reads statistics, not data. Only the partition whose min/max range straddles July 4th is opened; the other four are eliminated before any I/O.

This is also why two SQL habits quietly kill performance. Wrapping a filter column in a function — WHERE DATE(order_ts) = '2026-07-04' — means the min/max stats on the raw order_ts column can’t be used, so pruning is defeated and every partition gets scanned. And SELECT * forces every column chunk to be read even though the columnar layout was designed to let Snowflake read only the columns you asked for. The half-open range order_ts >= '2026-07-04' AND order_ts < '2026-07-05' on the bare column, selecting only needed columns, is what lets both optimizations fire.

Immutability changes how you think about DML

Micro-partitions are immutable. Snowflake never edits a row in place — a truth that has bigger consequences than it first appears. When you run an UPDATE, DELETE, or MERGE, Snowflake reads the affected micro-partitions, produces new micro-partitions with the changes applied, and re-points the table’s metadata at the new files. The old files don’t vanish; they’re retained for Time Travel, then Fail-safe.

That’s the mechanism behind the quadrupled bill from the intro. A one-column update touching rows spread across thousands of micro-partitions rewrites all of those partitions in full — and keeps the previous versions around for the retention window. This same copy-on-write design is exactly why Time Travel isn’t really a backup feature — it’s just versioned pointers to immutable partitions you already paid to store — and why zero-copy clones are nearly free at creation: a clone is a new set of pointers to the same existing files, and it only costs storage as the two copies diverge and new partitions get written.

Seeing it for yourself

None of this has to be taken on faith. You can inspect the physical reality directly. To see how well a table is clustered — how much its micro-partitions overlap on a column, which is what determines pruning quality — use the built-in function:
```
SELECT SYSTEM$CLUSTERING_INFORMATION('sales', '(order_date)');
```
```
{
  "cluster_by_keys" : "LINEAR(order_date)",
  "total_partition_count" : 12483,
  "average_overlaps" : 3.11,
  "average_depth" : 2.94,
  "partition_depth_histogram" : {
    "00000" : 0,
    "00001" : 4821,
    "00002" : 5102,
    "00004" : 2560
  }
}
```
A low average_depth means a filter on order_date hits few overlapping partitions — good pruning. A high depth means the values are smeared across many partitions and queries scan more than they should. To see the storage consequences of immutability, query the account usage view that breaks storage into its real components:
```
SELECT
    table_name,
    active_bytes      / POW(1024, 3) AS active_gb,
    time_travel_bytes / POW(1024, 3) AS time_travel_gb,
    failsafe_bytes    / POW(1024, 3) AS failsafe_gb
FROM snowflake.account_usage.table_storage_metrics
WHERE table_name = 'ORDERS'
ORDER BY active_bytes DESC;
```
```
+------------+-----------+----------------+-------------+
| TABLE_NAME | ACTIVE_GB | TIME_TRAVEL_GB | FAILSAFE_GB |
+------------+-----------+----------------+-------------+
| ORDERS     |    198.4  |         912.7  |      301.5  |
+------------+-----------+----------------+-------------+
```
That’s the intro’s bug, made visible: 198 GB of live data, but ~1.2 TB of retained old versions you’re billed for, generated by churny updates rewriting partitions night after night.

Clustering: natural, defined, and not free

By default, data is naturally clustered by the order it was loaded — load July’s data in date order and date-filtered queries prune beautifully; load it shuffled and they don’t. For large tables with a consistent filter pattern, you can define a clustering key and Snowflake’s Automatic Clustering service will reorganize micro-partitions in the background to keep them well-sorted. It genuinely helps pruning, and it’s central to keeping interactive dashboards fast.

But reclustering rewrites micro-partitions (immutability again), which consumes credits and generates yet more retained versions. Clustering keys earn their keep only on large tables that are frequently filtered on the key. Adding one to a small or write-heavy table often costs more than it saves — this is the same “don’t pay to reprocess what didn’t change” discipline behind dbt state-based selection.

The cost math

Put rough numbers on the immutability tax. Say you have a 200 GB table and a job that rewrites 20% of its partitions daily — a modest MERGE. With a 7-day Time Travel window, you’re retaining roughly seven days of old versions of that churned 20%: on the order of 200 GB of Time Travel bytes on top of the 200 GB active, plus a further ~7 days of Fail-safe Snowflake keeps regardless. You can easily end up billed for 3–4x your live data size. The fix isn’t to stop updating — it’s to batch changes so you rewrite partitions once instead of trickling small updates, to right-size Time Travel retention per table, and to be deliberate about which tables actually need churn. Storage is cheap per GB, but “cheap × 4 × forever” is a real line on the invoice.

FDN vs open formats

The proprietary FDN format is what gives Snowflake its pruning speed and features — but it also means your data is locked inside Snowflake’s engine. That trade-off is exactly what the industry’s shift toward open table formats is reacting to: Iceberg tables let you keep data as open Parquet on your own object storage, readable by other engines, at some cost in Snowflake-native performance and features. If you’re weighing it, I’ve written on the native-to-Iceberg migration trap and when it’s actually worth migrating. The short version: FDN for Snowflake-first workloads, Iceberg when open access matters more than raw speed.

The gotchas nobody warns you about

Small, frequent updates are a storage trap. Single-row updates to a big table rewrite whole micro-partitions and pile up retained versions. Batch DML; don’t trickle it.

Functions on filter columns silently disable pruning. DATE(ts) = ..., UPPER(name) = ..., or a cast on the filtered column all prevent Snowflake from using the raw column’s min/max stats. Filter on the bare column with ranges.

Load order is a performance decision. Because natural clustering follows insertion order, loading data shuffled destroys pruning for range queries. Sort on load, or accept the cost of a clustering key.

DELETE doesn’t free storage immediately. Deleted rows live on as old partition versions through Time Travel and Fail-safe. If you need space back fast, that retention window matters.

Overlapping ranges beat pruning even with a clustering key. A high average_depth from SYSTEM$CLUSTERING_INFORMATION means your “clustered” table still scans widely. Measure it; don’t assume the key is working.

The one principle

Snowflake doesn’t store rows — it stores immutable, self-describing micro-partitions, and every cost and speed characteristic you experience is a downstream consequence of that one fact. Pruning is the metadata header doing its job. Expensive updates are immutability doing its job. Time Travel, cloning, and your storage bill are all the same design viewed from different angles. Once you see storage as immutable statistics-wrapped column chunks, Snowflake stops being magic and starts being predictable — which is exactly when you can make it cheap and fast.

Related reading: What really happens when you run a query · Why Time Travel isn’t a backup · Zero-copy clone storage costs · FDN vs Iceberg: the migration trap · Snowflake micro-partitions docs · FoundationDB powers Snowflake metadata
July 31, 2026
Building a Bulletproof ETL Audit Logger: Capturing Airflow Execution Context in Snowflake
The 2 a.m. page said the pipeline “succeeded.” The dashboard was green. And the finance team was still staring at yesterday’s numbers, because one task in a forty-task DAG had quietly processed the wrong micro-batch window and nobody could prove when, or why, without SSH-ing into a worker and grepping logs by hand. That’s the gap between “DAG success/failure notifications” and actual observability: a green checkmark tells you the code didn’t throw, not that the right data moved in the right window at the right time.

The fix isn’t a fancier alerting tool. It’s an audit table — a row written to Snowflake at the start and end of every single task, carrying the execution context Airflow already knows: which logical date this run is for, which try number, when the task actually started and finished, how long it took, and what it touched. Once that table exists, “when did this break and why is it slow” stops being an archaeology project and becomes a SELECT. This is the complete build: the Snowflake schema, the Airflow callback code that captures context at both ends of every task, and what the whole thing looks like when it runs.

TL;DR

→ DAG-level success/failure is too coarse. Capture context at task start and task end for granular observability — timing, retries, and the exact micro-batch window per task.

→ Airflow exposes the execution context through callbacks: on_execute_callback fires right before a task runs (your “start” hook), and on_success_callback / on_failure_callback fire at the end. Each receives the full context dictionary.

→ The context carries what you need: logical_date (the micro-batch window), dag_run.run_id, ti.try_number, ti.start_date, plus ds/ds_nodash for partition keys. In Airflow 3, access it programmatically with get_current_context() from the Task SDK.

→ Attach the callbacks once via default_args and every task in the DAG is audited automatically — no per-task boilerplate.

→ Ship rows to a centralized Snowflake PIPELINE_AUDIT_LOG table keyed by dag_id + task_id + run_id + try_number, with a START row and an END row per attempt so duration and status fall out of a simple query.

→ Once the data lands, debugging execution delays is a SELECT … ORDER BY duration_seconds DESC, and finding the slowest task in the slowest run is a window function, not a log grep.

Why DAG-level notifications aren’t observability

A DAG success signal answers one coarse question. The unit of observability you actually want is the task attempt.

A DAG success notification answers one question: did the whole thing finish without an unhandled exception? That’s necessary and nowhere near sufficient. It can’t tell you which task in the chain was slow, whether a task silently ran on its second retry, which logical date window each task actually processed, or how today’s run compares to last week’s for the same task. Those are the questions you actually have during an incident, and log-grepping to answer them is how a five-minute diagnosis becomes a two-hour one.

The unit of observability you want is the task attempt, not the DAG run. Every task attempt has a start, an end, a try number, and a logical date. If you record those four things for every attempt in one queryable place, you can answer “when did this get slow,” “which task is the bottleneck,” and “did this run process the window it should have” directly — and you can do it after the fact, without the worker still being alive.

Step 1: the Snowflake audit table

Start with the destination. The schema is deliberately simple — one row per task-attempt per phase (START and END), keyed so you can pair them up and compute duration. Keeping START and END as separate rows (rather than updating one row) means a task that dies hard still leaves its START row behind, which is itself a signal.
```
CREATE TABLE IF NOT EXISTS ops.pipeline_audit_log (
    audit_id        STRING DEFAULT UUID_STRING(),
    dag_id          STRING       NOT NULL,
    task_id         STRING       NOT NULL,
    run_id          STRING       NOT NULL,
    try_number      NUMBER       NOT NULL,
    phase           STRING       NOT NULL,   -- 'START' | 'END'
    status          STRING,                  -- 'RUNNING' | 'SUCCESS' | 'FAILED'
    logical_date    TIMESTAMP_NTZ,           -- the micro-batch window
    event_time      TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP(),
    duration_sec    NUMBER,                  -- populated on END
    operator        STRING,
    map_index       NUMBER,                  -- for dynamically mapped tasks
    hostname        STRING,
    error_message   STRING,
    loaded_at       TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP()
);
```
A few deliberate choices. logical_date is stored as its own column because it’s the micro-batch window the task is for — distinct from event_time, the wall-clock moment the row was written. Conflating those two is the single most common audit-table mistake, and it’s exactly the confusion that hid the “wrong window” bug in the opening story. try_number is in the key because retries are first-class events you want to see, not noise to collapse. And map_index is there so dynamically mapped tasks (the .expand() fan-out) each get their own audit trail instead of blurring together.

Step 2: extracting the execution context

Airflow hands you everything through the context dictionary. The pieces that matter for auditing:
```
def extract_audit_fields(context: dict) -> dict:
    """Pull the audit-relevant fields out of the Airflow context."""
    ti = context["ti"]                     # the TaskInstance
    dag_run = context["dag_run"]

    return {
        "dag_id":       ti.dag_id,
        "task_id":      ti.task_id,
        "run_id":       dag_run.run_id,
        "try_number":   ti.try_number,
        # logical_date is the micro-batch window this run is FOR.
        # Asset-triggered DAGs in Airflow 3 have none — fall back to None.
        "logical_date": context.get("logical_date"),
        "operator":     ti.operator,
        "map_index":    ti.map_index,
        "hostname":     ti.hostname,
        "start_date":   ti.start_date,
    }
```
The distinction that trips people up: logical_date (formerly execution_date) is the window the run represents, which may be hours or months before the wall clock if you’re backfilling. ti.start_date is when the task actually began executing. You want both — one to know what the task processed, the other to know when and how long. In Airflow 3, if you’re inside task code rather than a callback, you get the same dictionary with from airflow.sdk import get_current_context and context = get_current_context().

Step 3: the callbacks that fire at start and end

This is the heart of it. on_execute_callback runs immediately before the task’s own code — that’s your START row. on_success_callback and on_failure_callback run after — those are your END rows, one carrying SUCCESS, the other FAILED plus the exception.
```
from datetime import datetime, timezone

def _write_audit_row(fields: dict) -> None:
    """Insert a single audit row into Snowflake via a reusable hook."""
    from airflow.providers.snowflake.hooks.snowflake import SnowflakeHook
    hook = SnowflakeHook(snowflake_conn_id="snowflake_ops")
    hook.run(
        """
        INSERT INTO ops.pipeline_audit_log
            (dag_id, task_id, run_id, try_number, phase, status,
             logical_date, duration_sec, operator, map_index,
             hostname, error_message)
        VALUES
            (%(dag_id)s, %(task_id)s, %(run_id)s, %(try_number)s,
             %(phase)s, %(status)s, %(logical_date)s, %(duration_sec)s,
             %(operator)s, %(map_index)s, %(hostname)s, %(error_message)s)
        """,
        parameters=fields,
    )

def audit_on_start(context: dict) -> None:
    f = extract_audit_fields(context)
    f.update(phase="START", status="RUNNING",
             duration_sec=None, error_message=None)
    _write_audit_row(f)

def audit_on_success(context: dict) -> None:
    f = extract_audit_fields(context)
    duration = (datetime.now(timezone.utc) - f["start_date"]).total_seconds()
    f.update(phase="END", status="SUCCESS",
             duration_sec=round(duration, 2), error_message=None)
    _write_audit_row(f)

def audit_on_failure(context: dict) -> None:
    f = extract_audit_fields(context)
    duration = (datetime.now(timezone.utc) - f["start_date"]).total_seconds()
    f.update(phase="END", status="FAILED",
             duration_sec=round(duration, 2),
             error_message=str(context.get("exception"))[:2000])
    _write_audit_row(f)
```
Two production notes. First, keep the callback body cheap and defensive — a callback that raises can interfere with task handling, so in a hardened version you wrap _write_audit_row in a try/except that logs and swallows, because a failed audit write should never fail the pipeline. Second, opening a fresh Snowflake connection per callback is fine at low task volume; at high volume you’d batch these through a staging mechanism rather than one INSERT per event, which the “gotchas” section revisits.

Step 4: wire it into every task with one line

The elegance is that you attach these once through default_args, and every task in the DAG inherits them — no per-task decoration, no touching your existing operators.
```
from airflow import DAG
from airflow.operators.python import PythonOperator
import pendulum

default_args = {
    "on_execute_callback": audit_on_start,
    "on_success_callback": audit_on_success,
    "on_failure_callback": audit_on_failure,
    "retries": 2,
}

with DAG(
    dag_id="sales_etl",
    schedule="@hourly",
    start_date=pendulum.datetime(2026, 1, 1, tz="UTC"),
    catchup=False,
    default_args=default_args,   # <- every task is now audited
) as dag:

    extract = PythonOperator(task_id="extract_orders",
                             python_callable=run_extract)
    transform = PythonOperator(task_id="transform_orders",
                               python_callable=run_transform)
    load = PythonOperator(task_id="load_to_warehouse",
                          python_callable=run_load)

    extract >> transform >> load
```
That’s the whole integration. Three callbacks defined once, referenced in default_args, and every task — extract, transform, load, and any you add later — writes a START and an END row automatically.

What it looks like when it runs

When the DAG executes, each task emits two rows. Here’s the Airflow task log showing the callbacks firing, followed by the rows that land in Snowflake:
```
[2026-07-18T02:00:03Z] INFO - Executing on_execute_callback: audit_on_start
[2026-07-18T02:00:03Z] INFO - Audit START written: sales_etl.extract_orders try=1
[2026-07-18T02:00:41Z] INFO - Marking task as SUCCESS. dag_id=sales_etl, task_id=extract_orders
[2026-07-18T02:00:41Z] INFO - Executing on_success_callback: audit_on_success
[2026-07-18T02:00:41Z] INFO - Audit END written: sales_etl.extract_orders try=1 duration=38.4s
```
And the resulting rows in ops.pipeline_audit_log:

The rows that land in Snowflake. The 112-second transform and the correct 02:00 window are visible at a glance — neither was in the green checkmark.
```
DAG_ID     TASK_ID          RUN_ID              TRY  PHASE  STATUS   LOGICAL_DATE         DURATION_SEC
---------  ---------------  ------------------  ---  -----  -------  -------------------  ------------
sales_etl  extract_orders   manual__2026-07-18   1   START  RUNNING  2026-07-18 02:00:00        (null)
sales_etl  extract_orders   manual__2026-07-18   1   END    SUCCESS  2026-07-18 02:00:00        38.40
sales_etl  transform_orders manual__2026-07-18   1   START  RUNNING  2026-07-18 02:00:00        (null)
sales_etl  transform_orders manual__2026-07-18   1   END    SUCCESS  2026-07-18 02:00:00       112.65
sales_etl  load_to_warehouse manual__2026-07-18  1   START  RUNNING  2026-07-18 02:00:00        (null)
sales_etl  load_to_warehouse manual__2026-07-18  1   END    SUCCESS  2026-07-18 02:00:00        54.10
```
Immediately you can see what a green checkmark never showed you: transform_orders took 112 seconds — nearly three times extract — and every task processed the 02:00 logical window as intended. That’s the observability the DAG notification couldn’t give you, and it’s now sitting in a table.

Step 5: the queries that pay it back

The point of the table is what you can ask it. Duration per task-attempt, pairing START and END:
```
SELECT dag_id, task_id, run_id, try_number,
       MAX(duration_sec) AS duration_sec,
       MAX(CASE WHEN phase = 'END' THEN status END) AS final_status
FROM ops.pipeline_audit_log
GROUP BY dag_id, task_id, run_id, try_number
ORDER BY duration_sec DESC NULLS LAST;
The slowest task in each run — the bottleneck finder — with a window function:

SELECT dag_id, run_id, task_id, duration_sec
FROM (
    SELECT dag_id, run_id, task_id, duration_sec,
           ROW_NUMBER() OVER (PARTITION BY dag_id, run_id
                              ORDER BY duration_sec DESC) AS rn
    FROM ops.pipeline_audit_log
    WHERE phase = 'END'
)
WHERE rn = 1
ORDER BY duration_sec DESC;
```
And the one that catches silent regressions — a task getting slower over time, comparing each run to that task’s trailing average:
```
SELECT dag_id, task_id, run_id, logical_date, duration_sec,
       AVG(duration_sec) OVER (
           PARTITION BY dag_id, task_id
           ORDER BY logical_date
           ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING
       ) AS trailing_avg
FROM ops.pipeline_audit_log
WHERE phase = 'END' AND status = 'SUCCESS'
QUALIFY duration_sec > trailing_avg * 1.5   -- 50% slower than usual
ORDER BY logical_date DESC;
```
That last query is the one that turns the audit log from a forensic tool into an early-warning system: it surfaces the task that’s creeping slower before it becomes the 2 a.m. page.

The gotchas nobody warns you about

A raising callback can disrupt task handling. If audit_on_failure itself throws (say Snowflake is briefly unreachable), you can turn one problem into two. Wrap the write in try/except, log the failure, and swallow it — the audit system must never be able to fail the pipeline it’s observing.

One INSERT per callback will not scale. At a few hundred task-attempts a day it’s fine. At tens of thousands, opening a Snowflake connection per event is both slow and expensive (every connection burns warehouse time). The scalable pattern is to write audit events to a lightweight buffer — a local file, a queue, or Snowpipe/streaming ingestion — and land them in batches, so your observability layer isn’t itself a warehouse cost problem.

try_number semantics shifted across Airflow versions. Historically ti.try_number read differently inside a running task versus after completion, which has burned people building retry logic on it. Pin your understanding to your Airflow version and verify what value you actually get in each callback rather than assuming — a quick log line during rollout saves confusion later.

Asset-triggered DAGs have no logical_date. In Airflow 3, DAGs triggered by asset events don’t get a logical date or the derived ds/ds_nodash variables. Your extract_audit_fields must tolerate None there and lean on dag_run.run_id for identity, or the callback will KeyError on exactly the DAGs you were proud of modernizing.

Wall-clock duration isn’t queue time. The duration computed from ti.start_date is execution time, not the time the task spent waiting in the scheduler queue. If you’re debugging delays specifically, capture the gap between the DAG run’s start and the task’s start too — a task that’s “fast” but starts late points at scheduler or pool contention, a completely different fix than optimizing the task itself.

The one principle

Observability is a table, not a notification. Record every task attempt’s start and end with the execution context Airflow already hands you — logical date, try number, timings — and ship it to one Snowflake table. Then “when did this break, which task is slow, and did it process the right window” become queries instead of log archaeology. A green checkmark tells you nothing failed loudly. An audit row tells you what actually happened — and that’s the difference between hoping your pipeline is healthy and knowing it.

Related reading: Airflow templates & context reference (official docs) · Accessing the Airflow context (Astronomer) · Orchestrating dbt With Airflow on Snowflake · Dynamic Airflow DAGs via Snowflake Metadata · Debugging Zero-Copy Clone Storage Costs in CI/CD
July 18, 2026

Category: Snowflake

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

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

DDM vs Row Access Policies: The Conceptual Split

Setting Up Dynamic Data Masking

Creating and applying a basic masking policy

Testing with POLICY_CONTEXT before applying

Tag-Based Masking: The Scalability Pattern

IS_AGENT_ACTIVATED: Governing AI Agent Access

Row Access Policies: Controlling Visible Rows

The Gotchas

FAQ

The Three Views and What Each One Covers

Basic Usage Monitoring Queries

Daily credit burn by function and model

Monthly spend by user

Cortex Agent attribution

Automation Pattern 1: Account-Level Monthly Spend Alert

Automation Pattern 2: Per-User Monthly Spending Limits

Automation Pattern 3: Runaway Query Detection and Cancellation

The Gotchas

FAQ

What the MCP Server Actually Is

The Five Tool Types You Can Expose

CORTEX_AGENT_RUN — the recommended default

CORTEX_ANALYST_MESSAGE — natural language to SQL

CORTEX_SEARCH_SERVICE_QUERY — vector search over docs

SYSTEM_EXECUTE_SQL — raw SQL execution

GENERIC — UDFs and stored procedures

Setup: From Zero to Working Connection

Step 1 — Create the OAuth security integration

Step 2 — Create the MCP server object

Step 3 — Grant privileges

Step 4 — Connect the client

The Gotchas Nobody Warns You About

What the MCP Server Doesn’t Do (Yet)

Why the Demo Lied to You

The Token Inflation You’re Not Accounting For

The Two-Currency Problem

The Cortex Search Idle Tax

Cortex Agents: The Cost Multiplier Nobody Drew on the Whiteboard

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

The Gotchas Nobody Warns You About

TL;DR

What Time Travel Actually Stores

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

Querying and Restoring Inside the Window

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

The Cost Math

The Gotchas Nobody Warns You About

The One Principle

TL;DR

What medallion actually solved