graincheck

Catch metric logic errors before they reach production.

graincheck is a deterministic analytical-SQL integrity layer. It maps a project's grain, joins, aggregations and declared dbt metadata to metric-level risk — then names which reported numbers each risk affects, what evidence the claim rests on, and the one query that settles it.

It never executes anything, and it does not prove correctness. A finding is a claim to verify, not a verdict. Every one of them carries three ratings that are never merged:

Impact	how much it would matter if this is real
Evidence	what kind of thing establishes it — structural, metadata, heuristic, intent, runtime
Confidence	how far it should be trusted before anyone acts

Two things graincheck refuses to do: report a model it could not read as clean, and print one accuracy number across rules that behave nothing alike. diagnostics/rule_precision.py publishes precision per rule; docs/FAILURE_MODES.md publishes each rule's known false positives.

pip install .            # or: pip install sqlglot, to run from this directory

# scan a project
python -m graincheck scan ./target/compiled --manifest target/manifest.json \
       --dialect snowflake --report review.html

# walk through one model
python -m graincheck explain fct_customer_revenue --path ./target/compiled

The problem it addresses

A model that has been wrong since the day it was written has a stable row count, a unique primary key, passing tests, a smooth history with no anomaly, and no diff because nobody is changing it. It runs clean and returns a plausible number.

graincheck reads one query at a time, with no history and no baseline, and asks whether the SQL computes what its name claims.

Where it sits alongside existing tooling

Most data-quality and observability systems operate on data behavior, expected properties, or change detection. graincheck focuses on static analytical SQL risk before execution. These are complementary, not competing.

Tool class	Operates on	Examples
Observability	data behavior over time	Monte Carlo, Anomalo, Soda, Elementary
Diff / impact analysis	change between versions	Datafold, Recce, SQLMesh, audit-helper
CI linting and static checks	SQL text and structure	SQLFluff, Altimate, dbt-project-evaluator
Declarative tests	properties someone anticipated	dbt tests, Great Expectations
graincheck	analytical semantics of a single query	—

Some of these overlap with graincheck. Altimate's dbt-tools ships static checks in the same territory, including a fan-out check; SQLFluff covers style and structure. graincheck's specific contribution is a small set of checks for aggregation semantics — which aggregates a given join actually multiplies, CTE grain resolution, additivity, and the severity/evidence separation described below. It is a focused addition to this category, not a replacement for it.

On dbt-project-evaluator's "Model Fanout": that rule means a parent with 3+ leaf children in the DAG. It is not join fan-out and does not read model SQL. Different phenomenon, same word.

Three ratings, never collapsed into one

Conflating these is how static analysers lose their audience.

	Question it answers	Values
Impact	How much would it matter if this is real?	high · medium · low
Evidence	What kind of thing establishes it?	structural · metadata · heuristic · intent · runtime
Confidence	How far should it be trusted before anyone acts?	verified · high · medium · low

• structural — the syntax tree alone establishes the pattern. No assumption about your data or naming.
• metadata — established by the SQL plus your declared dbt uniqueness tests, CTE grain, or a resolved surrogate key. Exactly as strong as that metadata is.
• heuristic — inferred from column naming. A badly named additive column will be flagged; a well-named non-additive one will be missed.
• intent — whether this is wrong depends on what the metric is supposed to mean. Only a human can settle it.
• runtime — measured against your data by the verification query. The only level that is not an inference.

HIGH / HEURISTIC / LOW is a normal and useful combination: it would matter a lot if real, it rests on a column name, and nobody should act on it without reading the model. Confidence is capped per rule — JOIN_FANOUT can reach high, NON_ADDITIVE_SUM cannot — and a fan-out where the relation simply has no test declared is reported at medium, because absence of evidence is not evidence of a defect.

Checks

Impact is how much it would matter; the confidence ceiling is how far a finding from this rule can ever be trusted. Measured precision per rule is in docs/FAILURE_MODES.md, with each rule's known false positives written out.

