How to Set Plausible Data Ranges Without Hiding Errors

A numeric column can have the correct data type and still contain values that make no sense. A negative salary, a percentage above 100 or an order dated before a company existed may pass basic parsing while quietly distorting totals, charts and models. Plausible range checks catch these problems, but poorly chosen limits can be just as dangerous: they may erase legitimate edge cases or turn assumptions into hidden cleaning rules.

The goal is not to force every value toward the average. It is to separate impossible values from unusual ones, document the reasoning and choose a response that preserves evidence.

A valid number is not necessarily a valid value

Data type validation answers a narrow question: can this value be represented as a number? Range validation asks whether the number is acceptable in its business context.

Consider an employee dataset with these records:

| employee_id | age | annual_salary | utilization_pct | |---|---:|---:|---:| | E101 | 34 | 62000 | 84 | | E102 | -3 | 58000 | 77 | | E103 | 71 | 920000 | 103 | | E104 | 19 | 0 | 0 |

The negative age is impossible under an ordinary employee definition. A utilization value of 103 may be invalid if the field is strictly capped at 100, but it could be legitimate if overtime is represented relative to standard capacity. A salary of zero may indicate unpaid leave, an intern, a missing-value substitution or an error. The high salary is unusual, yet it may be accurate.

None of these decisions can be made from distribution statistics alone. They require the field definition, units, population and business process.

Separate hard limits from review thresholds

A useful range policy has at least two levels.

Hard limits identify values that cannot be accepted under the field definition. Examples include a negative quantity for inventory on hand when back orders are stored separately, a probability below zero or a calendar month above 12.

Review thresholds identify values that are possible but deserve attention. A very large transaction, an unusually long delivery time or a salary far above the typical band may belong here.

This distinction prevents an important mistake: treating statistical rarity as proof of invalidity. An outlier is a value that differs markedly from the rest of a distribution. A data error is a value that violates a known requirement or was produced incorrectly. A record can be both, either or neither.

For example, a transaction of 500,000 may be a genuine enterprise order in a table dominated by small purchases. Automatically capping it at a percentile would change a valid observation. A transaction of -500,000 might also be valid if refunds use negative amounts. The column’s name and distribution are not enough; the accounting convention matters.

Build range rules from evidence

Use a deliberate process instead of choosing convenient round numbers.

1. Confirm the meaning and unit

Write down what the field measures, the unit used and the population covered. A temperature limit is meaningless without knowing whether the values use Celsius or Fahrenheit. Likewise, a ratio stored from 0 to 1 requires different limits from a percentage stored from 0 to 100.

Check whether units vary between source systems. A sudden cluster of values around 0.75 in a percentage column may represent a unit mismatch rather than low performance.

2. Identify authoritative constraints

Look for constraints in source-system forms, database definitions, regulatory guidance or documented operating policies. These are stronger evidence than assumptions based on a sample.

Some constraints are universal for the field, while others depend on category. A discount may be limited to 30% for one product class and 60% for another. In that case, one global maximum is too blunt.

3. Examine historical values without copying them blindly

Historical minimums and maximums help reveal the observed range, but they do not automatically define the allowed range. Previous data can contain errors, and a new valid case may fall outside the old sample.

Use historical distributions to generate questions. Investigate extreme records, source changes and unit shifts before converting observations into rules.

4. Define boundary behavior precisely

Specify whether each limit is inclusive. “Age must be above 18” differs from “age must be at least 18.” Also define how null values are handled. A null may be permitted for optional data even when non-null values must fall within a range.

A clear rule might read: “For active employees, contracted_hours must be non-null and between 1 and 60 inclusive.” This is testable and exposes its scope.

5. Test rules against representative edge cases

Create examples at, below and above each boundary. Include nulls, decimal values, negative zero where relevant, and records from special categories. Testing the rule against known edge cases is safer than discovering its interpretation during a production run.

6. Assign an action separately from detection

Detection and correction should not be the same decision. A failed check might trigger a warning, quarantine a row, request source confirmation or block a downstream process. Automatic replacement is appropriate only when the intended correction is well supported.

Choose responses that preserve information

Once a violation is detected, avoid silently clipping every value to the nearest boundary. Changing 135% to 100% may produce a plausible-looking result while concealing whether the source used the wrong unit, allowed overtime or contained a transcription error.

Safer responses include:

• Flagging: Keep the original value and add the issue to a review queue.
• Quarantining: Exclude the record from a specific analysis while retaining it for investigation.
• Converting: Apply a documented unit conversion when the source unit can be established reliably.
• Setting to missing: Use this only when the value is known to be invalid and no defensible replacement exists.
• Correcting from an authority: Replace the value when a trusted source provides the intended value.
• Rejecting the dataset: Stop processing when a critical constraint fails and downstream results would be unsafe.

Record the original value, rule name, action and reason whenever data is changed. This creates a path for review and helps distinguish source defects from deliberate preparation choices.

Make range checks contextual and reproducible

Ranges often depend on other columns or on time. A shipment date cannot precede its order date. A contract end date may be null for an active agreement but required for a terminated one. A child ticket price may have a different allowed range from an adult price.

These are conditional rules rather than simple column minimums and maximums. Document the condition explicitly instead of forcing all records through one global threshold.

Rules can also change. Keep them in version-controlled configuration or code, give each rule a stable name and record why it exists. When a policy changes, update the rule deliberately and note the effective date. Do not rewrite old results without preserving which rule version produced them.

A reproducible range check should answer four questions:

1. What field and population does the rule cover?
2. Which values pass or fail, including boundaries and nulls?
3. What happens when the rule fails?
4. Where did the limit come from?

Turn suspicious values into an actionable review

Start with one consequential numeric field rather than attempting to govern every column at once. Confirm its definition and unit, define separate hard and review limits, test the boundaries, and select a non-destructive response for failures. Then run the check on a representative dataset and inspect the flagged records with someone who understands the source process.

The strongest range policy does not merely make a dataset look tidy. It prevents impossible values from passing unnoticed while protecting rare, valid observations from automatic deletion. Treat the rule as a documented decision, not a guess embedded in a cleaning script.