A source column changed from integer to free text overnight — how should the landing load react?

## What actually happened upstream A column that held integers now holds free text. In practice this is almost always one of three things: someone started writing a sentinel like `"N/A"` or `"unknown"` into a field that had no null convention; the application changed the column's storage type in a migration; or an upstream export stopped formatting the value and began passing through whatever the user typed. The three have very different implications, and the ingestion response has to survive all of them without losing evidence — because at 3am you do not yet know which one it is. ## The direction of a type change matters more than the change **Widening** moves a column to a type whose domain contains the old one: `int` to `bigint`, `bigint` to `decimal`, any of them to text. Every value that existed still fits. Widening is information-preserving and therefore always safe to apply at the landing boundary. **Narrowing** moves the other way. Text to integer, timestamp to date, decimal to integer. Some existing values no longer fit, so the operation either errors or truncates. Narrowing is information-destroying and is never a safe automatic response. An integer becoming free text is a widening from the source's side, so the correct landing response is to widen too. The uncomfortable feeling — "but my column should be a number" — is a statement about the *published* model, not about the landing zone. ## Land wide, type on read The pattern that handles this cleanly is a two-step: **Landing.** Store the value as text, exactly as it arrived, alongside the batch identifier and load timestamp. The load never fails on a value; the raw evidence is preserved verbatim. **Publish.** Project a typed column out of the text with a safe cast, and split the rows that do not convert: ```sql select order_id, try_cast(amount_raw as decimal(18,2)) as amount, amount_raw from landing.orders where try_cast(amount_raw as decimal(18,2)) is not null; ``` and the complement of that predicate goes to a reject location with the raw string attached. Now the numeric column downstream is genuinely numeric, the bad values are visible and countable, and nothing was thrown away. The exact spelling of the safe-cast function differs by engine — `TRY_CAST`, `SAFE_CAST`, `toInt64OrNull` and friends — so name the concept, not one vendor's function, unless you are sure which engine you are on. ## The two failure modes to name in an interview **Coercing silently.** `coalesce(cast(x as int), 0)` is the single worst response. It succeeds, it looks tidy, and it produces sums that are quietly wrong. Any conversion that cannot happen must produce a row somewhere a human will see, not a default. **Filtering the bad rows away.** Dropping unparseable rows from the load is only marginally better: totals are now wrong by omission, and the evidence is gone. Rejected rows belong in a reject location with enough context to reprocess them, not in the bit bucket. ## Do not narrow back A source that emits `"N/A"` occasionally will produce batches that look entirely numeric. If your evolution logic infers the type per batch, it will try to narrow the landing column back to integer, which fails against already-stored text or rewrites the whole column. Type inference at landing must be **monotonic**: widen when required, never narrow, and record the widening in the schema-change log so a human can decide whether the published model should change. ## When the retype is permanent and semantic Sometimes the column really has changed meaning — an identifier that used to be numeric is now alphanumeric because the source added a new region prefix. No cast can rescue that: the published column's type must change, every downstream join on it must be checked, and the change belongs in a deliberate model revision rather than in an ingestion workaround. The signal that you are in this case rather than the sentinel case is that the non-numeric values are structured and increasing in share, not sporadic. ## Detection, so you find out on the first batch Widening at landing means the pipeline does not break, which is good for availability and bad for attention. Pair it with alerting so nobody discovers this a month later: - Alert on the schema diff itself when the observed source type changes. - Alert on cast-failure rate crossing a threshold at the publish step — zero is the normal value, so any non-zero rate is a signal. - Track the reject table's row count per batch as a first-class metric. A pipeline that absorbs the change *and* raises its hand is the correct behaviour. One that absorbs it silently has just deferred the incident.