app.dataengineerhub.blog

Tag: snowflake

  • The 2026 Migration Trap: Moving from Native Tables to Dynamic Apache Iceberg v3 in Snowflake

    The 2026 Migration Trap: Moving from Native Tables to Dynamic Apache Iceberg v3 in Snowflake

    The pitch is intoxicating and mostly true: keep your data in open Apache Iceberg format on your own object storage, let external engines read it, and let Snowflake’s Dynamic Tables handle the low-latency transformations on top — one declarative pipeline, no lock-in, a real lakehouse. In 2026, with Iceberg v3 generally available on Snowflake since May 7, teams are migrating native tables to dynamic Iceberg tables expecting exactly that. Most of them hit the same wall in the same order.

    The wall is this: “open and interoperable” describes the storage format, not the write path, and “low latency” describes Dynamic Tables under conditions that partitioned Iceberg writes and cross-engine change tracking quietly violate. The migration doesn’t fail loudly. It succeeds, ships, and then your incremental pipeline starts doing full refreshes you didn’t ask for, your partitioned writes fan out into a metadata problem, and the external engine you promised could write to these tables turns out to be read-only. This is the guide to the traps — the ones that don’t show up in the quickstart — and how to design around them before they cost you a quarter.

    TL;DR

    → Iceberg v3 (GA on Snowflake May 2026) brings deletion vectors, row lineage (native CDC), VARIANT, and multi-argument partition transforms. You cannot upgrade v2 to v3 in place — you recreate the table. Plan the migration, don’t expect an ALTER.

    → Dynamic Iceberg tables support PARTITION_BY, TARGET_FILE_SIZE, and PATH_LAYOUT. PATH_LAYOUT = HIERARCHICAL only produces Hive-style partitioned paths when paired with PARTITION_BY — and over-partitioning (more than a few thousand partitions) turns your metadata layer into the bottleneck.

    → The cross-engine reality: Snowflake-managed Iceberg tables are read-write for Snowflake, read-only for external engines. Writes from external engines to Snowflake-managed v3 tables via Horizon Catalog aren’t supported yet. External engines can only write to externally-managed tables.

    → Dynamic Tables track changes at the row level for native tables but the file level for externally-managed Iceberg base tables. Frequent copy-on-write on the external table degrades incremental refresh — a file changes, the whole file is “changed.”

    → INSERT OVERWRITE on a base table resets change-tracking metadata and forces a full refresh. Row lineage (v3) and primary keys with RELY are how you keep incrementality alive across rewrites.

    → Deletion vectors (v3 merge-on-read) are governed by heuristics: Snowflake only writes a deletion vector if fewer than ~5% of a file’s rows are deleted and the file is larger than ~1.6 MB. External engines that don’t understand v3 deletion vectors force you to set ICEBERG_MERGE_ON_READ_BEHAVIOR = 'DISABLED' (copy-on-write) for compatibility.

    What v3 actually changed, and why in-place upgrade isn’t a thing

    The target architecture: a Bronze/Silver/Gold lakehouse where Dynamic Iceberg tables handle incremental transforms and write open Iceberg that external engines can read. The traps live in the arrows, not the boxes.

    Iceberg v3 is a genuine step change, not a point release. It adds deletion vectors (up to ~10x faster DML by avoiding positional-delete merges at read time), row lineage for native change data capture, a VARIANT type for semi-structured data with structured-query performance, default column values, geometry/geography types, nanosecond timestamps, and multi-argument partition transforms. On Snowflake it went to preview in March 2026 and GA on May 7, 2026.

    Here’s the first thing that trips migrations: you can’t upgrade an Iceberg table from v2 to v3. There is no ALTER TABLE ... SET ICEBERG_VERSION = 3 that rewrites your existing table in place. You configure the default Iceberg version and create new v3 tables, migrating data into them. This matters because teams plan the migration as a flag flip and discover it’s a recreate-and-backfill — which, for a large partitioned table, is a real project with a real compute bill, not a maintenance-window toggle. The related gotcha: v2 tables using copy-on-write represent an updated or relocated row in a standard stream as a DELETE followed by an INSERT for the same row, so any CDC logic you built on v2 stream semantics needs re-validation against v3’s row lineage before you cut over.

    The partitioned-write trap: HIERARCHICAL paths and the metadata ceiling

    Dynamic Iceberg tables expose three storage-shaping properties: PARTITION_BY, TARGET_FILE_SIZE, and PATH_LAYOUT. The one that surprises people is PATH_LAYOUT. It defaults to FLAT, meaning all Parquet data files land directly under the data/ directory. Set it to HIERARCHICAL and Snowflake writes Hive-style partitioned paths — but only in combination with PARTITION_BY. Setting HIERARCHICAL without a partition spec does nothing useful; the two are a pair.

    A minimal partitioned dynamic Iceberg table looks like this:

    CREATE DYNAMIC ICEBERG TABLE my_dt (
  product_id NUMBER, product_name STRING, order_time TIMESTAMP_NTZ
)
  TARGET_LAG = '20 minutes'
  WAREHOUSE = my_wh
  EXTERNAL_VOLUME = 'my_vol'
  CATALOG = 'SNOWFLAKE'
  BASE_LOCATION = 'my_dt'
  PARTITION BY (YEAR(order_time))
  PATH_LAYOUT = HIERARCHICAL
  AS SELECT product_id, product_name, order_time FROM staging;

    The trap isn’t the syntax; it’s the partition cardinality. Iceberg’s metadata tracks files per partition, and every partition you create adds manifest overhead. Snowflake’s own guidance is blunt: avoid creating more than a few thousand partitions, and test query performance against your actual workload before finalizing a partitioning strategy. The failure mode when you ignore this is quietly brutal — partition by DAY(event_time) on a table with a few years of history and a high-cardinality secondary key, and you can generate tens of thousands of tiny partitions, each with its own small files. Now your Dynamic Table refresh spends its time in metadata planning rather than moving data, and your “low-latency” pipeline has a latency floor set by manifest bookkeeping.

    The design rule that keeps you out of trouble: partition on the coarsest grain that still prunes your dominant query pattern (usually a month or a broad category), let TARGET_FILE_SIZE and Snowflake’s file management handle within-partition layout, and reach for clustering rather than finer partitions when you need more selective pruning. Hierarchical paths are for interoperability and human-navigable storage, not a license to over-partition.

    The cross-engine write trap: “interoperable” is asymmetric

    This is the one that derails architecture diagrams. The interoperability story — external engines like Spark and Trino reading your Iceberg data — is real, but it runs in one direction for Snowflake-managed tables. Snowflake-managed Iceberg tables are read-write for Snowflake and read-only for external engines. As of the v3 GA, reading Snowflake-managed v3 tables from an external engine via the Horizon Iceberg REST Catalog API is generally available; writing from external engines to Snowflake-managed v3 tables through Horizon is explicitly not supported yet.

    If your architecture needs an external engine to write Iceberg that Snowflake then transforms, you must use externally-managed tables — data written by Spark into a catalog like AWS Glue, which Snowflake reads via a catalog integration and a linked database. That’s a supported and powerful pattern (it’s the canonical Bronze layer of an open lakehouse), but it’s a different architecture with different semantics than “Snowflake-managed tables that everyone can write to,” which does not exist today. Decide early which engine owns writes for each table, because that choice dictates managed-vs-external, and switching later means a migration. A further sharp edge: you can’t write with vended credentials to cloned or converted tables, and you can’t write at all to a table that was converted from externally-managed to Snowflake-managed — conversions are one-way for write access.

    The change-tracking trap: file-level vs row-level

    The granularity of change tracking decides how much work an incremental refresh does. Row-level (native) processes a tight delta; file-level (external Iceberg) can reprocess an entire file because one row moved.

    Dynamic Tables get their speed from incremental refresh — processing only what changed since the last refresh. The catch that native-table migrators don’t see coming: Dynamic Tables track changes at the file level for externally-managed Iceberg base tables, whereas they track at the row level for native Snowflake tables. That single difference reshapes your performance profile.

    With a native base table, if one row in a micro-partition changes, Snowflake knows it was that row, and the incremental refresh processes a tight delta. With an externally-managed Iceberg base table, change tracking is file-granular: a copy-on-write update that rewrites a data file marks the entire file as changed, so the refresh reprocesses every row in it, even if one row moved. On a table with frequent small updates and copy-on-write behavior, this inflates the change set dramatically and can make an “incremental” refresh behave like it’s doing far more work than the actual data change justifies. Snowflake’s documentation states it plainly: frequent copy-on-write operations on externally-managed Iceberg tables may impact incremental-refresh performance.

    Then there’s the metadata reset. INSERT OVERWRITE on a base table — a common pattern for batch reloads — resets change-tracking metadata, and the next Dynamic Table refresh falls back to a full recomputation. If your ingestion rewrites tables wholesale, your downstream “incremental” pipeline isn’t incremental at all.

    How v3 features rescue the change-tracking story

    The good news is that v3 exists partly to solve this, and using its features deliberately is the difference between a fast lakehouse and a slow one.

    Row lineage is the headline. In v3, tables track _row_id (a stable unique identifier assigned to each row) and _last_updated_sequence_number (the commit that last touched the row). This lets any compliant engine reliably match the same row across snapshots and detect row-level changes — native CDC in the format itself, not bolted on. Row lineage is supported for both Snowflake-managed and externally-managed v3 tables and underpins append-only and standard streams on Snowflake-managed v3 tables.

    Primary keys with RELY are the pragmatic rescue for the INSERT OVERWRITE problem. If you declare a reliable primary key on the base table, Snowflake compares rows by key value instead of leaning on change-tracking columns — so even when a table is fully rewritten, it computes the minimal set of actual changes rather than reprocessing everything. This is also how you enable incremental refresh downstream of a full-refresh dynamic table, by giving Snowflake a stable identity to diff against. For append-only CDC, the QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) = 1 pattern gives you latest-row-per-key with a derived unique key, handling out-of-order arrival without extra logic.

    Deletion vectors replace v2’s positional deletes for merge-on-read, and they’re governed by heuristics worth knowing: Snowflake writes a deletion vector only if fewer than ~5% of a data file’s rows are deleted and the file exceeds ~1.6 MB; otherwise it rewrites the file (copy-on-write). You control the behavior with ICEBERG_MERGE_ON_READ_BEHAVIOR. The compatibility trap: if an external engine in your stack doesn’t yet understand v3 deletion vectors, you must set that parameter to 'DISABLED' to force copy-on-write, or the external engine will misread the table. Interoperability constrains you to the capabilities of the least capable engine that touches the table.

    The gotchas nobody warns you about

    Change tracking must be on, with non-zero Time Travel, on every underlying object. Incremental refresh silently depends on it. Snowflake will try to enable it automatically for incremental dynamic tables, but if you recreate a base object you must re-enable it — and a base object with Time Travel set to zero quietly breaks incrementality.

    The GRANT syntax has a trap for dynamic Iceberg tables. To grant access to future dynamic Iceberg tables in a schema, you use GRANT … ON FUTURE ICEBERG TABLES without the DYNAMIC keyword. The intuitive ON FUTURE DYNAMIC ICEBERG TABLES does not cover them, so a reasonable-looking grant leaves new tables inaccessible.

    Gen2 warehouses matter more than you’d expect. Snowflake’s Dynamic Table performance work — measured up to ~2.8x faster refresh over the past year — is specifically tied to Gen2 warehouses for patterns like top-level aggregates, QUALIFY row/rank = 1, cluster-by, and joins. If your incremental pipeline is on Gen1, you’re leaving a large multiple of refresh speed on the table before any Iceberg tuning.

    Cross-region and cross-cloud tables bill for transfer. A Snowflake-managed Iceberg table whose external volume sits in a different region or cloud than your account incurs cross-region data-transfer charges under the DATA_LAKE transfer type. Keep external volumes in the same region as your account unless you have a deliberate DR reason not to.

    A migration order that avoids the traps

    Sequence matters. First, decide per table who owns writes — if an external engine writes, it’s externally-managed; if only Snowflake writes, Snowflake-managed — because that’s the irreversible-ish decision. Second, set your default Iceberg version to v3 and plan recreate-and-backfill for existing v2 tables rather than expecting an upgrade. Third, choose a coarse partition grain (validated against real query patterns, staying well under a few thousand partitions) and use clustering for finer pruning. Fourth, make change tracking deliberate: declare reliable primary keys where base tables get rewritten, lean on row lineage for CDC, and confirm change tracking plus non-zero Time Travel on every base object. Fifth, pin ICEBERG_MERGE_ON_READ_BEHAVIOR to match the least-capable engine that reads the table. Then move workloads to Gen2 warehouses and measure incremental-refresh times against your latency target before you call it done.

    The one principle

    “Open Iceberg lakehouse with low-latency Dynamic Tables” is true only when the write path, the partition cardinality, and the change-tracking granularity all line up — and by default they don’t. Migrating native tables to dynamic Iceberg v3 is a design exercise, not a format swap: decide who writes, partition coarsely, give Snowflake a stable row identity to diff against, and constrain merge-on-read to your least-capable engine. Get those four right and the lakehouse is genuinely fast and open. Get them wrong and you’ve built a slow data lake with extra steps, one full refresh at a time.

    Related reading: Create dynamic Apache Iceberg tables (official docs) · Manage Iceberg tables: row lineage & deletion vectors · Snowflake Iceberg v3: When to Migrate · dbt State on Snowflake: Skip Unchanged Models · Dynamic Airflow DAGs via Snowflake Metadata

  • Governing the AI Agent: Securing Snowflake CoCo and MCP Workflows in Production

    Governing the AI Agent: Securing Snowflake CoCo and MCP Workflows in Production

    In March 2026, two days after Snowflake shipped Cortex Code, security researchers at PromptArmor published something that should have changed how every data team thinks about AI agents. They didn’t break Snowflake’s authentication. They didn’t steal a password. They fed the agent a piece of poisoned content — an indirect prompt injection — and the agent, reasoning helpfully as designed, used its own cached Snowflake credentials to exfiltrate data and drop tables. The attacker never logged in. The agent did the damage, under its own legitimate identity, because someone told it to and nothing stopped it.

    That’s the whole problem with the agentic enterprise in one incident. We spent a decade getting good at controlling what people can do in Snowflake — roles, grants, masking, row access policies. Then we handed an autonomous agent the keys and discovered our governance model couldn’t tell the difference between a human running a query and an agent running the same query on someone’s behalf. CoCo isn’t just writing SQL anymore; it’s orchestrating pipelines, calling external tools over MCP, and taking actions across your stack. The question is no longer “can it do useful work” — it obviously can — but “what stops it from doing damage, and who approves the actions that matter.”

    This is the practitioner’s guide to governing agentic workflows in production: the identity model, data-movement controls, and multi-party approvals that let you use CoCo and MCP without turning every agent into an unaudited superuser.

    TL;DR

    → An agent runs with the privileges of the role that invoked it. If that role has broad SELECT across production, the agent has the same reach — and it evaluates whether a query is answerable, not whether it’s appropriate. Over-scoped roles are the root risk.

    → The injection surface is wide (READMEs, web content, table data, MCP tool responses) and can’t be eliminated. Governance shifts from “prevent the injection” to “limit the blast radius when one lands.” Scope first, then monitor.

    → AI Agent Identity (GA at Summit 2026) gives each agent a cryptographic identity, per-agent RBAC, and a full audit trail — so policies can treat agent traffic differently from human traffic and you can actually attribute actions.

    → Data Movement Policies restrict where data can flow and which channels agents can use — the control that stops an over-scoped agent from piping regulated data out through an MCP integration.

    → Multi-party approval (private preview) puts a human (or two) in the loop for destructive or high-sensitivity actions — the agentic equivalent of a code review before a DROP.

    → The MCP Gateway (built on Snowflake’s Natoma acquisition) centralizes and governs every MCP connection — identity-aware authorization and audit at the tool-call level, instead of each agent wiring its own servers ungoverned.

    → Start today: enable the free built-in prompt-injection guardrails, audit which agents touch sensitive data, write an explicit agent policy (what each agent may and may not do), and apply data-movement policies to your most sensitive tables.

    The core problem: an agent inherits your blast radius

    Everything else follows from one fact, so internalize it first. A Cortex Agent — and CoCo is one — runs under the privileges of the Snowflake user or role that invoked it. It has exactly the access that role has. Not less, because it isn’t sandboxed away from the role’s grants by default; not more, because Snowflake’s perimeter still applies. Whatever the invoking role can SELECT, the agent can SELECT.

    In a world of humans, over-scoped roles are a latent risk — a person could query the HR schema they never actually touch, but they don’t, because they know not to. An agent has no such judgment. It does not weigh whether querying the compensation table is appropriate to the task; it weighs whether the query is answerable given the permissions available. Ask an agent configured for product analytics a question that happens to be answerable using financial data its role can reach, and it will answer. There is no internal boundary that says “that’s not my department.”

    So the blast radius of every over-scoped role expands dramatically the moment that role underpins an always-on agent. The single most important governance move you can make is not a new feature — it’s scoping the agent’s role down to exactly the data its job requires, and no more. Every control below is a layer on top of that foundation. If the foundation is a role with broad production access, no amount of monitoring saves you.

    Same agent, same injection — the only difference is how tightly the underlying role is scoped. Least privilege is what makes a successful injection cheap.

    Why you defend the blast radius, not the perimeter

    The PromptArmor attack teaches the strategy. The injection didn’t target authentication; it targeted the agent’s reasoning and then rode its existing credentials. The injection surface — anything the agent reads and treats as context — is enormous: source files and READMEs, web pages it fetches, rows in tables it queries, and crucially the responses that come back from MCP tools. You cannot realistically eliminate that surface. A determined attacker will eventually land an injection.

    That reframes the whole job. If you can’t stop every injection, you make a successful one cheap. Controls that limit what the agent can access limit what an attacker can do through it. Scope first, then monitor. Every governance control that follows exists to shrink the blast radius of an injection that gets through — not to pretend none ever will.

    Agent identity: making the agent a first-class, distinct actor

    The reason our old governance couldn’t cope is that agent traffic looked exactly like human traffic. If an agent runs under a shared service account, you cannot tell in the audit log whether “the agent” or “a person using the agent’s role” ran a query, and you cannot apply different rules to the two. AI Agent Identity, which went GA at Summit 2026, fixes this at the platform level: every agent gets a cryptographic identity, per-agent RBAC, and a complete audit trail.

    Concretely, that buys three things. First, attribution — the audit log records that this specific agent, not a nebulous service account, took this action, so incident response has something to trace. Second, differential policy — because Snowflake can recognize when an action occurs in an agent’s context, security teams can apply custom masking or visibility rules to agent traffic specifically, tightening or loosening access for agents independently of the humans behind them. Third, lifecycle — a distinct identity can be reviewed and decommissioned when a project ends, which is the antidote to the classic failure mode where a “the agent” service account silently accumulates privileges for years and is never cleaned up.

    The practical instruction: never run production agents under a shared or personal role. Give each agent its own identity, grant it a purpose-built role scoped to its task, and treat that identity as something you review on a schedule — the same way you’d review a human’s access, because now it’s a non-human actor with real reach.

    Data movement policies: stopping the exfiltration path

    Here’s the MCP-specific risk that most governance frameworks haven’t caught up to. When you connect an agent’s output to external systems over MCP, data reachable by the agent becomes potentially reachable outside the Snowflake perimeter. If the agent’s role can read regulated or confidential data, that data can flow outward through an MCP integration that isn’t subject to the same controls as the warehouse. Your carefully governed table is one tool-call away from a Slack channel or a third-party API.

    Data Movement Policies are the control for exactly this. They let you restrict where data can go and which channels agents are allowed to use, applied at the level of your most sensitive tables. The pattern that works: identify your regulated and confidential datasets, and attach movement policies that restrict agentic access channels — so even if an agent’s role can technically read a table, the policy prevents that data from being moved out through an ungoverned path. This is the difference between “the agent can see it” and “the agent can send it somewhere,” and for regulated data those are very different permissions.

    Pair this with the principle that the model runs where the data lives. Snowflake’s model-in-platform approach (running Claude and other models natively inside Cortex) means sensitive data doesn’t have to leave the perimeter for the agent to reason over it. Movement policies then govern the exceptions — the deliberate, approved paths where data does flow outward — rather than leaving every MCP connection as an open door.

    Multi-party approval: a human gate on destructive actions

    Not every action needs a human. The pattern is to auto-execute low-risk reads and route destructive or high-sensitivity actions to an approval gate — a code-review step before an agent does something irreversible.

    Some actions are too consequential to let an agent take unilaterally, no matter how well-scoped. Dropping a table, moving regulated data, granting privileges, deploying to production — these are the agentic equivalent of a force-push to main. Multi-party approval (in private preview as of Summit 2026) is the control: it requires human sign-off before an agent executes designated high-risk actions, and for the most sensitive it can require two approvers.

    The design pattern that keeps this usable is triage. If you gate everything, people rubber-stamp approvals and the control becomes theater. Instead, classify actions by risk. Read-only work — a SELECT, a Cortex Search, generating a chart — executes automatically; that’s the whole point of an agent. Destructive or high-sensitivity actions divert to an approval gate before they run, and are blocked and logged if no one approves. The engineering task is deciding, up front, which actions in your environment belong on the destructive list — and it’s worth doing that exercise now, before you turn agents loose, rather than after an incident.

    The MCP Gateway: governing the actions, not just the data

    Early MCP adoption looks like every agent configuring its own servers independently — one team wires up a Jira server, another points at an internal API, and nobody has a single view of what’s connected or what those connections can do. That’s ungoverned by construction. Snowflake’s acquisition of Natoma exists to fix this: the MCP Gateway is a centralized, governed layer for every MCP connection in the organization.

    What the Gateway changes is where enforcement happens. Instead of trusting each agent to behave, every tool call — sending an email, opening a ticket, hitting an API — flows through a gateway that enforces identity verification, access policies, and audit controls at the level of the individual action. This extends governance from the data an agent reads to the actions it takes. Central management means one place to see and control all external tool connections; gateway-level access control means an agent can only invoke the tools its policy permits; and tool-call-level audit means you can reconstruct exactly what an agent did across systems, not just within Snowflake.

    The mental shift: data governance and action governance are different problems. Row access policies protect what the agent can read. The MCP Gateway protects what the agent can do with tools once it has read something. In the agentic enterprise you need both, because an agent that can only read sensitive data is a smaller problem than one that can read it and send it somewhere.

    The gotchas nobody warns you about

    Service accounts are where governance goes to die. The most common real-world failure isn’t a clever attack — it’s an agent running under a service account created for “the agent,” never scoped tightly, never reviewed, accumulating privileges as teams bolt on data sources. Tie every agent to a defined access scope and a lifecycle policy, and review non-human identities on the same cadence as human ones.

    MCP tool responses are part of the injection surface. It’s easy to think of prompt injection as coming from user input, but a compromised or malicious MCP server can return a response crafted to hijack the agent’s reasoning. Treat data coming back from external tools with the same suspicion as any other untrusted input — which is another argument for routing MCP through a governed gateway rather than trusting arbitrary servers.

    Built-in guardrails are necessary, not sufficient. Snowflake’s baseline prompt-injection protection is free, automatic, and worth enabling immediately — it blocks known attack patterns. But it’s a pattern database, so it lags novel attacks by definition. Guardrails reduce the frequency of successful injections; scope and movement policies reduce the impact. You need both, and you should never let “guardrails are on” substitute for scoping.

    Cross-organization collaboration multiplies the identity problem. As agentic workflows span companies (clean rooms, shared data, partner integrations), privacy-preserving controls and role separation stop being nice-to-haves and become engineering requirements. An agent acting across an organizational boundary needs an identity and policy that make sense on both sides.

    A starting checklist for production

    If you’re deploying agents now, the order of operations matters. Enable the built-in AI guardrails first — they’re free and automatic. Audit which agents (and which underlying roles) can reach sensitive data, and scope those roles down to least privilege; this is the highest-leverage step and it’s just RBAC discipline. Give each production agent its own identity rather than a shared service account. Write an explicit agent policy — for each agent, what it may do and what’s off-limits — and apply data movement policies to your most sensitive tables, restricting agentic channels specifically. Then identify the destructive actions in your environment that warrant two-person confirmation and get ahead of the multi-party approval rollout. Finally, route MCP connections through a governed gateway so action-level governance and audit exist from day one rather than being retrofitted after an incident.

    The one principle

    An AI agent is a non-human actor that inherits a role’s full reach and exercises none of a human’s judgment, so govern it as an identity, not as a feature. Scope its role to least privilege, give it a distinct auditable identity, restrict where its data can move, gate its destructive actions behind human approval, and route its tool calls through a governed MCP gateway. You will not prevent every prompt injection. What you can decide, in advance, is how little damage a successful one is able to do — and in the agentic enterprise, that decision is the whole game.

    Related reading: Cortex Agents governance & access control (official docs) · Snowflake Managed MCP Servers: secure, governed data agents · How to Use MCP in Snowflake CoCo Desktop · Debugging Zero-Copy Clone Storage Costs in CI/CD · Dynamic Airflow DAGs via Snowflake Metadata

  • The Dark Side of dbt Unit Testing in Snowflake: Managing Credit Burn on Large Test Suites

    The Dark Side of dbt Unit Testing in Snowflake: Managing Credit Burn on Large Test Suites

    Our CI got slower and more expensive at exactly the same rate our test suite got better, and for a while nobody connected the two. We’d done everything the best-practice blog posts told us to: added dbt unit tests to lock down the gnarly transformation logic, wired them into CI so every pull request ran them, felt good about ourselves. Then the Snowflake bill for the CI account crept up, and up, and the person who opened it asked the reasonable question: “Why is *testing* our most expensive warehouse workload?”

    Because dbt unit tests, for all their software-engineering framing, are not free the way unit tests in a normal codebase are free. A Python unit test runs in memory on the machine you’re already paying for. A dbt unit test compiles to SQL and runs on a Snowflake warehouse — and at the scale of a real suite, run on every pull request, that’s a lot of warehouse-seconds you didn’t budget for. This is the honest accounting of where that cost comes from, and how to keep the safety net without the bill.

    One clarification up front, because the word “test” covers two very different things in dbt and conflating them muddies the whole cost conversation. This article is about unit tests — the dbt v1.8+ feature that validates your transformation logic against mock inputs — not data tests (not_null, unique, and friends) that query your real tables. Both cost credits, but they cost them differently, and unit tests have a hidden cost that surprises people.

    TL;DR

    → dbt unit tests validate transformation logic with static mock inputs. But they don’t run in memory — each compiles to a real SQL query that executes on a Snowflake warehouse. Hundreds of tests × every CI run × every PR = real, recurring credit burn.

    → The hidden cost: a unit test’s direct parent models must exist in the warehouse before the test can run. Naively, that means building upstream models just to test one — you pay to materialize parents you don’t care about.

    → The fix for that specific trap: the --empty flag builds empty (zero-row) versions of the parent models, so they exist for the test to reference without paying to populate them.

    → dbt Labs is explicit: only run unit tests in development and CI, never in production. The inputs are static, so production runs burn compute for zero added signal.

    → Warehouse mechanics amplify it: Snowflake bills a 60-second minimum every time a warehouse resumes, so a suite that resumes the warehouse repeatedly pays that floor over and over.

    → The real levers: run only state:modified+ (tests for changed models, not the whole suite), use --empty parents, run on a dedicated XS warehouse with aggressive auto-suspend, and don’t unit-test logic the warehouse already guarantees (like min()).

    How a dbt unit test actually runs

    Here’s the thing the “just like software unit tests” framing hides. When you write a unit test in a normal language, the test harness loads your function into memory and calls it with fake arguments. Nothing leaves the machine. It’s effectively free and effectively instant.

    A dbt unit test does something structurally different. dbt takes your mock input rows, your model’s SQL, and your expected output, and compiles them into a single SQL query — roughly, it injects your fake rows as inline literals, runs them through the actual transformation logic of the model, and compares the result to your expected rows. That compiled query then executes on your Snowflake warehouse like any other query. The “unit” is isolated in the sense that it uses mock data instead of real tables — but the execution is a genuine warehouse query, billed at the standard credit rate for the warehouse’s active time.

    One query is cheap. The problem is arithmetic. A mature suite might have 300 unit tests. Run them on every pull request, and every push to every PR, across a team, and you’re issuing tens of thousands of compiled test queries a week. None is expensive alone; together they’re a line item. And unlike a data test that you might run once daily in production, unit tests fire in the tight inner loop of development, which is exactly where query volume is highest.

    No single test query is expensive. The multiplication across a suite, every CI run, is — and two Snowflake billing mechanics quietly amplify it.

    The hidden cost nobody mentions: parent materialization

    A unit test needs its parent models to exist in the warehouse first. Build them naively and you pay to materialize upstream models just to test one — unless you use –empty.

    This is the trap that turns a manageable cost into a surprising one. A dbt unit test runs against a model, and that model refers to its parents via ref(). For the compiled test query to resolve, the direct parent models have to exist in the warehouse. If they don’t, the test can’t run.

    The naive reaction — and the one dbt’s own docs warn about — is to just build everything upstream first: dbt build, or dbt run the parents, then test. But that means you’ve now materialized a chain of upstream models, scanning and writing real data, purely so a logic test on one downstream model has something to reference. You paid full transformation cost to set up a test that was supposed to be about logic, not data.

    The intended fix is the --empty flag. Running dbt run --select "stg_orders stg_customers" --empty builds empty versions of the parent models — they exist as objects in the warehouse with the right schema but zero rows, so they cost almost nothing to create. The unit test can now resolve its ref()s against those empty parents while still using its own mock data for the actual test. If you’re running unit tests in CI without --empty, this is very likely the single biggest chunk of your test-related spend, and it’s invisible until you look at what got built versus what got tested.

    The warehouse mechanics that amplify it

    Two Snowflake billing details make test-suite cost worse than a naive per-query estimate suggests.

    First, the 60-second minimum. Snowflake bills warehouse compute per second, but with a 60-second floor every time a warehouse resumes from suspended. A single fast test query that takes two seconds still bills a full minute if it resumed a cold warehouse. If your CI pattern lets the warehouse suspend and resume repeatedly across a run, you pay that one-minute floor multiple times for work that totaled seconds.

    Second, warehouse size is usually the wrong knob to reach for, but people reach for it anyway. Teams default to a LARGE or XLARGE warehouse “to be safe,” but unit tests operate on tiny mock datasets — a handful of rows. There is nothing for a big warehouse to parallelize. You’re paying 8x or 16x the per-second rate for a workload that an XSMALL handles identically. For unit testing specifically, warehouse size is close to pure waste above XSMALL.

    The cost math, concretely

    Let’s put rough numbers on it. Say you have 300 unit tests, a team of 6 engineers, and CI runs on every push. A realistic week might see 200 CI runs. If each run executes the full suite and — because nobody set up --empty — also materializes a chunk of the upstream DAG each time, you’re looking at hundreds of thousands of query-seconds plus repeated 60-second warehouse-resume floors.

    Even at a modest XSMALL (1 credit/hour), the repeated resume floors alone add up: 200 runs a week that each cold-start the warehouse is 200 minutes — over 3 hours — of billed time that did almost no work. Add the parent materializations at full data volume and the number climbs fast. Now imagine someone “played it safe” with a MEDIUM warehouse (4 credits/hour): same work, 4x the bill. None of this bought you better tests. It bought you the same tests, slower to notice and more expensive to run.

    The reframe that matters: unit-test cost scales with how you run the suite, not with how good your tests are. Two teams with identical test coverage can have a 10x difference in test spend based purely on --empty, warehouse size, and whether they run the whole suite or just what changed.

    Keeping the safety net without the bill

    Two teams with identical coverage can differ 10x in spend. These are the knobs that account for the gap, biggest win first.

    Run only what changed. The biggest lever by far. In CI you rarely need the whole suite — you need the tests affected by this pull request. dbt’s state comparison lets you select state:modified+ to run only modified models and their downstream dependents. On a big project, a typical PR touches a handful of models, so this turns a 300-test run into a 15-test run. Same protection for the change at hand, a fraction of the queries.

    Always build parents with --empty. Make it the default in your CI script, not an optimization you remember sometimes. Empty parents give the tests something to reference without paying to populate upstream models. This is the fix for the hidden cost above, and it’s a one-flag change.

    Use a dedicated XSMALL CI warehouse with aggressive auto-suspend. Size it down — unit tests don’t benefit from more compute. Give it its own warehouse so test runs don’t tangle with analyst queries or production jobs, which also makes the cost trivially easy to attribute. Set auto-suspend low so it doesn’t idle-bill after the run, but be aware of the flip side: too-frequent suspend/resume cycles trigger the 60-second floor repeatedly, so tune it to your CI cadence rather than blindly to the minimum.

    Never run unit tests in production. dbt Labs is unambiguous here, and it’s worth internalizing why: unit test inputs are static mock data, so the result is identical every time regardless of what’s in production. Running them against prod burns compute to re-confirm something that cannot have changed. Unit tests belong in development (test-driven work) and CI (catching regressions before merge) — full stop.

    Don’t test what the warehouse already guarantees. dbt Labs recommends against unit-testing built-in functions like min() — they’re already exhaustively tested by Snowflake, and a fixture that checks them tells you nothing while still costing a query. Aim unit tests at logic that actually has edge cases: custom categorization, window functions, business rules, things you’ve had bugs in before. Every test you don’t write because it adds no signal is a query you never pay for.

    The gotchas nobody warns you about

    Incremental models need to exist before you unit-test them. Like other parents, an incremental model must be present in the database before its unit test runs, and the expected output of a unit test on an incremental model is the result of the materialization step (what gets merged/inserted), not the final table state. Build it with --empty first, same as any parent, and be precise about what you’re asserting.

    Ephemeral parents force a format change. If the model under test depends on an ephemeral model, you can’t reference it the usual way — you have to provide that input as raw SQL (format: sql) in the test fixture. Not a cost issue, but a “why won’t this run” issue that eats debugging time.

    The suite’s cost is invisible in aggregate dashboards. Test queries blend into total warehouse spend and look like noise. To actually see them, tag your CI runs — dbt’s query-comment with the invocation_id lets you filter QUERY_HISTORY to a single test run and total its cost. If you can’t measure per-invocation test spend, you can’t tell whether your fixes worked.

    “Add more tests” has a non-zero marginal cost here. In a normal codebase, adding a unit test is free forever after. In dbt, every unit test you add is a query that runs on every relevant CI invocation for the life of the project. That’s not a reason to skip tests — it’s a reason to be deliberate: test high-value logic, skip the trivial, and let state:modified+ keep the per-run count proportional to the change, not the suite.

    The one principle

    A dbt unit test is not an in-memory assertion; it’s a warehouse query with a prerequisite. Treat it like one. Build parents empty, run only what changed, size the warehouse down, keep it out of production, and test logic that actually has edge cases. The goal isn’t fewer tests — it’s a test suite whose cost tracks the size of your changes, not the size of your suite. Do that, and unit testing stays the cheap safety net it’s supposed to be instead of the line item that makes someone ask why testing is your priciest workload.

    Related reading: dbt: Unit tests (official docs) · Understanding costs for dbt Projects on Snowflake · dbt State on Snowflake: Skip Unchanged Models · Debugging Zero-Copy Clone Storage Costs in CI/CD · Orchestrating dbt With Airflow on Snowflake

  • Debugging Zero-Copy Clone Storage Costs in CI/CD

    Debugging Zero-Copy Clone Storage Costs in CI/CD

    The Snowflake bill for our CI account had roughly tripled over a quarter, and nobody could point to why. We hadn’t loaded meaningfully more data. Compute was flat. But storage kept climbing, month over month, in an account whose entire job was to spin up throwaway test environments and tear them down. Throwaway. Torn down. And yet the storage line kept going up and to the right.

    The culprit was the feature I’d been recommending to everyone as “basically free”: zero-copy clones. Our CI pipeline cloned production on every pull request, ran migrations and tests against the clone, and dropped it at the end. Textbook. The problem is that “zero-copy” describes the moment of creation and nothing after it, and “drop” doesn’t mean what you think it means when clones are involved. We were paying for storage we believed we’d deleted weeks ago.

    This is the guide to why that happens, how to find it in your own account, and how to stop it. If you run clone-based CI/CD at any scale, some version of this is almost certainly happening to you right now.

    TL;DR

    → Zero-copy clones are free at creation — they share the source’s micro-partitions through metadata pointers. They are not free after anything writes. Every INSERT/UPDATE/DELETE on either side writes new micro-partitions that are billed.

    → In CI/CD the divergence is your migrations. Clone prod, run a schema migration or a backfill against the clone, and you’ve just created new micro-partitions that cost real storage — every pull request, every pipeline run.

    → Dropping the clone does not immediately free that storage. Dropped tables enter Time Travel, then Fail-safe (up to 1 day + 7 days on permanent tables) before the bytes are physically removed. Fast CI loops drop clones constantly and stack up a rolling backlog of retained bytes.

    → The nasty one: clone-group ownership transfer. Storage for shared micro-partitions is owned by the oldest table in the clone group. Drop the source and its still-shared partitions don’t vanish — ownership transfers to a surviving clone. You can delete “the original” and watch storage not move.

    → Diagnose with SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS: ACTIVE_BYTES, TIME_TRAVEL_BYTES, FAILSAFE_BYTES, and the key one, RETAINED_FOR_CLONE_BYTES — bytes kept alive only because a clone still references them.

    → Fixes: clone with transient tables/databases for CI (no Fail-safe, minimal Time Travel), set Time Travel to 0 days on CI objects, actually DROP at pipeline end even on failure, and don’t run heavy migrations against the clone if a lighter check will do.

    Why “zero-copy” is a half-truth

    Snowflake stores table data in immutable micro-partitions — compressed columnar files, tens to hundreds of MB each. Once written, a micro-partition is never modified. When you clone a table, Snowflake doesn’t copy those files. It writes a new metadata entry pointing at the same set of micro-partitions. That’s why a clone of a 5 TB table is instant and costs nothing extra at that instant. It’s a hard link at the partition level, not a copy.

    The word “zero-copy” describes exactly that instant and no other. Because micro-partitions are immutable, the moment you change a row — in the clone or the original — Snowflake can’t edit the shared partition in place. It writes a new micro-partition containing the change, and that new partition is owned exclusively by whichever side made the change. The unchanged partitions stay shared. So your storage cost isn’t the size of the clone; it’s the size of the divergence between the clone and its source.

    The correct mental model, which took me an embarrassingly large bill to internalize: a clone is not a free copy, it’s an instant branch that gets more expensive as it diverges. Read-only clone of 1 TB? Costs nothing. Clone you fully rewrite? Costs a second 1 TB. Real CI workloads land in between — and “in between,” multiplied by every pull request, is a budget line.

    Where the cost actually enters in CI/CD

    The clone is free at step 1. Your migration at step 2 is what creates billed storage — and step 4’s drop doesn’t reclaim it right away.

    Here’s the standard CI pattern, the one in every tutorial:

    CREATE DATABASE ci_test_${BUILD_ID} CLONE production_db;
