[Bug] dbt-snowflake doesn't handle lowercase table names from `SHOW OBJECTS`

[dataders]

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

Which packages are affected?

• dbt-adapters
• dbt-tests-adapter
• dbt-athena
• dbt-athena-community
• dbt-bigquery
• dbt-postgres
• dbt-redshift
• dbt-snowflake
• dbt-spark

Current Behavior

via @ameyakhedekar (community slack thread)

if you use a Catalog-Linked Database (CLD) attached to Unity Catalog, you might encounter this error

Compilation Error in model dbt found an approximate match. Please delete ...  

the working hypothesis is that, for time immemorial, Snowflake's SHOW OBJECTS would return case-insensitive tables names which means dbt receives upper-cased identifiers (provided there was now account-level override).

However, tables in the CLD pointing to Unity Catalog return case-sensitive identifiers by default

We suspect this is what is causing the approximate match

Expected Behavior

dbt-snowflake should account for potential case-sensitivity in the results of SHOW OBJECTS as a result of external catalog identifiers

Steps To Reproduce

try to materialize a table to a CLD that points to Unity Catalog

Relevant log output

Environment

- OS:
- Python:
- dbt-adapters:
- <adapter>:

Additional Context

No response

[jtcohen6]

The logic for raising an "approximate match" error is tricky:

dbt-adapters/dbt-adapters/src/dbt/adapters/base/relation.py

Lines 180 to 193 in 8129e46

	exact_match = True
	approximate_match = True
	for k, v in search.items():
	if not self._is_exactish_match(k, v):
	exact_match = False
	if str(self.path.get_lowered_part(k)).strip(self.quote_character) != v.lower().strip(
	self.quote_character
	):
	approximate_match = False
	if approximate_match and not exact_match:
	target = self.create(database=database, schema=schema, identifier=identifier)
	raise ApproximateMatchError(target, self)

From what I remember (and from rereading the code), when dbt-core looks up a relation in the cache:

• If project-level quoting is turned on for this field (database/schema/identifier), then the identifiers must match exactly to be a "cache hit"
• If project-level quoting is turned off (default on Snowflake), and the identifiers aren't the same, but they differ only in the presence of the quote character, then dbt-core will raise this "approximate match" error

So I'm wondering if this error crops up in cases where:

• dbt is writing a model to a Snowflake catalog-linked database, where the linked catalog requires lowercase and quoting (e.g. Unity, Glue)
• instead of turning on project-level quoting, the user puts double quotes directly within the model alias:

{{ config(
    catalog_name = 'my_linked_catalog',
    alias = '"my_model"'
) }}

select ...

(I haven't been able to repro this myself yet, mostly because I'm on the train and have slow wifi)

These caching/metadata queries are definitely something we need to consider, if we want the catalog spec (catalogs.yml) to handle quoting + case-sensitivity behind the scenes — which is desirable IMO!