How can I debug the dbt error that says "Database error while listing schemas in database XYZ"?

Question

Thank you for any help/ideas on this.

I am using Snowflake and dbt CLI, with Fivetran as the orchestrator

I added a profile called dev to my profiles.yml and put in all the connection details

profiles.yml now looks like this

default:
  target: prod
  outputs:
    prod:
      type: snowflake
      account: XYZ
      user: XYZ
      role: XYZ
      authenticator: XYZ
      database: ANALYTICS
      warehouse: XYZ
      schema: REPORTING
    dev:
      type: snowflake
      account: XYZ
      user: XYZ
      role: XYZ
      authenticator: XYZ
      database: ANALYTICS
      warehouse: XYZ
      schema: DEV_THEDRE

When I run the command dbt run target prod -m model_name there is no issue (my models get materialized into the schema called Reporting.

However, When I run the command dbt run target dev -m model_name , I get an error message: DEncountered an error: Runtime Error Database error while listing schemas in database "DEV" Database Error 002043 (02000): SQL compilation error: Object does not exist, or operation cannot be performed.

However, there is no database called dev in my snowflake instance. I don't understand why its trying to find this database called "DEV".

I changed the name of the profile to sand to see if the word dev would be replaced for the word sand in the error message, but the error message still says "DEV"

The dbt logs file shows the following:

2021-10-17 20:37:54.100339 (ThreadPoolExecutor-0_0): Acquiring new snowflake connection "list_DEV".
2021-10-17 20:37:54.113467 (ThreadPoolExecutor-0_0): Using snowflake connection "list_DEV".
2021-10-17 20:37:54.113577 (ThreadPoolExecutor-0_0): On list_DEV: /* {"app": "dbt", "dbt_version": "0.20.2", "profile_name": "default", "target_name": "dev", "connection_name": "list_DEV"} */

    show terse schemas in database DEV
    limit 10000
2021-10-17 20:37:54.113669 (ThreadPoolExecutor-0_0): Opening a new connection, currently in state init
2021-10-17 20:37:54.130004 (ThreadPoolExecutor-0_0): Loading KWallet
2021-10-17 20:37:54.131793 (ThreadPoolExecutor-0_0): Loading SecretService
2021-10-17 20:37:54.133543 (ThreadPoolExecutor-0_0): Loading Windows
2021-10-17 20:37:54.135003 (ThreadPoolExecutor-0_0): Loading chainer
2021-10-17 20:37:54.135968 (ThreadPoolExecutor-0_0): Loading macOS
2021-10-17 20:38:11.616136 (ThreadPoolExecutor-0_0): Snowflake query id: 019facf6-0601-3ffb-0038-1583017946a2
2021-10-17 20:38:11.617116 (ThreadPoolExecutor-0_0): Snowflake error: 002043 (02000): SQL compilation error:
Object does not exist, or operation cannot be performed.
2021-10-17 20:38:11.617625 (ThreadPoolExecutor-0_0): Error running SQL: macro list_schemas
2021-10-17 20:38:11.617828 (ThreadPoolExecutor-0_0): Rolling back transaction.
2021-10-17 20:38:11.618305 (ThreadPoolExecutor-0_0): On list_DEV: Close
2021-10-17 20:38:11.918642 (MainThread): Connection 'master' was properly closed.
2021-10-17 20:38:11.918799 (MainThread): Connection 'list_DEV' was properly closed.
2021-10-17 20:38:11.919141 (MainThread): Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'end', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x104d84400>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x104c9db50>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x104ca10d0>]}
2021-10-17 20:38:11.919411 (MainThread): Flushing usage events
2021-10-17 20:38:12.176058 (MainThread): Encountered an error:
2021-10-17 20:38:12.176328 (MainThread): Runtime Error
  Database error while listing schemas in database "DEV"
  Database Error
    002043 (02000): SQL compilation error:
    Object does not exist, or operation cannot be performed.```

Thank you once again!

Answer 1

I found the issue There was a macro that said If target = Prod then database = defaultname ELSE database = dev I changed it and its working now