Schema migrations and data quality

Here is a request that sounds almost free:

“Can we add a middle-name field to customer profiles?”

Visually, the change may appear tiny: add one input box to the profile screen and save one additional value.

But the request affects more than the interface.

The frontend must collect the value, the API must accept it, the backend must validate it, and the database must store it. And further downstream, internal tools may need to display it, existing integrations may encounter it, and reports and data pipelines may need to understand it.

Most importantly, the company already has customers whose records were created before the field existed.

Changing a live product’s data structure is called a schema migration.

What is a schema migration?

A migrationConcept · lights on your mapmigrationA controlled change to the structure or contents of a database: adding, removing, or renaming a column, changing a data type, creating a table or index, modifying a constraint, or populating a new field for existing records. Migrations are written as scripts, reviewed, tested, and deployed through controlled processes, because they alter the rules governing the company’s persistent memory. is a controlled change to the structure or contents of a database.

A migration might:

• Add a column
• Remove a column
• Rename a column
• Change a data type
• Create a new table
• Add an index
• Add or modify a constraint
• Move data into a different structure
• Populate a new field for existing records

For the middle-name feature, a migration might add a column:

This changes the schema of the customers table.

Before the migration, a customer row might contain:

After the migration, the table might contain:

The database now has a place to store a middle name, but Erik’s existing record does not automatically acquire one.

Why migrations require care

A production database is not an empty spreadsheet waiting to be redesigned.

It may be serving:

• Customer requests
• Mobile applications
• Internal tools
• Automated jobs
• Reports
• Data pipelines
• Other services
• Thousands of simultaneous reads and writes

Changing the schema can affect all of them.

A poorly planned migration might:

• Block database activity
• Slow down important queries
• Break older application code
• Reject valid requests
• Lose information
• Produce inconsistent records
• Make a rollback difficult
• Cause different application versions to disagree

This is why migrations are commonly written as scripts, reviewed, tested, and deployed through controlled processes. Database changes are treated like code because they alter the rules governing the company’s persistent memory.

The compatibility problem

Software and database changes do not always become active at exactly the same instant. Imagine that some backend servers are running the old code while others are running the new code.

The new code understands middle_name. The old code does not.

A safe rollout may therefore happen in stages.

First, the team adds a nullable column that old code can ignore:

Next, the team deploys backend and frontend code that can read and write the new field, and later it may populate missing values, add stronger constraints, or remove old behavior.

This staged approach helps maintain backward compatibility, meaning older and newer parts of the system can continue working during the transition.

A migration is therefore often more than one database command. It can be a sequence of coordinated product and engineering changes.

Null and missing values

In Module 3, you learned that null means the absence of a value.

In a relational database, NULLConcept · lights on your mapNULLA column exists for a row, but no known value is stored in it. Critically, it does not say why. No middle name, a middle name never given, and a row created before the field existed all look identical. commonly means:

This column exists for this row, but no known value is stored in it.

Erik’s row might contain:

This does not necessarily mean Erik has no middle name.

It could mean:

• He does not have one.
• He has one but never provided it.
• The company has not asked him yet.
• The data was not available during migration.
• An older version of the application created the record.

These are different real-world situations represented by the same database value, and that ambiguity is why teams must think carefully about what NULL means for each field.

Null versus an absent field

A missing field in JSON and a NULL database value are related but not identical.

This JSON explicitly includes a null value:

This JSON omits the field entirely:

An API may interpret these differently.

For example:

• An omitted field could mean “leave the existing value unchanged.”
• An explicit null could mean “clear the existing value.”

The API contract must define the meaning.

Once the backend writes the record into a relational table, the column exists as part of the schema, and its value may then be NULL.

Nullable and required columns

A column that accepts NULL is called nullable.

A required column may be declared with a NOT NULL constraint, such as middle_name TEXT NOT NULL.

But making a new field required creates a problem. What value should the database use for the million customers who already exist?

The migration cannot safely require a value that historical records do not have unless the team supplies one.

For this reason, teams often initially make new fields nullable, and later they may decide whether the field should remain optional or become required after existing data has been handled.

What is a backfill?

A backfill is the process of populating new or corrected data for records that already exist.

Suppose a verified identity system can supply some customers’ middle names.

After adding the column, it might run a job that updates historical rows:

A real backfill might process millions of rows in smaller batches to avoid overwhelming the database.

A backfill may need to answer:

