dbt-jobs-as-code

dbt-jobs-as-code is a tool built to handle dbt Cloud Jobs as well-defined YAML files.

It offers some advanced configuration options, like some templating capability to use the same YAML file to update different dbt Cloud projects and/or environments (see templating).

A given dbt Cloud project can use both jobs-as-code and jobs-as-ui at the same time, without any conflict.

The way we differentiate jobs defined from code from the ones defined from the UI is that the code ones have a name ending with [[<identifier>]].

⚠️ Important: If you plan to use this tool but have existing jobs ending with [[...]] you should rename them before running any command.

Below is a demonstration of how to use dbt-jobs-as-code as part of CI/CD, leveraging the new templating features.

!

Why not Terraform

Terraform is widely used to manage infrastructure as code. And a comprehensive Terraform provider exists for dbt Cloud, able to manage dbt Cloud jobs (as well as most of the rest of the dbt Cloud configuration like projects, environments, warehouse connections etc...).

Terraform is much more powerful but using it requires some knowledge about the tool and requires managing/storing/sharing a state file, containing information about the state of the application.

With this package's approach, people don't need to learn another tool and can configure dbt Cloud using YAML, a language used across the dbt ecosystem:

• no state file required: the link between the YAML jobs and the dbt Cloud jobs is stored in the jobs name, in the [[<identifier>]] part (or in the job description when using --use-desc-for-id)
• YAML: dbt users are familiar with YAML and we created a JSON schema allowing people to verify that their YAML files are correct
• by using filters like --project-id, --environment-id or --limit-projects-envs-to-yml people can limit the projects and environments checked by the tool, which can be used to "promote" jobs between different dbt Cloud environments

But why not both dbt-jobs-as-code and Terraform?

But more than being exclusive from each other, dbt-jobs-as-code and Terraform can be used together:

• with dbt-jobs-as-code being used to manage the day to day jobs (handled by the data team)
• and Terraform being used to manage the rest of the dbt Cloud configuration and even CI and merge jobs (handled by the platform or central team)

Usage

Installation

With uv (recommended)

We recommend using uv/uvx to run the package. If you don't have uv installed, you can install uv and uvx, following the instructions on the official website.

• to run the latest version of the tool: uvx dbt-jobs-as-code
• to run a specific version of the tool: uvx dbt-jobs-as-code@0.9.0
• to install the tool as a dedicated CLI: uv tool install dbt-jobs-as-code
• to upgrade the tool installed as a dedicated CLI: uv tool upgrade dbt-jobs-as-code

With pip

You can also use pip if you prefer, but we then recommend installing the tool in its own Python virtual environment. Once in a venv, install the tool with pip install dbt-jobs-as-code and then run dbt-jobs-as-code ...

Pre-requisites

The following environment variables are used to run the code:

• DBT_API_KEY: [Mandatory] The dbt Cloud API key to interact with dbt Cloud. Can be a Service Token (preferred, would require the "job admin" scope) or the API token of a given user
• DBT_BASE_URL: [Optional] By default, the tool queries https://cloud.getdbt.com, if your dbt Cloud instance is hosted on another domain, define it in this env variable (e.g. https://emea.dbt.com)

Commands

The CLI comes with a few different commands

validate

Command: dbt-jobs-as-code validate <config_file_or_pattern.yml>

Validates that the YAML file has the correct structure

• it is possible to run the validation offline, without doing any API call
• or online using --online, in order to check that the different IDs provided are correct
• it supports templating the jobs YAML file (see templating)

plan

Command: dbt-jobs-as-code plan <config_file_or_pattern.yml>

Returns the list of actions create/update/delete that are required to have dbt Cloud reflecting the configuration file

• this command doesn't modify the dbt Cloud jobs
• this command can be restricted to specific projects and environments
  • it accepts a list of project IDs or environments IDs to limit the command for: dbt-jobs-as-code plan <config_file_or_pattern.yml> -p 1234 -p 2345 -e 4567 -e 5678
    • it is possible to limit for specific projects and/or specific environments
    • when both projects and environments are provided, the command will run for the jobs that are both part of the environment ID(s) and the project ID(s) provided
  • or it accepts the flag --limit-projects-envs-to-yml to only check jobs that are in the projects and environments listed in the jobs YAML file
