NLQueries GitHub

Blog  / 

Merging dbt Model Descriptions into NLQueries: How the Importer Behaves and What to Test

Published . Written for nlqueries-core 0.4.

dbt knowledge-base import

Here's a small, very ordinary failure. Someone asks the internal data assistant, "For tenant A, what was paid-order revenue in October?" It comes back with a confident "$30,000." The number in the database is right: the column holds 30,000. But that column is amount_cents, so the real figure is 300.00, and the answer is off by a factor of a hundred. If the assistant had also forgotten to filter out cancelled orders, it would have counted one more order and added another 5,000 cents.

The annoying part is that the team already wrote this down. The dbt model that builds orders has a column description saying amounts are integer cents and only PAID counts as revenue. The meaning lives in dbt's docs; it just never reached the assistant's prompt. NLQueries Core 0.4.1 has an import-dbt command aimed at exactly this gap, and this post follows the October question through it: what the importer carries across, how it settles disagreements with hand-edited text, which mappings go quietly wrong, and what you should check before a merged file goes anywhere near users.

Fair warning: this is a walk through the importer's behavior and the tests you'd need, not a finished integration to copy. The matching and merge rules were exercised on small synthetic files, but the complete export, import, promote and ask sequence hasn't been demonstrated end to end, and nothing here measures an accuracy gain.

What the import moves, and what it doesn't

The import-dbt command merges "a dbt project's model and column documentation into a knowledge base" (CLI reference). In NLQueries, the knowledge base (KB) is the YAML file that holds your schema, descriptions and sample values; it's what the query and ask commands read when they build a prompt. The importer reads dbt's artifacts with the standard library's JSON parser, so your NL2SQL service doesn't need dbt-core installed at runtime (dbt_importer.py).

That's a narrow boundary, and it's narrow on purpose. What moves is prose: model descriptions and column descriptions. What doesn't move is metric logic. If your project uses the dbt Semantic Layer, the command lists those metrics in its output, but it doesn't write them to the KB, and derived metrics are surfaced for review rather than turned into a guessed column. So this isn't a way to execute MetricFlow metrics through NLQueries. If you need that, it's a separate decision.

Better descriptions give the model better context for writing SQL. They don't prove any answer is right, and they don't add a security boundary. Validation and execution policy are separate machinery (sql_policy.py), and you still need to check results against a known answer, which comes later in this post.

Pin the manifest, and never merge in place

Two inputs matter: the dbt artifact and the KB you're merging into.

On the dbt side, parsing a project produces target/manifest.json, which carries metadata for every resource (dbt manifest reference). One detail trips people up: the manifest's schema version isn't the dbt version. The current reference table maps manifest v12 to dbt 1.8 through 1.11 and to dbt v2. Record both values from your artifact's own metadata block next to the artifact, so a later "why did coverage change?" conversation has something to point at. You can hand import-dbt either the target directory or the manifest.json file directly.

Here's the shape of the running example. The model is called fct_orders, but it's aliased to orders and built into the analytics schema. A staging model, stg_orders, also lands as a table called orders, but in staging. This is an illustrative excerpt, not a full dbt project.

# models/marts/schema.yml (illustrative excerpt; fct_orders is configured with alias "orders")
version: 2
models:
  - name: fct_orders
    description: One row per customer order, deduplicated. Use this table for revenue reporting.
    columns:
      - name: amount_cents
        description: Order total in integer cents (divide by 100 for currency units). NULL when the order was never priced.
      - name: status
        description: Lifecycle state. Only PAID counts as revenue; CANCELLED never does.

On the NLQueries side, start from a KB generated from the live schema, then write the merge to a new file. That one habit matters more than anything else in this post. import-dbt writes to the --kb path in place unless you pass --output, and the merged file replaces the target as a whole (CLI reference). A separate output gives you a diff to review and an untouched original to roll back to.

# Untested sketch: command shapes from the Core 0.4.1 CLI reference.
pip install "nlqueries-core==0.4.1"   # plus the driver extra for your database; see Getting Started

# 1. Build a KB from the live schema. --no-include-samples skips the per-column value queries.
nlqueries export-kb sales --output kb/original.yaml --no-include-samples

# 2. Merge dbt docs into a NEW file, never in place.
nlqueries import-dbt path/to/dbt/target --kb kb/original.yaml --output kb/merged.yaml

