Find INTERSECT of two tables using DBT

Question

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

Answer 1

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 %}
   ...