app.dataengineerhub.blog

Tag: data-engineering

Why LLMs give different answers to the same question

The bug report said: “The model is broken. It gives a different answer every time I ask the same question.” I’ve gotten some version of this from three different engineers now, and each time the fix is the same — not a code change, but a change in how they think about what a language model actually is. Because the model isn’t broken. It’s doing exactly what it was built to do. The expectation is what’s broken.

Traditional software is a vending machine: press B4, get the same chips every time. Same input, same output, forever. That determinism is so deeply baked into how engineers think that when an LLM returns “Sure, here’s an email…” one moment and “I’d be happy to help you draft that…” the next — same prompt, same model, same settings — it feels like a defect. It isn’t. A language model doesn’t retrieve answers. It rolls them, one token at a time, from a set of loaded dice it learned during training. This is the guide to why that happens, how to control it, and the surprising truth that you can’t fully turn it off.

TL;DR

→ LLMs don’t store answers — they predict the next token as a probability distribution over the whole vocabulary, then sample one token from it, append it, and repeat. Different samples → different answers.

→ At each step the model outputs raw scores (logits) for every possible token. Softmax turns those into probabilities. A decoder picks one. That pick is where variation enters.

→ Temperature reshapes the probability distribution before sampling. Low temperature (→0) sharpens it toward the single most likely token (predictable, repetitive). High temperature (0.8–1.2) flattens it (diverse, creative, riskier).

→ top_p (nucleus sampling) and top_k limit which tokens are even eligible — they cut the long tail of unlikely tokens so the model can’t wander into nonsense.

→ The counterintuitive part: even at temperature 0 (greedy decoding), you are not guaranteed identical output. Floating-point rounding and how the server batches your request with others introduce tiny variations that can cascade into different tokens.

→ This is a feature, not a bug. If the model always picked the single highest-probability token, every answer would collapse into the same bland, repetitive text. Sampling is what gives it range.

→ To maximize reproducibility: pin a dated model version (not “latest”), set temperature 0 and top_p 1, use a seed if the API offers one, and design your tests to accept semantic equivalence — not byte-for-byte matches.

The core idea: the model predicts, it doesn’t retrieve

Here’s the mental shift that fixes the “it’s broken” reaction. When you ask an LLM a question, it does not look up a stored answer. Before it writes a single word, it scores every token in its vocabulary — tens of thousands of possible next pieces of text — by how well each would continue what’s been written so far. Those raw scores are called logits. They’re just the model’s unnormalized confidence in each candidate token.

Then a function called softmax converts those scores into a proper probability distribution: numbers between 0 and 1 that sum to 1. Maybe “Sure” gets 18%, “I’d” gets 15%, “Happy” gets 9%, and a long tail of thousands of other tokens splits the rest. A decoder then samples one token from that distribution, appends it to the context, and the whole loop runs again for the next token. And the next. Hundreds of times.

The key realization: for almost any interesting prompt, there is no single correct next token. There are thousands of valid continuations. An email can open with a greeting, a question, a bold statement, an apology — all reasonable. The model has learned that they’re all plausible, and it assigns each a probability. When it samples, it might pick “Sure” this time and “I’d” the next. From that one different first token, the entire rest of the response can diverge. That’s not the model malfunctioning. That’s the model exploring the space of good answers.

Temperature: the dial that reshapes the dice

Temperature doesn’t add randomness — it reshapes the probability distribution the model samples from. Low = sharp spike, one clear winner. High = flattened, many contenders.

People call temperature “the creativity slider.” That’s directionally right but explains nothing about what’s actually happening. Mechanically, temperature is a number the logits are divided by before softmax converts them to probabilities.

Divide by a small number (temperature near 0) and the differences between logits get exaggerated. The most likely token’s probability balloons toward 100% and everything else shrinks toward 0. The distribution becomes a single tall spike. The model has almost no choice but to pick that top token every time — predictable, consistent, and at the extreme, repetitive and a little robotic.

Divide by a larger number (temperature around 1) and the differences compress. The gap between the top token and the runners-up narrows, so more tokens become live options. The distribution flattens. Now the model genuinely might pick the second or fifth most likely token, which is where variety, surprise, and “creativity” come from — along with a higher chance of an odd or wrong choice.

My rule of thumb from production use: temperature 0–0.3 for anything where correctness and consistency matter (classification, extraction, structured output, factual Q&A). 0.7–0.9 for drafting, brainstorming, and copy where you want variety. Above 1.0 only when you’re deliberately chasing unusual output and can tolerate the misfires.

top_p and top_k: fencing off the nonsense

Temperature reshapes the whole distribution, but two other levers control which tokens are even allowed into the drawing.

top_k is the blunt version: keep only the k most likely tokens, discard the rest, sample from what’s left. top_k = 40 means “only ever consider the 40 best options.” It stops the model from occasionally grabbing a bizarre low-probability token from the tail.

top_p (nucleus sampling) is smarter and more common. Instead of a fixed count, it keeps the smallest set of top tokens whose probabilities add up to p. top_p = 0.9 means “keep adding tokens from most-likely down until we’ve accounted for 90% of the probability mass, then sample only from that set.” When the model is confident (one token has most of the mass), the nucleus is tiny. When it’s uncertain (mass spread over many tokens), the nucleus is larger. It adapts to how sure the model is.

In practice you usually tune temperature or top_p, not both aggressively. A common safe setting for consistent output is temperature 0 with top_p 1; a common creative setting is temperature 0.8 with top_p 0.9.

The part that surprises even experienced engineers

Temperature 0 gets you close to deterministic, not all the way. Floating-point rounding and server-side batching introduce tiny variations that can tip a near-tie to a different token.

Here’s the thing almost everyone gets wrong, including people who’ve shipped LLM features: setting temperature to 0 does not guarantee you’ll get the same answer twice. Temperature 0 means greedy decoding — always take the single highest-probability token — so in theory there’s only one path. In practice, reproducibility still breaks, and it’s worth understanding why because it will bite you during evaluation and debugging.

The first reason is floating-point arithmetic. A forward pass through a large model is billions of arithmetic operations, and computers represent numbers with finite precision. Tiny rounding errors accumulate. When the top two candidate tokens are nearly tied — say 42.7% versus 42.6% — a rounding difference of a hair can flip which one “wins” the greedy pick. That one flipped token cascades: the next context is now different, so the whole rest of the output can diverge.

The second reason is subtler and more modern: batch variance. When you send a prompt to a hosted model, the server doesn’t process it alone — it batches your request with other users’ requests for GPU efficiency. The exact composition of that batch changes the order and grouping of the underlying matrix operations, and because floating-point addition isn’t perfectly associative (a + b + c can differ slightly from c + b + a at the bit level), the logits come out microscopically different depending on who else you were batched with. You changed nothing; the server’s batching did.

