Schema evolution
Overview
Learn how SQL Flow carries a source schema change to each target, or holds the table for your review.
Schema evolution keeps your target tables in step when the structure of a source table changes. When you add, drop or change a column on the source, SQL Flow carries the change to every target of the pipeline, at the same point in the stream as on the source. If a target can't make a change exactly, SQL Flow holds that one table for your review while everything else keeps streaming.
Schema evolution works for every source, capture mode and target. There's nothing to switch on.
How a schema change flows
SQL Flow reads the change from the source's change stream at the point where the DDL committed, so rows changed before it are written with the old columns and rows changed after it with the new ones. Each target then applies the change in order with the data, or holds the table for your review. With two targets, a change can be applied on one and held on the other.
What happens to a change
| Outcome | When | What you do |
|---|---|---|
| Applied automatically | The target can make the change exactly: an added column, a dropped column that isn't part of the key, and other column changes the target can reproduce. | Nothing. |
| Held for review | The target can't apply the change by itself, for example a dropped key column or a changed row key. | Review the proposed DDL on the pipeline. Other tables keep streaming. See Reviewing held changes. |
| Pipeline stops | The capture can't follow the change exactly, for example a renamed or dropped table, or a column type change that needs a conversion. | Fix the cause, then reload. See Changes that stop the pipeline. |
Supported changes lists every kind of change and its outcome.
Holds are per table
A hold pauses one table on one target, at the position of the change. The rest of the pipeline keeps going:
- Every other table keeps streaming to that target, and every table keeps streaming to the pipeline's other targets.
- The held table's later changes are kept, in order, and none is applied until you decide. A later schema change of the same table waits for the earlier one.
- The source log isn't held, but the agent's spool can't shrink past the change until you decide. See Lag, spool and log retention.
- A hold survives agent restarts and a takeover by another agent.
When you apply the DDL, the table catches up from the change and rejoins the stream. See How the table resumes.
Where you see schema changes
| Where | What it shows |
|---|---|
| Schema review card, on every tab of the pipeline | Each table held for review on any target, with the proposed DDL and the decision buttons. It appears only while a review is open. |
| The pipeline's Schema tab | Schema changes: the DDL events detected on the pipeline's source, newest first, and finished reviews. |
| Schemas page | The schema events of all your pipelines. See Schema events. |
| Alerts | A pipeline that stops at a schema change raises Pipeline failed, then Pipeline failed: needs operator, because restarting can't get past the change. |
A held table raises no alert. After you change a source table, check the pipeline for a Schema review card.
Settings
Schema evolution has no settings. It follows what you set elsewhere:
- Names. Changes are applied under the target table and column names you set in Configure. See Table and column names.
- Types. An added column gets the same type as the source on a target of the same engine, and the mapped type on another engine.
- In-flight processing. A schema change on a table that a processing rule covers is always held, until you reload the pipeline. See Tables with in-flight processing rules.
Plan a schema change
- Change the source, not the target. SQL Flow applies a change only to a target table that has exactly the shape it expects. A target table you altered by hand, including one where you added the new column in advance, stops the pipeline at the change.
- Add columns that the stream can describe. A nullable column, or a
NOT NULLcolumn with a constant default, is applied automatically. A default such asNEWID()orGETDATE()gives each existing row its own value, which the change stream doesn't carry, so the pipeline stops. - Avoid renaming or dropping captured tables. Both stop the pipeline. To retire a table, remove it from the pipeline first. See Changing tables.
- Check the pipeline afterwards. Look for a Schema review card, and for the change on the Schema tab.
