# schema-guard

**Your AI agent stops inventing column names.**

Coding agents write SQL against the schema they *think* you have, based on your README, an old query, or a naming
convention. Then it fails in CI, in a dashboard, or at 2am. Snowflake's own developer blog ran a whole post on this
in September 2026 ([My coding agent won't stop hallucinating table columns](https://www.snowflake.com/en/developers/blog/coding-agent-hallucinating-table-columns/)).

schema-guard keeps a snapshot of your real tables and columns in the repo (names and types only, no data, no
credentials) and checks the agent's SQL against it **before it runs or lands in a file**:

```
schema-guard: this SQL names things that are not in the schema snapshot (.schema-guard/schema.json, taken 2026-09-29T01:35:07Z):
- `analytics.customers` has no column `country`. Did you mean: `country_iso2`?
- `analytics.orders` has no column `customer_id`. Did you mean: `cust_id`, `order_id`?
- `analytics.customers` has no column `id`. Did you mean: `cust_id`?
Fix the names and try again. ...
```

That's a real denial from the eval below: Claude Haiku 4.5 writing `models/revenue_by_country.sql` from a README
that describes last year's schema. The agent reads the denial, fixes its SQL and moves on. You never see the broken version.

## Does it help? (measured)

**Setup.** An analytics repo whose README describes an older schema (`customer_id`, `created_at`, `country`),
plus two up-to-date models that use a few of the real names. The agent can read and write files but can't reach
the warehouse. That's the situation in Snowflake's post: the agent has the repo, not the account. Each request
asks for a new SQL model, and afterwards the grader runs every file the agent wrote against the real DuckDB
warehouse. 4 requests × 3 arms × 3 runs, on Claude Code.

| Arm | Haiku 4.5: fails on a missing name / runs / correct | Sonnet 5: fails / runs / correct |
|---|---|---|
| baseline (repo only) | **12** / 0 / 0 of 12 | **12** / 0 / 0 of 12 |
| rule (snapshot + one line in CLAUDE.md) | 0 / 12 / 11 of 12 | 0 / 12 / 12 of 12 |
| hook (snapshot + hook, no instruction) | 0 / **12** / **12** of 12 | 0 / **12** / 11 of 12 |

What that means:
- **Without a snapshot, neither model wrote one working file (0 of 24).** Both trusted the README, and even when they
  copied real names from the existing models they mixed them with stale ones.
- **With the snapshot, every file ran (48 of 48).** The 2 wrong answers are logic errors, not names: Haiku started
  weeks on Sunday, and Sonnet counted the last days of 2025 in the first week.
- **The hook is the safety net for agents that don't go looking.** Haiku was denied in 10 of its 12 hook runs and
  fixed the names on the first retry every time. Sonnet found `.schema-guard/schema.json` by itself and was never
  denied. A one-line rule gets the same result if the agent follows it; the hook doesn't depend on that, and it also
  covers ad-hoc queries and MCP tools.
- Cost: the hook arm cost about the same as baseline ($0.65 vs $0.58 for 12 Haiku runs; $1.61 vs $1.61 for Sonnet).

**Be skeptical of this:** the world is small and synthetic, and the stale README is designed in (docs drift is
normal, but I chose how far). There are 3 runs per cell. Two grader references were added after I read runs:
"net revenue" net of refunds (it changed 2 Haiku grades, one rule run and one hook run), and listing all 52 weeks
with zeros (5 Sonnet grades). Both are disclosed in [evals/scenarios.py](evals/scenarios.py), and every run's SQL is in
[evals/results/](evals/results/). Rerun it: `cd evals && python run_eval.py --model <model> --runs 3`.


## Install

```bash
pip install "schema-guard[duckdb] @ git+https://github.com/idk-arsh/schema-guard"
schema-guard snapshot --dbt target        # or --duckdb, --bigquery, --snowflake, --databricks, --url, --ddl, --csv
git add .schema-guard/schema.json
```

Then use it however your team works. All of these read the same snapshot:

| Where | How |
|---|---|
| **Claude Code** (hook) | `/plugin marketplace add idk-arsh/schema-guard` then `/plugin install schema-guard`, or `schema-guard install` to add it to `.claude/settings.json` |
| **Cursor, Claude Desktop, VS Code, Windsurf** (MCP) | `{"command": "uvx", "args": ["--from", "git+https://github.com/idk-arsh/schema-guard", "schema-guard-mcp"]}`. Tools: `list_tables`, `describe_table`, `search_columns`, `check_sql` |
| **pre-commit** | `- repo: https://github.com/idk-arsh/schema-guard` / `rev: v0.1.0` / `hooks: [{id: schema-guard}]` |
| **CI** | `schema-guard check models/ queries/` exits 1 on a missing table or column. `schema-guard snapshot --dbt target --check` exits 1 if the committed snapshot is out of date |
| **Any agent** (AGENTS.md, .cursorrules) | paste [rules/schema-guard.md](rules/schema-guard.md) |

## Where the snapshot comes from

| Source | Command | Needs |
|---|---|---|
| dbt | `--dbt target` | `dbt docs generate` (catalog.json). With only manifest.json, tables are checked but columns aren't |
| DuckDB / SQLite | `--duckdb wh.duckdb` / `--sqlite app.db` | nothing |
| Postgres, MySQL, Redshift ... | `--url postgresql://...` | `sqlalchemy` + driver |
| BigQuery | `--bigquery my-project.my_dataset` (or `region-us`) | the `bq` CLI; INFORMATION_SCHEMA queries are free |
| Snowflake | `--snowflake MY_DB [--connection name]` | `snowflake-connector-python`, `~/.snowflake/connections.toml` |
| Databricks | `--databricks my_catalog` | `databricks-sql-connector`, `DATABRICKS_HOST` / `_HTTP_PATH` / `_TOKEN` |
| Migrations or a schema dump | `--ddl migrations/` | nothing; CREATE / ALTER / DROP applied in file order |
| Anything else | `--csv columns.csv` | an export of `information_schema.columns` |

Several files in `.schema-guard/` are merged, so one repo can cover more than one warehouse. The person taking the
snapshot needs warehouse access once; the agent never does.

## What it checks

- **Shell commands:** `bq query`, `snowsql`, `snow sql`, `psql`, `duckdb`, `sqlite3`, `databricks`, `spark-sql`,
  `mysql`, `trino`, SQL passed to scripts (`python run_sql.py "..."`, `python -c "...sql..."`), heredocs and `-f file.sql`.
- **Files:** every `.sql` the agent writes or edits. dbt `{{ ref() }}` and `{{ source() }}` are resolved to real
  tables. On an edit, only problems the edit *adds* are reported, so old debt in a file doesn't block new work.
- **MCP tools:** any tool with a `sql` / `query` / `statement` argument (Snowflake, Databricks, BigQuery, Postgres
  MCP servers).
- **Resolution:** CTEs, subqueries, correlated subqueries, aliases, `USING`, set operations, CTAS and temp tables
  created earlier in the same script, INSERT column lists, UPDATE SET, DELETE WHERE. Parsing is by
  [sqlglot](https://github.com/tobymao/sqlglot), so 20+ dialects.

## When it stays quiet

A false block costs more trust than a missed one, so it says nothing when it can't be sure:

- SQL it can't parse, Jinja beyond ref/source/config, sources it can't see into (UNNEST, LATERAL, table
  functions, PIVOT), `SELECT *` from a table it doesn't know, struct and JSON field access.
- Tables from a database the snapshot doesn't cover (unless the name is a near miss of one it does).
- **Stale snapshot:** if the agent sends the exact same SQL again after a denial, it goes through. A new column can
  slow the agent down once but never lock it out. Refresh with `schema-guard snapshot`, and put `--check` in CI.

## False blocks (measured, no LLM)

A guard that blocks valid SQL gets uninstalled, so this matters more than the catch rate.

| Corpus | Valid queries | False blocks | Planted wrong names caught |
|---|---|---|---|
| [Spider](https://yale-lily.github.io/spider) dev, 20 databases (held out: never looked at while building) | 1,034 | **0** | 1,032 / 1,034 |
| [defog sql-eval](https://github.com/defog-ai/sql-eval), 7 databases × Postgres, BigQuery, Snowflake, MySQL, SQLite | 960 | 0 | 959 / 960 |

Every valid query is human-written gold SQL that runs on its database, so any finding would be a false block.
The planted mistakes swap one real name for a wrong one the way agents get it wrong (a column from another table,
`_id` / plural / `_name` variants, singular vs plural table names). I fixed 3 checker bugs that defog exposed, so
treat its numbers as training numbers; Spider is the honest one. Its 2 misses are inside correlated subqueries, where
the checker deliberately gives the benefit of the doubt. Run them: `python evals/benchmark_spider.py`,
`python evals/benchmark_defog.py` (needs `pip install defog-data`).


## Limits

- It checks names, not meaning. `SUM(gross_amount)` when you wanted `net_amount` passes.
- Dynamic SQL built from string pieces in application code isn't seen.
- The snapshot is only as fresh as the last `schema-guard snapshot`.
- The Snowflake, Databricks and BigQuery snapshot readers are unit-tested on their output format, not yet run
  against live accounts. If you run one, [tell me how it went](https://github.com/idk-arsh/schema-guard/issues).

## Related

Part of a set of small, measured tools for AI agents working on data:
[data-agent-rules](https://github.com/idk-arsh/data-agent-rules) (rules + safety hooks, cost checks, masked previews),
[show-your-sql](https://github.com/idk-arsh/show-your-sql) (every number in the answer traced to a query result),
[data-test-guard](https://github.com/idk-arsh/data-test-guard) (agents can't delete or loosen tests to go green).

MIT licensed. Using it? Open a ["We use this" issue](https://github.com/idk-arsh/schema-guard/issues/new?template=we-use-this.yml) or add a line to [ADOPTERS.md](ADOPTERS.md). False blocks are the bug I most want to hear about.