How bad is it? One 2026 benchmark sent the same prompt ten times at temperature 0 across several models and measured byte-for-byte identical responses. On an open-ended prompt, results ranged from around 70% identical on one model down to essentially 0% on others — the longer and more open-ended the prompt, the faster determinism collapsed. Another team ran a single prompt a thousand times at temperature 0 and got around 80 distinct outputs. It’s fixable with special deterministic inference kernels, but that’s an infrastructure choice most hosted APIs don’t make by default because it’s slower.

Why this is a feature, not a bug

It’s tempting to see all this as a flaw to be stamped out. But step back: if a model always emitted the single most probable token, it would be nearly useless for most of what we use it for. Ask it to write three taglines and you’d get the same one three times. Ask for brainstorming and it would give one rigid answer. The probabilistic sampling is precisely what lets a model produce a greeting one way and a different, equally-good way the next — what makes it feel like it has range instead of a single canned response per prompt.

The variation isn’t randomness in the “anything goes” sense. It’s controlled exploration of a space of good answers, bounded by the probabilities the model learned. Turn the temperature down when you need the boundaries tight; turn it up when you want the model to roam. The dial is the point.

The gotchas nobody warns you about

“Same answer” is the wrong test. If your evaluation checks whether the model returns byte-identical output across runs, it will fail for reasons that have nothing to do with quality. Test for semantic equivalence — does the answer mean the same thing, contain the same facts, pass the same downstream parse — not exact string match.

Structured output is where variation actually hurts. A human reading two differently-worded but equivalent answers doesn’t care. A downstream system parsing the model’s output with a regex absolutely does. If run one returns {"status": "approved"} and run two returns The status is approved., your parser breaks. This is why low temperature plus a strict output schema (or structured-output / JSON mode) matters so much for anything programmatic.

“latest” is a moving target. If you pin your app to a model alias like “latest” or an undated name, the provider can update the underlying model and your outputs shift overnight — a different kind of non-determinism entirely, at the version level. Pin a specific dated model identifier so you control when the model changes.

Reasoning models add a hidden layer. Models with extended thinking generate a hidden chain-of-thought before the final answer. That internal reasoning is itself sampled, so even more variation can accumulate before you see the first visible token. Same principles, more surface area.

How to get the most reproducible output you can

You can’t make a hosted LLM perfectly deterministic, but you can get close enough for most needs. Pin a specific dated model version rather than a moving alias. Set temperature to 0 and top_p to 1. Use the API’s seed parameter if it offers one, and record any response fingerprint the provider returns so you can tell when the underlying system changed. For self-hosted models, pin the inference engine version, the numeric precision (bf16 vs fp16), and the batch settings — and if you truly need bit-for-bit reproducibility (for audits, evaluation, or RL training), look into the batch-invariant deterministic kernels that some inference stacks now support, accepting that they run a bit slower.

Then, most importantly, build your evaluation to tolerate the residual variation. Assert on meaning, structure, and facts — not on exact wording. The teams that fight non-determinism with string equality lose; the teams that design around semantic equivalence ship.

The one principle

A language model is a probability engine, not a lookup table. Different answers to the same prompt aren’t a malfunction — they’re the visible result of sampling from a distribution of good continuations. Control the spread with temperature and top_p, pin your versions, and test for meaning rather than exact text. Once you stop expecting a vending machine and start treating it like a set of well-trained dice, almost everything about its behavior makes sense.

Related reading: Why AI Agents Forget: Memory Architecture in AI Agents · Why Your RAG Pipeline Is Failing Silently · OpenAI API: temperature, top_p, and seed parameters · Anthropic Claude API reference

July 9, 2026
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
July 8, 2026
Why your RAG Pipeline Fails ( and how to fix it in Production )
You built a RAG pipeline. You retrieved relevant documents. You fed them to Claude. You got back a confident, well-structured answer. The answer sounds great. It cites sources. It reads like an expert wrote it.

It’s grounded in the wrong documents.

This is how most RAG systems fail in production, and it’s not because the LLM is broken. It’s because the retrieval stage — the R in RAG — silently returns the wrong documents 40-73% of the time, and by the time you notice, the AI has already confidently answered 10,000 queries wrong.

The painful lesson the industry learned in 2026: the bottleneck in RAG is not generation. It’s retrieval. And naive RAG — dump documents in a vector database, embed them, retrieve by cosine similarity — handles maybe 60% of real-world queries correctly. The other 40% fail because semantic similarity and actual relevance are not the same thing.

This is the guide that fixes it. Real production patterns. Real numbers. Real failure modes and how to detect them before they cost you.

TL;DR

→ Naive RAG (vector-only retrieval) fails ~40% of the time. The retrieval stage, not generation, is the bottleneck. Retrieval failure = confident wrong answer (hallucination wearing a tuxedo).

→ The 2026 production pattern: hybrid search (semantic + keyword BM25) + reranking (cross-encoder model) + semantic chunking. This combination catches the 40% that naive RAG misses and costs ~$0.005 per query instead of $0.001.

→ Semantic chunking (split on sentence similarity boundaries, not character counts) is where 60% of RAG pipelines silently fail. Fixed chunking creates orphaned context and broken topic boundaries.

→ Agentic RAG: multi-step retrieval where the agent decides what to search for, when to go deeper, and whether to trust the retrieved docs. Costs $0.02-0.10 per query but handles complex, multi-hop questions.

→ GraphRAG: structure your knowledge base as a graph (entities, relationships) instead of flat documents. Accuracy improves 2-5x on complex reasoning questions. Still experimental for most teams but emerging as the 2026 winner.

→ Evaluate RAG quality with RAGAS (Retrieval-Augmented Generation Assessment) framework. Measures: faithfulness (is the answer grounded?), answer relevance (does it answer the question?), context relevance (did retrieval find good docs?). Don’t ship without these metrics.

→ Most RAG failures are invisible until someone compares the AI’s answer against the truth. You need continuous evaluation + human-in-the-loop feedback loops. “It works!” is how failures hide.

Why naive RAG fails, and what failure looks like

A naive RAG system goes like this: user asks a question → embed the question → search vector database for similar documents → return top-K (usually top-5) → feed to LLM → LLM generates answer.

The problem lives in step 3. Embedding similarity captures surface-level meaning, but not always the meaning you actually need. Example: a user asks “what’s our return policy for damaged goods?” The system retrieves documents about product damage assessment, shipping damage claims, and warranty coverage. All are semantically similar. None explicitly answer “what do we do when someone returns damaged goods?” The LLM reads the retrieved docs, finds no explicit answer, hallucinates a plausible-sounding policy, and returns it with confidence.

The user doesn’t know it’s made up because the answer is well-written, cites sources, and sounds authoritative. The LLM didn’t lie. The retrieval system retrieved low-relevance documents, and the LLM did its job: generate something coherent from what it was given.

Naive RAG fails silently. The failure is invisible until someone actually checks if the answer is true.

This is why in 2026, the best RAG systems run retrieval as a multi-stage pipeline: keyword search for precision, semantic search for recall, then ranking to bubble the actually-most-relevant doc to the top. It’s more expensive per query ($0.005 vs $0.001) but catches 35-40% more edge cases.

