[CT-685] [Bug] Redshift - second target database requires ra3_node: true and ignores database name

[jan-benisek]

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

Context - we are switching from one Redshift DB to another (both are dc2). Therefore, I have created second target in profiles.yml and we are slowly changing the sources.

company:
  target: prod
  outputs:
    prod:
      type: redshift
      host: A
      user: <user>
      pass: <pass>
      port: 5439
      dbname: <db name>
      schema: prod
      threads: 8

    lake:
      type: redshift
      host: B
      user: <user>
      pass: <pass>
      port: 5439
      dbname: <db name>
      schema: prod
      threads: 8
      ra3_node: true

The switch in the model.sql looks as follows:

{% if target.name == 'lake' %}

{{ config(..., database='prod',)}}
select * from {{ source('foo', 'bar') }}

{% endif %}

{% if target.name == 'prod' %}

{{ config(...)}}
select * from {{ ref('foobar') }}

{% endif %}

I run dbt run --select model --target lake

First issue - database name is ignored unless explicitly specified in config

If not specified, it tries to create the model as

create  table
    "prod"."events"."table_name__dbt_tmp"
  as (

and shows

cross-database reference to database "test" is not supported.

This works with database='prod', but this should not be necessary IMO.

Second issue - not specifying ra3_node: true in profiles.yml

When I do not specify this, i get following error

Cross-db references allowed only in RA3.* node. (test vs prod)

Which does not make sense, because I do not have RA3 nodes in the first place, so the parameter should not work. Secondly, I am not making any cross-db queries and do not intend to. They just live in the same project temporarily.
I suppose dbt does not like that. I found similar issues and discussions where ppl actually want to do cross-db queries.

Expected Behavior

I would like to be able to have two Redshift databases in the same project work just fine, without the additional hacks mentioned above.
I understand that cross-referencing DBs is an issue, but I do not intend to do so.

Steps To Reproduce

Described above.

Relevant log output

No response

Environment

- OS:Mac
- Python: 3.8.2
- dbt: 1.0.1

What database are you using dbt with?

redshift

Additional Context

No response

[gshank]

Normally people specify the database for a particular target in the target definition. It's not clear to me why you're not doing that. Is there some reason that doesn't work?

[jan-benisek]

@gshank Do you mean in the profiles? I specified it, but omitted here for simplicity. Let me put it back.
That is the strange thing, it does not work despite the db being specified.

[gshank]

I see. So you're switching back and forth between one target and another. The 'ref' and 'source' calls are buried in "if" statements, so I'm wondering if there's an issue with creating the connections between the model node and the other model (the ref) or the source. In some cases adding a SQL comment at the top will force the ref connection to be made: -- depends_on: {{ ref('upstream_parent_model') }}

You might look at the model in the manifest.json that's written out and see if you can find the appropriate depends_on.nodes and sources, or invalid references to the wrong database.