9  Table Structure and Freshness

NoteCompact validation reports

As in Chapter 6, we hide the report header and footer with pb.config(report_incl_header=False, report_incl_footer=False) so that each report shows just the step rows. In your own work, keep the header and footer visible when the extra context helps.

Column checks look at values and row checks look at records, but both assume you have the right table in front of you. Table-level checks test that assumption. They ask whether the table has the columns you expect, whether it holds a plausible amount of data, whether it is recent enough to trust, and whether it matches another table you are comparing it against. If any of these is off, the results of the other checks in a plan may not mean much.

The problems these checks catch tend to cause confusing failures further down the line. For example, a column that gets renamed upstream can make downstream queries fail or return nulls, and a feed that stops updating keeps serving old numbers that still look perfectly valid. These are problems with the table itself rather than with individual values, so it helps to check for them directly instead of trying to work backward from a pile of value-level failures.

We use the game_revenue dataset for the structural checks and small hand-built tables where a self-contained example is clearer.

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

We start with the most basic structural question: are the columns you expect actually there?

9.1 Schema validation

Pipeline code makes assumptions about a table’s structure. It expects certain columns to exist, with certain names and certain types. When one of those assumptions breaks, the pipeline usually breaks too. Schema validation writes those assumptions down so that a violation is caught as soon as the data arrives.

9.1.1 Checking that columns exist

The col_exists() method is the simplest structural check. It confirms that the named columns are present, and each column gets its own test unit.

(
    pb.Validate(data=game_revenue)
    .col_exists(columns=["player_id", "session_id", "item_type", "item_revenue"])
    .interrogate()
)

		STEP	COLUMNS	VALUES	TBL	EVAL	UNITS	PASS	FAIL	W	E	C	EXT
#4CA64C	1	col_exists()	player_id	—		✓	1	1 1.00	0 0.00	—	—	—	—
#4CA64C	2	col_exists()	session_id	—		✓	1	1 1.00	0 0.00	—	—	—	—
#4CA64C	3	col_exists()	item_type	—		✓	1	1 1.00	0 0.00	—	—	—	—
#4CA64C	4	col_exists()	item_revenue	—		✓	1	1 1.00	0 0.00	—	—	—	—

All four columns are present, so all four steps pass. Since each column is its own step, a missing column shows up on its own row in the report. Let’s ask for a column that does not exist.

(
    pb.Validate(data=game_revenue)
    .col_exists(columns=["player_id", "customer_id"])
    .interrogate()
)

		STEP	COLUMNS	VALUES	TBL	EVAL	UNITS	PASS	FAIL	W	E	C	EXT
#4CA64C	1	col_exists()	player_id	—		✓	1	1 1.00	0 0.00	—	—	—	—
#4CA64C66	2	col_exists()	customer_id	—		✓	1	0 0.00	1 1.00	—	—	—	—

The player_id step passes and the customer_id step fails, which tells you exactly which column is missing. This is often the first thing to check on a table you are not familiar with or that someone else maintains, since everything else depends on the columns being there.

9.1.2 Matching names and types

Usually you care about types as well as names, and sometimes about column order too. The col_schema_match() method checks a table against a Schema, an object that lists the column names and types you expect. A schema can list every column in order, which is the strictest check. In practice, you often list only the columns you depend on and relax the match so that extra columns and a different order are allowed.

schema = pb.Schema(columns=[("player_id", "String"), ("item_revenue", "Float64")])

(
    pb.Validate(data=game_revenue)
    .col_schema_match(schema=schema, complete=False, in_order=False)
    .interrogate()
)

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

The single test unit passes, because both columns exist with the stated types. Setting complete=False allows the table to have columns that are not in the schema, and in_order=False allows the columns to appear in any order. Together they say “these columns must exist with these types” without requiring them to be the only columns or to appear in this order. Next, let’s claim that player_id is an integer when it is actually a string.

(
    pb.Validate(data=game_revenue)
    .col_schema_match(
        schema=pb.Schema(columns=[("player_id", "Int64")]),
        complete=False,
        in_order=False,
    )
    .interrogate()
)

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

The check fails because the declared type does not match the actual one. It is much easier to find out about a type change here than through a confusing error later in your analysis.

