How Can I Run a Fivetran Data Model Against Multiple Sources and Output the Results in Separate Schemas?

Question

How can I run a Fivetran data model against multiple sources and output the results in separate schemas?

Environment

Fivetran data models

Answer

To work with multiple data sources and store the results in separate schemas, do the following:

In your root dbt_project.yml file, add the following dbt-jinja code to the models section. Replace <transform_model> and <source_model> with the applicable model names, <schema_1> and <schema_2> with the desired schema names, and <schema_variable> with a variable that will indicate the source. You will apply the variable in your dbt run command in step 2.
```
models:
<transform_model>:
+schema: "{% if var('<schema_variable>') == '<schema_1>' %} <schema_2> {% else %} <schema_2> {% endif %}"
<source_model>:
+schema: "{% if var('<source_schema>') == '<schema_1>' %} <schema_1> {% else %} <schema_2> {% endif %}"
```
Run the following dbt command, using the --vars argument to set the value of the schema variable. Replace <schema_variable>, <schema_1>, and <schema_2> with the applicable values you set in step 1.
```
dbt run --vars '{<schema_variable>: "<schema_1>"}'
dbt run --vars '{<schema_variable>: "<schema_2>"}'
```

Example

If you had two sources, Shopify US and Shopify UK, you would do the following:

Add the following custom dbt-jinja to the models section of your root dbt_project.yml file.

models:
shopify:
+schema: "{% if var('shopify_schema') == 'shopify_uk' %} shopify_uk {% else %} shopify_us {% endif %}"
shopify_source:
+schema: "{% if var('shopify_schema') == 'shopify_uk' %} shopify_uk {% else %} shopify_us {% endif %}"

Run the following dbt command:

dbt run --vars '{shopify_schema: "shopify_us"}'
dbt run --vars '{shopify_schema: "shopify_uk"}'

In the above example, the custom dbt-jinja code checks the value of the shopify_schema variable. If the variable is set to shopify_uk, the output schema will be shopify_uk. If the variable is set to shopify_us, the output schema will be shopify_us. The --vars argument in the dbt command sets the value of the shopify_schema variable.