dbt-testpilot 0.2.0

Scan a dbt project, profile the real data, and propose (and write) the dbt tests you're missing — with a human approve step.

dbt-testpilot

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.2.0) — pip install dbt-testpilot. Part 0 → Week 4 complete; hardened for real-world data. 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.

Contents

• 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

What it does

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

How it works

1. Read the dbt project's manifest.json / catalog.json (from target/). (done)
2. Profile each model in DuckDB — row counts, null %, cardinality, ranges, value patterns. (done)
3. Propose tests: deterministic heuristics first, LLM-augmented for rationale, relationships, and custom tests (strict JSON). (done)
4. Approve — you review; approved tests are written into the model's schema.yml. (done)
5. Run — dbt test executes them. (done)

Requirements

• 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.yaml for round-trip schema.yml edits (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)

---

Week 0 — environment setup

One-time setup on your machine. No account here needs a credit card.

1. Create accounts

• 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.)

2. Install tools (macOS / Apple Silicon)

# 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 "[e-mail]"   # match GitHub
uv python install 3.12

3. Create the project and a virtual environment

uv init dbt-testpilot && cd dbt-testpilot     # or: git clone <this repo> && cd dbt-testpilot
uv venv --python 3.12 && source .venv/bin/activate

4. Install dbt — read this (common gotcha)

python -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 adapter

Plain pip install dbt installs the newer Rust-based Fusion / platform CLI, which shadows dbt-core on your PATH. For a stable, artifact-friendly setup, install the Python dbt Core v1 line plus dbt-duckdb, inside the venv.

5. Clone the sandbox and build it

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

6. Configure secrets

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.

7. Verify your LLM keys

pip install google-genai groq
python check_llm.py          # uses LLM_PROVIDER from .env; `python check_llm.py groq` forces a lane

Expect [ok] gemini responded: '...key OK'.

Week 0 is done when: dbt build succeeds on jaffle_shop and check_llm.py prints [ok].

---

Week 1 — profiling your data

The profiler is the dbt_testpilot Python package in this repo.

Run it

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)

What you get

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.

Example output

== 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)

How it works (architecture)

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.

---

Week 2 — propose the missing tests

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.

Run it

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)

The heuristic rules (deterministic, no key)

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

The LLM layer (--llm)

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

What you get

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.

Example output (heuristics on jaffle_shop)

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

How it works (architecture)

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.

---

Week 3 — write tests + run

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.

Run it

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

Verified before 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.)

The approve/reject flow

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.

Built-in vs custom tests

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

End-to-end result (jaffle_shop)

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

How it works (architecture)

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.

---

Week 4 — package & publish

The final step: turn the tool into something anyone can install with pip install dbt-testpilot.

Install it (end users)

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.

What's in the package

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

Build & publish (maintainer steps)

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 publish

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

Result

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.

---

Project layout

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

Roadmap

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

Troubleshooting

• no manifest.json under .../target — run dbt build (and dbt docs generate for catalog.json) in the dbt project first.
• catalog.json missing — dbt build alone doesn't create it; run dbt docs generate.
• SDK not installed (for --llm) — pip install google-genai groq.
• No module named duckdb — pip install duckdb inside the active venv.
• No module named 'ruamel' — pip install ruamel.yaml (needed by apply).
• 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-verify only if you're sure).
• apply skipped 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 switch LLM_PROVIDER to groq/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.

Changelog

0.2.0

• Fix: all files are now read/written as UTF-8. Non-ASCII data (e.g. são paulo) previously crashed dbt parsing on Windows (UnicodeDecodeError), so dbt test never ran.
• New — verify before writing: apply checks every approved test against your real data and writes only those with zero violations; tests that would fail are reported as findings instead of being written. New flags: --db, --no-verify.
• New custom-test macros: distinguishes positive (> 0) from non_negative (≥ 0), and adds not_future, not_empty_string, is_integer — each macro shares one predicate with the verifier so they can't drift.
• LLM: all models are sent in a single batched request (fixes the ~20-requests/day free-tier limit) with 429 retry/backoff and a stricter, data-grounded prompt.

0.1.0 – 0.1.2

• Initial release: profile a dbt project's data, propose missing tests (heuristics + optional LLM), apply approved tests to schema.yml, run dbt test. MIT licensed; published to PyPI.

Changelog

0.2.0

• Verify-before-write — every approved test is checked against your real data first; only tests with zero violations are written (so dbt test stays green), and anything the data would fail is reported as a finding rather than silently written. New apply flags: --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 breaks dbt parse on 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 from non_negative (≥ 0); added not_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_null on a column with nulls, no unique on one with duplicates, etc.).

0.1.0

• Initial release — the profile → propose → apply → dbt test pipeline (heuristics + optional LLM + custom-test macro generation).

License

MIT — see LICENSE. © 2026 Kushal Mishra.