As an analytics engineer, I am perpetually auditing the quality and lineage of data entering our warehouse. A persistent bottleneck has been the semi-structured data locked within PDF invoices from various vendors. Manual extraction is untenable, and while OCR services exist, they often lack the structured output necessary for reliable loading. Recently, I've been experimenting with ChatPDF's API as a parsing layer, followed by a Python script to impose a schema and validate the extracted data. The results, while not flawless, present a compelling and reproducible pipeline for moderate volumes.
The core concept leverages ChatPDF's ability to comprehend document context. Instead of treating the invoice as a mere image, we can prompt it to identify key entities. My workflow consists of two distinct stages:
1. **Data Extraction via ChatPDF API:** A PDF is sent to the API with a carefully engineered prompt to request JSON output.
2. **Data Transformation & Validation:** The JSON is ingested by a Python script (using Pydantic) that defines a strict schema, performs type coercion, and applies basic business rules.
Here is the foundational Python script for the extraction phase. Note the specificity of the prompt; it is crucial for consistent results.
```python
import requests
import json
def extract_invoice_data_via_chatpdf(pdf_path, api_key):
"""
Sends a PDF invoice to ChatPDF API and requests structured data.
"""
# 1. Upload the PDF file to ChatPDF
files = {'file': open(pdf_path, 'rb')}
headers = {'x-api-key': api_key}
upload_response = requests.post(
'https://api.chatpdf.com/v1/sources/add-file',
headers=headers,
files=files
)
source_id = upload_response.json()['sourceId']
# 2. Send a structured prompt to extract data
prompt = """
Extract the following fields from this invoice as a JSON object.
If a field is not found, use null.
Fields:
- invoice_number (string)
- invoice_date (string, format YYYY-MM-DD if possible)
- vendor_name (string)
- total_amount (numeric, ignore currency symbols)
- tax_amount (numeric)
- line_items (array of objects with 'description', 'quantity', 'unit_price')
Return ONLY the valid JSON object, no other text.
"""
chat_headers = {
'x-api-key': api_key,
'Content-Type': 'application/json',
}
chat_data = {
'sourceId': source_id,
'messages': [
{'role': 'user', 'content': prompt}
]
}
chat_response = requests.post(
'https://api.chatpdf.com/v1/chats/message',
headers=chat_headers,
json=chat_data
)
# 3. Clean and parse the JSON from the response
raw_content = chat_response.json()['content']
# Isolate JSON from potential markdown code fences
json_start = raw_content.find('{')
json_end = raw_content.rfind('}') + 1
json_str = raw_content[json_start:json_end]
return json.loads(json_str)
```
The raw JSON output is then passed to a validation script. This is where data quality is enforced. I use Pydantic models to define the expected schema, convert strings to proper dates and decimals, and flag records that fail validation for manual review.
```python
from pydantic import BaseModel, validator, Field
from datetime import date
from decimal import Decimal
from typing import List, Optional
class LineItem(BaseModel):
description: str
quantity: Decimal
unit_price: Decimal
class InvoiceSchema(BaseModel):
invoice_number: str
invoice_date: Optional[date]
vendor_name: str
total_amount: Decimal = Field(gt=0)
tax_amount: Decimal
line_items: List[LineItem]
@validator('invoice_date', pre=True)
def parse_date(cls, v):
# Add custom logic to handle various date formats
if v is None:
return None
# ... date parsing logic ...
return parsed_date
@validator('tax_amount')
def tax_leq_total(cls, v, values):
if 'total_amount' in values and v > values['total_amount']:
raise ValueError('Tax amount cannot exceed total amount.')
return v
```
**Key Observations & Pitfalls:**
* **Prompt Engineering is Critical:** The quality of the output is directly proportional to the clarity and structure of your prompt. Iteration is required.
* **Not a Silver Bullet:** For highly complex or graphical invoices, extraction accuracy can drop. This method works best with text-based PDFs.
* **Cost & Volume Consideration:** The API is cost-effective for batch processing but may not scale economically for thousands of invoices daily compared to dedicated document processing services.
* **The Validation Layer is Non-Negotiable:** Never load the extracted data directly. The Pydantic schema acts as a crucial data contract, ensuring only valid, typed data proceeds downstream to your staging tables.
This hybrid approach has allowed me to automate a previously manual data entry task with a measurable improvement in accuracy and auditability. The final validated data is then ready to be loaded via a dbt seed model or directly into a `stg_invoices` table for further transformation.
- dan
Garbage in, garbage out.