Why does a column-by-column transformation pass leave identity in free text, attachments and derived columns?
answer
- The pass only knows the columns
- People type names into comment boxes
- Stored documents are opaque to it
- Columns computed from originals rebuild them
basics
~20 sA column pass replaces values in fields somebody classified. It cannot read inside free-text notes or stored documents, and it does not recompute columns built from the values it changed, so names, numbers and reconstructable values survive in all three places.
solid answer
~50 sThe pass works from a list of classified columns and applies a replacement rule to each, so three things fall outside it. **Free text** -- a comment, a support note, a delivery instruction -- carries names, phone numbers and details of a complaint in a place no column classification reaches, written by people who have never heard of the pass. **Attachments** stored against a row are opaque: the pass sees a reference, not a scanned form or a photograph. **Derived columns** are computed from values that were replaced -- an initials string, a username built from a name, a search index, an age that narrows a date of birth back down -- and a pass aimed at the source column leaves the stored copy untouched. Each needs handling by name: replaced wholesale, substituted, or recomputed after the pass from already-transformed sources.
code
pseudocode · 11 linestransformDataset(source):
for each column in classifiedColumns(source):
apply(ruleFor(column), column) // the part everyone remembers
// the three the pass does not reach on its own
for each column in freeTextColumns(source):
replaceWholeValue(column, generatedProseOfSimilarShape())
for each attachment in attachmentsOf(source):
substitute(attachment, sampleDocumentWithNoRealSubject())
for each column in derivedColumns(source):
recompute(column, from = alreadyTransformedSources(column))go deeper
Know that a comment box, an uploaded document and a column computed from a name can all hold personal data after the named columns have been replaced. Being able to point at those three places is what is expected at this level.
Explain the mechanism: the pass works from a list of classified columns, so it cannot read inside unstructured content and does not recompute columns built from the values it changed. Have a concrete example of each of the three ready.
Show how you find them before they ship: inventory by content rather than by column name, search the output for known original values, and read a hand sample of free text. Interviewers expect a story about a column nobody had classified.
Own the policy for unstructured content: replaced wholesale by default, real text kept only by exception and by name, attachments substituted, derived columns recomputed from transformed sources -- plus a standing rule that any new column is unhandled until somebody says otherwise.
## What a column pass can and cannot see A transformation pass takes a list of columns somebody classified and applies a replacement rule to each. Its unit of work is the column. Everything it does well follows from that, and so does everything it misses: it has no opinion about content it was not pointed at, no ability to open what it treats as an opaque blob, and no notion that one column can be computed from another. | Blind spot | Why the pass misses it | What survives inside it | | --- | --- | --- | | Free text | The column was classified as a comment, not as an identifier | Names, phone numbers, addresses, details of a complaint | | Attachments and stored documents | The row holds a reference; the content is opaque | Signed forms, scans, photographs, exported statements | | Derived and cached columns | Not on the classification list, or written before the pass ran | Initials, display labels, search terms, reconstructable dates | ## Free text is written by people who do not know a pass exists Comment boxes, support notes, delivery instructions, cancellation reasons, internal annotations: these carry whatever the writer needed to record, including the exact information the classified columns had removed. An agent types the caller's name because the account number was not to hand. A courier note holds a phone number and a door code. A cancellation reason describes a bereavement. Searching that text for known patterns finds some of it, and finds only what was anticipated -- the phrasing you predicted, in the language you predicted, spelled the way you predicted. As a supplement it is fine. As the control it is not, because a clean result is indistinguishable from a result whose personal content you failed to recognise. The defensible default is to replace the whole value with generated prose of similar length, language and structure, so the paths that render, truncate, index and search that column are still exercised. Keep real text only where a specific test needs a specific phrase, and then author that one row deliberately. ## Attachments travel with the rows and nobody looks inside If a row can carry an uploaded document, an extract of that table carries the documents too -- or, worse, carries references that still resolve to the originals in shared storage. The content is a photograph of an identity document, a signed contract, a letter from a clinic, a screenshot with somebody's mail client visible behind it. None of that is reachable by a rule written against a column. Two things have to be settled explicitly: whether the extract carries attachment content at all, and whether the references inside it still point at real stored objects. Substituting a small set of sample documents belonging to no real subject is usually the cheapest correct answer, and it keeps the extract small as a side effect. ## Derived columns quietly rebuild what you removed This is the subtlest of the three, because the column looks harmless and the pass looks complete. The recurring examples: - an initials or display-name column built from the real name and never recomputed; - a username, handle or email local part generated from the name; - a search index or denormalised search column holding the original text; - an age, band or tenure figure computed from a date of birth, from which the date can be narrowed back; - a cached label on a related row -- an order storing the customer name it was placed under; - an exported summary file stored alongside the data and refreshed on its own schedule. The rule of thumb is that anything written *by code* rather than by a user is a candidate, and anything predictable from another column is a candidate. Handle them by recomputing after the pass from the already-transformed sources rather than by transforming them independently. Independent transformation is how a name and its own initials end up disagreeing, which is both a leak signal and a test failure waiting to happen. ## Making the blind spots visible 1. Inventory by *content*, not by column name: which columns are free text, which reference stored content, which are computed. 2. Search the transformed dataset for a handful of known original values. If any of them appears, something retained or rebuilt it. 3. Read a hand sample of free-text values -- a few dozen. It is unglamorous and it is the fastest way to learn what your users actually write. 4. Re-run the inventory whenever the schema changes. A column added by another team defaults to *not handled*. ## The point The pass is not wrong; it is narrow. Its output means every classified column was transformed, and nothing more than that. Identity lives in the places a column view does not reach, and those places have to be handled by name, deliberately, and re-checked whenever the shape of the data moves.
- How do you discover which columns in a dataset are derived from the ones you transformed?Trace them rather than guess. Look for columns written by application code rather than by a user, caches and denormalised display columns, search indexes, and anything whose value is predictable from another column. A cheap empirical check is to search the transformed output for a handful of known original values: if any of them turns up, something retained or rebuilt it.
- A comment column is needed for a test -- how do you keep it without keeping the names in it?Replace the values, not the column. Generate text of similar length, language and structure so the paths that render, index, search and truncate it still run, and keep the original prose out of the extract entirely. If one specific real phrase matters to a test, author that single row by hand rather than carrying the whole column across.
A proofreader who only checks the headings will happily pass a document whose body is full of errors; a column pass reads the headings of your data.
saying these in an interview costs you the question
- Assumes every field holding personal data was on the classification list
- Believes a comment box only ever holds product feedback
- Forgets that stored documents travel with the rows
- Replaces a name column but leaves a column computed from it
- Treats one search of the free text as proof it is clean
- Leaves attachment references pointing at the real stored originals