datahub-dbt-gen

Generates dbt staging models, tests and docs from DataHub metadata over MCP — and proposes the metadata first. A PII tag in the catalog becomes a masked column in the generated SQL

Comment

Inspiration

I have spent twenty-five years building data warehouses and BI layers, and the same hour of work keeps repeating itself: writing a staging model for a table someone has already documented elsewhere. The column meanings, the PII classifications, the key constraints — all of it exists in the catalog. Then someone opens an editor and types it out again in a schema.yml, by hand, where it immediately begins to go stale.

The waste is obvious once you name it. What is less obvious is the second half: catalogs are usually empty, because curating them is tedious and nobody is measured on it. So the metadata that would make generation possible does not exist, and the generation that would make curation worthwhile does not happen.

DataHub as a context platform is what makes both halves solvable at once. It is a place to put meaning that agents can read and write. So I built the loop rather than either half.

Repo: https://github.com/FergalMoriarty/datahub-dbt-gen

What it does

Two commands.

propose reads a table's schema over the DataHub MCP Server, samples five rows from the source database, and asks an LLM to suggest a description and governance tags for each column. It prints its proposals and writes nothing until a human confirms. Approved suggestions are written back over the same MCP server, into the same fields a steward edits through the UI.

generate reads that curated metadata back out over MCP and produces dbt staging models: stg_<table>.sql plus a schema.yml with descriptions, not_null and unique tests inferred from key constraints, and relationships tests resolved from foreign keys.

The part that matters is where they meet. A column tagged PII in the catalog comes out of the generator wrapped in a masking macro. That is not a naming heuristic — first_name and email would be easy to guess. It is a tag a human approved, changing the SQL that gets written.

On the uncurated employee table, propose flagged birth_date as PII. No name-matching rule catches that: nothing in the word "birth_date" announces that it identifies a person. It needed to see the values. Approving that suggestion and re-running the generator produced a masked birth_date column in the model, with a meta.contains_pii flag beside it in the YAML.

Sample output for all eleven Chinook tables is committed in the repository, so the difference between a curated and an uncurated table is visible without running anything.

How I built it

Self-hosted DataHub via datahub docker quickstart, with Chinook in a Postgres container on the same Docker network and ingested through a standard recipe.

Everything crosses the MCP server, spawned as a subprocess over stdio using FastMCP. get_entities returns everything the generator needs in one call: field paths, native types, nullability, isPartOfKey, foreign key relationships, and — crucially — the human-edited descriptions and tags. update_description and add_tags handle the writes, both at column level.

The two commands are deliberately asymmetric. propose runs the MCP server with TOOLS_IS_MUTATION_ENABLED=true; generate does not. Mutation tools are disabled by default, and separating the transports makes the generator's read-only nature structural rather than a matter of discipline.

Generation is deliberately deterministic. There is no LLM in that path. Every line of output maps to a rule:

Metadata Output
Single-column primary key not_null + unique
Composite primary key not_null only
nullable: false not_null
Foreign key relationships test
Tag PII mask() macro + meta.contains_pii
Tag SparselyPopulated Suppresses not_null

Challenges I ran into

The catalog was empty, and that is the actual problem. My first design assumed curated metadata and generated from it. That works, but it quietly assumes away the hard part. Adding propose meant the project addressed the reason catalogs are empty rather than stepping around it.

The generated tests found a bug in my own inference rules. I was emitting a column-level unique test for every column marked isPartOfKey. On playlist_track that produced two failures on the first dbt test run — correctly, because neither playlist_id nor track_id is unique alone. Only the pair is. The rule now checks key cardinality first. I have kept this in the README because the tool catching its own error is a better argument for generated tests than any claim I could make.

The LLM invented its own tag vocabulary. Despite a prompt listing exactly three allowed tags, it returned lowercase pii and a made-up contact_info. Because the generator matches on exact strings, nothing was masked and the failure was silent. Proposed tags are now filtered against an allow-list in code, with unknown ones dropped and warned about. Prompt instructions are a request; validation is a guarantee.

A bulk write that silently wrote almost nothing. add_tags accepts parallel entity_urns and column_paths arrays and documents this as bulk tagging. In mcp-server-datahub 0.6.0, a batched call applied only the last pair while reporting success for the whole batch — a thirteen-tag write landed three, and the tool told me it had written thirteen. I only caught it because the generator produced one masking macro instead of nine. propose now issues one call per column.

Deciding whether data is sensitive by sending it to a third party. There is a real tension in sampling values to detect PII. I capped the sample at five rows with values truncated, and added --no-sample for anyone who cannot send data out — at the cost of the detections it would otherwise make.

Accomplishments I am proud of

The loop closes, and it closes entirely over MCP. An agent proposes governance metadata, a human approves it in seconds rather than typing it over an afternoon, and the approved classification changes the SQL a build actually runs. Each of those pieces exists elsewhere. Connecting them so that a tag becomes a masking macro is the contribution.

I am also pleased that generation stayed deterministic. It would have been easier — and more fashionable — to hand the whole thing to a model. But dbt models gate nightly builds, and "plausible" is the wrong target for code that does that. Putting the LLM where judgement is needed and rules where correctness is needed is the design decision I would defend in a review.

What I learned

MCP is a genuinely good fit for catalog access. One call returns schema, constraints, relationships and human curation together, which meant the generator could stay small.

I also learned something about the shape of agent tooling. The instinct is to give the model more autonomy. The better move was to give it less: propose, print, wait. And to verify what came back — every failure I hit, from the invented tag vocabulary to the silent partial write, came from trusting output that code should have been checking. That applies to the model and to the tools it calls equally.

What's next

  • Composite key tests via dbt_utils.unique_combination_of_columns
  • Glossary terms via the MCP server's add_terms, mapped onto dbt's meta
  • PointInTime tags on timestamps driving incremental model strategies
  • Lineage from the MCP server driving generation above the staging layer
  • A --check mode for CI, failing a build when generated models drift from the catalog

Built With

Share this project:

Updates