ContractsGENERALFORMAT-SPECIFICWAREHOUSE-SPECIFIC

Nullability & Defaults

Making a field nullable is a breaking change for everyone who assumed it was not. A default hides missing data behind a plausible value. And "unknown", "not applicable" and "the pipeline dropped it" are three different facts stored identically.

What actually happens How to build it Can I trust it?

Who needs this, what one row is, and why the obvious build breaks

Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.

The question

This column contains a null. Does that mean the value is unknown, that it does not apply, or that something in the pipeline lost it — and can anyone downstream tell?

Who needs this

Any consumer that aggregates, filters, joins or branches on the field, which is all of them. The consumers most affected are the ones who never considered the question: a WHERE status != 'cancelled' that silently drops nulls, an AVG whose denominator quietly changes, a join that loses rows, a CASE whose ELSE absorbs an absence as if it were a category (The Dimensions of Data Quality).

What one row is

The unit is one field of one row, and the reason it is empty. That reason is the information the platform routinely fails to carry: the null is stored, the reason is not, and the reason is what every downstream decision actually depends on.

The obvious build

Fill it in. A null is awkward — it breaks arithmetic, it drops out of filters, it confuses BI tools — so give the column a sensible default and the problem goes away. Zero for a number, "unknown" for a string, the epoch for a timestamp, false for a flag.

Why it breaks

Zero for a missing amount is a number, so it is summed. A hundred missing amounts drag an average down by a hundred zeros and the total is short by exactly the amount that was missing, with no trace (Breaking Schema Changes).

How it breaks with real data

Zero for a missing amount is a number, so it is summed. A hundred missing amounts drag an average down by a hundred zeros and the total is short by exactly the amount that was missing, with no trace (Breaking Schema Changes).
false for a missing boolean is an assertion. "We do not know whether this customer consented" becomes "this customer did not consent", or worse, the other way round (PII in Pipelines).
The epoch for a missing timestamp puts every affected record in a partition fifty years ago, where it is quietly excluded from every query anyone actually runs.
"unknown" as a string joins to nothing and groups into a category that appears in every breakdown, so a chart gains a bar that represents a pipeline defect (Dimension Tables).
A field is made nullable to accommodate a new code path. Every consumer that assumed non-null keeps working — filters silently drop rows, joins silently lose them, and an aggregate silently changes its denominator (Schema Evolution).
A null introduced by a failed join is indistinguishable from a null the source actually sent, so an investigation that starts downstream cannot tell whether the data was missing or the model was wrong (Missing Rows).

Source→Ingestion→Raw→Transformation→Validation→Storage Model→Serving→Consumer→Observability

What is actually happening

SQL null is unknown, not empty and not zero, and it propagates through three-valued logic: a comparison with null is neither true nor false, so a row with a null fails both col = x and col != x. Every filter is therefore an implicit non-null filter unless it says otherwise (SQL Transformations).
Aggregates skip nulls rather than propagating them. AVG over a column with nulls changes its denominator; COUNT(col) and COUNT(*) differ by exactly the null count; SUM over an all-null column returns null, which a coalesce somewhere downstream will convert to a plausible zero (Data Quality).
Joins drop nulls: a null key matches nothing, including another null key. A fact row whose dimension key is null does not appear in an inner-joined result and does not appear in the row count of what was lost, either (Fact Tables).
A default fills the gap at write time, which converts an absence into a value permanently. Once written, no query can distinguish it from a real observation, because the two are byte-identical. This is the irreversible step (Forward Compatibility).
The three reasons a value can be missing — not known, not applicable, not delivered — are semantically distinct and physically identical. Carrying the distinction requires a second field, and almost nobody adds one (Semantic Changes).

Three different facts, stored identically

A null in a column is not one fact. It is at least three, and the platform stores all of them as the same absence of bytes. Distinguishing them is the difference between an answerable question and an unanswerable one during an incident.

"Unknown" means the value exists in the world and the platform does not have it. "Not applicable" means the value does not exist for this row — a shipped date on a cancelled order. "Lost" means the value existed, was sent, and something between the producer and this table dropped it. The correct downstream behaviour differs for each: unknown should be excluded from a rate, not applicable should be excluded from the denominator entirely, and lost should be an incident.

A fourth case is easy to forget and easy to misdiagnose: the field did not exist yet. Every field added mid-history is null for everything before it, and without a schema version recorded per partition that is indistinguishable from a producer that stopped populating it (Forward Compatibility).

