Scans a dbt project, profiles the real data, and proposes the dbt tests you're missing —
not_null,unique,accepted_values,relationships, and custom — with a human approve step.
Status: Live on PyPI (v0.3.0) — pip install "dbt-testpilot[llm]". Multi-model (Gemini + free NVIDIA NIM), verify-before-write, guardrails, data-quality findings, LLM-authored tests, drift detection, and DuckDB/SQLite/Postgres.
Part of a small dbt reliability toolkit (alongside a SQL→dbt converter).
Analysts chronically under-test their data. dbt-testpilot reads your dbt project's metadata, profiles the actual tables in DuckDB, and suggests the tests you're missing — you stay in control and approve what gets written. The design is heuristics-first, LLM-augmented: obvious tests come from deterministic rules; the LLM adds rationale, cross-table relationships, and custom tests.
pip install "dbt-testpilot[llm]" # omit [llm] for heuristics only
# from inside your DuckDB-backed dbt project:
dbt build # produce manifest + catalog
dbt-testpilot profile --project-dir . --out profiles.json
dbt-testpilot propose --profiles profiles.json --out proposals.json --llm
dbt-testpilot apply --proposals proposals.json --project-dir . # review → verify → write
dbt testapply verifies every proposed test against your real data and writes only the ones that pass, so dbt test stays green.
- What it does
- How it works
- Requirements
- Week 0 — environment setup
- Week 1 — profiling your data
- Week 2 — propose the missing tests
- Week 3 — write tests + run
- Week 4 — package & publish
- Project layout
- Roadmap
- Troubleshooting
- Changelog
- License
| Stage | Capability | Status |
|---|---|---|
| Week 1 | Profile every model's data — nulls, cardinality, ranges, value lists, existing tests | Done |
| Week 2 | Propose the missing tests — heuristics + optional LLM, as validated JSON | Done |
| Week 3 | Write approved tests to schema.yml (human approve) and run dbt test |
Done |
| Week 4 | pip install dbt-testpilot |
Done |
- Read the dbt project's
manifest.json/catalog.json(fromtarget/). (done) - Profile each model in DuckDB — row counts, null %, cardinality, ranges, value patterns. (done)
- Propose tests: deterministic heuristics first, LLM-augmented for rationale, relationships, and custom tests (strict JSON). (done)
- Approve — you review; approved tests are written into the model's
schema.yml. (done) - Run —
dbt testexecutes them. (done)
- macOS (Apple Silicon) or Linux
- Python 3.12 (dbt Core supports 3.10–3.13; 3.12 is the safe middle)
- DuckDB (Python package
duckdb);ruamel.yamlfor round-tripschema.ymledits (Week 3) - A dbt project to point at — this repo uses jaffle_shop_duckdb as its sandbox
- A Gemini and/or Groq API key (only needed from Week 2)
One-time setup on your machine. No account here needs a credit card.
- GitHub — enable two-factor auth.
- Google AI Studio (https://aistudio.google.com) → Get API key → save it (primary LLM: Gemini).
- Groq (https://console.groq.com) → create an API key (fast backup lane).
- (PyPI / TestPyPI — used for publishing in Week 4.)
# Homebrew (skip if installed) — then follow its PATH note for /opt/homebrew
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
brew install git uv duckdb
git config --global user.name "Your Name"
git config --global user.email "you@example.com" # match GitHub
uv python install 3.12uv init dbt-testpilot && cd dbt-testpilot # or: git clone <this repo> && cd dbt-testpilot
uv venv --python 3.12 && source .venv/bin/activatepython -m pip install --upgrade pip wheel setuptools
pip install dbt-core dbt-duckdb # do NOT run `pip install dbt`
dbt --version # expect a 1.x core + the duckdb adapterPlain
pip install dbtinstalls the newer Rust-based Fusion / platform CLI, which shadowsdbt-coreon your PATH. For a stable, artifact-friendly setup, install the Python dbt Core v1 line plusdbt-duckdb, inside the venv.
git clone https://github.com/dbt-labs/jaffle_shop_duckdb.git
cd jaffle_shop_duckdb
# follow that repo's README to set up, then:
dbt build # creates target/manifest.json, target/catalog.json, jaffle_shop.duckdb
cd ..cp .env.example .env # then edit .env and paste your keys.env holds LLM_PROVIDER (gemini | groq | ollama), GEMINI_API_KEY, and GROQ_API_KEY. It is gitignored — never commit it.
pip install google-genai groq
python check_llm.py # uses LLM_PROVIDER from .env; `python check_llm.py groq` forces a laneExpect [ok] gemini responded: '...key OK'.
Week 0 is done when: dbt build succeeds on jaffle_shop and check_llm.py prints [ok].
The profiler is the dbt_testpilot Python package in this repo.
source .venv/bin/activate
pip install duckdb
python -m dbt_testpilot profile --project-dir jaffle_shop_duckdb --out profiles.json| Flag | Meaning |
|---|---|
--project-dir |
dbt project directory (must contain target/ and the .duckdb file). Default . |
--db |
Explicit path to the DuckDB file (auto-detected from --project-dir if omitted) |
--out |
Write per-model profiles to this JSON file |
--max-values |
Max distinct values captured for low-cardinality columns (default 25) |
Console — per model, each column with type, null %, cardinality, UNIQUE / NOT NULL flags, min/max range, low-cardinality value lists, and any tests it already has.
profiles.json — the machine-readable input for Week 2. Each column carries:
name, data_type, null_count, null_pct, distinct_count, distinct_pct, is_unique, min, max, top_values (for low-cardinality columns), and existing_tests.
== main.customers == (rows: 100)
customer_id INTEGER nulls 0.0% distinct 100 (100%) [UNIQUE, NOT NULL] range[1..100] tests:unique,not_null
first_order DATE nulls 38.0% distinct 46 (46%) range[2018-01-01..2018-04-07]
== main.orders == (rows: 99)
status VARCHAR nulls 0.0% distinct 5 (5%) [NOT NULL] tests:accepted_values
values: completed(67), placed(13), shipped(13), returned(4), return_pending(2)
| Module | Responsibility |
|---|---|
artifacts.py |
Parse manifest.json (model list + already-existing tests) and catalog.json (declared column types) |
profiler.py |
Read the actual columns/types from DuckDB's information_schema, then one aggregate pass per table for row count, null %, cardinality, and min/max; grab value lists for low-cardinality columns |
report.py |
Render each profile as a JSON-ready dict and a readable console view |
cli.py / __main__.py |
The profile command |
Two design choices worth knowing: columns are read from the database itself (source of truth, not stale metadata), and existing tests are captured so later steps propose only the tests you're missing.
propose reads the profiles.json from Week 1 and suggests the dbt tests each model is missing. It runs in two layers: a deterministic heuristic base (no API key, works offline) and an optional LLM layer (--llm) that adds rationale, relationships, and custom tests. Tests a column already has are always skipped.
source .venv/bin/activate
# heuristics only — no key needed
python -m dbt_testpilot propose --profiles profiles.json --out proposals.json
# heuristics + LLM augmentation (reads LLM_PROVIDER + key from .env)
python -m dbt_testpilot propose --profiles profiles.json --out proposals.json --llm| Flag | Meaning |
|---|---|
--profiles |
Path to the profiles.json produced by profile (default profiles.json) |
--out |
Write the proposals to this JSON file |
--llm |
Also query the LLM and merge its proposals |
--provider |
gemini or groq (default: LLM_PROVIDER from .env) |
--env |
Path to the .env holding your API keys (default .env) |
| Test | Proposed when |
|---|---|
not_null |
the column has zero nulls across the table |
unique |
distinct count equals the non-null count (a real key) |
accepted_values |
a categorical column (text/boolean) with ≤ 15 distinct values — the observed values become the allowed set |
relationships |
a *_id column that is not this table's own key but is unique in another model (a foreign-key guess) |
Confidence is high for clear cases and medium for softer guesses (relationships, nullable uniques).
Each model's profile is sent to Gemini/Groq one model at a time (small prompts, friendly to free-tier token limits) with an instruction to return only the missing tests as strict JSON. Every returned item is validated against the proposal schema — the column must actually exist, accepted_values must include values, relationships must include to/field — and anything malformed or hallucinated is dropped. If a call fails, that model degrades to heuristics-only instead of erroring. The LLM can also suggest custom tests (a named check plus a rationale).
Console — per model, each proposed test with its column, source (Heuristic / LLM), confidence, arguments, and a one-line rationale.
proposals.json — grouped by model; each proposal carries model, column, test, arguments, rationale, source (heuristic | llm), and confidence.
== main.orders == (2 proposed)
+ not_null order_date [H/high] 0 nulls across 99 rows
+ not_null status [H/high] 0 nulls across 99 rows
== main.stg_payments == (4 proposed)
+ not_null order_id [H/high] 0 nulls across 113 rows
+ relationships order_id [H/medium] -> ref('orders').order_id
+ not_null payment_method [H/high]
+ not_null amount [H/high]
On jaffle_shop the heuristics alone produce 14 proposals (12 not_null, 2 relationships) and correctly skip every column that already has a unique / accepted_values / relationships test.
| Module | Responsibility |
|---|---|
proposals.py |
Proposal schema, the deterministic heuristic rules, LLM-output validation, and merge/dedupe (drops anything already tested) |
llm.py |
Per-model prompt, Gemini/Groq call in JSON mode, parse + validate, graceful per-model fallback |
The approved proposals feed Week 3, which writes them into each model's schema.yml and runs dbt test.
apply closes the loop: review the proposals, write the ones you approve into the models' schema.yml, and run dbt test. Nothing is written without your OK — approval is interactive by default.
source .venv/bin/activate
pip install ruamel.yaml
# review each proposal interactively, then write the approved ones
python -m dbt_testpilot apply --proposals proposals.json --project-dir jaffle_shop_duckdb
# preview only (writes nothing)
python -m dbt_testpilot apply --proposals proposals.json --project-dir jaffle_shop_duckdb --dry-run
# accept all and run dbt test in one go
python -m dbt_testpilot apply --proposals proposals.json --project-dir jaffle_shop_duckdb --yes --run| Flag | Meaning |
|---|---|
--proposals |
Proposals JSON from propose (default proposals.json) |
--project-dir |
dbt project whose schema.yml files get updated |
--yes |
Approve all (non-interactive) |
--min-confidence |
Only consider proposals at/above low / medium / high |
--dry-run |
Show what would be written; change nothing |
--include-custom |
Generate vetted macros for supported custom tests (positive, non_negative, not_future, not_empty_string, is_integer) and write them |
--db |
DuckDB file used to verify tests before writing (auto-detected from --project-dir if omitted) |
--no-verify |
Skip data verification (not recommended) |
--run |
Run dbt test after writing |
Every approved test is checked against your real data before it lands in schema.yml: dbt-testpilot runs the equivalent query and only writes tests with zero violations, so dbt test comes back green. A test the data would fail — unique on a column with duplicates, positive on a column containing 0, a relationships FK with orphans — is not written; it's reported as would FAIL: N rows so you can investigate it as a likely real data issue. (Use --no-verify to skip.)
For each proposal you see the model, column, test, arguments, and rationale, then choose [y]es / [n]o / [a]ll remaining / [q]uit. Approved tests are merged into the model's schema.yml — located via the manifest's patch_path — using round-trip YAML, so your existing descriptions, comments, ordering, and formatting are preserved. It matches the file's existing key (tests: or data_tests:) and uses dbt's arguments: wrapper for tests that take arguments. Missing columns are created; duplicate tests are skipped.
Only dbt's built-in generic tests (not_null, unique, accepted_values, relationships) are written by default, so dbt test runs green immediately.
The LLM also proposes custom tests (e.g. positive_amount, date_in_past_or_present). dbt runs a test named foo via a macro test_foo, which won't exist for these — so by default they're skipped with a note. With --include-custom, dbt-testpilot generates vetted macros into macros/dbt_testpilot/ for the patterns it recognises (value ≥ 0; date not in the future) and writes those tests. Genuinely bespoke ones (multi-column or regex logic) are still skipped for you to implement — auto-writing SQL we can't guarantee would risk false confidence. On jaffle_shop this turns 9 of the 13 LLM suggestions into passing tests (dbt test → 45/45).
Approving the built-in proposals added 16 tests, and dbt test passed clean:
Done. PASS=36 WARN=0 ERROR=0 SKIP=0 TOTAL=36
That's 20 pre-existing + 16 generated — including not_null on orders.order_date, relationships from stg_orders.customer_id → customers, and more. With --include-custom, the 9 supported custom tests get generated macros too, taking the suite to 45/45 (bespoke tests skipped).
| Module | Responsibility |
|---|---|
yaml_writer.py |
Locate each model's schema.yml (manifest patch_path) and merge approved tests into the right column via round-trip YAML — no clobbering |
apply.py |
Interactive approve/reject, built-in vs custom filtering, then optionally shell out to dbt test |
macrogen.py |
Generate vetted generic-test macros for supported custom patterns (value ≥ 0, not-future) |
That completes the loop — scan → review → approve → tests running.
The final step: turn the tool into something anyone can install with pip install dbt-testpilot.
pip install dbt-testpilot
# with the optional LLM extra (Gemini/Groq SDKs):
pip install "dbt-testpilot[llm]"That gives you the dbt-testpilot command (profile / propose / apply). The only hard dependencies are duckdb and ruamel.yaml; the LLM SDKs are an optional extra.
Defined in pyproject.toml (license text in LICENSE):
| Piece | Value |
|---|---|
| Build backend | hatchling |
| Console command | dbt-testpilot = dbt_testpilot.cli:main |
| Core dependencies | duckdb, ruamel.yaml |
Optional extra [llm] |
google-genai, groq |
| Python | >=3.10 |
| License | MIT |
| Version | dynamic, read from dbt_testpilot/__init__.py |
Publish to TestPyPI first — it's a rehearsal sandbox, and PyPI versions are permanent (you can't overwrite one). Then verify and publish to real PyPI.
uv build # builds sdist + wheel into dist/
twine check dist/* # validate metadata + README rendering
# 1) TestPyPI (rehearsal)
export UV_PUBLISH_TOKEN=pypi-<TESTPYPI_TOKEN>
uv publish --publish-url https://test.pypi.org/legacy/
python -m venv /tmp/verify && /tmp/verify/bin/pip install \
--index-url https://test.pypi.org/simple/ \
--extra-index-url https://pypi.org/simple/ dbt-testpilot
/tmp/verify/bin/dbt-testpilot --help
# 2) real PyPI
export UV_PUBLISH_TOKEN=pypi-<PYPI_TOKEN>
uv publishReleasing an update: bump the version in dbt_testpilot/__init__.py (e.g. 0.1.0 → 0.1.1) before uv build — PyPI rejects a re-upload of an existing version. The published page (README + metadata) refreshes only when a new version goes live.
dbt-testpilot is live on PyPI: https://pypi.org/project/dbt-testpilot/. A brand-new venv → pip install dbt-testpilot → run against any DuckDB dbt project → it profiles, proposes, and writes the tests you approve.
dbt-testpilot/
├─ dbt_testpilot/ # the tool (Python package)
│ ├─ __init__.py
│ ├─ __main__.py # python -m dbt_testpilot
│ ├─ artifacts.py # parse manifest.json + catalog.json
│ ├─ profiler.py # DuckDB profiling (Week 1)
│ ├─ proposals.py # heuristic proposals + schema/validation (Week 2)
│ ├─ llm.py # LLM proposals, strict JSON (Week 2)
│ ├─ yaml_writer.py # merge approved tests into schema.yml (Week 3)
│ ├─ apply.py # approve/reject flow + run dbt test (Week 3)
│ ├─ macrogen.py # generate macros for supported custom tests (Week 3.5)
│ ├─ report.py # JSON + console rendering
│ └─ cli.py # profile + propose + apply commands
├─ jaffle_shop_duckdb/ # sample dbt project (sandbox) — gitignored
├─ GETTING_STARTED.md # from-scratch usage guide
├─ README.md
├─ pyproject.toml, uv.lock # uv project scaffolding
├─ .env / .env.example / .gitignore
└─ profiles.json # generated profiler output — gitignored
- Part 0 — accounts, installs, sandbox, secrets, repo hygiene
- Week 1 — data profiler (
manifest/catalog+ DuckDB stats →profiles.json) - Week 2 — propose missing tests (heuristics + optional LLM, validated JSON)
- Week 3 — write approved tests to
schema.yml+dbt test - Week 4 — packaged & published to PyPI (
pip install dbt-testpilot)
See GETTING_STARTED.md for a from-scratch usage guide.
no manifest.json under .../target— rundbt build(anddbt docs generateforcatalog.json) in the dbt project first.catalog.jsonmissing —dbt buildalone doesn't create it; rundbt docs generate.SDK not installed(for--llm) —pip install google-genai groq.No module named duckdb—pip install duckdbinside the active venv.No module named 'ruamel'—pip install ruamel.yaml(needed byapply).- A test I approved wasn't written — it failed verification against your data (shown as
would FAIL: N rows), so it was withheld. That's a real data issue or a bad suggestion — investigate rather than force it (--no-verifyonly if you're sure). applyskipped a custom test — either you didn't pass--include-custom, or it's a bespoke pattern with no macro template (implement it by hand).- Gemini
429 / RESOURCE_EXHAUSTED— the free tier is ~20 requests/day. The tool batches all models into one request and retries with backoff; if the daily quota is gone, wait or switchLLM_PROVIDERtogroq/ollama. - Non-ASCII data (accents like
ã/ç, CJK, emoji) — handled; all files are read/written as UTF-8 (fixed in 0.2.0). - Free-tier limits change — provider is read from
.env, so switching Gemini ⇄ Groq ⇄ Ollama is a one-line edit.
- Docs/demo — a new complete, unedited real-session demo (GIF + MP4): a ~1M-row jaffle-shop run across DuckDB, SQLite, and Postgres ending in
dbt testPASS=199 / ERROR=0. The README demo image now uses an absolute URL so it renders on PyPI as well as GitHub. No library code changes from 0.3.1.
- Postgres works from the CLI —
--dbnow accepts apostgresql://…connection URL onprofile/apply/benchmark/drift(previously only file paths were accepted, so Postgres was unusable via the CLI). Profiling no longer callsmin()/max()on boolean columns (unsupported in Postgres). Verified: DuckDB, SQLite, and Postgres return identical results. - Sharper
accepted_valuesguardrails — now also dropped on boolean columns, identifier/UUID columns, long free-text, and any value list containing a quote dbt can't render safely (real low-cardinality enums likecountry_codeare kept). - Idempotent
apply— tests for models without a schema.yml are written to a managed_dbt_testpilot.ymlthat is regenerated each run, so re-applying never leaves stale tests behind; a model's own schema.yml is merged, never clobbered. (Standard block-style YAML is preserved exactly; hand-formatted inline/flow-style YAML in the same file may be compacted — data is never changed.) - DeepSeek — corrected the NIM slug to
deepseek-ai/deepseek-v4-flashand made it opt-in (its free tier is capacity-limited); defaultbenchmarkproviders are nowheuristic,nemotron,gemini. benchmarkreporting — a provider that errors (bad slug / capacity limit) now records the error in the JSON report instead of silently showing zero.
- Multi-model LLM — pluggable providers: Gemini, plus free NVIDIA NIM (Nemotron 3 Ultra, DeepSeek), Groq, and any OpenAI-compatible endpoint. One free
NVIDIA_API_KEYserves the NIM models.propose --provider a,bruns an ensemble (--ensemble union|vote). benchmarkcommand — compare providers on the same project and report each one's would-fail rate (LLM false-positives before verification).- Accuracy: guardrails + expanded heuristics —
not_future/positive/non_negativenow come from deterministic rules (0% false-positive, off the LLM); guardrails drop over-fit noise (accepted_valueson counts,uniqueon measures, redundantpositive+non_negative, etc.). - Findings vs bad-test split —
applydistinguishes a valid test your data currently fails (a real data-quality finding) from a wrong proposal. New flags:--write-failing-as-warn(write findings as warn-severity sodbt testsurfaces them) and--findings-out report.json. - LLM-authored custom tests (on by default;
--no-llm-teststo disable) — the model can author any rule as a sanitized, verified SQL expression: a reusable single-column check → a generic macro, or a multi-column business rule → a dbt singular test. - More curated custom tests —
not_zero,probability,percentage,latitude,longitude,valid_email. driftcommand — re-checks already-written tests against current data/schema; flags stale tests (data drifted) and schema drift (model/column gone). Non-zero exit for CI.- Beyond DuckDB — pluggable engine: DuckDB (default), SQLite (stdlib), Postgres (
pip install "dbt-testpilot[postgres]"). Custom-test predicates are dialect-aware. - Token accounting —
propose --llmreports per-provider token usage. - CLI —
--version/-Vand a detailed--helpwith copyright.
- Docs: the
LICENSElink in this README is now an absolute URL so it resolves on PyPI (previously a repo-relative link that 404'd).
- Verify-before-write — every approved test is checked against your real data first; only tests with zero violations are written (so
dbt teststays green), and anything the data would fail is reported as a finding rather than silently written. Newapplyflags:--db,--no-verify. - Unicode / Windows crash fixed — all files are read and written as UTF-8, so non-ASCII data (e.g.
são paulo) no longer breaksdbt parseon Windows. - Batched LLM calls — all models go in a single request (was one per model) with 429 retry/backoff, so a large project no longer exhausts the Gemini free-tier daily quota.
- More accurate custom macros —
positive(> 0) is now distinct fromnon_negative(≥ 0); addednot_future,not_empty_string,is_integer. Each generated macro and its data check share one predicate, so they can't drift. - Stricter, data-grounded LLM prompt — proposals must be justified by the statistics (no
not_nullon a column with nulls, nouniqueon one with duplicates, etc.).
- Initial release — the
profile → propose → apply → dbt testpipeline (heuristics + optional LLM + custom-test macro generation).
MIT — see LICENSE. © 2026 Kushal Mishra.