The Setup
I took the CIMA contract partly because it was close to home, and walking in was surreal: I recognized most of the staff. In my Pleasanton neighborhood, the 40- and 50-something parents had set up a co-ed rec-league softball team, and it turned out the CIMA crew had their own team in the same league. We’d been playing against each other for seasons without knowing we’d end up colleagues.
CIMA specialized in collecting, aggregating, and distributing automotive data: the full parts catalog for every car on the road, the maintenance history of every reported vehicle, every recall by make and manufacturer, every sale, every crash, and much more. Their value hook was the ability to ingest data across a wide range of protocols and formats and normalize it into a queryable database.
The reality was brutal. Data arrived as EDI, a blob of text with optional fields and separators, as CSV, and in a huge range of other structures, and all of it had to be normalized into a fixed set of target data structures, at enormous volume. The legacy code could only process a fraction of each day’s incoming data, some feeds couldn’t be ingested at all, and the lead time to onboard a new feed was quoted in quarters. They had one engineer; their CTO, Christian McCarrick, wanted far better. They were a Microsoft shop, and this was pre-pandas, pre-tensors, when Python was still a novelty. They hired me, as a contract developer, to solve it over a short period.
My Role
Contract developer brought in to re-architect and build the ingestion and normalization pipeline from the concept up, then teach it to the on-staff engineer and hand it off.
What I Built
I liked the problem, so I stepped back and gave it a deep think. Four realizations shaped the design:
- We needed a way to codify the CIMA target database structure, the standard.
- We needed a way to codify the structure of each inbound feed, and to capture a new feed’s structure easily.
- We needed a way to build mappings from a feed’s structure to the CIMA standard, and apply that translation to new feeds.
- We needed a high-performance runtime to accept arriving data, run the translation, and make idempotent updates to the data store without creating duplicates.
From that, I built two parts, in VB.NET, a Microsoft shop, over a matter of days:
A GUI mapping tool. It let anyone interrogate a new feed for its structure, record breaks, fields, values, and generate a translation to our standard, emitted as a mapping XML. Beneath it I wrote code that could inspect a feed and infer its inherent structure, generating regex and other extractors from the detected delimiters and fields to turn a raw inbound feed into structured records. The mapper then let you map each feed field to the standard field, backed by a library of translators: type converters, formatters, and regex extractors, to handle the actual field-to-standard transformation.
An in-memory translation service. When data arrived, the service selected that feed’s mapping record and performed the translation in memory at high speed, writing to the data store idempotently and de-duplicating as it went.
So the whole was four pieces: a GUI mapper, feed-to-record structure inference, feed-record-to-standard-record mapping, and the in-memory processor. The key idea, the one I’m proud of, was making onboarding a new data source a configuration task, not an engineering project.
Net Result
It worked beautifully. The in-memory translation handled hundreds of thousands of records in minutes. Onboarding a new data source dropped from quarters to often under an hour. The system was reliable and performant, and it let CIMA expand its ingestion capability substantially, turning their core value proposition from a bottleneck into a strength. With hindsight, the mapping engine itself could have been a product for the market; we never pursued that conversation.
The Story
The softball-league coincidence above set the tone: a hometown contract that turned out to be full of familiar faces.
After about three months I handed the code and the technology over to CIMA’s on-staff engineer, Jino Jose, whom I’d taught to own and extend it, a great guy. A fond memory: staring down a particularly ugly EDI feed, I let fly a frustrated streak of Spanish on the assumption nobody around me spoke it, and Jino burst out laughing. He’d grown up speaking Spanish. Lesson: never assume. I also met Cameron Loos at CIMA, who comes back into the story later.
After roughly three months, early 2010, I handed it off and moved on. I took on a few other things for them, nothing significant. Itron came next.
What I Learned
- Model the shape, not the instance. The win wasn’t coding each feed. It was building the engine that infers a feed’s structure and maps it, so the Nth feed costs almost nothing. The same instinct as seeing the finished build before placing the first brick.
Key People
- Christian McCarrick, CTO at CIMA who hired Paul. linkedin.com/in/christianmccarrick
- Jino Jose, CIMA’s on-staff engineer, whom Paul taught and handed the ingestion system to.
- Cameron Loos, met at CIMA; recurs later in the story.
Technologies Used
VB.NET, a Microsoft shop; a GUI mapping tool; automated feed structure inference, delimiter/field detection → generated regex and extractors; mapping defined as XML; a translator library: type converters, formatters, regex extractors; a high-speed in-memory translation service with idempotent, de-duplicating writes to the data store. Pre-pandas / pre-tensors era.