DBT(Data Build Tools) - drop the default database prefix that gets added to each model on deployment

Question

In DBT, whenever we deploy the models, the database name gets prefixed to each deployed model in the sql definition in database.

I need to configure the dbt project in a way that it doesn't prefix database name to the deployed models.

Answer 1

Do you mean that:

1. You don't want the schema name with a prefix added to it, like just be finance.modelname instead of dbname_finance.modelname, or
2. you want the relation name to be rendered with a two-part name (schema.modelname) instead of the three-part name (database.schema.modelname)?

If #1, I recommend you read the entire custom schema names docs page, specifically the part about Advanced custom schema configuration

If it's #2, this is a change required at the adapter level. Since you've tagged synapse, I'd wager a guess that you're using Synapse SQL Serverless Pools because I have also encountered the fact that you can't use three-part names in Serverless pools. Last week, I actually made dbt-synapse-serverless a separate adapter from dbt-synapse which in fact disables the three-part name.

Answer 2

You can overwrite the built-in ref macro. This macro returns a Relation object, so we can manipulate its output like this:

{% macro ref(model_name) %}

    {% do return(builtins.ref(model_name).include(database=false)) %}

{% endmacro %}

So, from there, all models that use the ref function will return the Relation object without the database specification.

dbt code:
select * from {{ ref('model') }}

compiled code:
select * from schema_name.model

EDIT:

As you requested, here's an example to remove the database name from the sources:

{% macro source(source_name, table_name) %}

    {% do return(builtins.source(source_name, table_name).include(database=false)) %}

{% endmacro %}

I've worked with sources from different databases, so if you ever get to that case, you might want to edit the macro to offer an option to include the database name, for example:

{% macro source(source_name, table_name, include_database = False) %}

    {% do return(builtins.source(source_name, table_name).include(database = include_database)) %}

{% endmacro %}

dbt code:
select * from {{ source('kaggle_aps', 'coaches') }}
select * from {{ source('kaggle_aps', 'coaches', include_database = True) }}

compiled code:
select * from schema_name.object_name
select * from database_name.schema_name.object_name

More details can be found in the official documentation