Latest Insights

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

Being able to access all your organization’s data in the Snowflake Data Cloud fuels data consumption and decision-making, but only as long as your Data Cloud can keep up with changes to source system data. 

In this 3-minute video, Passerelle engineer Guillaume D. walks through how Data Rocket saves maintenance time and headache by dynamically adjusting target tables in the Snowflake Staging Layer when changes are made to the source systems table structure. 

The Dynamic Ingestion Framework is only one small component of Data Rocket. Ready to learn more? Request a complimentary consultation now. 

Watch Now.

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.

Return to Blog