Rule	Impact	Evidence	Conf. ceiling	Finds
JOIN_FANOUT	high	metadata	high	An aggregate across a join whose key is not tested unique on the joined side
FANOUT_INSIDE_CTE	high	metadata	high	An aggregate over a CTE whose rows were already multiplied by a join inside it, one or two steps earlier, where there was no aggregate to flag
JOIN_KEY_CASE_MISMATCH	high	structural	medium	A join key UPPER()/LOWER()-cased on one side and read as-is on the other, traced across every model the column passes through
OUTER_JOIN_FILTER_IN_WHERE	high	structural	high	A LEFT JOIN silently converted to INNER by a WHERE predicate
AVG_OF_RATIOS	high	structural	medium	AVG(a/b) where the business means SUM(a)/SUM(b)
IMPLICIT_CROSS_JOIN	high	structural	high	A comma join with no condition. An explicit CROSS JOIN is noted at low/intent; UNNEST / LATERAL is not a cross join at all and is ignored
NON_ADDITIVE_SUM	high	heuristic	low	SUM() of a rate, ratio, percentage or margin
COUNT_STAR_ACROSS_JOIN	medium	structural	medium	COUNT(*) counting result rows rather than entities
SEMI_ADDITIVE_SUM	medium	heuristic	low	SUM() of a balance or level across time
BETWEEN_ON_TIMESTAMP	medium	heuristic	low	Inclusive upper bound dropping the final day
UNGUARDED_DIVISION	low	structural	low	Division with no NULLIF guard
DISTINCT_MASKS_FANOUT	low	intent	medium	SELECT DISTINCT papering over a grain problem

Twelve rules, deliberately. The roadmap is to make these trustworthy, not to reach a hundred — and five of them (JOIN_FANOUT, OUTER_JOIN_FILTER_IN_WHERE, COUNT_STAR_ACROSS_JOIN, AVG_OF_RATIOS, NON_ADDITIVE_SUM) are the ones being made excellent first.

diagnostics/rule_precision.py grades every rule against five kinds of case — obvious positives, borderline positives, obvious negatives, near-miss negatives, and real-world examples — and prints precision and recall per rule. It deliberately prints no total: a rule with 100% precision over four cases and one with 90% over forty are not the same claim, and one average hides both. A rule with no near-miss negatives is marked UNTESTED however good its score looks. Building it found three real false positives that reading the code had not: SUM(rate_card_amount), SUM(balance_change) and SELECT DISTINCT <one column> over a join.

It reads the whole project, not one file at a time

Three of the four defects found by hand in Fivetran's dbt_shopify were invisible to a checker that looks at one file in isolation, and finding them by hand is what produced graincheck/project.py:

• Grain across select * from ref(...). Every dbt model opens with orders as (select * from {{ ref('shopify_gql__orders') }}). That CTE has no GROUP BY, so a single-file view can only say "grain unknown" and report every join to it. The grain is knowable: the staging model three hops upstream tests a surrogate key, the hash's inputs are (order_id, source_relation), and every model in between passes the rows through. graincheck now follows that chain — through renames (id as order_id), through GROUP BY, through a row_number() = 1 or QUALIFY de-duplication, through a pass-through macro model — and reports the provenance in the finding ("inherited: tested unique in stg_shopify_gql__order"). Fifteen medium findings on dbt_shopify were this, and every one of them was noise.
• Multiplication introduced in a CTE that does not aggregate (FANOUT_INSIDE_CTE). The join that breaks the grain is frequently not in the same SELECT as the aggregate it corrupts. Every join in the aggregating step can be correct while a plain CTE two steps earlier already repeated the rows.
• A join key normalised on one side only (JOIN_KEY_CASE_MISMATCH). upper(code) in three staging models, plain code in a fourth, and a join between them five models later. Nothing about a single file reveals it; the column has to be traced back to where its case is decided.

Everything it cannot establish stays unknown and silent: an unreadable macro model, an ambiguous select * over several relations, a UNION whose branches disagree, a PIVOT. That is the whole design — a lineage guess that turns an unproven join into a proven one would delete a true finding, which is worse than saying nothing.

Reviewed exceptions, not ignores

The first real project will contain a SUM(customer_score) that is additive on purpose. If the only available answer is "turn the rule off", the knowledge of why it is fine dies with the conversation.

# graincheck.yml
graincheck:
  exceptions:
    - rule: NON_ADDITIVE_SUM
      model: fct_customer_scores
      reason: score is a points total, not a rate — additive by design
      owner: analytics-platform
      expires: 2027-01-01      # optional

A suppressed finding is moved, not deleted: it reappears under Reviewed exceptions with the reason and the owner. An exception with no reason is refused. One that matches nothing this run is reported as STALE, and one past its expires date stops suppressing. That turns an ignore list into a small piece of governance history.

Six measured case studies

examples/case_studies.py executes both the flagged query and the corrected one for each pattern and measures the difference. Nothing below is asserted — it is all computed at run time.

