Snowflake variant performance

Question

I have a Snowflake table which is couple of TBs of size. I am at a point to decide whether to have a field inside a variant column or to have it as a standalone column. The data type of the field is a simple string.

I was evaluating and comparing the performance by querying this field (in the where clause) from within variant column against querying directly as a separate column. The statistics show that querying from within variant is 40%-60% slower than querying it from a regular column.

Could not find anything in Snowflake docs that say querying variants for varchar is slower. Any thoughts on this is much appreciated. I will need to make some important decision based on this and educate the end users of the table

Thanks in advance

I'd recommend reviewing the query profiles for both queries, to see what the differences are. docs.snowflake.com/en/user-guide/ui-query-profile.html I'm working with a client who has large tables with variants, they store some columnar data in standard columns and "less frequently used" data in the variants, works very well and performs great. — Rich Murnane
Thanks Rich for your inputs. I do think that's the way to go too. Data that is frequently queried are better off in standard columns — Syamjith

Michael Golos Michael Golos · Accepted Answer · 2021-10-04T16:04:10

It depends on the structure of this data and how you will use it later. If you plan to store complex semi-structured data and query it for single nodes, your best option is to store the data in the VARIANT type. You can read more here: Semi-structured Data Considerations

If, as you wrote, you store a simple string, it is better to use the STRING type. Thanks to this, you will avoid converting types, you will also be able to use Search Optimization Service or Clustering.

Have you done any benchmarking with Search Optimization enabled on this table?

Snowflake variant performance

2 Answers