dbt write-back

For a project using standalone YAML, see Lightdash YAML write-back.

​

Control who can write back

The write-back workflows on this page are controlled by two scopes, both included in the Developer role and above:

• manage:CustomFields lets a user write back custom metrics and custom dimensions. It also lets them create and edit custom dimensions.
• manage:SourceCode (Create pull requests to update source code) lets a user write back models from the SQL runner. It also controls whether AI agents can open write-back pull requests for them.

To give someone Developer access without write-back, duplicate the Developer role as a custom role and remove both scopes. Removing only one leaves the other write-back path open. Access to the repository in GitHub doesn’t decide who can write back. When a user’s linked GitHub account can’t access the repository, Lightdash opens the pull request with its GitHub App instead.

​

Bitbucket Cloud setup

Connect your project using the Bitbucket token setup instructions. All three workflows below use that stored project token and the configured repository, branch, and dbt directory. Bitbucket support covers dbt projects on bitbucket.org. These UI workflows do not support standalone Lightdash YAML on Bitbucket or Bitbucket Server/Data Center. For native YAML on Bitbucket Cloud, use AI writeback. Review and merge the pull request in Bitbucket, then sync the project in Lightdash. Writeback does not run your dbt models, display Bitbucket Pipelines status, or create an automatic preview project.

​

Write back Metrics from Custom Metrics

You can build Custom Metrics in Lightdash and write these back to your dbt project so they are promoted to permanent Metrics in your YAML. We recommend using dbt write-back for custom metrics that you’re creating frequently so they become Metrics that are reusable, governed and available for everyone else to build from. 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 available if you or others 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 project. Write-back requires a base dimension column. Write back to project is only available for custom metrics that were built from a dimension (the YAML write-back attaches the metric to that dimension in the dbt model). Custom metrics that were cloned from an existing metric do not have a base dimension, so the option is hidden from both the individual metric menu and the bulk write-back action in the sidebar’s Custom metrics section. 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. 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 Dimensions from Custom Dimensions

You can build Custom Dimensions in Lightdash and write these back to your dbt project. We recommend using dbt write-back for Custom Dimensions that you’re creating frequently so they become Dimensions that are reusable, governed and available for everyone else to build from. What’s the difference between a dimension and a custom dimension? Dimensions written to your dbt project (or YAML Dimensions) are reusable by everyone in your project and will show up in the list of available Dimensions for a Table. Custom Dimensions are only saved in the chart they’re used in and will not be available if you or others open the same Table to build a new chart. To get started, create a Custom Dimension in the Explorer. Hover over the Custom Dimension in the sidebar and click on the three-dot menu, then Write back to project.

1. Click Write back to project
   
2. Preview the Dimension YAML and open a pull request.
   
3. Approve and merge the pull request in your dbt project.
   

Once the pull request with your new Dimension 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).

​

Known limitations

Bin Dimensions

You can write back fixed-width, custom-range, and custom-group bins. Fixed-number bins can’t be written back, because their boundaries depend on the query results; switch them to one of the other bin types first. Writing back a bin doesn’t carry its sort order. Saved charts keep using the custom bin, so their ordering doesn’t change. If bin order matters, add a separate numeric ordering dimension to your model and use it with the new YAML dimension.

​

Write-back Dimensions automatically replacing Custom Dimensions

At the moment, if you write back a Custom Dimension to your dbt project’s YAML, the Custom Dimension will not be automatically replaced with the new YAML Dimension. Once your new Dimension is created in dbt, you’ll need to manually update your charts and replace the Custom Dimensions with the new Dimension.

​

Write back dbt 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, GitLab, or Bitbucket Cloud. For Bitbucket Cloud, the dialog previews the repository and both filenames before opening the pull request: Bitbucket writeback creates the SQL and YAML files together in one commit. If either filename already exists, choose a different model name; writeback does not overwrite the existing model. 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.

​

Getting your model to appear as a Table in Lightdash

You might need to adjust Tables configuration in your project settings 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