7 Aggregate Validations

Every method in the previous chapter checked each row in turn. Aggregate validations check a column as a whole. Instead of checking that each value is positive, they check that the column’s total, average, or standard deviation is where you expect it to be. The column is reduced to a single number, and that number is the one and only test unit for the step.

Going from many test units to one changes how you read the result. A per-row check tells you how many values failed, while an aggregate check can only tell you whether the summary value passed or failed. That makes aggregate checks suited to a different kind of question, one about the column overall rather than about individual entries. This chapter covers the three families (sums, averages, and standard deviations), the tol= argument used for reconciliation, and what it means in practice for a step to have exactly one test unit.

We use the game_revenue dataset throughout, mainly its item_revenue and session_duration columns. For reference, the total of item_revenue is about 8,687, the mean of session_duration is about 25.6, and the standard deviation of item_revenue is about 13.2.

game_revenue = pb.load_dataset(dataset="game_revenue", tbl_type="polars")

7.1 When an aggregate is the right check

An aggregate check fits when the property you care about belongs to the column rather than to any one row. The clearest case is reconciliation, where a total has to match a figure from somewhere else, such as a revenue sum that must agree with an accounting ledger. No per-row check can express this, because no single value is right or wrong on its own. Only the sum can be compared to the expected figure.

Aggregates are also useful for plausibility monitoring, where you know roughly what an average or a standard deviation should be and want to hear about it when that changes. If the average order value suddenly halves, that often points to an upstream problem, even when no individual row looks broken and every per-row check on the column still passes. Summary statistics are also cheap to compute, so a few aggregate checks add very little to the cost of running a plan.

7.2 Checking a column total

The col_sum_gt(), col_sum_lt(), col_sum_ge(), col_sum_le(), and col_sum_eq() methods compare a column’s sum against a value, with one method for each comparison operator. Here we check that total revenue is above a minimum of 5,000.

(
    pb.Validate(data=game_revenue)
    .col_sum_gt(columns="item_revenue", value=5000)
    .interrogate()
)

		STEP	COLUMNS	VALUES	TBL	EVAL	UNITS	PASS	FAIL	W	E	C	EXT
#4CA64C	1	col_sum_gt()	item_revenue	5000		✓	1	1 1.00	0 0.00	—	—	—	—

The report shows a single test unit, and it passes because total revenue across the 2,000 rows is about 8,687. A per-row check on the same column would have produced 2,000 test units, but the sum reduces the column to one number, so the step passes or fails as a whole. If we flip the check and require the total to be under 5,000, it fails.

(
    pb.Validate(data=game_revenue)
    .col_sum_lt(columns="item_revenue", value=5000)
    .interrogate()
)

		STEP	COLUMNS	VALUES	TBL	EVAL	UNITS	PASS	FAIL	W	E	C	EXT
#4CA64C66	1	col_sum_lt()	item_revenue	5000		✓	1	0 0.00	1 1.00	—	—	—	—

The most common use of a sum check, though, is reconciling against a known figure, and for that you use col_sum_eq() with its tol= argument. Suppose an accounting system reports total revenue of 8,687.12, rounded to the cent. The actual sum of the column is 8,687.11791, so an exact comparison fails even though the two figures agree for all practical purposes.

(
    pb.Validate(data=game_revenue)
    .col_sum_eq(columns="item_revenue", value=8687.12, tol=0)
    .interrogate()
)

		STEP	COLUMNS	VALUES	TBL	EVAL	UNITS	PASS	FAIL	W	E	C	EXT
#4CA64C66	1	col_sum_eq()	item_revenue	8687.12		✓	1	0 0.00	1 1.00	—	—	—	—

With tol=0 (the default) the check fails over a difference of about 0.002. Allowing a tolerance fixes that.

(
    pb.Validate(data=game_revenue)
    .col_sum_eq(columns="item_revenue", value=8687.12, tol=1)
    .interrogate()
)

		STEP	COLUMNS	VALUES	TBL	EVAL	UNITS	PASS	FAIL	W	E	C	EXT
