I want to find common columns between 2 tables using DBT. But I am unable to find the right way to do it. can anyone help with that? Thanks in advance
Asked
Active
Viewed 2,135 times
1
-
Can you specify the data warehouse you are using? The answer will be dependent on that unless you are asking, "How can I find the intersection of the columns in two DBT models?" – sgdata Mar 26 '21 at 12:52
-
@sgoley, thanks for putting your effort into my question. Yes, I am looking for finding an intersection between 2 DBT models. – amitesh shukla Mar 27 '21 at 05:32
1 Answers
4
You can create a macro that uses adapter.get_columns_in_relation()
and jinja.
{% macro compare_columns(table1, table2) %}
{% set columns1 = adapter.get_columns_in_relation(ref(table1)) %}
{% set columns2 = adapter.get_columns_in_relation(ref(table2)) %}
{% for column in columns1 %}
{% if column in columns2 %}
{{ log("Column: " ~ column.name, info=true) }}
{% endif %}
{% endfor %}
{% endmacro %}
get_columns_in_relation
returns a list of Column objects, which contain the column name and type, so the above code will confirm that the columns and their types are the same. If you just want to confirm the column names match then you'll need to add logic to compare the name attribute at the beginning of the if statement.
...
{% set columns2_cleaned = [] %}
{% for column in columns2 %}
{{ columns2_cleaned.append(column.name) }}
{% endfor %}
{% for column in columns1 %}
{% if column.name in columns2_cleaned %}
...

s_a_morton
- 56
- 5