Detecting Columns That Changed Type Between CSV Deliveries

The April load failed with ERROR: invalid input syntax for type integer: "1.5", CONTEXT: COPY o, line 2, column qty. The fix looks obvious: a quantity of 1.5 got into an integer column, so ask the sender what a half unit means, or widen the column. And then the next run fails on a date, and the one after on a total with a comma, and by the third rerun nobody trusts the file. The failure that stopped the load was the first of six changes between the March and April deliveries, and a database reports the first. This article builds the check that reports all of them before the load, and shows that comparing inferred column types, the obvious approach, misses two of the six.

The two deliveries

The first rows of each file, side by side:

head -n 4 work/val/drift/orders-2025-03.csv
echo
head -n 4 work/val/drift/orders-2025-04.csv

order_id,zip,qty,amount,shipped_on,express,note
10001,33101,9,3428.17,2025-03-15,true,gift
10002,33101,3,381.02,2025-03-05,true,call first
10003,02134,7,3859.21,2025-03-20,true,call first

order_id,zip,qty,amount,shipped_on,carrier,express,note
10001,90210,1.5,"2,767.13",18/04/2025,UPS,1,call first
10002,10001,6,1836.03,15/04/2025,USPS,0,
10003,60601,8,566.04,22/04/2025,DHL,1,leave at door

March is the baseline. In April, compared with March:

1. qty has values like 1.5 in 2% of rows, where March had whole numbers.

2. zip has lost its leading zeros. March had values like 02134 (see order 10003); in April no zip starts with a zero, so the same area is written 2134.

3. amount has grouped numbers like "2,767.13", quoted, in about 4% of rows.

4. shipped_on changed from 2025-03-15 to 18/04/2025, day first with slashes.

5. express changed from true/false to 1/0.

6. A new column, carrier, appears in the middle, and one order_id has a letter prefix (A10778).

None of them is exotic. Each is what happens when a spreadsheet, a different export setting or a new version of the source system is introduced into the middle of a pipeline.

Comparing inferred types

The first idea is to let a reader infer the types for each file and compare them. With pandas read_csv:

python - <<'PY'
import pandas as pd
a = pd.read_csv('work/val/drift/orders-2025-03.csv'); b = pd.read_csv('work/val/drift/orders-2025-04.csv')
print(pd.DataFrame({'march': a.dtypes.astype(str), 'april': b.dtypes.astype(str)}).to_string())
print()
print('zip in March:', a.zip.head(3).tolist(), ' zip in April:', b.zip.head(3).tolist())
PY

              march    april
amount      float64      str
carrier         NaN      str
express        bool    int64
note            str      str
order_id      int64      str
qty           int64  float64
shipped_on      str      str
zip           int64    int64

zip in March: [33101, 33101, 2134]  zip in April: [90210, 10001, 60601]

The dtype table shows amount going from float64 to str, express from bool to int64, order_id from int64 to str and qty from int64 to float64, and carrier appearing. Four of the six drifts are visible. But zip is int64 in both columns, so the comparison reports nothing, and the printed values show why: in March zip was already read as an integer, so 02134 had become 2134 before any comparison happened. The loss of the leading zero is not drift between the files. It is damage done by the reader to both. shipped_on is str in both, which hides the format change.

DuckDB's sniffer is more careful about leading zeros:

duckdb -box -c "SELECT column_name, column_type FROM (DESCRIBE SELECT * FROM read_csv('work/val/drift/orders-2025-03.csv'))"
duckdb -box -c "SELECT column_name, column_type FROM (DESCRIBE SELECT * FROM read_csv('work/val/drift/orders-2025-04.csv'))"

┌─────────────┬─────────────┐
│ column_name │ column_type │
├─────────────┼─────────────┤
│ order_id    │ BIGINT      │
│ zip         │ VARCHAR     │
│ qty         │ BIGINT      │
│ amount      │ DOUBLE      │
│ shipped_on  │ DATE        │
│ express     │ BOOLEAN     │
│ note        │ VARCHAR     │
└─────────────┴─────────────┘
┌─────────────┬─────────────┐
│ column_name │ column_type │
├─────────────┼─────────────┤
│ order_id    │ VARCHAR     │
│ zip         │ BIGINT      │
│ qty         │ DOUBLE      │
│ amount      │ VARCHAR     │
│ shipped_on  │ DATE        │
│ carrier     │ VARCHAR     │
│ express     │ BIGINT      │
│ note        │ VARCHAR     │
└─────────────┴─────────────┘

March has zip as VARCHAR, presumably because the leading zeros kept it from being a number, and April has zip as BIGINT, since the zeros were gone. A real change, detected. But shipped_on is DATE in both months. DuckDB recognized 18/04/2025 as a date and adapted, with the type unchanged and the text format different. If the downstream code assumed ISO, nothing in the type comparison says it is wrong. Its type for order_id went from BIGINT to VARCHAR, and express from BOOLEAN to BIGINT.

