Combining dbt_utils.get_relations_by_pattern with dbt_utils.union_relations breaks lineage in Redshift

Help
, ,
1

The problem I’m having

We have a few models, that select from a longer list of other models and combine them with a “union all”. Thus we found the two macros in dbt_utils, that in combination makes the modeling much more handy than ever before. However, we just stumbled over an issue, that this breaks the lineage at least on Redshift. What basically happens is, that the complete lineage left of the union model is missing. And since we are using a pattern like “prefix_%” it happens sometimes, that the “_tmp” tables created by dbt end up in the list of relations, but are already gone, when creating the union relation, which in the end leads to an error.

What I’ve already tried

The following works and is also documented like this in the docs, but breaks the lineage:

{% set rels = dbt_utils.get_relations_by_pattern(
  schema_pattern='<schema>',
  table_pattern='<tables>_%',
) %}

{{ dbt_utils.union_relations(
    relations=rels,
) }}

I understood, that this is the intended way, like it was discussed e.g. here: Unioning identically-structured data sources

Therefore I’ve tried to get the result of get_relations_by_pattern (taking the identifier) and put it into a a list combining the model name with the “ref()” function:

{% set tables = [] %}
{% for rel in rels %}
    {% set table = "ref('" + rel.identifier + "')" %}
    {% set _ = tables.append(table) %}
{% endfor %}

{{ dbt_utils.union_relations(
    relations=tables | string(),
) }}

However, passing this list as a string to union_relations brings the error:

Macro union_relations expected a Relation but received the value: [

    > in macro _is_relation (macros\jinja_helpers\_is_relation.sql)
    > called by macro default__union_relations (macros\sql\union.sql)
    > called by macro union_relations (macros\sql\union.sql)
[...]

Funny enough, when debugging the string of tables as text and copying this text into “relations” manually, it works exactly as I would expect it. Is there a way, how we can get this to work?

2

When not explicitly doing ref() in your model - the lineage will break unfortunately. This is expected and there really are no workarounds.

Note: @Jeremy Yeo originally posted this reply in Slack. It might not have transferred perfectly.

3

I understood, that we have to use an explicit ref on the relations, but why is it not working, when passing on a string, that was enriched with the “ref()” function. I would have guessed, that this should be handled by the compilation or am I getting this wrong? I’m not an expert on python or jinja, so I might get things wrong obviously.