Migrating from hand-rolled dbt

Got ~10k lines of hand-rolled entity-resolution SQL spread across dozens of dbt models? Almost all of it encodes a small set of real decisions — a few blocking keys, a handful of comparison fields and thresholds, some survivorship rules — buried in sprawl. GoldenMatch’s dbt converter ingests your dbt project and distills it into one reusable config, then measures how faithfully that config reproduces your pipeline’s existing output. The value is distillation + proof, not faithful SQL translation. Lossy extraction is fine because it is verified — exactly the posture the Splink converter ships with.

This complements the GoldenMatch-in-dbt package: that adds GoldenMatch to a dbt pipeline going forward; this converter is for replacing hand-rolled ER a team already has. The emitted config drops straight into a {{ goldenmatch_dedupe(...) }} model.

The key enabler: the manifest, not raw SQL

The converter reads your dbt manifest.json — the structured metadata dbt compile (or dbt parse / dbt docs generate) produces — not blind .sql files. The manifest carries every model’s compiled SQL, the full ref()/source() DAG, columns, tests, and the warehouse adapter. That structure is what makes distillation tractable, and it needs no warehouse credentials (only the optional verify against a live output table does).

dbt compile           # produces target/manifest.json

One command

goldenmatch import-dbt target/manifest.json -o goldenmatch.yaml

It:

identifies the models that do entity resolution (DAG + naming + shape signals);
extracts the recognizable ER idioms into ONE GoldenMatch config;
prints a coverage scorecard + a couldn't extract list for human review.

Distilled target/manifest.json -- 14/58 models analyzed as ER -- 4 blocking key(s)
  -- 3 exact + 3 fuzzy comparison field(s) -- 2 survivorship rule(s)
  -- 3 construct(s) flagged for review -- story: fuzzy-ER consolidation
Wrote config to goldenmatch.yaml.

Add --verify <output.parquet> --source <rows.parquet> to prove it reproduces your existing clusters (below). Useful flags: --min-confidence tunes how aggressively models are identified as ER; --strict fails on any lossy finding.

Or one line of Python

from goldenmatch.config.from_dbt import from_dbt

conv = from_dbt("target/manifest.json")   # or an already-parsed manifest dict
print(conv.coverage.line())

if conv.config is not None:                # None on a non-ER project
    import goldenmatch as gm
    result = gm.dedupe_df(df, config=conv.config)

conv.er_models is the ranked list of identified ER models (each with a confidence + the signals that fired), and conv.signals is every recognized signal — including the couldnt_extract items — so you can audit exactly what carried over.

Two honest value stories

The report tells you which applies, so it never over-claims an F1 win:

Fuzzy / probabilistic ER sprawl → an accuracy + consolidation story: GoldenMatch does the fuzzy matching the SQL did badly, in a tested engine.
Exact keep-latest dedup sprinkled everywhere (the common case) → a consolidation + maintainability story: 10k lines become a 20-line tested config. The “accuracy” number here is trivially ~1.0 — the win is consolidation, and conv.coverage.story says so (exact-dedup, not fuzzy-er).

What it recognizes

Over each identified ER model’s compiled SQL (dialect-aware — DuckDB, Snowflake, BigQuery in the MVP; others fall through to a dialect-agnostic core and are flagged):

dbt idiom	→ GoldenMatch
`QUALIFY ROW_NUMBER() OVER (PARTITION BY … ORDER BY … DESC) = 1`	blocking keys + exact matchkey + most-recent survivorship
`dbt_utils.deduplicate(partition_by=…, order_by=…)`	same
`dbt_utils.generate_surrogate_key([…])`	blocking keys + exact matchkey
`GROUP BY <natural key>` (inside an identified ER model)	blocking keys + exact matchkey
`jaro_winkler_similarity(a, b) >= t` / `levenshtein(a, b) <= k` / `soundex(a) = soundex(b)` + a self-join `ON` key	comparison field + scorer + threshold, blocked on the join key
`LOWER`/`UPPER`/`TRIM` wrapping a key column	a standardizer transform on that field

Everything else — arbitrary business logic, CASE ladders, priority hierarchies — is attached to the report as a couldn't extract finding with the model name + SQL excerpt, never silently dropped.

Verify it reproduces your pipeline (the trust step)

Your hand-rolled dbt ER model already produces an output table — a surrogate-key→member mapping or a canonical/golden table. That existing output is label-free ground truth. verify_against_dbt runs the converted config on a sample of the source rows and reports pairwise cluster agreement against it:

from goldenmatch.config.dbt_verify import verify_against_dbt

v = verify_against_dbt(conv.config, source_df, dbt_output_df, id_column="id")
if v is not None:                          # None on an empty / non-overlapping output
    print(f"reproduces {v.agreement['f1'] * 100:.1f}% of existing clusters")
    print(v.is_faithful)                   # True at pairwise F1 >= 0.95

goldenmatch import-dbt target/manifest.json \
  --verify dbt_output.parquet --source source_rows.parquet --id-column id

The output_table is a two-column id, cluster_id frame (name the columns with output_id_column / output_cluster_column if they differ). Verification is best-effort: a missing, empty, or non-overlapping output degrades to a skip notice, never a crash — the config is still written, it’s just a suggestion until you point it at an output table.

Boundaries (stated honestly in the report)

Extraction is heuristic → partial coverage. The couldn't extract list is a first-class output for human review, not a footnote.
Survivorship & conditional business rules are the hardest to extract. The MVP recognizes most-recent (DESC window order-by) and reports it with the exact remediation (GoldenMatch applies most_recent per field); priority hierarchies and CASE ladders are flagged.
Dialect variance — the MVP covers DuckDB / Snowflake / BigQuery; the long tail is flagged.
Verify needs the output table. With it the proof is strong; without it the config is a reviewed suggestion.

Get started

Concepts

GoldenMatch

GoldenCheck

GoldenFlow

GoldenPipe

GoldenAnalysis

InferMap

SQL extensions

Reference

Case studies

Research

Migrating from hand-rolled dbt

The key enabler: the manifest, not raw SQL

One command

Or one line of Python

Two honest value stories

What it recognizes

Verify it reproduces your pipeline (the trust step)

Boundaries (stated honestly in the report)

​The key enabler: the manifest, not raw SQL

​One command

​Or one line of Python

​Two honest value stories

​What it recognizes

​Verify it reproduces your pipeline (the trust step)

​Boundaries (stated honestly in the report)

The key enabler: the manifest, not raw SQL

One command

Or one line of Python

Two honest value stories

What it recognizes

Verify it reproduces your pipeline (the trust step)

Boundaries (stated honestly in the report)