[Bug] Impossible to use adapter.get_relation to find quoted case-sensitive relations in Snowflake

[TerjeRusska]

Is this a new bug?

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

Current Behavior

source configuration

sources:
  - name: communication
    database: COMMUNICATION__PROD
    schema: public
    quoting:
      database: true
      schema: true
      identifier: true
    tables:
      - name: email_archive

macro from stg__ file, nodes originate from graph.sources.items()

{%- set source_relation = adapter.get_relation(
database=adapter.quote(node.database) if node.quoting.database else node.database,
schema=adapter.quote(node.schema) if node.quoting.schema else node.schema,
identifier=adapter.quote(node.name) if node.quoting.identifier else node.name) -%}

Runtime Error
  Compilation Error in model stg__communication__email (models/staging/communication/stg__communication__email.sql)
    When searching for a relation, dbt found an approximate match. Instead of guessing 
    which relation to use, dbt will move on. Please delete "COMMUNICATION__PROD"."public"."email_archive", or rename it to be less ambiguous.
    Searched for: "COMMUNICATION__PROD"."PUBLIC"."EMAIL_ARCHIVE"
    Found: "COMMUNICATION__PROD"."public"."email_archive"

I'm not searching for "COMMUNICATION__PROD"."PUBLIC"."EMAIL_ARCHIVE", the variables given to the macro matches with "COMMUNICATION__PROD"."public"."email_archive"

Somewhere along the way the macro takes quoting configuration from project level ??? and causes the mismatch

Expected Behavior

Found relation for "COMMUNICATION__PROD"."public"."email_archive"

Steps To Reproduce

1. Project level quoting is default for Snowflake (all false)
2. Snowflake QUOTED_IDENTIFIERS_IGNORE_CASE is FALSE (default behavior)
3. Source defined with quoting enabled and includes lowercase identifiers
4. Call adapter.get_relation macro in a model with quoted variables that match the source config

Relevant log output

No response

Environment

- OS: Ubuntu 22.04.5 LTS
- Python: Python 3.10.12
- dbt-adapters:
Core:
  - installed: 1.8.8
Plugins:
  - snowflake: 1.8.4 - Up to date!

Additional Context

No response

[amychen1776]

This is specific to dbt-snowflake so will be transferring this issue. This seems like an issue with Snowflake quoting