Dynamic calculations in DBT models (get_column_values)
Have you ever needed to apply filters or calculations dynamically in DBT?
In many cases, we need to perform a different calculation or filter for each row in a table dynamically. This can be achieved, for example, by having another model that indicates which filter to apply in each case.
In this article, we will see how we can apply these filters or dynamic calculations using dbt + Snowflake.
To do this, we will use the get_column_values macro inside the dbt-utils package.
The code of this macro can be found at “https://github.com/dbt-labs/dbt-utils/blob/main/macros/sql/get_column_values.sql"
Next, we show how to do it:
- We have the “SALES” model in which the country is stored, the net sales for the said country, and 3 more values that will be applied depending on one country or another as indicated in our second model (“DYNAMIC_VALUES”) in which the calculations mentioned above.
Sales model
Dynamic_values model
As we see in the “DYNAMIC_VALUES” model:
- when the country is ‘ES’ the calculation to be carried out will be “net_value + add_value1”
- when the country is ‘FR’ the calculation to be carried out will be “net_value + add_value1 + add_value2”
- when the country is ‘IT’ the calculation to be performed will be “net_value + add_value1 + add_value2 + add_value3”
- when the country is ‘DE’ the calculation to be performed will be “net_value”
- when the country is ‘PT’ the calculation to be performed will be “net_value — add_value1 — add_value2 — add_value3”
We already know that these calculations could be done directly through a CASE, but what would happen if they change very often, and instead of having 5 countries we had 100? should we be changing our code every time this calculation changes on top of having a CASE of 100+ distinct cases?
This is where the get_column_values macro comes into play. Next, we see the code of our dbt model in which the said macro is used to apply this calculation dynamically in each execution. With this, if the way of calculating the “final_calculation” field in the “DYNAMIC_VALUES” model changed every day, we wouldn’t have to make any changes to our “TEST_DYNAMIC_COLUMN” model.
As we can see, we use the macro to obtain all the countries that we have in the “DYNAMIC_VALUES” model to later apply the corresponding calculation to each country stored in the “calculation” column.
The compiled code of this model would be the following:
And the result of our model would be the following:
This is a fairly simple example to understand the functionality, but really this can be used to perform complex calculations or filters, and that can be changed very often without needing to change our model every time.
I hope this helps you!
Thank you!
