Exporting to dbt

Some customers keep QRY after a migration. Others want the result as something their own team owns and runs — and for analytics engineering that is usually a dbt project. Forge exports one: the tables it copied become sources, the views it translated and verified become models, and the routines they call are created before the models build. Nothing is translated again, and nothing the export builds touches what Forge deployed.

Goal

You finish this page with a dbt project that runs green against the target dataset and that your team can commit to their own repository.

Prerequisites

• A project whose view translations have run and validated — the export only takes views that are Migrated or Validated; a view still in Needs review or Failed is left out and the README says why.
• On the machine that will run it: dbt-bigquery (pip install dbt-bigquery) and credentials for the target project (gcloud auth login, or a service account the profile points at).

What the export contains

Forge artifact	In the dbt project	Why
Tables Forge copied	sources in models/sources.yml, each column with the unique / not_null tests that the source catalog's primary keys and NOT NULLs justify	A copied table is data dbt reads; it is not dbt's to build
Verified view translations	models/*.sql, materialised as views, with ref() for other models and source() for tables	Decided by what the referenced object is in the migration — never by its name
UDFs and table functions	Created by run hooks, addressed as {{ target.schema }}.fn — on-run-start before the models, or on-run-end after them when the routine reads a model	dbt has no function objects; the hooks make them exist where the models look, when the relations they read exist
Procedures	Copied under forge_reference/, or a model once converted	dbt has no procedures. What each one can be is read from its own DML — see below

Models build into a sibling dataset (<target>_dbt) in the target dataset's own region, so dbt run never rewrites the objects Forge deployed and a profile in the wrong region cannot silently see nothing.

Steps

1. Preview

Project Overview → Deliverables card → Preview. Each artifact is listed with its role (source / model / routine / reference / left out) and the reason. What you see there is what the README in the zip will say — the two cannot disagree, they come from the same classification.

2. Export

Export dbt project downloads a zip:

<project_code>/
  dbt_project.yml
  profiles.yml.example      # project / dataset / region already filled in
  models/sources.yml        # copied tables + tests
  models/*.sql              # verified views
  macros/                   # the hooks that create the routines (before / after the models)
  forge_reference/          # procedures, for reading
  README.md                 # what each artifact became, and what was left out and why

3. Run it

cd <project_code>
cp profiles.yml.example profiles.yml       # or ~/.dbt/profiles.yml
dbt debug --profiles-dir .                 # connection test
dbt ls    --profiles-dir . --resource-type model
dbt run   --profiles-dir .                 # routines, models, then routines that read a model
dbt test  --profiles-dir .                 # the tests on sources and on converted models

dbt docs generate && dbt docs serve gives the lineage graph — models hanging off their sources, tests on each source. That page is the visual; QRY itself only shows the preview.

4. Check it says what Forge said

A model built by dbt is the same SQL Forge deployed and validated, so a quick comparison closes the loop:

dbt show --profiles-dir . --inline "select count(*) as n from {{ ref('v_EpisodiosDetalle') }}"

against the same count on the Forge-deployed view, or on the source. Measured on a hospital-warehouse estate: dbt run 5/5, dbt test 121/121, the four models equal to Forge's views and to the source.

5. (Optional) Convert a load procedure into a model

dbt has no procedures, so by default they ship under forge_reference/ as deployed. But what each one can be in dbt is decided from its own writes, and the preview says so for every procedure:

The procedure	In dbt	Why
One target, INSERT … SELECT or MERGE	Incremental model	Its full population is the model; its anti-join is the is_incremental() filter and its key the unique_key
Closes the current row, inserts the new one	Snapshot (not generated)	dbt has SCD2 of its own, but the history moves into dbt's table — that is your decision, so Forge names the strategy and key and stops
UPDATE only	Not a model	It computes a column on a table something else builds; in dbt that expression belongs inside that table's model
Several targets	Not a model	It loads N tables; one model per table is a rewrite, not a mapping
INSERT … VALUES, orchestration	Not dbt	A model is a query — this is the load's own bookkeeping

To convert one, open it in the Artifacts tab → Propose dbt model. Forge either explains why it cannot be one, or proposes the model with what changes in behaviour (the log rows dbt will not write, a full refresh rebuilding rows the procedure would have left). Convert applies it.

From then on dbt owns the table that procedure loaded: it stops being a source, every model that reads it does so through ref(), and the primary-key / NOT NULL tests it carried as a source travel to the model. Routines that read it are created after the models (on-run-end) — their relation does not exist until dbt builds it.

In the preview, the table the procedure loaded moves from source to built by dbt, naming the procedure that now builds it.

The proof is the same as everywhere else in Forge: run dbt run, then reconcile what dbt built against the source table. On the hospital estate, both convertible shapes were measured — an INSERT … anti-join calendar and a single-target MERGE — each equal to the source row for row, the second run a no-op, and a row tampered in the built table restored by the next incremental run.

6. Hand it over

Commit the folder to the team's repository. From here it is theirs: scheduling, environments, CI, and the procedures that stayed under forge_reference/ — the README says, for each, what it could become and why it was not converted.

Common issues

The procedure I converted still appears under forge_reference/. Export again — the zip is built when you download it. If it persists, the conversion was cleared (the dialog shows Revert to reference while it is active).

A view I expected is under "Left out". It has not validated. The README gives the reason; fix the artifact in Forge (see Reviewing translations), re-run the wave, export again.

The preview shows a conflict. A routine reads a table dbt builds (a converted procedure's) and a model calls it. It would have to be created after the first and before the second; no hook can do both, so dbt run fails as exported. Revert the conversion, or inline the routine into the model that calls it. The README names both sides.

dbt run cannot find a function. The hook creates the routines in {{ target.schema }} — the dataset your profile points at. If you edited the profile to another dataset, the routines went there too; the models reference them by target.schema, so keep the two consistent.

dbt test fails on a unique test that passed in Forge. The test comes from a primary key in the source catalog. If the target copy carries duplicates, the Forge reconciliation for that table should already be red — look there first; the dbt test is a second witness, not a different claim.

The profile's region does not match. The export reads the target dataset's location from the target itself and writes it into profiles.yml.example. If you moved the dataset, edit location:.

See also

• Approving and deploying — the validations the export relies on.
• Reviewing translations — getting a left-out view to Migrated.
• Forge reference — the deliverable in the feature overview.