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

Question

I want to iterate over all the columns using dbt.

Clingfish

Please expand with an example and some workable model / column name examples as well as the type of warehouse you are using (Snowflake, BQ, etc.) – Bonis

Answer 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 adapter.get_columns_in_relation(ref('my_model')) }}] %}
...operation...
{% endfor %}

Bernettabernette

Thanks! I stumbled on the dbt_utils.star but could not get it to ouput a list of columns. But I think adding the list brackets should do the trick – Clingfish

@NripeshPradhan great, did you manage to figure it out? – Bernettabernette

your suggestion worked for looping across all columns. – Clingfish

Thanks @NripeshPradhan, could you mark this as an accepted answer! Would be super useful! – Bernettabernette

there is a syntax error in the first line if I am not mistaken - it shall read {% for col in [ dbt_utils.star(ref('my_model')) ] %} without curly brackets around the macro – Jimmiejimmy

Unfortunately that macro does not work anymore. You need to use the following for dbt-core==1.7.13 and dbt-utils==1.1.1: {% for col in adapter.get_columns_in_relation(ref("session_tables")) %}. Mind the missing "{{" as Greg Hoar quoted. – Sextet

Thanks @GregK, I've accepted your edit. – Bernettabernette

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

Borries

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

Palace