#	Pattern	Question asked	Reported	Actual	Error
1	Join fan-out inflates a count	How many orders per customer?	113	99	+14.1%
2	WHERE converts LEFT JOIN to INNER	How many customers, excluding returns?	60	100	−40.0%
3	Average of ratios	What is our average order value?	1,646.08	1,688.89	−2.5%
4	Summing a stored percentage	What was our margin this year?	1,092	39.81	+2643.3%
5	BETWEEN on a timestamp	What did we take in March?	23,348.32	24,127.93	−3.2%
6	Summing a balance across time	What total balance do we hold?	8,164,500	692,750	+1078.6%

Cases 1–3 run on dbt Labs' published jaffle_shop seed CSVs. Cases 4–6 run on generated data, because no public seed set contains a stored margin percentage, a timestamp column or a monthly balance snapshot; each case prints its construction in full.

How to read the detection rate. These six cases were chosen to illustrate the six patterns graincheck checks for, so detecting all six is close to tautological and is not a measure of recall on unseen code. What the file establishes is the part that is not obvious: that each flagged shape corresponds to a real, measurable error in an executed query, and how large that error is.

Case 1 in full — the one worth reading

The query an analyst writes when asked "revenue and order count per customer":

SELECT c.id AS customer_id,
       SUM(p.amount) AS total_revenue,
       COUNT(*)      AS order_count
FROM raw_customers c
JOIN raw_orders   o ON o.user_id  = c.id
JOIN raw_payments p ON p.order_id = o.id
GROUP BY 1

It runs without error and returns entirely normal-looking numbers. 13 of the 99 orders carry more than one payment row, up to 3.

graincheck, before anything is executed:

[HIGH  /metadata  ] JOIN_FANOUT             COUNT computed across a join to raw_payments on ['order_id']
[MEDIUM/structural] COUNT_STAR_ACROSS_JOIN

Executed:

total revenue   flagged 167,200.00   corrected 167,200.00   identical
order count     flagged        113   corrected        99    OVERSTATED 14.1%

The precision is the point. SUM(payments.amount) is correct despite the fan-out, because each payment row still appears exactly once. COUNT(*) counts result rows and is wrong by 14%. graincheck names only the COUNT — a tool that flagged both equally would be crying wolf on half its findings.

That distinction did not exist until the script was run against real data. It lives in _agg_is_multiplied: after a fan-out, every result row corresponds to one row of the fanning table, so an aggregate over that table's columns sees each row once, while an aggregate over any other table has its rows repeated. The first version of the rule used join order instead — "multiplied if it entered before the fanning table" — which was an artifact of this example, where every other table happens to precede it. Reading Fivetran's dbt_shopify found the other shape and corrected it.

Run against real production dbt code

Five Fivetran dbt packages — real analytics models deployed at thousands of companies:

Package	Models	Examined	Findings	High	Skipped: no SQL in the file	Skipped: parse error
dbt_shopify	264	147	29	6	105	12
dbt_netsuite	108	71	1	0	27	10
dbt_salesforce	28	21	10	0	0	7
dbt_hubspot	205	83	4	0	70	52
dbt_stripe	69	21	12	0	25	23

Read the "Examined" column before the "Findings" column. A file that was not examined is reported, never silently passed, and graincheck tells you to run dbt compile and scan target/compiled/ for a complete review. On the compiled output of jaffle_shop, 25 of 25 models are examined.

The "no SQL in the file" column is the larger one, and it is there because of a defect found while verifying something else. A Fivetran staging model is often nothing but {{ fivetran_utils.union_data(...) }} — the SQL lives in the macro, and de-rendering leaves an empty string. Those files used to return no findings, no error: counted as parsed, filed as clean, with not one line of their SQL read. Across a 1,320-model production corpus that was 402 models (30%). They are now reported as unexamined with the reason, which is why these counts are lower than earlier versions of this README claimed. Lower and true beats higher and wrong.

De-rendering used to be the binding constraint on everything this tool could say. Across a 1,193-model corpus of thirteen public dbt packages the parse rate was 10.8% — 1,064 files unexamined — because every {{ ... }} expression was replaced with the scalar 1, without regard for where it sat. That turned cast(x as {{ dbt.type_string() }}) into cast(x as 1), which is invalid in every dialect, and partition by email {{ partition_by_source_relation() }} into two expressions with no comma between them. Substitution is now position-aware — type macros become a type, column-list macros become *, clause-continuation macros become nothing, and {{ dbt_utils.group_by(n=3) }} becomes a real GROUP BY 1, 2, 3 (which the CTE-grain logic then reads). A ref() buried inside a larger expression — from {{ ref('a') if metafields_enabled else ref('b') }} — now yields the relation name rather than a bare 1 in a FROM clause, and an unresolvable macro in a relation position becomes an identifier instead of a guaranteed ParseError.

