CODEWITHSUNDEEP
sqlnamespacesschemaversioningdbt
Paul
Paul

Reputation: 3361

DBT - how to namespace tables generated by different versions of a project without schemas?

Let's say I have a project in dbt. When I run it, it generates a bunch of tables. Now I want to change the underlying SQL and see what happens to these tables, how they differ from before the change. So I want to be able to compare all the tables generated by the old version to all the tables generated by the new version. Ideally I would like the method to work for any number of versions, not just two. Basically the question is how to put each version in its own namespace.

Method 1: run the new version of the project in a new schema, so I can compare old.foo to new.foo. But getting another schema from the database admins is a painful process.

Method 2: Have both versions in the same schema, but add a prefix, like new_ to the table name for the new version. So, old version has table foo, new version has new_foo, and I compare foo to new_foo.

Is there any convenient way to do Method 2 in dbt? Is there a third method I should be considering? Or am I doing something fundamentally wrong to even find myself in this situation? It seems like it shouldn't be such a rare problem but I can't find any information about what I can do in this situation.

Upvotes: 2

Views: 913

Answers (1)

Adam Kipnis
Adam Kipnis

Reputation: 10971

One possible way to do this is to override the default alias macro. The macro gets called even if there is no alias defined in the configuration, so you can use that as an opportunity to rename the target table.

The version below will prefix any model that does not have an alias set in the configuration with name of the target profile when the run is not against the prod profile.

{% macro generate_alias_name(custom_alias_name=none, node=none) -%}
    {%- if target.name != 'prod' and custom_alias_name is none -%}
        {{ target.name ~ "_" ~ node.name }}
    {%- elif target.name == 'prod' -%}
        {{ node.name }}
    {%- else -%}
        {{ custom_alias_name | trim }}
    {%- endif -%}
{%- endmacro %}

If your model is foo.sql and you run this against a profile named "prod", the table will be foo. If you run it against "dev", it will be dev_foo. If your model has an alias, then the alias name will take precedence regardless of the target profile. You can decide if you want to include the special behavior if the model has an alias name. Just modify the else block.

Upvotes: 2

Related Questions