AI Glossary / Data engineering and architecture / Intermediate
Schema migration
Definition
Schema migration is a controlled change to the structure of a database, such as adding a table, renaming a column, changing a data type, or introducing a constraint. It moves the database from one defined structural version to another. Existing records and running applications may need coordinated changes. A schema migration therefore involves more than editing a diagram or changing a class definition in application code.
What it means
An application evolves while its stored data must remain usable. A new feature might need an additional field; a better design might split one table into two. The migration describes how the existing database reaches the new state. Some migrations change only structure, while others require data transformation or backfilling. Moving an entire database to another server is a different activity, although it can occur alongside a schema migration.
How it works
Describe the starting and target schemas, dependencies, and data assumptions. Create a versioned change using database commands or a migration framework. Test it on representative data, including records that violate new assumptions. Plan application compatibility during deployment, validation after the change, and recovery if something fails. Some changes can be applied in stages: add a new structure, populate and validate it, switch consumers, then remove the old structure when safe. Locking, transaction support, and online-change behavior depend on the database and operation.
Three practical examples
- Product features: an application adds an optional display name while retaining the existing legal-name field, then updates its interface to use the new value where present.
- Data quality: a team introduces a uniqueness constraint after identifying and resolving duplicate identifiers under an approved policy.
- Analytics pipelines: engineers replace a loosely formatted date string with a typed date field, isolating invalid records and checking the interpretation before switching reports.
Think of it this way
Think of renovating an occupied building. Changing the floor plan affects the people and services already using it, so the work needs sequencing and checks. The analogy stops at database specifics: some structural changes are nearly instantaneous, while others scan or rewrite large amounts of data. The apparent simplicity of a change request does not reliably predict its operational cost.
Worked example
A table has 1,000 records with a field called delivery_date_text. Of these, 970 contain an agreed year-month-day format, 20 are blank, and 10 contain ambiguous day/month strings. The proposed new field is a nullable date. A cautious migration adds that field, converts the 970 unambiguous values, and leaves the remaining 30 unresolved under a documented policy. It does not guess the meaning of the ten ambiguous strings. After owners resolve them and decide how blanks should be represented, the team compares counts and sample dates before changing the application. If a non-null constraint is required, it cannot be validly added while unresolved nulls remain. The numbers are illustrative, not a suggested universal error tolerance.
Illustrative case study
Fictional example: Oak Scheduling splits a single contact field into email and phone fields. Its first plan assumes every old value is an email address. A trial on representative records reveals mixed values and free-text notes. The team revises the migration to classify known patterns, flag uncertain records, and preserve the original during review. Only after validation does it switch the interface. This invented case shows why a structurally valid migration can still misinterpret data if its assumptions are wrong.
Common misconceptions and mistakes
Do not assume an automatically generated migration is semantically correct. A tool may detect a rename as deletion and addition, or lack the business rule for transforming values. Do not assume every migration can be reversed without data loss; dropping information may make reversal impossible without a retained copy. Also avoid equating successful execution with successful deployment. Reports, background jobs, integrations, and older application instances may still depend on the previous schema.
Business use
Treat schema migrations as part of the release process with ownership and review. Identify dependent consumers, estimate operational impact using realistic volumes, and define acceptance checks. Keep the change history aligned with the actual database state. Separate business decisions about ambiguous records from mechanical conversion rules. For AI products, check feature-generation jobs and stored evaluation data as well as the user-facing application; a renamed or reinterpreted column can change model inputs even when the application still starts successfully.
People also ask
Is schema migration the same as data migration?
They overlap, but schema migration changes structure, while data migration can move or transform records without changing the schema.
Does a rollback script guarantee recovery?
No. It may not restore discarded information or handle all partial-deployment states.
Can migrations run without downtime?
Some can, depending on the operation, database, workload, and deployment design. The phrase “small change” is not enough to establish that.
Check your understanding
A new column is filled correctly for 95% of records, and the application requires it for every record. Is the migration ready simply because the database command succeeded?
Answer: No. The remaining records violate the application's requirement. Resolve or explicitly handle them and verify compatibility before relying on the new field.
Related terms
- Data Definition Language
- Database transaction
- schema versioning
- data migration
- backfill
- constraint
- backward compatibility
Learn next
design a harmless migration on a sample database that adds a typed field alongside an old text field. Write the conversion assumptions, ambiguous cases, and acceptance checks before running it. Compare the proposed recovery plan with what information would actually remain after each step.
More in Data engineering and architecture
- Schema - A schema is a description of how data is organized and constrained.
- Schema crosswalk - A schema crosswalk documents how elements in one metadata or data schema correspond to elements in another.
- Schema matching - Schema matching is the process of identifying meaningful correspondences between elements of different schemas.
- Second normal form (2NF) - Second normal form, abbreviated 2NF, is a relational database design condition.
- Semantic data model - A semantic data model describes what data represents, including the concepts, relationships, and rules needed to interpret it in a domain.
- Semantic heterogeneity - Semantic heterogeneity is variation in the meaning or interpretation of data across sources that appear to describe related things.
Sources
Primary sources consulted: PostgreSQL 18, Modifying Tables, https://www.postgresql.org/docs/18/ddl-alter.html; ALTER TABLE, https://www.postgresql.org/docs/current/sql-altertable.html — search excerpts consulted. Django migration documentation, https://docs.djangoproject.com/en/4.1/topics/migrations/ — excerpt distinguishing schema and data operations consulted as a versioned example, not current-version guidance. Scenarios are original; full editorial review remains pending.
Previous: Schema matching Next: Schur decomposition