The production RAG stack that actually works

Stage 1: Chunking (semantic, not fixed-size)

The first mistake 80% of teams make is chunking on character boundaries. “Split every 512 characters. Overlap by 64 characters.” This creates orphaned context: a chunk that starts mid-sentence, another that ends mid-concept. When the LLM reads it, critical context is missing.

Semantic chunking detects topic boundaries by tracking embedding similarity between consecutive sentences. When the similarity drops below a threshold (typically 0.6–0.7 cosine similarity), a new chunk begins. This keeps related information together and avoids splitting mid-concept.
```
def semantic_chunk(sentences, threshold=0.65):
chunks = []
current_chunk = [sentences[0]]
for i in range(1, len(sentences)):
prev_embedding = embed(sentences[i-1])
curr_embedding = embed(sentences[i])
similarity = cosine_similarity(prev_embedding, curr_embedding)
if similarity < threshold:
chunks.append(' '.join(current_chunk))
current_chunk = [sentences[i]]
else:
current_chunk.append(sentences[i])
chunks.append(' '.join(current_chunk))
return chunks
```
Semantic chunking increases embedding quality and reduces “lost in the middle” failures where the right document exists but is buried under irrelevant chunks.

Stage 2: Hybrid Search (semantic + BM25)

Embed your chunks into a vector database, but also index them for keyword search (BM25). When a query arrives:

1. Semantic search: return top-10 by embedding similarity
2. Keyword search: return top-10 by BM25 relevance
3. Merge: combine, deduplicate, keep top-15

Keyword search catches queries where exact terms matter. Semantic search catches paraphrasing and conceptual matches. Together they cover ~95% of real queries. Individually, they cover ~60%.

Stage 3: Reranking (cross-encoder model)

Feed your merged top-15 documents into a reranking model (e.g., Cohere’s reranker, BGE-reranker, or `rank-bge-reranker-base`). The reranker is a cross-encoder that scores each (query, document) pair with a relevance score, not just an embedding similarity.
```
# Pseudo-code
merged_docs = hybrid_search(query)
scores = reranker.score([(query, doc) for doc in merged_docs])
top_5 = sorted(zip(merged_docs, scores), key=lambda x: x[1], reverse=True)[:5]
```
Reranking is expensive (slower than vector search, more compute) but critical for quality. It catches the 35-40% of edge cases where semantic and keyword search both agree the document is relevant, but it actually isn’t.

Stage 4: Prompt Engineering (context window management)

Feed your reranked top-5 documents into the LLM with a structured prompt:

You are a helpful assistant. Answer the user's question using ONLY the documents below. If the documents don't contain the answer, say "I don't have that information."
```
Documents:
{document_1}
{document_2}
...
{document_5}

Question: {query}
Answer:

The key: explicitly tell the model to refuse if the documents don't support the answer. This stops hallucinations when retrieval fails. (It doesn't always work, but it reduces hallucination by 30-40%.)
```
The cost math: naive vs hybrid vs agentic

he tradeoff: naive is cheap and wrong. Agentic is expensive and right. Hybrid is the sweet spot for most production systems.

Assume you’re answering 100,000 customer questions per month.

Naive RAG
Vector embedding: $0.0001 per query
Vector search: $0.0005 per query
LLM generation: $0.0003 per query
Total: ~$0.001 per query = $100/month
Expected retrieval quality: 60%

Hybrid + Rerank
Vector embedding + BM25: $0.001 per query
Reranking (cross-encoder): $0.003 per query
LLM generation: $0.0003 per query
Total: ~$0.005 per query = $500/month
Expected retrieval quality: 95%

Agentic RAG
Multi-step retrieval + ranking: $0.01–0.05 per query
LLM generation (longer context, more calls): $0.01–0.05 per query
Total: ~$0.02–0.10 per query = $2,000–10,000/month
Expected retrieval quality: 98%+ (handles multi-hop, complex reasoning)

For most teams, hybrid + rerank is the financial sweet spot. It catches 35% more edge cases than naive RAG at 5x the cost — and one wrong answer to a customer can cost more than the 35% savings.

Detecting RAG failure before it costs you

The insidious part of RAG failure is invisibility. You need evaluation metrics.

Use RAGAS (Retrieval-Augmented Generation Assessment Score) — a framework that measures:

Context Relevance: Did retrieval actually find relevant documents? Measured as: what percentage of the retrieved docs are actually relevant to the query. Threshold: >0.7.

Faithfulness: Is the LLM’s answer grounded in the retrieved documents, or did it hallucinate? Measured as: what percentage of the generated answer can be verified against the source documents. Threshold: >0.8.

Answer Relevance: Does the answer actually answer the question? Measured as semantic similarity between the answer and the question. Threshold: >0.7.

A RAG system with context relevance 0.4, faithfulness 0.6, and answer relevance 0.5 is broken and needs intervention. Hybrid search is the first lever. Reranking is the second. If you’re still failing after that, you likely have a chunking problem.

Continuous evaluation: Pick a sample of your queries (100+ per week). Have humans label whether the AI’s answer was correct. Track: what % of failures are retrieval failures vs generation failures? This feedback loop is how you discover your RAG system is failing before it affects thousands of users.

The gotchas that wreck RAG in production

Stale embeddings. Your vector database was built two months ago. Your source documents were updated yesterday. The embeddings don’t reflect the new content. Result: the system retrieves documents that existed when you built the index but whose meaning has shifted. Regenerate embeddings on a schedule — daily for fast-moving docs, weekly for stable docs.

Metadata loss. You chunk documents into 100 pieces. You embed and index them. Later, when you retrieve a chunk, do you know which document it came from? When it was written? Who wrote it? If not, you’ve lost the context metadata that makes retrieval useful. Always store (and retrieve) chunk metadata: source, date, author, doc type.

Long context window false confidence. Million-token context windows make it tempting to dump everything into the prompt and let the LLM find the answer. This works until it doesn’t — “lost in the middle” failures happen at massive scale with massive contexts. Reranking is even more important with long contexts, not less.

Retrieval-generation misalignment. Your retriever optimizes for “documents similar to the query.” Your generator optimizes for “coherent answer.” These aren’t the same. A document can be similar to the query and still not directly answer it. Reranking helps, but you also need to tune your retriever prompt to emphasize direct answers, not just conceptual relevance.

The one principle

Naive RAG fails invisibly. Hybrid + rerank + evaluation is the default production pattern in 2026. Agentic RAG is the endgame, but start with hybrid because it’s cheaper and 95% retrieves correctly. The difference between a RAG system that confidently hallucinates and one that retrieves correctly is usually three decisions: semantic chunking, hybrid search, reranking. Make those three moves, and most of your silent failures disappear.

Related reading: Why AI Agents Forget: Memory Architecture in AI Agents · RAGAS: Retrieval-Augmented Generation Assessment Score · LangChain Retrievers Documentation · BGE Reranker Model (Hugging Face) · Data Contracts: Stop Schema Breakage Before It Happens
July 7, 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
July 4, 2026
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
July 4, 2026
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

