A pattern extraction over a 2,000,000-row text column raises nothing, yet 340,000 rows come back with no value — what do you check?
answer
- no error is not no failure
- an extraction is total, not validating
- count absent before, count absent after
- sample the misses; they cluster
- bound the miss rate as an assertion
basics
~20 sExtraction over a column is total, not validating: a row the pattern does not fit yields no value rather than an error, so misses are silent. Check how many of those rows held no value beforehand, sample the rest, and put a bound on the miss rate so the next run fails loudly.
solid answer
~50 sA column-shaped extraction has to return something for every position, so a row the pattern does not fit comes back with no value. That is an ordinary result, not an error, which is why 340,000 failures cost you nothing at run time and everything downstream. First separate the two populations: count the rows that already held no value before the extraction, and subtract, because the difference is the real miss count. Then sample the misses — they almost always cluster into three or four shapes, such as untrimmed whitespace, a different separator, a prefix one source adds, or a different casing. Normalise before extracting rather than widening the pattern until the miss count reaches zero, which is how garbage starts matching. Finally make the rate an assertion, so a future input that drifts stops the job.
go deeper
Recall that an extraction over a column returns something for every row, so a row the pattern does not fit comes back with no value instead of raising. Silence is not success.
Explain how to measure it: absent in the source against absent in the result, the difference being the real misses, and normalise before extracting so the pattern carries less variation.
Demonstrate the operational habit — sample the misses, group them by upstream source, bound the rate with an assertion, and keep the non-matching rows instead of dissolving them into absence.
The judgment is where the threshold sits and who owns the rejects. A bound too tight halts on noise, a bound too loose is decoration, and an unowned rejects output is the same as discarding it.
## Why nothing was raised An extraction applied to a whole column must produce one result per position — that is what makes it a column at all. So the operation is **total**: every row gets an answer, and the answer for a row whose value the pattern does not fit is *no value*. Nothing exceptional happened from the tool's point of view. A pattern that fits 83% of the rows is indistinguishable, at run time, from a pattern that fits all of them. This is the central fact about text extraction as a column operation, and it is the opposite of how the same job behaves when you write it per record in your own program, where you would naturally have written a branch for the non-matching case and probably logged it. ## First, separate two populations that look identical The 340,000 rows with no value are a mixture: - rows that **had no value to begin with** — the source never supplied one; - rows that **had a value the pattern did not fit** — the genuine misses. They are indistinguishable in the output and have completely different remedies. Measure both: 1. Count the rows with no value in the **source** column before the extraction. 2. Count the rows with no value in the **result**. 3. The difference is the true miss count, and the ratio of that difference to the rows that did carry a value is the miss rate that matters. A related trap in the other direction: some designs hand back a zero-length value rather than a no-value marker on a non-match, and a zero-length value is not skipped by the things that skip absence. If your count of "no value" in the result looks implausibly low, check whether the misses are hiding as empty values instead. ## Then read the misses rather than guessing at them Take a sample of the rows whose source value was present and whose result is absent, and look at them. In practice they collapse into a handful of shapes: - **whitespace that was never trimmed**, so the value does not begin where the pattern expects; - **a different separator or a different order of the parts** from one upstream source among several; - **a prefix or suffix** one system adds and another does not; - **a different casing** than the pattern assumes; - **a legitimately different kind of value** that was never meant to match — a placeholder, a marker for unknown, a free-text note in a field that is otherwise structured. The last one matters most, because it is not a bug in the pattern. It is a signal that the column holds more than one kind of thing, and no amount of pattern work will make one extraction correct for all of it. ## Order the clean-up so the pattern has less to absorb A large share of misses disappear by **normalising before extracting**: trim the surrounding whitespace, fold to one case, collapse repeated internal spaces, unify a separator that appears in two forms. Each of those is a cheap whole-column job with an obvious, checkable effect. The alternative — encoding every variant into the pattern itself — makes the pattern the only place the data's messiness is documented, and it is the hardest place to read it. The temptation to avoid is **widening the pattern until the miss count reaches zero**. A pattern loose enough to match everything extracts something from values that should have been rejected, and those wrong extractions are far more expensive than the misses, because they are not absent and nobody counts them. ## Make the next run loud This failure is cheap to catch and cheap to prevent, and the prevention is the part a senior answer is expected to name: 1. **Assert a bound.** After extraction, assert that the miss rate among present values is below a threshold you chose deliberately. A run that drifts past it fails instead of publishing. 2. **Route the misses somewhere.** Keep the non-matching rows as their own output rather than letting them dissolve into absence. A rejects output that is checked is worth more than a log line that is not. 3. **Record the rate over time.** A miss rate that is stable at 2% is a known population; the same 2% jumping to 17% is a new upstream source, and the number is the only thing that tells you. 4. **Decide the policy explicitly.** Repair, reject, or accept — but written down, because the default is accept-and-say-nothing. ## What varies between tools Designs differ in whether a non-match yields a no-value marker, a zero-length value, or — where the surface offers it — a raised error you can opt into. Some let you ask for a flag column saying whether each row matched, which turns the whole problem into a count. Before you trust a miss count, establish which of those your tool did; the number means something different in each case.
- Why is widening the pattern until nothing misses a worse outcome than the misses?Because a miss is absent and countable, while a wrong extraction is a plausible value nobody inspects. Loosening a pattern until every row yields something guarantees it now extracts from values that should have been rejected, and those rows travel downstream as ordinary data. Misses are a visible cost; false extractions are an invisible one.
- The miss rate is a steady 2% and everyone has accepted it. What still needs doing?Pin it. Record the rate per run and assert a bound just above it, so the first input that drifts stops the job rather than quietly publishing a worse number. Also sample the 2% once properly — a stable rate is often one identifiable upstream source, and naming it converts a tolerated loss into a fixable one.
- How would you tell whether the misses come from one upstream source rather than being spread evenly?Group the rows by whatever identifies the source — the origin field, the load date, the file the row arrived in — and compare the miss rate per group against the overall rate. Evenly spread misses point at the pattern; a rate concentrated in one group points at that source's format, which is a different fix and usually a cheaper one.
saying these in an interview costs you the question
- Reads the absence of an error as proof that every row matched
- Counts absent results without subtracting values that were already absent
- Assumes a non-match is always reported or logged somewhere
- Widens the pattern until the miss count reaches zero
- Extracts first and normalises case and whitespace afterwards
- Treats a stable miss rate as evidence that nothing needs an assertion