That change is what surfaced three further instances of the same fan-out pattern in dbt_shopify, including two in the REST model family that had never been parsed at all.

sqlglot is permissive in the same direction: !!! not sql parses happily into a chain of NOT expressions, and a de-rendered macro file often parses into something that is not a query. Those files used to come back as parsed, zero findings too. The scanner now checks that a parsed file actually contains a query node and reports it as unexamined when it does not.

Running on real code also produced the scanner's most important fix. The first version treated every CTE as an untested table and produced 56 fan-out findings on one package, nearly all noise. It now resolves CTE grain: a CTE built with GROUP BY customer_id is one row per customer by construction, and joining on that key is silent. Findings on dbt_shopify fell from 67 to 31, and the survivors are specific — for example a join to a CTE grouped by [kind, order_id, source_relation] on only [order_id, source_relation], where an order with several transaction kinds multiplies the aggregate.

That finding is a question for a maintainer, not a proven bug. Treat every finding that way.

The false positive that was caught before it was sent

Hand-checking the high-severity findings against the actual Fivetran source found one that was simply wrong. In int_shopify_gql__discounts_abandoned_checkouts.sql:

LEFT JOIN discount_application
    ON abandoned_checkout_discount_code.code = discount_application.code
   AND abandoned_checkout_discount_code.source_relation = discount_application.source_relation
WHERE COALESCE(discount_application.value_type, '') != ''

graincheck flagged it as a LEFT JOIN neutralised by a WHERE clause, with the reason "unmatched rows have NULL there and fail the comparison." That reason is false. COALESCE turns the NULL into '', the comparison evaluates to FALSE rather than NULL, and the row is dropped because the author decided it should be. The COALESCE is the author explicitly saying I know these are NULL and I have handled it.

The outcome resembles an INNER JOIN, but reporting it would have been worse than reporting nothing: a maintainer who catches a wrong reason stops trusting every other finding in the document. The rule now stays silent when the outer-joined column is wrapped in COALESCE, IFNULL, NVL, IIF or a CASE.

Checking the same rule from the other side exposed the opposite bug: exp.In is not an exp.Binary node in sqlglot, so LEFT JOIN o ... WHERE o.status IN ('a','b') — the same defect, written the way analysts most often write it — was never detected at all. Both directions now have tests.

This is what the "make ten rules trustworthy rather than reach a hundred" roadmap actually looks like in practice: one afternoon of reading real SQL, one false positive removed, one whole class of true positives gained.

coverage — how much of this can you actually defend

$ python -m graincheck coverage ./models

PROJECT INTEGRITY

  Models found                  264
    fully examined              147
    partially examined            0   (a join in these could not be resolved)
    not examined at all         117   (unparsed, or all SQL inside macros)

  Joins onto a relation          85
    eligible (have a key)        83
      verified                   17
      not established            12   (a declared key exists and the join does not use it)
      unknown                    54   (no uniqueness test declared at all)
    no condition at all           2   (cross products)
    unresolved                    0   (onto a subquery)

  JOIN SAFETY COVERAGE        20.5%
  METRIC PATH COVERAGE        64.8%   (212 of 327 aggregate output columns)

  Join keys carrying the most traffic without an established grain:
      5 join(s)  [UNKNOWN]  stg_shopify__order on ['order_id', 'source_relation']
...

On dbt_shopify that is 17 of 83 join keys (20.5%) proven unique by a declared test, and 212 of 327 reported numbers whose join path is fully established.

The three-way split matters more than the percentage. unknown means no uniqueness test is declared at all — absence of evidence, and the key may well be unique. not established means the project does declare a key and the join uses a different one, which is the project contradicting itself. Collapsing those two into "bad" overstates what is known, and a reviewer who spots that stops trusting the rest of the report.

Both formulas are printed underneath the numbers, every run, because a coverage figure without its denominator can be made to mean anything.

A finding answers is this model wrong. This answers the question a lead asks before funding any of it: how much of what we join on has a test behind it, and how much is on trust?

The gap it measures is specific, and it is not an accident of any one project. dbt convention is to test uniqueness on a surrogate key in the output layer:

{{ dbt_utils.generate_surrogate_key(['id', 'source_relation']) }} as unique_key   -- tested unique

while every downstream model joins on the natural key — (order_id, source_relation) — usually through an intermediate model that carries no test at all. graincheck resolves the surrogate back through the column alias (id as order_id) so a test on unique_key correctly proves that join safe. What is left after that resolution is the real gap.

Across 20 published dbt packages (Fivetran, Velir, dbt-labs and others), 48 of 356 join keys — 13.5% — are proven unique by a declared test. The other 308 are assumptions nobody has checked.