July 3, 2026
Someone renamed a column.Your Pipeline Died.Here’s the fix
Someone on the backend team renamed order_total to order_amount. Clean name. Makes total sense for their domain model. They shipped it on a Thursday afternoon. By Friday morning, your revenue dashboard was showing zero. Not wrong numbers. Zero. Because your Snowflake pipeline was still selecting order_total from the events table, and the column simply wasn’t there anymore.

You found out from a Slack message. From a director. At 9 AM.

This is the most common production incident in data engineering in 2026, and it’s almost never caused by bad code. It’s caused by the absence of a formal agreement between the team producing data and the team consuming it. That agreement has a name: a data contract. And most data teams still don’t have one.

The excuse is usually some version of “we move too fast.” The reality is that the teams who move fastest are the ones with contracts, because they stop discovering breaking changes from directors on Friday mornings and start catching them in CI on Thursday afternoons, before anything ships.

TL;DR

→ A data contract is a formal specification — schema, semantics, SLAs, ownership — between a data producer and its consumers. Not documentation. Enforcement.

→ Most data incidents don’t start with missing data or broken code. They start with a well-intentioned upstream change that silently invalidated an assumption someone downstream was relying on.

→ Contracts have three parts: schema (structure and types), semantics (what fields actually mean), and SLAs (freshness, completeness, availability). Schema-only contracts miss most real breakages.

→ The dual-write pattern is the only safe migration path for breaking changes: keep old field + add new field → both populated during transition → deprecation notice with a hard date → removal at v2. Each phase takes at minimum 30 days. Skipping phases causes incidents.

→ 90 days minimum notice for breaking changes. Data pipelines have long release cycles; consumers need time to update downstream logic, tests, and dashboards.

→ A contract not enforced in CI is just documentation. The ODCS (Open Data Contract Standard) YAML spec plus `datacontract-cli` gives you executable, version-controlled contracts in about 30 minutes per dataset.

→ dbt integration: map contract checks to dbt tests. Require a version bump plus consumer sign-off on breaking changes before merge. After one month of this, most teams report significantly fewer schema surprises.

→ The worst gotcha: contracts that only cover schema, not semantics. A field that changes meaning without changing type is undetectable to automated checks — and it’s how revenue figures silently drift for weeks.

Why schemas break and who owns the blame

Schema evolution sits between two teams that don’t talk to each other on the same cadence. The producer team — usually a backend or platform engineering team — is shipping product features, often weekly, and treats every field they emit as their own. The consumer team — your data engineering team — is running pipelines that depend on those fields staying stable, and finds out about breaking changes the same way archaeologists find ruins: by digging through wreckage.

The producer isn’t wrong for evolving their schema. The consumer isn’t wrong for depending on it. The incident happens because there was no shared definition of what “a safe change” means, no process for communicating it, and no tooling to enforce the agreement. The blame falls on the process, not the person. Which means the fix is a process change, not a person change.

Schema evolution is the load-bearing problem in data engineering in 2026, and it’s the problem most teams handle the worst. The good teams treat upstream schemas as contracts and run checks against those contracts on every pipeline run. The teams that lose stakeholder trust treat upstream schemas as suggestions and find out about every breaking change from a Slack message that starts “hey, the dashboard looks weird.”

That Slack message is always sent on a Friday. It is always sent to a director.

What a data contract actually contains

The mistake most teams make when they start with data contracts is writing schema-only contracts. Field names, data types, nullability. It feels rigorous. It catches a specific class of errors — column removed, type changed — but misses most real incidents.

Real breakages happen at the semantics layer. The producer changes order_total from gross to net revenue. Same field name. Same FLOAT type. No schema violation. But your revenue dashboard is now off by 23%, silently, because the number means something different than it did last week. A schema validator cannot catch this. Only a semantic contract can — one that documents what a field means, how it should be used, and what constitutes a valid business interpretation of its values.

A complete data contract has three layers. Schema: field names, data types, nullability, constraints (no negative values in a price field, for example). Semantics: what each field means in business terms, how it maps to domain concepts, what transformations are applied before it reaches the consumer. SLAs: freshness guarantees (this dataset is refreshed within 15 minutes of source update), completeness thresholds (at least 99.5% of expected rows must be present), availability targets, and a named owner with actual contact information — not “data team.”

The Open Data Contract Standard and the YAML spec

The good news for teams starting in 2026 is that there’s a growing standard: ODCS (Open Data Contract Standard), a YAML-based specification that defines schema, quality rules, SLAs, and ownership in a single document. It’s human-readable, version-controllable in git, and machine-parseable by tools like `datacontract-cli`, which can validate contracts, run compatibility checks, and generate reports.

A minimal ODCS contract for an orders dataset looks like:
```
dataContractSpecification: 0.9.3
id: orders-v1
info:
title: Orders
version: 1.0.0
owner: [email protected]
servers:
production:
type: snowflake
database: PROD_DB
schema: PUBLIC
table: orders
models:
orders:
fields:
order_id:
type: string
required: true
description: Unique identifier for the order
order_amount:
type: number
required: true
description: Net revenue after discounts and returns, in USD
minimum: 0
created_at:
type: timestamp
required: true
servicelevels:
freshness:
description: Data refreshed within 15 minutes of source update
threshold: PT15M
completeness:
description: At least 99.5% of expected rows present
threshold: "99.5%"
```
This is not documentation theater. This YAML file is executable. `datacontract-cli test` validates your actual Snowflake table against this contract. It checks types, required fields, minimum values, and can be wired into CI so that any schema change that would violate the contract fails the PR before it merges.

The only safe migration path for breaking changes

When a producer needs to make a breaking change — remove a field, rename it, change its type, change its semantics — the contract provides a coordination mechanism. There’s a specific pattern that works, and teams that skip steps in it pay for it.

Day 0: Announce. The producer creates a deprecation notice in the contract YAML, updates the changelog, and notifies consumers via a designated channel. Critically, this notification includes a hard date for removal — not “eventually” or “when everyone has migrated.” Deprecated without a date is just a polite rumor. A field can sit in limbo for eighteen months while producers assume nobody uses it and consumers assume it will live forever.

Days 0–60: Dual-write. The producer populates both the old field and the new field simultaneously. Consumers can migrate on their own schedule during this window. The producer monitors usage of the old field (this is easy with Snowflake’s QUERY_HISTORY and column-level access tracking) to know when all consumers have switched.

Day 60: Deprecation notice with hard date. Consumers who haven’t migrated get a 30-day final warning. This is the reminder that actually motivates stragglers. The hard date is non-negotiable.

Day 90+: Removal at v2. The old field is gone. The contract version bumps to 2.0.0. This is a semantic major version — it breaks backward compatibility — and that bump is what triggers automated alerts to any consumer still on v1.

