Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

merging tables in snowflake

Our use case:
1. Create a copy of all the tables in staging schema(basically cloning tables from production).
2. Compare the cloned tables with the new tables(From source)
a. if there are updates
->update the cloned table in staging
b. if there are new rows,
->insert them into the cloned table
b. if there are no updates,
->don't make any changes
3. Move the cloned table into production.


Question 1: Can we achieve this using table update component?
Question 2: If so, how can we use variables to hold all the columns of respective tables when using variable(We want to avoid giving names of the columns manually each time)
Question 3: How to merge all the columns of the tables in the when matched case in table update component

1 Community Answers

Matillion Agent  

Veronica Kupetz —

Hi Venkata,

Thanks for reaching out. Here are the answers to your questions:

1. Yes, you can achieve it partially with a Table Update Component. In addition, you will need to use the Detect Changes Component. This component will allow to compare 2 tables (source and target). If there are changes, there will be an “Indicator” column referencing if it is new, changed, deleted or no change. You can then use 2 Filter Components. If the “indicator” is equal to “N”, you can have it perform a Table Output and append to the target table. If the “indicator” is equal to “C” (for change), you can have it perform the Table Update.

In addition, here is a very helpful example regarding Type 6 Slowly Changing Dimensions Example that may benefit you to review.

2. You can use Grid Variables to populate those columns. I am happy to share a sample job that my colleague has created that you can review and alter for your use case. I will send that to your email as there is no way to post on this forum.

3. I am not sure if I am understanding the last question correctly, but I think the above 2 answers should help out to get you started.

Best Regards,
Veronica

Post Your Community Answer

To add an answer please login