The number that mattered wasn’t 671,000. It was 6:32.
But before I got there, I had to survive a BOM file.
What a BOM File Does to Your Morning
A BOM — Byte Order Mark — is a hidden character. Three invisible bytes at the start of a UTF-8 encoded file, placed there by certain export tools as a signature. Completely benign in most contexts. Catastrophic if you’re parsing column headers programmatically and nobody told you it was there.
The file came in as a standard monthly lead drop from a third-party vendor. CSV, normal structure, expected columns. I loaded it, ran my process, and watched it fail in a way that made no sense. The column I was looking for was right there in the header row. My code couldn’t find it.
I opened the file in a hex editor. The first column name didn’t start with the letter I was looking at. It started with EF BB BF followed by the letter. Three bytes of invisible garbage prepended to the header, making Name into something my string comparison had never seen before and would never match.
That was lesson one: files lie. Specifically, files produced by systems you don’t control lie in ways you won’t anticipate until they do it to you. The fix was one line. The lesson was architectural.
Tool One: The Fuzzy Scrubber
The lead data problem predates the BOM file. It starts with a simpler, more persistent irritant: duplicate companies.
When you’re working with raw lead data at scale — scraped data, third-party drops, list purchases — you consistently encounter the same fundamental problem. A regional franchise has fifty locations. A corporate chain has a hundred. A national company has branch offices in every market you’re targeting. Each one appears as a separate row with a slightly different name. Smith Plumbing, Smith Plumbing LLC, Smith Plumbing of South Florida, Smith Plumbing — Boca Raton.
You don’t want fifty versions of the same company. You want one, or none.
The first tool I built was a fuzzy scrubber. Not exact match deduplication — exact match is easy and catches almost nothing. Fuzzy matching: similar names, above a threshold, clustered and collapsed. The goal was to identify companies that were likely regional, corporate, or franchise operations and remove them from the working set before they reached the dialer.
The first threshold caught seven clusters. Too aggressive — legitimate distinct companies were getting collapsed. I tuned it. Three clusters. Better. Still not perfect, but better is the goal in data work. Perfect is a fiction that costs you the pipeline.
The scrubber became step one of what would eventually be a twelve-step process. I didn’t know that yet.
The Encoding Problem
Every data engineer eventually learns that text encoding is not a solved problem.
It’s solved in theory. UTF-8 is the standard. Everyone agreed. The agreement doesn’t survive contact with files produced by legacy systems, Windows-default exports, Excel users who have never thought about encoding in their lives, or third-party vendors whose ETL tools were written in 2003.
The lead data came from multiple sources. Each source had its own encoding habits. Most of the time UTF-8 worked. Sometimes it didn’t, and the failure mode wasn’t a clean error — it was silent corruption. Characters mangled into question marks or replacement symbols. Phone numbers with invisible characters that made them unparseable. Company names with encoding artifacts that defeated fuzzy matching and left junk in the dataset.
The encoding handler became step two. Detect the encoding before you process. Normalize to UTF-8 explicitly. Validate that the result is clean. Only then proceed.
The BOM problem was a subcase of this. A BOM-aware reader handles it automatically. I had not been using a BOM-aware reader. I was, after the BOM incident.
Common Columns and the First Real Pattern
By the time I had a fuzzy scrubber and an encoding handler, I was starting to see a pattern in what the data needed across different downstream destinations.
We run multiple dialer systems. Each dialer has its own expected column format. The CRM backend has its own schema. A lead that’s processed correctly for one system is formatted wrong for another. If you’re loading data manually into each system, this is an annoyance — you reformat before each import. If you’re trying to automate the flow, it’s a structural blocker.
I started mapping what each system actually needed. What columns. What names. What formats. What was optional, what was required, what would cause a silent failure if missing versus an explicit error.
The overlap was significant. Most of what dialers need from a lead record is the same: company name, contact name, phone number, address, state, some kind of category or industry tag. The differences were in naming conventions and field formats, not in the underlying information.
This observation led directly to the most important structural decision in the whole pipeline.
Golden Columns
If multiple downstream systems all need roughly the same information, and the variation is in format rather than content, then there exists a canonical representation of a lead record that can be transformed into any downstream format without data loss.
I called this the Golden Columns.
The Golden Column set was the formal expected schema that a lead record had to conform to before it could go anywhere. Not the format any one system needed — the superset of everything any system might need, normalized to a single consistent representation. Once a record was in Golden Column format, outputting it for any dialer or the CRM was a transform, not a reconstruction.
This was the moment the project stopped being a collection of data-cleaning scripts and started being a pipeline.
A pipeline needs a contract. The contract defines what goes in, what comes out, and what the shape of the data is at each stage. Before the Golden Columns, I had tools. After them, I had stages. That’s a different thing. Tools are independent. Stages are composable. You can chain stages. You can add a stage without breaking the others. You can test a stage in isolation.
The pipeline design followed from the contract almost automatically.
Twelve Steps
By the time I had formalized the Golden Columns, I could see the full shape of what the pipeline needed to do to take raw third-party lead data to a dialer-ready output. I wrote it out as an ordered sequence:
Encoding detection and normalization. BOM handling. Field presence validation against the Golden Column set. Company name fuzzy deduplication. Phone number parsing and format normalization. Address standardization. State code normalization. Industry and category tagging where present. Missing field handling and defaults. Golden Column output generation. Dialer-specific format transforms. Final validation pass.
Twelve steps. Each one a discrete, testable operation. Each one necessary. Each one the result of a specific failure or discovery from the months of one-off processing that came before.
671,000 records. Six minutes and thirty-two seconds.
The speed came from the architecture. When every step is a discrete operation on a structured dataset — not a row-by-row loop, not a nested conditional mess, but a vectorized operation on a typed frame — the performance is a consequence of the design, not a separate optimization pass. I profiled it anyway. The bottleneck was where I expected it: the fuzzy matching at scale. I gave it more room to work in batch rather than iterating. The number dropped.
6:32. That became the baseline.
The Lesson That Took Twelve Steps to Learn
I didn’t design this pipeline. I discovered it.
Every tool in it started as a one-off fix for a specific problem I hadn’t anticipated. The fuzzy scrubber came from the franchise duplicate problem. The encoding handler came from the corruption problem. The Golden Columns came from the multi-system formatting problem. The BOM handler came from a hex editor at 9am wondering why a column name that was clearly visible was unreadable by my parser.
None of it was planned. All of it was necessary.
That’s how real data infrastructure gets built in practice: not from a design document, but from an accumulation of problems that eventually reveal the shape of the system underneath them. The design document comes after, when you’ve seen enough of the problems to know what the system is actually doing.
The danger is stopping before you write the design document. If I’d kept the twelve steps as twelve separate scripts, I’d have twelve places to maintain, twelve places to break, twelve things to run in the right order from memory. The pipeline consolidates that into one process with a contract.
The Golden Columns are that contract. Once you have a formal expected schema for your data, you have something you can build a tool around instead of continuing to improvise around a problem.
The twelve steps were the improvisation. The pipeline was the design.
The Processes Aren’t Proprietary. The Problems Are Universal.
These patterns aren’t specific to one system or one company. They’re the natural result of working with third-party lead data at scale, and the order you build them in will follow the same logic regardless of your stack or your source.
The fuzzy deduplication problem exists everywhere franchise and regional data gets aggregated. The encoding problem exists everywhere data crosses system boundaries. The multi-system schema problem exists everywhere more than one downstream consumer needs the same upstream data in a different format.
The specific implementation I built is tuned to a specific set of systems, specific dialer configurations, specific CRM expectations. But the architecture transfers: identify your downstream schemas, define your canonical representation, build each cleaning step as a discrete testable stage, measure the output.
The twelve steps I landed on were the twelve problems I encountered. Your twelve steps will be different. But you’ll encounter the BOM file. You’ll encounter the franchise duplicate problem. You’ll hit the moment where two systems need the same record formatted two different ways and you realize you’ve been solving the wrong problem.
When you get there, you need a contract. Define what a clean record looks like before you worry about what any specific system needs from it. Everything else follows from that.
Leave a Reply