How Null Rates and Distinct Counts Detect a Broken Join

A join between a fact table and a dimension returns slightly fewer rows than expected. The shortfall is a few percent, which is inside the noise of a nightly volume check, so no alert fires. The fault is not latent because it is subtle: it is latent because the only check in place measures a quantity that barely moves. The report built on the join loses one region entirely, and nothing in the volume record distinguishes that day from any other.

The volume check could not have caught it, because volume is a single number that responds to every cause at once. A profile of the joined columns would have caught it on the first load: the null rate of the dimension’s foreign key, previously zero, becomes the fraction of fact rows that no longer match. One column, one aggregate, one day of change.

What a profile is, and what it costs

A column profile is a set of aggregates computed over one column: the count of nulls, the count of distinct values, the minimum and maximum, and often a small number of most frequent values with their frequencies. Every one of these can be computed in a single pass, and several of them are already computed by the storage layer for its own purposes.

That last point is what makes profiling affordable. A columnar format stores per-column statistics in the file footer, including the null count and, for some types, the minimum and maximum, so a profile over an existing table can be assembled from metadata without reading any data pages. The cost of a null-rate check over a partitioned table is therefore proportional to the number of files, not the number of rows. Distinct counts are the exception: no format stores them, and computing them exactly requires either a full scan with a hash set proportional to the cardinality or a sort, both of which scale with the data rather than the file count.

The alternative to an exact distinct count is a sketch. HyperLogLog estimates cardinality in a fixed amount of memory, on the order of a few kilobytes for accuracy in the low percent range, and its error is a function of the number of registers rather than the number of distinct values. The standard error is approximately $1.04/\sqrt{m}$ for $m$ registers, so 2,048 registers give roughly a two percent error, and 16,384 registers give under one percent. The estimate is mergeable, which means per-partition sketches can be combined into a table-wide estimate without rescanning, and that property is what makes cardinality monitoring practical at warehouse scale.

Why the null rate is the most informative single number

A null rate is the fraction of rows in which a column holds no value. It is trivial to compute and it detects a class of failure that nothing else does, because most join failures manifest as a key that has nothing to match.

In a left join, an unmatched probe row produces a null in every column taken from the build side. If the dimension’s key format changes, or its rows are filtered by a predicate that excludes some keys, or the dimension is simply not yet loaded, the probe rows survive and their dimension columns become null. The row count barely moves, because the fact rows are all still there. The null rate of the dimension’s columns moves from near zero to the fraction of probe rows that failed to match, which is exactly the size of the error.

The reverse case is a right or inner join, where unmatched rows are dropped rather than nulled. There the row count does move, but only by the number of unmatched rows, which may be small relative to the total. The complementary signal is the null rate of the probe side’s key in the input to the join, before the join runs: a key column that has acquired nulls indicates that the upstream source is producing records without the attribute, and those records will drop out of an inner join downstream. Monitoring the input null rate localises the fault to the producer rather than to the join.

Null rates also detect a subtler failure: the coalesced default. A transform that replaces nulls with a sentinel such as an empty string, a zero, or the string unknown converts a detectable absence into a plausible value. The null rate drops to zero, which looks like an improvement, and the sentinel accumulates. Detecting this requires profiling the frequency of the sentinel values, which is why a profile should include the top few values by count and not only the aggregate statistics. A column whose top value is unknown at fifteen percent is broken regardless of its null rate.

Cardinality as a fingerprint

A distinct count is a fingerprint of a column’s content, and its value as a check comes from how it changes.

A column that should hold one row per customer has a distinct count equal to its row count. If the distinct count falls below the row count, duplicates have entered, and the ratio is the duplication factor. A column that holds a categorical attribute, such as a country code, has a distinct count in the low hundreds that changes rarely; a jump from 240 to 3,000 indicates either an encoding change or a new source with free-text values in a coded field.

The direction of the change is diagnostic. A collapse in cardinality, where a column that held thousands of distinct values suddenly holds a handful, usually means a truncation, a case-folding, or a join that matched far more broadly than intended. An explosion means the opposite: a key that was supposed to be unique is no longer, or a field that was coded has started carrying raw input.