9.1.3 Tightening or relaxing the match

By default the match is strict. The schema has to list every column, in order, with exactly matching names and types. (Our two-column schema from above would fail with the defaults, because game_revenue has eleven columns.) Several arguments relax these requirements one at a time. It is worth seeing each of them, because the right setting can decide whether a plan survives a harmless change in how the data is produced. First, column order. Here is a schema that lists two columns in the opposite order from the table, with order enforced.

schema = pb.Schema(columns=[("item_revenue", "Float64"), ("player_id", "String")])

(
    pb.Validate(data=game_revenue)
    .col_schema_match(schema=schema, complete=False, in_order=True)
    .interrogate()
)

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

The check fails, because item_revenue comes after player_id in the table. Setting in_order=False accepts the columns in any position.

(
    pb.Validate(data=game_revenue)
    .col_schema_match(schema=schema, complete=False, in_order=False)
    .interrogate()
)

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

Now it passes. Type matching can be relaxed as well. With case_sensitive_dtypes=False and full_match_dtypes=False, a type name can differ in case and can be a partial match. So declaring player_id as "string" matches its actual String type.

(
    pb.Validate(data=game_revenue)
    .col_schema_match(
        schema=pb.Schema(columns=[("player_id", "string")]),
        complete=False, in_order=False,
        case_sensitive_dtypes=False, full_match_dtypes=False,
    )
    .interrogate()
)

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

This passes. Without those two arguments, the same schema fails, because "string" and "String" are not an exact match. The relaxed settings help when different systems spell the same type differently, such as int64 and Int64. If a column can legitimately arrive as one of several types, you can list all of them, and the match passes if the actual type is any one of those.

(
    pb.Validate(data=game_revenue)
    .col_schema_match(
        schema=pb.Schema(columns=[("item_revenue", ["Float64", "Float32"])]),
        complete=False, in_order=False,
    )
    .interrogate()
)

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

This passes because item_revenue is Float64, which is one of the listed types. If you only care that a column exists and not what type it has, you can list it without a type.

(
    pb.Validate(data=game_revenue)
    .col_schema_match(
        schema=pb.Schema(columns=[("player_id",), ("item_type",)]),
        complete=False, in_order=False,
    )
    .interrogate()
)

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

That passes because both columns are present. When choosing among these options, think about which parts of the structure your code actually depends on. A good rule is to use the loosest setting that still catches the changes you are worried about.

9.2 Dimensional validation

A table can have exactly the right schema and still hold the wrong amount of data. A load that only captured half the records is one example, and a schema check will not notice it. Checking the table’s dimensions can.

9.2.1 Row counts

The row_count_match() method checks the number of rows and produces a single test unit. The game_revenue table has exactly 2,000 rows.

(
    pb.Validate(data=game_revenue)
    .row_count_match(count=2000)
    .interrogate()
)

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

The check passes. For data that changes from day to day, an exact count is usually too strict, so the tol= argument allows a range around the expected number. For example, row_count_match(count=1900, tol=100) accepts anything from 1,800 to 2,000. As with the aggregate checks in Chapter 7, a tol= below 1 is a proportion, so tol=0.05 allows 5 percent either way. If you expect the count to match another table rather than a fixed number, you can pass that table as count=. Here we compare the full table against its first 1,500 rows.

extract = game_revenue.head(1500)

(
    pb.Validate(data=game_revenue)
    .row_count_match(count=extract)
    .interrogate()
)

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

The check fails because 2,000 does not equal 1,500. Comparing against another table like this is how you catch an unexpected drop in volume, even when the number on its own would look reasonable. The simplest version of the check just makes sure the table is not empty, by combining a count of zero with inverse=True.

(
    pb.Validate(data=game_revenue)
    .row_count_match(count=0, inverse=True)
    .interrogate()
)

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

With inverse=True, the check passes because the row count is not zero. This is an easy way to catch a situation where loading the data produced no rows.

TipWhen an exact row count is a suitable check

Some tables should have exactly the same number of rows every time, and for these an exact count with no tolerance is the most precise check you can write. Reference tables are a common example: a table of the 12 months, a calendar table for 2025 (365 rows), or a lookup of product categories should always have the same number of rows. Joins are another good case for this type of check. A left join onto a lookup table should return exactly as many rows as the table you started with, so row_count_match() with count= set to the original table catches a duplicate key that multiplied rows in the join (see Chapter 5).

