Building LLM-Powered ETL Pipelines: Beyond Simple Text Processing

Large Language Models have transformed how we think about data extraction. Instead of writing brittle regex patterns^[1] or training custom models, we can now use GPT-4 to extract structured data^[2] from virtually any unstructured text.

At Hedgehog, I built an LLM-powered ETL system that processes thousands of documents daily, extracting structured data with 95% accuracy. Here's how we did it and what I learned about productionizing AI systems.

The Problem

Traditional data extraction is fragile. Every new document format requires new parsing logic. Every edge case breaks existing patterns. We needed a system that could:

• Extract data from PDFs, emails, web pages, and documents
• Handle multiple languages and formats
• Scale to thousands of documents per day
• Maintain high accuracy while controlling costs

Architecture Overview

The system uses a multi-stage pipeline:

1. Document Ingestion - Accept various file formats
2. Content Extraction - Convert to clean text
3. LLM Processing - Extract structured data
4. Validation & Cleanup - Ensure data quality
5. Storage - Save to database with metadata

class LLMETLPipeline {
  async processDocument(document) {
    const text = await this.extractText(document);
    const chunks = this.chunkText(text, 4000); // GPT-4 context limits
    
    const results = await Promise.all(
      chunks.map(chunk => this.extractDataFromChunk(chunk))
    );
    
    const merged = this.mergeResults(results);
    const validated = await this.validateData(merged);
    
    return this.saveToDatabase(validated);
  }
}

Key Design Decisions

1. Chunking Strategy

Long documents exceed LLM context limits. Our chunking strategy preserves semantic meaning:

chunkText(text, maxTokens) {
  // Split on natural boundaries
  const paragraphs = text.split('\n\n');
  const chunks = [];
  let currentChunk = '';
  
  for (const paragraph of paragraphs) {
    if (this.tokenCount(currentChunk + paragraph) > maxTokens) {
      if (currentChunk) chunks.push(currentChunk);
      currentChunk = paragraph;
    } else {
      currentChunk += '\n\n' + paragraph;
    }
  }
  
  if (currentChunk) chunks.push(currentChunk);
  return chunks;
}

2. Prompt Engineering for Consistency

Structured output requires careful prompt design:

const extractionPrompt = `
Extract the following information from this document:

Required fields:
- company_name: string
- revenue: number (in USD)
- employees: number
- founded_year: number

Rules:
- Return JSON only
- Use null for missing values
- Convert all currencies to USD
- Extract employee count as integer

Document:
${documentText}

JSON:`;

3. Cost Optimization

LLM APIs are expensive. We optimized costs through:

• Smart caching - Hash input text and cache results
• Model selection - Use GPT-3.5 for simple extractions, GPT-4 for complex ones
• Batch processing - Group similar documents together

async extractData(text) {
  const hash = this.hashText(text);
  const cached = await this.cache.get(hash);
  if (cached) return cached;
  
  const complexity = this.assessComplexity(text);
  const model = complexity > 0.7 ? 'gpt-4' : 'gpt-3.5-turbo';
  
  const result = await this.callOpenAI(text, model);
  await this.cache.set(hash, result, '24h');
  
  return result;
}

Error Handling and Validation

LLMs are probabilistic^[3]. Even with good prompts, they make mistakes. Our validation layer catches common issues:

validateExtraction(data) {
  const errors = [];
  
  // Type validation
  if (data.revenue && typeof data.revenue !== 'number') {
    errors.push('Revenue must be a number');
  }
  
  // Range validation
  if (data.founded_year && (data.founded_year < 1800 || data.founded_year > 2024)) {
    errors.push('Founded year seems unrealistic');
  }
  
  // Cross-field validation
  if (data.employees > 10000 && data.revenue < 1000000) {
    errors.push('Large employee count with low revenue - verify');
  }
  
  return { data, errors, needsReview: errors.length > 0 };
}

Performance Results

After 6 months in production:

• 95.2% accuracy on structured data extraction
• Average processing time: 8.5 seconds per document
• Cost: $0.03 per document (down from $0.12 initially)
• Throughput: 2,000+ documents per day

Lessons Learned

1. Prompt Versioning is Critical

Track prompt changes like code changes. A small prompt modification can dramatically change output quality.

2. Human-in-the-Loop is Essential

Even at 95% accuracy, the 5% failure rate matters. Build review workflows for edge cases.

3. Monitor Token Usage Closely

LLM costs can spiral quickly. Implement usage monitoring and alerts from day one.

4. Test with Real-World Data

LLMs behave differently on clean test data vs. messy real-world documents. Test early and often with production-like data.

The Future

LLM-powered ETL is just the beginning. We're exploring:

• Multi-modal processing^[4] for images and tables
• Custom fine-tuned models^[5] for domain-specific extraction
• Agentic workflows that can make decisions about data quality

The key is starting simple and iterating based on real production feedback. LLMs are powerful, but they're tools that need good engineering around them to be truly useful.