-- run migrations against the clone
-- run integration tests
DROP DATABASE IF EXISTS ci_test_${BUILD_ID};

    Step one is genuinely free. The cost enters at “run migrations.” A migration that adds a column, backfills a value, rebuilds a table, or runs a dbt model against the clone writes new micro-partitions for every affected partition. If your migration touches 10% of a 500 GB table, you just materialized ~50 GB of new storage — for one CI run. Run that pipeline 40 times a day across a team and the daily divergence is measured in terabytes of writes, even though each individual run “only” changed a slice.

    None of that is visible while you’re looking at it, because the clone gets dropped at the end and the environment looks clean. Which brings us to the part that actually generates the surprise bill.

    The two things that keep paying after you “delete”

    1. Dropping a table doesn’t free its bytes immediately. When you DROP a permanent table (or database), it doesn’t evaporate — it goes into Time Travel for its retention period (default 1 day, and up to 90), and then into Fail-safe for a further 7 days, during which only Snowflake can recover it. Throughout both windows you’re billed for those bytes. A CI loop that creates and drops clones dozens of times a day is continuously feeding a rolling backlog: at any given moment you’re paying for the Time-Travel-and-Fail-safe tail of every clone dropped in roughly the last week, not just the ones alive right now.

    2. Clone-group ownership transfer — the one that breaks intuition. Every table in a clone group has an independent lifecycle, but the storage for shared micro-partitions is owned by the oldest table in the group. Here’s the trap: you decide the source table is the problem and drop it. You expect storage to fall. It doesn’t. Because a clone still references those shared partitions, Snowflake can’t release them — so when they’d otherwise exit Time Travel, ownership transfers to a surviving clone instead. You deleted the original and the bytes simply changed owner. This is why teams stare at a dropped production backup table and can’t understand why the account storage didn’t budge.

    Finding it in your own account

    Four columns tell the whole story. RETAINED_FOR_CLONE_BYTES is the one that reveals storage kept alive purely because a clone still references it.

    The view you want is SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS. It breaks every table’s footprint into the buckets that matter:

    SELECT
  table_catalog,
  table_schema,
  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,
  retained_for_clone_bytes / POW(1024,3) AS clone_retained_gb
