Comparing the schema and discard the Object

[mohit_jain]

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

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:

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 

[mohit_jain]

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 

[darshthakkar]

Got it. You'll have to use a couple of snaps for designing this.

Have a defined payload coming from source, check for any additional columns (can be achieved via a filter/router snap). If there is no change, it can go to downstream system for ingestion.

If there is a change, copy the output into 2 different stream, one for notifying the group a new column was found in the payload and the other for an ingestion to downstream in such a way that you're using an ALTER TABLE query to add a column on top of ingesting data for the existing columns.

This use case will require a lot of testing from your side and can be achieved by parent-child relation pipeline as well. I would urge you to test it out without parent-child (if you've never used it before) and then replicate the functionality in parent-child so that you know where and how the data is flowing.

Thanks.

[alchemiz]

Hi @mohit_jain ,

Attached poc pipeline that checks parameter base column names versus streaming document keys
canvas_2023_08_13.slp 

** Set reuse checked in pipeline execute

Thanks,
EmEm

[mohit_jain]

Hi @alchemiz 

Thanks for you reply
The only thing is here we are not getting headers from the pipeline parameter( There are no parent and child pipeline in this). We have a 2 files . 
1. Initial file - which record is already dropped into the SFTP location.
2. Incremental File - File will come on the daily basis.
We have to compare both the files header and fetch the extra call out the load the unfetch column.
Here I am attaching two files for you reference can you please perform on this file in the pipeline and fetch the extra column out.
I am also attaching the pipeline for you reference.
Initial Load:

Incremental Load: 

Here we have to discard the Column E and load the A, B, C, D column. Also Fetch the E column and give alert that there is E column extra in the file

Pipeline Design :

Thanks 

Mohit Jain

[mohit_jain]

Hi @alchemiz 
Do you have got any chance to look into my reply.
Thanks 
Mohit jain