What's your data ETL methodology? I think this is more like an open question: How do you do the ETL for your company?

We are using MySQL as our database management system and we have a production schema. Every month, when we received new data files, we processed the data into that. 

The problem is, if directly processing data into the production schema, if anything goes wrong, we have to rollback (if we made backup before). So, currently, I replicate that schema and do the ETL there, once everything is fine, I move those tables into the production one. 

However, the size of the production schema is about 250GB (some table rows are more than 800mil), it is difficult to replicate that schema and do the ETL.

So, any brilliant idea? Appreciated!

Additional considerations:


We are MySQL 8.0 Enterprise Edition. 
Database is hosted on AWS RHEL EC2.
When using the tables, we may need to refer to all historical data so I don't think partition by month will help...
If you are replacing all the data, then build new tables, test them, then swap.
If you have a Master-Master replication setup, then break the connection, do the ETL, test, then failover and reestablish the link.
If something else, please elaborate.


More details from me after more from you.