• it supports templating the jobs YAML file (see templating)
• deleting jobs from dbt Cloud by removing them from the YAML file(s) requires the command to be scoped with both -p/--project-id and -e/--environment-id:
  • if the config file(s) have jobs left for other projects/environments, but none for the requested -p/-e scope, those dbt Cloud jobs get proposed for deletion
  • if the matched config file(s) declare an empty jobs key (e.g. jobs: {}, or every job was removed), --account-id is also required, since there is no job left anywhere to read the account ID from
  • a pattern/directory that doesn't match any job-bearing YAML at all (no file declares a jobs key) is always a no-op and logs a warning to check the config pattern, regardless of scoping
  • in the other two cases, if the required flags are missing, the command logs a warning naming exactly which one(s) to add instead of silently doing nothing

sync

Command: dbt-jobs-as-code sync <config_file_or_pattern.yml>

Create/update/delete jobs and env vars overwrites in jobs to align dbt Cloud with the configuration file

• ⚠️ this command will modify your dbt Cloud jobs if the current configuration is different from the YAML file
• this command can be restricted to specific projects and environments
  • it accepts a list of project IDs or environments IDs to limit the command for: dbt-jobs-as-code sync <config_file_or_pattern.yml> -p 1234 -p 2345 -e 4567 -e 5678
    • it is possible to limit for specific projects and/or specific environments environment ID(s) and the project ID(s) provided
  • or it accepts the flag --limit-projects-envs-to-yml to only check jobs that are in the projects and environments listed in the jobs YAML file
• it supports templating the jobs YAML file (see templating)
• same as plan, deletions (see above) are only reconciled when explicitly scoped with -p/--project-id and -e/--environment-id (and --account-id for an emptied config file)

import-jobs

Command: dbt-jobs-as-code import-jobs --config <config_file_or_pattern.yml> or dbt-jobs-as-code import-jobs --account-id <account-id>

Queries dbt Cloud and provide the YAML definition for those jobs. It includes the env var overwrite at the job level if some have been defined

• it is possible to restrict the list of dbt Cloud Job IDs by adding ... -j 101 -j 123 -j 234
• this command also accepts a list of project IDs or environments IDs to limit the command for: dbt-jobs-as-code sync <config_file_or_pattern.yml> -p 1234 -p 2345 -e 4567 -e 5678
• this command accepts a --include-linked-id parameter to add the dbt Cloud job ID of each job to the YAML as linked_id. That ID is what link (to manage the jobs with sync, by renaming them) and update-jobs (to update them in place, without renaming them) use to find the jobs in dbt Cloud
• once the YAML has been retrieved, it is possible to copy/paste it in a local YAML file to create/update the local jobs definition.

Once the configuration is imported, it is possible to "link" existing jobs by using the link command explained below, or to change them in place with update-jobs.

link

Command: dbt-jobs-as-code link <config_file_or_pattern.yml>

Links dbt Cloud jobs with the corresponding identifier from the YAML file by renaming the jobs, adding the [[ ... ]] part in the job name.

To do so, the program looks at the YAML file for the config linked_id. linked_id can be added manually or can be added automatically when calling dbt-jobs-as-code import-jobs with the --include-linked-id parameter.

Accepts a --dry-run flag to see what jobs would be changed, without actually changing them.

When using --use-desc-for-id, the [[ ... ]] tag is stored in the job description instead of the job name.

unlink

Command: dbt-jobs-as-code unlink --config <config_file_or_pattern.yml> or dbt-jobs-as-code unlink --account-id <account-id>

Unlinking jobs removes the [[ ... ]] part of the job name (or description, when using --use-desc-for-id) in dbt Cloud.

⚠️ This can't be rolled back by the tool. Doing a unlink followed by a sync will create new instances of the jobs, with the [[<identifier>]] part

• it is possible to restrict the list of jobs to unlink by adding the job identifiers to unlink ... -i import_1 -i my_job_2

update-jobs

Command: dbt-jobs-as-code update-jobs <config_file_or_pattern.yml>

Updates existing dbt Cloud jobs from a YAML file, without linking them: no [[ ... ]] is added to the job names. This is useful to change a parameter on many jobs at once, e.g. to add dbt_state to the cost_optimization_features of all the jobs in an environment:

dbt-jobs-as-code import-jobs --account-id 1234 --environment-id 5678 --include-linked-id > jobs.yml
# edit jobs.yml
dbt-jobs-as-code update-jobs jobs.yml --dry-run
dbt-jobs-as-code update-jobs jobs.yml