No drama. No guessing. No 2 AM rollback. Give consumers at least 90 days notice for breaking changes. This seems long, but data pipelines have long release cycles, and consumers need time to update downstream logic, tests, and dashboards.

Making it executable: CI enforcement that actually works

The critical architectural decision with data contracts is this: a contract not enforced in CI is just documentation, and documentation drifts. Within six months, the contract YAML and the actual schema diverge, nobody updates the contract when they ship features, and you’re back to tribal knowledge with extra steps.

The enforcement pattern that works:

1. Compatibility check on PR. Before any schema change merges, run `datacontract-cli diff` against the current production contract. Breaking changes fail the PR automatically. Non-breaking changes (adding a nullable field, loosening a constraint) pass. The definition of “breaking” is explicit in the contract spec, not up to whoever reviews the PR.

2. Consumer sign-off for breaking changes. If a breaking change is intentional (the producer knows and has planned for it), the PR requires explicit approval from all registered consumers of that dataset. This is enforced via GitHub CODEOWNERS or equivalent. Producers can’t ship breaking changes unilaterally.

3. dbt test integration. Map contract quality rules to dbt tests. Freshness SLAs become `dbt source freshness` checks. Completeness thresholds become row count assertions. Not-null requirements become `not_null` tests. These run on every dbt build, so violations are caught before models complete — not after reports are wrong.

4. Runtime validation at ingestion. Before data loads into your Silver or Gold layers, validate incoming records against the contract. Rows that violate constraints get quarantined in a dead-letter queue, not silently loaded as nulls. This catches semantic drift that schema validation misses: an order_amount field that’s suddenly returning negative values because someone upstream changed the sign convention.

The gotchas that sink most implementations

Exposing raw transactional schemas as data products. This is the most common structural mistake. When your data contract directly mirrors your application’s OLTP schema, every application refactor becomes a consumer’s problem. The fix is a stable abstraction layer — expose only what consumers need, not the underlying operational detail. Schema changes to the application layer should be absorbed by your ingestion layer, not propagated downstream.

Brittle contracts that break more than they prevent. Strict attribute lengths, tightly constrained enums, or hyper-specific format requirements feel like good quality controls. In practice, they make schemas so rigid that producers constantly need change approvals for minor operational updates that have no downstream impact. Design contracts around semantic guarantees and business invariants, not implementation details. amount > 0 is a semantic guarantee. DECIMAL(18,4) is an implementation detail that will change.

Unclear ownership is the silent killer. Data contracts fail most often not because of tooling gaps, but because accountability is unclear. When something breaks, teams scramble to diagnose issues that fall between ownership boundaries. Every contract needs a named owner with actual incident-response obligations. Not a team. Not a Slack channel. A person whose name is in the contract and who gets paged when a contract violation is detected at runtime.

Semantic changes that look like no-ops. Changing what a field means without changing its name, type, or schema is the hardest class of breakage to catch. order_amount switching from gross to net. A user_id changing from internal to external identifiers. These require semantic versioning (a major version bump) and human review, not just automated compatibility checks. Your CI can catch structural breakage; only your team can catch semantic breakage.

Contracts that cover batch but ignore streaming. If you have a Kafka-based event pipeline feeding your Snowflake tables, the schema contract lives in the Kafka topic, not in the table. Changes to the Kafka Avro schema — registered in Confluent Schema Registry or AWS Glue — need the same versioning and deprecation discipline as your warehouse schemas. Most teams only contract the warehouse side and get burned by streaming schema changes that propagate silently into their pipeline.

The real cost math

Data engineering incidents from schema breakage are expensive in ways that don’t show up on warehouse bills. A typical schema incident at a mid-sized company looks like: 3–4 hours of two engineers debugging, 1 hour of a data analyst investigating wrong numbers, a director review, and a post-mortem. Call that 10 person-hours, at a blended rate of $150/hour. That’s $1,500 per incident.

Teams that experience two schema incidents a month — which is conservative for a team without contracts — are burning $3,000/month, or $36,000/year, on incidents alone. That doesn’t count the cost of wrong decisions made from bad data before the incident was even discovered. One revenue calculation running off a silent semantic change for three weeks is often worth more than a year of incident cost.

The tooling investment for data contracts — `datacontract-cli`, ODCS YAML per dataset, CI integration — is a few days of engineering time. The 90-day discipline is a process change, not a tooling cost. The math is not close.

Where to start (not where everyone starts)

Everyone says “start with your most critical datasets.” That’s correct but useless. More specifically: identify the three datasets that caused production incidents in the last 90 days. Start with those. Not your biggest datasets. Not your most complex. The ones that already broke something.

For each: write the ODCS YAML (schema + semantics + SLAs + owner). Add `datacontract-cli` compatibility checks to the PR workflow for that dataset. Map the quality rules to dbt tests. That’s the first sprint. After one month of this on three datasets, you’ll have a template, a workflow, and enough muscle memory to expand to the rest of the catalog without it feeling like a governance initiative nobody asked for.

The one principle

Change is inevitable. Unmanaged change is expensive. A data contract is the agreement that makes change boring instead of dangerous. The goal isn’t to prevent schemas from evolving — schemas should evolve as the business evolves. The goal is to make every evolution visible, deliberate, and announced far enough in advance that nobody finds out about it from a director on a Friday morning.

Related reading: Open Data Contract Standard (ODCS) · datacontract-cli on GitHub · dbt State: Skip Unchanged Nodes, Cut Runtime by 60% · dbt Fusion: 30x Faster Parsing · Snowflake Query Execution: What Really Happens
July 1, 2026
Why AI Agents Forget: The Architecture behind Memory failures

Your AI agent isn’t getting dumber over time. It’s getting amnesiac. It forgets a constraint you set ten turns ago, even though it followed it perfectly at turn three. It contradicts itself across sessions. It treats a fact you corrected last week as if it never heard the correction. Teams blame the model. They swap GPT for Claude, Claude for Gemini, hoping a smarter model fixes the problem.

It doesn’t. Because the problem was never reasoning. It’s architecture. Specifically: most teams are using the context window as a database, and the context window was never built to be one.

A 2026 study tracking 4,416 trials across six conversation depths found something precise: when an agent violates a constraint it followed correctly ten turns earlier, the model didn’t change — the attention weight on that constraint dropped below the threshold needed to enforce it. That’s not a reasoning failure. That’s a memory architecture failure wearing a reasoning costume.

TL;DR

→ The context window behaves like RAM, not storage: volatile, finite, and degraded by clutter. Most agent failures blamed on “the model” are actually memory architecture failures.

→ Constraints decay with distance. A rule followed correctly at turn 3 can silently fail by turn 10 — not because the model forgot, but because attention weight on it dropped below the enforcement threshold.

→ Four memory types need separate handling: working (current task), episodic (past interactions), semantic (facts/preferences), and procedural (learned skills). Production systems collapse these into one bucket and pay for it.

