1

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.

Paul
  • 3,321
  • 1
  • 33
  • 42
  • 1
    I would definitely recommend Method 1 over Method 2. You may even want to have a different database, one for prod and one for staging. I don't see why creating schemas should be limited by your admins. You need to have schema create privileges with your DBT role anyway. Seems a little too controlling to make schema creation an issue. Every developer should have their own schema for development purposes. – Adam Kipnis Feb 11 '23 at 01:51
  • I work at a big company and this is how it is. There's no way they'll ever give me schema create privileges. Maybe one day we'll have a staging database with more flexibility. – Paul Feb 11 '23 at 02:42

1 Answers1

1

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.

Adam Kipnis
  • 10,175
  • 10
  • 35
  • 48