Exact counts also work for part of a table. While row_count_match() does not accept segments= you can filter a table first with pre=. For example, a panel survey that should have exactly 500 respondents in each wave can be checked one wave at a time with row_count_match(count=500, pre=lambda df: df.filter(pl.col("wave") == 3)). Data for a closed period (such as last quarter’s transactions) is a good candidate too, since its row count should never change and any change means something was added or removed.

9.2.2 Column counts

The col_count_match() method does the same thing for the number of columns. The game_revenue table has 11.

(
    pb.Validate(data=game_revenue)
    .col_count_match(count=11)
    .interrogate()
)

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

The check passes. A column count is an easy way to notice that a table gained or lost columns between runs, which could otherwise go unnoticed as long as the columns your code reads are still there. Like row_count_match(), it accepts inverse=True to require any count other than the one given.

9.3 Data freshness

A table can have the right structure and still be out of date, with every value valid but none of them recent. The data_freshness() method checks that the most recent timestamp in a datetime column is within a maximum age of a reference time. Because “recent” depends on when the check runs, we fix the reference time here so the example gives the same result every time, and build a small table whose latest timestamp is two hours before it.

import polars as pl
import datetime as dt

reference = dt.datetime(2025, 6, 1, 12, 0, tzinfo=dt.timezone.utc)

recent = pl.DataFrame({
    "updated_at": [reference - dt.timedelta(hours=2), reference - dt.timedelta(hours=9)],
})

(
    pb.Validate(data=recent)
    .data_freshness(column="updated_at", max_age="24 hours", reference_time=reference)
    .interrogate()
)

		STEP	COLUMNS	VALUES	TBL	EVAL	UNITS	PASS	FAIL	W	E	C	EXT
#4CA64C	1	data_freshness()	updated_at	1d @2025-06-01 12:00:00+0000		✓	1	1 1.00	0 0.00	—	—	—	—

The single test unit passes, because the newest row is only two hours older than the reference time, well within 24 hours. Only the newest timestamp matters here, so the nine-hour-old row makes no difference. The max_age= argument accepts a string like "24 hours" or "2 weeks", or a timedelta. If you leave out reference_time=, it defaults to the current time, which is what you want in a live pipeline. Now a table whose newest row is too old:

stale = pl.DataFrame({
    "updated_at": [reference - dt.timedelta(days=3), reference - dt.timedelta(days=5)],
})

(
    pb.Validate(data=stale)
    .data_freshness(column="updated_at", max_age="24 hours", reference_time=reference)
    .interrogate()
)

		STEP	COLUMNS	VALUES	TBL	EVAL	UNITS	PASS	FAIL	W	E	C	EXT
#4CA64C66	1	data_freshness()	updated_at	1d @2025-06-01 12:00:00+0000		✓	1	0 0.00	1 1.00	—	—	—	—

The check fails because the most recent timestamp is three days old, well past the 24-hour limit. If you have an agreement about how current a dataset should be, a freshness check lets you enforce it and get alerted when it is not met. This comes up often in the pipeline scenarios of Chapter 30.

9.4 Table comparison

Sometimes the question is whether a table matches another table exactly. The tbl_match() method compares a table against a reference in stages, each stricter than the last. It checks the column count, then the row count, then the schema, and finally the values cell by cell, and it fails at the first stage where the tables differ. The whole comparison produces a single test unit. Two identical tables pass.

expected = pl.DataFrame({"id": [1, 2, 3], "value": [10, 20, 30]})
actual = pl.DataFrame({"id": [1, 2, 3], "value": [10, 20, 30]})

(
    pb.Validate(data=actual)
    .tbl_match(tbl_compare=expected)
    .interrogate()
)

		STEP	COLUMNS	VALUES	TBL	EVAL	UNITS	PASS	FAIL	W	E	C	EXT
#4CA64C	1	tbl_match()	None	EXTERNAL TABLE		✓	1	1 1.00	0 0.00	—	—	—	—

The check passes because every column, row, and value agrees. Changing a single value is enough to make it fail.

