Semantic dbt tests on Databricks: 50,000 real reviews through a Unity Catalog function

The last post wrote four dbt tests as English sentences and ran them on a local DuckDB. They caught a customer called Mickey Mouse, a return coded late whose comment says the box arrived crushed, and a support ticket with someone's email address spelled out in words. A regex baseline missed most of those.

The obvious question afterwards was whether any of it survives contact with a real warehouse. A Python plugin on a laptop is the easy case. On Databricks the model call has to run somewhere the platform allows, the API key has to live somewhere nobody can read it, and the whole thing has to cope with tens of thousands of rows that nobody hand-crafted.

So demo 05 moves the same tests to Databricks. The schema.yml blocks are byte-for-byte the same, and a test checks that. What changed is everything underneath. Then I ran it on 50,000 real product reviews, and it failed its own gate by one point of recall.

This is the fifth demo in my Jev demo series. Code, answer keys and every logged run are public in jev-demos/05-dbt-databricks↗.

What the dbt user sees#

Nothing new, which was the point. The four yardstick tests are unchanged from the DuckDB demo, and the production run adds a fifth in the same shape, next to the structural tests on a reviews model:

- name: body
  data_tests:
    - not_null
    - jev_expect:
        name: product_reviews_body_matches_stars
        arguments:
          fails_if: "The overall sentiment of the review `body` clearly contradicts its `stars` rating (1 = very bad, 5 = excellent)."
          context: [stars]
          threshold: 0.8
          criteria:
            "true": "A glowing text with 1-2 stars, or an angry/negative text with 4-5 stars"
            "false": "Sentiment roughly matches the stars, including sarcasm that matches a low rating and mixed 3-star reviews"
        config: {severity: warn, store_failures: true, tags: [production]}

One thing did change since the first post: the tests run at severity: warn. Someone suggested it in a comment on LinkedIn, and they were right. A probability should flag a row for a person to review. It shouldn't stop a pipeline. Flagged rows still land in the audit schema with their probability jev_p, so you can query them like any other stored test failure.

Where the model call lives#

On DuckDB the plugin registered a UDF on the connection. Databricks has no equivalent hook for dbt, so the call moved into the platform itself: a Unity Catalog Python function.

jev_demo.jev.noul_pack(records ARRAY<STRING>, question STRING, model STRING)

It's a plain Python UDF with a SECRETS (...) clause, standard library only (urllib, no requests). The TypeSafe↗ API key is a Unity Catalog secret that only this function can read. The key never shows up in dbt, in a profile, in an environment variable or in a notebook. Anyone with EXECUTE on the function can use it, and nobody else needs READ SECRET.

Three platform details cost me time, so here they are:

• Serverless SQL warehouses block outbound network calls from UDFs by default. The workspace preview "Enable networking for isolated workloads in Serverless SQL Warehouses" has to be on, or the function fails on its first request.
• The function runs with its owner's rights. Anyone who can call it spends the owner's secret. In a real setup the owner should be a service principal or a group, not a person.
• The function body is a Python module, embedded verbatim into the CREATE FUNCTION statement by a deploy script. That means pytest tests the exact code that runs in the warehouse, which is the only reason I trust the retry logic (more on that below).

Judging moved out of the test#

In the DuckDB version the test itself called the model. On Databricks that's the wrong place. A dbt test is a SELECT, it runs every time, and nothing persists between runs. So the architecture flipped:

dbt build
 ├─ model builds → post-hook jev_judge()
 │     states = distinct (tested column + context) values in the model
 │     misses = states with no successful judgment yet
 │     packs  = misses cut by estimated tokens
 │     one function call per pack → INSERT INTO jev.judgments
 └─ jev_expect test → SELECT model rows JOIN judgments WHERE p >= threshold

A post-hook on each model does the judging and appends to a Delta table, jev.judgments. The test is now an ordinary SELECT that joins against it. That one table does two jobs:

