Skip to content
Peter Calvanelli

Building in Public

Building Cardinal, Part 1: Checking SQL Without Running It

Peter Calvanelli

Every data team has the story. Revenue doubled overnight and nobody touched the revenue model. Someone added a second address to the customers table three models upstream, a join that used to match one row started matching two, and every sum below it counted twice. The pipeline was green. The tests passed. The number was wrong.

I’ve been building a tool that catches that before it merges. It’s called Cardinal, short for cardinality. It’s written in Rust, it’s in beta, and the source is public.1 This is the first in a series about building it in the open: what it does, why it’s built the way it is, and what goes wrong along the way. I’ll write the next parts as the work happens, not after.

Two different questions

dbt’s answer to the fan-out problem is a unique test.2 You declare that order_id is unique in a model, and after the model is built, dbt runs a query to confirm it. That’s a good answer to one question: did this table have duplicates today?

It’s a bad answer to a different question: can this table ever have duplicates? A test that passed on Monday passes because Monday’s data happened to be clean. The join that produces duplicates is still there, waiting for the first customer with two addresses. And the test runs after the build, on warehouse time, often nightly, so the feedback arrives hours after the pull request that caused it was merged.

The second question is the one a type checker answers. A type checker doesn’t run your program on today’s inputs and see if it crashes. It reads the code and proves that a certain class of crash can’t happen on any input. Cardinal is a type checker for row counts.

What it reads

Cardinal reads two files dbt already writes: the manifest, which holds every model’s compiled SQL, and the catalog, which holds column types. It needs nothing else.

From the manifest it learns each table’s key, the columns that identify one row. dbt projects already say this, in unique tests, unique_combination_of_columns tests, and primary_key constraints. Cardinal reads them all, plus one declaration of its own, grain, for tables that have no test yet.3

Then it turns each model’s SQL into a logical plan and walks it, operator by operator.4 At every join, filter, aggregate, window, and union, it works out three things about the rows coming out: which column sets are still keys, which columns depend on which others, and which columns are provably non-null. A join between orders and line items has one row per line, not per order. So after that join, shipping_fee is a value that belongs to the order but repeats per line. If something downstream sums it per day, the fee is counted once per line, and Cardinal says so:

error[G001]: a join repeats values that are then added up
 --> models/marts/daily_revenue.sql:4:24
  |
4 |     sum(line_amount) + sum(shipping_fee) as revenue
  |                        ^^^^^^^^^^^^^^^^^ shipping_fee has one value per (order_id)

That’s the core of it. Every finding is a statement about structure that holds for any data the tables could contain. There are a couple dozen finding codes so far: joins that fan out, filters and inner joins that can drop rows, sums of averages and distinct counts that don’t add,5 declared keys the SQL doesn’t actually guarantee, and incremental models whose unique key isn’t one.

Silence over guessing

The first design rule, and the one I expect to defend most, is that where Cardinal doesn’t know a key, it says nothing. Not a warning. Nothing.

A tool like this lives or dies on whether people trust its errors. A checker that guesses a key and then reports a fan-out based on the guess is a checker people turn off. So an unknown key produces exactly one finding, naming the cause and the models it blocks, and the rest stays quiet. A separate command ranks those causes by how many models each would unlock, so you fix the one that matters.

If you genuinely don’t know a table’s key, Cardinal will guess one from column names and join patterns. But it won’t use the guess. It writes the guess as a dbt test, dbt checks it against the real data on your schedule, and only guesses that pass get used, labeled as guesses. The one time the data gets consulted, it’s dbt doing the consulting, in the warehouse, where that question belongs.

Structure, not meaning

The first essay on this site was about meaning: what “active customer” is supposed to mean, and how a semantic layer drifts away from it. Cardinal is deliberately upstream of that problem. It checks whether revenue counts each order once, not whether revenue should include refunds. Those are different jobs, and they need different tools. But a definition can’t be right if the model underneath it fans out, so I think the structural check has to come first.

Where it stands

Cardinal started on November 3rd as a repository called Derive and got its real name the same afternoon. A week later it’s at beta 6, nine crates, and a CI setup where it checks pull requests against the artifacts from main and fails only on guarantees a change loses. Findings come out as plain text or as SARIF for GitHub code scanning,6 and a single comment silences one with a reason attached.

It’s fast, because there’s nothing to wait for. On a project of twenty to forty compiled models, a full check takes about 300 milliseconds, which is less time than a warehouse takes to say hello. That’s fast enough to run on every commit, and there’s a watch mode that runs it on every save.

It’s also in beta, and I want to be plain about what that means. Output, flags, and config will change. Its user base is one person, and I’m biased, because I wrote it. Every bug report so far has been filed by the maintainer, triaged by the maintainer, and closed by the maintainer, usually with a note saying “fair point.” If you try it and it breaks, you’d double the user count and I’d be glad of the company.

The next part is about the analysis itself: how keys, dependencies, and non-null facts move through a query plan, and the evidence chain that lets every finding explain why it fired. After that, the key-guessing handshake with dbt, and then what happened when I pointed it at projects that weren’t mine.

Footnotes

  1. github.com/PCalvanelli/cardinal. Beta; for now it installs with cargo from a clone. Every finding code has a page under docs/explain/ with a broken and a fixed example. ↩

  2. dbt’s docs on data tests. unique and not_null are the built-in ones most relevant here. unique_combination_of_columns comes from dbt_utils. ↩

  3. A unique test with a where filter only covers some rows, so Cardinal doesn’t count it as a key. The full list of what it reads is in docs/configuration.md. ↩

  4. The SQL front end is Apache DataFusion’s parser and planner, run without its optimizer. Cardinal wants the plan the author wrote, not the one the engine would execute, because the findings point back at lines in the model. ↩

  5. The G008 finding. Summing daily distinct user counts into a monthly figure counts a user once for every day they were active; summing two averages gives a number that means nothing. Cardinal spots these from the SQL alone. ↩

  6. SARIF is the format GitHub’s code scanning reads, so findings show up as annotations on the pull request. ↩

Tags

cardinaldbtsqlrust