Profiling a dataset and writing validation rules

In depth

Validation rules

A validation rule is a condition that a value, a row or a set of rows must satisfy, stated precisely enough for a computer to test. The European Statistical System’s handbook on data validation, published on Eurostat’s CROS portal, defines validation as checking whether a combination of values belongs to a set of acceptable combinations; each rule is a compact description of part of that set. Rules come from the data dictionary, from the business’s own logic and from the physical world: no stay lasts −2 nights.

Four of these kinds have names you will meet often. A range check tests that a value lies between a minimum and a maximum. A format check tests that text follows a pattern; patterns are usually written as regular expressions, which lesson 5 introduces. A cross-field check tests a relationship between values in the same row. Referential integrity holds when every reference from one table to another points to a row that exists: an order for customer C77 is broken if the customer table has no C77. The referring column, here customer_id in the orders table, is called a foreign key.

Set membership and referential integrity shade into each other. A short, fixed list, such as three regions, can be written into the rule itself. A list that changes, such as the product catalogue or the customer list, belongs in its own table, and the rule becomes a check that every value exists there.

Hard rules and soft rules

Some rules must hold for logical or physical reasons. Eurostat’s guidance calls them hard (or fatal) edits, and a value that fails one is an error. Others flag values that are possible but suspicious; these are soft (or query) edits, and a failure means “check this”, not “this is wrong”. For Lantern, quantity of at least 1 is a hard rule: an order of −1 packs can’t exist as an order. Quantity of at most 20 is a soft rule: most customers buy one to three packs, so 40 deserves a look, but cafés do buy in bulk. At Harborview, check-out after check-in is hard; a nightly rate above the list price of the most expensive room is soft.

Give every rule a severity and an action. Hard failures are errors to correct or exclude, with the decision recorded; soft failures go to someone who can check them against the source. Neither kind is a reason to delete a row on sight.

Writing good rules

• Testable. “Prices look sensible” is not a rule; “unit_price is a number greater than 0 and at most 100” is.
• One condition per rule, with a name (R1, R2 …), so that a failure report says exactly what failed and where.
• Explicit about blanks. Most rules quietly pass a missing value. In SQL a CHECK constraint is satisfied when its condition is unknown, which is what a comparison with a NULL gives, so CHECK (quantity >= 1) accepts a blank quantity. Pair each range or format rule with a completeness rule wherever the value is required.
• Tolerant where arithmetic is. A cross-field rule on money should allow for rounding: the absolute difference between order_total and quantity × unit_price − discount is at most $0.01.
• Sourced. Note where each rule comes from (the data dictionary, a manager, the laws of arithmetic) so that it can be challenged and updated when the business changes.

Measuring quality

Rules become measurements when you count their failures. A rule’s failure rate is the share of the rows it checks that fail it, and the share of rows failing at least one rule summarises a whole rule set. Report both. “R3 fails on 3 of 10 rows” tells the data owner where to look; a single score such as “the data is mostly clean” tells nobody what to fix. Here is a rule set for the extract, and what it finds:

R7 is checked only on the seven rated orders, because blank ratings are allowed. The hard rules record nine failures but only seven rows fail at least one of them, because 5006 and 5008 each fail two.

Two features of that table matter whenever you measure quality. First, the denominator is the rows a rule checks, not all rows: R7 does not apply to blank ratings, which are a matter for completeness. Second, failures can’t simply be added: nine hard failures fall on seven rows, so 70% of the extract fails at least one hard rule, not 90%. Uniqueness can be measured the same way: 9 distinct order_ids among 10 rows is 90%.

Completeness needs the dictionary too. The rating column is 70% filled, but because rating is optional, its blanks are not a defect. A required column with the same 70% would be a serious problem: for email, it would mean three orders whose receipts couldn’t be sent.

Summary statistics need definitions too

Two statistics in a profile can differ between tools. The standard deviation can divide by $n$ (the population formula) or by $n - 1$ (the sample formula). This course uses $n - 1$, as does pandas by default; NumPy’s std divides by $n$ unless told otherwise. Quartiles have several definitions. The site’s tools interpolate linearly between sorted values, like Excel’s QUARTILE.INC and NumPy’s default, which gives a lower quartile of 1 and an upper quartile of 2.75 for the extract’s quantities. A rule that places the upper quartile at position $0.75(n + 1)$ in the sorted list gives 3.25 instead. Neither is wrong; say which you used.

From profile to plan

The output of profiling and validation is not a cleaner dataset. It is an issue list: each problem, the evidence for it, the dimension it affects and a proposed action, with the lesson of this course that handles it. Only when the list is complete do you start changing data, and then in an order that avoids creating new problems: fix types and formats before range checks (a price stored as text can’t be compared with 0), and resolve duplicates before counting anything.

Notice what the list leaves out: the blank ratings. The dictionary allows them, so they are not a defect, although they will matter if you analyse ratings; lesson 6 asks whether customers who don’t rate differ from those who do.