• It's the cache. The key is a hash of model version, layout, question and state. A state with a successful judgment is never sent again. Change the sentence and the key changes, so everything is judged fresh.
• It's the ledger. Every row records the pack it came from, tokens, attempts, retries, errors and the dbt invocation. The summary line after each build is computed from it, and so is every number in this post.

The hook and the test build the question, the state and the key from the same macro, so they can't drift apart. And the hook only judges when the jev_expect tests are actually selected in the invocation. A plain dbt run, or a build that excludes tag:semantic, builds the models and never calls the model. That's how you keep a probabilistic, billed step out of every ordinary run without writing a separate job for it.

Two failure modes are loud by design. A tested row with no successful judgment (a pack failed, or you changed the sentence and only ran dbt test) comes back as a failing row with jev_p = NULL, never a silent pass. And every build checks that each missing state was judged exactly once: rows inserted equals states missing, pack sizes sum to rows inserted, no key appears twice. If any of that fails, the run fails.

Packing by tokens, not rows#

The first post ended on a packing lesson: batch rows into one request and you change the question the model answers, unless each record sits in its own question. Demo 05 keeps that nested layout and changes how packs are cut. Instead of a fixed row count, packs fill up to an estimated 48k tokens (the API takes 64k per request), with a cap of 256 rows.