Why it is null	How it arises	What a consumer should do	How it is stored	How to make it distinguishable
Unknown	The customer did not provide it; the source system has no value.	Exclude from rates and averages; report the unknown share explicitly rather than hiding it.	A null	A status column, or an explicit "not provided" category alongside the real ones.
Not applicable	A shipped date on an order that was cancelled; a discount on an order with no promotion.	Exclude from the denominator entirely — these rows are not part of the population being measured.	A null. Byte-identical to the row above.	The row's own status makes it derivable, if the contract says the relationship exists.
Lost in the pipeline	A failed cast, a join that did not match, a producer that stopped populating the field.	Escalate. This is an incident, not a data characteristic (Missing Rows).	A null. Byte-identical to both rows above.	Null-rate monitoring plus a raw-layer comparison — the only way to tell, and only while raw is retained.
The field did not exist yet	A field added in March; every partition before March has no such column.	Report the period as out of scope rather than as an observation of absence.	A null, or a default if someone supplied one — in which case it looks like an observation.	Schema version recorded per partition. Without it this is permanently ambiguous (Metadata: Technical, Operational and Business).
Suppressed	Masking or redaction applied for governance reasons (Data Masking, Tokenisation & Encryption).	Report as suppressed, never as missing — the distinction matters to whoever reads the report.	A null, unless a masking sentinel was used.	An explicit masking indicator, which governance tooling usually can emit and usually is not asked to.

Making a field nullable is a breaking change

GENERALThree-valued logic is standard SQL and behaves identically across engines; the null-safe comparison operator is spelled differently in several of them, and count(*) filter (where ...) is not universal syntax. The behaviours being demonstrated transfer; the exact statements do not.

Relaxing a field from non-null to nullable feels like a widening, and widenings feel safe. Structurally it is one: every value that was valid before is still valid. Behaviourally it is not, because a null is not merely a new value — it is a value that changes the result of operations it participates in (Backward Compatibility).

The diff below makes the point with a required field becoming optional. Every consumer keeps running. Not one of them errors. Four of them return a different answer than they did yesterday, and only the one with an explicit non-null test finds out on purpose.

The SQL underneath shows the three mechanisms. A filter drops nulls because a comparison with null is unknown rather than true. A join drops nulls because a null key matches nothing, not even another null. An average drops nulls from its denominator as well as its numerator, so it reports a perfectly plausible figure computed over a population that quietly shrank.

A required field becomes optional

Before

order_id: string NOT NULL
placed_at: timestamp NOT NULL
amount_minor: integer NOT NULL
country: string NOT NULL

After

order_id: string NOT NULL
placed_at: timestamp NOT NULL
amount_minor: integer NOT NULL
country: string NULL

change country becomes nullable so a new guest-checkout flow, which does not collect an address, can emit orders. The producer regards this as an additive relaxation, and structurally they are right.

Consumer	Effect	How it shows up
Revenue by country dashboard	Guest orders group into a null bucket that the BI tool renders as blank or hides entirely, so the sum of the visible bars is less than the reported total.	Silently — no error, wrong result
Model filtering `where country != 'US'`	Silently excludes every guest order, because a comparison with null is unknown rather than true. The non-US total is now short by the guest share.	Silently — no error, wrong result
Join to a country dimension	An inner join drops every guest order from the fact table. Row counts fall and there is no record of what was dropped (Dimension Tables).	Silently — no error, wrong result
Average order value by country	The denominator changes because nulls are skipped, so the reported average is over the population that happened to have a country.	Silently — no error, wrong result
Ingestion job with a NOT NULL constraint on the target column	Rejects the batch on the first guest order. The only consumer that gets an error, and the only one repaired deliberately.	Loudly — it raises

The three mechanisms, and the explicit forms that survive a nullable field

1-- 1. FILTERS. A comparison with null is unknown, not true, so this
2--    excludes every row where country is null. Nothing warns you.
3select sum(amount_minor) from fct_orders where country != 'US';
4 
5--    Explicit version: decide what a null means here and say so.
6select sum(amount_minor)
7from fct_orders
8where country is distinct from 'US';        -- null is treated as not-US
9 
10-- 2. JOINS. A null key matches nothing, not even another null key.
11--    An inner join silently removes those rows from the result and
12--    from the row count you would use to notice.
13select o.order_id, d.region
14from fct_orders o
15join dim_country d on d.country = o.country;
16 
17--    Explicit version: keep them, and count them.
18select o.order_id, coalesce(d.region, 'unassigned') as region
19from fct_orders o
20left join dim_country d on d.country = o.country;
21 
22select count(*) filter (where o.country is null) as orders_without_country,
23       count(*) filter (where o.country is not null and d.country is null)
24                                                   as orders_with_unmatched_country,
25       count(*)                                    as orders_total
26from fct_orders o
27left join dim_country d on d.country = o.country;
28 
29-- 3. AGGREGATES. avg skips nulls in numerator AND denominator, so it
30--    reports a plausible average over a population that quietly shrank.
31select avg(amount_minor)                       as avg_over_known_only,
32       sum(amount_minor) / count(*)            as avg_over_all_rows,
33       count(*) - count(amount_minor)          as rows_with_no_amount
34from fct_orders;

