Data is being imported and stored as JSON in a VARIANT column. Query performance was fine, but most recently, poor query performance has been…

Data is being imported and stored as JSON in a VARIANT column. Query performance was fine, but

most recently, poor query performance has been reported. This could be caused by the following

factors:

The order of the keys in the JSON was changed. Snowflake stores semi-structured data internally in a

column-like structure for the most common elements, and the remainder in a leftovers-like column.

The order of the keys in the JSON affects how Snowflake determines the common elements and how

it optimizes the query performance. If the order of the keys in the JSON was changed, Snowflake

might have to re-parse the data and re-organize the internal storage, which could result in slower

query performance.

There were variations in string lengths for the JSON values in the recent data imports. Non-native

values, such as dates and timestamps, are stored as strings when loaded into a VARIANT column.

Operations on these values could be slower and also consume more space than when stored in a

relational column with the corresponding data type. If there were variations in string lengths for the

JSON values in the recent data imports, Snowflake might have to allocate more space and perform

more conversions, which could also result in slower query performance.

The other options are not valid causes for poor query performance:

There were JSON nulls in the recent data imports. Snowflake supports two types of null values in

semi-structured data: SQL NULL and JSON null. SQL NULL means the value is missing or unknown,

while JSON null means the value is explicitly set to null. Snowflake can distinguish between these

two types of null values and handle them accordingly. Having JSON nulls in the recent data imports

should not affect the query performance significantly.

The recent data imports contained fewer fields than usual. Snowflake can handle semi-structured

data with varying schemas and fields. Having fewer fields than usual in the recent data imports

should not affect the query performance significantly, as Snowflake can still optimize the data

ingestion and query execution based on the existing fields.

Reference:

Considerations for Semi-structured Data Stored in VARIANT

Snowflake Architect Training

Snowflake query performance on unique element in variant column

Snowflake variant performance