[ELE-1119] dbt_columns and filtered_information_schema_tables models fails on large amount of schemas

[Maayan-s]

https://elementary-community.slack.com/archives/C02CTC89LAX/p1686855852542619

_ELE-1119

[elongl]

Another case.

[pei0804]

I think I may have found the problem area and will report it.
The error message is as follows

00:32:12  Database Error in model filtered_information_schema_columns (models/edr/metadata_store/filtered_information_schema_columns.sql)
00:32:12    002003 (02000): SQL compilation error:
00:32:12    Database '"snowflake"' does not exist or not authorized.
00:32:12  
00:32:12  Database Error in model dbt_columns (models/edr/dbt_artifacts/dbt_columns.sql)
00:32:12    002003 (02000): SQL compilation error:
00:32:12    Database '"snowflake"' does not exist or not authorized.

It got error to SQL.

select count(*)
        from "snowflake".INFORMATION_SCHEMA.schemata
        where upper(schema_name) = upper('account_usage')
            and upper(catalog_name) = upper('snowflake')
/* {"app": "dbt", "dbt_snowflake_query_tags_version": "2.0.1", "dbt_version": "1.5.2", "project_name": "dbt", "target_name": "dev", "target_database": "TEST", "target_schema": "public", "invocation_id": "0f2b2213-d6ab-46b6-97d0-54212036b157", "node_name": "filtered_information_schema_columns", "node_alias": "filtered_information_schema_columns", "node_package_name": "elementary", "node_original_file_path": "models/edr/metadata_store/filtered_information_schema_columns.sql", "node_database": "ELEMENTARY", "node_schema": "elementary", "node_id": "model.elementary.filtered_information_schema_columns", "node_resource_type": "model", "node_tags": ["elementary"], "node_refs": [], "materialized": "view"} */

The error is caused by the "snowflake" part.
The Snowflake database that Snowflake has is all uppercase.
So the following SQL will succeed.

select count(*)
        from "SNOWFLAKE".INFORMATION_SCHEMA.schemata
        where upper(schema_name) = upper('account_usage')
            and upper(catalog_name) = upper('snowflake')

If we can better adjust how Database is specified, the problem seems to be resolved.

[pei0804]

#945 (comment)

I have solved this problem.
It was a compatibility issue with the setup.

diff --git a/dbt/dbt_project.yml b/dbt/dbt_project.yml
--- a/dbt/dbt_project.yml
+++ b/dbt/dbt_project.yml
@@ -46,7 +46,7 @@ on-run-end:
 # Snowflake Configs
 # ======
 quoting:
-  database: true
+  database: false
   identifier: false
   schema: false

[yu-iskw]

Another case. I would like to create just the empty schema of filtered_information_schema_tables at the initialization with dbt run --select elementary.

[elongl]

Hi @yu-iskw, our main use for filtered_information_schema_columns is the schema changes tests.
However, due to this issue, we're planning to change the implementation of the way we take the schema snapshots.

here's our plan for solving this issue:

1. We're going to change the implementation of the schema_changes and schema_changes_from_baseline tests to use adapter.get_columns_in_relation instead of using those models.
2. Delete the filtered_information_schema_columns model.

Therefore, this model is not going to exist and removing it will by nature solve your issue.
However, the dbt_columns model will remain.

[yu-iskw]

Hi @elongl , thank you for sharing the plan. That sounds good.

We would like to initialize and migrate elementary's schema without accessing data of our dbt models using dbt run --select elementary. We segregate dbt models in a dbt project. The segregations are mutually exclussive. As we use dbt and elementary with BigQuery, there is no service account which can access any models in the dbt project. We split the service accounts to deal with dbt models in each segregation for security. As I tried to migrate elementary's schema on BigQuery from elementary 0.8.0 to 0.10.3, dbt run --select elementary tries to access the information schemas of all dbt models. The dbt run --select elementary doesn't work on our use case. So, I would like the initialization/migrtion process to work without any data access to our own dbt models including the information schemas.

[lschinasi]

I am also having this issue with the dbt_columns model due to a lowercase db name. I updated the quoting in the project.yml file, but this doesn't work because it breaks for a different db name.

20:09:35 On model.elementary.dbt_columns: select count(*)
        from db_name.INFORMATION_SCHEMA.schemata
        where upper(schema_name) = upper('{DB_NAME}')
            and upper(catalog_name) = upper('db_name')
Database 'DB_NAME' does not exist or not authorized.
20:09:35 Snowflake adapter: Error running SQL: macro check_schema_exists
20:09:35 Snowflake adapter: Rolling back transaction.
20:09:36 Timing info for model.elementary.dbt_columns (compile): 20:09:15.120370 => 20:09:36.045354
20:09:36 On model.elementary.dbt_columns: Close
20:09:36 Database Error in model dbt_columns (models/edr/dbt_artifacts/dbt_columns.sql)
  002003 (02000): SQL compilation error:
  Database 'DB_NAME' does not exist or not authorized.
20:09:36 29 of 30 ERROR creating sql view model dbt_user_elementary.dbt_columns .... [ERROR in 21.30s]

[haritamar]

I'm closing since these models no longer exist in Elementary so I tend to think these issue are irrelevant
(there is a model called dbt_columns today but it has a different meaning)