Repository: dbt-labs/dbt-snowflake (python)

Issue:
[Bug] Use `show ... starts with` instead of `show ... like` in `_show_object_metadata`
### Is this a new bug in dbt-snowflake?

- [X] I believe this is a new bug in dbt-snowflake
- [X] I have searched the existing issues, and I could not find an existing issue for this bug

### Current Behavior

`show objects` queries can get slow and take up to 3s

### Expected Behavior

`show objects` queries take optimal 300ms

### Steps To Reproduce

1. Run many individual `show objects` queries in sequence

### Relevant log output

_No response_

### Environment

_No response_

### Additional Context

Solution from Snowflake: https://dbt-labs.slack.com/archives/C04R0T4H8ES/p1719874443806149?thread_ts=1719586466.620999&cid=C04R0T4H8ES

Pull request description:
Fix #1102: Use `show ... starts with` instead of `show ... like` in `_show_object_metadata`
Resolves #1102 

### Problem

We want to use `show...starts with` instead of `show...like` in `_show_object_metadata` for faster query times, as per Snowflake guidance.

### Solution

1. Modify the `snowflake__show_object_metadata` macro
2. Since `starts with` is case-sensitive, convert cases within the relation before sending off to macro; this is done via the new `as_case_sensitive` method.
3. Write a test for `as_case_sensitive` to ensure expected behavior.
4. Profit! These metadata queries should now take 100ms instead of 3s (see screenshot).

![snowflake_query_runtimes](https://github.com/dbt-labs/dbt-snowflake/assets/49380716/1a848e63-746f-4f01-aee0-f3fffa0f1fe0)

### Closed Questions

Let’s say I have 3 tables:
```
my_table
my_table_testing
my_table_backup
```

❓ Am I guaranteed that
`show objects in schema my_schema starts with 'my_table' limit 1` will always return metadata for `my_table` and never for the other two?
Since technically all three satisfy the `starts_with 'my_table'` condition.

**Answer from Snowflake: Yes, in this case, as you have specified limit 1, the output will be `my_table`.**

### Checklist

- [x] I have read [the contributing guide](https://github.com/dbt-labs/dbt-core/blob/main/CONTRIBUTING.md) and understand what's expected of me
- [x] I have run this code in development, and it appears to resolve the stated issue
- [x] This PR includes tests, or tests are not required/relevant for this PR
- [x] This PR has no interface changes (e.g., macros, CLI, logs, JSON artifacts, config files, adapter interface, etc.) or this PR has already received feedback and approval from Product or DX


Patch:
diff --git a/.changes/unreleased/Fixes-20240705-165932.yaml b/.changes/unreleased/Fixes-20240705-165932.yaml
new file mode 100644
index 0000000..ffe902c
--- /dev/null
+++ b/.changes/unreleased/Fixes-20240705-165932.yaml
@@ -0,0 +1,6 @@
+kind: Fixes
+body: Use show ... starts with instead of show ... like in _show_object_metadata
+time: 2024-07-05T16:59:32.087555+01:00
+custom:
+    Author: aranke
+    Issue: "1102"
diff --git a/dbt/adapters/snowflake/impl.py b/dbt/adapters/snowflake/impl.py
index 092510e..6854b19 100644
--- a/dbt/adapters/snowflake/impl.py
+++ b/dbt/adapters/snowflake/impl.py
@@ -156,7 +156,7 @@ class SnowflakeAdapter(SQLAdapter):
     def get_catalog_for_single_relation(
         self, relation: SnowflakeRelation
     ) -> Optional[CatalogTable]:
-        object_metadata = self._show_object_metadata(relation)
+        object_metadata = self._show_object_metadata(relation.as_case_sensitive())
 
         if not object_metadata:
             return None
diff --git a/dbt/adapters/snowflake/relation.py b/dbt/adapters/snowflake/relation.py
index f477265..ace8569 100644
--- a/dbt/adapters/snowflake/relation.py
+++ b/dbt/adapters/snowflake/relation.py
@@ -2,12 +2,12 @@ from dataclasses import dataclass, field
 from typing import FrozenSet, Optional, Type
 
 from dbt.adapters.base.relation import BaseRelation
+from dbt.adapters.contracts.relation import ComponentName, RelationConfig
 from dbt.adapters.relation_configs import (
     RelationConfigBase,
     RelationConfigChangeAction,
     RelationResults,
 )
-from dbt.adapters.contracts.relation import RelationConfig
 from dbt.adapters.utils import classproperty
 from dbt_common.exceptions import DbtRuntimeError
 
@@ -106,3 +106,17 @@ class SnowflakeRelation(BaseRelation):
         if config_change_collection.has_changes:
             return config_change_collection
         return None
+
+    def as_case_sensitive(self) -> "SnowflakeRelation":
+        path_part_map = {}
+
+        for path in ComponentName:
+            if self.include_policy.get_part(path):
+                part = self.path.get_part(path)
+                if part:
+                    if self.quote_policy.get_part(path):
+                        path_part_map[path] = part
+                    else:
+                        path_part_map[path] = part.upper()
+
+        return self.replace_path(**path_part_map)
diff --git a/dbt/include/snowflake/macros/adapters.sql b/dbt/include/snowflake/macros/adapters.sql
index 1777204..4cb4bcf 100644
--- a/dbt/include/snowflake/macros/adapters.sql
+++ b/dbt/include/snowflake/macros/adapters.sql
@@ -49,7 +49,7 @@
 
 {% macro snowflake__show_object_metadata(relation) %}
   {%- set sql -%}
-    show objects like '{{ relation.identifier }}' in {{ relation.include(identifier=False) }} limit 1
+    show objects in {{ relation.include(identifier=False) }} starts with '{{ relation.identifier }}' limit 1
   {%- endset -%}
 
   {%- set result = run_query(sql) -%}


Test patch:
diff --git a/tests/unit/test_relation_as_case_sensitive.py b/tests/unit/test_relation_as_case_sensitive.py
new file mode 100644
index 0000000..f362d66
--- /dev/null
+++ b/tests/unit/test_relation_as_case_sensitive.py
@@ -0,0 +1,19 @@
+from dbt.adapters.snowflake.relation import SnowflakeRelation
+from dbt.adapters.snowflake.relation_configs import SnowflakeQuotePolicy
+
+
+def test_relation_as_case_sensitive_quoting_true():
+    relation = SnowflakeRelation.create(
+        database="My_Db",
+        schema="My_ScHeMa",
+        identifier="My_TaBlE",
+        quote_policy=SnowflakeQuotePolicy(database=False, schema=True, identifier=False),
+    )
+
+    case_sensitive_relation = relation.as_case_sensitive()
+    case_sensitive_relation.render_limited()
+
+    assert case_sensitive_relation.database == "MY_DB"
+    assert case_sensitive_relation.schema == "My_ScHeMa"
+    assert case_sensitive_relation.identifier == "MY_TABLE"
+    assert case_sensitive_relation.render() == 'MY_DB."My_ScHeMa".MY_TABLE'