(It was 48 of 334 until a de-rendering bug found on Windows was fixed: normalising line endings and correcting one substitution rule made four more models parse across the corpus, which added 22 join edges to the denominator without adding any proven keys. The measured gap got slightly worse because more of the code became visible, which is the direction this number should be expected to move.)

That number was produced three times before it was written here: once from the syntax tree plus a YAML parser, once from pure text regexes sharing no code with the first, and once by graincheck itself. The first two agreed on 259 of 263 joins they both found. Reconciling the four disagreements is what turned up three defects — a composite uniqueness test being flattened into a bag of column names, one malformed schema file silently deleting every test declared in it, and surrogate keys not being resolved at all. Before those fixes the same measurement read 9.7%, and an earlier flat-set version of it read zero. The two-implementation comparison is the only reason any of that was caught.

False comfort — the answer to "we already run dbt-project-evaluator"

dbt-project-evaluator asks does this model have a primary key test? graincheck asks is the key we join on proven unique? Those are different questions, and it answers its own correctly. The gap between them is measurable:

FALSE COMFORT

  12 join(s) land on a model that PASSES the project's primary-key test requirement while
  joining it on a key the project never proves unique.

      3 join(s)  shopify_gql__orders
              badge earned on : ['unique_key']
              joined on       : ['order_id', 'source_relation']
              in              : int_shopify_gql__daily_orders, int_shopify_gql__discounts_order_aggregates, …

The incumbent's rule is reimplemented from its own int_model_test_summary.sql — some column with both unique and not_null, or a combination test — so the comparison is against what it would actually say rather than a paraphrase.

--emit-tests — the patch, not just the report

Every competitor reports. None hands back the fix. The fix is completely determined by what was already measured:

  # 5 model(s) join `stg_shopify__order` on ['order_id', 'source_relation'] and nothing proves it unique.
  # Relied on by: int_shopify__inventory_level__aggregates, shopify__line_item_enhanced, …
  - name: stg_shopify__order
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - order_id
            - source_relation

Only ever a test, never a rewrite of a model. A test is safe whichever way the answer turns out: if the key is unique it passes forever and a guarantee the project was already relying on becomes explicit; if it is not, it fails on the next run and a silent fan-out becomes a loud one. Nobody's numbers change on the strength of a static tool's opinion.

--history — coverage as a trend

COVERAGE OVER TIME

  when                 commit      join safety  metric path  eligible  verified
  2026-09-10T09:14:02  a91f3c0           63.0%        70.0%       100        63
  2026-09-17T16:15:27  03e91d7           61.0%        68.0%       110        67

  Since the previous run:
    join safety coverage   61.0%  (-2.0 ↓)
    join edges             +10   of which unproven: +6

Test coverage became a number teams manage on the day it became a line on a chart. One appended JSON Lines row per run, append-only — a coverage number that can be edited after the fact is not one anyone should trust.

explain — one model, read out loud

$ python -m graincheck explain fct_customer_revenue --path ./models

MODEL GRAIN
  one row per customer_id, name

JOIN PATH
  FROM      stg_orders AS o
  LEFT JOIN stg_customers AS c  ON customer_id
             -> safe. ['customer_id'] is tested unique on this table.
  LEFT JOIN stg_order_items AS oi  ON order_id
             -> CAN FAN OUT. This table is tested unique on ['order_item_id'], not the join key.

AGGREGATES PRODUCED
  revenue = SUM(o.order_total)   margin = SUM(o.gross_margin_pct)
  avg_margin = AVG(o.profit / o.order_total)   order_count = COUNT(*)

RISK
  1. [HIGH / STRUCTURAL] AVG_OF_RATIOS
  2. [HIGH / METADATA]   JOIN_FANOUT
  3. [HIGH / HEURISTIC]  NON_ADDITIVE_SUM
  ...
LIKELY EFFECT / VERIFY / RECOMMENDED REMEDIATION follow, per finding.

Tested beyond the packages it was built on

The rules were developed against Fivetran's dbt packages, which is a real risk: a checker tuned on one publisher's house style can be quietly overfitted to it. So it is also run against eight unrelated projects — Velir's dbt-ga4, dbt Labs' snowplow and jaffle-shop-classic, Elementary's dbt-data-reliability, dbt-date, dbt-external-tables, dbt_metrics, and a community Spotify project.

That immediately found the worst false positive in the tool's history. dbt-ga4 flagged nine high-severity cartesian products, all of them this line:

FROM events, UNNEST(items)

