Snowflake’s variant data type columns can accept a variety of data types, but these columns require some additional considerations when attempting to modify their data. The two primary considerations are that you may need to use a “SELECT” clause instead of a “Values” clause, and you will need to use one of a couple methods of casting the data into a variant type. See the following examples for clarification. In each of the examples below, we are inserting a new record with two columns, ID and a variant data type column value (I.e., “VARIANT1”):
We can use “TO_VARIANT” to insert a float value into the variant column:
INSERT INTO VARIANTTABLE (ID, VARIANT1) SELECT 1, TO_VARIANT(5.0)
We can use “PARSE_JSON” to insert JSON structures into the variant column:
INSERT INTO VARIANTTABLE (ID, VARIANT1) SELECT 1, PARSE_JSON('{"test": "Testing", "url":"www.website.com"}')
For both query examples above, you must use the “SELECT” clause instead of the “VALUES” clause – using the “VALUES” clause in this situation will cause a SQL compilation error.
For more details on how Variant data types operate and should be handled, see the Snowflake documentation here: https://docs.snowflake.com/en/sql-reference/data-types-semistructured#examples
