orders = pl.DataFrame({
"order_id": [1, 2, 3, 4, 5],
"quantity": [2, 1, 3, 5, 4],
"unit_price": [10.0, 25.0, 5.0, 8.0, 12.0],
"amount": [20.0, 25.0, 15.0, 45.0, 48.0],
"status": ["shipped", "shipped", "cancelled", "shipped", "pending"],
})12 Advanced and Custom Validation
The built-in methods from the previous chapters cover the checks that come up most often, but many real rules are too specific for any of them. For example, revenue might need to equal quantity times unit price, or a cancelled order might need to have an amount of zero. This chapter covers the methods that let you write that logic yourself while still working inside a validation plan, so the results appear in the same report as everything else.
There are three such methods, each more flexible than the last. The col_vals_expr() method takes a single expression, conjointly() combines several conditions into one rule, and specially() takes any function you write. We also cover the pre= argument, which transforms the data before a check runs, and the col_vals_in_table() method for checking keys against another table. A small orders table gives each example something to check, and one of its rows is deliberately wrong.
Order 4 is the planted error. Its amount is 45, but five units at 8 each should come to 40. Each method below catches this in its own way.
12.1 Native expressions with col_vals_expr
The col_vals_expr() method checks rows against an expression written for your data’s backend, which for a Polars table means a Polars expression. The expression should give True or False for each row, and rows where it is True pass. This is the most direct way to check a relationship between columns, like the arithmetic that connects amount to quantity and unit_price.
(
pb.Validate(data=orders)
.col_vals_expr(expr=pl.col("amount") == pl.col("quantity") * pl.col("unit_price"))
.interrogate()
)Four of the five rows pass and one fails. The failure is order 4, where the amount is not quantity times price. Because the expression is evaluated for each row, this works like the column checks of Chapter 6, with one test unit per row. The expression is handed directly to the backend, so it runs efficiently, and the method works with Pandas or a database table as long as you write the expression in that backend’s syntax. (The exact == comparison is fine here because these prices multiply out exactly. For computed decimals, see the floating-point note in Chapter 6.)
An expression can also apply a rule only to certain rows. Say a cancelled order must have an amount of zero, and orders with any other status can have any amount. As an expression, that becomes: a row passes if it is not cancelled, or if its amount is zero.
(
pb.Validate(data=orders)
.col_vals_expr(expr=(pl.col("status") != "cancelled") | (pl.col("amount") == 0))
.interrogate()
)This time order 3 is the only failure. It is cancelled but still has an amount of 15. The other four orders pass automatically, since the rule does not apply to them. Writing a conditional rule as “the condition does not apply, or the requirement holds” is a common pattern, and it lets one expression handle a rule that none of the built-in methods could. If a single expression can capture your rule, col_vals_expr() is usually the simplest choice.
12.2 Multi-condition rules with conjointly
Some rules are really several conditions that must all be true at once. You could write each condition as its own step, but then you get a separate count for each condition and no count of the rows that break the rule as a whole. The conjointly() method combines the conditions, and a row passes only if every condition is true for it. Each condition is a function that returns an expression.
(
pb.Validate(data=orders)
.conjointly(
lambda df: pl.col("amount") == pl.col("quantity") * pl.col("unit_price"),
lambda df: pl.col("amount") > 0,
lambda df: pl.col("quantity") > 0,
)
.interrogate()
)There is one test unit per row, and four pass while order 4 fails. Order 4 breaks the first condition, even though it meets the other two. This matches how you would read a business rule like “a valid order has a correct, positive amount and a positive quantity”. To decide between conjointly() and separate steps, ask whether the conditions make up one rule or several independent ones. If they are parts of a single definition of a valid row, use conjointly(). If they are separate concerns and you want to know which one failed, use separate steps. Most plans use separate steps most of the time.
12.3 Arbitrary logic with specially
When a rule cannot be written as an expression, the specially() method takes a function that receives the table and returns the result. What the function returns decides how the result is counted. If it returns a table with a single boolean column, each row is a test unit. This is the most common form. Here we check that amount divided by quantity gives the unit price.
def per_unit_price_holds(data):
return data.select((pl.col("amount") / pl.col("quantity")) == pl.col("unit_price"))
(
pb.Validate(data=orders)
.specially(expr=per_unit_price_holds)
.interrogate()
)| STEP | COLUMNS | VALUES | TBL | EVAL | UNITS | PASS | FAIL | W | E | C | EXT | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| #4CA64C66 | 1 |
specially()
|
✓ | 5 | 4 0.80 |
1 0.20 |
— | — | — | — |
Four rows pass and order 4 fails, since 45 divided by 5 is 9, not the stated unit price of 8. The function can also return a single True or False, in which case the whole table is one test unit. That suits a check about the table as a whole.
def table_is_not_empty(data):
return data.height > 0
(
pb.Validate(data=orders)
.specially(expr=table_is_not_empty)
.interrogate()
)| STEP | COLUMNS | VALUES | TBL | EVAL | UNITS | PASS | FAIL | W | E | C | EXT | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| #4CA64C | 1 |
specially()
|
✓ | 1 | 1 1.00 |
0 0.00 |
— | — | — | — |
This gives a single test unit, which passes because the table has rows. A third option is to return a plain Python list of booleans, in which case each item in the list is a test unit.
Since specially() runs any function you give it, you can use a function factory: an outer function that takes a setting and returns a validation function that uses it. This turns a one-off check into a reusable rule. The factory below takes a tolerance and returns a function that checks the amount against quantity times price within that tolerance.
def amount_matches_within(tol):
def check(data):
return data.select(
(pl.col("amount") - pl.col("quantity") * pl.col("unit_price")).abs() <= tol
)
return check
(
pb.Validate(data=orders)
.specially(expr=amount_matches_within(tol=0))
.interrogate()
)| STEP | COLUMNS | VALUES | TBL | EVAL | UNITS | PASS | FAIL | W | E | C | EXT | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| #4CA64C66 | 1 |
specially()
|
✓ | 5 | 4 0.80 |
1 0.20 |
— | — | — | — |
With a tolerance of 0 the check is exact, and order 4 fails just as it did before. The difference is that the rule now takes a setting. Order 4 is off by 5, so amount_matches_within(tol=5) or any larger tolerance would let all five rows pass. One definition now covers a range of related checks, so you do not need several nearly identical functions that differ only in a number. The downside of specially() is that you have to write and maintain the code yourself, so it is best kept for rules the other methods cannot express.
12.4 Preprocessing with pre
Sometimes the data needs to be transformed before a check makes sense. The pre= argument takes a function that receives the data and returns a modified version, and the check runs on that modified version. The original table is not changed. A common use is checking a derived value, such as confirming that a column falls between 0 and 1 after it has been normalized.
small_table = pb.load_dataset(dataset="small_table", tbl_type="polars")
def normalize_d(df):
return df.with_columns((pl.col("d") / pl.col("d").max()).alias("d"))
(
pb.Validate(data=small_table)
.col_vals_between(columns="d", left=0, right=1, pre=normalize_d)
.interrogate()
)| STEP | COLUMNS | VALUES | TBL | EVAL | UNITS | PASS | FAIL | W | E | C | EXT | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| #4CA64C | 1 |
col_vals_between()
|
✓ | 13 | 13 1.00 |
0 0.00 |
— | — | — | — |
All 13 rows pass, because dividing by the maximum scales d into the range 0 to 1 before the check sees it. The change only applies to this step, so small_table itself still has its original d values, which run from 108.34 to 9,999.99. A pre= function can also filter rows. For example, pre=lambda df: df.filter(pl.col("f") == "high") would run the check on just the six rows where f is "high", and the report would show six test units instead of 13. The pre= argument is available on most column, row, and custom checks, and it is a clean way to check transformed data without storing the transformation.
12.5 Referential integrity
A common requirement when tables are related is referential integrity: every value in one table’s key column must exist in another table. For example, every customer_id on an order should match a real customer. The col_vals_in_table() method checks this. You give it the column to check, the reference table, and the column in the reference table to look in.
customers = pl.DataFrame({"customer_id": [1, 2, 3]})
customer_orders = pl.DataFrame({
"order_id": [10, 11, 12, 13],
"customer_id": [1, 2, 3, 99],
})
(
pb.Validate(data=customer_orders)
.col_vals_in_table(
columns="customer_id",
ref_table=customers,
ref_column="customer_id",
)
.interrogate()
)Three of the four orders pass and one fails. The failure is the order for customer 99, who is not in the customers table. An orphaned key like this would otherwise make an inner join drop the row without any warning. The method also handles composite keys (pass a list of columns to both columns= and ref_column=), and the reference table can come from a different backend than the table being checked, such as a Polars DataFrame checked against a DuckDB table. For a small reference table that is already in memory, col_vals_in_set(set=customers["customer_id"].to_list()) gives the same result, and a few of the later playbooks use that simpler form.
WarningCustom logic does not serialize
The custom checks in this chapter have one limitation to know about before you rely on them in a plan you want to share or store as text. Their logic lives in Python functions and expressions, which cannot be written out as text. When you export a plan with to_code() or to_yaml(), any col_vals_expr(), conjointly(), or specially() step is written with a placeholder expression, any pre= function or step actions= is dropped, a callable active= setting is replaced with True, and a col_vals_in_table() step is left out entirely. Pointblank warns you when this happens. This matters for the YAML workflows of Chapter 20 and the AI-assisted authoring of Chapter 18, both of which use that text form. Plans made only of the other built-in checks export cleanly.
12.6 Summary
When a rule does not fit the built-in methods, three methods let you write it yourself. The col_vals_expr() method checks rows against a single expression and is the simplest choice for a relationship between columns. The conjointly() method combines several conditions into one rule and passes a row only when all of them hold. The specially() method takes any function, and what the function returns decides the test units, from one per row to one for the whole table.
The pre= argument transforms or filters the data for a single step without changing the original table. The col_vals_in_table() method checks that every key in one table exists in another. Keep in mind that these custom pieces do not fully survive being exported to code or YAML. This chapter finishes the tour of validation methods in this part of the book. The next part moves from writing checks to acting on their results, starting with Chapter 13, which covers classifying failures by severity and responding to them automatically.