Detect Schema Drift Between Monthly Excel Files

The monthly export arrives, the report script runs, and nothing fails — but a column called "Net amount" became "Net Amount (EUR)" and the script quietly filled it with blanks, a new region "Benelux" appeared and fell outside every filter, and the quantity column now contains text because someone typed "10 pcs". This is schema drift: the structure or content of a recurring file changes without warning. Each change on its own is small; together they are the most common reason automated reports become silently wrong. The defence is cheap: describe each file's schema in a small snapshot — columns, types, blank rates, categories, ranges — compare it with the previous month's, and stop with a clear report when something important changed. This page builds that check with pandas. It belongs to Validating Excel Data with Python.

Prerequisites

pip install pandas openpyxl

The exports/ folder holds the August and September 2026 order exports from the same system. September's file has drifted in several ways at once — the kind of month every report owner eventually has. The task is to find out exactly how, automatically, before the September report is produced.

Step 1 — Take a schema snapshot

A snapshot is a small dictionary that describes a file well enough to compare months: each column's position, type, share of blanks, and either its distinct values (for categories) or its range (for numbers and dates):

import json
import pandas as pd

def snapshot(path, max_categories=20):
    df = pd.read_excel(path)
    cols = {}
    for pos, col in enumerate(df.columns):
        s = df[col]
        info = {"position": pos, "dtype": str(s.dtype), "blank_share": round(float(s.isna().mean()), 4)}
        numeric = pd.to_numeric(s, errors="coerce")
        info["non_numeric"] = int((numeric.isna() & s.notna()).sum()) if s.dtype == object else 0
        if pd.api.types.is_numeric_dtype(s) or info["non_numeric"] < len(s) * 0.5 and s.dtype == object and numeric.notna().any():
            info["min"], info["max"] = float(numeric.min()), float(numeric.max())
        elif pd.api.types.is_datetime64_any_dtype(s):
            info["min"], info["max"] = str(s.min().date()), str(s.max().date())
        elif s.nunique() <= max_categories:
            info["categories"] = sorted(map(str, s.dropna().unique()))
        cols[col] = info
    return {"file": str(path), "rows": len(df), "columns": cols}

aug = snapshot("exports/orders_2026-08.xlsx")
sep = snapshot("exports/orders_2026-09.xlsx")
print(json.dumps(aug["columns"]["Region"], indent=1))
print(json.dumps(sep["columns"]["Qty"], indent=1))

The snapshot records what matters for a report: Region's four categories and no blanks in August; Qty's type, range and — in September — two values that are not numbers. Categories are captured only for columns with few distinct values, so an ID column does not produce a huge list. Saving each month's snapshot as JSON (json.dump(sep, open(...))) gives a history of how the export has evolved, and means the comparison only needs the new file plus last month's small JSON, not last month's whole workbook.

Step 2 — Compare the column sets

The first comparison is structural: columns removed, added, or moved. Removed and added columns in the same month are often one renamed column, so suggest likely pairs by name similarity:

from difflib import SequenceMatcher

old_cols, new_cols = list(aug["columns"]), list(sep["columns"])
removed = [c for c in old_cols if c not in new_cols]
added = [c for c in new_cols if c not in old_cols]
renames = []
for r in removed:
    best = max(added, key=lambda a: SequenceMatcher(None, r.lower(), a.lower()).ratio(), default=None)
    if best and SequenceMatcher(None, r.lower(), best.lower()).ratio() >= 0.6:
        renames.append((r, best, round(SequenceMatcher(None, r.lower(), best.lower()).ratio(), 2)))
moved = [c for c in old_cols if c in new_cols and aug["columns"][c]["position"] != sep["columns"][c]["position"]]
print("removed:", removed, "| added:", added)
print("probable renames:", renames)
print("moved:", moved)

"Discount %" was removed and "Channel" added, and "Net amount" is gone while "Net Amount (EUR)" appeared — SequenceMatcher scores that pair highly and suggests it as a rename, which is exactly what happened. A suggestion is not a decision: a person confirms the rename, and the report's column mapping is updated. Columns that merely moved — here Qty, pushed one place right by the new Channel column — matter only to code that reads columns by position, which is a good reason never to do that.

Step 3 — Compare types, blanks, categories and ranges

For columns present in both months, compare each property of the snapshot and describe every change in words:

def column_drift(old, new, col):
    issues = []
    o, n = old["columns"][col], new["columns"][col]
    if o["dtype"] != n["dtype"]:
        issues.append(f"type {o['dtype']} → {n['dtype']}")
    if n.get("non_numeric", 0) > o.get("non_numeric", 0):
        issues.append(f"{n['non_numeric']} non-numeric value(s)")
    if n["blank_share"] - o["blank_share"] > 0.05:
        issues.append(f"blanks {o['blank_share']:.0%} → {n['blank_share']:.0%}")
    if "categories" in o and "categories" in n:
        new_vals = sorted(set(n["categories"]) - set(o["categories"]))
        gone = sorted(set(o["categories"]) - set(n["categories"]))
        if new_vals: issues.append(f"new values {new_vals}")
        if gone: issues.append(f"missing values {gone}")
    if "min" in o and "min" in n and isinstance(o["min"], float):
        span = (o["max"] - o["min"]) or 1
        if abs(n["max"] - o["max"]) > 0.5 * span or abs(n["min"] - o["min"]) > 0.5 * span:
            issues.append(f"range {o['min']:g}–{o['max']:g} → {n['min']:g}–{n['max']:g}")
    return issues

