Schema changes: why one renamed field breaks five

On Tuesday, someone in sales ops renames a picklist value in Salesforce from “Enterprise” to “Enterprise (1000+)”. Nothing fails. On Wednesday, the pipeline loads the new value, the dashboards group by segment, and the Enterprise row shows a third of last week’s revenue. A new row called “Enterprise (1000+)” shows the rest, and it sits below the fold. The CFO notices on Friday.

Handling changes to source structure is the sixth step of data integration. The changes that break a pipeline loudly are the easy ones. The expensive ones break nothing.

What is schema change management?

Schema change management detects changes to the structure of the systems you read from, such as fields added, renamed, removed or changed in type, and picklist values added or renamed. It works out what depends on each change, and tells the owners of those things before the next pipeline run, instead of after a report is wrong.

Why one renamed field breaks five integrations

1. Teams only watch for failures. A removed field fails loudly. A renamed picklist value or a changed field type fails nothing and changes the numbers.
2. The source team doesn’t know who reads their system. The admin who renamed the value had no way to know five pipelines and twelve dashboards depended on it.
3. Nobody knows what depends on a field. When a change is noticed, the next day goes on working out which reports and integrations use it.
4. Alerts go to the wrong person. The source system’s owner hears about it, but the people whose dashboards break don’t.
5. Every change gets the same alert. An added field and a removed field look the same in the alert, so people stop reading them.

What it costs

Wrong numbers that look right are the most expensive kind. A dashboard that errors gets fixed the same day. A dashboard that drops a third of its rows without an error can run for weeks, and every decision made from it in that time was made on the wrong number.

It also costs the data team’s time. Every change becomes an investigation, and the same people spend part of every week tracing fields through pipelines by hand.

What to fix first

1. Snapshot every source schema on a schedule. Compare each snapshot with the last one, so changes are detected instead of discovered.
2. Classify each change by impact. Added fields are safe. Removed fields and type changes break things. Renames, including renamed picklist values, are the dangerous middle and need a person.
3. Map what depends on each field. Keep a record of which pipelines, tables and dashboards read each field, so an alert can say what breaks.
4. Alert the owners of what breaks. Tell the people whose dashboards and integrations depend on the field, as well as the source system’s owner.
5. Pause the affected pipeline on a breaking change. Stop the load for that table until a person confirms the mapping.

The guide to building schema change management has the prompts for each step.

What it looks like in a live sync

AVID Property Group found its Salesforce workflows broke whenever the Salesforce schema changed. It rebuilt its sync on Tray.ai to handle new and deleted fields automatically, with small delta loads every 15 minutes.

Where it fits

Schema changes are the most common reason a change data capture pipeline stops, and the reason a Salesforce to Snowflake copy drifts. They are also what an API facade protects apps from. Catching them is half of keeping the warehouse right; data quality monitoring is the other half.

For all seven steps, see the data integration process.