What I Learned from Building Two Data Pipelines
Last year I worked on two data pipelines with opposite problems.
At Spark Amplify, I worked on a production pipeline that could adapt whole books for readers at different reading levels. One of them was Mark Twain's Adventures of Huckleberry Finn. A large language model (LLM) rewrote the text, while later stages checked that the result was easier to read without losing the meaning, details, or voice of the original.
The other pipeline was Grid Info Tracker, a side project built with another engineer. Its job was to turn monthly grid reports and legal agreements into a queryable history of energy projects. It covered roughly three years of ERCOT GIS and battery releases, along with dates extracted from scanned agreements. Here the output was easy to name, but the input was a mess: shifting spreadsheets, inconsistent filenames, changing schemas, and scanned PDFs.
Both projects made the same weakness obvious. A pipeline can run successfully and still produce data you should not trust. The useful work is deciding what can go wrong, checking for it, and making those checks visible.
When the output is subjective
Each Spark Amplify book moved through several stages. An LLM revised the text, then automated checks compared the revision with the original. Since we were processing more than a hundred texts, rare mistakes did not stay rare for long. The system had to catch them without someone rereading every passage after every step.
Fluency was a poor measure of success. A sentence could sound natural and still alter the story. In Adventures of Huckleberry Finn, for example, a character could not suddenly wear different clothes or speak in a voice that did not fit the book. The facts and tone mattered as much as readability.
We used BERT similarity scores to detect changes in meaning and word-count checks to catch revisions that had grown or shrunk too much. Neither metric could tell us whether a passage was good. They were useful because they turned some kinds of failure into numbers we could inspect.
That distinction changed how I thought about evaluation. Language quality is not binary, and one score cannot stand in for judgment. The practical approach was to define several signs of a bad result, set reasonable thresholds, and send uncertain cases back for review. The model did the rewriting; the surrounding system made the rewriting usable.
Precision before intelligence
The grid project had a narrower question: what was the legally enforceable date in each agreement? Getting the answer into a database was harder than it sounded.
Many agreements were scans rather than digital PDFs, so ordinary text extraction failed. I tried Tesseract and several cloud OCR services. The most reliable early setup used pdfplumber to handle the PDF pages and PIL to prepare the images before OCR. Sharpening, deskewing, and converting the scans made a large difference.
I manually labeled about twenty documents and used them as a small test set. This was important because dates are unforgiving. A system that reads most of a page correctly can still be useless if it changes one digit in the date.
Once the OCR was reliable enough, an OpenAI model selected the enforceable date from the extracted text. A regular expression could find dates, but it could not reliably distinguish an effective date from a signing date or the date of an amendment. The agreements also followed different formats. The model was useful because the rule was fuzzy, not because it removed the need for rules.
The documents often ran for hundreds of pages. After inspecting them, I found that the relevant date usually appeared near the beginning or end, so the pipeline sent only the first and last ten pages to the model. That reduced cost and kept irrelevant text out of the prompt.
We later moved to Google's OCR and text-processing tools. The change was straightforward because the extraction steps were separate from the rest of the pipeline. That was the first time I saw modularity pay for itself so directly: a component stopped being the best option, and replacing it did not require a rewrite.
Monthly data is an unstable interface
The ERCOT data looked simpler. We received new CSV files each month and needed to merge roughly three years of releases into consistent GIS and battery datasets.
Appending the files was the easy part. Each release carried small surprises. Column names changed. Extra rows appeared. A battery field would be empty one month and populated the next. Comments showed up in some rows and disappeared from others. Any script written around one month's file was making assumptions about the next one.
We needed separate scripts for the first import and for later monthly updates. Some transformations were written in dbt, which I had not used before. I worked mostly with pandas, SQL, and BigQuery, using joins and comparisons to see exactly how one release differed from another.
I began to treat each monthly file like a new version of an undocumented API. It had to be inspected before it was trusted. The merge code needed explicit checks for the columns and types it expected, and it needed to fail clearly when those expectations stopped being true.
This also changed my idea of clean data. Cleanliness is not a property a dataset has on its own. Data is clean enough for a particular use. A missing battery field may be harmless for one analysis and fatal for another. The pipeline has to encode that difference.
What I would keep
I used AI in both projects, but for different jobs. At Spark Amplify it generated text. In the grid project it resolved ambiguity that was difficult to express as a fixed rule. In both cases, most of the engineering sat around the model: preparing inputs, defining acceptable output, testing edge cases, and deciding when a person should look again.
I used to count a pipeline as reliable when it finished without crashing. That is only the first test. A reliable pipeline also shows what it processed, which assumptions held, and where the result may be wrong.
The next dataset will fail in ways I cannot predict. The habit I want to keep is simple: decide how I will recognize a bad result before I start trusting the good ones.