#4CA64C	1	col_sum_eq()	item_revenue	8687.12 tol=1		✓	1	1 1.00	0 0.00	—	—	—	—

Now the check passes, because tol=1 accepts any sum within 1 of the target. You might expect to write tol=0.01 for “within one cent” here, but that is not how tol= works, as the callout below explains. With a large total like this one, tol=1 is the tightest tolerance you can set. Beyond absorbing rounding, the tolerance also sets how much real difference you will accept before treating the two totals as different.

How tol is actually interpreted

A tol value below 1 is treated as a proportion of the target, so tol=0.01 means 1 percent of 8,687.12, an allowance of about 86. A value of 1 or more is an absolute allowance in the column’s own units, so tol=20 accepts any sum within 20 of the target. In both cases the allowance is then truncated to a whole number. This means you cannot ask for “within one cent” of a large total. A tolerance is always either a percentage of the target or a whole number of units.

7.3 Checking an average

The col_avg_* methods work the same way for a column’s mean, again with one method per operator. A common pattern is to keep the average within a plausible range using two checks, one for the lower bound and one for the upper. The mean of session_duration is about 25.6, so a range of 20 to 30 should pass.

(
    pb.Validate(data=game_revenue)
    .col_avg_ge(columns="session_duration", value=20)
    .col_avg_le(columns="session_duration", value=30)
    .interrogate()
)

		STEP	COLUMNS	VALUES	TBL	EVAL	UNITS	PASS	FAIL	W	E	C	EXT
#4CA64C	1	col_avg_ge()	session_duration	20		✓	1	1 1.00	0 0.00	—	—	—	—
#4CA64C	2	col_avg_le()	session_duration	30		✓	1	1 1.00	0 0.00	—	—	—	—

Both steps pass, so the mean falls between 20 and 30. There is no “between” method for aggregates, so a range always takes two checks. One small benefit is that when the range is breached, you can see right away which side was crossed. To see a failure, we can require the average to be below 25.

(
    pb.Validate(data=game_revenue)
    .col_avg_lt(columns="session_duration", value=25)
    .interrogate()
)

		STEP	COLUMNS	VALUES	TBL	EVAL	UNITS	PASS	FAIL	W	E	C	EXT
#4CA64C66	1	col_avg_lt()	session_duration	25		✓	1	0 0.00	1 1.00	—	—	—	—

The single test unit fails, since the mean of about 25.6 is just above 25. In a monitoring setup, this is the signal you want: the average has crossed a line you chose on purpose. Every individual session duration could still look reasonable, which is why a per-row check would not catch this kind of shift.

7.4 Checking the spread

The col_sd_* methods compare a column’s standard deviation against a value, which lets you put limits on how spread out the data is. The usual form is an upper limit. The standard deviation of item_revenue is about 13.2, so it should stay under 20.

(
    pb.Validate(data=game_revenue)
    .col_sd_lt(columns="item_revenue", value=20)
    .interrogate()
)

		STEP	COLUMNS	VALUES	TBL	EVAL	UNITS	PASS	FAIL	W	E	C	EXT
#4CA64C	1	col_sd_lt()	item_revenue	20		✓	1	1 1.00	0 0.00	—	—	—	—

The check passes. A standard deviation check can catch problems that a sum or average check would miss. For instance, a batch of corrupted values in both directions can increase the spread a lot while leaving the mean about where it was. A lower limit is useful too. If the standard deviation drops toward zero, the column has probably become stuck on a single repeated value. Here we check that revenue still varies by more than 5.

(
    pb.Validate(data=game_revenue)
    .col_sd_gt(columns="item_revenue", value=5)
    .interrogate()
)

		STEP	COLUMNS	VALUES	TBL	EVAL	UNITS	PASS	FAIL	W	E	C	EXT
