3  Inspecting & Profiling Your Data

You cannot validate what you do not understand. A validation rule is a statement of expectation, and an expectation has to come from somewhere. For data you know well, that knowledge lives in your head: you already know which columns should never be empty, which values are categorical, and what a plausible range looks like. For data you are meeting for the first time, you have none of that, and writing rules before you have looked is how validation plans end up either too loose to catch anything or too strict to ever pass.

This chapter is about the looking part. Pointblank ships a small set of inspection tools that answer the first questions you ask of any table: What columns are here, and what types do they hold? How many rows are there? Where are the values missing, and how are they distributed? Each tool turns a vague sense of “let me get to know this data” into a repeatable step you can run on any table, whether it lives in a local DataFrame or a remote warehouse.

We will move from the broadest view to the most detailed. First we preview a handful of rows to orient ourselves, then we profile every column at once, then we focus specifically on missing values, and finally we look at how the same tools work against data that never leaves the database. The goal throughout is not to validate anything yet, but to learn enough that the validation plan almost writes itself. Building and running that plan is the subject of Chapter 4, and letting Pointblank draft one for you from a profile is covered in Chapter 18.

3.1 The example data

Every example in this chapter uses one of the datasets bundled with Pointblank, so you can run the code exactly as written without hunting for a file. The load_dataset() function returns a ready-to-use table, and its tbl_type= argument controls whether you get a Polars DataFrame, a Pandas DataFrame, or a DuckDB-backed table. We begin with small_table, which is deliberately tiny at thirteen rows and eight columns, because it is easy to reason about while still containing the kinds of imperfections that matter.

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

Before looking at any values, it is worth confirming the two most basic facts about a table: how many rows and columns it has. The get_row_count() and get_column_count() functions answer exactly those questions, and they work identically across every backend Pointblank supports.

print(pb.get_row_count(small_table), "rows")
print(pb.get_column_count(small_table), "columns")
13 rows
8 columns

Those two numbers are often the first sign that something is wrong. A table you expected to hold a million rows that reports a few hundred usually means a load failed or a filter ran too aggressively. With the dimensions confirmed, we are ready to look at the data itself.

3.2 A first look with preview()

The simplest inspection is to look at some rows, but a raw DataFrame print is a poor way to do it. Wide tables wrap awkwardly, long tables scroll past the screen, and neither the column types nor the overall shape are easy to pick out. The preview() function solves this by rendering a compact, formatted view that shows a few rows from the top and a few from the bottom of the table, along with each column’s type.

Function signature
preview(
    data,
    columns_subset=None,
    n_head=5,
    n_tail=5,
    limit=50,
    show_row_numbers=True,
    max_col_width=250,
    min_tbl_width=500,
    incl_header=None
)

Calling it on small_table shows the whole idea at a glance.

pb.preview(small_table)
PolarsRows13Columns8
date_time
Datetime
date
Date
a
Int64
b
String
c
Int64
d
Float64
e
Boolean
f
String
1 2016-01-04 11:00:00 2016-01-04 2 1-bcd-345 3 3423.29 True high
2 2016-01-04 00:32:00 2016-01-04 3 5-egh-163 8 9999.99 True low
3 2016-01-05 13:32:00 2016-01-05 6 8-kdg-938 3 2343.23 True high
4 2016-01-06 17:23:00 2016-01-06 2 5-jdo-903 None 3892.4 False mid
5 2016-01-09 12:36:00 2016-01-09 8 3-ldm-038 7 283.94 True low
9 2016-01-20 04:30:00 2016-01-20 3 5-bce-642 9 837.93 False high
10 2016-01-20 04:30:00 2016-01-20 3 5-bce-642 9 837.93 False high
11 2016-01-26 20:07:00 2016-01-26 4 2-dmx-010 7 833.98 True low
12 2016-01-28 02:51:00 2016-01-28 2 7-dmx-010 8 108.34 False low
13 2016-01-30 11:23:00 2016-01-30 1 3-dka-303 None 2230.09 True high

The preview shows the first five and last five rows with a divider between them, so you see how the data looks at both ends of the table without scrolling through the middle. Each column header carries its data type, which is often where the first surprise appears: a date read as a string, or an integer column that arrived as a float. The c column here is worth noting, because a couple of its cells are empty, and that is the missing data we will return to later in the chapter.

3.2.1 Focusing the preview

On a wide table, showing every column defeats the purpose of a compact view. The columns_subset= argument narrows the preview to the columns you actually care about, and n_head= and n_tail= control how many rows appear at each end. Together they let you frame a preview around a specific question rather than dumping the entire table.

pb.preview(small_table, columns_subset=["a", "c", "f"], n_head=3, n_tail=3)
PolarsRows13Columns8
a
Int64
c
Int64
f
String
1 2 3 high
2 3 8 low
3 6 3 high
11 4 7 low
12 2 8 low
13 1 None high

Here we have asked for just three columns and three rows at each end, which is enough to check that a looks like a small integer, c holds counts with the occasional gap, and f takes a limited set of text values. Narrowing the view this way is especially useful on tables with dozens of columns, where the signal you want is easily lost among columns you do not. A quick, focused preview is usually all it takes to decide which columns deserve closer profiling.

3.3 Profiling columns with col_summary_tbl()

A preview shows you individual rows, but it cannot tell you the character of a column across all of its values. For that you need a profile: the type of each column, how many values are missing, how many are distinct, and the range and central tendency of the numbers. The col_summary_tbl() function computes all of this in one call and presents it as a single readable table.

Function signature
col_summary_tbl(data, tbl_name=None)

Running it on small_table produces a column-by-column summary of the entire table.

