Need to run the same dbt package(Quickbooks) for multiple clients
dbt quickbook: GitHub - fivetran/dbt_quickbooks at v0.10.0
I am using fivetran to get information on QuickBooks from my clients.
I am using dbt cloud with dbt quickbook package to build the models. The issue is I have multiple clients, each client source tables are saved in a separate BQ dataset. Each client has one dataset.
I connected fivetran to my github, to automate the dbt run, but I am not able to tell dbt where the source tables for that client are, for it to run the output models!
I tried to create an env variable, and use it in the project.yml, but you can’t use an env variable in project.yml!
Then I used the env variable in the source.yml of the QuickBooks package model, this worked when running it via the dbt cloud. But when I check-in my changes, dbt doesn’t check in the quickbook dbt package with my changes, as it is a third party package and it is inside the package.yml, github ignores those packages,a cording to this link: “Run dbt deps to install the package(s). Packages get installed in the dbt_packages directory – by default this directory is ignored by git, to avoid duplicating the source code for the package.” (Packages | dbt Developer Hub)
There is an option in the QuickBooks dbt , to add to project.yml a flag to run on multiple clients, but that would mean that I have to manually add each new client to the file, which I want to avoid. Also, it would end up having all clients in one single table, which is not optimal!
Some example code or error messages
# dbt_project.yml
...
config-version: 2
vars:
quickbooks_union_schemas: ['quickbooks_usa','quickbooks_canada'] # use this if the data is in different schemas/datasets of the same database/project
quickbooks_union_databases: ['quickbooks_usa','quickbooks_canada'] # use this if the data is in different databases/projects but uses the same schema name
#source file what I wanted to do:
version: 2
sources:
- name: quickbooks
schema: "{{ env_var('DBT_SCHEMA') }}"
database: "{{ var('quickbooks_database', target.database) }}"
loader: fivetran
loaded_at_field: _fivetran_synced