• Where does the historical value come from?
• Is that source trustworthy?
• Which records can be populated safely?
• How will failures be retried?
• How will progress be measured?
• Can the operation be paused?
• What should remain NULL?
• How will the results be verified?

Adding the column creates space for the information, and the backfill supplies it for the records that already exist.

Default values

Another option is to give a new column a default value.

For example:

New rows that omit the field may receive false.

But a default must mean something real, and here false is truthful: no existing customer has opted in. The middle-name column from earlier is a different story. Setting every existing customer’s middle name to an empty string would not reveal whether the person lacks a middle name or whether the company does not know it.

A convenient default can create misleading data if it pretends to know something the company does not know.

A good migration asks:

Is this default a truthful business value, or merely a value that makes the code easier?

Validation at three layers

The first lesson of this module introduced the three validation layers. Here they meet a real field: the middle name may be checked at several points.

Client-side validation

The frontend may limit the number of characters or explain which characters are accepted.

This helps the user correct mistakes before submitting the form.

Backend validation

The backend independently validates the value.

It may check:

• Whether the user may edit this profile
• Whether the field is too long
• Whether the request follows the API contract
• Whether the value satisfies product rules

This is the authoritative application-level validationConcept · lights on your mapdata validationChecking a value at several layers before it becomes stored truth. Client-side validation helps the user correct mistakes before submitting; backend validation authoritatively enforces the API contract and product rules; database constraints protect the stored structure..

Database constraints

The database may enforce rules such as:

• The value must be text.
• The value may not exceed a configured length.
• The column may or may not contain NULL.

The layers work together:

The frontend improves the experience. The backend enforces product rules. The database protects the stored structure and configured constraints.

The database does not automatically understand every business meaning. It can enforce that a value is text, but it may not know whether the name is genuine, culturally formatted correctly, or appropriate for a particular legal process.

What is data quality?

Data qualityConcept · lights on your mapdata qualityWhether data is suitable, reliable, and trustworthy for its intended use: accurate, complete enough, consistent, timely, valid, and unique where appropriate. Poor data quality develops gradually (one missing field, one duplicate customer, one delayed pipeline) until the small issues compound and people stop trusting the system. describes whether data is suitable, reliable, and trustworthy for its intended use.

High-quality data may need to be:

• Accurate
• Complete enough
• Consistent
• Timely
• Valid
• Unique where appropriate
• Connected to the correct entities
• Governed by clear definitions

Poor data quality develops gradually.

One missing field may appear harmless. One duplicate customer may seem manageable. One delayed data pipeline may affect only a single report.

Over time, those small issues compound until people stop trusting the system.

Incomplete data

Data is incomplete when important expected information is missing.

A customer without an email address is incomplete data. So is an order without a store ID, a payment without a recorded status, a profile with an unexplained NULL, or a report missing an entire day of activity.

Incomplete does not always mean invalid.

A middle name may legitimately be optional, and the problem occurs when users of the data assume the value is complete when it is not.

Data quality depends partly on defining which fields are required for each purpose.

Invalid data

Data is invalid when it violates an expected rule.

Examples include:

• A negative order quantity
• An impossible date
• An unknown order status
• An email placed in a numeric field
• An order referencing a nonexistent customer
• A required value stored as NULL

Validation and database constraints help prevent invalid data from entering the system.

However, rules change, and data that was considered valid under an old product design may not satisfy a newer design.

Migrations sometimes need to correct historical records before stronger constraints can be added.

Duplicate records

A duplicate recordConcept · lights on your mapduplicate dataMultiple rows or records representing the same real-world entity or event: the same customer with two accounts, the same event imported twice by a retry. Finding and combining duplicate records is called deduplication, which is difficult because the system must avoid merging two different people who merely look similar. occurs when multiple rows represent the same real-world entity or event.

The customers table might contain:

Are these two different people who share an email? Or did the same customer accidentally receive two accounts?

The database cannot always answer those questions from the stored values alone.

Duplicates arise in several ways. A user signs up twice, two systems import the same record, or a retry creates the same event more than once when an integration does not use idempotency. Weak matching rules let near-duplicates through, names and contact information change, and merging historical systems brings both copies along.

Finding and combining duplicate records is called deduplication, and it can be difficult because the system must avoid merging two different people who merely look similar.

Duplicate facts

Duplication can also mean storing copies of the same fact across several places.

Erik’s email may exist in the primary customer database, a support platform, a marketing platform, an analytics warehouse, and a spreadsheet maintained by operations.

