Predictable "static" dbt model referencing

[Kayrnt]

required knowledge about dbt

• dbt uses profiles to manage connections with the engines. It can be stored at user home level (~/.dbt/profiles.yml) or at project level (my_project/profiles.yml).
• each profile defines a default schema name
• dbt models are referenced through their filename (select foo from {{ ref('bar') }}).
• the default schema used by dbt to write a model is <target_schema>_<custom_schema> (target == profile one / custom is the one that you can specify at model or project configuration level)
• dbt allows to override the default schema macro to change that behavior in jinja code

Comparison with SQLMesh

• SQLMesh defines Gateway & Environment to connect to engines
• Those concepts don't define a default schema
• The schema is defined at model level but it is also overridden when it's "applied" to isolate environments & physical/virtual level

Context

SQLMesh offers a dbt integration that allow to run directly the project but also to use them as part of the multi project integration.
However to map the SQLMesh to dbt approach, SQLMesh resolves the schema used by dbt to form the FQN that will be used for both model referencing (from model A to model B) and in tests.

The problem

As in SQLMesh the FQN is static, it's working as intended to define test but for dbt ... it's a problem.
It's also painful use with any feature that rely on selectors as switching profile changes the FQN.

Example

In following PR, I'm using a multi project integration and had to set the schema to dbt_cou but it's not referenced anywhere in my project because it's overridden through my profiles.yml

Some ideas to fix it:

• Use the project name as part of the FQN for dbt models so that dbt_project_name.model_name is the FQN that will be referenced in SQLMesh. It's predictable and stable but "inconsistent" with SQLMesh approach that expectsmy_schema.model_name.
• Provide a variable in SQLMesh config from dbt integration to define the schema used for the FQN in SQLMesh: it could be used only for the FQN or it could also override the schema being used for all models?
• Ignore dbt usual schema name resolution (both macros and profiles.yml one) and use only the schema defined at dbt model level + configuration level one

[tobymao]

@Kayrnt unit tests should only run with one profile, i don't really see a reason for needing them to run for multiple profiles.

[Kayrnt]

I understand that this behavior is not very intuitive, but there are specific reasons for making unit tests work with any profile:

1. Profiles as a Gateway-Environment Mix
   A profile in this context acts as a mix between a SQLMesh Gateway and an environment. SQLMesh supports running tests against different gateways. As documented in SQLMesh, profiles are primarily used in the target.
   However, this approach can be limiting for many users. By default, dbt's best practices suggest creating one profile per environment. Current dbt support assumes a "single production profile" that emulates the "production gateway." In SQLMesh, you would use its environment system to handle isolation instead.

2. Advanced Use Cases with BigQuery
   Consider an advanced setup where a user employs BigQuery's standard edition for 90% of their workload but uses BigQuery ML (available only in the enterprise edition) for specific cases. In dbt, such a setup might require two CI profiles targeting different GCP projects. Each profile would execute unit tests for its respective models, ensuring functionality within the corresponding "gateway" (leveraging the execution_project setting supported in both dbt and SQLMesh).

Although it’s possible to "cheat the system" by configuring a single profile through a specialized profiles.yml in the dbt project, this approach is cumbersome:

• All parameters would need to rely on the env_var macro.
• Users would need to set export DBT_PROFILES_DIR=path/to/dbt_project_directory and configure every parameter as an environment variable.

This workaround is complex and stretches the configuration system in ways that make it less user-friendly, especially since SQLMesh doesn't support a --dbt-profile or --dbt-profile-dir flag to directly select the desired profile.

Recommendation:

While workarounds exist, they require advanced expertise and could hinder dbt-to-SQLMesh migrations, reducing overall adoption.

If introducing new options or variables to simplify dbt and SQLMesh integration is not on the table, I strongly recommend documenting this approach thoroughly. Many users are likely to encounter challenges and could benefit greatly from clear guidance.

[tobymao]

Thanks for your detailed response. After consideration, I think that we won't be introducing any workarounds for handling multiple profiles in unit tests. We don't think it's a very common path for folks to run into. If you're up for it, we'd be happy to accept some documentation about how to achieve what you're looking for as an advanced use case.

[github-christophe-oudar]

Ok, I'll keep you posted with my attempts and I'll try to document the way to do it if it works as expected.