Why can a Great Expectations regex expectation pass on a column that is entirely null?

middlenice to knowfreq 32%

answer

nothing present means nothing to violate
presence and format are separate questions
nulls are excluded, not counted as unexpected
an empty batch passes nearly everything
pair the format rule with a not-null rule

basics

~10 s

Most row-wise value expectations evaluate only non-null values, so a fully null column offers nothing to violate the pattern and the check passes vacuously. Assert completeness separately with expect_column_values_to_not_be_null.

solid answer

~40 s

In Great Expectations, column map expectations other than the explicit null checks treat missing values as not evaluated rather than as unexpected. expect_column_values_to_match_regex therefore asks "do the non-null values match?", and when every value is null the answer is vacuously yes - zero evaluated rows, zero unexpected, success true. The same logic hits expect_column_values_to_be_in_set and expect_column_values_to_be_between, and an empty batch makes essentially every row-wise expectation pass at once. The remedy is to make presence its own assertion: pair the format check with expect_column_values_to_not_be_null (with a mostly if some nullness is legitimate) and add expect_table_row_count_to_be_between so a zero-row or short batch fails loudly. This is the single most common way a green Great Expectations run coexists with a broken upstream extract.

In Great Expectations, column map expectations other than the explicit null checks treat missing values as **not evaluated** rather than as unexpected. `expect_column_values_to_match_regex` therefore asks "do the non-null values match?", and when every value is null the answer is vacuously yes - zero evaluated rows, zero unexpected, `success` true. The same logic hits `expect_column_values_to_be_in_set` and `expect_column_values_to_be_between`, and an empty batch makes essentially every row-wise expectation pass at once. The remedy is to make presence its own assertion: pair the format check with `expect_column_values_to_not_be_null` (with a `mostly` if some nullness is legitimate) and add `expect_table_row_count_to_be_between` so a zero-row or short batch fails loudly. This is the single most common way a green Great Expectations run coexists with a broken upstream extract.

code

python · 8 lines

python

# passes on a column that is 100% null - nothing present to violate the pattern
gx.expectations.ExpectColumnValuesToMatchRegex(
    column="email", regex=r"^[^@\s]+@[^@\s]+$"
)

# assert presence separately, and assert the batch is not empty
gx.expectations.ExpectColumnValuesToNotBeNull(column="email", mostly=0.98)
gx.expectations.ExpectTableRowCountToBeBetween(min_value=10000)

go deeper

for a junior

Remember that a null is not automatically a failure: most value expectations look only at the values that are present, so you need an explicit not-null expectation to catch a missing column.

for a middle

Explain the mechanism - missing values are excluded from the evaluated set, so zero evaluated rows means a vacuous pass - and name the fix: table row-count floor plus explicit not-null expectations.

for a senior

Tell the production version: an upstream join breaks, the column goes all-null, every format check stays green and the empty column reaches a dashboard. Show that you trend null rates rather than trusting the boolean.

for a principal

Own the suite-authoring standard that prevents whole classes of vacuous passes - mandatory volume and completeness expectations on every gated asset - so no team ships a suite that can be green over an empty extract.

The behaviour

Great Expectations splits row-wise column expectations into two conceptual questions: is the value present? and is the present value acceptable? Only the first family - expect_column_values_to_not_be_null and expect_column_values_to_be_null - answers the presence question. Everything else in the expect_column_values_to_* family answers the second one, and to do that it first sets nulls aside: missing values are excluded from the evaluated set rather than being counted as unexpected.

The consequence is arithmetic. Feed a column of one million nulls to expect_column_values_to_match_regex. Zero rows are evaluated, so zero rows are unexpected, so the proportion satisfying the condition is trivially complete and success is true. The check has told you the truth - none of the present values violated the pattern - it just was not the question you thought you were asking.

Why the design is defensible

It is not a bug, and interviewers like candidates who can say why. Separating presence from format keeps failure messages precise. If nullability were folded into every expectation, a failing regex check on a partially populated column would be ambiguous: are the values malformed, or merely missing? By making presence its own expectation you get two independent verdicts, each pointing at a different upstream cause - a broken source join versus a broken formatter. It also lets legitimately optional columns be format-checked at all: an optional phone column can be required to look like a phone number when it has one, which is exactly the right rule.

The failure it produces in production

The realistic incident is an upstream change - a renamed source field, a broken join, a vendor enrichment that silently stopped returning matches - that leaves a column entirely or overwhelmingly null. Every format and range expectation on that column goes green. The suite reports success, the Checkpoint's actions cheerfully rebuild Data Docs showing all-clear, and the empty column lands in the warehouse where a dashboard turns into a flat line a week later.

The worse sibling of this is the empty batch. If today's extract produced no rows at all - a failed export, a wrong partition path, a filter that matched nothing - then every row-wise expectation in the suite evaluates zero rows and passes. A suite of thirty carefully written expectations, all green, over nothing.

How to write the suite so it cannot happen

Four habits, roughly in order of value:

Assert volume at the table level. expect_table_row_count_to_be_between with a floor derived from observed history catches the empty and the suspiciously-short batch before any column check is even interesting.
Assert presence explicitly, per column that matters. expect_column_values_to_not_be_null(column="email") - with mostly if some nullness is normal - turns "the column stopped being populated" into a red result instead of a vacuous green one.
Pair presence with format. Keep the regex or set expectation for shape, and let the not-null expectation carry completeness. Two expectations, two independent diagnoses.
Watch the null rate, not just the boolean. The validation result carries element_count and unexpected_count; a column sliding from 2% null to 40% null is a real signal well before any threshold you set would trip.

The same trap in a different disguise

