Our team is currently investigating adding our manual data conversion scripts to the SF instead of managing them manually and running a bunch of scripts before and after the 020-upgrade script.
We ran into a small issue, the current option for adding manual data conversion including from version x to y or always don't exactly suffice. We are looking for a smart option in between those two, which works similarly to a smart upgrade of tables, control procedures or tasks.
An example of the problem, I have version 1.1, 1.2 and 1.3 of a project. If I add a manual conversion script to the 1.2 using the ‘always’ option, this script is generated into any upgrade following the 1.2 upgrades. For example 1.1 → 1.2, 1.1 → 1.3 but also 1.3 → 1.4. And that one we find less desirable.
If we chose the second manual data conversion option, x to y, and add the script to the 1.2 version we have to specify which specific version the code is generated from (the x). Most logical would be to specify this manual data conversion script to be applicable from 1.1 to 1.2. Now the conversion script is only generated into upgrades from 1.1 → 1.2. Not into 1.1 → 1.3 for example.
We would like to be able to generate these into upgrade scripts ‘smart’-ly. Thus, if we add a manual conversion script in version 1.2, it will be generated in updates 1.1 → 1.2, 1.1 → 1.3, 1.1 → 1.4, etc. But not into the upgrade script 1.3 → 1.4.
Splitting up upgrades and upgrading from 1.1→ 1.2 and then 1.2→ 1.3 manually doesn't work for us as we have about 40 instances of our applications running as production databases, a fair number of which are 24/7 available/active with limited time for downtime/upgrades.
I agree that the data migration part of an upgrade could be smarter and better. I think there are already quite a few ideas out there to improve.
I personally would like to see the upgrade being cut up on multiples steps create / migrate / delete. Where migration of tables can be out of the box via the SF or replaced with custom upgrade scripts replacing the ‘default’ all together.
And to support reusability of custom migration scripts it would com in handy to choose if you want combined upgrades over multiple versions or a sequence of upgrades per intermediate version so you can prepare each upgrade and reuse it in various deployment scenario's.
As for@Freddy 's comment: agreed that the Data Migration part could use further improvements. I like the Idea of linking upgrade script to specific Table/Column migrations, as an override to a Default very much!
It would be better to create a separate Idea for that indeed. Small part of it overlaps with this idea: