DBT apply similar transformation to many tables in a loop

Question

I am checking dbt for a specific use case and I have the following question.

Can I apply/use similar transformations to many tables in some kind of loop?

For example:

I want to do the following for many tables, to insert missing rows into the target table:

INSERT into stage.<table_name> SELECT * FROM loading.<table_name> EXCEPT SELECT * FROM stage.<table_name>;

How could I do it in a FOR LOOP or any other way for all tables?

Thanks.

Answer 1

you can do this but i feel that you are progressing down a route that isn't aligned to using dbt. yes you can use a loop. however you should consider using an incremental model rather than a insert like you provide.

The steps you should take.

1. convert your thinking/logic to be an incremental model
2. create a table (externally or within a seed) with a list of tables
3. create a macro that you use in your incremental model which generates the code.

you can use something like this macro to generate a list from your table, you should then call this macro from the macro you use to generate your model code.

{% macro generate_tables_set() %}

{% set tables_sql %}
select table_name
from my_table_of_tables
{% endset %}
{%do return(run_query(tables_sql)) %}

{% endmacro %}

Here is an example of a macro that uses that list

{% macro do_something() %}

{% set sql_template %}

your sql goes here, use ==TABLE== to denote your table
e.g. select * from stage.==TABLE==


{% endset %}

{%set this_sql_list =[] %}
{% for this_name in generate_tables_set()%}
  {%   set this_sql = this_sql|replace("==TABLE==",this_name)%}
  {%   do this_sql_list.append(this_sql) %}
{% endfor %}
{{this_sql_list|join(" UNION ALL ")}}
{% endmacro %}