Schema drift | DLH.io Documentation

How the agent handles new columns, dropped columns and column type changes between SQL Server and the Delta Lake or Apache Iceberg target.

Source schemas change: columns are added, widened or retyped, and a batch of changed rows may not look like the table the agent created months ago. Two mechanisms cover this for delta and iceberg output.

Change in SQL Server	Mechanism	Default behaviour
New column added	Schema evolution (always on)	Column is appended to the target table with NULLs for older rows.
Column dropped	Schema evolution	Column stays in the target with NULLs for new rows. It is never dropped.
Column type widened compatibly (int to bigint, decimal(9,2) to decimal(18,2), anything to string)	Drift detection: safe cast	Batch is aligned to the target type silently.
Column type changed incompatibly (numeric to string, string to boolean, decimal narrower than a new value)	Drift detection: incompatible	Governed by `on_schema_drift_action` and `on_schema_drift_action_handling`.
Batch where every row has NULL in a column	Type alignment	Column is cast to the existing target type; no drift is reported.

Drift detection compares the in-memory batch against the target table's schema. It never scans the SQL Server catalog, so it costs nothing on large databases with thousands of tables.

Settings

data_retrieval:
  on_schema_drift_action: migrate_target
  on_schema_drift_action_handling: fail_once
  databases:
    - name: SalesDb
      tables:
        - name: dbo.LegacyImport
          all_columns: true
          on_schema_drift_action: coerce_to_target
          on_schema_drift_action_handling: do_schema_drift_action

Both settings live under data_retrieval and can be overridden per table.

`on_schema_drift_action`: what to do

Value	Effect
`fail` (default)	The table's write fails with a descriptive error that names each drifted column with its source and target type. Other tables continue.
`coerce_to_target`	Cast the batch to the existing target types and write. The target schema does not change. Empty strings become NULL when cast to numeric types; values that cannot be cast fail the table.
`migrate_target`	Rewrite the target table so the drifted columns adopt the new type, then write the batch. No re-extraction from SQL Server is needed. For decimals the new type is the exact `decimal(p,s)` declared in SQL Server.

`on_schema_drift_action_handling`: when to do it

Value	Effect
`fail_once` (default)	The first run that detects incompatible drift fails that table with the descriptive error and records it in the local state database. On the next run the agent looks up the declared column types for only that table in SQL Server, applies the action, clears the record and continues.
`do_schema_drift_action`	Apply the action immediately in the run that detects the drift.
`do_nothing`	No proactive handling. If the underlying write fails on a type mismatch, the error is wrapped with the schema diff so the failing column is named, and the table is retried on the next run.

fail_once is the default because it makes drift visible (one failed run, one alert) before the target table is changed, while still self-healing on the following run without manual intervention.

What a drift error looks like

Error extracting SalesDb.dbo.Location: Schema drift detected for
.../delta/SalesDb.dbo.Location: [GeocodeValidated: source=string target=bool (incompatible)]
- on_schema_drift_action_handling=fail_once: failing this run; the drift is recorded
and the next run will apply on_schema_drift_action=migrate_target for this table only

The message names the column, both types and what will happen next. It replaces the raw engine errors such as Failed to coerce then (Utf8, ...) and else (None) or ... is too large to store in a Decimal128 of precision 9.

What happens on a fail_once cycle

Run N: drift detected

The batch for dbo.Location arrives with a column typed differently from the Delta or Iceberg table. The table is marked FAILED in the run summary, its CT/CDC pointer is not advanced (so no changes are lost), the drift is stored in the state database and a failure notification is sent if notifications are enabled.

Run N+1: targeted resolution

The agent sees the pending drift for dbo.Location, queries sys.columns for that single table, resolves the authoritative type and applies the configured action (migrate_target rewrites the column; coerce_to_target casts the batch). The run summary shows the table as OK and the pending drift count in the state store block drops to 0.

Subsequent runs

Normal incremental processing. The CT/CDC changes accumulated during run N are applied in run N+1 because the pointer was preserved.

Pending drift is visible in the DLH.io AGENT STATE STORE (DuckDB) block printed at the start of every run and in --diagnose.

Schema refresh from SQL Server

Drift detection covers the target side. schema_refresh_mode on each database entry controls how often the agent re-reads column metadata from SQL Server for the source side, which is what picks up new columns so they can be extracted at all:

Value	Behaviour
`always`	Refresh source metadata every run. Simplest, fine for small databases.
`never`	Use the cached schema from the state database. Fastest; new columns are not noticed until you change the setting or run a historical load.
`table_changed`	Refresh only tables whose `sys.tables.modify_date` changed since the last refresh. Recommended for large databases.
`1H`, `12H`, `1D`, `2W`, `1M`	Refresh when the elapsed time since the last refresh exceeds the interval.

Recommendations

Production default: on_schema_drift_action: migrate_target with fail_once and notifications enabled. You get one alert per drift event and automatic remediation on the next run.
Regulated environments where the target schema must not change without review: keep fail, and change to migrate_target on the specific table once approved.
Tables fed by loosely typed imports (spreadsheets, EDI): coerce_to_target with do_schema_drift_action keeps the target stable and quietly normalizes stray values.
Downstream engines: after a migrate_target on Snowflake Iceberg tables run ALTER ICEBERG TABLE ... REFRESH; Delta readers pick up the new schema from the transaction log.
Mind Change Tracking retention. A table that stays failed longer than the CT retention period (often 2 days) needs a full reload of that table to be consistent; fail_once normally resolves in one scheduling interval so this only matters if runs are not happening.

Page Contents