Both are visible in a single number, and neither is visible in the row count. That is the argument for profiling every column of a table rather than a chosen few, since the column that breaks is rarely the one that was chosen. The complement is a check that counts rows per partition and compares them over time, which catches faults that leave every marginal profile unchanged.

Profiles over time: the comparison is the check

A profile value in isolation says little. A distinct count of 4,200 is unremarkable until it is compared with the 4,200 of yesterday and the 4,180 of the day before, at which point a value of 3,100 is obviously anomalous. The check is therefore on the change in the profile, not on its level.

Comparing consecutive profiles has a failure mode of its own: the comparison is sensitive to the size of the population. A table that grows by one percent a day has a distinct count that grows by some fraction of a percent a day, and a fixed absolute threshold on the change will eventually fire on normal growth. Expressing the change as a ratio and thresholding the ratio handles growth but not seasonality, where the population legitimately varies by a large factor between weekdays and weekends.

The version that tolerates outliers compares against a distribution of past values rather than a single predecessor, and flags values outside a band derived from that distribution. A median and a median absolute deviation over the last several comparable periods is a simple form that tolerates both growth and seasonality, because both are present in the history being summarised. The band widens where the history is variable, which is where false alerts would otherwise originate.

from statistics import median

def mad_band(history, k=3.0):
    """Median and scaled MAD over past profile values; returns (low, high)."""
    med = median(history)
    mad = median(abs(x - med) for x in history)
    # 1.4826 rescales MAD to a standard-deviation equivalent for normal data.
    spread = 1.4826 * mad
    return med - k * spread, med + k * spread

def is_anomalous(value, history, k=3.0):
    low, high = mad_band(history, k)
    return value < low or value > high

The band’s weakness is that it is blind to a change that persists. Once a broken value has been present for enough periods, it enters the history and becomes the new normal, and the check stops firing. Guarding against this requires either a reference profile frozen at a known-good state, or a rule that excludes recent periods from the history when computing the band. The first is more reliable and requires a decision about which state was good; the second adapts automatically and will eventually accept any persistent change.

Distribution, not just count

Aggregate statistics are blind to shape. Two columns can have identical null rates, distinct counts, and ranges while one of them has a distribution that has been silently truncated.

A numeric column whose maximum is bounded by a cap applied upstream has the same profile as one that is naturally bounded, until the cap moves. The signal is in the frequency distribution: a spike at exactly the cap value, or a spike at zero, indicates a clamp rather than a measurement. Capturing this requires either a histogram or a count of the most frequent values, and the latter is much cheaper: a top-ten list over a column will show a clamped value immediately if it is frequent enough to matter.

The same applies to dates. A timestamp column whose maximum is exactly midnight of the current day suggests that the producing system is writing a date rather than a time, and a column whose minimum is a round number such as the epoch suggests that missing values are being encoded as zero rather than null. Both are detectable from the extremes alone, which the storage layer already records.

Skew is the distribution property that matters most for cost, and it is visible in the profile of a join key. If the top value of a key column holds a large share of the rows, any operation that partitions by that key will route most of the data to one task. A profile that reports the top values with counts makes the skew visible before the job that suffers from it runs, which is the cheapest possible place to catch it. The same top-value information also fixes the expectation for a partition volume check, because a partition whose rows are concentrated in one key cannot be compared against a table-wide average.

Where profiling stops being enough

A profile describes marginal distributions. It says nothing about relationships between columns, and most semantic errors are relational: a total that does not equal the sum of its parts, a start date after its end date, an address whose postal code does not match its city. None of these change any single column’s null rate or cardinality.

Catching them requires a check that evaluates a predicate over combinations of columns, which costs a scan and cannot be derived from metadata. The practical division is to profile every column on every load, because it is nearly free, and to evaluate relational assertions on a schedule that fits their cost, often once per partition rather than once per row group. The profile catches the faults that arrive as a change in one column’s shape; the relational check catches the faults that arrive as a change in how columns agree, which is what a referential integrity check does for the specific case of a key that resolves to nothing.

There is also a class of fault that neither catches: a value that is well-formed, plausible, correctly distributed, and simply wrong for the row it is attached to. A customer’s record with another customer’s phone number passes every marginal check, because both numbers are valid phone numbers. Only a cross-check against an independent source of truth detects it, and an independent source of truth is precisely what a pipeline reading from one system does not have.