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

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