4.5: Valid Ranges and Business Rules

Valid Ranges and Business Rules

Data values should not only be present and of correct type, but also valid in context. This means they should fall within reasonable or expected ranges, and adhere to any business rules or logic specific to the dataset’s domain. Cleaning data involves verifying these conditions and handling any values that violate them.

Range constraints: Many numerical or date fields have natural bounds. For example, a person’s age should be non-negative and perhaps below 130 (no verified human is older than that). If you have a dataset of survey respondents and one record has age = 999, that’s clearly outside the valid range and likely an error (perhaps a placeholder or a data entry issue). Similarly, percentages should typically lie between 0 and 100. Dates might have to be in the past or within a certain interval (e.g., a transaction date shouldn’t be in the future beyond perhaps time zone differences). Identifying out-of-range values is straightforward with filters or conditional queries: e.g., SELECT * FROM Survey WHERE age < 0 OR age > 120;. During data cleaning, such values need to be addressed: you might remove them if they are invalid, correct them if you can (maybe that 999 was meant to be 99), or set to NULL with a flag indicating an error.

Business rules: Beyond generic ranges, business rules are constraints derived from domain knowledge or requirements. These can be logical relationships between fields. For instance: - In an e-commerce dataset, OrderShipDate should never be before OrderDate – items can’t ship before they are ordered. If you find any records where OrderShipDate < OrderDate, that’s a violation to clean (it could indicate a timestamp error or swapped fields). - In a university database, a student’s GraduationDate should not come before their EnrollmentDate. - A sensor reading might have a rule that it must change gradually – if one record jumps by an impossible amount relative to the previous, it might be a glitch (business rule on continuity).

Business rules often require cross-field or cross-record checks. They might not be enforced in the raw data, so it falls to the cleaning process to validate them. Sometimes these rules can be implemented with CHECK constraints in SQL (a CHECK constraint can enforce a condition like CHECK(OrderShipDate >= OrderDate) on each row). If not enforced at insertion, then during cleaning you perform such checks manually. Any violations can be reported and corrected if possible.

Integrity constraints as business rules: Some business rules overlap with what we discussed as integrity constraints. For example, a foreign key constraint (discussed in the next section) is essentially a business rule saying “every reference in this field must exist as a primary key in the related table”. When those are broken, they violate referential integrity. Another common rule is cardinality constraints, like expecting a one-to-one or one-to-many relationship. For example, each invoice should correspond to exactly one payment. If you find an invoice with two payments recorded (one-to-many where it should be one-to-one), you have a data validity issue.

Example – dice roll range: An illustrative example from the lecture slides involved the roll of two dice. The sum of two six-sided dice can only range from 2 (1+1) to 12 (6+6). Any value outside that (like 1 or 13) is impossible and indicates invalid data. Moreover, not all values in 2–12 are equally likely; 7 is the most common outcome while 2 and 12 are the least. A dataset of many dice rolls should reflect that distribution. If the data shows a uniform distribution or weird spikes, it may indicate fabrication or errors. Figure 4.1 below shows the expected distribution of sums for two dice (out of 36 possible combinations). Any recorded sum outside 2–12 would be an obvious error, and even values within the range should roughly follow the frequency pattern shown.

Figure \(\PageIndex{1}\): The bar chart shows all 36 possible outcomes, with sums of 6, 7, and 8 being most frequent and sums of 2 and 12 being rarest, illustrating how expected distributions serve as validity checks. (CC BY 4.0; Felix Amoruwa) Access to a detailed description of Figure \(\PageIndex{1}\)

In real business data, the “range” might not be a physical law like dice, but there are still expectations. For example, if you have a field for employee_grade_level that should be 1–5, finding a 0 or 6 would violate the allowed range. A data cleaning step would be to scan for any values outside 1–5 and then decide how to treat them (correct if obvious, or exclude).

Handling invalid values: When a value violates a range or rule, there are a few options to handle it:
- Correction: If possible, figure out the correct value. Perhaps by cross-checking other data. For instance, if an entry says a person’s date of birth is 2023 instead of 1923, context (like other fields showing they are a centenarian) might allow correction to the plausible value. Correction is ideal but often one doesn’t have external info to do it confidently.
- Exclusion or Nullification: Treat the value as missing/invalid – e.g., set it to NULL or exclude that record from certain analyses. Marking it as NULL (with a note) acknowledges it as unknown rather than using a wrong value. Excluding the record entirely might be warranted if the error in one field makes the whole record unreliable and you cannot salvage it.
- Defaults or Binning: In some cases, if a value is just slightly out of bounds or not critical, you might default it into an “Other” or “Unknown” category. For example, if a survey response for age is 200 (clearly a mistake), you could lump it into an "Unknown age" category for analysis purposes after confirming it’s a small number of cases.

All such decisions should respect the analytical goals and be documented. If too many values are invalid, it may signal a bigger problem in data collection that needs to be addressed (or it may render the dataset not useful for certain analysis until fixed).

Business rule enforcement in SQL: SQL can help during cleaning by writing queries that specifically check each rule.

For example:

SELECT OrderID, OrderDate, ShipDate
FROM Orders
WHERE ShipDate < OrderDate;

Any rows returned violate the shipping rule. Similarly, use conditional aggregations to check summaries – e.g., ensure no customer has more than one loyalty account if that’s a rule. The cleaning process might involve writing a series of such data quality queries, reviewing their output, and then updating or annotating records that fail the checks. In an automated pipeline, these could become data validation tests that raise errors or warnings when business rules are broken (some teams integrate such tests using tools or custom scripts as part of ETL).

In summary, valid range checks and business rules are about making sure the data not only exists, but makes sense. By applying these rules during cleaning, we catch many subtle errors that simple type or null checks might miss. This ensures the dataset is not just technically sound, but also logically consistent with the real-world phenomena it represents.