Forum Discussion

mohit_jain's avatar
mohit_jain
New Contributor III
3 years ago
Solved

Comparing the schema and discard the Object

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 column...
  • virender_prajap's avatar
    virender_prajap
    3 years ago

    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.

mohit_jain's avatar
mohit_jain
New Contributor III
3 years ago

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

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

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

 

darshthakkar's avatar
darshthakkar
Valued Contributor
3 years ago

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!

  • mohit_jain's avatar
    mohit_jain
    New Contributor III
    3 years ago

    Hi darshthakkar 

    We can not do the truncate load on the daily basis the only requirement is to find the extra column if we get in the future how we can handle it and send the notification that we got the extra column.

    Thanks