#4CA64C	1	col_sd_gt()	item_revenue	5		✓	1	1 1.00	0 0.00	—	—	—	—

This passes as well, since the standard deviation is about 13.2. If an upstream bug ever replaced the column with a constant, this check would fail, even though the total and the average might still look plausible.

7.5 How aggregate checks behave

Aggregate checks behave differently from the per-row checks of the previous chapter in a couple of ways that affect how you read their results. Each aggregate step has exactly one test unit, so it either passes completely or fails completely. That also changes how thresholds work. The failure fraction of a one-unit step can only be 0 or 1, so any threshold between those values acts like an on/off switch rather than a graded level of severity. Here is the failing average check from earlier, run with a warning threshold of 0.1.

(
    pb.Validate(data=game_revenue, thresholds=pb.Thresholds(warning=0.1))
    .col_avg_lt(columns="session_duration", value=25)
    .interrogate()
)

		STEP	COLUMNS	VALUES	TBL	EVAL	UNITS	PASS	FAIL	W	E	C	EXT
#AAAAAA	1	col_avg_lt()	session_duration	25		✓	1	0 0.00	1 1.00	●	—	—	—

The step fails with a failure fraction of 1, which is above the 0.1 warning threshold, so the report marks it at the warning level. Had the step passed, its failure fraction would be 0 and it would not reach any threshold. Chapter 13 covers thresholds in more detail.

The aggregate methods also take fewer arguments than the per-row methods. They accept columns=, value=, and tol=, along with the usual thresholds=, brief=, actions=, active=, and dimension=. They do not accept na_pass=, pre=, or segments=. So if you want a separate total for each group in a column rather than one total for the whole table, you need to validate each group as its own table. Segmentation, covered in Chapter 11, is only available for the per-row checks.

7.6 Bringing it together

A few aggregate checks together make a quick summary of a table’s overall condition. The plan below combines a reconciliation, an average range, and a limit on the standard deviation.

(
    pb.Validate(data=game_revenue, tbl_name="game_revenue", label="Aggregate controls")
    .col_sum_eq(columns="item_revenue", value=8687.12, tol=1)
    .col_avg_ge(columns="session_duration", value=20)
    .col_avg_le(columns="session_duration", value=30)
    .col_sd_lt(columns="item_revenue", value=20)
    .interrogate()
)

		STEP	COLUMNS	VALUES	EVAL	UNITS	PASS	W	E	C	EXT
#4CA64C	1	col_sum_eq()	item_revenue	8687.12 tol=1	✓	1	1 1.00	—	—	—	—
#4CA64C	2	col_avg_ge()	session_duration	20	✓	1	1 1.00	—	—	—	—
#4CA64C	3	col_avg_le()	session_duration	30	✓	1	1 1.00	—	—	—	—
#4CA64C	4	col_sd_lt()	item_revenue	20	✓	1	1 1.00	—	—	—	—

All four steps pass. The revenue total matches the ledger figure, the average session duration is between 20 and 30, and the standard deviation of revenue is under 20. If any of these changes on a future run, the matching step will fail, which tells you something upstream has changed before you start looking at individual rows. Because the checks are cheap and the report is short, this kind of section works well at the start of a larger plan.

7.7 Summary

Aggregate validations check a property of a whole column by reducing it to a single number, which becomes the step’s only test unit. The col_sum_* methods compare a column’s total against a value and are the natural choice for reconciliation, usually through col_sum_eq() with a tol= allowance. Keep in mind that a tol= below 1 is a proportion of the target, not a small absolute amount. The col_avg_* methods check the mean, most often as a range built from two checks, and the col_sd_* methods check the standard deviation, where an upper limit catches unusual spread and a lower limit catches a column that has become constant.

Because each aggregate step has one test unit, it passes or fails as a whole, and thresholds act as a simple switch. The aggregate methods do not take na_pass=, pre=, or segments=. The next chapter, Chapter 8, moves from column summaries to whole rows, with checks such as uniqueness and completeness that apply to an entire record.