Semantic dbt tests: data quality checks in plain English

Structural dbt tests check the shape of your data. not_null, unique, accepted_values and relationships are cheap and precise, and every project should have them. They also pass a customer called Mickey Mouse, a return coded late whose comment says "the box was crushed flat", and a support ticket that says "send the refund to jasperbakker92 at gmail dot com". Every one of those rows has the right type, a valid code and a non-null value. Each one is still wrong, and the only way to see it is to read it.

The usual next step is a regex test, which works right up until the data is written by people. So I tried a third option: write the test as an English sentence, and let a small model return the probability that each row fails it. Then I scored it honestly, against a hidden answer key and against the regex tests I'd have written anyway.

This is the fourth demo in my Jev demo series. The code, the data, the answer key and every logged run are public in jev-demos↗. The scorecard below replays those logged runs, so you can move the threshold yourself.

The test, as written#

This is the whole interface. A jev_expect test sits in schema.yml next to the structural tests it complements:

- name: comment
  data_tests:
    - not_null
    - jev_expect:
        name: returns_comment_matches_reason_code
        arguments:
          fails_if: "The customer's `comment` describes a different main reason for the return than `reason_code` (damaged, wrong_item, late, changed_mind, other)."
          context: [reason_code]
          threshold: 0.8
          criteria:
            "true": "The stated main reason plainly belongs to another code"
            "false": "Consistent with the code, or too vague to tell; code 'other' fits anything unusual"
        config: {severity: error, store_failures: true, tags: [semantic]}

fails_if is the defect, phrased so that "yes" means the row fails. context lists the other columns the model sees next to the tested one: here the comment is only wrong relative to its reason_code. threshold is the probability at which a row gets flagged. criteria is a short rubric for true and false, and it's where you encode the edge cases you already know about ("code other fits anything unusual"). The rest is plain dbt: severity, store_failures and tags behave exactly as they do on any other test.

The project has four of these, on a jaffle_shop with 1,300 rows under test:

How it's wired#

There's no new dbt feature involved, just a UDF and a generic test.

1. dbt-duckdb loads a plugin (configured in profiles.yml) that registers a jev_noul(state, question) Arrow UDF on the DuckDB connection. Jev is TypeSafe↗'s small judgment model. It returns a typed value (here a probability that a statement holds) rather than generated text, which is what a test needs.
2. The jev_expect macro compiles each test block into one jev_noul call per row. The state is a JSON object holding the tested column plus its context columns. The question is the fails_if sentence plus the criteria. It sits inside a materialized CTE, so each row's probability jev_p is computed exactly once, and the test returns where jev_p >= threshold.
3. The UDF batches rows by question, dedupes identical states, sends the rest under a concurrency and rate limit, and caches every judgment in SQLite. The cache key includes the model alias, so a model update means a fresh cache.
4. dbt test --select tag:semantic runs the four tests. store_failures: true writes every flagged row into the audit schema, which is exactly what you'd want to query when a test goes red.

One practical note: this needs dbt-core, not dbt Fusion. Fusion is a from-scratch Rust engine and doesn't load Python adapter plugins, so it can't register the UDF. The project pins dbt-core 1.12.5 with dbt-duckdb 1.11.0.

Scoring it honestly#

A data test that "seems to work" on a few rows you picked yourself proves nothing. So the demo has three layers of honesty built in.

• A hidden answer key. eval/golden_defects.csv labels every planted defect, and every hard negative: a row built to look wrong while being fine. The surname Test. A delivery-area postcode in a ticket. A sarcastic 1-star review that is correctly rated 1 star. Anything not in the key is clean. The dbt project never reads this file; only the scoring script does.
• A real baseline. Each semantic test has a regex/keyword twin in tests/baseline/, written the way I'd write it in a real project: name blocklists and digit checks, keyword-to-reason-code maps, a sentiment lexicon, IBAN, phone, email and postcode patterns. It runs in the same dbt test and stores its failures the same way.
• A gate, fixed up front. Jev had to reach precision and recall ≥ 0.85 on every test and beat the regex baseline's F1 on every test. No wording, threshold, data or answer-key changes were allowed to get there. The starting wording passed as written.

Here's the official run (live, pack=1, 2026-09-24):

That's 1,300 judgments in 1,057 requests, about 54 seconds wall time (bounded by the 1,200 requests-per-minute limit) and $0.017 for the lot. PII precision was 0.93 in an earlier pack=1 run and 1.00 in this one: one hard negative sits right at the threshold, so the honest number is a range, 0.93–1.00.

Where regex breaks, row by row#

The table is the summary. The rows are the argument. Every example below is from the dataset, with the probability Jev gave it at pack=1.

Names. The regex blocklist has test, foo, bar and co in it, because that's what fake names look like. It flags Frank Bar, Mei Foo and Miguel Co, all real surnames (Jev: 0.26, 0.18, 0.13). It misses Mickey Mouse (0.93), Donald Duck (0.83), lorem ipsum (0.97) and First Last (0.96), because none of them contains a blocked word, a digit or an @.

