Data validation between source and target

Question

I have created a data pipeline to create ETL from Source to Target.

Source : DB2

Target: SQL SERVER

For validation i did NULL check, count check , distinct check....etc

As data is transactional in nature, aggregates not provide much insight.

for ex: sum (profit) won't be correct.

May someone please share their thoughts what are the major checks can be perform for Sales and transaction.

Thank you very much

Answer 1

When migrating sales or monetary transaction data from one system to another system with a different database, there are several important considerations and validations that must be taken into account to ensure data integrity, in addition to the ones you mentioned. Here are some of the post-migration validations that might be performed:

1. Data consistency: Verify that the migrated data is consistent with the original data. This includes verifying that sales amounts, totals, taxes, and other details match the expected values.
2. Numerical Accuracy: Ensure that numerical values, especially monetary amounts, have been migrated accurately and without rounding errors.
3. Currency and units: Validate that currency and unit conversions have been performed correctly during the migration, especially if you are moving from a system with a different currency.
4. Missing records: Check that all sales records were migrated correctly and that there are no missing transactions in the target system. I guess you already did that when you used the COUNT validation.
5. Reference integrity: Verify that relationships between tables, such as associations between sales and customers, remain intact and that there are no orphaned records or incorrect related data.
6. Date and time validations: Ensure that transaction dates and times have been migrated correctly and are in the proper format.
7. Tax validations: If taxes are handled differently in the target system, verify that tax calculations and assignments are accurate.
8. Financial Reconciliation: Perform a financial reconciliation to ensure that overall revenue and expense totals are maintained after migration.
9. Performance testing: Test the performance of the new system with the migrated data to ensure that it can handle the sales transaction workload without degrading performance.
10. Manual audit and review: Perform manual audits to verify samples of migrated data and make sure they match the original data.

These are just a few of the many validations that may be required after migrating sales data or monetary transactions between different database systems. It is important to collaborate with database and finance professionals to identify and address potential problems. For example, on the organizations I have worked with, there are people who can see the total amount on reports and they know if the totals make sense or not.

Answer 2

Hello

I guess you should have a Primary Key and/or an ID column with Unique Values. If so, you can take a range of IDs on both source and destination and verify SUM, COUNT, NULLs , etc