Column profiles and domain constraints before analysis

Last updated: 5 Oct 20265 min read

tutorial

A column profile measures observed shape and violations; it does not decide what a value means without a domain contract.

Profile the right snapshot

For delivery records, count rows, unique shipment IDs, missing arrival times, negative durations and records outside the analysis window. Profile by source and ingestion day so one broken feed is not hidden by a clean majority. Save the source snapshot and code revision. A reproducible snapshot lets a reviewer regenerate the same counts after the live tables change.

State validity rules

A nonnegative duration is a range constraint, while “arrived after dispatch” compares two fields. A unique shipment ID is an identity rule, not a distribution statistic. Treat a missing arrival time as unknown or censored according to the business event, never as zero minutes. Missingness policy should be set before summary calculations.

Separate observation from intervention

The profile may flag a 930-minute delivery, but it cannot by itself prove a data error. The shipment could have been held overnight. Send impossible records to quarantine, plausible extremes to investigation and routine records to analysis. Record each disposition and the rule version. Outlier triage needs evidence beyond a percentile.

Check the denominator

If 1,500 shipments were dispatched and 47 have no arrival time, a mean duration over 1,453 completed shipments describes completed shipments only. Report that coverage beside the mean. If the dashboard silently drops the 47, a process delay can appear as an improvement. Keep counts for eligible, observed, missing, invalid and excluded records in every output packet.

Implementation

def profile_shipments(shipments):
    shipment_ids = set()
    report = {"rows": 0, "duplicate_ids": 0, "missing_arrival": 0, "invalid_order": 0}
    for shipment in shipments:
        report["rows"] += 1
        shipment_id = shipment["shipment_id"]
        report["duplicate_ids"] += shipment_id in shipment_ids
        shipment_ids.add(shipment_id)
        arrival = shipment["arrival_minute"]
        report["missing_arrival"] += arrival is None
        report["invalid_order"] += arrival is not None and arrival < shipment["dispatch_minute"]
    return report

Performance and operating cost

A single pass over N rows takes O(N) expected time and O(U) space for U unique IDs. Exact distinct counting can be memory-heavy; partitioned or approximate counts are alternatives only when the contract permits them.

Common Mistakes

Do not replace a missing event time with zero.
Do not call every extreme value invalid.
Do not show a cleaned statistic without eligible and excluded counts.