How to Dynamically Add Tags to Models in dbt Based on Another Model's Values?

Help
1

The problem I’m having:

I want to run models using --select tag:... , but the tag needs to be added dynamically based on a value from another model. However, I can’t figure out how to add the tag dynamically to the model.

The context of why I’m trying to do this:

I have multiple clients with the same data sources. Each client’s data needs to be refreshed on different schedules: hourly, daily, weekly, etc. With hundreds of clients and tables, I don’t want to rewrite models for each client and each source multiple times. I have created a model that generates tables in distinct environments (distinct Snowflake databases). Since data refresh schedules can change, I can’t hardcode tags in the models.

What I’ve already tried:

My source.yml file looks as this:

version: 2

sources:
  - name: facebook_ads
    database: FIVETRAN_DATABASE
    schema: "{{ env_var('DBT_SCHEMA') ~ 'FACEBOOK_ADS' }}"
    tables: &facebook_ads_tables
      - name: ACCOUNT_HISTORY
      - name: AD_HISTORY
      - name: CAMPAIGN_HISTORY


  - name: settings
    database: settings
    schema: customers
    tables:
      - name: customers_database

I have multiple environments for each client with settings to determine the target database and schema.

I think macros should help add the tag based on a value in the customers_database model. I created the following macro:

{% macro get_sync_frequency(environment_name) %}
    {% set sql %}
        SELECT SYNC_FREQUENCY
        FROM {{ ref('stg_customers_database') }}
        WHERE SCHEMA_NAME = '{{ environment_name }}'
    {% endset %}

    {% set result = run_query(sql) %}

    {% if result and result.rows %}
        {% set sync_frequency = load_result(result).rows[0][0] %}
        {{ return(sync_frequency) }}
    {% else %}
        {% do log("No result found for environment_name: " ~ environment_name, info=True) %}
        {{ return('default_frequency') }}
    {% endif %}
{% endmacro %}

Then I add this to the top of the model:

{% set sync_frequency = get_sync_frequency(env_var('DBT_ENVIRONMENT_NAME')) %}

{{ config(
    tags=[sync_frequency]
) }}

Also, in dbt_project.yml file I added the following:

on-run-start:
  - "{{ get_sync_frequency(env_var('DBT_ENVIRONMENT_NAME')) }}"

However, this does not work; no tags are added to the model.
What am I doing wrong here?

2

Hello, we have exactly the same needs for one of our project. Any solution found since the initial post ? Thanks in advance.