I am new in dbt and I would like to ask you a question to understand if this tool fits my case.
Let’s say that I have a table called ‘sales’ that contains 5 columns (‘day’, ‘item’, ‘category’, ‘store’, ‘sales’). For each day-item-category-store combination, I want to calculate the moving average of the last 7 days.
As for the ‘category’ column, I have 8 unique values, and the rows of the table are proportionately split within these 8 categories. Moreover, between the ‘category’ and ‘item’ columns, there is a hierarchical relationship, meaning that all the rows that refer to a specific item are included in the same category.
At the same time, I have 8 threads available on my machine.
Is there any smart way to execute this calculation using all the available threads, one thread for each category, without writing 8 separate models (SQL files)?
Ideally, I would like the results to be split into 8 new tables, one for each category e.g. moving_avg_cat1, moving_avg_cat2, moving_avg_cat3, etc.
Thanks in advance.