Skip to content

Generating dbt models

From CrossModel to a running dbt pipeline

This example generates a complete dbt project from the BrightGreen models and runs it locally on DuckDB - no cloud warehouse required.

It is the most complete example in the knowledge base, because it covers both halves of the workflow: modelling and generating in CrossModel, then executing the result in dbt.

Two environments, one repository

The example is deliberately split across two environments that never need each other installed:

Modeling environment Execution environment
Tools CrossModel dbt Fusion, DuckDB
Who Data modeller Data engineer / analyst
What Define models, generate dbt code, commit Pull changes, run dbt, query results

The Git repository is the handoff point. Modellers commit generated SQL; engineers pull and run it. Neither side has to install the other's tooling.

The pipeline

CSV Seeds  -->  Source Models  -->  Mapping Models  -->  DWH Tables
(raw data)      (views)             (transformations)    (final output)

The worked example: the Customer entity from JuiceERP flows through a mapping that concatenates FirstName and LastName into a FullName, producing the CustomerFullName warehouse table.

Get the workspace

On the welcome page, under Tutorials & Examples, choose:

examples → code-generation → dbt → nunjucks

What's in the workspace

Path Purpose
BrightGreen/Generation/Templates/dbt/source_entity_model.sql Source layer models (views)
BrightGreen/Generation/Templates/dbt/entity_mapping.sql Mapping layer models (transformations)
BrightGreen/Generation/Templates/dbt/dwh_entity_model.sql Warehouse layer models (tables)
BrightGreen/Generation/Templates/dbt/empty_seed.csv Empty CSV seed scaffolding
dbt/BrightGreen/ The generated dbt project - models, seeds and dbt_project.yml

Part 1 - generate (in CrossModel)

  1. Browse the .cm files under BrightGreen/ to see the source entities, warehouse entities and mappings the generation is driven from.
  2. Open CrossGenerate from the right-hand bar, or from View → CrossGenerate.
  3. Choose engine Nunjucks and run each of the three templates, writing the output into dbt/BrightGreen/models/.
  4. Commit the generated models and seed files.

At this point dbt/BrightGreen/ contains everything the execution environment needs.

Part 2 - run (in dbt)

You need dbt Fusion and the DuckDB adapter:

pip install dbt-duckdb

Sign in with a dbt account - a free one is enough - then load data and build:

cd dbt/BrightGreen

# Load the CSV seed data into DuckDB
dbtf seed

# Build all models: source views, mapping views, DWH tables
dbtf build

dbt or dbtf?

Both commands exist depending on how your environment variables are configured. If dbtf is not found, try dbt.

Sample data is provided under seeds_example/ - copy it into dbt/BrightGreen/seeds/ before running dbtf seed if your seed files are still empty.

Part 3 - query the results

SELECT * FROM demo.customerfullname;

You should see the concatenated customer names, exactly as the mapping defined them. A helper script is included as well:

python query_duckdb.py

What to try next

  • Add an entity or mapping. Define it in CrossModel, re-run generation, and the corresponding dbt model appears. This is the loop the example is built to demonstrate.
  • Change materialization. dbt_project.yml materializes source and mapping models as views and warehouse models as tables. Adjust to taste.
  • Adapt the templates. The Nunjucks templates control the generated SQL patterns - change them once and every model follows.
  • Compare with Snowflake. Generating Snowflake SQL solves the same problem for a cloud warehouse, with load procedures instead of a dbt project.