# 3. Review before anything reads it.
diff kb/original.yaml kb/merged.yaml

Here sales is a connector alias you registered earlier with nlqueries connect ... --alias sales. Turning off samples is optional; it stops the export running a sample query per table and a SELECT DISTINCT per text column, which matters on warehouses billed by bytes read. The Getting Started guide covers installation, drivers and provider configuration. Qdrant is needed for embeddings, the semantic cache and document retrieval.

Who wins when two descriptions disagree

Every description in the merged KB carries a description_source marker, and the merge follows a fixed precedence: manual edit > dbt doc > database schema > LLM-generated. The CLI reference says a human edit is never overwritten. The command's code spells out the exact rule: re-import protects nonempty manual and legacy-unmarked descriptions; dbt documentation can replace dbt-, schema- or LLM-sourced descriptions according to precedence (cli/main.py). The same rule applies to the table description and to every column.

That's the behavior you want for the second scenario in the running example. Say a data engineer fixed the customers description by hand last quarter, to note that tenant_id is descriptive only and isn't an access control. When dbt's wording changes and you re-import, the manual text stays put and the orders columns pick up dbt's text. Your review of the diff should confirm exactly that: the amount_cents and status entries now say description_source: dbt, the hand-edited table still says manual, and nothing unrelated has moved.

There's one rule in the source that's easy to miss. In the KB generator, a description with no recorded source is treated as manual, "so an upgrade or a first dbt sync never clobbers existing content" (kb_generator.py). That's a safe default with a consequence: merge into an old or hand-assembled KB whose descriptions lack description_source, and the dbt docs won't replace them, so the import looks like it did nothing. That's another reason to start from a fresh export-kb, which records a source for every field, rather than from a KB file someone kept around.

Where the mapping quietly goes wrong

A clean exit code tells you the command finished. It doesn't tell you the right descriptions landed on the right tables. Four things deserve a look before you trust a merge, and you can see each of them by running the importer's parsing functions on their own, loaded by path from the pinned source. The harness below is custom code, not an NLQueries command.

# inspect_import.py: custom harness; loads the pinned dbt_importer.py by path.
import importlib.util, logging, sys
logging.basicConfig(level=logging.WARNING, format="%(levelname)s %(message)s")
spec = importlib.util.spec_from_file_location("dbt_importer", sys.argv[1])
mod = importlib.util.module_from_spec(spec); sys.modules["dbt_importer"] = mod
spec.loader.exec_module(mod)

models = mod.parse_dbt_models(sys.argv[2])          # target/manifest.json
kb_tables = [{"name": "ORDERS", "schema": "ANALYTICS"}, {"name": "orders", "schema": "staging"},
             {"name": "customers", "schema": "analytics"}, {"name": "region_codes", "schema": "analytics"}]
for t, m in zip(kb_tables, mod.match_dbt_docs(models, kb_tables)):
    print(f"{t['schema']}.{t['name']} -> {m.schema + '.' + m.name if m else None}")
print("bare orders ->", mod.match_dbt_docs(models, [{"name": "orders"}])[0])
print("broken manifest ->", mod.parse_dbt_models(sys.argv[3]))

The synthetic manifest it reads contains the two orders models above, a customers model, an order_items model whose description runs past 2,000 characters, and a region_codes seed; the third argument is a deliberately malformed JSON file. Run with Python 3.11 against the Core 0.4.1 importer, it prints the following. The output is trimmed to the lines that matter. Warnings go to stderr, so their position among the printed lines can vary, and the malformed-file warning is followed by a JSON traceback.

WARNING dbt model analytics.order_items has descriptions longer than 2000 characters; they are cut and end with '...'.
ANALYTICS.ORDERS -> analytics.orders
staging.orders -> staging.orders
analytics.customers -> analytics.customers
analytics.region_codes -> None
bare orders -> None
WARNING Could not read dbt artifact broken_manifest.json.
broken manifest -> []

Each line is a case you'll want covered in CI.

Aliases and identifier case. The model is fct_orders, but the importer keys on its alias, orders, because that's the identifier the warehouse and the live-schema KB use. Matching ignores case, so a Snowflake-style ANALYTICS.ORDERS still finds analytics.orders. That's the documented intent in the source.

