Time Machine

Requests from the BI team are usually unreasonable. This one was the worst yet.

They wanted to see how data had changed over time. Specifically, data already in the RDB, already updated, compared against its state from a week ago. I wanted to tell them to write a batch job, but without pre-update snapshots, there's nothing for a batch to work with.

MariaDB has a feature called System Versioning. Declare it on a table, and every time a row is updated, the old version is automatically preserved. To see the past, just write FOR SYSTEM_TIME AS OF and a timestamp. It's called a time travel query. MySQL doesn't have it. Neither does PostgreSQL. MariaDB only. Snowflake has something similar, but in the RDBMS world, this is still far from standard.

There are catches, though. You can't look back past the moment you enabled it. A time machine, but it can't travel to before it was installed. History rows accumulate in the table, so backups balloon. There's no automatic purge. Changing column types requires compatibility with historical data, so you can't casually run ALTER. Convenient features come with maintenance costs.

I explained to the BI team. First, we'd need to migrate to MariaDB. After migration, enable System Versioning. Meaning last week's data would only be visible, at the earliest, one week after migration is complete.

After a silence, they said, "We'll just download it every week." I'd been dragged into the BI team's laziness, but between fellow lazy people, I couldn't find it in me to be angry.