And some of that duplication is unavoidable because each separate system needs its own working copy.

The question becomes:

Which copy is authoritative, and how are the other copies updated?

Without a clear answer, the copies may drift apart.

Data drift

Data drift in this context occurs when copies or representations of information gradually stop matching.

Erik changes his email in the customer application.

The main customer database updates immediately, but the marketing platform updates tomorrow, the support tool fails to receive the change, a spreadsheet remains unchanged, and an analytics pipeline keeps the old value for a week. The systems now disagree.

This is sometimes described as data synchronization or data consistency trouble, depending on the situation, and the company needs rules governing how changes move between systems and what happens when an update fails.

Stale data

Data is stale when it was once correct but no longer reflects the current state.

A cached account status may be several minutes old. A daily report may not include this morning’s orders. An internal tool may display an email address copied before the customer changed it.

Stale data is not necessarily the result of a bug.

Some systems deliberately accept delay in exchange for speed, availability, or simpler processing.

The important requirement is that users understand the freshness guarantee.

A dashboard labeled “updated nightly” should not be treated as real-time operational truth.

Conflicting sources of truth

Suppose Erik’s email differs across three systems:

Which of the three should the company trust?

A source-of-truthConcept · lights on your mapsource of truthThe system designated to own the authoritative version of a field. For example, “the customer database is the source of truth for account email.” Other systems may hold synchronized copies, but they should not independently redefine the value without a controlled process. A source of truth does not mean that no copies exist; it means one system has the authority to settle disagreements. decision.

The company must designate which system owns the authoritative version of the field.

For example:

The customer database is the source of truth for account email.

Other systems may hold synchronized copies, but they should not independently redefine the value without a controlled process.

A source of truth does not mean that no copies exist. It means one system has the authority to settle disagreements.

Master data

Some organizations use the term master data for important shared entities such as customers, products, employees, suppliers, and locations.

When many systems need their own versions of the same entity, the organization may establish a master system or process responsible for maintaining the authoritative record.

You only need to recognize the term.

It names the same broader challenge:

How does a company maintain one trusted identity for important business entities across many systems?

Data quality and trust

Poor data quality has practical costs. It can cause:

• Incorrect customer messages
• Failed deliveries
• Bad product decisions
• Misstated financial reports
• Duplicate charges or refunds
• Compliance problems
• Wasted operational work
• Conflicting experiments

It can also cost trust, first the customers’ and then the employees’ faith in the company’s own analytics.

Once teams stop trusting the data, they create manual checks and private spreadsheets.

Those workarounds create additional copies, which can produce even more disagreement, so data-quality problems can reinforce themselves.

Improving data quality

Improvement has many levers, and most of them have already appeared in this lesson. Validation at the three layers and well-chosen database constraints keep bad values out. Safe migrations and honest backfills change structure without corrupting history. Duplicate detection and idempotent writes fight copies, a designated source of truth and reliable synchronization keep systems agreeing, and monitoring catches missing or unusual values early. Behind all of it sit ownership for important datasets and a process for correcting errors.

Data quality is an ongoing property of how products collect, validate, store, move, and update information, not one cleanup project.

Seeing the middle-name change honestly

Return to the original request:

“Can we add a middle-name field?”

The complete change may require the team to:

1. Decide whether the field is optional.
2. Define what NULL means.
3. Add the database column through a migration.
4. Keep old and new application versions compatible.
5. Update the API contract.
6. Add backend validation.
7. Add the frontend field.
8. Update internal tools.
9. Decide whether historical records need a backfill.
10. Update exports and downstream systems.
11. Monitor whether the field is being stored correctly.
12. Decide which system is the source of truth.

The request may still be straightforward.

But it reaches well past the act of drawing a text box, changing the shape and meaning of data across a live product.

The mental model to remember

A schema migration is a controlled change to a database’s structure or existing data.

A backfill populates new or corrected values for historical records.

NULL means that a database column contains no known value for a row.

Backward compatibility allows older and newer parts of a system to continue working during a change.

Data quality describes whether data is accurate, complete enough, consistent, timely, valid, and trustworthy for its intended use.

A duplicate record occurs when multiple records represent the same real-world entity or event.

Stale data was once accurate but no longer reflects the latest state.

A source of truth is the authoritative system that settles disagreements among copies.

Data constraints, backend validation, synchronization, and ownership all contribute to trustworthy data.

You should now understand why even a small field change can require planning across the schema, historical records, APIs, application code, internal tools, and downstream systems.