I saw https://www.masterywithsql.com/ posted on HN a few months ago, and I'm planning to finally start it over break. But what do I use after that?
The trick is not in the application of highly complex SQL. The trick is in the PROCESS — making it robust, testable, traceable, and reversible for every object despite the complex dependencies. And then indeed validating every step went well, with the validation depth depending on the objects criticality (OK return code vs check of record counts vs check of totals on fields vs check of totals per category etc)
If you‘re really into it, consider the use of automation and tracing IDs for individual loads.
If you‘re worried about the time it takes despite automation: work on the bulk of the data, but use DB triggers to record delta from your snapshot, then treat that „sidecar“ accordingly.
Then: do at least one end-to-end test.
Learn from the mistakes at every single stage, and act on what you‘ve learned.
- learn how to backup the database (I suggest via the official documentation)
- restore that backup (on a different environment)
- validate the restore is complete (compare the data)
- if the backup and restore are good, now you can start learning how to migrate without the stress that you'll lose historical data
At this kind of scale + uptime requirement, you'd want to do a migration like this gradually - hydrating the new system with data and keeping it up-to-date with changes for a period of weeks/months while doing testing + validation (and ideally also doing a gradual cutover, although that might not be realistic given banking infrastructure/application design).
Probably the relevant approaches to look into after reviewing basic backup/restore are things like log-shipping or change-data-capture, although choosing the right approach would be highly dependent on the underlying technology, architecture, and requirements...
- database restore - database migration (your case) - data migration to a different platform and presumably different data model (the bank‘s case, or my answer here)
Not sure which case the question was reffering to, though...