How to - override the default database using the generate_database_name macro when triggering a run via the dbt Cloud API

[jeremyyeo]

The following playbook covers:

1. Triggering jobs to run with the dbt Cloud API.
2. Customising the database that dbt builds into using the generate_database_name macro.

It is expected that you already know:

• The basics of how dbt works.
• How to create dbt Cloud jobs.
• How to use API's (via Python, curl or some other method).

We will be making use of @boxysean's Python code in this discourse post.

---

1. Add the following macro to your project:

-- macros/get_custom_database.sql
{% macro generate_database_name(custom_database_name=none, node=none) -%}

    {%- set selected_database = var('db_override', 'development') -%}

    {{ selected_database | trim }}

{%- endmacro %}

The generate_database_name macro is responsible for determining the database that dbt uses when it runs. You can incorporate much more complex logic (see above linked documentation to it) to it but what we're doing here is we're instructing dbt to look for a variable called db_override (that we can set at runtime) and use that as the name of the database. Note that if the variable is not set during the run, the value of 'development' will be assigned to that variable by default (and that is what will determine the database name).

2. Create a simple dbt Cloud job that we can trigger with our Python script:

Note that the ACCOUNT_ID and JOB_ID values that we will be using in our script can be retrieve from the URL when you are on the jobs page. For example, a URL like https://cloud.getdbt.com/#/accounts/12345/projects/54321/jobs/11223/settings/ means that your ACCOUNT_ID = 12345 and your JOB_ID = 11223.

3. Copy the following script somewhere and change the ACCOUNT_ID and JOB_ID accordingly:

Click to expand...

import enum
import os
import time

# Be sure to `pip install requests` in your python environment
import requests


ACCOUNT_ID = <your-dbt-cloud-account-id>
JOB_ID = <your-dbt-cloud-account-id>

# Store your dbt Cloud API token securely in your workflow tool
API_KEY = os.getenv('DBT_CLOUD_API_TOKEN')


# These are documented on the dbt Cloud API docs
class DbtJobRunStatus(enum.IntEnum):
    QUEUED = 1
    STARTING = 2
    RUNNING = 3
    SUCCESS = 10
    ERROR = 20
    CANCELLED = 30


def _trigger_job() -> int:
    res = requests.post(
        url=f"https://cloud.getdbt.com/api/v2/accounts/{ACCOUNT_ID}/jobs/{JOB_ID}/run/",
        headers={'Authorization': f"Token {API_KEY}"},
        json={
            'cause': f"Triggered by my workflow!",
        }
    )

    try:
        res.raise_for_status()
    except:
        print(f"API token (last four): ...{API_KEY[-4:]}")
        raise

    response_payload = res.json()
    return response_payload['data']['id']


def _get_job_run_status(job_run_id):
    res = requests.get(
        url=f"https://cloud.getdbt.com/api/v2/accounts/{ACCOUNT_ID}/runs/{job_run_id}/",
        headers={'Authorization': f"Token {API_KEY}"},
    )

    res.raise_for_status()
    response_payload = res.json()
    return response_payload['data']['status']


def run():
    job_run_id = _trigger_job()

    print(f"job_run_id = {job_run_id}")

    
    while True:
        time.sleep(5)

        status = _get_job_run_status(job_run_id)

        print(f"status = {status}")

        if status == DbtJobRunStatus.SUCCESS:
            break
        elif status == DbtJobRunStatus.ERROR or status == DbtJobRunStatus.CANCELLED:
            raise Exception("Failure!")


if __name__ == '__main__':
    run()

4. Run the script after exporting the DBT_CLOUD_API_TOKEN env var with your dbt Cloud API Key:

$ export DBT_CLOUD_API_TOKEN=XXXXXX
$ python main.py
job_run_id = XXXXXX
status = 1
status = 1
status = 3
status = 3
status = 3
status = 10

Check that the run successfully created our model in the development database (default behaviour since we didn't pass in the db_override var at run time:

5. Now let's override the steps of the run by modifying the json payload in the script above:

...
    res = requests.post(
        url=f"https://cloud.getdbt.com/api/v2/accounts/{ACCOUNT_ID}/jobs/{JOB_ID}/run/",
        headers={"Authorization": f"Token {API_KEY}"},
        json={
            "cause": f"Triggered by my workflow!",
            "steps_override": [
                "dbt run --vars 'db_override: analytics'"
            ]
        },
    )
...

6. Run the script with the new payload:

$ python main.py
job_run_id = XXXXXX
status = 1
status = 1
status = 3
status = 3
status = 3
status = 10

Check that the run successfully created our model in the analytics database:

[boxysean]

Hey @jeremyyeo! Thanks for this. :-)

My initial reaction is "why are the two topics combined into one post?" I have to admit, I share my Discourse post you linked to customers and prospects at least once a week. I would love to have this resource available on the docs site rather than Discourse, but I don't typically talk about the generate_database_name functionality at the same time.

So my ask is... could this be broken out into two separate posts? One that replaces my Discourse post, and the other about generate_database_name? Would that work for you too, or is there a compelling reason why these two topics are combined into one post?

[boxysean]

Hi @jeremyyeo yeah I definitely understand the balance and appreciate your of post for the reasons you describe. 😄 In my role I also need to do the same kind of educating, though in a different context. The scope of my original Discourse post is the right amount of information for my purpose -- just today I had a prospective customer how to use Azure Data Factory and dbt Cloud together. I would feel hesitant to share the post with a broader scope in response, so I would likely stick with my Discourse post!

[jasnonaz]

I think we can do both here - port @boxysean's post over to the Developer Blog and create a How-To for the hyper-specific guides like Jeremy has put together. It's going to be really great to build up a library of those that we can share over time.

Sean - do you think we would want to update your Discourse post on a structural level at or should we port it over as is, with potentially a bit of added context. If we want to update it, then we could create a new Discussion. If we want to keep it mostly the same, I think we can go to an issue and start moving forward getting this up.

[gwenwindflower]

exactly what jason said here -- i think we can create a clearer space for quickies for sure but there's actually a fair bit of depth here that's worthy of being a Devhub Guide or Developer Blog Article. And we can always of course graduate quick and tactical discussion examples up as needed/wanted.

One additional to thing to highlight: we want to reserve the Playbook terminology for a specific type of Guide that comes with a repo and covers something tactical. Think MRR Playbook etc, we want to continue that tradition of Playbook being a repo you can clone and walk through a Guide to learn a technique. That said -- I'm open to whatever name we do give quick examples -- I'm going to spike something out now, but flexible if you all have ideas! 🙏🏻 Thank you for helping to shape this!

[boxysean]

Hi all, just coming back from vacation. :-)

Sean - do you think we would want to update your Discourse post on a structural level at or should we port it over as is, with potentially a bit of added context. If we want to update it, then we could create a new Discussion. If we want to keep it mostly the same, I think we can go to an issue and start moving forward getting this up.

I will leave it to you, dear Developer Blog editors, to decide whether it should be reworked or kept the same. My main requirement for the new post on the Developer Blog would be that it is very easy to share with someone who has the question, "how do I trigger dbt Cloud from my orchestrator?". That's what my Discourse post aims to address, and I think it does so fine.

A couple of new developments since I wrote my original post was the inclusion of the dbt Cloud Operators in Airflow and Prefect main branches, so perhaps a small update is in order?

[gwenwindflower]

sung actually has a fantastic and thorough airflow guide that should be ready later this month -- this could perhaps just be a sub-module of that if it's not already included

[gwenwindflower]

The Airflow + dbt Cloud guide is now live if you want to try adding/expanding on it with some of this!