The third query is the pattern worth keeping: report the aggregate, the population it was computed over, and the size of the gap between them, in one result. A consumer who can see all three cannot be misled by any of them.

A default is a fabricated fact

The reason defaults are so tempting is that they make every problem above disappear. Filters behave, joins match, averages are stable, and the BI tool renders a clean chart. What has actually happened is that an absence has been converted into an observation, and no query written afterwards can undo it.

The test to apply is simple: if this value were real, would anyone be able to tell? If a default of zero is indistinguishable from a genuine zero, it will be summed as one. If a default of "standard" is indistinguishable from a real standard shipment, it will be counted as one. A default that is distinguishable — an out-of-domain sentinel, a separate status column, a null — preserves the information; one that is plausible destroys it.

There is one case where a default is unambiguously right: when the contract genuinely specifies it. If the producer states that an omitted discount_minor means zero discount, then zero is not a fabrication, it is the documented encoding of a real fact. The difference is whether the default expresses the producer's intent or covers up the platform's ignorance (Data Contracts).

What each null-related check sees, and what it does not

Check	Expresses	Catches	Still misses
Null rate per column, per run, against a per-column threshold	The field is populated about as often as it should be.	A rename, a failed cast, a producer that stopped populating a field, a join that stopped matching — four different failures with one query.	Everything filled by a default: a defaulted column has a null rate of zero and looks perfectly healthy (Breaking Schema Changes).
Not-null assertion on fields the contract declares required	The producer is honouring the nullability clause of the contract.	The first null from a code path nobody anticipated, at the boundary, before it reaches a model.	A null that arrives as a default instead — the producer filled it, so the assertion passes and the value is invented.
Distinct-value count and top-value share on low-cardinality columns	The value distribution looks like a real distribution.	A sentinel or a default flooding a column with one implausibly dominant value (Distribution Tests).	A default that matches the genuinely dominant value, which is what a well-chosen default usually is.
Unmatched-row count on every join	The join is matching what it is supposed to match.	Rows lost to null keys and rows lost to keys that exist but do not match — two very different problems that produce the same shortfall.	A join that matches the wrong row rather than no row, which produces no unmatched count at all and a completely wrong answer (Fact Tables).
Row count of the aggregate's population alongside the aggregate	How many rows this number was actually computed over.	A denominator that quietly changed — the failure that moves a metric without moving anything anyone monitors.	Nothing about whether the rows that were included were the right ones. It reports the size of the population, not its correctness.

The first row is the cheapest broad detector in the domain and the second row is the only one that can act before data lands. Neither of them sees a default, which is why the design advice is to prefer null at write time rather than to plan on detecting fabrication later.

The producer sometimes has no value for this field

How should the absence be represented, given what consumers will do with it?

Null, with the reason in a second column

when The absence has more than one possible cause and consumers need to behave differently for each.

cost Two columns instead of one, and every consumer must learn to read both. The most correct option and the one most often skipped for that reason.

Null, unqualified

when There is only one plausible reason for absence, and consumers agree on what to do with it.

cost Filters, joins and aggregates all behave differently around it, so every consumer must handle it explicitly or be silently wrong.

A contract-specified default

when The producer genuinely means something by the omission — an absent discount is no discount — and that meaning is written in the contract.

cost Nothing, provided the contract really says so and the meaning never changes. If the meaning later changes, it is a semantic change with no schema difference (Semantic Changes).

An out-of-domain sentinel

when The pipeline or storage format cannot express null, which is a real constraint in some file formats and legacy interfaces.

cost It only works while the sentinel stays out of the valid domain, and every consumer must know it. A sentinel inside a measure's range will eventually be summed by somebody.

A plausible in-range default

when Essentially never for a measure. Occasionally defensible for a low-stakes display attribute where the alternative is a broken chart.

cost Irreversible loss of the distinction between observed and absent. Choose this only where nobody will ever aggregate the column (Forward Compatibility).

