Currently our system is in live proving phase. So, we need to check whether the set of tables populated in production are matching with the tables populated in sandbox (test). At the moment we have written a query for each table comparison and then run it in sql client to check it. There will be few more tables to check in future. I thought of automating the process in python by supplying the table names to a function which can then load the two tables in dataframes and then do a comparison which could highlight the differences.
Some of the tables have 2.7 millions rows for a day and are wide having 400 columns. When I tried to load the data (2.7 m rows * 400 columns) into dataframe, I get an error as it runs out of memory as I run my query in Jupyter where I have only 20 GB limit. what are the options here? Is Pandas dataframes only way to compare this large dataset? or are there any other library to achieve the same?
For handling this kind of data I would recommend using something like Hadoop rather than pandas/python.  This isn't much of an answer but I can't comment yet.
Python comparing millions of rows and hundreds of columns between two tables from relational DB