Posted inAI

Stop Fighting Regex: Build an Intelligent ETL with LLMs and Pydantic

AI tutorial - IT technology blog
AI tutorial - IT technology blog

The Messy Reality of Unstructured Data

Traditional ETL (Extract, Transform, Load) pipelines work perfectly when your source is a clean CSV or a structured API. But the moment a client sends a folder full of erratic emails, PDF medical reports, or Slack logs, the old-school approach hits a wall. I once spent an entire weekend wrestling with a 200-line Regular Expression (Regex) script to parse customer feedback. It worked for exactly two hours—until a user decided to use a different date format.

Code is deterministic and rigid, while natural language is fluid and unpredictable. We need a bridge between human communication and the strict requirements of a SQL database. By combining Large Language Models (LLMs) with Pydantic, we can build pipelines that actually “understand” the data they are processing.

The Modern ETL Stack: LLM + Pydantic

Think of this stack as a three-part harmony:

  • The Reasoning Engine (LLM): Models like GPT-4o or Claude 3.5 Sonnet don’t just match strings. They understand context. They know that “the patient in room 4” and “Mr. Henderson” refer to the same entity.
  • The Validator (Pydantic): LLMs are prone to “hallucinations” or adding unnecessary conversational filler. Pydantic acts as a strict gatekeeper. It forces the LLM to return data that fits your exact schema. If the AI tries to put a name in an age field, Pydantic rejects it.
  • The Permanent Record (SQLAlchemy): Once the data is cleaned and validated, we pipe it into a structured environment like PostgreSQL or SQLite for long-term storage and BI analysis.

I’ve deployed this pattern to handle thousands of documents. In one case, it reduced a manual data entry task from five hours of human labor per day to roughly three minutes of automated processing.

Setting Up Your Pipeline

We will use Python for this build. To bridge the gap between the LLM and Pydantic, the instructor library is the best tool for the job. It patches the standard OpenAI client to return actual Pydantic objects instead of raw strings.

Install the dependencies to get started:

pip install openai instructor pydantic sqlalchemy

Defining the Data Blueprint

Schemas come first. Before writing the extraction logic, we must define what “success” looks like. Imagine we are parsing messy medical referral notes. We need the patient’s name, their age, a list of symptoms, and a priority level.

from pydantic import BaseModel, Field
from typing import List

class PatientExtraction(BaseModel):
    name: str = Field(..., description="The full name of the patient")
    age: int = Field(..., description="The age of the patient in years")
    symptoms: List[str] = Field(..., description="A list of specific medical symptoms")
    priority: str = Field(..., description="Urgency: Low, Medium, or High")

The Field descriptions aren’t just for documentation. The instructor library passes these strings directly to the LLM as instructions, helping the model identify the correct data points within the noise.

The Extraction Engine

The extraction logic is surprisingly lean. By using the “patched” client, the model is forced to follow our Pydantic schema. If the model fails to produce valid JSON, instructor can automatically retry the request, showing the AI exactly where it made a validation error.

import instructor
from openai import OpenAI

# Initialize the patched client
client = instructor.from_openai(OpenAI(api_key="YOUR_OPENAI_API_KEY"))

messy_text = """
Received a call from John Doe, he's 45. 
Complaining about severe lower back pain and some numbness in the left leg. 
This looks urgent, get him in today.
"""

def extract_patient_data(text: str) -> PatientExtraction:
    return client.chat.completions.create(
        model="gpt-4o-mini",
        response_model=PatientExtraction,
        max_retries=3,
        messages=[{"role": "user", "content": text}],
    )

extracted = extract_patient_data(messy_text)
print(f"Extracted: {extracted.name} | Priority: {extracted.priority}")

This self-healing loop is the “secret sauce.” It makes the system significantly more resilient than a standard API call that might return malformed text.

Storing Validated Data in SQL

Now that we have a clean Python object, we can save it to a database. We’ll use SQLAlchemy to map our Pydantic data to a SQLite table. This keeps the data types consistent from the AI’s “thought process” all the way to the disk.

from sqlalchemy import create_engine, Column, Integer, String, JSON
from sqlalchemy.orm import declarative_base, sessionmaker

Base = declarative_base()
engine = create_engine("sqlite:///patients.db")
Session = sessionmaker(bind=engine)

class PatientRecord(Base):
    __tablename__ = 'patients'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)
    symptoms = Column(JSON)
    priority = Column(String)

Base.metadata.create_all(engine)

def save_to_db(data: PatientExtraction):
    with Session() as session:
        new_record = PatientRecord(
            name=data.name,
            age=data.age,
            symptoms=data.symptoms,
            priority=data.priority
        )
        session.add(new_record)
        session.commit()

save_to_db(extracted)

Scaling for Production

Moving from a local script to a production pipeline requires a few adjustments. First, consider costs. Processing 1,000 documents with GPT-4o might cost $30, but GPT-4o-mini can handle the same task for under $1 with nearly identical results for structured extraction.

Second, use Pydantic’s field_validator to enforce business logic. For example, if the LLM extracts a priority that isn’t in your allowed list, you can force it to a default value or raise an error before the data hits your database.

from pydantic import field_validator

# Inside your PatientExtraction class
@field_validator('priority')
@classmethod
def validate_priority(cls, v: str) -> str:
    allowed = ['Low', 'Medium', 'High']
    if v not in allowed:
        return 'Medium' # Safe fallback
    return v

Wrapping Up

Combining the reasoning power of LLMs with Pydantic’s strict validation changes the ETL game. You no longer have to fear messy, unstructured text. By defining a clear schema and letting the AI handle the interpretation, you can build pipelines that are both flexible and type-safe.

Start small. Pick one manual data entry task, define a Pydantic model for it, and run a few tests. Once you see the reliability of this pattern, you’ll never want to write a complex Regex string ever again.

Share:
Tags: