SnowPro Advanced Architect

By Snowflake · Question Mode

QuestionQ83

Performance, Cost, and Resource Optimization

A company has a source system that supplies JSON records for various IoT operations. The JSON is loaded directly into a persistent table containing a VARIANT field. The data is rapidly expanding to hundreds of millions of records, and performance is becoming an issue. A common access pattern filters on the create_date key within the VARIANT field.

What can be done to improve performance?

Explanation

Snowflake recommends extracting frequently queried semi-structured keys into relational columns for better pruning. Dates and timestamps loaded in a VARIANT value are stored as strings, so placing create_date in a native TIMESTAMP column enables micro-partition metadata and partition pruning for date filters.

Learn more

Community Discussion

No comments yet. Be the first to start the discussion!