Source table configuration based on prefix

Help
1

I have a schema with tables that have the same structure for different accounts that I am dynamically unioning using Jinja.

I would like to make the source configuration dynamic so if we add new accounts in the future, the source config (freshness, tests) will automatically run on these tables.

Example:

myschema.advertising_data_account1
myschema.advertising_data_account2
myschema.advertising_data_account3

What I would like to be able to do is something like this in sources.yml

tables:
- name: advertising_data
prefix: advertising_data
freshness:
warn_after: {count: 12, period: hour}
error_after: {count: 24, period: hour}

I found this article which is close, but I’d rather not split into separate schemas unless that is the only way-

Is this possible?

2

You can probably just skip the step of creating sources for all of these tables. You lose a little bit of observability, but gain a lot of productivity.

dbt_utils has a macro called get_relations_by_pattern to easily build new Relations for matching tables in your database: dbt-labs/dbt-utils: get_relations_by_pattern. (github.com). The name is a little misleading – you don’t use this to get relations that are already defined in the graph, you use it to construct new Relation objects out of tables in your database.

You could use it like this:

-- models/staging/myschema/stg__advertising_data.sql
{% set source_tables = dbt_utils.get_relations_by_pattern(
    'myschema', 'advertising_data_account%'
) %}
{{ dbt_utils.union_relations(relations=source_tables) }}

Then in lieu of your source freshness test, you can create a custom data test that checks for the same thing. The union_relations macro adds a column called _dbt_source_relation that contains the name of the source table, which is useful here:

-- tests/test_advertising_data_freshness
select _dbt_source_relation, max(updated_at)
from {{ ref('stg__advertising_data') }}
group by 1
having max(updated_at) < sysdate() - interval '12 hours'
3

Thanks - i’m using dbt_utils.get_relations_by_pattern already, but didn’t think about making the custom test.

The custom test should work for my needs.