Issues using Diff Snap

Hi,

I’m new to snap logic, I have two data sources (Oracle, SQL) the new is Oracle and existing is SQL.

The pipeline’s first run, runs as expected, but the second, where I would expect all data to be in the “unmodified” output data preview is not working as expected. I’m trying to find where my settings are incorrect.

Entire Pipeline:

Diff:
image

I believe my issue is similar to this

Any help is appreciated thank you,

Are you executing the pipeline using the Validate button? If so, try holding the Shift key when you click the Validate button. That will bypass caching.

@dmcnichol
My guess is that you have large number of records in both of the sources, and when you hit execute, the pipeline works fine, detects modifications and unmodified rows accordingly. But when you try to validate the data, you are validating the pipeline, and by that, you end up with 2 completely different sets of data from both sources. Meaning, the IDs from the “original” set are not in the “new” set.
So as a result of that, all of the IDs from one source will go to Deletions, and from the other source, will go to Insertions.

Find a way to validate the data differently. One suggestion is to take specific group of IDs, and when you validate, you’ll make sure to use those IDs in both sources.

Thank you both for your quick responses. :grinning:

I’m dealing with a small set of data 498 records.

I’ve been using ‘Shift click Validate’ as a fall back when snaps are not behaving as I thought they should or I change the underlying file or database structure.

in order to simplify my issue (pretty sure this is new user growing pains) I’ve filtered both New and old data sources by id=271967386.

First run performs as expected:

Second run not so much (expected 1 record in the ‘Unmodified’ output)

I suspect it is due to data type? could anyone possibly confirm?

Oracle (new) data preview before Diff Snap:

image

SQL (old/existing) data preview before Diff Snap

image

I’m going to try using the mapper snap or another transform to solve.

Thank you all again for your time.

Update:

I cut down the data to only the ID field and recreated the destination SQL table, so 1 record 1 field and the issue was still present.

Adding a mapping snap which changed the ID(Oracle Source) from number to string seems to resolve it, but when I try to add other fields (still 1 record) the record is routed to the “Modified” output.

I’ve used notepad++ compare plugin on the two Diff snap’s inputs and they are a match, this has really confused me

Mapper Snap Details:

Pipeline with mapper:

any advice appreciated

We’ll need more info than this to help diagnose the issue. Please insert a Mapper in front of each input to the Diff and provide a screenshot of the input data in each Mapper so we can see what it shows for the data and the data types.

Sure thing, thanks again.

I believe it maybe a datatype issue with ORDER_TOTAL and ORDER_SUBTOTAL

I’m able to get it to work properly with one record.

Oracle Mapper:

SQL Mapper:

Pipeline Overview:

Data Before Mapping Oracle Data:

Data After Mapping Oracle Data:

Data Before Mapping SQL data:

Data After Mapping SQL data:

Hope this helps, if you need anything else let me know

Try disabling Pass through on the Mappers and instead, map each field individually so that the output from each Mapper is in a consistent order and has consistent types. I would suggest starting with just the three fields you’re mapping directly now, see how the Diff behaves, and if it behaves as expected, map another field. Repeat for each field until you’ve got them all working or until you find a problematic field that makes the Diff behave in an unexpected way. Perhaps disable the snaps after the Diff so that you’re not changing any data until the Diff behaves as expected.

I think I have it halfway solved. it appears the Oracle Sort Snap was not working as expected.

In order to resolve I hard coded an “ORDER BY” in the Oracle Select statement, but after raising the “Preview Document Count” to 2000 the validation and full run worked properly.

Guess it came back to new user mistake.

Thank you both for taking the time to help me through this.

I’m sure i’ll be back soon :slight_smile:

1 Like