Change Column Name Dynamically Using Mapping Table dbt

Question

I would like to change column names in dbt model using mapping table:

So instead this one:

to have table with NewColumnName values.

How I can do this. Thanks in advance!

Answer 1

You could:

1. create two sets of variables (one for the column names in the "old" model, and one for the values of the new column names in the mapping table);
2. then create a for loop to iterate over the old column names in the old table, and the values of the newcolumnname column in the mapping table; and
3. finally apply the values of the newcolumnname as aliases in your model.

So, one possible approach would be:

{% set old_column_names = dbt_utils.get_filtered_columns_in_relation(from=ref('old_names')) %}
{% set new_column_names = dbt_utils.get_column_values(ref('mapping_table'), 'newcolumnname') %}

select
  {% for old_column_name in old_column_names %}
    {{ old_column_name }} as {{ new_column_names[loop.index0] }}
      {% if not loop.last -%} , {% endif -%}
  {% endfor %}

from {{ ref('old_names') }}

This is assuming that the order and number of the columns from the base model will always be the same as the one in the mapping table.