Tag: daily

I’ve been working with a data team that imports data from suppliers daily into a MySQL DB. The data from the supplier needs to be worked on by them and formatted properly before we can use it in production.

Every night, I must import that “formatted” data into a production Amazon RDS instance (same MySQL version). Here’s the catch. The databases are for an e-commerce solution. Products might be added everyday by the data team, but new products could have been added by sale staff via the site directly into the production database which explains why the 2 databases are desynced constantly.

This is what I did to solve the issue :

• Each element in both database gets a UUID (same across both DBs) assigned so that we can identify it uniquely when importing and set its foreign keys properly to references we already have in the database with the same UUID.

example: the data team adds a product in the “products” table. this product
might have the id (3) and the category_id (23). When we import it, we
might already have 100 products created by our staff in that table so we must assign this new
product the id (101). Also, if we already have it’s category created on our end,
it might not be the id (23) so we identify the relation on their end,
look for the category with the same UUID on our end (let say it’s category
(53)) we then assign the category_id (53) to the product.

Imagine this for all tables and all relations. This is all done via a script in our PHP Laravel API that runs nightly.

Sorry for the long backstory as this issue is not easily explained by text. Now what I am wondering is how bad is it to handle the data in such a way. It seems to be VERY primitive to change the keys like this. This manipulation is preventing me from using nice automated tools like Amazon DMS or AWS Glue which could be used for nightly imports. I’m not used to work with an external data team that have a copy of our DB structure and work in it 7 days a week for us to import it into our ever moving database but I am sure this is not a rare scenario so there must be something obvious I am missing.

• How could I handle those nightly seeds better?
• Am I missing out on some tool that should obviously be used here but that we are trying to code on our end for no reason?
• Am I just overthinking something that is just “normal” to have to do when managing the production environnment with another team seeding data and should just make my script very robust and ignore trying to automate it more by fear of causing issues more than solving them.