→ Best 2026 architectures hit ~92.5 on LoCoMo and ~94.4 on LongMemEval benchmarks at roughly 6,900 tokens per retrieval — a fraction of full-history prompting.

→ Memory poisoning is now a named, ranked threat (OWASP ASI06, 2026). Attack success rates of 80–99.8% have been demonstrated against production-style agents.

→ Unlike prompt injection, memory poisoning is temporally decoupled: the attacker writes today, the agent misbehaves months later, with no single suspicious moment to catch in logs.

→ Frameworks like Letta, Mem0, and Cognee treat memory as a tiered OS-style hierarchy — context as RAM, external store as disk — rather than a bigger prompt.

→ Bigger context windows do not solve this. They delay the symptom and raise the cost per query while “lost in the middle” retrieval failures persist regardless of window size.

The assumption everyone makes (and shouldn’t)

Ask most engineers how their agent “remembers” things, and the honest answer is: it doesn’t, not really. It re-reads the entire conversation history on every single call. Every query triggers full recomputation from scratch — the model has no concept of “yesterday” unless yesterday’s text is physically present in today’s prompt.

This statelessness is a deliberate design choice, and it has real upside: reproducibility, simplicity, no hidden corrupted state between calls. But it creates two structural problems nobody can engineer around with a smarter model. First, computational inefficiency — you’re paying to recompute similarity over text the model has already processed a hundred times. Second, and more dangerous: context window limits. Long multi-turn conversations, agentic workflows, and long-running tasks all need more history than fits, so teams either truncate (losing information) or compress (introducing error) or simply hope the window is big enough this time.

Bigger windows feel like the obvious fix. They aren’t. Long context windows still suffer “lost in the middle” retrieval failures — the model technically has the information but doesn’t weight it correctly when it matters — while full-history prompting creates real cost problems at enterprise scale. You can have a million-token window and still watch an agent forget a name mentioned at token 40,000 because it’s buried under everything that came after.

Why the RAM analogy actually explains the failures you’re seeing

The context window shares three properties with RAM that distinguish it from persistent storage, and the mismatch is what breaks production agents. It’s volatile — everything disappears at session end, including a preference stated at turn one and a constraint set at turn three. It’s finite — there’s a hard ceiling, and once you hit it, something gets evicted whether you chose it or not. And it’s expensive per byte — every token you keep “just in case” is a token you pay to process on every single call, forever, for the life of that conversation.

When you build against the context window as if it were a database — appending forever, never pruning, assuming everything you put in stays retrievable — you get failures that look exactly like the model is getting confused, contradictory, or “dumber.” It isn’t. You’re running a database workload on a RAM-shaped substrate, and RAM does what RAM does: it fills up, and old things get pushed out or buried.

The fix isn’t a bigger window. It’s a second layer: a persistent memory store, external to the context window, that you control like an operating system controls RAM — deciding deliberately what goes in, what stays, and what gets evicted, instead of letting the model figure it out by attention weights alone.

Four memory types, one bucket (the real architectural sin)

Most production agents collapse everything into a single, undifferentiated memory blob: conversation history. But mature memory architecture treats at least four types as distinct, because they decay differently, get retrieved differently, and fail differently when mishandled.

Working memory — the current task state, what you’re doing right now. Short-lived, high-relevance, meant to be discarded once the task completes.

Episodic memory — specific past interactions and experiences. “Last Tuesday the user asked about refund policy and got frustrated with the answer.” Time-stamped, specific, useful for continuity.

Semantic memory — durable facts and preferences, stripped of the conversational context that produced them. “User prefers email over Slack.” “User’s company uses Snowflake, not BigQuery.” This is what most people mean when they say “the agent remembers me.”

Procedural memory — learned skills and patterns of action. “When this user asks for a report, format it as a table, not prose.” This is the hardest to do well and the most valuable when done right.

Production systems that dump all four into one vector store and retrieve by similarity alone tend to surface the wrong type at the wrong time — episodic noise crowding out a stable semantic fact, or a one-off preference from a bad mood three months ago resurfacing as if it were a permanent rule. Coordinating transitions between these types — when does an episodic memory get distilled into a semantic fact? when does a procedural pattern get unlearned? — is most of what separates a memory system that improves over months from one that quietly degrades.

The retrieval pipeline, and where the cost actually goes

In a properly built memory layer, the model never sees your full history. During conversations, the system extracts facts and stores them in a vector database indexed by user, session, and agent identifiers. At the start of a new session — or mid-conversation, as needed — relevant memories are retrieved using a combination of semantic similarity, keyword matching, and entity matching, then injected into the context window right before the model responds. Only the most relevant facts surface, which keeps token usage low and retrieval precise instead of dumping everything and hoping attention sorts it out.

This is where the real cost math lives. A naive approach — replaying full conversation history every turn — scales token cost linearly with conversation length, and by month three of an active user relationship, you’re paying to reprocess tens of thousands of tokens of mostly irrelevant history on every single message. A well-built retrieval layer holds that flat: leading 2026 systems achieve strong recall on multi-session benchmarks while retrieving roughly 6,900 tokens per call, regardless of how long the relationship has run. That’s not a marginal efficiency gain — it’s the difference between a cost curve that’s flat and one that grows without bound as your best, most loyal users accumulate the longest histories.

The benchmarks that matter here are LoCoMo (long conversation memory), LongMemEval, and BEAM — they specifically test whether an agent can recall and reason over facts buried many sessions back, not just within a single long context. Recent leaders score around 92–94 on these, with the largest gains coming from temporal reasoning (knowing when something was true, not just that it was said) and multi-hop retrieval (connecting two separate facts from different sessions to answer one question).

The gotchas nobody warns you about

Constraints decay with distance, silently. This isn’t intuitive until you’ve watched it happen. An agent that perfectly honors “never mention competitor X” for the first eight turns will sometimes mention competitor X at turn fifteen — not because anything changed, but because the attention weight on that instruction, buried further and further back, dropped below the threshold needed to actually constrain output. Negative constraints (“don’t do X”) decay faster than positive instructions (“do Y”), because there’s no ongoing signal reinforcing the absence.

“Lost in the middle” doesn’t go away with bigger context. Models reliably retrieve information near the start or end of a context window far better than information buried in the middle. Doubling your context window doesn’t fix this — it just moves where the “middle” is, and gives you more room to bury things in it.

Memory poisoning is not prompt injection’s cousin — it’s a different threat class entirely. Prompt injection is session-scoped: it does damage now, and the damage ends when the session ends. Memory poisoning writes malicious content into persistent storage, where it survives across every future interaction, triggered by completely unrelated conversations months later. OWASP formalized this as ASI06 in its 2026 Agentic AI Top 10, specifically because the defenses that work against prompt injection — input moderation, output filtering, session-bounded monitoring — don’t catch an attack that was planted in February and triggers in April.