Returns. Keyword maps can't read negation. "not damaged or anything, just completely the wrong toastie in the box" is coded wrong_item, which is correct, but regex sees "damaged" and flags it (Jev: 0.04). "the ham & cheese jaffle wasn't wrong or broken, just took forever, nearly 68 minutes" is correctly coded late (0.05). Meanwhile "this has bacon in it and i ordered the vegetarian option", coded late, contains no keyword regex associates with wrong items, so it passes. Jev flags it at 0.93.

Reviews. Sarcasm is lexicon kryptonite. "Great, cold again. Love that for me." with 1 star reads as positive to a word list, which flags it as a mismatch (Jev: 0.09). "Genuinely never had a bad order from this place" with 5 stars trips on "never" and "bad" (0.02).

PII. This is the one that should worry you. Regex flags every ticket asking "does postcode 3512 CT fall within your delivery zone", which isn't personal data (0.27). And it misses "send the refund to jasperbakker92 at gmail dot com" (0.98) and "it's the yellow house at number 42 on the Dorpsweg in Utrecht now" (0.96). Those are exactly the ways people write personal data when a form won't let them.

Across all four tests there isn't a single defect that regex catches and Jev misses at pack=1. Open the scorecard, pick a test, and the "only regex catches" count reads zero. Now drag the threshold down.

The threshold is a product decision#

Every test has a threshold, and the scorecard shows why it's worth thinking about rather than defaulting to 0.5. On the PII test, drop it to 0.2 and six hard negatives start failing: postcodes, and the shop's own phone number. Push the names test to 0.9 and you start missing cartoon characters. The probabilities rank rows very well. In the benchmark every request layout kept AUC ≥ 0.995 on every test, so real defects almost always score above clean rows. Where exactly you cut is a trade between a noisy test and a leaky one. That's your call, and it belongs in schema.yml where a reviewer can see it.

Packing: the part that almost went wrong#

At one row per request, the project makes 1,057 requests. That's fine for a demo and slow for a warehouse. The obvious fix is to pack several rows into each request. The first attempt put all the packed rows into one shared state and asked a separate question about each. Precision held, but recall on the returns test fell off a cliff: 1.00 at pack=1, 0.42 at pack=32.

Returns is the test that relates two fields, the comment and its code. With 31 other records sitting in the same state, every one of them is a distractor, and the model has to keep each comment bound to the right code. Row 44 is the clean example. "The box was crushed flat and the banana nutella jaffle inside was falling apart", coded late, scores 0.88 alone and 0.62 in a pack of 32, just under the 0.8 threshold. Press packing collapse in the scorecard to see all seven misses.

The fix was to move each record into its own question and leave the shared state empty (the nested layout, now the default). A question can't see another question's record, so answers stop depending on pack size. Both gated runs pass: pack=32 in 35 requests and pack=64 in 18, about two seconds either way, $0.006 instead of $0.017. Row 44 goes back to 0.86. The full write-up↗ compares six layouts across 23 live runs. One finding surprised me: even an innocuous shared note like "Data-quality check…" pushed borderline hard negatives over the line. An empty state is the only neutral state.

The general lesson applies to any model you batch, not just this one. Batching changes the question the model is answering. Measure it row by row against the unbatched run before you trust the speed-up.

What it gets wrong, and what this doesn't show#

The two remaining errors at pack=1 are honest ones, and I didn't tune them away:

• It misses Pietje Puk (0.45), the Dutch equivalent of "John Doe". That's culturally specific, and fair enough.
• It flags Anna Test (about 0.9 across runs). Test is a real surname. The regex flags her too.

And the limits, stated plainly:

• The data is synthetic, generated by a seeded script (make_seeds.py, seed 42). The same script planted the defects and wrote the answer key, so the hard cases are the ones I thought of. Real support tickets will find cases I didn't.
• The baseline is regex, not another model. This shows a semantic test beats the keyword tests you'd otherwise write. It does not show how a general-purpose LLM would do on the same rows, at what latency or cost. That comparison is a benchmark I haven't published yet, so I'm not making the claim.
• Four tests, 1,300 rows. Treat the numbers as "the approach works on realistic-looking data", not as a leaderboard.

What I'd actually do#

Keep every structural test. They're free, exact and fast. Add a semantic test only where a column carries meaning a human would have to read to validate: free-text fields next to a code, names, comments, anything that might carry personal data. Write the fails_if sentence as the defect, not as the rule, and put the known edge cases in criteria. Before the test gates anything, label fifty rows by hand, including hard negatives you deliberately tried to fool it with, and pick the threshold from that. Run it with store_failures so the flagged rows are queryable. When you batch for speed, keep each record in its own question. It's the same split I use everywhere, laid out in how I build AI-native: code for everything with a right answer, a model only for the judgment that remains.

The rest of the series puts the same kind of judgment into other tools data people already use: semantic SQL in DuckDB, a VISUALIZE clause that picks the chart, and a slightly less serious LinkedIn post grader. The series overview has all four. If you run dbt on DuckDB and want to try it, the demo folder↗ runs offline in a clearly labelled simulated mode without an API key.

Disclosure: I'm an independent user of Jev with no commercial relationship with TypeSafe. The numbers here come from logged live runs; the simulated mode's output is never quoted.