Sentinel values and fake NULLs

Concepts covered: Null Handling

What a sentinel is

A sentinel is an ordinary value a system wrote in place of a missing one. The column was declared NOT NULL, a form refused to submit without the field, or a CSV export had no way to write NULL. The table now holds a number or a string that looks like data and means 'we never knew'.

Every aggregate from the last lesson treats a sentinel as real. NULL is skipped by COUNT(age), AVG(age) and MIN(age); a 0 is included by all 3. A column of fake zeros gives a lower average, a wrong minimum and a count that claims more knowledge than the table holds.

The damage a fake zero does

The legacy_users table holds 5 users. A signup form that required the age field, and let people pass it with the default, wrote 0 for 2 of them. Run the average over every row, then again with the zeros filtered out.

SELECT
  'every row' AS scope,
  COUNT(*) AS n,
  AVG(age) AS avg_age
FROM legacy_users
UNION ALL
SELECT
  'age above 0' AS scope,
  COUNT(*) AS n,
  AVG(age) AS avg_age
FROM legacy_users
WHERE age > 0

The first row says the average user is 21.2; the second says 35.3. AVG folded both zeros into (34 + 0 + 27 + 0 + 45) / 5, and the 14-year gap comes entirely from values that were never ages. COUNT(age) would report 5 ages known, when only 3 are.

Profiling a column to find its sentinels

Sentinels are found by profiling: GROUP BY the column, count each group, and sort the biggest first. Real values spread across a range; a sentinel piles up in one group, often at a value the column could never hold. The shipments table records parcel weights from a warehouse scanner.

SELECT
  weight_kg,
  COUNT(*) AS n
FROM shipments
GROUP BY weight_kg
ORDER BY n DESC, weight_kg

Of the 10 shipments, 4 weigh -1 kilograms. No parcel has negative mass, and no real weight repeats more than twice, so -1 is a code for 'weight not captured'. AVG(weight_kg) over this table returns 3.35, while the 6 weighed parcels average 6.25.

A profile of discount_pct across 50,000 orders returns these counts: 0 has 31,200 rows, 5 has 8,100, 10 has 7,900, 15 has 2,750 and 100 has 50. Which value most needs a second look before you average the column?

• 100, because a discount of 100 percent is rare and a rare value is the signature of a code
• 0, because it holds far more rows than any other value and may mean 'no discount recorded' as well as 'no discount given'
• 15, because it breaks the run of values that double from 5 to 10 and looks like a typo
• 5, because the smallest nonzero value in a numeric column is where defaults usually land

Strings that pretend to be NULL

Text columns hide missing data behind placeholders: 'N/A', 'unknown', 'none', 'TBD', an empty string, or the 4-character word 'NULL' that a CSV export wrote when it met a real NULL. Every one of them is a value. carrier_code IS NULL is FALSE for each of them, and COUNT(carrier_code) counts each of them. The shipments table also carries a carrier code: 1 shipment has a true NULL there, and 3 others carry 'N/A'.

SELECT
  carrier_code,
  COUNT(*) AS n,
  COUNT(carrier_code) AS counted_as_present
FROM shipments
GROUP BY carrier_code
ORDER BY n DESC, carrier_code

The true NULL on S-10 behaves as the last lesson described: COUNT(*) sees the row and COUNT(carrier_code) skips it, so its group reads 1 and 0. The 'N/A' group gets no such treatment: 'N/A' is a 3-character string, as present as 'NWX'. A report that counts 'shipments with a known carrier' as COUNT(carrier_code) says 9 when the correct answer is 6.

About this lesson

This section is part of the NULL Values: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.