Create Multiple Tables in BigQuery Using dbt for-loop

Question

I am trying to create individual tables inside a single dataset in BigQuery using a for-loop in dbt, going through a list of accounts, with no success so far. A little bit of context - I am using Stitch to fetch data from Facebook Ads and to push it to our BigQuery warehouse. Then, based on the model below, create new separate table for each account with aggregated/modelled data.

The declaration of the variables looks like:

-- table that contains list of accounts
{% set account_data = ref('bq_acct_list') %} 
{% set accounts = get_column_values(table=account_data, column='bq_name_suffix') %}

And the query that the tables have to created based on is:

SELECT 
        DATE_TRUNC(DATE(date_start), DAY) date,
        account_id,
        account_name,
        ROUND(SUM(spend), 2) ad_spend
FROM `{{ target.project }}.{{account}}.ads_insights`
GROUP BY 1, 2, 3

What is missing (I think) is the wrapper of the query + the for-loop itself. Can anyone help me fill in the blanks?

Answer 1

dbt operates under a paradigm of one model (i.e. a .sql file in your models/ directory) is represented by one object (table/view) in your data warehouse — at the moment there's no way around that.

If you need to maintain separate tables per account I'd consider:

1. Wrapping up the logic into a macro:

-- macros/account_transform.sql
{% macro account_transform(account) %}
SELECT 
        DATE_TRUNC(DATE(date_start), DAY) date,
        account_id,
        account_name,
        ROUND(SUM(spend), 2) ad_spend
FROM `{{ target.project }}.{{ account }}.ads_insights`
GROUP BY 1, 2, 3
{% endmacro %}

2. Create a separate model for each account, and call the macro in each model:

-- models/my_first_account.sql
{{ account_transform('my_first_account') }}

-- models/my_second_account.sql
{{ account_transform('my_second_account') }}

Depending on your exact use-case, you might also consider creating a master table for all accounts, by unioning them together. That way, you only have to create one model. Check out the article on "Unioning together identically-structured sources" for some techniques for this approach.