Video: How to Dynamically Adjust Target Tables in Snowflake Staging Layer

Hello there, my name is Guillaume Durand. I’m a data engineer at Passerelle. And today, I’m going to show you how the Data Rocket Dynamic Ingestion can save you some maintenance headaches.

With Data Rocket, when a source system table structure changes, we automatically and dynamically adjust target tables in our Snowflake staging layer. We call this dynamic staging column adjustment. For this example, let me use sales order header table from a fictitious ERP. This would be your typical sales order header – we’ve got some order dates, PO number information, customer ID, shipping address, billing address, credit card validation, and on and on.

This row is already in Snowflake, it’s already in my staging layer, I’ve got one version of it. Now let’s look at the source system. For this example, let’s pretend my ERP vendor dropped an update. And the structure changed, I may have a new column or some data datatype change. So this is the exact same row as it is at the moment in the source system. Now let’s pretend I run this update and I just add a new column called additional comment. And let me populate this additional comment with some values. And this is how the data looks like. So this same sales order other if I scroll to the right, I got my new additional comment columns with some values here. Now let’s go to Talend and run our dynamic ingestion.

What I would expect after this run is to see a new row, a new version for that same sales order header ID with the new columns, and that would become our version number two. The first version will still stay in our staging data lake, but it would be closed and inactive. All right, now that the job is completed, let’s look at the data in Snowflake once again. As expected, I now have a second version of this row. If I scroll to the right side of it, I can see that my additional column has been added with my value. I can see this has now become the version number two this is the current active record. And I can look and see that the first one was active from June 28 until today and this next New Version two is active as of today until well so far the end of time. I hope you like it and you appreciate this video showing how we can dynamically adjust columns in the target staging layer in Snowflake.