• each job in the YAML is matched with the dbt Cloud job having the ID set in its linked_id (added by import-jobs --include-linked-id). The key of the job in the YAML (import_1...) is only a label and is not stored in dbt Cloud
• the command only updates existing jobs. It never creates, deletes, links or unlinks jobs, and the jobs in dbt Cloud that are not in the YAML are left untouched. Only the jobs that differ from dbt Cloud are updated
• all the jobs in the YAML are checked before anything is updated. If a job has no linked_id, if a linked_id is used twice or doesn't exist in dbt Cloud, the command fails and nothing is updated
• jobs already managed with sync (with an [[<identifier>]]) can be updated if their key in the YAML is their identifier, which is what import-jobs generates. Their identifier is kept as is
• custom_environment_variables in the YAML are not updated, use sync for those
• when jobs are managed with the identifier in the description, pass --use-desc-for-id: without it, the command refuses to update jobs whose description contains a [[ ... ]] tag, as that would remove the tag and unlink them
• after updating, the command lists the jobs updated and, if any, the jobs that were not updated (for example because dbt Cloud rejected the change), so a partial failure is easy to follow up on
• like sync, each updated job is sent to dbt Cloud in full, so settings that this tool doesn't support yet and that were set in the UI could be reset on the jobs that are updated. Review the --dry-run output and the jobs updated
• accepts --dry-run to see the differences without updating dbt Cloud, --fail-fast to stop at the first job failing to update, -p/-e to restrict the jobs of the YAML to specific projects/environments, and --vars-yml for templated YAML files
• compared to sync, which makes dbt Cloud match the YAML (including creating and deleting jobs), update-jobs is meant for one-off changes. Use sync to manage jobs long-term

deactivate-jobs

Command: dbt-jobs-as-code deactivate-jobs --account-id 1234 --job-id 12 --job-id 34 --job-id 56

This command can be used to deactivate both the schedule and the CI triggers for dbt Cloud jobs. This can be useful when moving jobs from one project to another. When the new jobs have been created, this command can be used to deactivate the jobs from the old project.

Job Configuration YAML Schema

The file src/dbt_jobs_as_code/schemas/load_job_schema.json is a JSON Schema file that can be used to verify that the YAML config files syntax is correct and to provide completion suggestions for the different fields supported.

To use it in VSCode, install the extension YAML and add the following line at the top of your YAML config file (change the path if need be):

# yaml-language-server: $schema=https://raw.githubusercontent.com/dbt-labs/dbt-jobs-as-code/main/src/dbt_jobs_as_code/schemas/load_job_schema.json

Templating jobs YAML file

validate, sync and plan support templating the YML jobs file since version 0.6.0

To do so:

• update the jobs YAML file by setting some values as Jinja variables
  • e.g project_id: {{ project_id }} or environment_id: {{ environment_id }}
• and add the parameter --vars-yml (or -v) pointing to a YAML file containing values for your variables

The file called in --vars-yml needs to be a valid YAML file like the following:

project_id: 123
environment_id: 456

There are some example of files under example_jobs_file/jobs_templated.... Those examples also show how we can use Jinja logic to set some parameters based on our variables.

When using templates, you might also want to use the flag --limit-projects-envs-to-yml. This flag will make sure that only the projects and environments of the rendered YAML files will be checked to see what jobs to create/delete/update.

Templating also allows people to version control those YAML files and to have different files for different development layers, like:

• dbt-jobs-as-code jobs.yml --vars-yml vars_qa.yml --limit-projects-envs-to-yml for QA
• dbt-jobs-as-code jobs.yml --vars-yml vars_prod.yml --limit-projects-envs-to-yml for Prod

The tool will raise errors if:

• the jobs YAML file provided contains Jinja variables but --vars-yml is not provided
• the jobs YAML file provided contains Jinja variables that are not listed in the --vars-yml file

Summary of parameters

As a reminder using --project-id and/or --environment-id is not compatible with using --limit-projects-envs-to-yml. We can only restricts by providing the IDs or by forcing to restrict on the environments and projects in the YML file.

Running the tool as part of CI/CD

An example of GitHub Action is provided in the example_cicd folder. This example requires having set the GitHub secret DBT_API_KEY.

You can copy/paste this file in your own repo under .github/workflows. The current script except your jobs yml file to be saved under jobs/jobs.yml

After a PR on main is approved, the action will run a sync to compare the local yml file with the dbt Cloud configuration and will create/update/delete dbt Cloud jobs to align the two.

Reporting bugs and contributing code

• Want to report a bug or request a feature? Let us know by opening an issue
• Want to help us build dbt? Check out the Contributing Guide