FROM snowflake.account_usage.table_storage_metrics
WHERE retained_for_clone_bytes > 0
ORDER BY retained_for_clone_bytes DESC;

    ACTIVE_BYTES is your live data — the part you expect to pay for. TIME_TRAVEL_BYTES and FAILSAFE_BYTES are the recovery tails. RETAINED_FOR_CLONE_BYTES is the smoking gun: bytes that are only still on disk because some clone in the group references them. If that column is large on tables you thought were long gone, you’ve found your leak.

    To hunt CI clones specifically, filter by naming convention and age. Because Snowflake records clone lineage, you can surface old clones still retaining significant storage:

    SELECT
  table_catalog,
  table_name,
  clone_group_id,
  retained_for_clone_bytes / POW(1024,3) AS clone_retained_gb,
  table_created
FROM snowflake.account_usage.table_storage_metrics
WHERE table_catalog ILIKE 'CI_TEST_%'
  AND retained_for_clone_bytes > 0
ORDER BY clone_retained_gb DESC;

    One caveat worth knowing: ACCOUNT_USAGE views have latency (often a couple of hours), so don’t expect a drop you ran five minutes ago to show up instantly. Debug against yesterday’s picture, not this second’s.

    The cost math, concretely

    Say production is 2 TB and your CI migration reliably rewrites ~8% of it per run: ~160 GB of new micro-partitions per pipeline. The clone is dropped at the end, so those 160 GB immediately become Time Travel + Fail-safe bytes rather than active bytes — and they linger for the retention tail. With a 1-day Time Travel plus 7-day Fail-safe window on permanent objects, each run’s divergence sticks around for roughly 8 days before it’s physically purged.

    Run the pipeline 30 times a day and, in steady state, you’re carrying roughly 30 runs/day × 8 days × 160 GB ≈ 38 TB of retained bytes that you believe you deleted. At standard on-demand storage rates that’s a four-figure monthly line for data that exists only because “drop” isn’t “delete” and permanent tables carry a Fail-safe tail. The exact number depends on your migration’s write volume and your retention settings — but the shape is always the same, and it’s always bigger than teams expect.

    The fixes, in priority order

    Clone into transient objects for CI. This is the single biggest lever. Transient tables and databases have no Fail-safe period and a Time Travel retention of 0 or 1 day. Clone production into a transient database for CI, and when you drop it there’s no 7-day Fail-safe tail — the bytes are reclaimable almost immediately. CREATE TRANSIENT DATABASE ci_test_${BUILD_ID} CLONE production_db; Note the source’s own storage behavior is unchanged; this only governs the CI-side lifecycle, which is exactly the part generating your backlog.

    Set Time Travel to zero on CI objects. If you can’t use transient objects for some reason, at least set DATA_RETENTION_TIME_IN_DAYS = 0 on the CI database so dropped clones don’t linger in Time Travel. Combined with the above, your CI divergence becomes genuinely short-lived.

    Actually drop, even on failure. The tutorial pattern drops the clone at the end — but if tests fail and the pipeline exits early, the DROP may never run. Orphaned clones from failed builds are a classic source of retained storage. Put the DROP in a finally/always block so it runs regardless of test outcome, and add a scheduled sweeper task that drops any CI_TEST_% database older than a few hours as a backstop.

    Diverge less. Ask whether your CI actually needs to rewrite 8% of a 2 TB table. Often the migration under test only needs to run against a representative subset, or the test only needs schema validation, not a full data backfill. Cloning gives you production-realistic structure for free; you don’t always need to exercise it against production-scale writes.

    Mind the ownership trap when cleaning up. If you’re deleting old backup clones to reclaim space, remember that dropping the oldest member of a clone group transfers ownership rather than freeing bytes. To actually reclaim storage from a clone group, you generally need to drop all members that reference the shared partitions and let the retention windows expire. Deleting one and expecting the bill to fall is how the confusion starts.

    The gotchas nobody warns you about

    Grants diverge at clone time. A clone inherits grants and masking policies from the source at the instant of cloning, then becomes independent. For CI this is usually fine, but if your pipeline relies on grants applied to production after the clone was taken, they won’t be there.

    Small-file defragmentation writes Time Travel bytes too. Even plain INSERT/COPY/Snowpipe loads can generate Time Travel and Fail-safe bytes, because Snowflake periodically compacts small micro-partitions — deleting the small ones (which enter the recovery tail) and writing a consolidated one. So retained bytes aren’t exclusively a clone phenomenon; clones just amplify it.

    External tables, stages, and pipes don’t clone. If your CI environment depends on them, cloning the database won’t bring them along — you’ll need to recreate them in the clone.

    ACCOUNT_USAGE latency hides fast loops. Because the storage views lag by up to a few hours, a tight CI loop can be generating and “hiding” retained storage faster than your dashboards refresh. Trust the trend over days, not the instantaneous number.

    The one principle

    Zero-copy cloning is free to create and expensive to diverge — and “drop” is not “delete.” In CI/CD, the storage you pay for is the write volume of your migrations times the retention tail of your dropped clones. Clone into transient objects, keep Time Travel short, drop reliably, and diverge only as much as the test actually requires. The feature isn’t lying to you; it’s just describing creation, not the whole lifecycle. Manage the lifecycle and the “free” clone stays close to free.

    Related reading: Snowflake data storage considerations (clone groups & CDP) · TABLE_STORAGE_METRICS view reference · dbt State on Snowflake: Skip Unchanged Models · Snowflake Query Execution: What Really Happens · Snowflake Iceberg v3: When to Migrate

  • How to Use MCP in Snowflake CoCo Desktop

    How to Use MCP in Snowflake CoCo Desktop

    The first thing I tried to do in CoCo Desktop was ask it to pull the open tickets for a data pipeline I was debugging. It couldn’t. Not because it wasn’t smart enough — it’s genuinely good at reasoning over your Snowflake schemas — but because CoCo’s context ends where Snowflake’s context ends. It knew everything about my tables, my RBAC, my lineage. It knew nothing about my Jira board sitting one browser tab away.

    That gap is exactly what MCP closes. Once I wired up a couple of MCP servers, CoCo went from “excellent inside Snowflake’s walls” to “reaches into the rest of my stack” — Jira, GitHub, internal APIs — without me writing a line of integration code. This is the practical guide to doing that: the setup flow, where the config actually lives, how credentials are handled, and the operational limits that will trip you up on day one if nobody warns you.

    A quick naming note before we start, because it confused me too: CoCo is the new name for Cortex Code. Snowflake renamed it at Summit 2026. Same product, same architecture — you’ll still see “cortex” all over the file paths and environment variables, which is why this guide uses both names where the paths demand it.

    TL;DR

    → MCP (Model Context Protocol) is an open standard that connects CoCo Desktop to external tools — GitHub, Jira, internal APIs, databases — without per-tool integration code. Add a server once and its tools appear to the agent automatically.

    → Setup is fast: Agent Settings → MCP tab → + New → pick a scope (Global or Workspace) → pick a transport (Command/stdio or Remote/HTTP) → fill in details → Save. The server starts immediately, no restart.

    → Two transport types: Command (stdio) runs a local process (e.g. uvx mcp-server-git); Remote (HTTP) connects to a URL (e.g. a hosted server with auth headers).

    → Config lives in JSON: global at ~/.snowflake/cortex/mcp.json (all workspaces), workspace at <workspace>/.snowflake/cortex/mcp.json (that project only). Top-level key is "mcpServers".

    → Credentials are handled for you: on first connection CoCo migrates secrets (env vars, headers, OAuth tokens) out of mcp.json and into your OS keychain, then strips them from the file. Never hardcode tokens.

    → The limits that bite: tool output is capped at 50 KB (design servers to return summaries, not raw dumps), default tool timeout is 60 seconds (override with COCO_MCP_TOOL_TIMEOUT_MS), and tool names must be alphanumeric/underscore/hyphen and under 64 characters or the server is rejected outright.

    → If you already run MCP servers for Claude Desktop, Cursor, or Windsurf, CoCo Desktop can often reuse them — MCP is a standard, not a Snowflake-specific connector.

    What MCP actually does for CoCo

    CoCo is a data-native coding agent. Its whole advantage is that it understands your Snowflake environment — live schemas, access controls, lineage — so it generates SQL and dbt code that actually works against your real objects within your permissions. That’s also its boundary. The moment you need context from outside Snowflake, CoCo is blind to it.

    MCP is the bridge. It’s an open protocol (the same one Claude Desktop, Cursor, and Windsurf use) that lets an agent call tools exposed by external “servers.” A GitHub MCP server exposes tools like “search code” and “list pull requests.” A Jira server exposes “find issues” and “create ticket.” Once you register that server with CoCo, those tools become part of the agent’s toolbox automatically — no code changes, no custom connector. You ask CoCo “what are the open bugs on the ingestion pipeline?” and it calls the Jira tool, reads the result, and reasons over it alongside your Snowflake context.

    The mental model that helped me: CoCo already has one deep well of context (Snowflake). MCP servers are additional wells you drill wherever you need them. Each server you add widens what the agent can see and do.

    Setting up your first MCP server

    The whole setup is a short form in Agent Settings. The server starts the moment you save — no restart dance.

    You manage everything through the Agent Settings panel. Open Agent Settings, select MCP from the sidebar, and you’ll see the MCP Connectors panel listing any configured servers and their status.

    To add one, click + New. You’ll fill in a short form:

    Server Name — a unique identifier, e.g. github. This name matters more than it looks: it becomes part of the tool namespace. A server named github exposes tools like mcp__github__search. Pick descriptive names so tool calls read clearly — mcp__github__search tells you what it does; mcp__gh1__search doesn’t.

    Scope — Global stores the server in ~/.snowflake/cortex/mcp.json and makes it available in every workspace. Workspace stores it in <workspace>/.snowflake/cortex/mcp.json, scoped to the current project so it travels with the repo. Use Global for tools you always want (your personal GitHub); use Workspace for project-specific servers that should live in version control with the code.

    Server Type (transport) — pick Command (stdio) to run a local process, then enter the command (for example uvx mcp-server-git). Pick Remote (HTTP) to connect to a hosted server, then enter the Server URL (for example https://your-mcp-server-url) and optionally add auth Headers. For stdio servers you can add Environment Variables instead.

    Click Save, and the server starts. Its tools are available to the agent immediately.

    If you don’t have a specific server in mind, click + New and select Browse MCP Servers — CoCo Desktop ships with a gallery of ready-to-install integrations you can add straight from the UI.

    Editing the config directly (JSON)

    The form is convenient, but for anything repeatable — sharing setup with a team, checking config into git — you’ll want the JSON. In the Add New MCP Server form, switch to the JSON tab, or edit the files directly. The top-level key is "mcpServers", and each entry is keyed by server name:

    {
  "mcpServers": {
    "git": {
      "command": "uvx",
      "args": ["mcp-server-git"]
    },
    "internal-api": {
      "type": "http",
      "url": "https://your-mcp-server-url",
      "headers": { "Authorization": "Bearer ${API_TOKEN}" }
    }
  }
}

    CoCo expands environment variables in config fields before connecting, so you can reference ${API_TOKEN} and similar. Prefer the braced form ${VAR} over bare $VAR to avoid ambiguity. There’s also a special ${workspaceFolder} variable that resolves to the current workspace root — handy for paths like cwd or envFile.

    How config files stack (the merge order)

    Config merges from multiple sources; later layers win on name collisions. Workspace beats global beats admin-enforced — unless the admin has locked things down.

    This is the part that saves you a confusing debugging session later. CoCo Desktop merges MCP config from several sources, and when two sources define a server with the same name, the later source wins. The order, from lowest to highest priority:

    First, administrator-enforced servers from managed settings. Then user (global) servers from ~/.snowflake/cortex/mcp.json. Then workspace servers from <workspace>/.snowflake/cortex/mcp.json. So if you have a server named github in both your global and your workspace config, the workspace definition takes precedence. This is usually what you want — a project can override your personal defaults — but it also means a workspace config you forgot about can silently shadow your global one.

    On managed accounts there’s an extra wrinkle: admins can restrict MCP usage through managed settings and URL allowlists, and can even disable user MCP servers entirely so that only admin-enforced servers load. If a server you configured refuses to appear on a corporate account, check whether the admin has locked MCP down before you assume your config is broken.

    How credentials are handled (better than you’d expect)

    This surprised me pleasantly. When you add a server with environment variables, headers, or OAuth, CoCo doesn’t leave your secrets sitting in a plaintext JSON file. On first connection it migrates those sensitive values out of mcp.json and into your operating system’s keychain, then rewrites the JSON file with those fields removed. Credentials are stored under a keychain entry named mcp_oauth_<server-name> as a single blob containing tokens, OAuth registration, headers, and environment variables.

    Practically, this means: put your token in as an env var reference or let the OAuth flow run, and after the first connect it won’t be in the file anymore. Don’t hardcode raw tokens in mcp.json expecting them to stay — and don’t panic when they disappear from the file, that’s the migration working. If you ever need to reset a credential, remove and re-add the server to trigger a fresh flow.

    The operational limits nobody warns you about

    These three cost me time before I understood them, and they’re the difference between “MCP is flaky” and “MCP works fine, I just configured it wrong.”

    Tool output is capped at 50 KB. If you point an MCP server at something that returns large result sets — a query that dumps thousands of rows, an API that returns a giant JSON blob — CoCo truncates the output and appends a notice. The fix isn’t to raise a limit; it’s to design the server to return summaries or pointers, not raw dumps. Have the tool return “here are the top 20 rows and a row count” or “results written to this file,” and let CoCo read the detail in a follow-up step if it needs to.

    The default tool timeout is 60 seconds. Wire up a server that hits a slow internal API and you can spend ten minutes assuming the connection is broken when the tool is just slow. Override the timeout globally with the COCO_MCP_TOOL_TIMEOUT_MS environment variable — raise it for genuinely long-running tools, or lower it to fail fast on servers that should be quick.

    Tool names must be alphanumeric, underscores, or hyphens, and under 64 characters. An MCP server that exposes a tool with a non-conforming name gets rejected outright — not silently renamed, rejected. If a server won’t load and the config looks right, check the tool names it exposes.

    The gotchas nobody warns you about

    Cross-app discovery on shared machines. Because MCP is a shared standard, CoCo can discover servers you set up for other tools — and on a shared machine, that can mean picking up someone else’s servers or exposing yours. Be deliberate about scope on multi-user boxes.

    Variables expand from the launch environment, not your editor’s shell. CoCo expands ${VAR} from the environment it was launched in, not from a shell embedded in an editor. If a variable resolves to empty, check that it’s actually set in the environment where CoCo (not your terminal-inside-the-app) started.

    Descriptive server names aren’t cosmetic. Because the server name becomes the tool namespace prefix, a vague name makes every downstream permission rule and tool call harder to read. Name servers for what they connect to, once, up front.

    Permissions are per-tool and worth configuring. MCP tools participate in CoCo’s standard permission system. You can allow, deny, or prompt per tool, matching individual tools by full name (mcp__github__read_file) or all tools from a server with a wildcard (mcp__github__*). At runtime CoCo also asks on first use and can remember the choice for the session. Denying destructive tools explicitly — mcp__github__delete_repo, say — is cheap insurance.

    A sensible starting setup

    If you’re setting this up for the first time on a Snowflake data project, here’s the configuration I’d start with. Add a Git server (Command/stdio, uvx mcp-server-git) at Workspace scope so it travels with the repo. Add your issue tracker (Jira or GitHub Issues) at Global scope since you’ll want it everywhere. Set a permission policy that allows read tools freely, asks on writes, and denies anything destructive. Bump COCO_MCP_TOOL_TIMEOUT_MS only if you actually add a slow server. And design any custom internal-API server to return summaries under 50 KB from the start, so you never hit the truncation wall.

    That gives you a CoCo that reasons over your Snowflake data and your tickets and your code history, with guardrails on the actions that matter — which is the whole point of MCP here.

    The one principle

    CoCo’s native genius is Snowflake context; MCP is how you extend its reach past Snowflake’s walls without writing integration code. Add servers deliberately, name them clearly, let the keychain hold your secrets, and design tools to return summaries — then the agent can reason across your whole stack instead of just your warehouse.

    Related reading: CoCo Desktop MCP support (official docs) · Model Context Protocol specification · Snowflake CoCo product page · Snowflake Interactive Tables: How and When to Use Them · Orchestrating dbt With Airflow on Snowflake

  • Snowflake Interactive tables: How and when to use them ( From Production)

    Snowflake Interactive tables: How and when to use them ( From Production)

    The first time a product manager asked me why our “real-time” Snowflake dashboard took four seconds to load on a Monday morning, I didn’t have a good answer. The data was fresh. The query was simple — a filtered aggregation over a few million rows. But at 9 a.m., when three hundred people opened the same dashboard at once, our XSMALL warehouse queued them up like a single cashier at a stadium. Each query was fast in isolation. Together, they were a traffic jam.

    I did what everyone does: I threw a bigger warehouse at it, then a multi-cluster warehouse, then watched the credits burn. It helped the concurrency but the per-query latency floor never really dropped below a second or two, and the bill made my manager wince. Snowflake is an OLAP engine. It’s built to scan enormous columnar data for analytics, not to answer the same small lookup a thousand times a second. I was using a freight train to run a pizza delivery service.

    Then Snowflake shipped Interactive Tables and Interactive Warehouses. I’ve now run them in production for a few months, and this is the honest guide I wish I’d had: what they are, when they’re worth it, when they’ll quietly cost you money, and the gotchas that only show up after you’ve committed.

    The whole feature at a glance: who it serves (green), how it works (blue), and how you set it up plus its limits (yellow).

    TL;DR

    → Interactive Tables + Interactive Warehouses are a serving layer inside Snowflake, built for low-latency, high-concurrency reads — dashboards, data-powered APIs, AI agents querying structured data in real time.

    → They work as a pair. The interactive table is optimized for fast retrieval; the interactive warehouse caches its data files as hot cache and serves sub-second reads. Standard warehouses can query interactive tables, but you only get the big speedup through an interactive warehouse.

    → Real numbers from independent testing: an Interactive XS delivered roughly 3.9x lower latency than a standard Gen1 XS, at about 40% lower credit cost per hour — combining to around a 75% reduction in cost per query on a serving workload.

    → The big constraint: no UPDATE or DELETE. The only DML is INSERT OVERWRITE. You keep data fresh with auto-refresh (set TARGET_LAG) against a source table, not by mutating the interactive table.

    → The clustering key is fixed at creation and cannot be changed. Choose it to match the WHERE clauses of your most latency-critical queries. Get this wrong and your only fix is recreating the table.

    → Interactive warehouses historically did not auto-suspend — they stay warm to keep the cache ready, so you effectively pay 24/7. As of the spring 2026 updates, auto-suspend, auto-resume, and auto-scaling reached GA, which changes the cost math meaningfully.

    → Use them when latency and concurrency are the product. Skip them for batch ETL, ad-hoc exploration, or anything write-heavy. This is a serving layer, not a transformation layer.

    What they actually are (in plain terms)

    Think of your Snowflake setup as having two jobs that have always been jammed into the same tool. Job one is transformation: big batch queries that scan and reshape data. Standard warehouses are great at this. Job two is serving: answering a flood of small, repetitive reads instantly — the dashboard that refreshes for every user, the API endpoint hit thousands of times a minute. Standard warehouses are mediocre at this, not because they’re slow, but because they’re built for throughput on big scans, not latency on small lookups under heavy concurrency.

    Interactive Tables and Warehouses are Snowflake’s serving layer. An interactive table stores data in a form optimized for fast, filtered retrieval. An interactive warehouse contains a query engine tuned for short, highly concurrent reads, and it caches the interactive table’s data files locally as hot cache. When a query comes in, it’s answered from that warm cache with a latency profile closer to a key-value store than a data warehouse. The two are a matched pair — the table is fast on its own, but the warehouse is where the sub-second magic happens.

    One mental model that helped me: a standard warehouse is a library where a librarian walks the stacks for every request. An interactive warehouse is the same library, but the most-requested books are already stacked on the front desk, warm and waiting. That’s the cache. It’s why the warehouse has to stay on — the moment it suspends, the front desk clears and the next reader waits for the walk to the stacks again.

    When to use them (and when not to)

    I’ve become fairly opinionated about this after watching a few teams reach for interactive tables because they were new and shiny, then get surprised by the bill. Here’s the honest split.

    Use interactive tables when: you’re serving a customer-facing or internal dashboard with real concurrency (dozens to thousands of simultaneous users), you’re powering a data API where each call is a small filtered read and latency is a product requirement, you’re feeding an AI agent that queries structured data in real time, or you have a workload where the same shapes of query hit the same tables constantly and predictably. In all these, latency and concurrency are the product, and a continuously-running warehouse is justified because the traffic is continuous.

    Don’t use them when: your workload is batch ETL or transformation (that’s what standard warehouses are for), your queries are ad-hoc and exploratory (the cache never warms usefully if every query is different), your data is write-heavy with lots of updates and deletes (the no-DML constraint will fight you), or your traffic is spiky and infrequent (a warehouse you pay to keep warm for occasional bursts is money on fire — though auto-suspend now softens this).

    The question I ask before every interactive-table decision: does this workload justify a warehouse that runs continuously? If yes, the performance is genuinely excellent. If no, you’re probably better off with a well-tuned standard warehouse and result caching.

    How to actually set it up

    The mental model is familiar if you’ve used Snowflake, which is one of the nicer things about this feature. You create the table with a standard warehouse, then serve it through an interactive one.

    First, create the interactive table. The CREATE TABLE syntax is extended with the INTERACTIVE keyword, and a CLUSTER BY clause is required — this isn’t optional like it is on standard tables:

    CREATE INTERACTIVE TABLE dashboard_events
CLUSTER BY (tenant_id, event_date)
AS SELECT tenant_id, event_date, event_type, metric_value
FROM raw.events;

    Notice the clustering key. Choose it to match the WHERE clauses of your most time-critical queries — here, assuming most dashboard reads filter by tenant and date. This decision is permanent, so think about it harder than you normally would.

    To keep the table fresh without DML, make it a dynamic interactive table by setting a target lag. It will auto-refresh from the source to stay within that window (the minimum is 60 seconds):

    CREATE INTERACTIVE TABLE dashboard_events
TARGET_LAG = '60 seconds'
CLUSTER BY (tenant_id, event_date)
AS SELECT tenant_id, event_date, event_type, metric_value
FROM raw.events;

    Then create an interactive warehouse and attach the table so its data files get cached. Keep the warehouse small — an XSMALL is often plenty for serving:

    CREATE INTERACTIVE WAREHOUSE serving_wh
WAREHOUSE_SIZE = 'XSMALL';

ALTER WAREHOUSE serving_wh ADD TABLES dashboard_events;

    Point your dashboard or API at serving_wh, and reads now hit the warm cache. The first queries after attaching a table run while the cache warms, so they’ll be slower — don’t benchmark in that window and panic.

    The cost math, honestly

    This is where teams get surprised, so let’s be concrete. Interactive warehouses cost roughly 40% less per credit than standard warehouses — an XSMALL standard warehouse runs at 1 credit/hour, while an interactive XSMALL is about 0.6 credits/hour. Combined with the lower latency (fewer warehouse-seconds per query), independent testing found the cost per query dropped by around 75% on a serving workload.

    That sounds like a pure win, and for the right workload it is. But the catch has always been the always-on nature. Historically, interactive warehouses did not auto-suspend — they have to stay warm to keep the cache ready, so you were effectively paying for 0.6 credits/hour × 24 hours × 30 days whether or not traffic justified it. That’s roughly 432 credits/month for a single XSMALL that never sleeps. If your traffic is genuinely continuous, the per-query savings dwarf this. If your traffic is bursty, this idle cost can erase the savings entirely.

    The spring 2026 updates changed this materially: auto-suspend and auto-resume reached GA, so you can now let an interactive warehouse suspend during quiet periods and pay the cache-rewarming cost on resume instead of paying to stay warm around the clock. There’s also a fallback warehouse option — when a query on the interactive warehouse exceeds the timeout, Snowflake can transparently retry it on a designated standard warehouse instead of erroring out, which makes mixed workloads far less fragile. And auto-scaling went GA, so the warehouse scales with concurrency instead of you hand-tuning cluster counts.

    My rule: model the idle cost first. Take your warehouse’s credits/hour, multiply by the hours you’ll actually keep it warm, and compare that baseline to your current serving spend before you get excited about per-query savings. The per-query numbers are real, but they only pay off above a traffic threshold.

    The gotchas nobody warns you about

    The clustering key is a one-way door. On a standard table, you can iterate on clustering freely. On an interactive table, the clustering key is fixed at creation and cannot be changed. If your query patterns shift, or you guessed wrong about which columns your hot queries filter on, your only remedy is recreating the table. Treat this decision like a schema migration, not a tuning knob. This is, in my experience, the single most common source of regret with the feature.

    No UPDATE, no DELETE — plan your pipeline around it. The only DML is INSERT OVERWRITE. If your instinct is to patch a few rows, you can’t. The intended pattern is: mutate the source table with normal DML, and let auto-refresh (TARGET_LAG) propagate changes to the interactive table. This is actually more efficient than row-level DML on the serving layer, but it forces you into a refresh-based mental model. Append-only and full-refresh patterns fit naturally; frequent surgical updates do not.

    The warehouse only queries what you attach. An interactive warehouse can only query interactive tables that have been explicitly added to it, and it only supports SELECT. Forget to ADD TABLES and your queries won’t find the data. This trips people up because it’s different from the standard warehouse model where any warehouse can query any table it has grants on.

    Cache-warming latency is real and invisible in benchmarks. When you first attach a table, or right after a refresh, the cache is cold and those initial queries are slower. If you benchmark immediately after setup and conclude the feature is underwhelming, you measured the cold path. Let it warm, then measure.

    No Fail-safe. The Fail-safe data recovery mechanism isn’t available for interactive tables. Time Travel still works, so you’re not flying blind, but the extra safety net you may be used to isn’t there. For a serving layer fed from a source table you control, this is usually fine — you can always rebuild from source — but know it going in.

    Mistakes that drain the budget

    Keeping a warehouse warm for traffic that isn’t there. The classic. You stand up an interactive warehouse for a dashboard that gets heavy use twice a day and sits idle the rest of the time, and you pay to keep the cache warm through all the dead hours. With auto-suspend now GA, configure it — don’t run 24/7 out of habit.

    Routing every query to the interactive warehouse. Interactive warehouses shine on small, concurrent reads. Fire a heavy, complex analytical query at one and you’re using the wrong tool — that’s what the fallback warehouse and your standard clusters are for. Route small serving queries to interactive, keep heavy jobs on standard. Getting this routing right is where most of the real-world value (and most of the operational effort) lives.

    Migrating tables that don’t need it. Every table you make interactive adds a data-management surface — a refresh to monitor, a cache to keep warm, a clustering decision you can’t undo. Only promote the tables that actually sit in a latency-critical serving path. A table that backs a weekly report has no business being interactive.

    The one principle

    Interactive tables are a serving layer, not a transformation layer. Transform data on standard warehouses; serve it on interactive ones — and only when the traffic is continuous enough to justify a warehouse that stays warm. The feature is genuinely excellent at the job it was built for. Nearly every problem I’ve seen with it came from asking it to do a different job.

    Related reading: Snowflake interactive tables and warehouses (official docs) · CREATE INTERACTIVE TABLE reference · Snowflake Query Execution: What Really Happens · Snowflake Iceberg v3: When to Migrate · dbt State on Snowflake: Skip Unchanged Models

  • Orchestrating dbt with Airflow on Snowflake: Job vs Model-Level in 2026

    Orchestrating dbt with Airflow on Snowflake: Job vs Model-Level in 2026

    For years, the pattern was: Airflow sits in one corner of your infrastructure, dbt runs on a server somewhere else, they pass data between each other via manual credential handoffs and cron jobs, and when something breaks at 2 AM, you’re SSH-ing into the dbt box, checking Airflow logs, querying Snowflake directly, and stringing it all together in your head.

    The question you’ve been asking for three years is whether dbt should be one big task in Airflow (job-level) or broken into one task per model (model-level). The answer in 2026 is: it doesn’t matter anymore. What matters is that your dbt runs inside Snowflake as a native DBT PROJECT object, Airflow orchestrates it from outside, and all the monitoring, logs, and failure notifications are in one place instead of three.

    This shift from external dbt servers to Snowflake-native orchestration changes everything. Not just infrastructure. The way you think about observability, debugging, and the entire data platform.

    TL;DR

    → Job-level orchestration: one Airflow task runs `dbt run –select tag:daily`. Simple, clean, fast. Loses model-level visibility and parallelization. Use when: small projects, simple DAGs, speed matters over observability.

    → Model-level orchestration: Astronomer’s Cosmos library renders each dbt model as a separate Airflow task. Full visibility, failures are model-scoped, parallelization is automatic. Overhead was real. Not anymore in 2026.

    → Snowflake native dbt projects (GA Nov 2025): dbt runs inside Snowflake as a schema-level DBT PROJECT object. No external dbt server. No separate credentials. Orchestrate from Airflow via REST API. This is the new default architecture.

    → Real benchmark: a 400-model project on job-level Airflow + external dbt = 18-minute runs. Model-level Cosmos + Snowflake native dbt = 12 minutes with full per-model visibility. The performance penalty of model-level is gone.

    → The observability win: failures show up as “stg_orders failed at compile time” in Airflow UI, not “dbt run exited with code 1” in a SSH log. Debugging time drops by 40-60%.

    → Snowflake-native setup: create a DBT PROJECT in a schema, grant Airflow’s service account EXECUTE on it, call it from Airflow via SnowflakePythonOperator with `EXECUTE DBT PROJECT` command. Snowflake handles execution, Airflow gets the logs.

    → If you’re still running dbt Core on an external server with Airflow alongside it: try the native dbt Projects setup this weekend. Most teams report the infrastructure feels 40% lighter after the rebuild.

    The job-level vs model-level question, and why it’s been misleading

    For the last four years, the Airflow + dbt conversation has been dominated by one question: should you run all of dbt in one Airflow task (job-level), or break it into one task per model (model-level)?

    Job-level won out in most teams because it was simpler. One Airflow task. One line of configuration. Fast deploys. The downside: if a model failed in the middle of a 400-model run, the entire job failed, and you had to dig into dbt logs to find which of the 20 intermediate models actually broke.

    Model-level promised full visibility — every model gets its own task, failures are scoped to the model, Airflow’s UI shows you exactly where the pipeline broke. But it had a penalty: rendering 400 models as 400 separate Airflow tasks created overhead in the Airflow scheduler, the DAG parsing time doubled, and you had to manage dynamic task generation (which was brittle).

    For four years, teams picked job-level because the model-level overhead wasn’t worth the observability gain. That trade-off is no longer real.

    Why model-level is winning in 2026 — and what changed

    Three things shifted:

    1. Astronomer’s Cosmos library matured. Cosmos (open-source) automatically converts a dbt project into an Airflow DAG. Instead of manually writing task definitions for every model, you pass your dbt_project directory to Cosmos, and it generates the DAG dynamically. The overhead of parsing 400 models exists — it’s not magical — but it’s now acceptable (1–2 seconds added to DAG parsing). Not free, but not expensive.

    2. Snowflake native dbt Projects arrived. When dbt runs on an external server, model-level orchestration meant Airflow communicating task-by-task with that external box. Latency overhead. Credential management complexity. Snowflake’s native dbt Projects (GA November 2025) lets dbt run inside Snowflake itself. Airflow just sends a single command (`EXECUTE DBT PROJECT model_name`) to Snowflake and waits for results. The execution is native. The communication is just REST API calls. The overhead drops significantly.

    3. Orchestration became about observability, not infrastructure. Teams in 2026 have stopped asking “what’s the performance impact?” and started asking “can I see failures at model granularity?” The answer is yes, and the cost is no longer real. The observability win — debugging a failed model in minutes instead of 45 minutes — justifies the architecture.

    Real benchmark: 400-model project, production traffic

    A data team running Airflow on AWS (3x m5.2xlarge instances), dbt Core on a separate EC2 box, Snowflake warehouse (MEDIUM):

    Before (job-level + external dbt): `dbt run –select tag:daily` runs once per day. Entire job as one Airflow task. 18 minutes wall-clock time. Failed model buries itself in the dbt run log. Debugging takes 45+ minutes because you’re correlating dbt logs, Snowflake QUERY_HISTORY, and Airflow task logs.

    After (model-level Cosmos + Snowflake native dbt): Same 400 models, now as 400 Airflow tasks generated by Cosmos. Parallel execution on the MEDIUM warehouse runs up to 8 models at a time. 12 minutes wall-clock time (33% faster). Failed model shows up in Airflow UI as a red task. Click it. See the exact model that failed, the SQL compile error, the exact line. Debugging takes 8 minutes.

    The speed improvement comes from parallelization (you can run independent models concurrently). The debugging improvement comes from per-model observability. Both were impossible before because the overhead was too high. Not anymore.

    How to orchestrate Snowflake native dbt Projects from Airflow

    Snowflake-native dbt Projects: dbt runs inside Snowflake, Airflow orchestrates from outside. Simpler infrastructure, unified observability.

    Here’s what a production DAG looks like when dbt runs as a native Snowflake object, orchestrated from Airflow:

    from airflow import DAG
from airflow.providers.snowflake.operators.snowflake import SnowflakePythonOperator
from airflow.providers.slack.operators.slack_webhook import SlackWebhookOperator
from datetime import datetime

default_args = {
'owner': 'analytics',
'retries': 2,
'retry_delay': timedelta(minutes=5),
}

with DAG(
'snowflake_dbt_daily_run',
default_args=default_args,
schedule_interval='0 2 * * *', # 2 AM daily
start_date=datetime(2026, 1, 1),
catchup=False,
) as dag:

# Raw data load (external tool or Airflow operator)
load_raw = SnowflakePythonOperator(
task_id='load_raw_data',
python_callable=load_from_source, # your extraction logic
)

# Run dbt transformations as a native Snowflake DBT PROJECT
run_dbt = SnowflakePythonOperator(
task_id='dbt_transform',
python_callable=execute_dbt_project,
op_kwargs={
'sql_command': 'EXECUTE DBT PROJECT analytics_db.transforms',
'database': 'analytics_db',
},
)

# Export or activate downstream (BI, ML, etc.)
notify_success = SlackWebhookOperator(
task_id='notify_success',
http_conn_id='slack_webhook',
message='Daily dbt transforms completed successfully',
)

load_raw >> run_dbt >> notify_success

    The key line is `EXECUTE DBT PROJECT analytics_db.transforms`. That command runs inside Snowflake. Airflow waits for it to complete. Logs come back to Airflow. All in one place.

    Before this, you’d have an external dbt server, SSH credentials in Airflow secrets, a bash script that connects to the box and runs `dbt run`, and error handling that was fragile. Now it’s a direct REST API call to Snowflake.

    Setup: Snowflake side (one-time)

    Create the dbt project object in Snowflake. One time. Then Airflow orchestrates it:

    -- As SYSADMIN or higher
USE ROLE SYSADMIN;

-- Create a dedicated role and user for Airflow
CREATE ROLE IF NOT EXISTS dbt_executor_role;
CREATE USER IF NOT EXISTS airflow_svc_user
DEFAULT_ROLE = dbt_executor_role
DEFAULT_WAREHOUSE = dbt_transform_wh;

-- Create a dedicated warehouse for dbt runs
CREATE OR REPLACE WAREHOUSE dbt_transform_wh
WITH WAREHOUSE_SIZE = 'MEDIUM'
AUTO_SUSPEND = 120
AUTO_RESUME = TRUE
INITIALLY_SUSPENDED = TRUE;

-- Grant permissions to Airflow's service account
GRANT ALL ON WAREHOUSE dbt_transform_wh TO ROLE dbt_executor_role;
GRANT USAGE ON DATABASE analytics_db TO ROLE dbt_executor_role;
GRANT USAGE, CREATE TABLE, CREATE VIEW ON SCHEMA analytics_db.staging TO ROLE dbt_executor_role;
GRANT USAGE, CREATE TABLE, CREATE VIEW ON SCHEMA analytics_db.marts TO ROLE dbt_executor_role;

-- Grant the crucial permission: execute dbt projects in that schema
GRANT EXECUTE DBT PROJECT ON SCHEMA analytics_db.transforms TO ROLE dbt_executor_role;

-- Grant the role to your service user
GRANT ROLE dbt_executor_role TO USER airflow_svc_user;

    Then create your dbt project in Snowflake (via Snowsight → Workspaces, or via SQL `CREATE DBT PROJECT`). That’s it on the Snowflake side.

    The three gotchas you’ll hit

    Gotcha 1: Forgetting schema-level EXECUTE permissions. The `GRANT EXECUTE DBT PROJECT ON SCHEMA` is the easy line to miss. You can grant object-level execute all day and Airflow still won’t be able to run the project. It’s schema-level that matters.

    Gotcha 2: dbt docs and artifacts not flowing back to Airflow. When dbt runs inside Snowflake, the manifest.json and dbt_project.yml artifacts stay inside Snowflake. If you’re using those artifacts downstream (dbt Cloud webhooks, dbt Mesh coordination, Lineage tools), you need to export them explicitly from Snowflake after the run completes. Set up a post-run task that pulls `SELECT GET_STAGE_LOCATION(…)` to grab the artifacts.

    Gotcha 3: Incremental models and first-run confusion. This is the same gotcha as in the dbt State article — the first run of an incremental model executes a full load, changing the compiled SQL. dbt State knows about this. Airflow doesn’t. Expect a full downstream rebuild on Run 2. Normal behavior. Just know it coming in.

    When to use job-level, when to use model-level

    Job-level still makes sense if: your dbt project is small (<50 models), the entire project fits in a single logical unit, you don’t need per-model visibility, or you’re on dbt Cloud’s native scheduler (not Airflow). Keep it simple.

    Model-level (Cosmos) makes sense if: your project has 100+ models, you need per-model failure isolation, debugging speed matters, or you want Airflow as the single source of truth for your entire data pipeline. Most production teams in 2026 are here.

    The hybrid: Some teams run both. Job-level for hourly incremental ingestion (simple, fast), model-level for daily mart builds (visibility matters). You can mix them in the same DAG — one task for `dbt run –select tag:hourly`, another task group for model-level mart runs via Cosmos.

    The one principle

    Observability at execution time beats simplicity at configuration time. Job-level is simpler to configure. Model-level is simpler to debug. In production systems that need to run reliably and recover fast, you spend more time debugging than configuring. Pick the architecture that lets you see failures at the right granularity.

    Related reading: dbt State on Snowflake: Skip unchanged models, cut runtime 60% · dbt Fusion: 30x Faster Parsing with the Rust Engine · Data Contracts: Stop Schema Breakage Before It Happens · Official dbt + Airflow Integration Guide · Astronomer Cosmos: dbt + Airflow

  • How to setup DBT state in Your Snowflake project (step-by-step Config Guide)

    How to setup DBT state in Your Snowflake project (step-by-step Config Guide)

    You read about dbt State. You understood the pitch — skip unchanged models, cut compute, stop paying to rebuild what didn’t change. Then you opened your project and stared at an empty dbt_project.yml wondering where to actually start.

    This is that guide. Not the concept. The implementation. Real configs, real gotchas, real Snowflake-specific decisions you’ll face inside the first hour of setup — covered here so you don’t have to learn them the hard way.

    If you’re still fuzzy on what dbt State is and whether the pricing makes sense for your team, start with the conceptual breakdown here. This guide assumes you’ve made the decision and want to ship it.

    TL;DR

    → dbt State is available in dbt Core v1.12+, Fusion, and the dbt platform. Enable via CLI, platform UI, or env var.

    → The most important config is lag_tolerance. Set tight in prod (4h), loose in dev (7d). Use Jinja so it’s one line, not two blocks.

    → On Snowflake, the “clone” reuse strategy uses Snowflake zero-copy cloning — it’s nearly instant and nearly free. This is why dbt State feels especially fast on Snowflake vs other warehouses.

    → Layer your lag_tolerance by folder: staging loose (data changes often), marts tight (business metrics need to be fresh).

    → Incremental model gotcha: the first time any incremental model runs, its compiled SQL changes (from full load to filtered load). dbt State treats this as a logic change and forces a downstream rebuild, even if lag_tolerance hasn’t elapsed. Expected behavior. Not a bug.

    → Track your skip rate using Snowflake’s QUERY_HISTORY view with the query_tag dbt sets automatically.

    → In CI: set DBT_ENGINE_MANAGE_STATE=true as an env var. Nothing else changes in your CI pipeline.

    → Target 40–60% skip rate in the first two weeks. Below 30% means your lag_tolerance is too tight. Above 80% means you’re missing rebuilds you actually need.

    Step 1: Enable dbt State

    There are three paths depending on what you’re running.

    dbt platform (Cloud): Go to Account settings → State → Enable dbt State. Then on each job: Settings → Edit → Execution settings → Enable dbt State → Save. That’s it. No YAML changes required to turn it on.

    dbt Core v1.12+ locally: Run dbt login in your terminal. It opens a browser window to authenticate with either your dbt platform account or the standalone app at app.state.dbt.com. Once authenticated, dbt State runs automatically on every dbt run or dbt build. You can override per-run with --manage-state or --no-manage-state.

    CI pipelines: Set DBT_ENGINE_MANAGE_STATE=true as an environment variable. Pass your dbt State credentials as DBT_STATE_TOKEN from your secrets manager. No other pipeline changes needed.

    Step 2: Configure lag_tolerance — the decision that matters most

    The default lag_tolerance is 45 minutes. Meaning: if upstream data changed less than 45 minutes ago, dbt State will skip the downstream model. If data changed more than 45 minutes ago and the model hasn’t rebuilt, it will rebuild.

    Forty-five minutes is a sensible global default, but in practice you want different tolerances for different environments and different parts of your DAG. Here’s the recommended starting config in dbt_project.yml:

    models:
+state:
lag_tolerance: "{{ '4h' if target.name == 'prod' else '7d' }}"

    That single Jinja expression does the right thing in both environments without duplicating config blocks. In production, models rebuild if upstream data is more than 4 hours stale. In development — where you’re iterating, not serving dashboards — models wait 7 days before triggering a rebuild. Your dev environment borrows production data through the clone strategy and doesn’t thrash on every upstream source change.

    Once you’ve run this for a week and have a feel for your actual skip rates, tune individual layers:

    models:

    +state:
lag_tolerance: "{{ '4h' if target.name == 'prod' else '7d' }}"
your_project:
staging:
+state:
lag_tolerance: "{{ '1h' if target.name == 'prod' else '3d' }}"
marts:
+state:
lag_tolerance: "{{ '2h' if target.name == 'prod' else '7d' }}"

    Staging models sit closer to raw sources that change frequently — tighter tolerance makes sense. Marts feed dashboards and executive reports — tighter tolerance there too, but not so tight that you’re rebuilding on every minor source refresh.

    Step 3: Set pre_clone for development environments

    pre_clone controls whether dbt State clones from production before running your model in a dev environment. This is where Snowflake’s zero-copy cloning makes dbt State genuinely fast — a clone of a 500GB fact table takes seconds and costs almost nothing in storage.

    models:
+state:
lag_tolerance: "{{ '4h' if target.name == 'prod' else '7d' }}"
pre_clone: "{{ 'always' if target.name in ['dev', 'ci'] else 'if_missing' }}"

    The options are:

    always — Clone from prod every time before running the model, even if a version already exists in your schema. Use this in dev when you want a fresh production baseline on every run.

    if_missing — Default. Clone from prod only if the table doesn’t exist in your schema yet. After the first clone, subsequent runs work on your existing version.

    never — Don’t clone. Build from scratch if the model needs to run. Rarely what you want.

    For CI jobs, always makes sense — each CI run should start from the current production state. For personal dev schemas, if_missing is usually fine after your initial setup.

    Step 4: Snowflake-specific config that pairs well with dbt State

    Layer your warehouse sizing, materialization, and lag_tolerance together. dbt State skip rate is highest on large mart tables where Snowflake cloning is fastest.

    A few Snowflake-specific configs pair directly with dbt State behavior.

    query_tag for tracking. dbt sets a query tag automatically on Snowflake sessions. Use it to track which models are being built vs skipped:

    # dbt_project.yml
models:
+query_tag: "dbt_{{ target.name }}_{{ model.name }}"

    Then query Snowflake’s QUERY_HISTORY to see your actual skip rate and time saved:

    SELECT
query_tag,
COUNT(*) AS query_count,
SUM(total_elapsed_time) / 1000 AS total_seconds,
SUM(credits_used_cloud_services) AS credits_used
FROM snowflake.account_usage.query_history
WHERE query_tag LIKE 'dbt_%'
AND start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP)
GROUP BY 1
ORDER BY credits_used DESC;

    This gives you a concrete before/after when you want to demonstrate ROI.

    transient vs permanent tables. By default, all dbt-created Snowflake tables are transient (no Fail-safe, 1-day time travel). Transient tables are slightly cheaper on storage. For models that dbt State frequently clones — large mart tables — transient is fine because you can always re-clone from production. For models you want full Snowflake time travel on, set transient: false explicitly:

    models:
your_project:
marts:
+transient: false # Keep full time travel on business-critical marts
staging:
+transient: true # Transient fine — replaceable by clone

    warehouse sizing per layer. dbt State reduces how many models actually run — which means you can tune warehouse sizes down without hurting throughput. If 40% of your models are being skipped, your MEDIUM warehouse is running at 60% utilization anyway. Consider dropping staging to XSMALL and only using MEDIUM for mart builds:

    models:
your_project:
staging:
+snowflake_warehouse: XSMALL
intermediate:
+snowflake_warehouse: SMALL
marts:
+snowflake_warehouse: MEDIUM

    Step 5: The incremental model gotcha you will hit

    The incremental model first-run gotcha: compiled SQL changes between run 1 and run 2. dbt State sees this as a logic change. Expect a full downstream rebuild on run 2. After that, behavior stabilizes.

    This one catches most teams in the first week. Here’s what happens.

    The first time an incremental model runs, dbt executes a full load — no WHERE clause, because there’s no existing table to filter against. The compiled SQL looks like:

    SELECT id, amount FROM raw_orders

    On the second run, is_incremental() becomes true. The compiled SQL now looks like:

    SELECT id, amount FROM raw_orders
WHERE id > (SELECT MAX(id) FROM fct_orders)

    dbt State sees this as a query logic change on fct_orders. It then marks every downstream model that depends on fct_orders as needing a rebuild — even if their own lag_tolerance hasn’t elapsed and their own code hasn’t changed.

    This is not a bug. It’s correct behavior — the incremental model’s SQL did change, semantically. But it means: the first two runs after enabling dbt State on an incremental model will look worse than steady-state. Run 1 is a full build. Run 2 triggers a full downstream rebuild. Run 3 onwards is where your skip rate actually stabilizes and starts saving you money.

    If you’re evaluating dbt State’s ROI, don’t measure it on day 1 or day 2. Give it a week of runs.

    Step 6: defer_to_target and environment setup

    By default, dbt State defers to your production environment. If your production environment isn’t literally named “prod” in your dbt platform setup, you’ll need to be explicit:

    # dbt_project.yml
