topic Re: Check Source data against target database in Talend Studio

Check Source data against target database

Anonymous — Mon, 13 Jan 2014 23:08:47 GMT

First, there's got to be a correct name for this... Data verification, data validation don't seem to fit. Data reconciliation? I don't know.
Anyway, having performed ETL from a data source and inserted records in a target database, how do we check the results?
I can do counts against the source and target manually. I can write a ton of SQL that will do joins like I created in tMap, and reverse left outer joins from source to target and target to source, and I can run queries to compare result columns but...
Isn't there a way to do this, faster, easier, and possible using the jobs that I designed?
How to we check our results? Compare source data against finished target data? Get lists of records not existing in one or the other database?

System and development info:
We're using TOS for Data Integration, 5.3.1, from Oracle to Oracle.
Our designs are pretty vanilla, and probably not up to best practices by experienced designers' standards.

Re: Check Source data against target database

Anonymous — Tue, 14 Jan 2014 02:22:14 GMT

Hi,
What's your expected result? The quality data? If so, are you referring to the feature in talend data quality product TalendHelpCenter:Table analyses?
Best regards
Sabrina

Re: Check Source data against target database

Anonymous — Tue, 14 Jan 2014 17:51:10 GMT

What I'm trying to get is proof that the data we are trying to ETL did, indeed, make it to the target.
What rows are missing, if any. And, in our case, are there any additional rows (shared database, and the developers stick in some test data).
As the target tables are joins of the source tables, I need to check the parent record, and any child/joined records.
The DQ check intra-table data, on a selected database, correct? I did not think that it allows comparisons between two separate databases.

Re: Check Source data against target database

Anonymous — Wed, 15 Jan 2014 07:31:58 GMT

Hi,
Yes, the DQ check intra-table data, on a selected database(one connection from DB).
For your scenario, you can "Compare" two tables between source DB and target DB to see if the target DB is consistent with source DB.
The job design should be:
t<DB>Input(target DB)-->tMap-->output
t<DB>Input(source DB)-->
Set inner join in tMap and the "Catch lookup inner join reject" as true.
Best regards
Sabrina

Re: Check Source data against target database

Anonymous — Wed, 22 Jan 2014 05:18:19 GMT

Thanks you Sabrina, I will be trying that soon. It will be nice to have the system check rather than writing so much code to reverse what I've put into the new DB.

Re: Check Source data against target database

Anonymous — Wed, 22 Jan 2014 05:49:22 GMT

Hi,
Feel free let me know if it is OK with you.
Best regards
Sabrina

Re: Check Source data against target database

Anonymous — Wed, 24 Sep 2014 07:53:26 GMT

Hi,
I'm following the same approach proposed by comment #4 in validating data after migration. In this approach is it possible to capture the place where the data is mismatch? Meantime I want to know whether is there any option to identify any missing rows when data is migrated from source table to target table? Please help on this.

Thanks and Regards,
Asanka.