How to Use dbt union_relations Macro to Redefine Columns with Arithmetic Operations?

I'm using the dbt macro union_relations to combine several views into one. In this process, I want to redefine an existing column while creating this new view. Specifically, I want to perform an arithmetic operation on the 'COSTS' column. More precisely, I want to add the VAT to the 'COSTS' column to obtain a new column that represents the costs including the VAT.

Here's a temporary code snippet of what I have so far:

{{ dbt_utils.union_relations(
    relations=[
      ref('ADS.ALL_ADVERTISEMENTS_V'),
      ref('ADS.ALL_OOH_V'),
    ],
    exclude=['_DBT_SOURCE_RELATION']
) }}

This code successfully performs the UNION operation on the selected relations. However, I need guidance on how to redefine the 'COSTS' column as follows within the same operation:

• Rename 'costs' to 'costs_no_vat'.
• Calculate 'costs_vat' as 'costs * 1.077'.

How can I achieve this transformation using the dbt framework?

The dbt_utils.union_relations macro simply returns a SQL statement. With that in mind you can place the macro into a CTE and query it like any SQL statement. Additionally, we can use the new Snowflake select * rename feature to rename the column.

with unioned as (
  {{ dbt_utils.union_relations(
      relations=[
        ref('ADS.ALL_ADVERTISEMENTS_V'),
        ref('ADS.ALL_OOH_V'),
      ],
      exclude=['_DBT_SOURCE_RELATION']
  ) }}
)

select * rename (costs as costs_no_vat)
  , costs * 1.077 as costs_vat
from unioned