This is a big and inevitable lesson learned in my data engineer career path. This not funny but a valuable experience in this time point. It is great thing that I know such lesson as early as possible. I made some mistakes in work while I try to change some key filed in migration. Changing key migration is painful so I have the following lessons learned here.
-
Always keep data integrity in mind while working with key fields. If we are trying to change pk to a new column, we have to make sure the new column is correctly populated in advance, since pk is not nullable. After the new column is ready to be a pk, we then set it as pk and remove the previous one. If we are trying to create a new column as fk, we can first define that column as nullable, and after it gets populated, we can revoke the nullable property for that column.
-
Do not be greedy to finish this in one step. We can try our best to break this down into small pieces so that the changes are only ripples but not waves for other teams.
-
Be aware of what downstream work will be influenced by this migration and let this updated to other teams in advance. Even if we check downstream effects in the future, it is also worthy of letting others notice this loudly.
-
Add local sanity check as a step when we manipulate key field migration