On this page
The Problem#
The billing team gets an e-mail about an overcharge, a declined payment, or a dispute claim. Currently, they have to go across multiple legacy systems to collect data. Between PDFs, Salesforce, legacy on-prem software and Excel, it’s a lot to keep track of and an arcane process living on people’s heads that honestly shouldn’t.
The billing pipeline’s aim is to grab PDFs, process them for data, and deposit them inside a Snowflake data warehouse for further analysis via Cortex AI. The Cortex AI bit is actually another project that was exclusively built inside Snowflake. The billing pipeline is just the AI Workflow piece for PDF processing.
So, the target for this project is the billing team, their process is arcane and slow, and the goal is to speed it up drastically. The billing pipeline is part of the planned improvements.
Workflow#
As with every pipeline, we start with ingestion. The project is a simple ASP.NET Core REST API with several endpoints. The main two are UploadDocument and GetDocumentProcessingStatus. Uploading a document does some initial validations in the endpoint, then tosses the file into a queue backed by Redis. With the PDF, we also require some metadata to be sent in the form request. This gets included in the queue message. We’re leveraging the Foundatio library to handle the queueing.
A background task using Foundatio’s Job abstract class picks up the message and kicks off the processing. This is a 5 step process. We’re using Microsoft Agent Framework and the Workflow class, with just 4 sequential nodes.
- First, we parse all the pages using Azure Document Intelligence.
- Next, we classify each page of the document.
- Once we have classifications for each page, we pick out specific pages for metadata extraction. For both classificaiton and metadata extraction, we use an LLM via Azure AI Foundry.
- Once this flow is complete, we drop the resulting JSON into another queue.
- A second worker picks up this message and attempts to store the data in Snowflake.
Decisions and Tradeoffs#
Explain the constraints, alternatives you considered, why you chose your approach, and what you gave up.
One of the first constraints we had to deal with was a legacy PDF storage system. Another constraint was having to deposit into Snowflake. A throughput requirement was to handle between 500-1000 documents per day. I guess some billing periods are more busy than others…
We opted to go with a Python script for picking up the PDFs and depositing them into the pipeline. We didn’t want to build this into the pipeline itself, because of networking restrictions with the hosting the pipeline (in AKS, and inside a VPS). There was too much red tape around opening ports and such, so we opted for a workaround script.
Depositing into Snowflake was easy to handle, but there were some requirements around batching that we needed to work with. We ended up storing up to 20 documents in Redis, then batching them into Snwoflake in one request. Once successfully deposited (and verified with a separate call) we deleted the local copies in Redis.
The throughtput requirement was an interesting case. We originally wanted to do all the processing in Snowflake using their PDF_EXTRACT, AI_CLASSIFY and AI_COMPLETE functions, but these did not scale at all. Each document took upwards 3 minutes to process which, if you do the math, 3000 minutes is 50 hours, so we would get backed up during busy times. Going with Azure Document Intelligence and AI Foundry we got the processing time down to under 10 seconds for even the largest documents. We classify the pages in parallel, 20 at a time so as to not explode our AI Foundry token limits.
Challenges#
As mentioned above, when we initially started on this project, we wanted to just use Snowflake for the entire pipeline. Since performance in Snowflake was unacceptable, we did waste a bit of time there trying to optimize our queries. To be frank, trying to write an entire pipeline in just SQL is not great. Do not recommend. Going back to .NET and building out using MAF was the right call. We already used MAF before in other projects.
The reason we tried Snowflake-only is because the team at Snowflake who was onboarding us told us this was totally possible. Trust but verify. Well, verification failed. It was a surprise honestly, because … I mean, it’s Snowflake. Famous, big tech. But yeah, lesson learned.
Another challenge we came across was tracing. Fortunately MAF comes with extensions to make this easier. Since the process itself is so fast, we opted to skip checkpointing to keep the architecture simple, but in retrospect this is an area for improvement. If the document fails during metadata extraction (for example PII trips the content filters), we end up having to reprocess the entire document including the PDF extraction. This is obviously inefficient.
Lastly, error handling. We currently retry PDF processing 3 times then fail it into DLQ. There’s no mechanism for DLQ audit. We have endpoints that let us view the DLQ and re-submit failed messages, but the error handling process could be more robust for sure.
Results#
The pipeline has been running for a few weeks and documents are getting parsed, deposited and absorbed by Snowflake’s Cortex AI. We had an early access program running for a few select billing team members and they came back with some excellent feedback. Improvements are slated in the Snowflake area, but the billing pipeline does what it’s supposed to without fail. Hard to estimate overall time improvement as the entire experience is still being built out, but early signs are promising!
We are processing around 300-400 documents daily without pause, with very few failures. Usually these are related to content filters, because some of the documents will have critical PII in them. We have a Copilot agent checking the DLQ for failures every few hours, getting the details, and sending e-mails to people. There’s no action plan right now on what to do with these failures.