Skip to main content

Detect and fix schema drift (zero-loss)

Partner files change. A new column appears in next week's CSV, nobody told you, and a naïve pipeline silently drops it. DataChord handles this two ways at once:

  • Zero-loss capture — every source column that isn't mapped (including a brand-new one) is swept into an _extra JSON column on your destination, so no data is ever lost, even on the very first run the column shows up.
  • Governed promotion — that same new column raises a drift event and a proposal you review. Approving it registers the column as UNMAPPED so you can map it to a first-class target field. You approve every change — the pipeline keeps running and losing nothing in the meantime.

By the end of this page you will have:

  • Pointed a CSV source at a watched folder and enabled drift detection.
  • Confirmed a brand-new column lands in _extra on the first run (zero loss).
  • Reviewed and approved a drift proposal, then promoted the new column.
  • Scheduled the whole loop to run unattended.
You'll need
  • A MapCraft project on the document lane with at least one saved mapping (open /flowbridge/{projectId} — create the mapping first if prompted).
  • A CSV source whose connector_type is file, pointing at a local/mounted path (e.g. /data/incoming/orders.csv). Object storage (S3/SFTP) is a separate connector and out of scope here.
  • A destination configured on the Deliver node — PostgreSQL, Snowflake, or BigQuery. _extra lands as JSONB (Postgres), VARIANT (Snowflake), or an autodetected STRUCT (BigQuery).

Step 1 — Enable drift detection​

Open FlowBridge → Drift for your project and click Enable drift detection.

This does two things in one recompile:

  1. Turns on the zero-loss _extra passthrough for the project's mapping(s).
  2. Adds a schema_drift health-check to the DAG, with its baseline expected_fields seeded automatically from your source schema — no hand-editing of DAG JSON.

The confirmation tells you how many baseline fields were captured and the new DAG version. (Equivalent API: POST /api/v1/drift/projects/{projectId}/enable.)

Step 2 — Run it and confirm zero loss​

Trigger a run (manually from the canvas, or wait for the schedule in Step 4). Drop a CSV that contains a column your mapping doesn't cover — say shipping_address — and let the pipeline run.

Open your destination table. The mapped columns are populated as usual, and the unmapped column is preserved inside _extra:

one delivered row
{
  "order_id": "1001",
  "total": "49.90",
  "_extra": { "shipping_address": "12 Park St, Kolkata" }
}

Nothing was dropped — and you haven't had to touch the mapping yet.

Step 3 — Review and approve the drift, then promote the column​

The same run that captured shipping_address records a drift event. Because this is an additive change (a new column, nothing removed), DataChord builds the proposal deterministically — no LLM — and leaves it PENDING_REVIEW.

Example drift event diagnostic
schema_drift diagnostic
{
  "missing_fields": [],
  "extra_fields": ["shipping_address"],
  "sample_size": 100
}

On FlowBridge → Drift, select the event and review the proposal card (rationale, confidence, and the patch diff). Click Approve. Approving:

  1. Advances the drift baseline so the alert clears and won't re-fire.
  2. Registers shipping_address in the source schema.
  3. Adds it as an UNMAPPED row in the MapCraft table.

Open MapCraft, map shipping_address to a target field, and save. On the next run it becomes a first-class column and drops out of _extra.

Breaking drift still self-heals

A removed or malformed field (missing columns, validator errors) is a breaking change — those keep flowing through the LLM self-healing fix-proposer, which proposes a DAG patch for you to review. Only additive drift uses the deterministic path above.

Step 4 — Schedule it​

Open FlowBridge → Schedule, add a cadence, and let the loop run unattended:

Cadence:      Cron
Expression:   0 2 * * *      (every day at 02:00)
Enabled:      ✔

Each scheduled run re-reads the CSV, sweeps any unmapped columns into _extra, and raises a drift event for anything new — so the data keeps landing and you stay in control of what gets promoted.

Verify​

  • A new column in the CSV appears inside _extra on the destination on the first run — before you map anything.
  • A schema_drift event with that column in extra_fields shows on the Drift page, with a PENDING_REVIEW proposal.
  • Approving the proposal adds an UNMAPPED row in MapCraft and clears the alert.
  • After you map the column, the next run promotes it to a real column.

What's next​