Value-Pairs in Snowflake: variant or object?

Question

We want to store value-pairs as semi-structured data types in Snowflake.

It looks like we could use either OBJECT or VARIANT datatype.

The documentation implies to use OBJECT for value-pairs but is not too clear as whether it is a "better" choice than Variant... Or I missed it (very possible)

So... OBJECT or VARIANT?

Update: So far, we have started with a column of type OBJECT that we populate with JSON. However, our JSON is simply value-pairs where the key name is never NULL.

Answer 1

Without knowing what kind of data you want to store in your table / column it's hard to answer. You could easily store an OBJECT in a VARIANT column but not the opposite way. When you store an OBJECT you have to ensure that the key is not empty. ("OBJECTS: Used to represent collections of key-value pairs, where the key is a non-empty string, and the value is a value of VARIANT type. Snowflake does not currently support explicitly-typed objects.") More information can be found here:

https://docs.snowflake.net/manuals/sql-reference/data-types-semistructured.html https://docs.snowflake.net/manuals/user-guide/querying-semistructured.html

If you would like to store JSON in your variant column you could directly query it or using a FLATTEN command to present it more table like.

Answer 2

For name/value pairs, OBJECT is sufficient. For example, OBJECT_CONSTRUCT('name',42). VARIANT allows you to store other data types as well.