Dynamically load relations only if they exist

Show and Tell
1

I have a model that has multiple sources as dependencies, but not all fonts are available at the time dbt runs because they are dynamic.

Is there a way to executing a select (or part of the code) only if a table (or schema) exists?

maybe with the get_relations_by_prefix helper? Thanks!

1 Like
2

I came up with this macro, based on the get_relations_by_prefix to return a relation if exists

{#
    Return a relation if exists in a given schema and (optionally) database

    Arguments:
    schema (required): The schema to inspect for relations.
    prefix (required): The prefix of the table/view (case insensitive)
    exclude (optional): Exclude any relations that match this pattern.
    database (optional, default = target.database): The database to inspect for relations.

#}
{% macro has_relation(schema, prefix, exclude='', database=target.database) %}

    {% set relations = dbt_utils.get_relations_by_prefix(schema, prefix, exclude, database) %}

    {% if not relations %}
        {{ return(False) }}
    {% else %}
        {{ relations }}
    {% endif %}

{% endmacro %}

In my code i have some if’s like

{% set fb_region_country_relation = has_relation('facebook_ads', 'insights_campaign_region_country') %}

{% if fb_region_country_relation %}
fb_region_country as (
    select
        {{ dbt_utils.surrogate_key('region', 'country') }}::CHAR(36) as id,
        {{ padronize_string_field('region', 'region', 'varchar(150)') }},
        {{ padronize_string_field('country', 'country', 'varchar(150)') }}

    from {{ ref('_fb_ads_insights_campaign_region_country') }}

    {% if is_incremental() %}
        where id not in (select id from {{ this }})
    {% endif %}
),
{% endif %}
3

When you use the word model do you mean one table, or a dbt project with multiple tables? For example, we have a file structure with a schema per folder and multiple .sql files inside of the folder to build all of the tables in that schema. We have the ability to either schedule all tables in that folder to run at one time, or just one table, based on which dbt run statement we execute, for example- “dbt run -m schema_folder” vs “dbt run -m schema_folder.table_file”.

It sounds like maybe, instead you’re saying that you have dynamic sources in the ETL used to build one table? For example, the sql to build one table might look like the following -
select
a.*
,b.*
,c.*
from campaign_country_a
join campaign_country_b on a.id = b.id
join campaign_country_c on a.id = c.id

It sounds like, when a campaign is turned off, then the table with data for that campaign is no longer available? If that’s the case, then why is that upstream table being dropped?

4

@ksapelak Yeah, we have an ELT script that extracts data from different marketing sources based on client configuration, then we build our dw using these tables, we have a multi database redshift with one database per client.

Both dimensions and facts are builded using different sources, ex Facebook ads and LinkedIn ads, and both share commom dim and facts, but If one of our clients enable only fb ads we need to build only using fb ads as source

5

Hi @edbizarro !
Why don’t you create an empty view for the tables that a custmer does not yet have?

create view some_name as
select null as c1, null as c2, null as c3 
where 1 = 2

Something like above allows you to create a view with the name you need and as many columns as you need but without any content, so you can keep selecting from it without knowing if the customer has that source or not.
You can even just start always with views like that and them point the right view to the real table containing the data when some of the data starts to come in.

Ciao, Roberto