Trick to rebuild summary tables when the existing data have to remain available during the operation

As a Data Engineer, I encountered situations where the summary or reporting tables need to be repopulated daily. When the population is ongoing, we need to make sure that the existing data is still available to the user.

We can achieve this by using a “shadow table” which acts like a staging table you build “behind” the real table. When you’re done building it, you can swap the tables with an atomic rename. For example, if you need to rebuild my_table, you can create my_table_new, fill it with data, and rename it as my_table. In case the new table has a problem, you can rename my_table to my_table_old before that for a quick rollback.

mysql> DROP TABLE IF EXISTS my_table_new, my_table_old;
mysql> CREATE TABLE my_table_new LIKE my_table;
-- populate my_table_new as desired
mysql> RENAME TABLE my_table TO my_table_old, my_table_new TO my_table;