How to Validate Cross-Field Rules Before Analysis

A dataset can pass every column-level check and still be logically impossible. An order date may be valid on its own, as may a shipping date, yet the record is suspect when shipping appears to happen first. These contradictions are easy to miss because they live between fields rather than inside one field. Cross-field validation turns that hidden business logic into explicit, reviewable checks.

Why column-by-column validation is not enough

Basic validation usually asks whether each value has the expected type, format or range. Those checks are necessary, but they do not establish whether related values make sense together.

Consider an order table containing these columns:

• ordered_at
• shipped_at
• status
• subtotal
• discount
• tax
• total

A parser may confirm that the two date columns contain dates and the financial columns contain numbers. It will not automatically know that a shipped order normally needs a shipping date, that shipping should not precede ordering, or that the total should reconcile with the component amounts.

Common cross-field rules include:

• An end date must be on or after a start date.
• A closed case should normally have a closure date.
• A cancellation date should be empty for an active subscription.
• A discount must not exceed the eligible subtotal unless a documented exception applies.
• A state or province code must be compatible with the selected country.
• A child record should not predate its parent record.
• A percentage and its numerator and denominator should agree within an accepted tolerance.

These are business invariants: conditions expected to remain true when the data represents reality correctly. Some are absolute. Others need exceptions, tolerances or an “unknown” outcome.

Write each rule as a precise statement

A vague instruction such as “check the dates” is difficult to implement and almost impossible to audit. Rewrite it as a statement with a defined scope, condition and response.

For example:

For records where both ordered_at and shipped_at are present, shipped_at must be on or after ordered_at. Violations require review and must not be corrected automatically.

That formulation answers four important questions:

1. Which records are eligible for the check?
2. What relationship is expected?
3. What counts as a violation?
4. What should happen after a violation is found?

Do the same for status-dependent rules. Instead of saying “closed cases need dates,” specify whether the rule applies to closed, resolved and cancelled statuses. Decide whether a missing closure date is an error, a warning or an accepted state while an upstream process catches up.

A useful rule register can contain:

| Field | Purpose | |---|---| | Rule ID | Stable name used in reports and discussions | | Scope | Rows to which the rule applies | | Expression | Expected relationship between fields | | Severity | Error, warning or informational finding | | Exception | Documented cases in which the rule does not apply | | Owner | Person or team able to resolve the issue | | Action | Review, quarantine, correct at source or accept |

Stable rule IDs help analysts compare findings across deliveries without relying on changing prose.

Separate invalid values from unknown outcomes

Cross-field checks often depend on complete inputs. Missing information should not automatically be treated as proof that a relationship failed.

Suppose shipped_at is blank. If the status is processing, the blank may be expected. If the status is shipped, it may be a quality issue. If the status is also blank, the rule may be unable to reach a conclusion.

It is often better to classify results into three states:

• Pass: the required values are available and satisfy the rule.
• Fail: the required values are available and contradict the rule.
• Unknown: missing, unparseable or ambiguous inputs prevent evaluation.

This distinction stops missing values from being mixed with genuine contradictions. It also reveals when a rule cannot evaluate a large portion of the dataset because prerequisite fields are incomplete.

Parsing failures deserve their own attention. If an invalid date becomes a missing value during conversion, retain the original text or a parse-status field. Otherwise, the cross-field report may describe the record only as unknown and conceal the underlying parsing problem.

Implement a reviewable date-order check in pandas

The following example keeps raw date values, parses working copies and extracts both contradictions and unresolved rows:

1import pandas as pd23orders = pd.read_csv("orders.csv")45orders["ordered_at_raw"] = orders["ordered_at"]6orders["shipped_at_raw"] = orders["shipped_at"]78orders["ordered_at"] = pd.to_datetime(orders["ordered_at"], errors="coerce")9orders["shipped_at"] = pd.to_datetime(orders["shipped_at"], errors="coerce")1011eligible = orders["ordered_at"].notna() & orders["shipped_at"].notna()12failed = eligible & (orders["shipped_at"] < orders["ordered_at"])1314violations = orders.loc[failed].copy()15unknown = orders.loc[~eligible].copy()

This code does not “fix” a reversed date pair. That restraint matters. The dates could be transposed, entered in different formats, sourced from systems using different time zones, or genuinely attached to the wrong order. Automatically swapping them would choose an explanation without evidence.

Before trusting the result, confirm that the parser used the intended date convention and time-zone assumptions. A comparison can be technically correct but operationally misleading when one timestamp is local and another is UTC.

Handle totals and percentages with tolerances

Arithmetic relationships present a different problem. Exact equality is often inappropriate when values have been rounded independently.

For an order total, the expected relationship might be:

subtotal - discount + tax = total

The rule should state:

• Whether shipping fees or credits are included.
• Whether tax is calculated before or after discounts.
• Which currency applies.
• The accepted rounding tolerance.
• How null discounts are interpreted.

Do not choose a tolerance merely to make warnings disappear. Base it on the system’s precision and accounting policy. A one-cent difference may be expected after line-level rounding, while the same tolerance would be unsuitable for measurements stored to several decimal places.

Percentages need similar care. If a completion rate is stored alongside completed and eligible counts, compare the reported rate with the recomputed rate only when the denominator is valid. Zero, missing and negative denominators should follow explicit rules rather than being allowed to produce misleading ratios.

Review failures before changing data

A failed cross-field rule identifies a contradiction, not its cause. Review a sample with enough context to distinguish among:

• Source entry errors.
• Parsing or locale mistakes.
• Delayed updates between systems.
• Incorrect joins that combined unrelated records.
• Legitimate exceptions missing from the rule.
• Incorrect business assumptions in the rule itself.

Preserve the original row identifier and include the relevant source fields in the exception output. A useful review file contains the rule ID, failure reason, original values, source reference and eventual disposition.

Avoid dropping failed records by default. Exclusion may make an analysis run, but it can bias the result if failures concentrate in a particular team, period, product or customer group. Quarantine questionable rows, measure their distribution and decide whether the analysis can proceed without them.

Make cross-field checks part of the workflow

Start with rules that protect the intended analysis. A time-to-resolution report needs ordered timestamps and compatible statuses. Revenue reporting needs reconciled amounts and currencies. Cohort analysis needs event dates that do not precede the entity’s creation.

For each critical rule:

1. Write the expected relationship in plain English.
2. Define eligible, failed and unknown states.
3. Preserve raw values used by parsers.
4. Assign severity and ownership.
5. Export exceptions with stable row and rule identifiers.
6. Review whether failures cluster in one source or period.
7. Record accepted exceptions rather than silently weakening the rule.
8. Run the same check on future deliveries.

The practical goal is not a dataset with no warnings. It is a dataset whose contradictions are visible, explainable and handled consistently. Begin with three to five relationships that could materially change your analysis, document them precisely, and turn their exception lists into a repeatable review step.