UNNEST expands an array within each row. It is the most common idiom in BigQuery and it multiplies nothing. Nine alarms on a package's most ordinary code would have ended any engagement in a minute. UNNEST and LATERAL FLATTEN are now recognised as row-correlated expansions and ignored.

The same scan exposed a second bug directly beneath it: BigQuery's parser normalises FROM a, b to kind='CROSS', so the "an explicit CROSS JOIN means the author meant it" downgrade was excusing genuine comma-join mistakes — on the one dialect where nested data makes that mistake easiest to make. The check now reads the source text instead of trusting the parse tree's kind.

Across those eight projects the tool now reports 8 findings in total, none high-severity. That is the right shape for well-maintained code, and it is the number to watch: if a rule change makes it jump, the rule got noisier rather than smarter.

Stated limits

• These are checks, not proofs. A clean result means none of the checked patterns are present. It is not a statement that the model is correct.
• It does not execute anything. It can tell you a shape is risky; it cannot size the error without your data. That is what the verify line on each finding is for.
• NON_ADDITIVE_SUM, SEMI_ADDITIVE_SUM and BETWEEN_ON_TIMESTAMP read column names. They are marked heuristic evidence for exactly that reason.
• Jinja is de-rendered, not compiled. ref() and source() become table names; other expressions become a stand-in chosen from their position. On macro-driven projects most models cannot be examined from source — measured at 147 of 264 on dbt_shopify, where 105 models contain no SQL at all outside their macros. Scan target/compiled/ for a real assessment; on compiled jaffle_shop it is 25 of 25.
• A file that was not examined is reported, never silently passed. That includes the file that de-renders to nothing because its query lives in a macro — the case that used to be filed as clean.
• Join-key coverage is a statement about declared tests, not about your data. A key with no test may well be unique. The point is that nothing in the project says so, and a green dbt test run is not evidence either way.
• It cannot know intent. Whether a filter was requested is a question for a human. Those findings are marked intent evidence.
• An aggregate over the joined table's own columns is assumed safe. After a fan-out, each of that table's rows normally appears once, which is why SUM(payments.amount) is correctly left alone in the jaffle_shop example above. The assumption breaks when the join key is a non-key on both sides — customers c LEFT JOIN t ON t.region = c.region repeats every t row once per customer in that region, and graincheck stays silent. That case is structurally identical to the safe one; the only difference is whether the left side is unique on the join key, which is a property of the data rather than of the query. Flagging it would fire on every correct detail-to-parent join in a project, so the rule stays quiet and the gap is stated here instead. It is pinned by a named test.
• Cross-model lineage is textual, not compiled. The project index parses each model's de-rendered SQL, so a model whose query lives inside a macro is opaque to it, and a chain that passes through one stops there. Opaque is treated as unknown: no finding is produced from it either way.
• Functional dependencies are invisible. A CTE grouped by (date, feed_key, feed_name, org_name) is one row per (date, feed_key) if the names are attributes of the feed — but nothing in the SQL says so, so a join on (date, feed_key) is still reported. graincheck only reduces such a grain when the pre-aggregation rows are provably unique on a subset of the grouping columns.
• No moat is claimed. This is ~1,000 lines built on open-source sqlglot, and the taxonomy it uses is published. The value is in the precision of the checks and the quality of the review around them, not in anything that cannot be rebuilt.

Tests and the health check

python -m pytest tests -q                              # 375 unit tests
python diagnostics/healthcheck.py                      # the full adversarial pass
python diagnostics/healthcheck.py --quick              # skip the corpus regression
python diagnostics/healthcheck.py --corpus ~/dbt-repos # regress against real projects

Every rule is tested twice: once on SQL that must trigger it, once on realistic SQL that must not. The negative cases matter more than the positive ones — one of them is what caught the SUM/COUNT distinction above.

diagnostics/healthcheck.py is the gate before handing a report to anyone. It exits non-zero unless all sixteen checks pass, and it is adversarial rather than confirmatory — it tries to break the tool:

	Check	What it does
