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. What could be causing this?

Question

Options

• AThere were JSON nulls in the recent data imports.
• BThe order of the keys in the JSON was changed.
• CThe recent data imports contained fewer fields than usual.
• DThere were variations in string lengths for the JSON values in the recent data imports.

• A
  4% (1)
• B
  83% (19)
• C
  4% (1)
• D
  9% (2)

Explanation

Snowflake automatically detects and caches the schema of semi-structured data stored in VARIANT columns. It extracts column-level statistics and builds micro-partition metadata based on the detected key structure of the JSON, which enables column pruning and efficient query execution. When the order of keys in the JSON changes, Snowflake's schema detection and cached column-level metadata becomes inconsistent with the new data layout. This invalidates the optimizations that allowed efficient pruning of micro-partitions, forcing full scans and degrading performance. Option A (JSON nulls) are handled gracefully and do not cause significant performance degradation. Option C (fewer fields) would reduce data volume and likely improve performance. Option D (string length variations) have minimal impact on query planning or pruning.

No community discussion yet for this question.