The attack success rates are not theoretical. Published research demonstrates attack success rates ranging from roughly 80% up to 99.8% against agent memory systems using techniques like indirect injection through documents the agent is asked to summarize, or webpages the agent is asked to fetch. One demonstrated case against a cloud agent platform showed a single crafted webpage URL, fetched by the agent, writing persistent instructions into session memory that silently exfiltrated data on every subsequent interaction.

Stale facts actively degrade output, they don’t just sit inert. A semantic memory that was true six months ago — “user works at Company A” — doesn’t just become irrelevant when it goes stale. If never pruned or updated, it actively competes with the correct, current fact at retrieval time, and similarity search has no inherent way to know which one is “more true.” Memory systems need explicit staleness handling, not just additive storage.

Cross-session identity is still mostly unsolved. If the same person talks to your agent from their phone, their laptop, and an anonymous browser session before logging in, stitching those into one coherent memory profile is an open research problem, not a solved one. Most production systems quietly accept fragmented identity as a known limitation rather than a bug to fix.

What the better architectures actually do

The frameworks that handle this well — Letta, Mem0, Cognee, and similar — share a common idea: treat memory like an operating system treats RAM, not like a developer treats a growing log file. Letta’s approach is explicit about this, using a tiered architecture where the active context functions as RAM and an external store functions as disk, with the agent able to read, write, and archive its own memory through function calls rather than having everything force-fed into every prompt. Mem0 takes a similar stance from the extraction side: pull key facts out of conversation, then run an explicit decision step — add, update, delete, or no-op — so memory accumulates deliberately instead of by default.

The common thread across all of them: memory is a dedicated architectural component, separate from the model’s context window, not just a longer prompt wearing a fancier name.

The real question: build, or borrow?

Reach for a managed memory framework if: you’re shipping a consumer-facing or long-running agent where users return across days or weeks, you don’t have a research team to spend on retrieval tuning, or you need cross-session identity and staleness handling out of the box rather than building it yourself.

Build it yourself if: your agent is genuinely single-session (no continuity needed across conversations), your team has the bandwidth to own retrieval quality and security hardening long-term, or you’re operating in a regulated environment where you need full control over where memory data physically lives.

Either way, budget real engineering time for the security side. Memory poisoning defenses — provenance tracking on what gets written to memory and from where, trust-scoring on retrieved content before it’s injected into context, and behavioral monitoring for an agent that starts defending beliefs it has no legitimate reason to hold — are not optional hardening for later. They’re part of the architecture, the same way input validation isn’t optional hardening for a web form.

The one principle

Treat the context window like RAM you actively manage, not a database that remembers for you. Decide deliberately what goes in, what gets promoted to durable storage, and what gets evicted — because if you don’t make that decision, attention weight and token limits will make it for you, silently, and you’ll find out about it from a user complaint instead of a design review.

Related reading: OWASP Top 10 for Agentic Applications · dbt State: Skip Unchanged Nodes, Cut Runtime · dbt Fusion: 30x Faster Parsing · Snowflake Iceberg v3 Migration Guide

June 30, 2026
dbt Fusion: 30x Faster parsing(And why Migration matters)

You’ve probably heard the buzz: dbt’s new Fusion engine is 30x faster. But what nobody says clearly is faster at what, for whom, and does it break your project? The answer is messier than the marketing.

The Fusion engine is a complete rewrite of dbt Core in Rust, released last year and hitting 4,500+ projects already. It’s genuinely fast at parsing and compilation — the steps dbt runs locally before it ever talks to your warehouse. But parsing speed doesn’t change your pipeline runtime. What does change is the developer experience: real-time error feedback in VS Code, instant file recompilation, the ability to catch typos before you run a job. That’s real. But the migration is not friction-free. Fusion enforces stricter validation than dbt Core, which means deprecated code patterns that currently just warn you will now block your runs. And some packages won’t work yet. It’s a choice, not a free lunch.

TL;DR

→ dbt Fusion is the Rust-based evolution of dbt Core. Parse times up to 30x faster (vs Python). Full project compilation 2x faster. Real-time VS Code integration with IntelliSense.

→ The speed win is in local parsing/compilation, not warehouse query time. It changes the developer experience, not your pipeline runtime.

→ Migration requires: resolve all deprecation warnings, update packages, test with --use-v2-parser flag first, upgrade dev then staging then production.

→ What breaks: strict YAML validation, old CLI flags (--models → --select), behavior change flags can’t be disabled, YAML anchors need to move under anchors: key, some packages incompatible.

→ Static analysis defaults to “baseline” mode (warnings, not errors), making gradual adoption possible. Opt models into “strict” mode incrementally.

→ dbt-autofix tool automatically fixes ~80% of compatibility issues. Don’t do it manually.

→ Always upgrade dev first, test for a week, then staging, then production. dbt Manifest incompatibility means you can’t mix v20 (Fusion) with v12 (Core) across environments.

The mental model that’s outdated

Most analytics engineers think of dbt as a slow, clunky tool. Write a model, run dbt build, wait 30 seconds for parse time on a big project, wait for warehouse execution, see the result. Then fix a typo, run again, repeat. It’s a cycle, and it’s painful at scale.

That cycle is the Python dbt Core experience. It’s been the same since 2016.

Fusion breaks that cycle. With Rust, with SQL comprehension, with the VS Code extension powered by a language server, you get real-time feedback. You type a SQL syntax error and the editor shows you the error as you type, without running dbt, without hitting the warehouse. You save a file and Fusion recompiles your entire project in your IDE in seconds, not minutes.

Developer experience: dbt Core requires warehouse round-trips for each error. Fusion catches errors in the IDE instantly, offline

But — and this is critical — this only affects your local development experience. Your actual pipeline runtime (the time from `dbt build` to “done”) is nearly unchanged. Parsing and compilation are usually 5–15% of total run time. Warehouse execution is the rest. Fusion doesn’t touch warehouse execution.

If you’re excited about Fusion because you think it will cut your 2-hour nightly pipeline to 1 hour, you’re disappointed. If you’re excited because you want real-time error feedback while you’re writing models, you’re right to be excited.

What changed: The 30x parsing speed, explained

dbt Core v1 (Python) parses your YAML, your Jinja templates, your SQL, and figures out the dependency graph all in Python. Python is slow at this. On a project with 500 models and intricate Jinja, parsing alone can take 15–30 seconds.

Fusion (Rust) does the same work but in a compiled binary. No Python interpreter overhead. No garbage collection pauses. Just native machine code doing the parse. Result: parsing in 0.5–1 second on the same project.

That’s the 30x number. Real number, not marketing.

Then Fusion goes further. It parses SQL syntax, understands column types, knows which functions exist on which warehouse, and validates your SQL before it ever hits the warehouse. dbt Core doesn’t do this; it just templated text and sends it up. Fusion actually understands your SQL. Which is why it catches errors in the IDE before you run the job.

But here’s what Fusion doesn’t change: the time your actual SQL queries take to run on the warehouse. That’s still determined by your warehouse optimizer, your data volume, your indexing. Fusion has zero impact on that.

What breaks during migration

