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...
  • Which MySQL version? Can you partition by month and then you have quick access to dropping partitions? – danblack Nov 7 '18 at 2:04
  • We are using MySQL 8.0. When using the tables, we may need to refer to all historical data so I dont think partition by month will help... – Jupyterous Nov 7 '18 at 3:10
  • partitioning doesn't mean you don't have access to the same historical data with in the same SQL, just means there's some constraints on the PK. With MySQL8.0, you can exchange a table into a partition. – danblack Nov 7 '18 at 5:49
  • Partitioning requires the PK to be the partitioned columns. The tables has many index, we will need to look at more on this. – Jupyterous Nov 9 '18 at 23:16
  • 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.

| improve this answer | |
  • We don't have a Master-Master replication setup. But actually I am looking at replication topics. What I did currently is duplicate the proc schema and do ETL there, once checked and replace the Proc schema with it. Currently we process data once per month. – Jupyterous Nov 9 '18 at 23:16
  • @Jupyterous - "proc schema" is a bit ambiguous: Just the CREATE TABLE? A full table? What? – Rick James Nov 9 '18 at 23:36
  • Sorry about the confusion. The Proc Schema is a schema where we put 30 production tables for data analytics. Every month we received new files and process them into these files. Because the sequence of the file processing is so important that once a file is missing or passed, we need to reprocess all the historical files. – Jupyterous Nov 12 '18 at 23:21

Not the answer you're looking for? Browse other questions tagged or ask your own question.