CODEWITHSUNDEEP
mergeduplicatessnowflake-cloud-data-platformprimary-key
SMR
SMR

Reputation: 521

Duplicate Primary Key while doing latteral flattening in SNOWFLAKE

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

enter image description here

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

enter image description here

Upvotes: 1

Views: 1010

Answers (1)

Eric Lin
Eric Lin

Reputation: 1520

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.

Upvotes: 1

Related Questions