Metadata-Version: 2.4
Name: graincheck
Version: 0.1.1
Summary: Find analytical SQL that runs clean and returns the wrong number.
Author: Prem Burugupally
License: Business Source License 1.1
        
        License text copyright (c) 2017 MariaDB Corporation Ab, All Rights Reserved.
        "Business Source License" is a trademark of MariaDB Corporation Ab.
        
        -----------------------------------------------------------------------------
        
        Parameters
        
        Licensor:             Prem Burugupally
        
        Licensed Work:        graincheck
                              The Licensed Work is (c) 2026 Prem Burugupally
        
        Additional Use Grant: You may use the Licensed Work to analyse SQL and to
                              produce, publish and act on its output for any purpose,
                              including commercially. You may run it in development,
                              in continuous integration and in production pipelines
                              inside your own organisation.
        
                              You may not provide the Licensed Work to third parties
                              as a hosted or managed service, embed it in a product
                              or service you offer to third parties, or offer a
                              commercial SQL-review service whose delivery depends on
                              the Licensed Work, without a separate commercial licence
                              from the Licensor.
        
        Change Date:          2030-09-28
        
        Change License:       Apache License, Version 2.0
        
        -----------------------------------------------------------------------------
        
        Terms
        
        The Licensor hereby grants you the right to copy, modify, create derivative
        works, redistribute, and make non-production use of the Licensed Work. The
        Licensor may make an Additional Use Grant, above, permitting limited
        production use.
        
        Effective on the Change Date, or the fourth anniversary of the first publicly
        available distribution of a specific version of the Licensed Work under this
        License, whichever comes first, the Licensor hereby grants you rights under
        the terms of the Change License, and the rights granted in the paragraph
        above terminate.
        
        If your use of the Licensed Work does not comply with the requirements
        currently in effect as described in this License, you must purchase a
        commercial license from the Licensor, its affiliated entities, or authorized
        resellers, or you must refrain from using the Licensed Work.
        
        All copies of the original and modified Licensed Work, and derivative works
        of the Licensed Work, are subject to this License. This License applies
        separately for each version of the Licensed Work and the Change Date may vary
        for each version of the Licensed Work released by Licensor.
        
        You must conspicuously display this License on each original or modified copy
        of the Licensed Work. If you receive the Licensed Work in original or
        modified form from a third party, the terms and conditions set forth in this
        License apply to your use of that work.
        
        Any use of the Licensed Work in violation of this License will automatically
        terminate your rights under this License for the current and all other
        versions of the Licensed Work.
        
        This License does not grant you any right in any trademark or logo of
        Licensor or its affiliates (provided that you may use a trademark or logo of
        Licensor as expressly required by this License).
        
        TO THE EXTENT PERMITTED BY APPLICABLE LAW, THE LICENSED WORK IS PROVIDED ON
        AN "AS IS" BASIS. LICENSOR HEREBY DISCLAIMS ALL WARRANTIES AND CONDITIONS,
        EXPRESS OR IMPLIED, INCLUDING (WITHOUT LIMITATION) WARRANTIES OF
        MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, NON-INFRINGEMENT, AND
        TITLE.
        
        -----------------------------------------------------------------------------
        
        Covenants of Licensor
        
        In consideration of the right to use the "Business Source License" name and
        trademark, Licensor covenants to MariaDB, and to all other recipients of the
        licensed work to be provided by Licensor:
        
        1. To specify as the Change License the GPL Version 2.0 or any later version,
           or a license that is compatible with GPL Version 2.0 or a later version,
           where "compatible" means that software provided under the Change License can
           be included in a program with software provided under GPL Version 2.0 or a
           later version. Licensor may specify additional Change Licenses without
           limitation.
        
        2. To either: (a) specify an additional grant of rights to use that does not
           impose any additional restriction on the right granted in this License, as
           the Additional Use Grant; or (b) insert the text "None".
        
        3. To specify a Change Date.
        
        4. Not to modify this License in any other way.
        
Project-URL: Homepage, https://pypi.org/project/graincheck/
Project-URL: Changelog, https://pypi.org/project/graincheck/#history
Keywords: dbt,sql,data-quality,static-analysis,analytics-engineering,grain
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Quality Assurance
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Requires-Python: >=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: sqlglot>=25.0
Requires-Dist: pyyaml>=5.1
Requires-Dist: jinja2>=3.0
Provides-Extra: dev
Requires-Dist: pytest>=7; extra == "dev"
Requires-Dist: pytest-cov>=4; extra == "dev"
Requires-Dist: ruff>=0.13; extra == "dev"
Requires-Dist: build>=1.0; extra == "dev"
Requires-Dist: twine>=5.0; extra == "dev"
Dynamic: license-file

# 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.

```bash
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.

```yaml
# 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"*:

```sql
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`:

```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:

```sql
{{ 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:

```yaml
  # 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:

```sql
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

```bash
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.
