How to handle convergent models?

[philippefutureboy]

Problem

We would like to have assistance with regard on how to structure our dbt models given our data model structure (convergent proprietary models into a standardized data model).

Given the following data model:

• Multitenant model where each tenant has a separate BigQuery dataset in which their respective data is loaded, then transformed.
• We integrate multiple competing providers that cover the same domain entities with different table structures; for each category of competing providers, a tenant will have data from only one of the competing providers.

For example, here's a simplified/genralized representation of a subset of the analytics DAG for two tenants respectively using different competing providers:

Tenant A (Provider P1):

P1.schedule --> std_schedule
P1.order --> std_order
P1.order_item --> std_order

Tenant B (Provider P2):

P2.t_schtemp --> std_schedule
P2.t_billinvoice --> std_order
P2.t_billitem --> std_order

Then how can we structure our dbt project to dynamically switch between providers?

Proposed solution

Our current (incomplete) understanding of how to support multiple concurrent implementation of a given model (e.g. std_schedule, std_order) is the following:

1. One dbt execution per tenant (given they have different impl & schema)
2. Tag sources (P1.*, P2.*) with the provider identifier (e.g. acme_provider, corp_provider)
3. Have a single model file for a standardized (common) model (e.g. std_schedule, std_order)
4. Split each provider custom implementations into separate SQL files to be included using jinja's include
5. Implement the standard models as switches between implementation using the passed tags

Would that work?

• Does dbt support jinja's include? / Should the includes be marked as excluded in the dbt project?
• Would dbt be able to correctly write a manifest when tags are passed?
• Would dbt be able to correctly interpret the jinja2 SQL given the appropriate tags?
• How would this work if we wanted to integrate with dbt Cloud?

Alternatively, if you have a better model to propose, we are all ears!

Thanks so much for your answer 🙏 😄

[philippefutureboy]

Closing in favor of #8863