actual_changed = pl.DataFrame({"id": [1, 2, 3], "value": [10, 20, 99]})

(
    pb.Validate(data=actual_changed)
    .tbl_match(tbl_compare=expected)
    .interrogate()
)

		STEP	COLUMNS	VALUES	TBL	EVAL	UNITS	PASS	FAIL	W	E	C	EXT
#4CA64C66	1	tbl_match()	None	EXTERNAL TABLE		✓	1	0 0.00	1 1.00	—	—	—	—

The check fails because of the one changed value (99 instead of 30). This strictness is what makes tbl_match() useful for a few specific jobs. One is regression testing, where you confirm that a transformation still produces the expected output after a code change. Another is confirming that the same data pulled from two different sources agrees, or that running a process twice gives the same result. In each case the requirement really is an exact match.

9.5 Structure first

It makes sense to put table checks at the top of a plan, ahead of the value checks. There is little point asking whether item_revenue is positive if the column is missing or the table is empty. Pointblank still runs every step no matter what, so putting the structural checks first does not skip anything. What it does is make the report easier to read, since you see any structural problem first. If a column is missing, a value check on that column does not fail in the usual way. It shows an evaluation error instead, and the failed col_exists() or col_schema_match() step above it explains why.

schema = pb.Schema(columns=[
    ("player_id", "String"),
    ("session_id", "String"),
    ("item_revenue", "Float64"),
])

(
    pb.Validate(data=game_revenue, tbl_name="game_revenue")
    .col_schema_match(schema=schema, complete=False, in_order=False)
    .row_count_match(count=0, inverse=True)
    .col_vals_not_null(columns="player_id")
    .rows_distinct(columns_subset=["session_id", "time"])
    .col_vals_gt(columns="item_revenue", value=0)
    .interrogate()
)

		STEP	COLUMNS	VALUES	TBL	EVAL	UNITS	PASS	FAIL	W	E	C	EXT
#4CA64C	1	col_schema_match()	—	SCHEMA		✓	1	1 1.00	0 0.00	—	—	—	—
#4CA64C	2	row_count_match()	—	≠ 0		✓	1	1 1.00	0 0.00	—	—	—	—
#4CA64C	3	col_vals_not_null()	player_id	—		✓	2000	2000 1.00	0 0.00	—	—	—	—
#4CA64C66	4	rows_distinct()	session_id, time	—		✓	2000	1978 0.99	22 0.01	—	—	—
#4CA64C	5	col_vals_gt()	item_revenue	0		✓	2000	2000 1.00	0 0.00	—	—	—	—

The first two steps confirm the structure: the expected columns exist with the right types, and the table is not empty. The value checks below them confirm that player IDs are present and revenue is positive, and they flag the 22 rows that share a session and timestamp (the same rows we looked at in Chapter 8). Reading from top to bottom, you can see that the table is structurally sound and that the only issue is that small set of duplicate events.

9.6 Beyond structure

A couple of table-level checks are about how much of a column is missing rather than whether the column exists. The col_pct_null() and col_pct_missing() methods check that the fraction of missing values in a column is near an expected level, which catches a change in completeness even when the schema has not changed. They are covered in Chapter 10 along with the rest of the missing-data tools. Most of the methods in this chapter (all except col_exists()) also accept the pre= argument, which transforms or filters the table before the check runs. This is occasionally useful when a structural expectation applies only to part of the table.

9.7 Summary

Table-level checks confirm that you are working with the right table before you look at what is in it. For structure, col_exists() checks that named columns are present, and col_schema_match() checks names and types against a Schema, with arguments like complete=, in_order=, and full_match_dtypes= to make the match as strict or as loose as you need. For size, row_count_match() and col_count_match() catch tables that have too few rows or that gained or lost columns. Both support inverse=True, and row_count_match() also takes a tol=.

For recency, data_freshness() checks that the newest timestamp in a column is within a maximum age. For exact agreement, tbl_match() compares a table against a reference and passes only if they match completely, which makes it the right tool for regression testing. Putting these checks at the top of a plan makes structural problems easy to spot in the report. The next chapter, Chapter 10, looks closely at missing values, including placeholder codes that a plain null check cannot see.