pb.col_summary_tbl(small_table)
PolarsRows13Columns8
Column NA UQ Mean SD Min P5 Q1 Med Q3 P95 Max IQR
date
date_time
Datetime(time_unit='us', time_zone=None)
 0
0		 12
0.92		 - - 2016
01
04 00:32:00		 - - - - - 2016
01
30 11:23:00		 -
date
date
Date
 0
0		 11
0.85		 - - 2016
01
04		 - - - - - 2016
01
30		 -
numeric
a
Int64
 0
0		 7
0.54		 3.77 2.09 1 1.06 2 3 4 7.4 8 2
string
b
String
 0
0		 12
0.92		 9 0 9 9 9 9 9 9 9 0
numeric
c
Int64
 2
0.15		 7
0.54		 5.73 2.72 2 2.05 3 7 8 9 9 5
numeric
d
Float64
 0
0		 12
0.92		 2,304.7 2,631.36 108.34 118.88 837.93 1,035.64 3,291.03 6,335.44 9999.99 2,453.1
boolean
e
Boolean
 0
0		 T0.62
F0.38		 - - - - - - - - - -
string
f
String
 0
0		 3
0.23		 3.46 0.52 3 3 3 3 4 4 4 1
String columns statistics regard the string's length.

Each row of this summary describes one column of the data. You can read off the storage type, the count of missing values, and the number of unique values, and for the numeric columns you also get statistics such as the mean, the minimum, and the maximum. This is the view that turns hunches into facts. The f column reveals itself as low-cardinality text, a strong hint that it is categorical and a candidate for a set-membership check, while the numeric columns expose the ranges you would use to set sensible boundaries. Reading a profile like this is usually the moment a validation plan starts to take shape in your mind.

3.3.1 Programmatic profiles with DataScan

The summary table is ideal for a human, but sometimes you want the profile as data rather than as a display, for example to store it, compare it against a later run, or drive logic in a script. The DataScan class is the engine behind the summary, and it exposes the profile in several forms. You construct it with a table, then ask it for whichever representation you need.

Class signature
scan = DataScan(data, tbl_name=None)

scan.get_tabular_report(show_sample_data=False)  # the visual summary table
scan.summary_data()                              # the profile as a data frame
scan.to_json()                                   # the profile as a JSON string
scan.save_to_json(output_file)                   # write the JSON to a file

The get_tabular_report() method returns the same kind of summary we just saw, which confirms that col_summary_tbl() is really a convenient shortcut over DataScan.

scan = pb.DataScan(data=small_table)
scan.get_tabular_report()
PolarsRows13Columns8
Column NA UQ Mean SD Min P5 Q1 Med Q3 P95 Max IQR
date
date_time
Datetime(time_unit='us', time_zone=None)
 0
0		 12
0.92		 - - 2016
01
04 00:32:00		 - - - - - 2016
01
30 11:23:00		 -
date
date
Date
 0
0		 11
0.85		 - - 2016
01
04		 - - - - - 2016
01
30		 -
numeric
a
Int64
 0
0		 7
0.54		 3.77 2.09 1 1.06 2 3 4 7.4 8 2
string
b
String
 0
0		 12
0.92		 9 0 9 9 9 9 9 9 9 0
numeric
c
Int64
 2
0.15		 7
0.54		 5.73 2.72 2 2.05 3 7 8 9 9 5
numeric
d
Float64
 0
0		 12
0.92		 2,304.7 2,631.36 108.34 118.88 837.93 1,035.64 3,291.03 6,335.44 9999.99 2,453.1
boolean
e
Boolean
 0
0		 T0.62
F0.38		 - - - - - - - - - -
string
f
String
 0
0		 3
0.23		 3.46 0.52 3 3 3 3 4 4 4 1
String columns statistics regard the string's length.

The difference is what else the scan can give you. Calling to_json() returns the entire profile as a JSON string, and summary_data() returns it as a data frame, either of which can be persisted and diffed over time to detect when a column’s shape has changed. That comparison-over-time use is the seed of drift detection, which we return to at the end of the chapter. For now, the important point is that the same profile is available both as something to read and as something to compute with.

3.4 Finding missing values with missing_vals_tbl()

Missing values are often the earliest and clearest signal of a data quality problem, but a single count of nulls hides more than it reveals. A column that is missing five percent of its values at random is a very different situation from a column that is completely empty for the most recent week of data. The missing_vals_tbl() function shows not just how much is missing but where, by dividing the table into row sections and reporting the missing proportion for each column within each section.

Function signature
missing_vals_tbl(data, missing=None, as_heatmap=False)

Running it on small_table makes the pattern in the c column visible.

pb.missing_vals_tbl(small_table)
Missing Values   2 in total
PolarsRows13Columns8
Column Row Sector
1 2 3 4 5 6 7 8 9 10
date_time
date
a
b
c
d
e
f
NO MISSING VALUES     PROPORTION MISSING:  
0%
100%
ROW SECTORS
  1. 1 – 1
  2. 2 – 2
  3. 3 – 3
  4. 4 – 4
  5. 5 – 5
  6. 6 – 6
  7. 7 – 7
  8. 8 – 8
  9. 9 – 9
  10. 10 – 13

The table breaks the rows into sections and shades each cell according to how much of that column is missing within that section. Because small_table is short, the effect is modest, but the structure is exactly what scales up: on a large table this view immediately distinguishes a column with scattered gaps from one whose missingness is concentrated in a particular stretch of rows. Reading the layout of missingness, rather than a single summary number, is what lets you tell an expected empty field from a genuine failure.

3.4.1 Reading missing-value patterns

3.4.2 Heatmaps and coded missingness

3.5 Inspecting data where it lives

3.5.1 Connecting to a database

3.6 From inspection to a validation plan

3.7 Profiling and drift detection

3.8 Summary