# Change Database Name when using --defer and --state

**URL:** <https://discourse.getdbt.com/t/change-database-name-when-using-defer-and-state/7840>\
**Category:** Help\
**Tags:** dbt-cloud, deferral-and-state\
**Created:** [April 14, 2023, 8:58am UTC](https://discourse.getdbt.com/t/change-database-name-when-using-defer-and-state/7840 "2023-04-14T08:58:39Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![rvasconcelossilva](https://avatars.discourse-cdn.com/v4/letter/r/a5b964/32.png) [@rvasconcelossilva](https://discourse.getdbt.com/u/rvasconcelossilva)\
**Post date:** [April 14, 2023, 8:58am UTC](https://discourse.getdbt.com/t/change-database-name-when-using-defer-and-state/7840/1 "2023-04-14T08:58:39Z")

</div>

Hi community,

I think the error I am having below maybe basic but I tried a few things that haven’t worked, so I would like some insights from you.

## The problem I’m having

I have two databases: BUILD, which is the dev environment, and PROD, which is the production. I want to user --defer for developing my new models and also for running the CI.  
Two problems:

1. The `dbt compile` does not work when I use --defer and --state arguments. It is always referring to the dev database and schema.
2. Even when I use `dbt run` with --defer and --state, the model I am referencing it gets the correct schema that is from PROD. However, the database is wrong. Is there a way to fix it?

## The context of why I’m trying to do this

I am using this in the dbt Cloud. As I said, I have BUILD and PROD databases, and to save some time and costs when I am building a new model referencing a model that already exists, I would like to read it from PROD.

## What I’ve already tried

I have already tried a few things in the generate database name macro and also in the ref but with no success.

I really appreciate the help!

---

<div class="post-metadata">

**Author:** ![rvasconcelossilva](https://avatars.discourse-cdn.com/v4/letter/r/a5b964/32.png) [@rvasconcelossilva](https://discourse.getdbt.com/u/rvasconcelossilva)\
**Post date:** [April 21, 2023, 1:01am UTC](https://discourse.getdbt.com/t/change-database-name-when-using-defer-and-state/7840/2 "2023-04-21T01:01:09Z")

</div>

Hi there,

I ended up doing the below change. I’d appreciate any thoughts on this and comments on this. Is there a better way of doing it.

```auto
{% macro ref(model_name) %}
    {% set relation = builtins.ref(model_name).include(database=false) %}
    {% if target.name == 'dev' and target.schema not in relation %}
          {% do return(var('prod_db') ~ '.' ~ relation) %}
    {% else %}
          {% do return( target.database ~ '.' ~ relation) %}
    {% endif %}
{% endmacro %}

```

---

<div class="post-metadata">

**Author:** ![rvasconcelossilva](https://avatars.discourse-cdn.com/v4/letter/r/a5b964/32.png) [@rvasconcelossilva](https://discourse.getdbt.com/u/rvasconcelossilva)\
**Post date:** [April 22, 2023, 1:26am UTC](https://discourse.getdbt.com/t/change-database-name-when-using-defer-and-state/7840/3 "2023-04-22T01:26:28Z")

</div>

I added more details here:

## Goal

The goal is to build a slim Development (dev) and Continuous Integration (CI) environment that runs in different databases.

## The challenge

Currently, [dbt](https://docs.getdbt.com/docs/introduction) allows us to configure a sandbox environment using the --defer and --state arguments when compiling, running or testing models. The article [Defer | dbt Developer Hub](https://docs.getdbt.com/reference/node-selection/defer) explains what Defer is, its benefits and its challenges.

So, let’s dive into a scenario:

Production database has one schema called to transform, one source table source\_a and three models, model\_a, model\_b and model\_c, as demonstrated below.

 ![dbt Slim Dev_CI Environment (2)](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/2X/4/47f986a9083483a53a3af3e23365fb8b3e7ae4ce.jpeg)

Their SQL definition is:

```auto
-- Model A -- model_a.sql
Select * from {{ source('source_a') }}

-- Model B -- model_b.sql
Select * from {{ ref('model_a') }}

-- Model C -- model_c.sql
Select * from {{ ref('model_b') }}

-- Compiled results

-- Model A -- model_a.sql
Select * from prod.transform.source_a

-- Model B -- model_b.sql
Select * from prod.transform.model_a

-- Model C -- model_c.sql
Select * from prod.transform.model_b

```

Let’s suppose that we want to make a change in the model\_b. However, the model\_a is a huge table that takes a long time to rebuild and has computing and storage costs. So, we want to consume the model\_a from the production that makes our changes only on model\_b.

dbt allows us to do that using the --defer and --state. So, I make my changes in model\_b and run the command that looks like this:

dbt run --select model\_b --defer --state ./path\_to\_last\_prod\_manifest\_artifact.

The compiled code of model\_b would be something like this:

```auto
-- Modified and compiled Model B -- model_b.sql
create view prod.dbt_rafael_transform.model_b as
Select * from prod.transform.model_a

```

So, model\_b would be created in the production database but in the dev\_rafael schema. Now, we have something link this:

 ![dbt Slim Dev_CI Environment (3)](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/2X/2/29d5ff02973ad8a1b9d07d2c2166c18690e41019.jpeg)

The best benefit of this is we can run tests and execute our code in a slim way saving costs and improving the velocity of our development. This process is also used for the CI, which means CI will only build the changed models.

However, I see only one issue with this solution, imagine now that we have three or four people working on changes, and it means we’ll have three or four more new schemas in the databases. The schemas that are going to be processed and modified alongside the production schema which is not safe, as everyone only should have read access to the production database.

So the expected behaviour I would like to see when using --defer and --state arguments are as below:

 ![dbt Slim Dev_CI Environment (4)](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/2X/e/ef8a161778d4fe471bd4bfacbfe14c4fbe8ce3d4.jpeg)

So, when I run dbt run --select model\_b --defer --state ./path\_to\_last\_prod\_manifest\_artifact, the compiled code would be something like:

```auto
-- Modified and compiled Model B -- model_b.sql
create view dev.dbt_rafael_transform.model_b as
Select * from prod.transform.model_a

```

So now, the database and schemas are dev keeping the production database clean and secure.

The question is: how can I achieve it?

Do I need to change the ref or generate\_database\_name macros? Should it be handled by --defer and state? Is this a bug in dbt?

---

<div class="post-metadata">

**Author:** ![rvasconcelossilva](https://avatars.discourse-cdn.com/v4/letter/r/a5b964/32.png) [@rvasconcelossilva](https://discourse.getdbt.com/u/rvasconcelossilva)\
**Post date:** [April 26, 2023, 11:35pm UTC](https://discourse.getdbt.com/t/change-database-name-when-using-defer-and-state/7840/4 "2023-04-26T23:35:02Z")

</div>

I generated a new manifest file from my production environment, which fixed the issue. Somehow the production database and the build/dev database were mixed in the manifest, which may be due to the tests. I fixed it, and now the --defer with --state works fine.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/a7a7ca1fe379aaf90952b0e13118a817babcd14f.png) [@system](https://discourse.getdbt.com/u/system)\
**Post date:** [May 3, 2023, 11:35pm UTC](https://discourse.getdbt.com/t/change-database-name-when-using-defer-and-state/7840/5 "2023-05-03T23:35:20Z")

</div>

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.