Skip to main content
Docs
Feature Requests Onboarding Trust Center Status Page

Community

Destination Improvement: Snowflake: use a different query to identify clustering keys to prevent unnecessary errors

Answered
Félix User

Currently, clustering keys are checked in Snowflake with a query like this:
SELECT SYSTEM$CLUSTERING_INFORMATION('<table_name>')​

This however throws errors when the targeted table has no clustering set up.
See Snowflake's documentation: https://docs.snowflake.com/en/sql-reference/functions/system_clustering_information.html

Using the following query instead would be better:
SELECT *
FROM <DB_NAME>."INFORMATION_SCHEMA"."TABLES"
WHERE CLUSTERING_KEY IS NOT NULL;

This would not generate errors, and would simply return an empty result set if there is no clustering set up on Fivetran-created tables.

Please sign in to leave a comment.

Comments

4 comments

Date Votes
  • Official comment
    Kevin Kim Fivetranner

    Hi Felix,

    Thanks for your post here!

    Thank you for this insight, I will share this feedback with our Snowflake engineering team. Do you happen to have an example of the error you are seeing with the current SELECT SYSTEM$CLUSTERING_INFORMATION('<table_name>')​ query? I want to be sure to share as much context with engineering before looking into this request.

    Are the errors you are seeing in the Snowflake side or within the Fivetran destination logs?

  • Tomer Nahum User

    Hi Kevin Kim,
    same issue in my environment. 
    SELECT SYSTEM$CLUSTERING_INFORMATION('table_name')
    produce this error in snowflake logs:

    Invalid clustering keys or table TABLE_NAME is not clustered.

    errors are seen on the snowflake side only. Fivetran logs are not mentioning these errors.

  • Joneson Oh User
    • Edited

    Can we please have this feature expedited? It has been 10 months since this was raised and it does not seem like a complicated fix.

    We have many tables across different environments, and this is causing a large amount of query failures every day for no reason, potentially causing us to miss true failures on Snowflake.

  • Arijeet Mukherjee User

    Please expedite this feature request so that we do not see unnecessary errors in our Snowflake system.
    Although they might not be leading to any additional cost but we tend to miss the actual errors for a user in Snowflake.