models:
+state:
lag_tolerance: "{{ '4h' if target.name == 'prod' else '7d' }}"
defer_to_target: production # name of your prod environment in the platform

    For dbt Core users managing their own targets, this is set in profiles.yml:

    # profiles.yml
my_project:
outputs:
prod:
type: snowflake
defer_to_target: production
# ... rest of connection config

    In practice: make sure the environment you’re deferring to is actually up to date. If production last ran 48 hours ago and your dev environment is trying to clone from it, you’re cloning stale data. The lag_tolerance in dev being set to 7d means you’re fine with data that’s up to 7 days old in dev — that’s usually acceptable. Just be aware of the tradeoff.

    What good skip rates look like on Snowflake

    In the first two weeks after enabling dbt State on a mature Snowflake project, here’s what typical skip rates look like by layer:

    Staging models: 20–40% skip rate. These sit close to raw sources that change frequently. You won’t skip many — and that’s expected.

    Intermediate models: 40–65% skip rate. These transform staging output into business-ready structures. When staging is unchanged, intermediate skips cleanly.

    Mart models: 55–80% skip rate. These are the expensive ones — large aggregations, heavy joins, wide tables. This is where dbt State pays for itself. A skipped mart model on a MEDIUM Snowflake warehouse is 30–60 seconds of compute you’re not paying for, every run.

    If your overall skip rate is below 30%, your lag_tolerance is probably too tight — data is changing within the tolerance window and everything is rebuilding. Loosen it and see what happens. If your skip rate is above 80% in production, double-check that you’re not accidentally skipping models that should be rebuilding. Query QUERY_HISTORY and verify the freshness of your mart tables against your source freshness.

    Three things that will trip you up

    Volatile SQL functions force a rebuild. If any model uses CURRENT_TIMESTAMP(), RANDOM(), or UUID_STRING() in its SQL, dbt State treats the query as non-deterministic and won’t skip it — ever. These functions mean the compiled SQL could produce different results on every run, so skipping isn’t safe. Audit your models for volatile functions if your skip rate is suspiciously low.

    LAST_ALTERED in Snowflake is misleading. Snowflake updates the LAST_ALTERED timestamp on a table whenever any metadata operation occurs — not just when data changes. If you’re using LAST_ALTERED in any custom freshness logic, it will report tables as “changed” more often than they actually are, leading to unnecessary rebuilds. dbt State doesn’t use LAST_ALTERED internally (it tracks actual data freshness via source freshness checks), but if you have custom macros that do, fix them.

    Dynamic tables and dbt State don’t fully overlap yet. Snowflake’s dynamic tables refresh automatically based on target_lag. If you’re using the dynamic_table materialization in dbt, dbt State’s lag_tolerance config is separate from Snowflake’s target_lag. They’re two different systems tracking two different things. Don’t assume they’re in sync — they’re not. Manage them explicitly.

    The full dbt_project.yml starting point

    Here’s a production-ready starting config for a typical Snowflake dbt project with dbt State enabled:

    name: 'your_project'
