[Bug] adapter.get_relations not working as expected

[fivetran-joemarkiewicz]

Is this a new bug in dbt-redshift?

• I believe this is a new bug in dbt-redshift
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

As seen in this dbt_ad_reporting issue discussion it seems that the adapter.get_relations component is not working as expected when trying to reference a relation in a database that is separate from the target database where the models are being written.

I would expect that running the below code would result in the properly formatted relation.value. However, we always end up seeing None as the value. I was able to recreate this error on my end and the OP of the Ad Reporting issue also was able to share the below code resulted in an unexpected empty relation.

{% set relation = namespace(value="") %}
{%- set relation.value=adapter.get_relation(
    database=source('fivetran_reddit_ads', 'campaign_report').database,
    schema=source('fivetran_reddit_ads', 'campaign_report').schema,
    identifier='campaign_report'
) -%}

with source as (

    select * from {{ relation.value }}

),

renamed as (

    select * from source

)

select * from renamed

Please note the adapter.get_relation is not being used directly in this capacity, but I have simplified it here to narrow in the issue. You can see the true origin of this code is found here and leveraged across a number of the Fivetran dbt packages.

Expected Behavior

I would expect the above query to return the proper database.schema.table relation result from the adapter.get_relation query.

Steps To Reproduce

1. Configure the above mentioned query to reference a valid source that is in a different Redshift database on the same cluster.
2. Run the model
3. See some sort of error indicating that the query was not successful
4. See in the compiled code that the relation.value compiled to None

Relevant log output

relation "none" does not exist

Environment

- OS: Mac OS
- Python: Python 3.10.11
- dbt-core: 1.8.1
- dbt-redshift: 1.8.0

The above are my specific to my environment. The OP may differ.

Additional Context

No response

[amychen1776]

@fivetran-joemarkiewicz has this worked in the past? This sounds like the feature request for dbt-redshift to support cross-db querying which we don't support today well.

[fivetran-joemarkiewicz]

@amychen1776 thanks for the response! In all the Fivetran dbt packages we offer the flexibility to define the schema and database via variables which doesn't have any restriction on cross-db querying. I've experienced the cross-db functionality working as expected in other adapters (such as BigQuery and Snowflake), but I don't know if we've ever encountered someone attempting this in Redshift until now. I can't be sure if this has worked in the past since we didn't attempt this ourselves, and this is the first time we've seen a bug report regarding this limitation.

You mention this cross-db isn't supported in Redshift today, I wasn't fully aware of this and I noticed there is an open Issue #742 which mentions the following is not supported (which is the scenario resulting in the creation of this ticket).

a source table is defined in a database different than what's given in profile

Given this and as you mentioned, it seems like this is not a supported feature of dbt-redshift and this should fold into the existing feature request. If that's the case, I can inform the OP within the Ad Reporting Issue and be sure to call this out in our documentation for anyone attempting to cross-database query when using Redshift and a Fivetran dbt package.

[amychen1776]

Yes - it does work seamlessly on the other adapters but for Redshift, unfortunately we need to update how we manage the metadata which is a bigger project. Since you have tagged the request, we can track it to this one as well (unfortunately I don't have a timeline to share for this feature just yet). I think it's the right call to update your documentation on this. For now, I'll close this issue up.