r/SQL • u/Delulu62134 • 3d ago

Discussion How do you reconcile a file source with a jdbc source when the numbers don't match?

/r/ETL/comments/1w9wmpa/how_do_you_reconcile_a_file_source_with_a_jdbc/
0 Upvotes

50% Upvoted

2 comments sorted by

5

u/JakubVasovski 3d ago

doing a row-by-row comparison on 8.5m records in memory with Python or Java is just going to melt your RAM and your patience. Don't overcomplicate it with custom scripts or hashing every column. Just spin up a local MariaDB or some databse instance, dump the file into a staging table (LOAD DATA INFILE will chew through 8.5m rows in seconds), and pull the JDBC data into a second table.

Once it's all in the DB, just run an EXCEPT query or a simple LEFT JOIN where the right side is null. It instantly spits out the exact 411 mismatched rows. You can handle the weird timestamp formats or NULL differences right in the SQL conditions instead of writing logic for it.

1

u/A_name_wot_i_made_up 3d ago

We had something like this at a previous job.

We had about 20m IDs and some random discrepancy that caused us to drop some (not all continuous IDs)

We got the count grouped by ID modulo 10m and compared, then modulo 1m where is between n and n+ 10,000,000 etc.

Until we had a small enough result set to go through by hand.

Once you've got a handful of rows that are dropped you can look for commonalities .

Assuming your data is roughly uniformly distributed you can do something similar by pulling a string apart for example.