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