How can I dynamically create models based on a variable list in dbt? (without creating a .sql for each var)

Help
1

The problem I’m having

I need to dynamically create dbt models based on a variable list instead of manually maintaining a separate .sql file for each model. The list of models can change frequently, so I want dbt to generate models at runtime based on configuration.

The challenge is that dbt seems to require .sql files to exist in models/ before running dbt run, and I’m unsure how to dynamically create models without manually creating them beforehand.

The context of why I’m trying to do this

I have different schemas of salesforce objects and I need a more scalable way to output transformed standard and custom object tables with formulas (based on the sfdc_formula_view macro).
We need to create formula tables for different Salesforce objects, such as:

• opportunity
• account
• custom_object_name_c
• another_custom_object_name_c

Instead of creating and maintaining a static .sql file for each Salesforce object, I want a way to generate these models dynamically based on a variable in dbt_project.yml:

vars:
  salesforce_objects_with_formulas: ["opportunity", "account", "custom_object_name_c", "another_custom_object_name_c"]

Ideally, this should allow:

  1. Auto-generating dbt models for each object in the list. It really would be a select *, but with also the additional SFDC formulas

  2. Keeping the models fully functional so that they can be materialized in dbt run.

  3. Avoiding unnecessary manual .sql file creation every time a new Salesforce object needs formula extraction.

What I’ve already tried

  • I tried looping inside a single .sql model, but dbt only allows one model per file, so only the last iteration ran.
    So theoretically something like this
{% set salesforce_objects = var('salesforce_objects_with_formulas', []) %}

{% for sfdc_object in salesforce_objects %}

    {{ config(
        alias='int_salesforce_' ~ sfdc_object ~ '_with_formulas',
        materialized='table'
    ) }}

    {{ salesforce_formula_utils.sfdc_formula_view(source_table=sfdc_object, materialization='table') }}

{% endfor %}
  • I considered using dbt run-operation to generate models in memory, but it seems dbt requires .sql files in the models/ directory before dbt run will recognize them. I was really hoping in-memory models was possible!
  • I attempted using a macro to define models dynamically, but dbt run did not pick up the generated models.
2

Instead of using Jinja to generate the models in memory, what if you used that Jinja code to generate actual .sql files? Since you define the variables in your dbt_project.yml and would need to update the project anyway with new variables, you could just generate .sql files anytime you had a new entry to your array variable.

This way, you can make sure you have tests and that your generated code is valid

3

Thanks for the suggestion! I see the benefit of generating .sql files so dbt can handle them properly, but in my case, I’m working with many different clients who have their own transformations and their own Salesforce objects. Manually creating or auto-generating separate .sql files for each object feels unscalable in the long term.

4

Today there’s a strong 1 file → 1 object pairing in dbt, but this definitely comes up from time to time and you’re describing a compelling use case!

For now I’ll suggest opening an issue - I thought we already had one but I can’t find it :thinking:

An objectively terrible idea that you really shouldn't do but might work

I have to emphasise that this is my personal idea and not something that dbt Labs recommends or promises will not break in weird ways in the future, but…

If you don’t mind making objects that don’t directly participate in the DAG, you can technically invoke dbt multiple times, and give the single model a different alias each time.

You should mind making objects that don’t participate in the DAG though - I’m not that familiar with the Salesforce formulae so I don’t know how practical that is.

It might not be so messy if you can union them all into one table later, using something like get_relations_by_pattern?

More pragmatically, could you run the Fivetran-provided script to generate the appropriate models on a GitHub action or something, so that the models are automatically persisted into your project?