Skip to main content

dbt Write-Back

dbt Write-Back allows you to develop your models and metrics in Lightdash and write back these changes to your dbt project. Writing back to dbt will create a new branch and start a pull request in your dbt project so you can save and manage these changes in your dbt project.

info
  • dbt Write-Back is currently only available for dbt projects hosted in GitHub.
  • You need to be at least a project developer to use dbt Write-Back.

Write-Back metrics from custom metrics​

You can build custom metrics in Lightdash and write these back to your dbt project.

We recommend using dbt write-back for custom metrics that you're using/creating frequently so they become metrics that are reusable, governed and available for everyone else to build from.

info

What's the difference between a metric and a custom metric?

Metrics written to your dbt project (or YAML metrics) are reusable by everyone in your project and will show up in the list of available metrics for a Table. Custom metrics are only saved in the chart they're used in and will not be saved in the list of metrics available if you open the same Table to build a new chart.

To get started, create a custom metric in the Explorer. Hover over the custom metric in the sidebar and click on the three-dot menu, then write back to dbt.

Once the pull request with your new metric is merged, you can click refresh dbt in Lightdash (or, if you're using GitHub actions, your project will automatically refresh once your changes are merged) and your custom metric will be replaced by your new YAML metric automatically.

info

Any custom metric that matches the definition of your new YAML metric will automatically be replaced by your YAML metric. This means if someone created the same custom metric, with the same, formatting, filtering, etc. in another saved chart, it will also be replaced automatically with your new YAML metric.

Write-Back models from the SQL Runner​

You can use Lightdash to build models in dbt from queries that you've built in the SQL runner. We recommend writing back models from the SQL runner if you're planning to use the query regularly so it can be easily used, governed and managed like other dbt models in your Lightdash project.

To get started, build and run a query in the SQL runner, then 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