'How do I loop through alll columns using Jinja in DBT?

I want to iterate over all the columns using dbt.

sqldbt


Solution 1:[1]

I think the star macro from the dbt-utils package + some for-loop logic might help you here? This depends on the exact use case and warehouse you're using (as pointed out in the comments).

The star macro generates a list of columns in the table provided.

So a possible approach would be something along the lines of:

{% for col in [{{ dbt_utils.star(ref('my_model')) }}] %}
...operation...
{% endfor %}

Solution 2:[2]

You can use the built-in adapter wrapper and adapter.get_columns_in_relation:

{% for col in adapter.get_columns_in_relation(ref('<<your model>>')) -%}
    ... {{ col.column }} ...
{% endfor %}

Solution 3:[3]

If you have the model node, and you have columns defined as model properties, this will work:

{% for col in model.columns.values() %}
  ... {{ col.name }}  ... {{ col.data_type }} ... 
{% endfor %}

You can get the model node from the graph:

{% set model = graph.nodes.values()
        | selectattr("resource_type", "equalto", "model")
        | selectattr("name", "equalto", model_name)
        | first %}

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 David Clarance
Solution 2 zoltanctoth
Solution 3 Hraefn

Related Questions