[X] I have searched the existing issues, and I could not find an existing issue for this bug
Current Behavior
I'm seeing this error when running dbt-snowflake on a schema with 12,500 objects:
16:39:51 Encountered an error:
Compilation Error in macro list_relations_without_caching (macros/adapters/metadata.sql)
dbt will list a maximum of 100000 objects in schema <DATABASE>.<SCHEMA>.
Your schema exceeds this limit. Please contact support@getdbt.com for troubleshooting tips,
or review and reduce the number of objects contained.
> in macro snowflake__get_paginated_relations_array (macros/adapters.sql)
> called by macro snowflake__list_relations_without_caching (macros/adapters.sql)
> called by macro list_relations_without_caching (macros/adapters/metadata.sql)
> called by macro list_relations_without_caching (macros/adapters/metadata.sql)
I can confirm that there are less than 100,000 objects in this schema.
-- use dbt service account role
show objects in <DATABASE>.<SCHEMA>;
result: 12,500
Expected Behavior
I should be able to run dbt-snowflake on a schema with 12,500 objects and the cache should reflect all 12,500 objects with no error.
Steps To Reproduce
Create a project with 12,500 objects in a single schema (or more than 10K really)
Initial analysis suggests this is the result of the 2024_07 Snowflake bundle. In particular, it looks like part of this change. We use the object name as a watermark when paginating. This is no longer deterministic, meaning that we could (and very likely do) get duplicates of relations and miss relations because we're picking a random starting point in a randomized list. In the past, this return recordset was ordered, making the pagination deterministic. We need to figure out a new watermark method.
Is this a new bug in dbt-snowflake?
Current Behavior
I'm seeing this error when running
dbt-snowflakeon a schema with 12,500 objects:I can confirm that there are less than 100,000 objects in this schema.
Expected Behavior
I should be able to run
dbt-snowflakeon a schema with 12,500 objects and the cache should reflect all 12,500 objects with no error.Steps To Reproduce
dbt runon the full projectRelevant log output
No response
Environment
Additional Context
Initial analysis suggests this is the result of the 2024_07 Snowflake bundle. In particular, it looks like part of this change. We use the object name as a watermark when paginating. This is no longer deterministic, meaning that we could (and very likely do) get duplicates of relations and miss relations because we're picking a random starting point in a randomized list. In the past, this return recordset was ordered, making the pagination deterministic. We need to figure out a new watermark method.