YAML validation gets strict. dbt Core accepts YAML files with extra keys at the top level (they’re just ignored). Fusion rejects them with an error. A common pattern: defining YAML anchors at the top level of your schema.yml file. In Fusion, those have to go under an anchors: key. The dbt-autofix tool fixes this automatically, but if you’re migrating manually, this is where you’ll get tripped up first.

Deprecated code is no longer a warning. In dbt Core v1, using an old feature generates a warning. In Fusion, it’s a hard error. Before you migrate, you must run your project on Latest dbt Core track, fix all deprecation warnings, then upgrade to Fusion. There’s no skipping this step.

Behavior change flags can’t be disabled. dbt Core has flags like require_column_description. You set require_column_description: false to opt out. Fusion enables all these flags and doesn’t allow you to opt out. If your project relies on column descriptions being optional, Fusion will reject it.

Old CLI flags are gone. The --models flag (deprecated since dbt 0.21) works in Core but errors in Fusion. Use --select. Same with --resource-type → use --resource-types. All your job definitions, all your scripts have to be updated.

get_relation() print behavior changes. In dbt Core v1, printing the result of `get_relation()` when the relation doesn’t exist shows “None”. In Fusion, it errors. This breaks some legacy macros. Rare, but it happens.

Package incompatibility is real. Not every dbt package has been updated for Fusion. If your project depends on an unmaintained package, you’ll have to fork it or wait. dbt Labs packages (dbt_utils, dbt_project_evaluator) are compatible. Most popular community packages are. But check the dbt package hub; it shows a Fusion-compatible badge.

Manifest incompatibility means you can’t mix dbt versions across environments. Fusion produces a v20 manifest. Latest dbt Core produces v12. If your dev environment is Fusion and your production is still Core, features like `state:modified` and `–defer` break because the manifests are incompatible. You have to upgrade all environments together or not at all.

The gotchas that actually matter

Static analysis defaults to “baseline” mode. This is Fusion’s secret weapon for adoption. Instead of strict SQL validation (which would break half the projects), Fusion defaults to “baseline” mode. Validation errors show as warnings, not blockers. Your project still builds and runs. You get real-time feedback in the IDE, but you’re not forced to fix everything at once. This is intentional — it’s a soft migration path.

You can opt individual models into strict mode with static_analysis: strict in your config. Opt in where you want full SQL comprehension. Leave the rest in baseline.

UDFs are limited in strict mode. If you use custom user-defined functions (UDFs), Fusion’s strict mode has trouble with them unless you register them in sql_header or on-run-start hooks. This catches most cases, but warehouse-native functions or post-hooks that define UDFs can cause issues. In baseline mode, most UDFs just work.

Parsing is local-only; dbt still needs the warehouse. Fusion’s fast parsing happens offline, on your laptop. But when you run dbt build, you still need warehouse access for execution, for macro evaluation, for source freshness checks. Fusion doesn’t change that. If you’re offline or your warehouse is down, you’re still blocked.

dbt Mesh with Semantic Layer has limitations. If you use cross-project metric references in dbt Mesh, that’s only supported in the legacy Semantic Layer YAML spec, not the new one. You’ll have to choose between Mesh cross-project references and the new cleaner YAML structure. Support for both is planned but not here yet.

The migration checklist (what actually works)

Step 1: Prepare on Latest dbt Core (1.12+). Move your entire project to the Latest release track on dbt Core. This includes all development environments and jobs. Don’t skip this. Latest is where deprecated features show up clearly.

Step 2: Run dbt-autofix. Install dbt-autofix (with uvx dbt-autofix) and run it on your project directory. It automatically rewrites your YAML to conform to the latest schema, moves YAML anchors under an anchors: key, updates deprecated configs, and upgrades packages to Fusion-compatible versions. This alone fixes ~80% of issues. Do not skip this step and do it manually. The tool is designed to be safe and generates a clean git diff.

Step 3: Fix remaining deprecation warnings. After dbt-autofix, you might still have warnings (old Jinja patterns, intricate macros, unsupported SQL features). Fix these manually. Your dbt_project.yml should now have zero deprecation warnings when you run on Latest track. If you see warnings, resolve them before going further.

Step 4: Update all packages. Run `dbt deps update` to pull the latest versions. Most popular packages now have Fusion-compatible releases. If you have a custom package that’s not Fusion-compatible, you’ll need to update it separately (or fork it).

Step 5: Test the v2 parser locally. If you’re on dbt Core v1.12, you can test Fusion’s parser without migrating the whole engine. Run dbt build --use-v2-parser. This delegates only parsing to Fusion’s engine but keeps Core’s execution. If this succeeds, you’re likely safe to migrate. If it fails, fix the issues before upgrading.

Step 6: Upgrade dev environment to Fusion. In the dbt platform, upgrade your development environment to use the Latest Fusion release track. Test everything in dev for a full week. Run jobs, run CI, run your usual workflows. This is not a 2-hour test; it’s a 7-day validation.

Step 7: Watch for manifest incompatibility. If your staging or production environments are still on dbt Core, the v20 (Fusion) and v12 (Core) manifests won’t talk to each other. Features like `state:modified` and `–defer` will silently fail. So either upgrade all environments together, or keep everything on Core for now. You can’t have a hybrid setup.

Step 8: Upgrade staging, then production. After dev validation, upgrade staging (if you have it). Run production-like workloads for another 24–48 hours. Finally, upgrade production. Don’t do all three at once.

Step 9: Monitor the first 48 hours of production. Watch scheduled job runs, compare run times to baselines, look for unexpected failures. Fusion is stable, but this is where you’d catch edge cases specific to your setup.

When the speed actually matters

Parsing 30x faster sounds great until you realize: parsing is 10 seconds out of a 120-second pipeline. You’re saving about 9 seconds. That’s real, but it’s not transformative.

Where the real win is: iterative development. You’re writing a model, trying to get the SQL right, going back and forth. In dbt Core, every iteration is a round-trip to the warehouse. In Fusion, every save gives you instant feedback in the IDE. No warehouse call. No wait. That’s where 30x matters. It’s not about total runtime; it’s about the feel of development.

If your team is not iterating heavily (you write once, test once, deploy), Fusion’s speed boost feels small. If your team is constantly tweaking, testing incrementally, exploring SQL, the developer experience jump is massive.

The one principle

Fusion trades migration friction for developer velocity. It requires you to clean up deprecated code, update packages, be intentional about configuration. That’s work upfront. But on the other side, you get a development experience that’s fundamentally faster. It’s not a free upgrade; it’s a real investment that pays off if you’re writing and iterating regularly.

If your team writes dbt code once and deploys it to production untouched, the friction might not be worth it. If your team is constantly refining, testing, exploring — which is most analytics teams — it is.

Related reading: Upgrading to dbt Fusion (official docs) · Migrate to the Latest YAML Spec · Snowflake Streams & Tasks: SCD2 Pipeline Guide · Snowflake Query Cost Estimator

June 22, 2026
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.

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

June 22, 2026