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?

score 9 · Answer 1 · answered Jul 24 '20 at 17:37

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:

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 %}

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.

Thank you for the clarification! I was also having this discussion in the dbt Slack channel and got to a similar conclusion after learning that dbt supports only 1 to 1 relation, model to view/table. (1) So the first solution would be, like you mentioned, to create a separate model for each account (using macro to obtain baseline data is an excellent idea!). (2) The second one is to create "all_accounts" model/table, and since I am using the BQ data as a data source inside Google Data Studio, I can create custom query, selecting each individual account from all_accounts. — Angel Dimov, Jul 27 '20 at 06:22

Create Multiple Tables in BigQuery Using dbt for-loop

1 Answers1