The business is not lying, it is describing one path
"That field is always filled in" is nearly always true of the process the speaker works in. The claims handler who told you is describing the screen they use, where the field is mandatory and the form will not submit without it. What they cannot see is the other three ways rows arrive in that table: a bulk upload from a broker, a legacy migration from the system replaced in 2019, and an automated path for low-value claims that skips the screen entirely. Each of those is a legitimate business process and none of them enforces the field.
Starting from that assumption changes the investigation from a data quality audit into a discovery of process, which is what it actually is. Your job is not to establish that the field is null a third of the time — you already know that — but to find out which populations the nulls belong to, because the answer determines whether you have found a defect, an undocumented business path, or a boundary on what your work can honestly cover.
Segment before you conclude
A single aggregate null rate is nearly useless. The informative step is to cut it several ways and look for clustering, because clustered nulls have causes and evenly distributed nulls have different ones.
SELECT
DATE_TRUNC('month', created_at) AS month,
source_system,
COUNT(*) AS rows_total,
COUNT(*) - COUNT(adjuster_id) AS rows_null,
ROUND(100.0 * (COUNT(*) - COUNT(adjuster_id)) / COUNT(*), 1) AS pct_null
FROM claims.claim
GROUP BY 1, 2
ORDER BY 1, 2;
Four cuts earn their place. By time, because a rate that is zero until a particular month and steady afterwards points at a release, a migration or a new integration going live. By source system or creating process, because that is where undocumented paths appear as a single value with a 100% null rate. By record type or category, because the field may be genuinely inapplicable to some kinds of row. And by the record's own lifecycle state, because a field populated at closure will be null for everything still open, which looks like a defect and is nothing of the kind.
The shapes map fairly reliably onto explanations. Nulls confined to one source system are that system not sending the field. Nulls confined to rows before a date are a migration that did not backfill. Nulls confined to one category mean the field does not apply and the schema never said so. Nulls spread evenly across every cut are the genuinely worrying case, because they suggest an optional path through the interface, a race in the write, or a downstream process that clears the value.
This is the step that separates an engineer doing data work from one doing careful data work, and it matters most when the field feeds a model or a metric. Missing values that are unrelated to what you are predicting are an inconvenience: you drop the rows or impute, and you lose precision. Missing values that correlate with the outcome are a hazard, because dropping them changes the answer rather than the confidence in the answer.
Check it directly. Compare the outcome rate for rows where the field is populated against rows where it is not, and compare the two populations on whatever other attributes you have. If claims missing an adjuster identifier are settled faster, cheaper and are overwhelmingly low value, then the null is not an accident — it marks the automated fast path, and a model trained only on populated rows has learned about manually handled claims and will be applied to a population that includes the others.
| What the cut shows | Most likely cause | What it means for the work |
|---|
| One source system, 100% null | That integration never sends the field | Get it added, or exclude that source explicitly and say so |
| All rows before a cutover date | Migration did not backfill | Restrict history, or backfill from the archive if it exists |
| One record type only | Field is inapplicable by design | Not a defect; encode it as a category, not as missing |
| Only rows in an open state | Populated later in the lifecycle | Filter on state; the field is fine |
| Evenly spread across every cut | Optional capture, or a write path that clears it | Genuine quality defect, needs a source-side fix |
Take it back as a question about their process
How you report this determines whether you get an answer or an argument. "Your data is bad, this field is a third empty" invites defence and produces nothing useful, partly because the person you are talking to knows the field is mandatory on their screen and will reasonably conclude you have queried the wrong table. What works is showing them the segmentation and asking them to name the process: "rows from BROKER_UPLOAD never carry an adjuster, and rows before March 2019 never do either. What creates the broker rows, and is there anyone who would know about the 2019 cutover?"
That version is answerable, it locates the gap in a process rather than in their competence, and it very often produces an explanation in one sentence from someone who has known it for years and never thought to mention it. It also protects you: if it turns out the field genuinely should always be present, you have handed them a defect with the evidence attached and a date range to investigate, which is a favour rather than a complaint.
Deciding between a pipeline fix, a source fix and modelling around it
Once you know the cause, the remedy follows from where the truth is. If the value exists somewhere else in their estate — on the policy, in an audit table, derivable from the assignment history — the honest fix is in your pipeline, with the derivation documented so the receiving team knows it is inferred rather than captured. If it exists nowhere, no amount of pipeline work invents it, and the choice is between getting the source changed, which is slow and outside your control, and building something that does not depend on the field, which is usually the right call on an engagement measured in weeks.
The one option to avoid is imputing quietly. Filling a third of the rows with a default, a mode or a placeholder produces a dataset in which the missingness is no longer visible to anyone who inherits it, and the failure surfaces later as a metric nobody can reconcile. If you must fill, keep the original column and add a flag, so that the fact of imputation is queryable rather than lost.
The mistake that survives all the way to production
The most common serious error here is deciding this is a data problem when it is a scope problem. A third of the rows behaving differently is rarely a blemish on a dataset that is otherwise fit for the purpose you were given — it is usually a second business process that nobody described to you during scoping, and it may be the one the customer actually cares about. An engineer who drops the nulls and proceeds delivers something that works on two thirds of the business and gets discovered in the demo, by the one person in the room who owns the broker channel.
The corrective instinct is to treat any large, clustered irregularity in the data as evidence that the requirement is incomplete, and to go back and re-ask the scoping question rather than to clean around it. The data is the most honest description of the customer's operations available to you, and where it disagrees with the briefing, the briefing is the thing that needs revising.
A null rate that clusters is telling you about a process; a null rate that does not is telling you about a defect. Segment before you clean, because the version of this you fix quietly is the version that reappears in the demo.
© 2026 Preptima. Originally published at preptima.com (opens in a new tab).