Problem Background
I am taking over a Python based simulator project. The simulator queries a relational database to read inputs to the simulation. There are about 20 input tables that must be filled in for the simulator to run. Some of these tables contain gigabytes of time series data. Currently, I know of only one existing input database that enables the simulator to run. This database is located in Snowflake.
The simulator uses the Python SQLAlchemy package to interact with the database. The data model is also defined by the SQLAlchemy code in the simulator. This SQLAlchemy code has the functionality to create an empty relational database in MySQL, Snowflake or SQLite.
I am aiming to "decouple" the program from this complex input database from the codebase so I can start writing test cases to test the individual components and identify bottlenecks that are slowing down the simulator. There is also a feature request to use excel sheets as inputs to simply the setup process. To achieve this aim, I also need to successfully perform this decoupling. This task of decoupling is proving to be very difficult. The reason for the difficulty are that the SQLAlchemy code is deeply embedded in the simulation, so much so that individual objects make database calls during their construction. The second issue is that the one, existing database is very complex and includes more information than the bare minimum required to run the simulator.
My approach has been to try to create a separate, simplified input database in MySQL or SQLite. This has proved to be more difficult than I expected because the data in the Snowflake database does not all follow the foreign key constraints laid out in the data model. Snowflake does not enforce foreign key constraints so data was entered into the database without following the data model.
Possible Solution What I need to do is to transfer the data from snowflake to my MySQL database without including any of the database rows that violate the foreign key constraints laid out in the SQLAlchemy data model.
Core Question How can I achieve this objective efficiently and without going row-by-row through the database?
I could try using Pandas to query the Snowflake database row-by-row, try to insert into the MySQL database and skip any rows that throw database exceptions. This database migration issue seems general enough, where they might be a best practice that I am unaware of, which is why I am asking this question.