On the yardstick data (demo 04's exact rows, answer key and regex baselines), that's 6 requests for the 1,057 distinct states. Demo 04 needed 18 at 64 rows per request, and 1,057 at one row per request. Four fresh live runs, all of them passed the gate, each for $0.006:

The recall ranges are real run-to-run noise, and I'd rather show them than pick the best run. Two golden defects sit right on their thresholds (customer 379 scored 0.69 to 0.75 against a threshold of 0.7, and return 144 scored 0.79 to 0.83 against 0.8), and both dipped under in the first run only. Across the four runs the mean standard deviation of a row's probability was 0.0042. Precision didn't move at all. So a single run's recall is reliable to about one row, and that's worth knowing before you put a hard gate on it.

The production run: 50,000 real reviews#

The yardstick data is synthetic, and I wrote it, so the hard cases are the ones I thought of. For the second run I used real text: a seeded sample of 50,000 reviews from the Amazon Fine Food Reviews dataset (SNAP, Stanford; McAuley and Leskovec, WWW 2013). User ids and profile names are dropped. On about 3% of the reviews that weren't 3 stars, the star rating was flipped (1↔5, 2↔4), which gives 1,386 planted defects with a known answer.

It ran in three logged steps: 47,500 rows, then 2,500 more, then a rerun.

The increment is the number I care about most as an engineer. Adding 2,500 rows judged only the 2,219 states it had never seen. Reviews repeat, and a repeated text with the same stars is a cache hit. The rerun made zero requests. That's what turns a per-row model call from a demo into something you could schedule nightly: you pay for new data, not for the table.

The other thing the timings show is that the model wasn't the slow part. In the spike before the build, Jev spent about 0.7 seconds on a full 256-row pack, while each SQL statement carried 13 to 27 seconds of warehouse overhead. Wall time on Databricks is dominated by the warehouse, not the model.

One caveat on cost: the dollar figures are the Jev API cost only. Warehouse DBUs aren't in them, because system.billing wasn't enabled on this workspace, and I'm not going to estimate a number I didn't log.

It failed the gate, and I'm keeping the FAIL#

The gate was set before the run: recall of at least 0.85 on the planted flips. Jev reached 0.84. That's a FAIL, it's logged as a FAIL, and it stays one. The regex baseline on the same data reached recall 0.73 at a raw precision of 0.24 (F1 0.36, against Jev's 0.85), so it's not close. But "beats regex" was never the bar.

What makes this interesting is that on real data, the answer key is wrong too, in both directions. Two blind audits, set up before any labelling, measured how wrong:

• Precision. Jev flagged 1,328 reviews, and 168 of them weren't planted flips. Raw precision counts all 168 as mistakes, which gives 0.87. A blind audit of 100 of them found that many are real mismatches that were already in the source data: glowing text with one star, angry text with five. Real customers do that. Audited precision is 0.94 (95% Wilson interval 0.93–0.95).
• Recall. A flipped rating isn't always a defect. Flip a lukewarm 2-star review to 4 and it can still read as plausible. An audit of 100 random planted flips found that 98 really read as mismatched. Corrected for that, recall over all flips comes out at about 0.86, a point estimate. Recall on the audited-real flips alone is 0.90 (95% Wilson 0.82–0.94).

So the honest summary is that recall is around 0.84 to 0.86, right at the edge of the gate either way. The corrected number is reported as a separate line next to the raw one, and it doesn't replace it.

A note on how the audits were labelled, because it matters: two independent LLM labellers (Claude Opus and Claude Sonnet), each given a shuffled copy with only the review text and stars. They never saw Jev's probability or whether Jev flagged the row. Where they disagreed, the tie went against Jev. They agreed on 81% of the precision audit (κ 0.60) and 99% of the key audit (κ 0.80). That's a reasonable measurement, but it's an LLM-labelled one, and I've labelled it as such everywhere it appears.

The general lesson here isn't about Jev. On real data, your answer key has error bars. If you score any classifier against planted defects, measure the key before you trust the score.

What I'd still worry about in production#

The demo writes these down rather than hiding them:

• There's no global rate limiter. TypeSafe allows 1,200 requests a minute and 250k tokens a second. REPARTITION(n) caps the packs in flight per statement, but dbt runs four threads, so four hooks can run at once. The coordination across them is the server's 429 and a jittered backoff inside the function. That retry path is tested offline and has never fired live (zero 429s in every logged run), which is why the production run kept only two packs in flight per statement.
• A Spark task retry can bill twice. If a task is retried after its first attempt already reached the API, the function can't see it. The only check is external: compare the ledger's token total with the provider's usage page for that day.
• Concurrent hooks and Delta. Two hooks appending to the same Delta table conflict (DELTA_CONCURRENT_APPEND) if their cache lookups scan the whole table. Each hook reads only its own question's rows, which avoids it. You find that one in testing, not in the docs.
• Small dbt-on-Databricks gotchas. Databricks SQL doesn't escape a quote by doubling it ('don''t' reads as two literals). dbt retry doesn't judge, because dbt leaves selected_resources empty on a retry. And dbt-databricks 1.12.5 caps dbt-core below 1.12.4.

What this doesn't show#

• The defects in the production run are planted. The text is real, the flips are mine. Real rating mismatches turned up anyway, which is what the precision audit found, but the recall number is about planted flips.
• The baseline is still regex. This shows the approach holds up on a real warehouse and real text. It doesn't show how a general-purpose LLM would do on the same rows, at what cost or latency. That benchmark is designed and not yet run, so I'm not making the claim.
• One test on the production data. Reviews against stars is the easiest of the four to plant at scale. Names, return reasons and PII on real data are still open.

What I'd take from it#

If you run dbt on Databricks, the shape is reusable without any of the Jev specifics. Put the model call in a Unity Catalog function so the secret never leaves the platform. Judge in a post-hook, not in the test, and only when the semantic tests are selected. Make a Delta table both the cache and the ledger, so reruns are free and every number has an invocation behind it. Fail loudly when a row wasn't judged. And when you score on real data, audit the answer key too.

It's the same split as the rest of the series and how I build AI-native: code handles everything with a right answer, including the caching, the counting and the gate, and the model only answers the one question code can't. The DuckDB version is still the place to start if you want to see the tests themselves, and its scorecard lets you move the thresholds. The demo folder↗ runs end to end in a clearly labelled simulated mode, without an API key.

Disclosure: I'm an independent user of Jev with no commercial relationship with TypeSafe. Every number here comes from a logged live run in the demo's docs/eval-results.md; the simulated mode's output is never quoted.