How to detect data drift in recurring CSV files

A recurring file can pass the same validation rules every month and still become materially different. Comparing each delivery with a previous good run helps expose changes in volume, completeness, duplication, types and uniqueness before they turn into downstream failures.

1. Treat schema drift and data drift as different problems

Schema drift is structural: columns appear or disappear, names change, or inferred types move from number to string. Data drift can happen even when the schema is unchanged: row count drops, missing values increase, duplicates appear or an identifier becomes less unique. Both matter in recurring imports.

2. Start with a trusted baseline

Choose a run that represents a delivery you accepted. A baseline does not need to contain the raw file forever; a compact structural summary can be enough for later comparisons. Useful baseline signals include row count, completeness, duplicate count, inferred column types and missing/uniqueness rates.

3. Compare row counts in context

A smaller or larger file is not automatically wrong, but a sudden change deserves attention. Percentage change is more useful than an absolute threshold when file sizes vary. The right warning threshold depends on the feed: a 20% shift may be suspicious for a monthly master-data export but normal for a daily transaction file.

4. Watch completeness and missing-value rates

Overall completeness can show broad degradation, while per-column missing rates reveal where it happened. A file may still contain every expected column but become unsafe because a previously complete key, email or amount field is now sparse.

5. Track duplicates and uniqueness

Duplicate rows and declining uniqueness are different signals. Exact duplicate rows can indicate repeated extraction or file assembly errors. A lower uniqueness rate in a business key can expose repeated identifiers even when the duplicated rows are otherwise different.

6. Detect type and mixed-type changes

Columns often drift gradually. A numeric amount field may begin receiving text placeholders, or a date field may mix several formats. Compare inferred types and mixed-type rates between runs so a mostly valid column does not hide a growing formatting problem.

7. Separate configuration changes from data changes

If cleanup rules, mappings or contract settings changed between runs, the comparison needs that context. Otherwise you may attribute a difference to the source when it was actually caused by a workflow edit. A configuration fingerprint makes that distinction visible without storing the full configuration in run history.

8. Use drift as a review signal, not an automatic verdict

Drift detection should explain what changed and help decide whether the delivery is trustworthy. It should not treat every difference as a failure. Combine longitudinal signals with explicit data contracts and row-level validation rules when a change must block downstream output.

9. Keep the comparison privacy-minimized

For many recurring workflows, useful drift analysis does not require retaining source rows or cell values. Row counts, aggregate rates, inferred types and other structural metrics can stay in local browser history while the actual CSV or Excel file remains outside the history store.

For related workflow design, see how to build a repeatable CSV workflow, how to detect schema drift with a data contract, and how to handle recurring vendor CSV and Excel files.