game_revenue = pb.load_dataset(dataset="game_revenue", tbl_type="polars")
small_table = pb.load_dataset(dataset="small_table", tbl_type="polars")8 Row and Completeness Validations
Column checks ask whether a value is acceptable. Row checks ask whether a whole row is acceptable, and some problems only show up when you look at rows. A row can have a valid value in every column and still be wrong, either because it duplicates another row or because a field it needs is empty. To catch those problems, the row has to be the unit of validation rather than the individual cell.
This chapter covers the two row-level checks that come up most often: distinctness and completeness. Most of the chapter is about the decisions each one involves. For distinctness, that means choosing which columns make up the key. For completeness, it means deciding which fields are required and which are optional. Both checks produce one test unit per row, so their reports read like the per-row column checks of Chapter 6 and tell you how many rows are affected. Some other row-level checks, like rules that tie several columns together or judgments made by an AI model, have their own chapters, and we point to those at the end.
We use game_revenue for realistic scale and the smaller small_table when a short result makes the behavior easier to see.
8.1 Row distinctness
Duplicate rows cause trouble that is easy to miss. They inflate counts and averages, and they can make later steps process the same record twice. They also usually point to a problem earlier in the pipeline, such as a deduplication step that failed or a join that matched more rows than expected. The rows_distinct() method finds duplicates, and most of the work in using it is deciding what “duplicate” should mean.
8.1.1 Checking whole-row uniqueness
With no arguments, rows_distinct() compares entire rows and checks that no two are identical across every column. The small_table dataset has one exact duplicate, which the check finds.
(
pb.Validate(data=small_table)
.rows_distinct()
.interrogate()
)Eleven of the 13 rows pass and two fail. The two failures are the identical pair (both dated 2016-01-20 04:30:00). Notice that both copies fail, not just the second one. Pointblank has no way of knowing which copy is the original, and counting every copy gives you the full size of the problem. A larger table can easily pass this whole-row check, as game_revenue does.
(
pb.Validate(data=game_revenue)
.rows_distinct()
.interrogate()
)| STEP | COLUMNS | VALUES | TBL | EVAL | UNITS | PASS | FAIL | W | E | C | EXT | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| #4CA64C | 1 |
rows_distinct()
|
✓ | 2000 | 2000 1.00 |
0 0.00 |
— | — | — | — |
All 2,000 rows are distinct across the full set of columns, so the check passes. That does not mean the table has no duplicates, though. Exact whole-row duplicates are the less common problem, and the more useful check is on a key.
8.1.2 Choosing the key
Usually you want uniqueness on a key, meaning a column or combination of columns that identifies a record, and you want to ignore the other columns. The columns_subset= argument does this. The choice of columns decides what the check means, and the wrong choice gives a result that is correct but not useful. Using session_id alone as the key shows what that looks like.
(
pb.Validate(data=game_revenue)
.rows_distinct(columns_subset=["session_id"])
.interrogate()
)Only 96 rows pass and 1,904 fail. That looks bad until you remember what the table holds. A session usually has several events, so session_id is supposed to repeat. The table has 618 sessions, and the 96 passing rows are the sessions that happen to have only one event. The check is answering exactly the question we asked. We just asked the wrong question, because session_id identifies a session, not a row.
A better key describes what should actually be unique. If a session should not log two events at exactly the same moment, then the session and the timestamp together form the key, so we add time.
(
pb.Validate(data=game_revenue)
.rows_distinct(columns_subset=["session_id", "time"])
.interrogate()
)Now 1,978 rows pass and 22 fail. These 22 rows are 11 pairs, where each pair shares a session and a timestamp. Whether they are real duplicates depends on the data, and we can narrow it down by adding more columns to the key. Adding item_name separates two different items logged in the same session at the same second.
(
pb.Validate(data=game_revenue)
.rows_distinct(columns_subset=["session_id", "time", "item_name"])
.interrogate()
)With item_name in the key, only four rows fail. So 9 of the 11 pairs were two different items at the same second, which is plausible. The remaining two pairs are more interesting. Each one has the same session, time, and item (an ad_5sec impression), and the rows differ only in item_revenue (0.08 versus 0.11 in one pair, and 0.006 versus 0.024 in the other). That is why the whole-row check passed. These look like the same ad event recorded twice with different revenue values, which is worth asking the data provider about. Adding columns to the key one at a time and watching the failure count drop is a good way to find the key that matches what your data model treats as a single record.
NoteChoosing the right key comes up again and again
Deciding which columns identify a record is something you will do repeatedly in this book. The columns_subset= of rows_complete() names the fields a record cannot do without, and a segmentation key names the groups you want results split by. In each case the question is the same: which columns does this rule apply to? Getting that right is often more of the work than choosing the method.
8.2 Row completeness
A null in a required field can break a calculation, drop rows from a join, or skew an aggregate. The rows_complete() method checks completeness row by row, asking whether each row has everything it needs. That is different from col_vals_not_null() in Chapter 6, which checks one column at a time. Use rows_complete() when what matters is whether the record as a whole is usable.
8.2.1 Checking whole-row completeness
With no arguments, rows_complete() requires every column in a row to have a value and fails any row with a null anywhere. Column c of small_table has two missing values, so those two rows fail.
(
pb.Validate(data=small_table)
.rows_complete()
.interrogate()
)Eleven rows pass and two fail, and the failures are the rows where c is null. This whole-row form is right for tables that really must have complete records, such as a dimension table, where any gap is a defect. Note that it looks for actual nulls, not placeholder codes like -99 (those are covered in Chapter 10). In most tables, though, some columns are optional, and requiring all of them to be filled in would flag rows that are fine.
8.2.2 Completeness on required fields
The columns_subset= argument limits the check to the columns that must have values, so the optional columns can contain nulls. If only columns a, b, and d of small_table are required, and c is optional, every row passes.
(
pb.Validate(data=small_table)
.rows_complete(columns_subset=["a", "b", "d"])
.interrogate()
)| STEP | COLUMNS | VALUES | TBL | EVAL | UNITS | PASS | FAIL | W | E | C | EXT | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| #4CA64C | 1 |
rows_complete()
|
✓ | 13 | 13 1.00 |
0 0.00 |
— | — | — | — |
All 13 rows pass, because both nulls are in column c, which we left out. This is how you write down which fields a record needs and which it can do without. Running the same check on the main identifier columns of game_revenue passes for all 2,000 rows.
(
pb.Validate(data=game_revenue)
.rows_complete(columns_subset=["player_id", "session_id", "session_start"])
.interrogate()
)| STEP | COLUMNS | VALUES | TBL | EVAL | UNITS | PASS | FAIL | W | E | C | EXT | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| #4CA64C | 1 |
rows_complete()
|
✓ | 2000 | 2000 1.00 |
0 0.00 |
— | — | — | — |
In fact, game_revenue has no nulls in any column, so the whole-row form would pass too. Listing the required columns is still worthwhile, since it keeps the check meaningful if optional columns with nulls are added to the table later.
8.3 Combining distinctness and completeness
The two checks work well together. With both in one plan, you can say that every record has its required fields and that no two records share a key. The plan below does this for game_revenue.
(
pb.Validate(data=game_revenue, tbl_name="game_revenue", label="Row contract")
.rows_distinct(columns_subset=["session_id", "time"])
.rows_complete(columns_subset=["player_id", "session_id", "session_start"])
.interrogate()
)The completeness step passes for every row, while the distinctness step flags the same 22 rows we saw earlier. Together they say the records are complete, but a few of them share a session and timestamp and need a closer look. Pairing a uniqueness check on the key with a completeness check on the required fields is also the basis of the data contracts in Chapter 23.
8.4 Beyond distinctness and completeness
Distinctness and completeness are the row checks you will use most, but other checks also look at whole rows. When a rule involves several columns at once, such as a ship date that must come after the order date unless the order was cancelled, the conjointly() method can express it. The specially() method and the expression-based col_vals_expr() handle anything that does not fit a built-in method. All three are covered in Chapter 12. When the judgment is about meaning, such as whether a free-text review expresses a clear sentiment, the prompt() method sends each row to a language model, as described in Chapter 17.
Both methods in this chapter also accept the same segments= and pre= arguments as the column checks. The segments= argument gives a separate result for each group, as described in Chapter 11. The pre= argument transforms or filters the data before the check runs, which is handy for limiting a rule to the rows it applies to, such as requiring unique email addresses only among active customers. Chapter 12 covers pre= in full.
8.5 Summary
Row validations catch problems that column checks cannot, because they look at the whole record. The rows_distinct() method checks uniqueness, either across entire rows or, more usefully, across a columns_subset= that defines the key. Every copy of a duplicated row counts as a failure, so the failure count shows the full extent of the problem. Choosing the key is the main decision. Adding columns one at a time and watching the failure count fall helps separate expected repetition from real duplicates.
The rows_complete() method checks that values are present, either in every column or in a columns_subset= that lists the required fields. Used together, the two checks state that records are both complete and unique. The next chapter, Chapter 9, moves from rows to the table as a whole, with checks on structure such as the schema, the table’s dimensions, and how recent the data is.