How to get models to run only on local timezones if your scheduler only supports UTC #dbt

README.md

How to get models to run only on local timezones if your orchestration platform only supports UTC

If you're using an orchestration platform that only supports UTC timezones - you may find it difficult cumbersome to schedule jobs to run on local timezones, accounting for daylight savings time switchovers, etc. Let's see how we can write a dbt macro using some builtin python modules to help us out.

-- macros/is_nz_business_hours.sql
{% macro is_nz_business_hours() %}
    {% set flag = 0 %}
    {% set dt = modules.datetime %}
    {% set pz = modules.pytz %}

    {% set time_utc = dt.datetime.utcnow() %}
    {% set time_utc = pz.timezone("UTC").localize(time_utc) %}
    {% set time_local = time_utc.astimezone(pz.timezone("Pacific/Auckland")) %}

    {#/* We only want our model to run on weekdays between 9 am and 5 pm in New Zealand. */#}
    {% if time_local.weekday() < 5 %}
        {% set flag = flag + 1 %}
    {% endif %}

    {% if time_local.hour >= 9 and time_local.hour <= 16 %}
        {% set flag = flag + 1 %}
    {% endif %}

    {% if execute %}
        {% do log("Current time: " ~ time_local.strftime("%a, %b %d, %Y %-I:%M:%S %p %Z") ~ " | " ~ time_utc.strftime("%a, %b %d, %Y %-I:%M:%S %p %Z")) %}
        {% if flag == 2 %}
            {% do log("Business hour") %}
        {% else %}
            {% do log("Happy hour") %}
        {% endif %}
    {% endif %}

    {% do return(flag) %}
{% endmacro %}

{% macro enable_or_disable() %}
    {% if is_nz_business_hours() == 2 %}
        {{ return(True) }}
    {% else %}
        {{ return(False) }}
    {% endif %}
{% endmacro %}

In the macros above, we're checking to see if the current time is in within our "business hours" example (9 am - 5 pm weekdays NZT). We can then use the enable_or_disable() macro in our models enabled config like so:

-- models/foo.sql
{{ config(..., enabled = enable_or_disable()) }}
...

Note: Pay close attention to how we're using the macro in the config block - it is more typical (best practice) to have the macro be specified as a string with jinja like:

{{ config(post_hook = '{{ some_macro() }}') }}

However, due to dbt's multi-step parsing/compiling/executing phases - we need to deviate from this best practice.

Let's see how this plays out in dbt Cloud with a project setup like:

-- models/all_hours.sql
{{ config(materialized = 'incremental') }}

select sysdate() as time_utc
       , convert_timezone('Pacific/Auckland', current_timestamp()) as time_local
       , {{ enable_or_disable() }} as is_nz_business_hours


-- models/business_hours.sql
{{ config(materialized = 'incremental', enabled = enable_or_disable()) }}

select sysdate() as time_utc
       , convert_timezone('Pacific/Auckland', current_timestamp()) as time_local
       , {{ enable_or_disable() }} as is_nz_business_hours

The all_hours model will run everytime the job runs but business_hours will only run if the job triggers between 9 AM and 5 PM, Monday to Friday.

And a dbt Cloud job with a cron schedule that runs at every 30 minutes past the hour (30 * * * *):

We can see that the job that ran at 4:30 PM included the business_hours model while the subsequent one at 5:30 PM did not:

If all of our models had the same enabled = enable_or_disable() config, effectively this would be a job that is local timezone aware even if the job runs on a different timezone UTC - since models would only be enabled at times you actually want them to be.