Schema changes

Source schemas move. Someone adds a column on a Tuesday afternoon, and a replication pipeline that was correct on Monday now has to decide, mid-stream, whether to carry the new column, ignore it, or stop and ask. Data Sync classifies every structural change it sees and applies a policy you chose in advance.

Compatible vs breaking Additive evolution only Human approval gate

Why this is the hard part

Detecting a schema change is not difficult. Data Sync reads the source's own change log, and the log tells it when the shape of a relation has changed. The interesting question is the next one: what should happen to the data already in flight, and to the target table that was built from the old shape.

There is no answer that is right for everyone. A new nullable column is almost always safe to carry through. A dropped column might be a cleanup you want mirrored, or it might be a mistake someone is about to revert. A type change from integer to text will not fit the column the sink already has. So Data Sync does two separate things: it classifies the change by whether it can be applied without losing information, and it routes the change according to the policy on the sync.

The rest of this page is those two mechanisms, plus what each sink physically does when a column appears.

How a change is classified

Every structural change Data Sync observes mid-stream lands in one of two classes: backward compatible, meaning the existing target can absorb it without losing data, or breaking, meaning it cannot.

Change	Condition	Class
Column added	nullable	Backward compatible — applied automatically
Column added	not nullable	Breaking — always requires approval
Type changed	a known widening	Backward compatible
Type changed	narrowing or incompatible	Breaking
Column dropped	always	Breaking

A not-null column addition is breaking for the obvious reason: the rows already at the sink have no value for it, and Data Sync will not invent one. A drop is breaking because the target column still exists and still has data in it — deciding whether that data should survive is a decision for you, not for the replication engine.

What counts as widening

A type change is backward compatible only when the new type can represent every value the old one could. Data Sync recognises these widenings:

From	To
`int8`	`int16` → `int32` → `int64`
`smallint`	`integer` → `bigint`
`float32`	`float64`
`real`	`double precision`
`varchar(n)`	`varchar(m)` where `m > n`
`varchar(n)`, `char`	`text`
`numeric(p,s)`	`numeric(p2,s)` where `p2 > p` and the scale is unchanged

Anything outside this list is treated as narrowing or incompatible, and therefore breaking. Note the scale condition on numeric: increasing precision is a widening, but changing the scale is not, because it changes how values are rounded.

Policy

The policy is set per sync, and your workspace carries a default that ships as require approval. It governs what happens to backward-compatible changes.

Policy	Behaviour
`widen`	Backward-compatible changes apply automatically and the stream keeps running.
`approve`	Every change gates on a human.
`fail`	The sync stops on an unmappable column.

widen is the low-friction setting. A new nullable column shows up at the source, the sink is extended, and nobody is paged. Choose it when the source is a service you trust to make additive changes and you would rather the pipeline kept moving than waited for a review. You are accepting that new columns can appear at the sink without anyone approving them first.

approve gates everything, compatible or not. Choose it when the sink is a governed target — a warehouse dataset with downstream consumers, a table whose schema is part of a contract — and an unannounced column is itself the problem you want to catch. This is the workspace default, and it is the conservative choice.

fail stops the sync when it meets a column it cannot map. Choose it when a silent divergence between source and sink is worse than an outage: the sync going down is loud, and you would rather find out that way than discover three weeks later that a column has been quietly absent.

A breaking change always routes through approval, regardless of policy. The policy only governs the compatible ones. Setting widen does not mean a dropped column or a narrowing type change is applied automatically — it never is. Those always stop and wait for a human.

The approval flow

When a change needs a decision, the sync moves to pending_schema_approval. Three things are true in that state, and they are the reason the flow is safe:

The sync holds its position. The checkpoint stays where it was; the source log is not advanced past the change.
No data is lost. Changes continue to accumulate in the source's log, subject to your source's own retention — a Postgres replication slot, a Spanner change stream's retention period.
Resolving the approval resumes from where it stopped, not from the beginning. There is no re-snapshot.

The state has exactly two legal exits:

Transition	When
`pending_schema_approval` → `starting`	The change is resolved and the sync resumes from its held position.
`pending_schema_approval` → `stopped`	You stop the sync instead of resolving it.

