Building LLM-Powered ETL Pipelines: Beyond Simple Text Processing

Fractional CTO · Founder of Injuria.ai
Large Language Models (LLMs[1]) have moved far beyond their initial fame as chatbots and creative writers. They represent a fundamental shift in how we perform one of the most persistent and frustrating tasks in software engineering: extracting structured data from unstructured text. For decades, this was the domain of brittle regex patterns[2] and complex, custom-trained machine learning models. Now, a single API call to a model like GPT-4[3] can often achieve better results with a fraction of the development effort. But moving from a promising demo in a notebook to a production system that reliably processes thousands of documents is a significant engineering challenge.
This article details the architecture and lessons learned from building and running an LLM-powered ETL[4] system that processes thousands of diverse documents daily, achieving over 95% accuracy on key data points. It's a close look at the practical realities of prompt engineering, cost optimization, validation, and building the necessary scaffolding around the model to make it a dependable component of a data platform.
From Regex to Reasoning: A Quick History of Data Extraction
For a long time, automated data extraction was a story of increasing abstraction. In the beginning, there was the regular expression. This powerful tool allowed developers to define precise text patterns, but it was notoriously fragile. A small change in a document's format, like an extra space or a new line, could break an entire parser. The maintenance burden was immense.
The next step involved classical Natural Language Processing (NLP) techniques, particularly Named-Entity Recognition[5] (NER). Tools like spaCy[6] came with pre-trained models that could identify entities like people, organizations, and dates. This was a major improvement, but it required significant domain-specific training data to recognize custom entities like invoice_number or product_sku. Getting this data was expensive and time-consuming.
The true paradigm shift arrived with the Transformer architecture[7], introduced in the 2017 paper "Attention Is All You Need"[8]. This innovation enabled models to understand context in text far more effectively, paving the way for modern LLMs. These models exhibit a property called in-context learning, which allows them to perform new tasks based solely on the instructions (the "prompt") provided at inference time, without any model retraining. This is the capability we exploit for ETL.
Anatomy of an LLM-Powered ETL Pipeline
A robust LLM-based ETL system is a multi-stage pipeline. The LLM call is the centerpiece, but the surrounding stages are what make it reliable and efficient. Our system breaks down into five distinct stages.
Stage 1: Ingestion and Content Extraction
The pipeline starts when a document arrives, which could be a PDF[9], an email, a raw HTML file, or a Word document. The first job is to get clean, high-quality text.
- For digital-native PDFs and HTML, libraries like PyMuPDF[10] in Python or Beautiful Soup[11] are excellent for extracting text content while preserving some layout information.
- For scanned documents or images, Optical Character Recognition[12] (OCR) is required. The quality of the OCR output is a critical factor; garbage in, garbage out absolutely applies. Poor OCR can introduce subtle errors that confuse the LLM.
This stage is about normalization. The goal is to produce a single, clean text string that represents the document's content, which subsequent stages can process without worrying about the original file format.
Stage 2: Intelligent Chunking for Model Context
Even with large context windows, many documents are too long to fit into a single LLM prompt. A 300-page financial report can't be processed in one go. The solution is chunking: breaking the document into smaller pieces. A naive approach, like splitting every 4,000 characters, is a terrible idea because it can slice a critical piece of information in half. A better chunking strategy preserves semantic meaning.
function chunkText(text, maxTokens, tokenCount) {
// A simple strategy based on natural boundaries like paragraphs.
// More advanced methods use recursive splitting or NLP libraries.
const paragraphs = text.split('\n\n');
const chunks = [];
let currentChunk = '';
for (const paragraph of paragraphs) {
// A real implementation would use a proper tokenizer, not just character count.
if (tokenCount(currentChunk + paragraph) > maxTokens) {
if (currentChunk) chunks.push(currentChunk.trim());
currentChunk = paragraph;
} else {
currentChunk += '\n\n' + paragraph;
}
}
if (currentChunk) chunks.push(currentChunk.trim());
return chunks;
}
We started with paragraph-based splitting but quickly moved to a more sophisticated recursive chunking method. This approach tries to split on large separators (like double newlines for paragraphs) first, then single newlines, then spaces, ensuring that the most related pieces of text stay together. We also implement an overlap of a few sentences between chunks. This helps the model maintain context if a relevant piece of data spans a chunk boundary.
Stage 3: The Core Extraction Task with Prompt Engineering
This is where the magic happens. For each text chunk, we send a carefully crafted prompt to the LLM. The quality of this prompt is the single most important factor for success.
A good extraction prompt has several components:
- Role and Goal: Tell the model what it is. "You are an expert data extraction assistant."
- Output Schema: Be explicit about the desired output format. Modern APIs, like OpenAI's, have a dedicated JSON mode[13] that forces the model to produce valid JSON[14]. For even more control, you can provide a full JSON Schema[15] defining types, required fields, and descriptions.
- Rules and Constraints: Specify rules like currency conversion, date formatting, or how to handle missing values (e.g.,
use null). - Few-Shot Examples (Optional but Recommended): Provide one or two examples of input text and the desired JSON output. This dramatically improves consistency.
const extractionPrompt = `
You are an expert financial analyst. Extract the following information from the provided document chunk.
Output your response as a single, valid JSON object. Do not include any other text before or after the JSON.
Desired JSON Schema:
{
"company_name": "string",
"total_revenue": "number (in USD, millions)",
"net_income": "number (in USD, millions)",
"fiscal_year_end_date": "string (YYYY-MM-DD)"
}
Rules:
- If a value is not present, use null.
- Convert all financial figures to millions of USD.
- The fiscal year is the one being reported on in the text.
Document Chunk:
---
${documentChunkText}
---
JSON:
`;
Stage 4: Merging, Validation, and Self-Correction
An LLM is a probabilistic system; it will make mistakes. This stage is the immune system of the pipeline, catching and correcting errors before they infect your database.
First, if the document was split into multiple chunks, you need to merge the results. A simple strategy is "last one wins," assuming the most relevant data appears later in a report. A better approach involves a more complex merge logic. If two chunks produce conflicting values for the same field, you might flag the document for human review or even use another LLM call to decide which value is correct based on the source text.
Next comes validation. This is a multi-layered process:
- Schema Validation: Does the output conform to the requested types and structure? In Python, a library like Pydantic[16] is perfect for this. It can parse the JSON and immediately flag type errors, missing required fields, or validation failures (e.g., an email field that doesn't contain an '@').
- Logical Validation: This involves cross-field checks based on domain knowledge. For example, a company's founding date cannot be after its IPO date. Net income cannot be greater than total revenue.
- Self-Correction: For certain validation failures, we can automate a correction loop. If a number is formatted as a string (e.g.,
"$1,234.5M"), we can send the erroneous output back to the LLM with a new prompt: "The previous response was invalid. The value fortotal_revenuewas a string. Please correct it to be a valid number in millions USD." This is surprisingly effective.
Stage 5: Storage and Data Provenance
Finally, the validated data is written to a database. It's critical to store not just the extracted data but also its provenance. For every data point, we store:
- The source document ID.
- The specific text chunk(s) it was extracted from.
- The version of the prompt used (we track prompts in Git[17] like code).
- The model used (e.g.,
gpt-4o-2024-05-13). - A confidence score (if available).
- A flag indicating if it required human review.
This metadata is invaluable for debugging, auditing, and creating datasets for future fine-tuning or evaluation.
A Worked Example: Deconstructing a Financial Report
Let's make this concrete. Imagine we have this text snippet from a fictional company's annual report:
In the fiscal year ending on December 31, 2023, InnovateCorp reported total revenues of $452.1 million, a 15% increase from the prior year. The company's net income for the period was $28.7 million. Headquartered in Palo Alto, the firm now employs over 2,500 people worldwide.
This text is fed into our pipeline. The prompt from Stage 3 is used, and the LLM returns a JSON string. In Stage 4, we use Pydantic in Python to validate it.
from pydantic import BaseModel, Field, field_validator
import json
class Financials(BaseModel):
company_name: str
total_revenue: float = Field(description="Total revenue in millions USD")
net_income: float = Field(description="Net income in millions USD")
fiscal_year_end_date: str
@field_validator('fiscal_year_end_date')
def validate_date_format(cls, v):
# Basic check for YYYY-MM-DD format
# A real implementation would use datetime parsing
if len(v) != 10 or v[4] != '-' or v[7] != '-':
raise ValueError('Date must be in YYYY-MM-DD format')
return v
# Raw output from the LLM
llm_output_string = '{"company_name": "InnovateCorp", "total_revenue": 452.1, "net_income": 28.7, "fiscal_year_end_date": "2023-12-31"}'
try:
# Pydantic parses and validates the data in one step
validated_data = Financials.model_validate_json(llm_output_string)
print("Validation successful!")
print(validated_data.model_dump_json(indent=2))
except Exception as e:
print(f"Validation failed: {e}")
This combination of LLM flexibility and strict, code-based validation provides the best of both worlds.
Productionizing the Pipeline: Cost, Latency, and Reliability
Building a working prototype is one thing; running it at scale is another. We focused on three key areas.
Taming Costs
LLM APIs are not cheap. Our initial costs were around $0.12 per document. We got this down to $0.03 through a multi-pronged approach:
- Aggressive Caching: We compute a hash[18] of the input chunk and prompt and cache the result. This prevents re-processing identical content.
- Model Tiering: Not all extractions require the most powerful model. We built a classifier that assesses document complexity. Simple, template-like documents are routed to cheaper, faster models (like Llama 3 or GPT-3.5-Turbo), while complex, dense prose is sent to top-tier models like GPT-4o[19] or Claude 3 Opus[20].
- Batching: We group requests together when possible to make more efficient use of the API.
Managing Latency
An LLM call can take several seconds. For a long document with many chunks, processing time can add up. We parallelize the processing of chunks, sending multiple requests to the API simultaneously. For user-facing applications, streaming the output token by token can also improve perceived performance.
Ensuring Accuracy and Reliability
At 95% accuracy, 1 in 20 documents will still have an error. For critical applications, this is not good enough. This is where a Human-in-the-Loop[21] (HITL) system becomes essential. Any extraction that fails validation or receives a low confidence score from the model is automatically routed to a human review queue. The corrections from this review process are fed back into an evaluation dataset, which we use to score prompt changes and decide when a new prompt is ready for production.
Prompt versioning is non-negotiable. A tiny, innocent-looking change to a prompt can have drastic and unforeseen consequences on the output. We treat our prompts like source code, versioning them in Git and running them against our evaluation dataset before deploying.
Common Pitfalls and How to Avoid Them
- Treating LLMs as Deterministic: They aren't. Even with
temperatureset to 0, model updates can change outputs. Always have a validation layer. - Believing Bigger Context is a Panacea: Larger context windows are great, but they don't eliminate the need for smart chunking on very large documents. They also increase costs and can sometimes lead to the model getting lost or distracted by irrelevant information.
- Choosing Fine-Tuning Prematurely: Fine-tuning[22] a model on your own data sounds appealing, but it's expensive, time-consuming, and can lead to overfitting. Start with advanced prompting techniques like few-shot examples and Retrieval-Augmented Generation[23] (RAG). Only consider fine-tuning when you have a large, high-quality dataset and have exhausted what's possible with prompting.
The Next Wave: Multi-Modal Models and Agentic Workflows
This field is moving incredibly fast. The purely text-based pipeline described here is already evolving. The latest generation of models, like GPT-4o, are multi-modal. They can natively read text in images and understand the layout of a page, including tables and charts. This will eventually allow us to bypass messy text extraction and OCR steps, feeding a PDF page image directly to the model.
We are also experimenting with agentic workflows. Instead of a single, monolithic prompt, we can give the LLM a goal and a set of tools. For example, an agent tasked with extracting financial data could be given a tool to read the document, a tool to search the web for currency exchange rates, and a tool to perform calculations. The agent then decides which tools to use in what order to accomplish its goal. This approach, supported by frameworks like LangChain[24], promises to handle much more complex and dynamic extraction tasks.
Building an LLM-powered ETL system is a journey in applied systems thinking. The model is the powerful but unpredictable star, and our job as engineers is to build a stage, a script, and a supporting cast that allow it to perform reliably and consistently every night.
Sources (24)
- Wikipedia: Large language model
- Wikipedia: Regular expression
- Wikipedia: GPT-4
- Wikipedia: Extract, transform, load
- Wikipedia: Named-Entity Recognition
- spaCy
- Wikipedia: Transformer (machine learning model)
- arXiv: Attention Is All You Need
- Wikipedia: PDF
- PyMuPDF Docs
- Beautiful Soup Docs
- Wikipedia: Optical character recognition
- OpenAI API Docs: JSON Mode
- JSON Standard
- JSON Schema
- Pydantic
- Git SCM
- Wikipedia: Hash function
- OpenAI: Hello GPT-4o
- Anthropic: Claude 3 Family
- Wikipedia: Human-in-the-loop
- Wikipedia: Fine-tuning (machine learning)
- Wikipedia: Retrieval-augmented generation
- LangChain