LLM Columns in dbt Break Idempotency. Cache by Hash, Not Run

TL;DR — A dbt model is supposed to be a pure function of its inputs. An LLM call inside a model is not, so full refreshes, CI diffs, and tests all quietly stop meaning what they used to. Treat LLM-derived columns as a content-addressed ledger keyed on input, prompt, and model identity, and let dbt join to it instead of regenerating it.

The most seductive thing a modern warehouse can do for an AI team is let you call a model from SQL. Classify a support ticket, extract an entity, summarize a contract. One expression in a SELECT, wrapped in a dbt model, and you have an "enriched" table that refreshes on schedule.

It works on day one. It is also a quiet violation of the contract that makes dbt worth using. My thesis: an LLM call is not a transformation, it is an observation, and you should store it like one.

The contract you didn't know you were relying on

dbt's whole workflow rests on an assumption that rarely gets stated: a model is a pure function of its upstream relations plus its code. Same inputs, same SQL, same output. Everything downstream leans on that:

• Full refresh is safe. You can drop and rebuild any table and expect equivalence.

• Slim CI works. If code and upstream data haven't changed, you can skip the model and trust the existing result.

• Diffs are meaningful. A change in output means a change in logic or data, so you can review it.

• Incremental models are an optimization, not a semantic choice. They should converge on what a full rebuild would produce.

Put a model call inside the SELECT and every one of these breaks, each in a slightly different way.

How it fails in practice

Start with the full refresh. Someone runs it to fix an unrelated schema issue. Every row is re-sent to the model. Sampling variance, a silently updated model alias, or a tweaked prompt means a few percent of your categorical labels flip. No SQL changed. Dashboards that group by that label move, and nobody can explain why from the git history.

Then incremental models. They now encode a hidden policy: rows processed last month were labeled by whatever model was live then, and rows processed today by whatever is live now. Your table is a palimpsest of model versions with no column saying which is which. A full rebuild would not reproduce it. The "optimization" has become the source of truth.

Then CI. Slim CI sees no modified code and skips the model. But if the prompt lives in a variable, a config table, or the endpoint behind an alias, the semantics changed and the checker is blind to it. Conversely, if CI does rebuild the model, you pay for per-row inference on every pull request and get different outputs each time, so the diff is noise.

Finally, tests. not_null, unique, and accepted_values pass happily on a column that is 15% wrong. These tests check shape. LLM output fails in meaning, which is the one place dbt's built-in tests don't look.

Content-addressed enrichment: a ledger, not a column

The fix is not to avoid LLMs in the warehouse. It is to separate generating an observation from using it.

Build an append-only enrichment ledger. Each row is keyed by a hash of everything that determines the output:

• the normalized input text (or its hash),

• a prompt identifier with its own version, treated as a deployable artifact,

• a pinned model identifier, never a floating alias,

• the decoding parameters that matter, such as temperature and output schema.

The ledger stores the output, a timestamp, and the key components in plain columns. It is written by a separate job, not by a dbt SELECT. That job asks one question per row: does a ledger entry already exist for this key? If yes, do nothing. If no, call the model and append.

Your dbt model then does what dbt is good at: a deterministic join. Inputs plus a pinned (prompt, model) pair select a ledger row. Rebuild it a hundred times and you get the same table, because the nondeterminism was captured once at write time and frozen.

This is just memoization with a content-addressed key, the same idea behind build caches and artifact stores. The model call becomes a cacheable, replayable side effect, and the transformation layer stays pure.

What this buys you

Upgrades become explicit events. Changing a prompt or model means bumping a pinned version in config. That is a reviewable diff in git. The new version backfills into new ledger rows; old rows remain. You can run both versions side by side, compare them, and cut over deliberately, rather than discovering the change in a metric review.

Full refresh becomes free and safe. Rebuilding the dbt model costs warehouse compute for a join, not inference for every row. The model is only re-invoked for genuinely new keys.

CI gets honest. Because prompt and model identity are part of the model's config, state comparison sees the change. And in CI you can point the join at a small frozen fixture of ledger rows, so tests are deterministic and cost nothing.

Lineage includes the model. Every enriched value can answer "which prompt and which model produced you?" That matters when a downstream consumer, human or another agent, asks why a record is labeled the way it is.

Tests that check meaning

The ledger gives you something to test against, and the tests have to be statistical, not structural. A few that pay for themselves:

• Distribution stability. Compare the label mix of a new version's outputs to the previous version on the same inputs. A big shift is a review trigger, not necessarily a failure.

• Golden-set agreement. Keep a small, human-labeled sample and assert a minimum agreement rate on every version bump.

• Self-consistency on a slice. Re-run a sample of keys and measure how often the output flips. High flip rate means the task is under-specified or the decoding is too loose, and no downstream join will fix that.

• Schema conformance at write time. Reject malformed outputs before they enter the ledger, so the warehouse never stores something it can't type.

These run on the ledger job, not on every dbt build. dbt's role is to enforce that downstream models only consume rows from approved (prompt, model) pairs.

Where this fits with the semantic layer

Semantic layers define metrics over governed columns. If one of those columns is an LLM-derived label, then the metric inherits its nondeterminism. "Tickets by category" changing between Tuesday's and Wednesday's queries is the sort of bug that destroys trust in the whole layer. Pinning the enrichment version into the metric's definition makes it a first-class dimension of the metric: category as of classifier v3. That is a boring, auditable statement, and boring is the goal.

The objection: it's more plumbing

It is. A ledger table, a writer job, and a version registry are more moving parts than one SQL expression. But the single-expression version defers that cost rather than eliminating it. You pay later, in unreproducible dashboards, surprise inference bills from a routine refresh, and debugging sessions where the honest answer is "the model changed and we didn't record it."

The rule of thumb I'd give any team: if a value came from a model, it is data you collected, not data you computed. Collected data gets an append-only log, provenance, and a deliberate process for replacing it. Computed data gets dbt. Keep the two on opposite sides of a join and both stay trustworthy.