version: '1.0.0'
profile: 'your_project'

models:
+query_tag: "dbt_{{ target.name }}"
+state:
lag_tolerance: "{{ '4h' if target.name == 'prod' else '7d' }}"
pre_clone: "{{ 'always' if target.name in ['dev', 'ci'] else 'if_missing' }}"

your_project:
staging:
+materialized: view
+snowflake_warehouse: XSMALL
+state:
lag_tolerance: "{{ '1h' if target.name == 'prod' else '3d' }}"

intermediate:
+materialized: table
+transient: true
+snowflake_warehouse: SMALL

marts:
+materialized: table
+transient: false
+snowflake_warehouse: MEDIUM
+state:
lag_tolerance: "{{ '2h' if target.name == 'prod' else '7d' }}"

    This gives you environment-aware lag tolerances, warehouse sizing tuned per layer, zero-copy clone behavior in dev and CI, and full Snowflake time travel on your business-critical mart tables.

    One principle

    dbt State doesn’t change what you build — it changes what you prove you don’t need to rebuild. The configs above aren’t magic. They’re your team’s explicit statement about how fresh each layer of your project needs to be, enforced automatically on every run. Get the lag tolerances right and the skip rate takes care of itself.

    Related reading: dbt State: The concept, the cost math, and when it pays off · Official dbt State setup docs · lag_tolerance config reference · dbt Fusion: 30x Faster Parsing · Snowflake Query Execution: What Really Happens

  • Stop Spinning Up Spark clusters for 50GB Datasets

    Stop Spinning Up Spark clusters for 50GB Datasets

    Your team has a 200GB Parquet file on S3. Someone suggests running the analysis in Spark. You spin up a four-node cluster, configure executors, tune shuffle partitions, wait three minutes for the cluster to initialize, wait fourteen minutes for the job to run, tear the cluster down, and get back a number.

    The same query in DuckDB runs on a single VM in four minutes, costs one-twentieth as much, and requires zero cluster management. You didn’t need distributed computing. You needed a fast query engine — and you reached for a freight train when a Ferrari would have done the job in a quarter of the time.

    This is the most expensive habit in modern data engineering, and it’s happening in thousands of production pipelines right now. Not because engineers are incompetent. Because the “big data playbook” — spin up Spark, process everything, shut down cluster — was written when cloud VMs had 8GB of RAM. A $300/month VM in 2026 has 128GB of RAM and NVMe SSDs that can sustain 3GB/s reads. The old rule — “data doesn’t fit in memory, use a cluster” — is eroding fast. And DuckDB is the reason.

    TL;DR

    → DuckDB is an embedded, in-process, columnar OLAP database. No server. No cluster. No JVM. Install in one `pip install duckdb`. Query CSV, Parquet, JSON on S3 with standard SQL.

    → For 50GB–1TB OLAP workloads on Parquet, DuckDB is typically 3–10x faster than Spark and 10–20x cheaper because it eliminates network shuffle, JVM overhead, and cluster management overhead.

    → Real benchmark: 500GB Parquet (stock trades, time-series aggregation + groupby). Spark on a 4-node cluster: 14 minutes. DuckDB on a single 16-core, 128GB VM: ~4 minutes. Cost ratio: 1:20.

    → DuckDB wins: SQL-first OLAP on Parquet/CSV/JSON, data that fits on one machine (up to ~1TB), CI/testing pipelines, local development, cost-sensitive workloads.

    → Spark still wins: petabyte-scale distributed ETL, Structured Streaming for real-time pipelines, MLlib integration, cross-node joins on truly massive datasets, fault tolerance across hundreds of nodes.

    → The practical hybrid: DuckDB for local dev and CI (zero startup time vs Spark’s 3-minute init); Spark for production TB+ workloads. Most teams using Spark everywhere could do 80% of their work on DuckDB.

    → Polars is in this conversation too: Rust-based DataFrame API, great for Python-first teams who don’t want SQL. DuckDB for SQL, Polars for code. They’re complementary, not competitive.

    → MotherDuck extends DuckDB to a managed cloud warehouse — multi-user, persistent storage, connectors — for teams that outgrow single-node but don’t want Spark’s complexity.

    What DuckDB actually is (and isn’t)

    DuckDB is an OLAP (analytical) database engine that runs inside your process. Not a server. Not a service. An embedded library, like SQLite, except built from scratch for analytical queries instead of transactional ones. You pip install duckdb and start querying. No cluster to manage. No JVM. No configuration files. No driver program. No shuffle partitions to tune.

    Under the hood, DuckDB uses vectorized execution: it processes data in columnar chunks, exploiting CPU SIMD instructions to handle hundreds of rows per clock cycle. It reads Parquet files with column pruning and predicate pushdown — it doesn’t load the whole file into memory, it skips the pages and row groups it doesn’t need. The result is query performance that competes with Spark on single-machine workloads at a fraction of the infrastructure cost.

    What DuckDB is not: a distributed system. It runs on one machine. If your data genuinely cannot fit on one machine or you need streaming, DuckDB is not your answer. But here’s the part of the conversation that’s rarely said clearly: most data engineering workloads in production are not distributed workloads. They’re workloads that teams are running on distributed infrastructure out of habit, convention, or because that’s what the senior engineer learned in 2019.

    The benchmark that changes how you think about this

    Real benchmark numbers: DuckDB eliminates cluster overhead, JVM serialization, and network shuffle. Wins by 3–10x on OLAP queries up to ~1TB. Cost difference is even larger than time difference.

    The numbers that matter come from a controlled test on a 500GB Parquet dataset of stock trade records: time-series aggregation with a multi-column groupby, the kind of query that sits at the core of most analytical pipelines.

    Spark on a 4-node cluster: 14 minutes end-to-end (including cluster init and tear-down overhead), at cluster-runtime node pricing. DuckDB on a single 16-core, 128GB RAM VM: ~4 minutes, no init overhead, running as a single process. Cost ratio: roughly 20:1 in DuckDB’s favor.

    Why does DuckDB win? Spark pays for distributed resilience even when you don’t need it. It shuffles data across network to prepare for cross-node joins that will never happen because the data fits on one machine. It serializes and deserializes through JVM objects. It manages a driver program and executor lifecycle. All of that overhead is real cost — not just money but latency. DuckDB simply reads columnar Parquet from local NVMe, pushes predicates down to skip file sections, and runs vectorized aggregation in CPU cache. No network. No JVM. No shuffle.

    For smaller queries: a grouped aggregation benchmark (sales by region on 10M rows) took DuckDB 2.5 seconds, Spark in local mode 8 seconds. A join on two 20M-row tables: DuckDB under 5 seconds, Spark 15 seconds. Important caveat: these are single-machine comparisons. At true petabyte scale, Spark’s distributed architecture wins because DuckDB simply runs out of hardware. But most teams never get to petabyte scale, and the ones who believe they have are often running 200GB datasets on Spark clusters because nobody revisited the architecture decision from three years ago.

    The cost math most teams never do

    Assume you have a 300GB daily analytics pipeline running on Spark. A modest cluster: 4 worker nodes, each 8 cores, 32GB RAM. You run it twice a day. On AWS, that’s roughly $0.30/node-hour, four nodes, maybe 45 minutes per run. That’s $0.90/run, $1.80/day, $657/year. Sounds manageable.

    Now add: the 15 minutes of Spark startup overhead per run ($0.30 wasted per run), the 20% of engineer time spent debugging shuffle OOM errors and executor failures, the CI runs that take 12 minutes instead of 2 because you’re testing against a Spark local context instead of DuckDB.

    The DuckDB alternative: a single c6i.4xlarge instance (16 cores, 32GB RAM), on-demand at $0.68/hour. Run it twice a day, average 8 minutes per run. That’s $0.18/day, $66/year. Plus near-zero maintenance overhead. For a 300GB pipeline, you’re looking at $591/year saved, plus meaningfully less engineer time.

    For larger teams running many such pipelines, multiply accordingly. The savings aren’t theoretical.

    Where DuckDB actually fits in your stack

    The decision is simpler than it looks: does your data fit on one machine? If yes, DuckDB is almost always the right choice. If not, Spark. The hard part is being honest about your actual data size.

    The practical split is cleaner than most discussions make it sound:

    Use DuckDB when: your data fits on one machine (roughly up to 1TB with modern hardware), the workload is SQL-first analytical queries, you’re building CI/testing pipelines (DuckDB starts in milliseconds; Spark in minutes), you’re doing local development and iteration, or you’re running cost-sensitive batch workloads where cluster overhead is pure waste.

    Use Spark when: your data physically cannot fit on one machine or needs distributed partitioning, you’re building streaming pipelines with Structured Streaming and need exactly-once semantics, you need MLlib for distributed model training, you have genuinely petabyte-scale joins that require cross-node shuffles, or you need fault tolerance across hundreds of nodes where a single node failure would be catastrophic.

    The hybrid that most teams are converging on: DuckDB in local development and CI (the “inner loop”), Spark in production for workloads that actually need distribution. This is the pattern Zach Wilson has written about: DuckDB for fast local testing and EDA, Spark for the production pipelines processing billions of events per hour. The tools aren’t competing for the same role — they’re occupying different rungs of the same ladder.

    DuckDB’s SQL is genuinely better to write

    Benchmark numbers aside, the developer experience gap is significant. DuckDB has shipped SQL extensions that most engineers discover and then can’t go back from.

    EXCLUDE lets you select all columns except a few: SELECT * EXCLUDE (internal_id, created_at) FROM orders. No more writing out 40 column names. COLUMNS with regex lets you pattern-match columns: SELECT COLUMNS('amount.*') FROM orders. QUALIFY filters on window function results without a subquery. Function chaining — first_name.lower().trim() — reads like Python. These aren’t gimmicks; they’re hours of saved typing at scale.

    DuckDB also queries files directly without loading them: SELECT * FROM 's3://my-bucket/data/*.parquet' WHERE event_date = '2026-06-01'. No ETL to load into a table first. No Spark session to initialize. The file is the table.

    The gotchas nobody warns you about

    DuckDB’s concurrency model is not Postgres. DuckDB supports multiple readers, but only one writer at a time. If you’re building a production system where multiple processes need to write simultaneously, you’ll hit locking issues quickly. MotherDuck solves some of this, but the base DuckDB model is single-writer. Don’t architect a high-write-concurrency system on raw DuckDB without understanding this.

    Memory is managed, but you can still OOM. DuckDB’s query engine is smart about memory, using streaming execution to avoid materializing entire result sets. But complex multi-join queries with many intermediate results can still consume more RAM than your VM has. Size your VM with headroom — if your dataset is 100GB, don’t run it on a 128GB instance. Leave 30–40% for overhead.

    DuckDB is not a transactional database. It has ACID transactions, but it’s optimized for append-heavy analytical workloads, not OLTP update/delete patterns. Using it as a general-purpose application database is the wrong tool for the job.

    Distributed DuckDB exists but isn’t production-ready at Spark scale. There’s a distributed extension project, but it’s nowhere near Spark’s maturity or fault tolerance. If you’re planning to “scale DuckDB to Spark scale” — that’s not the right mental model. When you outgrow single-node DuckDB, the answer is MotherDuck (managed, serverless) or Spark (distributed, self-managed). Not “distributed DuckDB.”

    The Polars question. If your team writes Python-first data pipelines, Polars is a serious alternative to DuckDB for single-machine workloads. Polars is a Rust-based DataFrame library — think pandas but 10–30x faster with a proper lazy execution model. It doesn’t support SQL natively (though it has SQL-like expressions). The practical split: DuckDB for SQL-first analytical queries; Polars for code-first transformations. Many teams use both: DuckDB to query and load Parquet, Polars to transform the resulting DataFrame. They compose cleanly together.

    When to migrate existing Spark pipelines

    Migrating an existing Spark pipeline to DuckDB isn’t always worth the effort even if DuckDB would be faster. Before migrating, ask three questions:

    Is the Spark pipeline causing operational pain (OOM errors, long startup times, expensive debugging)? Is the dataset under 1TB and not expected to grow past single-node capacity? Does the pipeline use only Spark SQL or DataFrame operations, not Spark-specific features like Structured Streaming or MLlib?

    If all three are yes, the migration is usually a morning’s work: translate PySpark DataFrames to DuckDB SQL, replace S3 Spark readers with DuckDB S3 file queries, run both in parallel for one week, decommission the cluster. Most SQL-based Spark pipelines translate directly because DuckDB’s SQL is a superset of what most teams actually use in Spark SQL.

    If any answer is no, keep Spark for that pipeline and use DuckDB for new workloads below the threshold.

    The one principle

    Match the tool to the actual data size, not the data size you imagine you might have someday. Spark is the right answer for distributed workloads that genuinely cannot fit on one machine. It is not the right answer for a 200GB daily pipeline just because someone wrote the original architecture when “big data” was the thing to say. In 2026, a single cloud VM has enough RAM, CPU, and NVMe storage to handle most analytical pipelines that companies think require distributed computing. DuckDB is the proof of that claim.

    Related reading: DuckDB S3 extension docs · MotherDuck: Managed DuckDB in the cloud · Snowflake Iceberg v3: When to Migrate · dbt Fusion: 30x Faster Parsing · Snowflake Query Execution: What Really Happens

  • Snowflake Iceberg V3: When to Actually Migrate(vs Native Tables)

    Snowflake Iceberg V3: When to Actually Migrate(vs Native Tables)

    Most data engineers I talk to still store everything in Snowflake native format. It’s simple: load data, query data, done. But here’s what nobody’s talking about: if you’re querying that data from anywhere else — Spark, Databricks, even just a local Python script — you’re paying a hidden “data tax.” Redundant storage, egress fees, ETL pipeline complexity. For Fortune 500 companies, that tax runs $2 million to $7 million a year. And Snowflake’s new Apache Iceberg v3 support (GA May 2026) actually changes the math. But migrating is a choice, not a reflex — and there are specific gotchas that’ll bite you if you don’t plan right.

    The honest decision tree: migrate if you’re paying egress fees or running multi-engine queries.

    TL;DR

    → Apache Iceberg v3 is GA on Snowflake (May 2026). New features: deletion vectors (10x faster DML), row lineage for CDC, VARIANT type for semi-structured data, nanosecond timestamps, default column values.

    → Month-to-month costs are roughly equal to native Snowflake tables (compute identical, storage ~$23/TB native vs ~$0.023/GB S3, negligible difference).

    → Migrate if: (a) you query from Spark/Databricks (egress fees kill you), (b) you’re paying >$500/month for Snowflake storage, (c) you want single copy of truth across multiple engines.

    → Don’t migrate if: you only query from Snowflake, storage bill is small, and you’re not building a multi-engine architecture.

    → New gotcha: You can’t upgrade v2 tables in-place to v3. No writing to v3 tables via external engines (Spark) yet. External engine compaction gets billed starting May 21, 2026.

    → Real win: Snowflake Storage for Iceberg (GA April 2026) means you don’t manage S3 buckets. Snowflake handles it, with Fail-safe recovery built in.

    → The “data tax” of $2M–$7M annually on Fortune 500 costs more than Iceberg migration ever will.

    The mental model that’s keeping you locked in

    Here’s the picture most teams hold: Snowflake stores data. We query it in Snowflake. Done. Native tables, simple syntax, life is easy. And if you only query in Snowflake, that model works fine. You get the speed, the simplicity, the integration with dbt, the Time Travel.

    But the moment you have data living in two systems — Snowflake for reporting, Spark for ML training, Databricks for a BI tool, even just a DuckDB instance on your laptop — you’ve broken the simple model. Now you have two copies of the data, or worse, a pipeline that’s constantly syncing between them. You’re paying Snowflake egress fees to get data out ($0.02 per GB across regions, $0.08 between clouds). You’re rebuilding the same transformation logic in both systems. You’re managing schema evolution in two places. The complexity compounds.

    Iceberg was built to solve exactly this. One copy of the data, on open cloud storage (S3, Azure Blob, GCS), readable by any engine that supports the Iceberg format. Snowflake, Spark, Databricks, Trino, DuckDB. All of them see the same table, the same schema, the same snapshot. No replication, no egress fees, no syncing.

    But Iceberg isn’t free. It trades simplicity for flexibility. And for teams that genuinely don’t need that flexibility, native tables are still the right call.

    The hidden cost of locking data into proprietary formats. For large teams, it’s massive.

    What changed in Iceberg v3, and why it matters

    Iceberg v2 shipped in 2023 and covered the basics: open format, ACID transactions, schema evolution, snapshots. v3 (released June 2025, GA on Snowflake May 7, 2026) added seven new capabilities. Only three actually change how you’d use it.

    Deletion vectors. In v2, if you deleted or updated a row, Iceberg had to rewrite the entire data file (copy-on-write). Slow and expensive. v3 adds deletion vectors — a separate, small metadata file that marks rows as deleted without touching the original data. Result: 10x faster DML operations on large tables. If you’re doing frequent small updates (common in streaming ingestion), v3 matters.

    Row lineage. v3 tracks which rows were inserted, updated, or deleted with metadata fields (_row_id, _last_updated_sequence_number). This is how Snowflake implements change data capture (CDC) without external tooling. A Dynamic Iceberg Table can now refresh incrementally on only the rows that changed, not the whole partition. Critical for SCD2 and CDC pipelines.

    VARIANT type. v2 forced you to choose: store JSON as a string (slow parsing at query time) or explode it into a wide schema (thousands of nullable columns, query disasters). v3 adds native VARIANT support, and Snowflake automatically shreds it (extracts nested fields and indexes them) at write time. Query performance on semi-structured data jumps dramatically. This alone is why observability platforms are betting on Iceberg.

    The other four (default column values, geometry/geography types, nanosecond timestamps, partition transform improvements) are niche. Don’t worry about them unless you hit them.

    The cost math: Native vs Iceberg in real dollars

    Let’s be honest: most articles skip the cost comparison and jump to “Iceberg is cheaper!” It usually isn’t, month-to-month. Here’s why.

    Two-column cost breakdown. Native Snowflake: 2,000 credits at $3 = $6,000 compute, $23/TB storage = $230, total $6,230/month. Iceberg (Snowflake managed): same $6,000 compute, S3 at $0.023/GB = $235 storage, bundled compaction = $0, total $6,235/month. Verdict: same cost, but Iceberg enables multi-engine and zero egress.

    The real numbers. On a month-to-month basis, they’re nearly identical. The wins come from elsewhere.

    For a typical 10 TB table with 1,000 queries per month (small-to-medium workload):

    Native Snowflake: Compute 2,000 credits ($6,000) + Snowflake storage 10TB at $23/TB ($230) = $6,230/month.

    Iceberg (Snowflake-managed storage, GA April 2026): Compute 2,000 credits ($6,000) + S3 storage 10TB (10,240 GB × $0.023/GB = $235) + compaction bundled ($0) = $6,235/month.

    Basically the same. Where Iceberg wins is not in monthly costs. It wins in:

    Egress fees. If you query that 10 TB table from a Databricks cluster once a month, native Snowflake costs 10,000 GB × $0.08/GB (cross-cloud egress) = $800. Iceberg: $0. Over a year, that’s $9,600. At any real-world scale (multi-engine queries), egress dominates.

    No data duplication. If you’re currently syncing data between Snowflake and Databricks (ETL pipeline, manual export, Fivetran), that pipeline costs money too. Shared Iceberg table means you stop paying to move the data. One table, multiple readers.

    Storage simplicity. With Snowflake Storage for Iceberg (new, April 2026), you don’t manage S3 buckets yourself. Snowflake handles encryption, replication, Fail-safe recovery. You save the operational tax of bucket management, lifecycle policies, and debugging storage issues.

    So here’s the honest scorecard:

    For Snowflake-only users: Native tables win. Simpler, no migration pain, costs are identical.

    For multi-engine shops (Snowflake + Spark + Databricks): Iceberg wins. Egress fees alone justify the migration, and you get single source of truth as a bonus.

    The gotchas that will hurt your migration

    You can’t upgrade v2 tables to v3 in-place. There’s no ALTER TABLE ... SET ICEBERG_VERSION = 3. To get v3, you have to CREATE a new table. That means copying data (compute cost, time), repointing your queries, and hoping nothing breaks downstream. On large tables, this is a multi-day operation.

    External engines can’t write v3 tables yet. You can read v3 tables from Spark, Trino, DuckDB, all day. But writing is blocked. Snowflake says it’s “planned,” but if you’re building a shared Iceberg table that Spark needs to update, you’re stuck on v2. This is a major limitation if you’re counting on true multi-engine write access.

    Compaction gets billed starting May 21, 2026. When an external engine writes to an Iceberg table (via Spark, Trino, etc.), it creates small data files. Snowflake’s compaction automatically consolidates them into bigger files for query performance. Until May 21, that was free. Now it costs credits. Budget for ongoing compaction maintenance if you have heavy external write workloads.

    ⚠️ Don’t convert cloned tables with vended credentials. If you clone a native Snowflake table and then convert it to Iceberg, you can’t write to it with vended credentials (external query engine creds). You’d have to connect the external engine directly to your S3 bucket, defeating the whole point. Create the Iceberg table fresh if you’re using vended creds.

    Schema changes are cheap but metadata bloat is real. Iceberg tracks every schema change as a separate metadata version. On tables with thousands of ALTER COLUMN operations, metadata can get unwieldy. Compact your metadata regularly with CALL SYSTEM$OPTIMIZE(...).

    The mistakes teams make when migrating

    1. Migrating for the wrong reason. “Everyone’s talking about Iceberg, so we should move.” Wrong. Migrate only if you have a concrete use case: egress fees, multi-engine queries, or storage cost >$500/month. Otherwise you’re trading simplicity for nothing.

    2. Not testing external engine read performance first. Iceberg’s query performance depends heavily on your cloud setup, partitioning strategy, and how many small files are sitting around. Test Spark/Databricks queries on a small Iceberg table before migrating your 100 TB production table. You might find that your workload is slower on Iceberg, not faster.

    3. Assuming v3 is backward-compatible with v2. It’s not. Engines that only understand v2 (like older Spark runtimes, Trino versions) will fail on v3 tables. Check that every tool in your stack supports v3 *before* upgrading. v2 → v3 is one-way; there’s no downgrade.

    4. Ignoring the partition evolution story. Iceberg lets you change your partitioning scheme without rewriting the whole table. It’s a huge feature, but it’s also easy to mess up. Bad partitioning (e.g., partitioning by a column with 10 million distinct values) creates a partition explosion. Get your partitioning right before you migrate, not after.

    5. Migrating everything at once. Pick one critical table, migrate it, test multi-engine queries for a month, then move the rest. Iceberg is mature enough for production, but it’s not old enough that every edge case is documented. Be intentional.

    When to actually migrate: The real decision

    Stop and ask yourself: Do you actually need Iceberg?

    Yes, if: You query the same data from Snowflake and Spark/Databricks. You’re paying egress fees. You have data warehouses in multiple clouds and want to query across them. You’re building a data lakehouse and want to ditch proprietary formats.

    No, if: You only query from Snowflake. Your storage bill is <$500/month. You’re using Snowflake’s Time Travel, zero-copy clones, and other native features heavily. You don’t need to share data with other engines.

    For most teams, the answer is no. And that’s okay. Native Snowflake tables are extremely good. Simple, fast, well-integrated with dbt. There’s no shame in staying native.

    But for teams hitting the “data tax” — redundant copies, egress fees, multi-engine complexity — Iceberg v3 actually delivers. The gotchas are real, but they’re manageable. The cost savings are modest month-to-month, but the flexibility is transformative.

    The one principle that matters

    Interoperability beats simplicity when you’re already paying for fragmentation. If your current architecture already costs you $800/month in egress, $300/month in ETL pipelines, and engineering time chasing sync issues, Iceberg’s “complexity” is actually a simplification. You’re not adding complexity; you’re replacing it with a standard.

    If you’re simple and integrated today, stay there. Don’t pay the cost of flexibility you don’t need. But if you’re paying the data tax, Iceberg’s math changes fast.

    Related reading: Snowflake Apache Iceberg tables (official docs) · Snowflake Time Travel: The Real Architecture · Snowflake Optima: 15x Faster Queries at Zero Cost · Query Snowflake in DuckDB and Cut Costs