How to Build an Automated Procurement Spend Leakage Pipeline with DuckDB, dbt, and AI Agents

The Procurement Leakage Problem in Fragmented Data Stacks

When an enterprise operates across multiple regional entities, procurement data rarely lives in one clean database. Mid-market companies and conglomerates in India routinely run SAP in head office, legacy Tally instances in regional factories, and local expense management tools like Zoho Expense or Happay for field teams. This fragmentation creates immediate Blindspots.

Spend leakage typically falls into three categories: split purchase orders designed deliberately to stay under managerial approval thresholds (for example, generating three separate POs for ₹48,000 rather than one for ₹144,000 that requires VP sign-off), duplicate payments caused by slight vendor name variations in separate systems, and contract price drift where invoices slowly creep above negotiated master service agreement rates.

Trying to catch these leaks through manual quarterly audits in Excel is completely ineffective. By the time an auditor flags an overpayment six months later, recovering the cash is an uphill battle. In this guide, I will walk you through how we build an automated, daily pipeline that ingests raw ERP extraction files, runs high-performance SQL checks using DuckDB and dbt, and passes flagged anomalies to an AI audit agent that evaluates contextual legitimacy before sending triaged alerts to your finance team.

Step 1: Extracting and Standardizing Incomplete ERP Datasets

The first hurdle in building a spend audit pipeline is dealing with dirty schema variants across source systems. Your SAP deployment might output standardized ISO supplier codes, while regional Tally exports store vendor names as free-form text strings entered by local accountants.

To handle high-volume processing without incurring hefty cloud warehouse compute costs during development, we use DuckDB as our local OLAP engine. You can read the official DuckDB documentation to explore its high-performance in-memory execution and native Parquet integration.

Our ingestion stage reads raw CSV or Parquet dumps directly from S3 or local storage into unified staging views. Here is how we define our base landing transformation in DuckDB:

Standardizing Raw Invoices and Purchase Orders

We create a unified schema containing five mandatory fields across both POs and invoices: source_system, document_id, normalized_vendor_name, invoice_amount, and line_item_timestamp. Using DuckDB's built-in string transformations, we strip out common legal entity suffixes like 'Pvt Ltd', 'LLP', or 'Inc', remove special characters, and convert all company names to lowercase. This step reduces false non-matches by up to 40% before fuzzy matching even begins.

Step 2: Detecting Split POs and Vendor Duplicates with SQL & dbt

With standardized data staged, we build deterministic dbt models to catch two primary types of spend anomalies: threshold avoidance splits and fuzzy duplicate invoices.

Pattern A: Threshold Avoidance (Split Purchase Orders)

Finance policies usually stipulate that any purchase order over a set amount—say ₹50,000—requires explicit senior manager approval. Bad actors or rushed procurement managers frequently split a single ₹120,000 order into three separate ₹40,000 purchase orders placed to the same vendor within a 48-hour window.

In dbt, we express this rule using window functions that group orders by normalized vendor and purchasing agent, evaluating rolling sum totals over 48-hour intervals:

Logic Summary: Filter for individual orders below the threshold ₹50,000 where the 48-hour aggregate sum for the same vendor and agent exceeds ₹50,000. Flag these document clusters as potential threshold split anomalies.

Pattern B: Jaro-Winkler Vendor String Matching

Duplicate vendor profiles are common when business units onboard suppliers independently. Vendor 'Shree Logistics' might exist alongside 'Shri Logistics Private Limited'. To detect these, DuckDB provides native string distance functions like Jaro-Winkler and Levenshtein.

We run a self-join query on our staging tables calculating the Jaro-Winkler similarity score across vendor names. Any pair returning a similarity index greater than 0.88 with identical invoice amounts within a 7-day period is immediately flagged for deeper review.

Step 3: Deploying an AI Audit Agent for Contextual Triage

Deterministic SQL is incredible at finding statistical anomalies, but it lacks business context. If you send every fuzzy string match or split PO alert straight to Slack, your finance department will experience alert fatigue within a week. A vendor might legitimately issue two ₹40,000 invoices on consecutive days because they delivered two distinct truckloads of raw material under separate delivery challans.

This is where an AI agent fits into the pipeline. Rather than alerting humans immediately, our pipeline routes SQL-flagged record sets to an LLM agent powered by Anthropic's Claude or a localized Llama model.

Configuring the Agent Prompt and Tools

The agent is given access to two specific tools: a lookup function to pull historical vendor contract terms and a search function to read original invoice line-item descriptions from the database.

We structure the agent's evaluation system prompt strictly:

• Input: Flagged anomaly cluster JSON (e.g., two POs under threshold within 24 hours).
• Action: Query line-item details for both POs using the database lookup tool.
• Reasoning: Determine if line items refer to identical SKUs/services or distinct operational deliverables.
• Output: Structured JSON containing risk_score (0.0 to 1.0), risk_category, concise justification, and suggested corrective action.

If the agent determines that two split POs are for identical line-item SKUs submitted by the same employee, it assigns a high confidence score (0.92) and drafts a draft escalation query for the purchasing manager. If the line items are completely unrelated SKUs, the agent lowers the risk score and logs the record as a false positive, filtering it out from human review.

Step 4: Delivering Actionable Operations to Finance

The final step is delivering triaged alerts into existing enterprise workflows. Pushing raw CSVs to an email inbox guarantees they will be ignored. Instead, we pipe high-confidence agent findings directly into a dedicated Slack channel or Metabase operational queue.

Each notification contains the core anomaly facts, the AI agent's concise reasoning, and direct action buttons: 'Approve Escalation', 'Mark as Valid Business Exception', or 'Merge Vendor Records'. If you want us to help you evaluate and redesign your financial data stack, consider booking a fixed-scope Systems Audit & Blueprint engagement with our team.

Key Architectural Takeaways

Building an effective spend audit system does not require replacing your existing ERPs or spending millions on proprietary vendor software. By combining DuckDB for high-speed local processing, dbt for modular SQL data models, and lightweight LLM agents for context-aware triage, you get a modern, low-latency engine that pays for itself within the first few caught anomalies.

Deterministic SQL catches exact rules, but AI agents are what allow us to evaluate the human intent behind suspicious approval patterns without drowning finance in false positives.