So comparing types finds some drift and misses two kinds: a change that makes a column less informative (zeros lost by reading both files the same way), and a change that the reader absorbed (a new date format). Types are a summary of what the reader chose to do. To see what the file does, look at the values.

Profile by kinds of value

The script classifies every value into a kind, using a few full-match patterns, and counts the kinds per column:

"""Profile each column by the kinds of value it holds, then compare two deliveries."""
import collections, csv, re, sys

CLASSES = [
    ("empty", r""),
    ("int_leading_zero", r"0\d+"),
    ("int", r"-?\d+"),
    ("decimal", r"-?\d+\.\d+"),
    ("grouped_number", r"-?\d{1,3}(,\d{3})+(\.\d+)?"),
    ("iso_date", r"\d{4}-\d{2}-\d{2}"),
    ("slash_date", r"\d{1,2}/\d{1,2}/\d{4}"),
    ("bool_word", r"true|false"),
    ("text", r".*"),
]


def classify(v):
    for name, pat in CLASSES:
        if re.fullmatch(pat, v):
            return name


def profile(path):
    with open(path, newline="") as f:
        r = csv.reader(f)
        header = next(r)
        counts = {c: collections.Counter() for c in header}
        rows = 0
        for row in r:
            rows += 1
            for c, v in zip(header, row):
                counts[c][classify(v)] += 1
    return header, rows, counts


def dominant(counter):
    # empty cells say nothing about what kind of column this is
    non_empty = {k: v for k, v in counter.items() if k != "empty"}
    return max(non_empty, key=non_empty.get) if non_empty else "empty"


def compare(a_path, b_path, tolerance=0.001):
    ha, na, pa = profile(a_path)
    hb, nb, pb = profile(b_path)
    print(f"rows {na} -> {nb}")
    if set(ha) != set(hb):
        print(f"columns added {sorted(set(hb) - set(ha))}, removed {sorted(set(ha) - set(hb))}")
    if [c for c in ha if c in hb] != [c for c in hb if c in ha]:
        print("shared columns are in a different order")
    for c in ha:
        if c not in hb:
            continue
        da, db = dominant(pa[c]), dominant(pb[c])
        shares_a = {k: v / na for k, v in pa[c].items()}
        shares_b = {k: v / nb for k, v in pb[c].items()}
        new = {k: round(v, 4) for k, v in shares_b.items() if v > tolerance and shares_a.get(k, 0) <= tolerance and k != da}
        gone = {k: round(v, 4) for k, v in shares_a.items() if v > tolerance and shares_b.get(k, 0) <= tolerance and k != da}
        if da != db:
            print(f"  {c:11} dominant kind changed: {da} -> {db}")
        elif new or gone:
            print(f"  {c:11} still {db}; new kinds: {new or 'none'}; kinds that disappeared: {gone or 'none'}")
        else:
            print(f"  {c:11} same ({da})")


if __name__ == "__main__":
    compare(sys.argv[1], sys.argv[2], float(sys.argv[3]) if len(sys.argv) > 3 else 0.001)

The kinds are an ordered list, so a value gets the first that matches: empty, an integer with a leading zero, a plain integer, a decimal, a number with thousands separators, an ISO date, a slash date, a true/false word, and text as the catch-all. A column is summarized by its dominant kind, and the comparison reports three things per column: the dominant kind changed, a kind appeared that was not there before, or a kind disappeared. The thresholds matter: a kind counts as present if it covers more than the tolerance fraction of rows, 0.1% by default.

On the two files:

python lab/val/drift.py work/val/drift/orders-2025-03.csv work/val/drift/orders-2025-04.csv

rows 5000 -> 5000
columns added ['carrier'], removed []
  order_id    same (int)
  zip         still int; new kinds: none; kinds that disappeared: {'int_leading_zero': 0.4948}
  qty         still int; new kinds: {'decimal': 0.02}; kinds that disappeared: none
  amount      still decimal; new kinds: {'grouped_number': 0.0394}; kinds that disappeared: none
  shipped_on  dominant kind changed: iso_date -> slash_date
  express     dominant kind changed: bool_word -> int
  note        same (text)

It reports the added column, and then column by column:

• zip: still integer-like, but the kind int_leading_zero, which was 49.5% of March, disappeared. That is the lost zeros, found by the values and not by the type.

• qty: new kind decimal at 2% of rows.

• amount: new kind grouped_number at 3.94%.

• shipped_on: dominant kind changed from iso_date to slash_date.

• express: dominant kind changed from bool_word to int.

• order_id and note: same.

Five of six findings, in one pass, each with a number. The sixth is the stray A10778, one row in 5,000, which is 0.02% and below the default tolerance. Setting the tolerance to zero reports it:

python lab/val/drift.py work/val/drift/orders-2025-03.csv work/val/drift/orders-2025-04.csv 0

