Beat methods to validate/test the output data with original data

Other Tech Topics
1

I have built dataflows from customer input data in the Asure data factory. We designed the Workflows which are built on Alteryx(another Etl tool) in Azure data factory.

Now testing the sample data is ok on both sides.

But how to validate the entire output of Alteryx and Azure be matched/Validated? Is there any tool?

My output file format is CSV.

Is there any automation process to validate all the rows of Alteryx output and Azure Output so that I can be sure that I have built the right Dataflow logic?

2

One approach is to import both CSVs into SQL Server and then check the two tables with EXCEPT. If both these queries produce zero rows then the contents are the same:

/* In Old but not New */
SELECT * FROM Old
EXCEPT
SELECT * FROM New
GO
/* In New but not Old */
SELECT * FROM New
EXCEPT
SELECT * FROM Old
3

I use Beyond Compare to perform file comparisons. It works quite well and will even identify CSV files and perform a column based comparison between the files.