A sync can reach pending_schema_approval from starting or from running. Monitor reports a sync waiting on a schema approval as warn, not error, because nothing has failed — it is waiting on you. The full state machine is in Core concepts.

Watch your source retention while a sync is parked. The position is held, but the source still has to be able to serve it. A sync left in pending_schema_approval for long enough that the Postgres slot's WAL grows past what you are willing to keep, or past a Spanner change stream's retention window, becomes an operational problem of its own. Set a schema_drift alert so approvals do not sit unnoticed.

Per-table new-column policy

Alongside the sync-level policy, each table carries its own setting for what to do when a new column appears in it. You set it in step 4 of the create-a-sync wizard (Mappings — columns & targets), on the Schema changes control attached to each table.

Setting	Behaviour
`include` default	Forward the column; the sink evolves the target to accommodate it.
`ignore`	Consume the change and project the column out. The sink never sees it and the target is not altered.
`require approval`	Intended to park the table until a human decides. See the warning below.

Per-table require approval is not yet enforced. It currently behaves like ignore — the column is consumed and projected out rather than parking anything for review. Use include and ignore, which behave exactly as described above, and when you need a human gate use the sync-level approval flow, which does work.

In step 4 of the sync wizard, expand a table to reach its per-column controls and its table-level settings, including Schema changes. The setting applies to columns that appear after the sync has started; columns present at configuration time are governed by the include and exclude choices.

What sinks do with an added column

Schema evolution at the sink is additive only, and the mechanics differ by sink. Knowing which one you are on matters, because two of the three will not touch a target unless you have explicitly allowed it.

Sink	On a new column	Creates tables?
Google BigQuery	Appends the missing column to the existing table. It never modifies or removes an existing column. The operation is idempotent, so it is safe to attempt before every write.	With the table’s create mode set to Auto-create. Use existing requires the table to exist.
PostgreSQL	Emits `ALTER TABLE … ADD COLUMN IF NOT EXISTS`, and only when `evolve_schema` is enabled on the sync. With it off (the default) the column is simply not added.	Never. The target must already exist; targets are treated as user-owned.
Cloud Spanner	Widens an existing target with `ALTER TABLE … ADD COLUMN` per missing column. Added columns are always nullable — Spanner cannot add a `NOT NULL` column to a populated table without a default. Key columns are never altered.	Only with `auto_create_table` enabled; off by default.

No sink ever issues destructive DDL. A dropped source column does not drop the target column, a narrowed source type does not retype the target, and no sink truncates or rewrites a table on your behalf. The target keeps the old column, and it keeps the data in it. If you want the target column gone, you drop it yourself.

The practical consequence is that target schemas accumulate. Over a long-lived sync, a target table is the union of every column that has ever existed at the source, not a mirror of what is there today. That is deliberate — it is the only behaviour that cannot lose data — but it means periodic cleanup at the sink is yours to plan.

Practical advice

Prefer nullable additions. A nullable column is the only structural change that flows end to end without anyone intervening. If you control the source schema, adding a column as nullable and backfilling it afterwards costs you nothing and keeps the pipeline running.

Coordinate not-null additions and drops with a planned restart. Both are breaking and both will park the sync. Rather than discovering that at 3am, schedule the source change alongside the approval: make the change, resolve the sync's pending approval, and confirm the sink took the new shape. Because the position is held, the cost of the pause is lag, not lost data.

Use the dry run after changing mappings. If you have adjusted column includes, renames or derived columns in response to a schema change, the Test this sync action on the Review step samples real source rows, runs them through the exact mappings the sync would use, and shows you the transformed output. Nothing is written to the sink and sample rows are never persisted, so it is safe to run against production. It is the cheapest way to confirm a mapping edit does what you think before you resume the sync.

Set a schema_drift alert. A sync sitting in pending_schema_approval is stable but not making progress. An alert on schema_drift, sustained for a duration you choose and routed to Slack, PagerDuty or a webhook, is what turns a silent pause into something someone acts on. See Monitoring & runs.