Two tables called orders. Each schema-qualified table got its own model's docs: staging.orders didn't inherit the reporting description from analytics.orders. The interesting line is bare orders -> None. When a KB table has no schema recorded and two models share the name, the importer refuses to guess. That's correct but silent: the table just gets no dbt description. Before you trust a match, check that the schema each node carries in manifest.json equals what your database actually reports.

Seeds and sources. region_codes is a seed, so it got nothing. Only resource_type == "model" nodes are imported; seeds, tests and sources are ignored. A model counts as documented if it has a table description or column descriptions, so a model documented only at column level still comes through. If your best documentation lives on dbt sources, though, this import won't carry it.

Long text and broken files. The long description was cut to 2,000 characters ending in ..., with one warning per model. The malformed manifest produced an empty list and a warning, not an error. The source says this is deliberate ("a bad file never breaks a build"), which is exactly why a bad artifact can produce an empty import that still exits cleanly.

The real import-dbt command uses these same schema-aware functions, and a run of it against a minimal synthetic manifest and KB showed the same behavior through the merge. That run used small files built for the check, not a full dbt project. It kept the manual customers description, gave analytics.orders and staging.orders their own dbt text, truncated the long order_items description with a warning, left the seed alone, exited 0, and finished with:

Merged dbt docs for 4 of 4 tables into kb-merged.yaml.
Found 5 documented models and 0 metrics (0 resolved to a measure).

Read those two lines together. Five documented models, four tables merged, exit code 0. The fifth model was documented only at column level, and it had no table in the KB at all. Nothing in the run flagged it.

An exit code isn't coverage, and neither is a table-level check

The obvious next move is a small CI script: walk the manifest's model nodes that have a table description, find the matching KB table, and fail unless its description_source is dbt or manual. That kind of script checks table-description markers only. It doesn't establish column documentation coverage, and it shouldn't gate promotion of a merged KB. Pointed at the merged file above, a check of that shape reports four of four documented models covered and exits 0, because it never looks at the column-only model. It would also stay green if amount_cents or status lost its description, and those two are the whole point.

A gate worth trusting has to compare more. For every documented model in the manifest, including column-only ones, it needs to confirm the expected KB table exists and that every documented column on it exists too. It should check that each description equals dbt's text, or dbt's text truncated to 2,000 characters with ..., and that the source is dbt unless the field is an approved manual override. Those overrides belong in a separate, reviewed list, so a deliberate human edit isn't confused with a missed import. It also needs to fail loudly on the quiet cases the importer tolerates: an empty or unreadable manifest that yields zero models, two models resolving to the same schema-qualified table, and KB tables without a schema that can't be matched at all.

No code for that gate appears here, because none has been tested against those cases. The acceptance tests for your own are concrete: a manifest with the amount_cents or status description removed must fail; a column-only model missing from the KB must fail; a KB description that doesn't match dbt's text must fail; an empty manifest must fail; duplicate relation keys must fail. An intentional manual override must pass, and must show up in its own report line rather than silently.

Checking the October question against a known answer

Once the merged file passes review and the gate, promote it deliberately. Copy it over the KB file the commands read for that connector, and keep original.yaml for rollback. Use the path reported by the default export-kb for that connector. Core derives the filename from the connector ID and writes it under ~/.nlqueries/knowledge_base by default. If you set KB_PATH, confirm which file ask and query load before you copy anything.

Use a fixture where you already know the answer. Here's a small synthetic one in SQLite syntax. Tenant A has four October orders: two paid orders worth 10,000 and 20,000 cents, one cancelled order worth 5,000 cents, and one paid order with a NULL amount. There's also a November order and a tenant B order to catch sloppy date and tenant filters.

-- Synthetic fixture (SQLite syntax). Expected answer: 30000 cents, 3 orders.
CREATE TABLE customers(id INTEGER PRIMARY KEY, tenant_id TEXT NOT NULL, region TEXT);
CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id),
                    order_date TEXT, amount_cents INTEGER, status TEXT);
CREATE TABLE order_items(id INTEGER PRIMARY KEY, order_id INTEGER REFERENCES orders(id), sku TEXT);

