From PDF to Dashboard: Building an AI Financial Pipeline

The problem was straightforward: a finance team receiving 200+ vendor invoices and bank statements per month as PDFs, manually extracting line items into a spreadsheet, then manually producing a variance report. Ten hours a week, every week, for years.

The interesting part was that the data was already there — just trapped in unstructured form. Building an extraction layer turned out to be less work than the ETL pipeline that followed it.

The extraction layer

I tried two approaches before landing on what works. Approach one: traditional PDF parsing with pdfplumber and regex. Fast but brittle — worked on 60% of invoices, failed on anything with a non-standard table layout or scanned image content. Not usable in production.

Approach two: send the PDF text (or image for scanned docs) directly to Claude and ask for structured JSON output. The prompt:

Extract all line items from this invoice as JSON.
Return an array of objects with these fields:
- date (ISO 8601)
- vendor_name (string)
- description (string)
- amount (number, positive for charges)
- currency (3-letter code)
- category (one of: [Software, Services, Hardware, Travel, Other])

If a field is not present, return null.
Return only the JSON array, no commentary.

Accuracy on the test set: 94% of line items correctly extracted, including from scanned PDFs via vision. The remaining 6% were handwritten or had formatting so unusual that even a human would need to re-request the document.

I use Claude for most invoices and fall back to Gemini for documents where Claude's vision struggled (some specific scanned formats). Both return the same JSON schema — the consumer doesn't care which model extracted it.

Airflow orchestration

The DAG structure:

fetch_new_pdfs → extract_line_items → validate_schema →
load_to_bigquery_raw → dbt_run → generate_variance_report →
send_to_finance_team

Each step is a separate Airflow task. This matters for retries — if the dbt run fails, I don't re-extract every PDF. Tasks only retry their own work.

fetch_new_pdfs pulls from a Google Drive folder where the finance team drops incoming invoices. A custom sensor watches for new files, deduplicates by file hash, and triggers the downstream tasks.

validate_schema runs Great Expectations checks on the extracted JSON before loading: required fields present, amounts are numeric, dates parse correctly, categories are in the allowed set. Documents that fail validation go to a review queue rather than being silently dropped.

The dbt layer in BigQuery

Raw extracted line items land in raw.invoice_line_items. dbt builds three layers:

Staging: type casting, null handling, standardizing vendor names (the same vendor appears as "AWS", "Amazon Web Services", "AMAZON WEB SVCS" across different invoices — staging normalizes these via a seed table of known vendor aliases).

Intermediate: joins line items to the finance team's budget allocation table (which maps vendor categories to cost centers and budget lines). This is the join that makes variance analysis possible.

Mart: fct_monthly_spend aggregates by vendor, category, and cost center per month. fct_variance joins actuals to budget plan and computes dollar and percentage variance per line.

Automated variance commentary

The variance report is the part that used to take the most time. Finance analysts would look at the variance table and write a paragraph explaining the biggest deviations — manually, every month.

I replaced that with an LLM call at the end of the Airflow DAG. The prompt receives the top 10 variances (by absolute dollar amount) and the previous month's commentary for context, and generates a structured narrative:

This month's spend was $284,000 against a budget of $271,000 (+4.8%).
The largest variance was AWS infrastructure (+$18,400), driven by the
migration project that began on March 12th. This was flagged as expected
in the March 10th budget revision. Software licenses came in under budget
by $6,200 following the Figma seat reduction in early March...

The finance team reviews and edits this narrative before sending — they're not publishing unreviewed LLM output. But the first draft is 80% done, reducing the commentary step from 2 hours to 20 minutes.

Results after four months

• Manual extraction time: from 10 hrs/week to under 30 minutes (review only)
• Revenue identified: +15% — the extraction layer caught invoices that had been filed without being logged in the spreadsheet
• Forecast accuracy: +20% — structured historical data enabled proper time-series modeling for the first time
• Validation failures caught before loading: 23 documents in four months, all of which would have silently corrupted the spreadsheet in the old process

The LLM extraction layer is the part that sounds risky but performed best in practice. The dbt transformation layer is where most of the reliability work lives — schema validation, vendor normalization, and the budget join logic. Get those right and the AI parts work cleanly on top of them.