A	Imports	Byte-compiles every file, imports every module, confirms the documented public API exists
B	Lint	ruff (correctness, bugbear, comprehension, return, perf) across the whole repo
C	Unit tests	The suite plus line coverage, failing below 90%
D	Fuzz	~50 hostile inputs plus 600 seeded random mutations; nothing may raise
E	Dialects	11 dialects × 48 fixtures = 528 combinations
F	False positives	18 known-correct queries that must produce zero findings
G	True positives	Every rule must still fire on the defect it exists to catch
H	Determinism	5 runs × 4 output formats, byte-identical
I	Report integrity	HTML well-formedness, and 6 injection payloads across 5 surfaces
J	CLI	26 invocations: every flag, all three subcommands, exit codes, failure paths
K	Performance	Throughput, and a ceiling on worst-case single-file time
L	Corpus regression	35 real public dbt projects diffed against a locked baseline
M	Per-rule precision	Every rule graded on its own case set; any false positive or false negative fails the build, and a rule with no near-miss negatives fails as untested
N	Install	pip install into a fresh venv, then run the console script from outside the repo
O	Console encoding	Every subcommand under cp437, cp1252, ascii, latin-1 and cp850, strict
P	Unseen repositories	The whole CLI end-to-end on real third-party dbt projects: no traceback, no hang, no empty output

Check D is there because SQL in the wild is hostile: files saved as Latin-1, macro output that de-renders into nothing, a 200,000-character string literal. Check F is the one that decides whether anyone keeps using the tool — a scanner that flags correct models gets ignored within a day. Check L holds a content fingerprint of every finding on 35 real projects, not just a count, so a change that moves findings around while keeping the total the same still fails.

Why N, O and P exist, and what that says about the twelve above them

For a long time this health check reported HEALTHY on every run while real defects kept being found by hand. That was structural, not bad luck. Checks A–M all run python -m graincheck from inside this directory, on Linux, in a UTF-8 locale, against SQL written here. So they can only ever answer one question: did I break something I already knew about? They are good at that and they are blind to everything else.

A stress pass outside those assumptions, plus the first run on Windows, found SEVEN defects, none of which any check above could see:

	What was wrong	What a user would have seen
packaging	pyproject.toml declared no packages, so setuptools' flat-layout discovery found both graincheck and diagnostics and refused to guess	pip install failed on every Python version. It had never once worked
dependency	PyYAML is imported by three modules, was never declared, and each ImportError was swallowed with return {}	A clean install read no schema.yml and said nothing. On a four-line project it turned 0 findings into 1 high-impact JOIN_FANOUT — a confident wrong answer
encoding	cmd.exe's default US codepage is cp437, which has no em dash, and every message here contains one	UnicodeEncodeError and exit 1 on a correct project, before a single finding printed
dialect	--dialect postgress does not raise; every model then fails to parse	"Found 0 issues", exit 0 — a green CI build on a project that was never read
manifest	a --manifest that is not JSON	JSONDecodeError traceback, losing a finished scan
interrupt	no KeyboardInterrupt handler	a twenty-line traceback when you press Ctrl-C on a ninety-second scan
line endings	a fixed-width lookback window in _jinja_substitute, where a CRLF costs one character of context	The examined/unexamined split depended on whether git converted the line endings. 18 models parsed on Linux, 20 on Windows, same commit

The dialect one is the worst thing this tool has ever done. graincheck's whole argument is that a silent pass is more dangerous than a loud error, and a one-letter typo made graincheck itself produce the most convincing silent pass available: zero findings, exit zero. It now refuses to start.

All seven are fixed, each has a test, and N/O/P are ship gates so the class cannot come back. A new test walks the package's own imports and fails if any is missing from pyproject.toml, and another asserts that LF, CRLF and CR inputs produce identical findings.

Windows: verified on 19 September 2026, Python 3.12.4, Windows AMD64 — and it found a seventh defect that no amount of Linux testing would have produced.

dbt_stripe parsed 20 of 69 models on a Windows checkout where the same commit on Linux parsed 18. Same sqlglot version, same files. The cause: _jinja_substitute picks a stand-in for each Jinja block by reading a fixed-width window of the characters before it, and a CRLF spends two characters where LF spends one. When the character pushed off the end of that window was the comma ending the previous SELECT column, the substitution flipped from 1 to nothing — and 1 immediately followed by an identifier is a ParseError that costs the whole model.

Findings were identical, so nothing looked wrong. But "unparsed is unexamined, not clean" is this tool's central claim, and it had been quietly depending on whether git converted the line endings. derender now normalises them first, so every platform reads the same bytes. Fixing the substitution rule underneath it made both platforms parse those files: 21 of 69, better than either got before.

The other Windows failure was a test of mine, not the tool: it shelled out to head, which does not exist there. Rewritten to use Python.

Run it yourself with docs/WINDOWS_VERIFY.md or Verify-Graincheck.ps1.

What the project-wide pass changed (23 September 2026)

The whole-project lineage described above was not planned. It came from four defects found by hand in Fivetran's dbt_shopify after graincheck had already scanned it and said nothing about three of them.

What it now finds that it could not before — each one verified against data, on DuckDB, using the package's own integration-test seeds:

