cancel
Showing results for 
Search instead for 
Did you mean: 

Comparing the schema and discard the Object

mohit_jain
New Contributor

Hi Team,

I have one scenario and want to solve it as soon as possible.
I have two files : Initial Load , Incremental Load

We have loaded the initial load data into the DB which contains the columns like : id, name, age, class

mohit_jain_0-1691659876373.png

But We have the incremental files on the daily basis but there are some case we have the extra column which we have to discard the value and alert to the team that column is added newly How we can do in the pipeline .
Incremental file columns:

mohit_jain_1-1691660036331.png

We have to give alert that this column is roll_number is added  and also discard the columns. We cant do manually because it is a daily job and me can user mapper in this case. 

Can anyone help me in this case.

Thanks in advance.
@koryknick @dmiller 

1 ACCEPTED SOLUTION

@mohit_jain the solution is very straight forward.
1. Get the header from your initial file.
2. Merge it with your incremental file.
3. Filter match and unmatched in two different output streams.

Download solution pipeline Compare_Schema_Discard_load & below is the screenshot.

Screenshot 2023-08-17 131936.png

View solution in original post

14 REPLIES 14

darshthakkar
Valued Contributor

I will merge them.


Diane Miller
Community Manager

Hi Team,

Can anyone help to find the values if the schema is different.
let me explain with the example:
We have loaded the initial load into the table:
Initial Load contains the column like

mohit_jain_0-1691653529791.png

Here you can see we have 4 columns ID, Name, Age, Class
Now we have the incremental load and it contains the columns like

mohit_jain_1-1691653622321.png

Here you can see we have 5 columns ID, Name, Age, Class as well as new added Roll_Number

Now We have to discard the roll_number value and send the alter that this column has added newly in this file 
As well as we dont want to do this manually by using mapper we have to schedule the pipeline on this daily basis and there are around 100 files we have to process the data

Can anyone help to create the dynamic pipeline for all the files.
Thanks In advance

 

@mohit_jain : Does the value change everyday on the existing records? Rationale behind asking this is that you can go ahead with Truncate and Load in Snowflake Bulk Load snap.

I had a similar use case wherein I was getting dynamic payload but that would ingest "Null" values to the existing records for the new columns and you wouldn't be able to troubleshoot on when the records were first ingested.

So, we asked the business to define the payload (which happened for a few integrations) and for the rest, we are ingesting JSON to snowflake and then snowflake handles it dynamically, hope this helps!