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:
-
Auto-generating dbt models for each object in the list. It really would be a
select *
, but with also the additional SFDC formulas -
Keeping the models fully functional so that they can be materialized in dbt run.
-
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.