Model	Defect	Measured
int_shopify_gql__discounts_abandoned_checkouts	An un-aggregated CTE joins checkout codes to discount_application on code alone, so each checkout repeats once per ORDER that used the code	one checkout's 0.50 discount reported as 1.50; item total 15.50 as 46.50
shopify_gql__discounts, shopify__discounts	upper(code) on the order and checkout sides, plain code on the redeem-code side, joined on code	the same code stored lower-case: 0 orders and 0.00; stored upper-case: 1 order and 10.00
int_shopify_gql__inventory_level_aggregates	Order LINES joined to FULFILMENTS on order_id, so an order shipped in two parts repeats every line	quantity_sold 2 -> 4, subtotal_sold 17.99 -> 35.98 for one order with two fulfilments

What it stopped saying. The same pass removed more than it added, because most of what it learned was that a join it had complained about was provably safe:

Corpus	Findings before	After
33 public dbt packages	115	55
TEAMSchools/teamster (2,006 models)	86	70
cal-itp/data-infra (625 models)	87	55

Every single removal was adjudicated by hand before it was accepted — 60 of them across the corpus — and the strong-finding count on the two unseen repos did not drop: teamster stayed at 3, cal-itp went from 7 to 7 (two pivot-related false positives out, two newly-proven fan-outs in). Six separate classes of noise went with them: a row identifier (id, dcid, unique_key) treated as a business key; a PIVOT read as though it kept its source's grain; COUNT(*) flagged because of a join in an unrelated CTE; a SUM over a CASE condition's column name; the same finding printed eleven times for one date spine; and a column named in a GROUP BY that the input was already unique on.

Head-to-head against the other tools (21 September 2026)

Three other free tools shipped this same idea between July and September 2026: dblect, sqlsure and dbt-assay. A 24-model dbt project was built with real dbt-core + dbt-duckdb, with a known answer for every model, and run through graincheck, dblect 0.1.0 and dbt-assay 0.23.0. (sqlsure checks one SQL string against a hand-written semantic model, so it could not be run on a project the same way. Altimate's PR reviewer was not tested.)

	real fan-outs caught	false alarms on correct SQL	outer-join bugs caught	false alarms
graincheck, before this run	4 / 6	1 / 4	2 / 2	0 / 3
graincheck, after fixing what this run found	6 / 6	0 / 4	2 / 2	0 / 3
dblect 0.1.0	2 / 6	0 / 4	2 / 2	0 / 3
dbt-assay 0.23.0 (assay tests)	4 / 6	2 / 4	—	—

Read the second row with suspicion. graincheck was fixed against these exact cases, so 6/6 is a regression guarantee, not a measurement. The first row is the fair comparison. The set is 19 cases written by the author of this tool, and 7 of them are modelled on cases graincheck was already tuned for. The competitors' strongest features were not measured: dbt-assay's LLM tier and its --verify mode, which counts keys in the real data, and dblect's declared contracts.

What the run found in graincheck, all now fixed:

• Scanning a built dbt project counted target/ too. 24 models were reported as 88, and every finding appeared up to 3 times. Every earlier test ran on repos cloned from GitHub, where target/ is ignored.
• JOIN ... USING (key) was never checked. None of the 4 tools caught this case.
• A join onto (SELECT * FROM x) was never checked.
• A rank column pinned inside a GROUP BY was treated as an upstream de-duplication, which was the teamster staff_renewal_feed false positive, previously only downgraded.
• The coverage metric had its own copy of the join-key logic that never got the cal-itp COALESCE fix. The package contains no POSIX-only assumption I can find — every path goes through pathlib, every file read is explicit about encoding, the one subprocess call (git, for --history) is optional and swallows a missing binary — and the failure modes a Windows console produces are now covered by check O and by the Windows-codepage tests. But no part of this has ever executed on Windows. Treat it as untested there until someone runs it.

Before all of this, the health check had already paid for itself: on its first run it found four real defects, two serious — scan_path raised UnicodeDecodeError on a single Latin-1 file and aborted the whole scan, and files that parsed into non-queries were reported as examined and clean.

Licence

graincheck is released under the Business Source License 1.1, not an open-source licence.

You may, free of charge: run it on your own SQL, in development, in CI and in production pipelines inside your own organisation, and publish and act on its output for any purpose, including commercially.

You may not, without a separate commercial licence: offer it to third parties as a hosted or managed service, embed it in a product or service you sell, or build a commercial SQL-review service whose delivery depends on it.

On 28 September 2030 the licence converts automatically to Apache 2.0.

For a commercial licence, or if you are unsure which side of the line your use falls on, open an issue and ask.