Check Source data against target database

Two Stars

Check Source data against target database

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.
Moderator

Re: Check Source data against target database

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Two Stars

Re: Check Source data against target database

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.
Moderator

Re: Check Source data against target database

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Two Stars

Re: Check Source data against target database

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.
Moderator

Re: Check Source data against target database

Hi,
Feel free let me know if it is OK with you.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Check Source data against target database

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.