Insert array into table in Snowflake

Trying to insert array into a table in Snowflake using VALUES clause may result in error similar to this one:

SQL compilation error: Invalid expression [ARRAY_CONSTRUCT(‘a1’, ‘a2’, ‘a3’, ‘a4’, ‘b1’)] in VALUES clause

To deal with it you need to use SELECT instead of VALUES:

insert into my_table_with_array_column 
SELECT 1,  ['a1','a2','a3','a4','b1']
UNION ALL 
SELECT 2, [];

Alternatively you can use ARRAY_CONSTRUCT:

insert into my_table_with_array_column 
SELECT 3, ARRAY_CONSTRUCT('a1','b1')
UNION ALL
SELECT 4, ARRAY_CONSTRUCT();