INSERT INTO customers VALUES (1,'A','East'),(2,'A','West'),(3,'B','North');
INSERT INTO orders VALUES
  (101,1,'2026-10-01',10000,'PAID'), (102,1,'2026-10-02',5000,'CANCELLED'),
  (103,2,'2026-10-31',20000,'PAID'), (104,3,'2026-10-02',70000,'PAID'),
  (105,1,'2026-11-01',40000,'PAID'), (106,2,'2026-10-15',NULL,'PAID');
INSERT INTO order_items VALUES (1,101,'a'),(2,101,'b'),(3,103,'c');

-- Reference answer
SELECT SUM(o.amount_cents) AS revenue_cents, COUNT(*) AS paid_order_count
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE c.tenant_id = 'A'
  AND o.status = 'PAID'
  AND o.order_date >= '2026-10-01' AND o.order_date < '2026-11-01';

On SQLite 3.45.3, the reference query returns (30000, 3): 300.00 in currency units, from three paid orders. The NULL-amount order counts as an order but adds nothing to revenue. Drop the status filter and the fixture's own rows tell you what happens: the cancelled order slips in, giving 35,000 cents over four orders. That's the mistake the status description is meant to head off. The tenant predicate here is just arithmetic for the expected answer; it isn't an authorization boundary.

To preview what NLQueries generates, use ask, which generates and validates SQL without executing it:

# Untested sketch: needs the merged KB promoted for this connector and a configured model provider.
nlqueries ask sales "For tenant A, what were paid-order revenue and paid-order count in October 2026?"

Two caveats. ask doesn't use the semantic cache, which is what you want for a before-and-after comparison. But with literal grounding on, which is the default, ask can still open the connector read-only to check string literals against stored values. Set NLQ_LITERAL_GROUNDING=false before startup if the preview must not touch the database at all (configuration).

For a trace you can keep, the Python entry point is clearer, because the result tells you whether the SQL validated and what the request was allowed to do (sync_runner.py, execution.py). One gotcha: the Python path doesn't resolve CLI aliases, so pass the canonical connector ID the KB was exported under, not sales. Set dialect to match your database as well.

# Untested sketch against Core 0.4.1 source. AGENT_ID is a placeholder for your configured agent:
# use the canonical connector ID the KB was exported under; CLI aliases aren't resolved here.
from nlqueries.execution import ExecutionPolicy
from nlqueries.orchestrator.sync_runner import run_query_sync

AGENT_ID = "<your-agent-id>"
QUESTION = "For tenant A, what were paid-order revenue and paid-order count in October 2026?"

result = run_query_sync(
    QUESTION,
    AGENT_ID,
    dialect="sqlite",
    use_cache=False,                                # neither read nor write the semantic cache
    execution=ExecutionPolicy.execute_read_only(),  # read-only transaction; DB grants still apply
)
print(result.sql_is_valid, result.execution_mode, result.from_cache)
print(result.sql)
if result.sql_result is not None:
    print(result.sql_result.rows)   # compare with the gold answer: [[30000, 3]]

Run it once with the original KB and once with the merged one, with the same model and the cache off, and keep both generated statements alongside hashes of both KB files. If the second statement filters on PAID and returns 30000, 3, that's one good data point. It isn't a general accuracy gain. One question can't establish that, and a passing validator only tells you the SQL parsed and passed policy, not that it answered the business question.

Making re-imports boring

dbt docs change, so treat the import like any other generated artifact. For each run, record the manifest's dbt version and schema version, a hash of the manifest, a hash of the merged KB, and who approved the promotion. Run import-dbt to a fresh output file every time, gate it with a coverage check that actually covers columns, and keep the diff in review.

Because nonempty manual and legacy-unmarked descriptions are protected, while dbt text can replace dbt-, schema- or LLM-sourced ones, every changed line in a re-import diff should trace back to a dbt doc change or to a field that's newly documented. If it doesn't, stop. A large or unexplained diff is a signal, not noise.

Also decide who owns a disagreement. A hand edit outranks dbt for good, which is the point, but someone should push the better wording back into dbt or the two will drift apart.

Your next step: take one model whose column semantics have burned you before, whether that's cents, a status flag or a soft-delete column. Export a fresh KB, import its manifest to a separate file, and write the column-level check for just that model, starting with a test that deletes the column's dbt description and expects a failure. Then ask one question whose answer you can compute by hand. If the generated SQL and the gold result line up, widen the check to the rest of the project. If they don't, you've found the gap before your users did.

References

All posts · RSS feed · Documentation