Duplicate Primary Key while doing latteral flattening in SNOWFLAKE

Question

I am kind a new with working with arrays in SNOWFLAKE database.

I am trying to load data into dimension tables in SNOWFLAKE database using merge statement where the primary keys of those dimension tables are generated in the staging table itself using nextval and used in dimension tables. I was fine until this point.

Now in my scenario, I might have arrays (having more than one value) as below screenshot and when I use latteral flatten those arrays and merge them into my dimension, I get duplicate primary key (For eg. If I have two values in my array, then I get same primary key value twice).

Could someone pleas help me in how I can overcome this problem or should I not generate the primary key for dimension tables in staging table and do it in the dimension table itself

Screeenshot of dataset with array of values

Screenshot of my result after merge of the array using lateral flatten

Answer 1

That's the purpose of FLATTEN function, if you have one row with ID 1, and data [v1, v2], then the result will be:

1 -> v1
1 -> v2

Example as below:

with t as (
    select 1 as id, parse_json('["v1", "v2"]') as data
)
select id, value::varchar 
from t, 
lateral flatten(input => data)
;

+----+----------------+
| ID | VALUE::VARCHAR |
|----+----------------|
|  1 | v1             |
|  1 | v2             |
+----+----------------+

I think you need to review what you want to achieve and FLATTEN might not what you are after.