How to build it

Most important first.

Declare nullability in the contract and enforce it at the boundary, because a field documented as non-null and not enforced will eventually contain a null from a code path nobody anticipated (Contract Enforcement).
Treat making a field nullable as a breaking change requiring notice, exactly as removing it would be. Widening a domain to include null changes the behaviour of every filter, join and aggregate over it (Backward Compatibility).
Prefer null over a plausible default when the value is genuinely unknown. Null is honest, it is visible in a null-rate chart, and it makes consumers decide explicitly. A default is convenient and silent (The Data Quality Dashboard).
Where the distinction matters, carry the reason in a second field — a status or a source-flag column — rather than in a sentinel value. Never use an in-range sentinel for a measure: zero, -1 and 9999 are all values, and every one of them will eventually be aggregated by somebody who did not read the documentation.
Monitor null rate per column with a per-column threshold, and treat a step change in it as a schema or producer event rather than as noise (Data Tests).
Make every join that could lose rows explicit about it: count what did not match, and fail or alert when the unmatched share crosses a threshold, rather than discovering the loss as a smaller total (Reconciliation).

What this actually promises

Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.

A non-null constraint enforced at the boundary guarantees the field is populated in everything that got through. It guarantees nothing about batches that were rejected, and nothing about what the value means (Transport Validation).
A default guarantees the field is populated. It explicitly does not guarantee the value was observed, and conflating those two is the entire failure of this lesson.
Nothing in a column guarantees why it is null. That information exists only if a second field carries it or if the schema version tells you the field did not exist yet (Metadata: Technical, Operational and Business).
Nothing guarantees a consumer handles null the way you intended. Filters, joins and aggregates each treat it differently, and a consumer using all three treats it three ways in one query.

Can I trust it?

A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.

The check that would catch this

The check is null rate per column per run, against a per-column threshold. It is the cheapest and most informative check in the entire domain: it detects renames, failed casts, producers that stopped populating a field, and joins that stopped matching (Data Tests).
It misses every null that was filled by a default, because a defaulted column has a null rate of zero and looks perfectly healthy while being partly invented.
It also misses the *reason*, which is what you actually need during an incident. A null rate tells you that a fifth of the rows are empty; only the raw payload tells you whether the producer sent them empty (The Raw Landing Zone).

Freshness

Nullability decisions add no latency. What they change is what a consumer can ask of the data at a given moment: a field that is null until a later stage populates it means a consumer reading early sees an absence that is really a timing artefact (Late-Arriving Data).
Fields that are legitimately null at write and populated later — a shipped date on an unshipped order — make "is this null because it has not happened yet" a freshness question rather than a quality one, and the two are constantly confused.
Enforcing non-null at the boundary converts a null into a rejected batch, which trades freshness for correctness in the same way every other check in this module does (Contract Enforcement).

When the schema or meaning changes

Widening a field to allow null is a domain change and therefore a compatibility event in both directions: new data may contain nulls old consumers never expected, and new consumers reading old data see a field that was never null and may have assumed it never could be (Forward Compatibility).
Narrowing a field back to non-null is harder, because it requires history to contain no nulls, and history almost always does.
Adding a field to an existing dataset makes it null for all history by definition. What you do about that — leave it null, backfill it, or default it — is the single most consequential decision in this lesson (Backfills).

How to re-run this safely

A null that was never filled is recoverable if raw retains the original payload: reprocess and the real value returns (Reprocessing vs Retrying).
A null that was filled with a default is generally not recoverable, because the fill destroyed the distinction between observed and absent. This is the asymmetry that should decide the design.
Recovering a null introduced by a broken join means fixing the join and reprocessing, and it requires knowing that the join was the source — which requires having counted unmatched rows at the time (Lineage Debugging).
When history has to be corrected, publish the correction atomically and tell the consumers who saw the earlier numbers, because a null becoming a value changes every aggregate over it (Atomic Publish).

What can go wrong

Failure modes

A default applied at write time, permanently erasing the difference between absent and observed.
A filter that silently excludes nulls because a comparison with null is neither true nor false, or a join that drops rows on a null key with no count of what was dropped.
An AVG whose denominator changes when a field starts arriving null, moving a metric without moving a row count.
A non-null constraint documented and never enforced, discovered when the first null arrives years later.
The mitigation's own failure: a strict non-null check that rejects a batch containing one legitimately null record, and gets relaxed to a threshold that then never fires (Alert Fatigue: The Page Nobody Reads).
A sentinel value inside the valid range of a measure, aggregated by someone who did not know it was a sentinel.

