As a lead on a customer data team, how do you decide whether SQL string cleaning belongs in each query, a generated column or the ingest pipeline?
answer
- how many consumers need one answer
- does the step lose information
- who can write around the rule
- what changing the rule costs
- keep the raw value
basics
~20 sDecide by how many consumers need one definition and whether the step loses data. Keep lossy, contextual cleaning in the pipeline with the raw value retained, deterministic row-level forms in generated columns, and display formatting in queries.
solid answer
~50 sI decide per rule with three tests: how many consumers need the same definition, whether the step loses information, and who can write around it. Display formatting, such as a rebuilt full name in one report, stays in the query because nothing else depends on it, though copied logic drifts between reports. A deterministic form computed from one row, such as a trimmed, lower-cased match key, suits a generated column: the database applies it for every writer, and a stored one can back a unique constraint. Lossy or contextual work, such as splitting names, repairing codes or rejecting bad rows, belongs in the pipeline, with the raw value kept, because it needs reject paths and reruns. The costs are real: changing a generated column's rule is a schema change, and a pipeline misses direct writes. I record the outcome per column, so nobody re-decides it per report.
go deeper
Be aware that cleaning can run in a query, a generated column or a pipeline. Know what TRIM and LOWER do in a query, and that a report's expression affects only that report.
Explain what a generated column is and why it applies for every writer, and why a split that guesses at a name should keep the raw value beside its result.
Trace disagreements between reports back to duplicated cleaning, and propose moving one rule to a shared home. Plan the backfill, since old and new forms do not compare equal until it finishes.
Set a default per kind of rule and state what would overturn it, weighing writer count, lossiness, rule churn and retention limits. Then make the rule findable and tested, so that it survives staff changes.
## The decision String cleaning means turning imported text into a form the business can rely on: trimming codes, folding case for matching, splitting and rebuilding names. Any of it can run in three places, and a team that never chooses ends up with all three disagreeing. There is no single right answer, so a lead's job is to set a default and say which facts would change it. | Place | Strength | Weakness | Fits | |---|---|---|---| | **In the query** | Trivial to change; no write path involved | Copies drift; cost is paid on every read; cannot back a constraint | Display formatting, one-off analysis | | **Generated column** | Applied for every writer; one definition; a stored one can back a constraint | A rule change is a schema change; the expression must be deterministic and limited to one row | Match keys and canonical forms | | **Ingest pipeline** | Handles context, lookups, rejects and reruns | Misses other write paths; a rule change needs a backfill | Lossy repairs, validation, splitting | A **generated column** is a column the database computes from other columns in the same row, under names such as computed or virtual column, depending on the engine. ## The tests I apply Take each rule on its own, because one table usually needs more than one answer: - **How many consumers need one definition?** One report can keep its own expression. Three reports with three `TRIM` expressions will eventually disagree, and that disagreement surfaces as totals nobody can reconcile. - **Does the step lose information?** Trimming a space is close to lossless; splitting `'Mary Ann Smith'` guesses at a person's name and can be wrong. Anything lossy needs the raw value kept beside it, so a corrected rule can rerun. - **Who can write around it?** If a support tool, a backfill script and a second service all write the table, a pipeline rule covers only one of them. A generated column covers all of them. - **Does it need other rows or tables?** A reference lookup, a reject queue or a dedupe across rows cannot live in a generated column. - **Does anything enforce on it?** A uniqueness rule on a cleaned email needs a stored form the database can hold, which a query expression cannot provide. - **How often will the rule change?** A rule still being learned is cheaper to change in a query or a pipeline than in a schema. ## A default for a customer import For a typical import, I would propose this and then let the team argue with it: 1. **Keep the raw columns** exactly as received, for as long as retention rules allow. 2. **In the pipeline**: split full names, repair codes, pad to width, and route rows that fail validation to a reject table with a reason. 3. **In a stored generated column**: the match key, such as a trimmed and lower-cased email, with a unique constraint on it. 4. **In a query or view**: the display name, because presentation rules differ by consumer and nothing depends on them. ## The costs worth naming Each choice has a cost that tends to arrive later: - **Generated columns tie cleaning to locale behaviour.** Case mapping depends on collation rules, so a collation or library change can leave stored values disagreeing with what the expression would now produce. - **Schema changes are not free.** Changing the expression can rewrite the column, so it needs planning on a large table. - **Pipelines create a gap.** Rows from before a rule change need a backfill, and until it finishes, old and new forms sit side by side and comparisons between them fail. - **Query-level rules hide.** The same logic pasted into twenty reports cannot be found or corrected in one place. A shared view reduces this, at the price of one more object to own. ## What would change my mind The default is a starting point, not a principle, and these facts would move it: - A single writer and a small team make a pipeline rule much safer, because no other write path exists to bypass it. - A heavy analytic read load may justify storing what would otherwise stay in a query. - Strict retention or privacy rules can limit how long raw values may stay, which raises the bar for lossy steps. ## Making the rule stick A rule nobody can find is not a rule. I would keep one short table per dataset listing each cleaned column, where it is computed, who owns it and what its raw source is. Then I would add tests with fixtures covering NULL, multi-byte names, one-word names and all-zero codes, and a review habit that rejects a new private copy of a rule that already has a home. The review cost is small next to reconciling two reports that each cleaned the same column their own way.
- What would make you move a cleaning rule from a query into a generated column?A second consumer needing the same definition, a uniqueness rule that has to hold on the cleaned form, or two reports that have started to disagree. The rule also has to be deterministic and computable from one row. If it needs a lookup or a reject path, it moves to the pipeline instead.
- Why keep the raw imported value once a pipeline has cleaned it?Cleaning rules have defects, and a lossy one cannot be undone. With the raw value kept, a corrected rule can re-derive every row, and you can audit what the rule changed. Without it, a bad name split is permanent. Retention and privacy rules still apply, so raw columns need a stated lifetime rather than living forever.
- How do you stop a team rule on string cleaning from eroding over time?Make it findable and tested. Keep a short per-dataset table of each cleaned column, where it is computed and who owns it. Add fixtures with NULLs, multi-byte names and one-word names that assert the canonical result. In review, reject a new private copy of a rule that already has a home.
- All cleaning belongs in the pipeline, so no report should ever format a string itself.
- Once a pipeline has cleaned a value, the raw input can be discarded with no risk.
- A generated column costs nothing to change when the cleaning rule changes.
- Repeating the same TRIM and LOWER logic in every report carries no risk of drift.
- A rule in the ingest pipeline also protects the table from rows inserted by other paths.