Discussion: Align migration logic between destinations #6600
Description
Context
Prerequisite for #6584 and related to https://discord.com/channels/872925471417962546/1062341270955110501.
Currently our auto migration logic differs between destinations, which leads to confusion on what's breaking, what's auto migratable and what user action is needed on each major version bump or migration failure.
Current state (based on #6043)
| Change type | Breaking? | Reason | Auto migratable |
|---|---|---|---|
| Adding a column | false |
Doesn't break any data consistency | Yes |
| Adding a column to a PK | false |
Doesn't break any data consistency | Yes, assuming the data exists and not null we recreate the constraint |
| Removing a column from a PK | false |
Assuming the new PK is unique this should be OK | Yes, we recreate the constraint |
| Removing a column | true |
Column will be null in future syncs |
Yes, we don't drop the column |
| Changing composite PK columns order | false |
May change the partitioning, but otherwise, harmless | Yes, we recreate the constraint |
| Changing column type | true |
Unknown if we can migrate | Yes in Postgres (we drop and add the column), No in BigQuery, Snowflake and SQLite (we fail the migration) |
We're mostly consistent except in type changes.
Proposal
We should decide what to do when a column type changes (try to convert, drop+add or error) and do it for all destinations.
Once we align the migration logic we can provide better action items once the migration fails.
As a follow up we could suggest a migrate --force command that recreates the new schema with the potential data loss, otherwise it can be quite tedious to figure out from the changelog which tables/columns need to be changed/dropped.
Thoughts?