dbt_utils.get_column_values and dbt_utils.star macros are not working when database in profiles.yml file is differennt from the database in the src.yml file

[IrinaSel]

Describe the bug

When database in profiles.yml is not the same as a database parameter in src.yml file dbt_utils.get_column_values fails with compilation error and dbt_utils.star returns * instead of list of columns in Redshift. Discovered this problem when we were trying to split source and production schemas and implement salesforce_fornula_utils package for some staging models.

select * from falcon.salesforce.fivetran_formula is working and returns data

Steps to reproduce

In profiles.yml file dbname: prod
In src.yml file

sources:
  - name: salesforce
    schema: salesforce
    database: falcon
    
    tables:
      - name: fivetran_formula

When running dbt compile for the test model:

select
{{ dbt_utils.get_column_values(source('salesforce', 'fivetran_formula'),'field')}}
from {{ source('salesforce', 'fivetran_formula') }}

Expected results

Expect to get select statement with column names taken from field column in salesforce.fivetran_formula table.

Actual results

Runtime Error
Compilation Error in model test (models/staging/salesforce/test.sql)
In get_column_values(): relation "falcon"."salesforce"."fivetran_formula" does not exist and no default value was provided.

> in macro statement (macros/etc/statement.sql)
> called by macro default__get_column_values (macros/sql/get_column_values.sql)
> called by macro get_column_values (macros/sql/get_column_values.sql)
> called by model test (models/staging/salesforce/test.sql)
> called by model test (models/staging/salesforce/test.sql)

System information

packages:

• package: dbt-labs/dbt_utils
  version: 0.8.6
• package: fivetran/github
  version: 0.5.1
• package: fivetran/jira
  version: 0.8.1
• package: elementary-data/elementary
  version: 0.4.7
• package: fivetran/salesforce_formula_utils
  version: [">=0.6.0", "<0.7.0"]

Which database are you using dbt with?

• postgres
• redshift
• bigquery
• snowflake
• other (specify: ____________)

The output of dbt --version:

Core:
  - installed: 1.3.1
  - latest:    1.3.1 - Up to date!

Plugins:
  - redshift: 1.3.0 - Up to date!
  - postgres: 1.3.1 - Up to date!

Additional context

Found this similar issue, but with Snowflake.

Are you interested in contributing the fix?

no

[joellabes]

@IrinaSel those macros are just abstractions over native dbt Core functionality, and I think they're working as expected! I don't actually know what the dbname property on in profiles.yml does (and the documentation is not as clear as I'd like), but I don't expect it to override the database stated for a specific source (especially since in dbt more specific configurations override more general ones).

I think that instead you'd be well-served by doing something like this:

sources:
  - name: salesforce
    schema: salesforce
    database: {{ "prod" if target.name == 'prod' else "falcon" }}
    
    tables:
      - name: fivetran_formula

[IrinaSel]

Hey @joellabes!
Unfortunately, database: {{ "prod" if target.name == 'prod' else "falcon" }} won't help us, since the main goal for having different databases in profiles.yml (or dbt Cloud production environment) and src.yml file is to separate source schemas from dbt ones. We always want to have database: falcon in the source. I still get the same error - In get_column_values(): relation "falcon"."salesforce"."fivetran_formula" does not exist and no default value was provided. even though I'm sure that relationship exists and I have no problem querying it with my user with select * from "falcon"."salesforce"."fivetran_formula"

[joellabes]

I have no problem querying it with my user

Is the user account you used that worked the same as the one that dbt is using? If the dbt user account doesn't have access to the table, it will probably say it doesn't exist as opposed to saying you don't have permission to access it.

What are the full names of the prod and staging tables you're trying to access? "falcon"."salesforce"."fivetran_formula" and "falcon"."SOMETHING"."fivetran_formula"?

[IrinaSel]

Hey!
It's all the same user, so it should have same access.
We were trying to use salesforce_formula_utils.sfdc_formula_view macros which runs dbt_utils.get_column_values macros inside that seems to not work when target database is different from raw database for Redshift.

It's all one table "falcon"."salesforce"."fivetran_formula", but dbt_utils.get_column_values can't select column names taken from field column in "falcon"."salesforce"."fivetran_formula" table.

[joellabes]

The error message you're seeing is caused by this line:

dbt-utils/macros/sql/get_column_values.sql

Line 25 in 965726e

{{ exceptions.raise_compiler_error("In get_column_values(): relation " ~ target_relation ~ " does not exist and no default value was provided.") }}

Which happens when relation_exists is false, i.e. when the result of load_relation(target_relation) is none.

dbt-utils/macros/sql/get_column_values.sql

Line 19 in 965726e

{% set relation_exists = (load_relation(target_relation)) is not none %}

Because of this, I'm back to noting that

those macros are just abstractions over native dbt Core functionality

You could get a more specific reproduction case by trying to use load_relation() when dbname and database match and when they don't. If it doesn't work then you'll need to open an issue against dbt-core as that’s how dbt utils achieves its functionality in the first place.