rows 5000 -> 5000
columns added ['carrier'], removed []
  order_id    still int; new kinds: {'text': 0.0002}; kinds that disappeared: none
  zip         still int; new kinds: none; kinds that disappeared: {'int_leading_zero': 0.4948}
  qty         still int; new kinds: {'decimal': 0.02}; kinds that disappeared: none
  amount      still decimal; new kinds: {'grouped_number': 0.0394}; kinds that disappeared: none
  shipped_on  dominant kind changed: iso_date -> slash_date
  express     dominant kind changed: bool_word -> int
  note        same (text)

order_id now shows new kinds: {'text': 0.0002}. That is the trade-off in any tolerance. At 0, every single odd value is a finding, which is correct for a column that feeds a load (one A10778 will fail an integer column) and noisy for a free-text column. At 0.1%, the report is calmer and a lone odd value gets through. Pick the tolerance per column from what a single bad value would cost. For keys and numbers, zero. For notes, a percent or two.

To check the check, the same script compares March with itself and every column reads same. A comparison tool that is not tested against identical inputs is the one most likely to cry wolf.

What the database would have said

A database reports the first value that fails for its column type. The April load:

psql -v ON_ERROR_STOP=1 -q <<'SQL' 2>&1
DROP TABLE IF EXISTS o;
CREATE TABLE o (order_id int, zip text, qty int, amount numeric, shipped_on date, carrier text, express boolean, note text);
\copy o FROM 'work/val/drift/orders-2025-04.csv' WITH (FORMAT csv, HEADER)
SQL
echo "exit status: $?"

ERROR:  invalid input syntax for type integer: "1.5"
CONTEXT:  COPY o, line 2, column qty: "1.5"
exit status: 3

It stopped at line 2 on qty = 1.5. If that were fixed, the next failures would be amount ("2,767.13" for a numeric column) and the date (18/04/2025), both on the same line, and then order_id on line 779, where A10778 is. Meanwhile, some drifts would load without a murmur. PostgreSQL's boolean type accepts 1 and 0, so express would go in without complaint, and zip goes into a text column with its zeros missing. The date is the dangerous one, because it depends on a setting:

psql -At -c "SHOW datestyle"
psql -At -c "SELECT '03/04/2025'::date"
psql -At -c "SELECT '15/04/2025'::date" 2>&1 | head -n 2
psql -At -c "SET datestyle = 'ISO, DMY'; SELECT '03/04/2025'::date, '15/04/2025'::date"

ISO, MDY
2025-03-04
ERROR:  date/time field value out of range: "15/04/2025"
LINE 1: SELECT '15/04/2025'::date
SET
2025-04-03|2025-04-15

With the default DateStyle of ISO, MDY, '03/04/2025'::date is 4 March, and '15/04/2025' fails because there is no month 15. Under ISO, DMY the same strings are 3 April and 15 April. In an April file of day-first dates, the days from 13 to 31 produce an error and the days 1 to 12 load as a different date. A load that fails halfway on a date is the good outcome. The bad outcome is a file whose first rows are all days of 12 or below, which loads clean. The PostgreSQL manual documents the input rules, and the date checking article has the strict version of the test.

What to do when a column drifts

The check gives you a list. The response is a decision for each item, and the order of decisions is usually the same.

1. Stop the load. A drifted file is not the file the contract describes. Do not let the loader be the one to find out.

2. Send the report to the provider, with the columns, the kinds and the shares. "Your April file has day-first dates in shipped_on" is something a person can act on in an hour.

3. Decide whether the change is intentional. If it is, update the contract and the schema, with the date, and adjust the cleaning step. If not, the provider fixes the export.

4. Keep the profile of every accepted delivery and compare the next one with the last accepted, not with a fixed baseline from a year ago. Drift is gradual.

5. Read sensitive columns as text, always, so that identifiers like zip never go through a number first. The pandas dtype article shows the one-line fix.

How to tell it worked

Compare a file with itself and expect all same. Compare two real deliveries you know differ in one way (rename a column, change one date format, add a letter to one id) and expect exactly that finding. Add the check to the load job as a step that must pass before the loader starts, and make it write its output to the job log, so that when it fails someone can read the reason without running anything.

Drift that keeps the same type

All of this finds changes in the form of a value. A change in its meaning looks identical. If amount switches from dollars to euros, or from dollars to cents, it is still a decimal, with the same share of grouped numbers. If status swaps the meaning of 1 and 2, every profile says same. If shipped_on goes from local time to UTC, the dates are valid, ISO and shifted. A profile of kinds can be extended with numeric summaries, the minimum, maximum and median for numbers and the distinct count for categories, which will show a column whose scale changed by 100. For the rest, you need the provider to tell you, which is a reason to put a change notice requirement in the contract, and a reason to compare totals with an independent source on every delivery.