Merge statement in SNOWFLAKE database on primary key error

Question

I am kinda confused with the way merge statement has to be written in my scenario to load data from a table into dimension and fact tables.

Below is the merge statement where I am trying to load data into my DIM product table from the JSON table but I am getting NULL values loaded.

Am I doing something wrong with the merge and should I not merge on primary key and instead on the fields like cityname

I am having the same issue while trying to load data into the fact table

Could someone please help?

merge into dim_product as a using (select productID, product from jsontable) as b
on b.productID = a.productID
when matched then update a.productID = b.productID
when not matched then insert (productID, product) values (b.productID, b.product));

Below is the Existing Dimension Products table

Below is the sample JSON from which I am trying to merge and insert the new record Mango into my DIM table and also populate the PRODUCTID into my fact table

Below is the fact table

Answer 1

If we do not have ProductID on the source and set it only in Dim_Product, we should use the business key. In your case, ProductName is the business key. The solution is simple, when you are doing MERGE you should use ProductName as the key instead of ProductID.

Your MERGE should look similar to this:

merge into dim_product as a using (select ProductName from jsontable) as b
on b.ProductName = a.ProductName
when not matched then insert (ProductName) values (b.ProductName));

If you have more attributes describing the Product, they should be modified inside the MERGE.