Skip to main content

dbt Write-Back

dbt Write-Back allows you to create models in dbt from queries you've built in the SQL Runner. You can then explore these models as Tables in Lightdash. Unlike Virtual Views, these models will be saved to and managed in your dbt project.

We recommend using dbt Write-Back if you're planning to use a query regularly so it can be governed and managed like other dbt models available in your Lightdash project.

info

dbt Write-Back is currently only available for dbt projects hosted in GitHub.

Getting started with dbt Write-Back​

Writing back to dbt will create a new branch and start a pull request in your dbt project to create a new .sql and .yml file based off of your SQL Runner query.

To get started, select the Write back to dbt option from the save drop-down.

You'll be asked to enter a name that will be used as the model name and file names in your dbt project. Clicking open pull request will open a pull request created by Lightdash against your dbt project in GitHub.

The new model will be written to your dbt project in a models/lightdash/ directory. The model will have the tag created-by-lightdash included in the model config.

Configuring your dbt write-back models in Lightdash​

Getting your model to appear as a Table in Lightdash​

You might need to adjust your Table Configuration settings in your project to have your new models appear as Tables in Lightdash.

If you've selected:

  • show entire project then you don't need to do anything. The models should appear automatically if you refresh your dbt project connection.
  • show models with any of these tags, you'll need to add created-by-lightdash to the list of accepted tags. By default, models that you write back to dbt will have the tag created-by-lightdash included in the model config.
  • show models in this list, you'll need to manually add the new models to the list of accepted models here.

Running your models in dbt​

If you get an Error: not found your_table_name, it's likely because you haven't built the table generated by your new model in your data warehouse yet. There are a couple of ways to handle this:

Manual approach​

Once your model is merged to your dbt project, you can run dbt run -s your_model_name_here to create the table in your data warehouse. You might need to add --target prod if your default profile is set to dev!

Automated approach using dbt Cloud​

If you're using dbt Cloud to manage your dbt project, you can create a job that will automatically run any new models with the tag created-by-lightdash.

To create a job that does this, you'll want to:

  1. open dbt Cloud, head to Deploy --> Jobs and click create new job
  2. toggle on triggered by pull requests in the Git Trigger section
  3. add the command dbt run --select tag:created-by-lightdash
  4. set compare changes against environment to prod. This will make sure that this job only runs when a model is changed from your pull request