The null-exclusion rule interacts with mostly, which sets a minimum proportion of evaluated rows that must satisfy the condition. Because the denominator is the evaluated - that is, non-null - population, mostly=0.99 on a column that is 90% null describes the quality of the surviving 10% only. Candidates who understand one of these two behaviours usually understand both; understanding the combination is what separates someone who has actually shipped a suite from someone who has read the docs.

In an interview

State the mechanism in one sentence, say why the design is reasonable rather than calling it a flaw, then move straight to the mitigation: row-count floor at the table level, explicit not-null expectations for the columns that matter, and treating the null rate as a trend. The failure story - a green suite over an empty extract - is what makes the answer memorable.

## The behaviour Great Expectations splits row-wise column expectations into two conceptual questions: *is the value present?* and *is the present value acceptable?* Only the first family - `expect_column_values_to_not_be_null` and `expect_column_values_to_be_null` - answers the presence question. Everything else in the `expect_column_values_to_*` family answers the second one, and to do that it first sets nulls aside: missing values are excluded from the evaluated set rather than being counted as unexpected. The consequence is arithmetic. Feed a column of one million nulls to `expect_column_values_to_match_regex`. Zero rows are evaluated, so zero rows are unexpected, so the proportion satisfying the condition is trivially complete and `success` is true. The check has told you the truth - none of the present values violated the pattern - it just was not the question you thought you were asking. ## Why the design is defensible It is not a bug, and interviewers like candidates who can say why. Separating presence from format keeps failure messages precise. If nullability were folded into every expectation, a failing regex check on a partially populated column would be ambiguous: are the values malformed, or merely missing? By making presence its own expectation you get two independent verdicts, each pointing at a different upstream cause - a broken source join versus a broken formatter. It also lets legitimately optional columns be format-checked at all: an optional `phone` column can be required to look like a phone number *when it has one*, which is exactly the right rule. ## The failure it produces in production The realistic incident is an upstream change - a renamed source field, a broken join, a vendor enrichment that silently stopped returning matches - that leaves a column entirely or overwhelmingly null. Every format and range expectation on that column goes green. The suite reports success, the Checkpoint's actions cheerfully rebuild Data Docs showing all-clear, and the empty column lands in the warehouse where a dashboard turns into a flat line a week later. The worse sibling of this is the **empty batch**. If today's extract produced no rows at all - a failed export, a wrong partition path, a filter that matched nothing - then *every* row-wise expectation in the suite evaluates zero rows and passes. A suite of thirty carefully written expectations, all green, over nothing. ## How to write the suite so it cannot happen Four habits, roughly in order of value: 1. **Assert volume at the table level.** `expect_table_row_count_to_be_between` with a floor derived from observed history catches the empty and the suspiciously-short batch before any column check is even interesting. 2. **Assert presence explicitly, per column that matters.** `expect_column_values_to_not_be_null(column="email")` - with `mostly` if some nullness is normal - turns "the column stopped being populated" into a red result instead of a vacuous green one. 3. **Pair presence with format.** Keep the regex or set expectation for shape, and let the not-null expectation carry completeness. Two expectations, two independent diagnoses. 4. **Watch the null rate, not just the boolean.** The validation result carries `element_count` and `unexpected_count`; a column sliding from 2% null to 40% null is a real signal well before any threshold you set would trip. ## The same trap in a different disguise The null-exclusion rule interacts with `mostly`, which sets a minimum proportion of evaluated rows that must satisfy the condition. Because the denominator is the evaluated - that is, non-null - population, `mostly=0.99` on a column that is 90% null describes the quality of the surviving 10% only. Candidates who understand one of these two behaviours usually understand both; understanding the combination is what separates someone who has actually shipped a suite from someone who has read the docs. ## In an interview State the mechanism in one sentence, say why the design is reasonable rather than calling it a flaw, then move straight to the mitigation: row-count floor at the table level, explicit not-null expectations for the columns that matter, and treating the null rate as a trend. The failure story - a green suite over an empty extract - is what makes the answer memorable.

What happens to a suite of row-wise expectations when the batch has zero rows?
Essentially all of them pass. Each evaluates zero values, finds zero unexpected, and reports success, so a thirty-expectation suite goes green over nothing. This is why a table-level expect_table_row_count_to_be_between with a floor derived from history belongs in almost every suite - it is the check that makes the others meaningful.
Is the null-exclusion behaviour a design flaw?
No, it is a deliberate separation of concerns. Keeping presence out of format checks makes failure messages unambiguous - malformed values and missing values point at different upstream causes - and it lets genuinely optional columns be format-checked only when populated. The cost is that you must assert completeness yourself.
How does this interact with the mostly parameter?
The denominator for mostly is the evaluated population, which excludes nulls for these expectations. So mostly=0.99 on a column that is 90% null only describes the quality of the populated 10%. Combining a high null rate with a tolerance produces a check that is green while the column is nearly empty.

saying these in an interview costs you the question

Assuming nulls are counted as unexpected values by default
Trusting a green suite without any row-count expectation
Believing a single format expectation covers completeness too
Calling the behaviour a bug rather than a separation of concerns
Reading only the success boolean and never the null rate

Why can a Great Expectations regex expectation pass on a column that is entirely null?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

The behaviour

Why the design is defensible

The failure it produces in production

How to write the suite so it cannot happen

The same trap in a different disguise

In an interview

▾common follow-ups3 questions

▾red flags5 flags

§ context

▾related questions3 questions

▾related topics3 topics

The behaviour

Why the design is defensible

The failure it produces in production

How to write the suite so it cannot happen

The same trap in a different disguise

In an interview

answer

go deeper

context