common = [c for c in old_cols if c in new_cols] + [(r, a) for r, a, _ in renames]
drift = {}
for c in common:
    old_name, new_name = (c, c) if isinstance(c, str) else c
    sep_view = {"columns": {old_name: sep["columns"][new_name]}}
    issues = column_drift(aug, sep_view, old_name)
    if issues:
        drift[new_name] = issues
for col, issues in drift.items():
    print(f"{col}: " + "; ".join(issues))

Region has a new value, "Benelux", and its blank share jumped from 0% to 15% — both reasons a regional report would quietly go wrong. Qty contains two non-numeric values, the "10 pcs" and "approx 4" that would break any sum, and its type changed from integer to object as a result. The renamed amount column is compared under its old name, so its type and range are checked too. Thresholds — a 5-point rise in blanks, a range moving by half its previous span — are deliberately simple; tune them to how much each column normally varies. The comparisons stay explainable, which matters when the output is a message to the team that owns the export.

Step 4 — A drift report and a decision

Gather everything into one report, classify each finding as blocking or informational, and stop the pipeline when anything blocking is present:

REQUIRED = {"Order ID", "Order date", "Region", "Qty", "Net amount"}
findings = []
for c in removed:
    renamed_to = next((a for r, a, _ in renames if r == c), None)
    level = "warn" if renamed_to else ("block" if c in REQUIRED else "warn")
    findings.append((level, c, f"removed" + (f" — probably renamed to '{renamed_to}'" if renamed_to else "")))
for c in added:
    if c not in [a for _, a, _ in renames]:
        findings.append(("info", c, "new column"))
for col, issues in drift.items():
    for i in issues:
        findings.append(("block" if "non-numeric" in i or "type" in i else "warn", col, i))

report = pd.DataFrame(findings, columns=["Level", "Column", "Change"]).sort_values("Level")
print(report.to_string(index=False))
blocking = report[report["Level"] == "block"]
print("RESULT:", "stop — fix or confirm before running the report" if len(blocking) else "proceed")
report.to_excel("drift_report_2026-09.xlsx", index=False)

The report lists every change with a level. Non-numeric quantities are blocking, because sums would fail or be wrong; the rename is a warning, because the mapping must be confirmed but the data is there; a new column is information. Here the result is "stop": the two text quantities must be fixed or explicitly handled before the September report runs. Writing the report to a workbook — or sending it to the export's owners — turns a silent break into a conversation on the day the file arrives. Converting confirmed changes into an updated reading contract is covered in Validate Excel Columns Before Import with pandas.

Making it routine

Run the snapshot and comparison as the first step of every scheduled report, store each month's snapshot alongside the file, and keep the thresholds and required columns in a configuration file rather than in code. Over a few months the snapshots also become documentation — when did the Channel column appear? when did blanks in Region start? — that nobody had to write by hand. For row-level rules on top of these file-level checks, schema libraries such as pandera are a good fit; see Validate Excel Data with pandera Schemas.

Common pitfalls

• Checking only that the script ran. Drift rarely raises an error; compare structure and content.
• Reading columns by position. A new column shifts everything; read by name.
• Treating every change as fatal. Classify findings so harmless additions do not block reports.
• Accepting rename suggestions automatically. Confirm them; similar names can be different fields.
• Keeping no history. Save each month's snapshot to compare and to explain.

Conclusion

Schema drift — renamed, added or removed columns, type changes, new category values, rising blanks — is the quiet way recurring Excel reports go wrong. Describe each file in a small snapshot of columns, types, blank rates, categories and ranges; compare it with the previous month's to find structural changes, suggest renames by name similarity, and list per-column drift in plain words; then classify the findings and stop the pipeline when anything blocking appears. Save the snapshots, and the check doubles as a history of how the export has changed.

Frequently asked questions

What is schema drift in Excel files? Changes in the structure or content of a recurring file, such as renamed or removed columns, new columns, type changes, new category values or more blanks, that can break or silently distort reports built on it.

How do I compare the columns of two Excel files in Python? Read both headers with pandas, take the set differences to find added and removed columns, and compare positions for columns present in both.

How can I detect a renamed column? When one column disappears and another appears in the same month, compare their names with difflib.SequenceMatcher and suggest pairs with a high similarity for a person to confirm.

Should the report fail when the file changes? Only for blocking changes, such as missing required columns or non-numeric values in numeric columns. Treat other changes as warnings or information.

Related

• Up one level: Validating Excel Data with Python — checking data before it is used.
• Check Excel Data Types with pandas — types within one file.
• Profile an Excel Dataset with pandas — a fuller one-off description.
• Fix Schema and Type Errors When Reading Excel with Polars — drift meeting a strict reader.