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

3 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


Srinivas Reddy —

@Veronica : Could you please provide the example developed using Grid variable and Detector component.
Since long back we are struggling to get the output using Grid variables to repeat the activity for multiple tables.
We are stuck badly while using grid variables.

Your example may help us and resolve our issue


Matillion Agent  

Kalyan Arangam —

Hi Srinivas,

Note that there are some shared jobs that you may “Unpack” and review to see how grid variables can be used.

Steps to unpack a shared-job -

1. Open any orchestration job
2. Go to Shared Jobs section under Components (left)
3. Expand “Read-Only→Matillion→Change Data Capture”
4. Right-click on “CDC Sync to Target” and select Unpack.

This will unpack the constituent jobs which have some examples of using grid variables.

I would recommend raising a separate ticket with your use case. Email us a brief of what you intend to achieve and attach an export of the jobs you’ve developed so far. We may be able to review and point out if any changes are necessary.

Best
Kalyan

Post Your Community Answer

To add an answer please login