I’ve been testing snowflake connector. While it worked with basic usage, I got some issues with advanced features. Sharing here for discussion before raising any issue in github. • When enabling the

profile

feature, I got the following error:

Cannot perform CREATE TEMPTABLE. This session does not have a current schema. Call 'USE SCHEMA', or use a qualified name.

Have you got this error before? Is there any way I could set which is the schema to be used for the profiler when creating the temporary tables? • When using the

snowflake-usage

connector, I got the error below.

"Failed to parse usage line {'query_start_time': datetime.datetime(2021, 10, 17, 3, 41, 14, 560000, "
                        'tzinfo=datetime.timezone.utc), \'query_text\': "create temporary table '
                        'avalanche.dwh_stage_iad.accepted_values_stg_ad_images_source_system__IAD__tmp as\\n      select '
                        'test_run_start_ts,\\n            row_count,\\n            failure_row_count,\\n              case when failure_row_count > '
                        "0\\n                  then 'ERROR'\\n                  else 'PASS'\\n            end as test_status\\n      from "
                        '(\\n        select current_timestamp as test_run_start_ts,\\n                count_all_sql.row_count,\\n                '
                        'case when count_all_sql.row_count > 0 \\n                      then (\\n                              -- begin of data test '
                        'query\\n                              select count(1) as row_count\\n      from  avalanche.dwh_stage_iad.stg_ad_images as '
                        "model\\n      \\n        where (\\n                source_system not in ('IAD')\\n              "
                        ')\\n                              -- and of data test query\\n                          )\\n                      else '
                        '0\\n                end as failure_row_count\\n          from (\\n                  -- begin of count all '
                        'query\\n                select count(1) as row_count\\n      from  avalanche.dwh_stage_iad.stg_ad_images as model\\n      '
                        '\\n                  -- end of count all query\\n              ) as count_all_sql\\n      );", \'query_type\': '
                        "'CREATE_TABLE_AS_SELECT', 'base_objects_accessed': [{'columns': [{'columnId': 82388016, 'columnName': 'SOURCE_SYSTEM'}], "
                        "'objectDomain': 'Table', 'objectId': 19718154, 'objectName': 'AVALANCHE.DWH_STAGE_IAD.STG_AD_IMAGES'}], 'user_name': "
                        "'SERVICE_AVALANCHE', 'first_name': 'Avalanche', 'last_name': 'Service Account', 'display_name': 'SERVICE_AVALANCHE', "
                        "'email': None, 'role_name': 'SERVICE_DBT'}",

• Also, regarding the

snowflake-usage

connector, it called my attention that it is handled as an independent connector instead of just a property on the

snowflake

connector. Because of that, while I can filter (`allow`/`deny`) tables and schemas with the

snowflake

connector, I can’t with the

snowflake-usage

one. This results in

snowflake-usage

producing events for tables that I don’t want to be in the catalog. Any reason why this split of the connector? Or how can I keep both connectors aligned on which tables being processed? Thanks in advance!

Hi @witty-butcher-82399, what is the role that you have specified? If it is not

accountadmin

, can you try with this role?

Actually I haven’t specified any role in the connector so I assume it is picking up the default one for the user. With

DESCRIBE USER

I found that default role is

META_DATA_READER

and no secondary roles. Not sure if

META_DATA_READER

is a custom role or what; Snowflake instance is managed by a different team. When setting up a connector, we try to avoid requesting users with broad permissions… and

accountadmin

sounds like that, am I wrong?

@handsome-belgium-11927, We use great_expectations for profiling the data. It creates a bunch of temp tables. The user cannot be strictly a read-only user if you want profiling to work.

Sure, I understand that. My concern is on not being able to specify which schema to be used for those temp tables.

@witty-butcher-82399, I'm looking into the other two bullets and will get back to you soon.

I am also stumbling here. I don't want to use ACCOUNTADMIN privileges here as I feel this would be over-privileged for a profiling job. Thu, I am looking for a way to set the database.schema for the temporary tables. It can be done for BigQuery via

profiling.bigquery_temp_table_schema

but not for Snowflake, as it seems :-(