Misreads

"Null is the same as zero." Null is unknown. Zero is an observation that the value was zero. Treating them as equivalent is the mechanism behind most silently wrong monetary aggregates (Breaking Schema Changes).
"A default makes the data cleaner." A default makes the data *look* cleaner and destroys information irreversibly. Clean data with fabricated values is dirtier than honest data with gaps (Forward Compatibility).
"Making a field nullable is a minor change." It changes the behaviour of every filter, join and aggregate over that field, for every consumer, without any of them being told (Backward Compatibility).
"The null rate is fine, so the column is fine." A defaulted column has a null rate of zero. So does a column filled with a sentinel. Null rate detects absence, not fabrication (Semantic Changes).
"WHERE status != 'cancelled' includes everything that is not cancelled." It excludes every row where status is null, because a comparison with null is unknown rather than true.

Privacy, retention and access

A default on a consent or preference field is a fabricated assertion about a person, and which direction it defaults in is a compliance decision rather than an engineering one (PII in Pipelines).
Nulls introduced by masking or redaction are a fourth reason a field can be empty, and one that must be distinguishable from the others so that a consumer does not report suppressed data as missing data (Data Masking, Tokenisation & Encryption).

Operating it

How you see it in production

Null rate per column per run, charted over time, with a threshold per column. The single highest-value chart in the domain (The Data Quality Dashboard).
COUNT(*) against COUNT(col) for key columns, which is the same signal expressed in a form that makes the gap explicit.
Unmatched-row counts on every join in a model, emitted as a metric rather than inspected during incidents (Pipeline Metrics).
Distinct-value counts on low-cardinality columns, which is how a sentinel or a default announces itself as an implausibly dominant value (Distribution Tests).

What changes at 10x and 100x

At 10x volume nothing about the semantics changes; what changes is that a small null rate becomes a large absolute number, and a threshold expressed as a count rather than a rate starts firing constantly.
At 10x consumers, the number of different interpretations of the same null grows with the number of people writing queries, and the contract clause becomes the only thing keeping them aligned (Data Contracts).
At high cardinality, a null key in a join stops being a rounding error: a dimension miss on a large fact table can drop a meaningful share of rows, and an inner join will not tell you (Fact Tables).

What drives cost here

Nulls cost a little storage in most columnar formats — a definition level or a validity bitmap rather than a full value — so the physical cost of honesty here is genuinely small (Dictionary, Run-Length, Delta and Bit Packing).
A second column carrying the reason costs one more column, which is the correct comparison: one column against a permanently ambiguous dataset.
The expensive item is the investigation that a defaulted column forces later, which is unbounded because the information needed to conclude it no longer exists.

What this approach costs

Preferring null over a default is more honest and strictly more annoying. Every downstream consumer now has to handle a null they would not otherwise have seen, and some of them will handle it by coalescing to the same plausible value one layer further down — where it is even less visible.
Enforcing non-null at the boundary converts a partially-empty batch into no batch. That is the right trade for a measure feeding a published metric and the wrong one for a field nobody reads.
Carrying the reason in a second column is correct and doubles the number of things a consumer has to understand about that field. Many will read only the first one.

Where this applies

Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.

GENERALThree-valued logic, aggregates skipping nulls and joins dropping null keys are properties of SQL semantics and behave the same across engines. What differs is the ergonomics — some engines make null-safe comparison easy and some make it verbose — which changes how often people get it right, not what is correct.
FORMAT-SPECIFICAvro expresses nullability as a union with null and can carry a per-field default that a reader applies when the writer omitted the field; Parquet encodes it as a definition level per value, so a mostly-null column costs little; CSV cannot distinguish an empty string from an absent value at all, which is why nullability arguments over CSV never resolve.
WAREHOUSE-SPECIFICWhether a warehouse enforces a NOT NULL constraint on load, or accepts it as documentation and does not check it, varies by product — and an unenforced constraint is more dangerous than none, because consumers read it as a guarantee. Confirm which yours does before relying on the declaration.

Where the depth lives

This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.

Databasepostgresql-types-and-tables relationships-and-keys joins

Backendparse-dont-validate transport-validation validation-layers deserialization

API Designremoving-fields validation-errors error-model enum-evolution

Securityinput-validation

Domains that do not exist yet

— Distributed Systems owns the case where a null arrives because a partial write or a partitioned replica returned an incomplete record rather than an error — an absence produced by the transport rather than by the producer.