Dynamic schema/model generation based on a metadata table in snowflake

Help
, , ,
1

I am a beginner to dbt and am looking to explore it to build an incremental load from “SourceDB.source” to “DestinationDB.schema1, DestinationDB.schema2, DestinationDB.schema3” using company_id. The “source” schema is incremental data from the source. As a first step, I have 25 model files to generate the model using a unique id (company_id - hardcoded using the “where” clause and replicated the source data in DestinationDB.schema1).

The problem I’m having

I need a generalized solution to replicate the same with other company_id’s.

The context of why I’m trying to do this

Create data isolation for each company by creating individual schema using company id.

What I’ve already tried

I tried setting schema dynamically using for loop. I am getting two “select” statement syntax errors.

Some example code or error messages

{%- call statement('companies_mapping_table', fetch_result=True) -%}
  select
    company_id,
    unique_key,
    target_schema
  from companies_mapping_table
{%- endcall -%}

{%- set result = load_result('companies_mapping_table') -%}
{%- set mappings = result['data'] -%}

{% for each in mappings %}
  {{ print("Running Macro: " ~ each[0] ~ ", " ~ each[1] ~ ", " ~ each[2]) }}

  -- Trying to create schema dynamically here
  {{
    config(
      database='destination_dev',
      schema='{{each[2]}}',
      materialized='incremental',
      unique_key='{{each[1]}}',
      incremental_strategy='merge'
    )
  }}

  select
    *
  from source_dev.raw_data.all_companies_data 
  where company_id = {{each[0]}}
  
{% endfor %}