Intelligent Document Processing in Databricks

[00:02] extraction company based in Washington DC. And the biggest challenge that they face is they have a huge volume of unstructured data which is present in form of PDF files, images and so on. And getting information out of this

[00:16] unstructured sources is a challenge for this company. Not just this company, more than 80% enterprises in the world today face this problem where the knowledge is trapped in this unstructured sources. And if you are

[00:30] building AI agents, the agents is not going to work well unless you find a way to extract information from this unstructured data sources. Databicks intelligent document processing offers this functionality of AI parse document

[00:45] which solves this exact problem. In this tutorial, we are going to build a data pipeline where we will extract information from PDF invoices and then we will use datab bricks genie to perform analytics. Let's get started.

[01:00] Green sheen is the imaginary company that we are going to use for this that we are going to use for this tutorial. They are building eco-friendly products and they have different suppliers. Supplier A, supplier C,

[01:12] supplier D. And when they supply all these materials, the invoices are paid and you get this PDF files. Okay. And the data team at green chain will do

[01:25] manual data extraction from this PDFs. They also have this basic software which they use but the data extraction is still manual and slow and that is a

[01:37] bottleneck because you don't have like 10 or 20 invoices. You have hundreds and hundreds of invoices and your analytics and data extraction is getting delayed due to this. The invoice looks like this. This particular one is from Bio

[01:51] Hue Chemicals and they sold this organic titanium dioxide natural clay filter to green sheen and these are the individual items. These are subtotal tax etc. Your goal here is to extract this information, this monetary information.

[02:07] And this is a list of PDF that we are going to use. Okay, we have a bunch of them. Eventually, a business manager would like to ask a question. How much is the total due for bio chemical? What is the average shipping cost and so on.

[02:21] is the average shipping cost and so on. You want answers of this questions fast and also in accurate way. We will implement intelligent document processing pipeline where the invoices will be stored in some cloud location.

[02:34] Okay, these will be the PDF files and then you will use OCR to extract text from this PDFs. Now when you're using OCR and other solutions, what they will

[02:46] OCR and other solutions, what they will do is you just get text out of PDF. But while using AI pass document, you first extract text. Okay, that's the first step. But look at this second step. Here you are extracting the semantics, the

[03:03] meaning of it. Okay, text alone is not sufficient. You want to know what is my table structure. You want to get the semantic understanding of the text. Then only you can get business value out of it. Once you have all these text blocks,

[03:17] it. Once you have all these text blocks, tables etc., You will use business logic which will be mainly regular expression in our case to extract total amount tax in our case to extract total amount tax shipping cost and so on. Eventually data

[03:30] will land into gold table in datab bricks and we'll connect Genie with it to ask questions in a natural language. All right. So this is the technical architecture. Let's first set up the free account of data bricks. If you

[03:45] already have the account, you can skip that portion. But if not, let's first set it up and then we'll ingest the PDF files into raw volume in datab bricks. Let's go ahead and create an account in data bricks. All you have to do is click

[03:58] a link down below in the video description and you will come here. This is datab bricks free edition. They have created this free edition so that learning community can benefit and we truly appreciate that afford. Setting it

[04:11] up is very easy. I'm going to use my Google account. You can use your email ID as well. So, click on continue with Google and I will select my Google account. Say continue. And here for personal use, get free edition. Click on

[04:27] personal use, get free edition. Click on continue and start the puzzle. Okay. So, I think this is the right answer. Hit submit. After few seconds, my account is ready. Let's explore our data set. I have in total 106 different invoices in

[04:42] this folder. I'm going to ingest this data in the raw volume in data bricks. Let's look at some of the invoices first. So the invoice looks something like this where you have individual product uh quantity the price etc. And

[04:58] then you have a total due. Okay. So these are like different invoices that I these are like different invoices that I have in my system. I will go to datab bricks now and I will use the just the default workspace. Okay. So workspace

[05:13] default schema. Go ahead and click on create and uh Go ahead and click on create and uh click on create a volume. So your data can exist externally. Let's say it exist in uh AWS S3. In that case you will

[05:30] create external location and you will map that to your S3. Here we are going map that to your S3. Here we are going to pull all this data uh in data bricks. to pull all this data uh in data bricks. Therefore I'm creating a volume. So here

[05:43] Therefore I'm creating a volume. So here let's call it raw invoices. Okay. And it's a manage volume which means data is managed within datab bricks. It's managed by unity catalog. Okay. So here um you will say default

[06:01] workspace. Click on create and my raw invoices volume is created. You can click on upload to this volume and you can drag and drop all these

[06:14] files. Okay. So let me just controll a this and drag and drop that here this and drag and drop that here and override files with the same name. I think there is no issue with that. Click on upload. All right, my files are

[06:32] uploaded. So see in the row invoices now you see all these files. So this volume is now ready. Let's go ahead and create a Jupyter notebook where we can parse

[06:44] a Jupyter notebook where we can parse all these PDF documents. So I will click all these PDF documents. So I will click on new notebook here and let's call it on new notebook here and let's call it invoice parser. Okay, invoice

[06:58] parser. And here I need to get the base path And here I need to get the base path where my documents are stored. Okay, so let's see what is the path of those documents. So we already created this uh

[07:13] documents. So we already created this uh volume, right? raw invoices and this is the path. Okay, so you can just click on this and you can copy paste it here and just to confirm what files you will read from this path you can use db utils.

[07:28] from this path you can use db utils. Okay, so using dbuttails. FS. LS it will display all the files. As you can see it has listed all the files. Okay, so it is

[07:40] able to access all these file paths 106 rows in total. Now let's read these file paths into a spark data frame. And the way you do that is spark dot read dot

[07:55] way you do that is spark dot read dot format. Okay. And the format here is format. Okay. And the format here is binary file then dot load binary file then dot load base path and this will be your raw data

[08:08] frame. Now I'm going to add few more options here. Let's add the option for option. Okay, dot option. And in that path I want to read only the PDF files.

[08:23] Only the PDF files. And that is something you can specify by giving this something you can specify by giving this parameter path globe filter star. PDF. So it will go to this base path which is this. And it will read only PDF file. If

[08:36] there is any other file which is present in this volume, it will not read it. And in this volume, it will not read it. And at last just call dot select at last just call dot select where you are um creating all these

[08:51] columns path, content, length and modification time. Okay, see here it displayed just the file paths but here you want to have path content length and you want to have path content length and modification time. So let's display this

[09:03] uh particular data frame. uh hit control enter. So you have this file and the content here looks gibberish because it's a binary data. You see it's a binary file. So the content looks gibberish but you have length, you have

[09:19] modification time and so on. And now we are going to use our AI parse document are going to use our AI parse document function. So AI parse document function is at the heart of intelligent document processing in datab bricks or in agent

[09:36] processing in datab bricks or in agent bricks. Uh if you read the documentation here see I'm going to attach link of all these pages. So this will extract the structured information from PDF file. PDF is a binary format. Okay. it it is

[09:53] PDF is a binary format. Okay. it it is unstructured data. You want to translate unstructured data into structured format and that is what AI parse document will and that is what AI parse document will do for you. Okay. So here uh it will

[10:08] take the content the binary content as an input and it will return the structure document in this format. So it will have document, pages, elements. B

[10:20] box is a bounding box, metadata and so on. And then you can use it in a SQL query. You can simply say AI pass document this content this is my pass document this content this is my file and it will give you a structured

[10:35] file and it will give you a structured output uh like this. Okay. And here is output uh like this. Okay. And here is the article I will attach to. Please read through it. The performance of AI pass document is much better compared to

[10:49] other solutions which is Amazon text track snowflake all of this datab bricks track snowflake all of this datab bricks performance is uh really uh high and the pricing is low see performances of Gemini is also high but the pricing wise

[11:06] datab bricks offers a low price option for document parsing and if your organization has millions and millions of documents. It it's better that you use low cost option like AI parse document. Okay. So here let's import

[11:23] document. Okay. So here let's import this particular expression and then in row data frame you will select the path. Okay. So this is the path. Basically you are creating a pass data frame which will have two columns path and then pass

[11:40] document. two columns and pass document is nothing but you take content. So content is this. See this content you take the binary content and you let it

[11:52] pass through this AI path document function. Okay. So let's run this first. function. Okay. So let's run this first. And since it has run now you will

[12:04] And since it has run now you will say display pass df dot limit maybe let's display only first two

[12:16] limit maybe let's display only first two rows and see uh for these two documents it has already passed it and if you look at this object you can copy paste this object in a file maybe. Okay. So, let's copy paste this

[12:31] in a file. So, here I'm going to format this JSON. So, this is how it looks this JSON. So, this is how it looks like. So, it has bounding box content. Then it has another bounding box content. C. So, let's look at that

[12:44] particular PDF invoices eco pigment supply. Okay. So, invoices eco pigment supply. Okay. So, invoices eco pigment supply. I think it is this one. And here if you look at it, let's see. Eco pigment supply harmony land port

[12:59] Portland whatever see harmony land port Portland. So it extracted that into this bounding box and then there is another bounding box called build to okay build

[13:11] bounding box called build to okay build two. So see these bounding boxes are like this you see. So this orange box this green box it will first extract the this green box it will first extract the main sections main bounding boxes like

[13:25] this. And then if you see a table for example here there is a table right example here there is a table right invoice number due payment terms etc then that is being extracted as this HTML segment okay subtotal

[13:40] thank you whatever so let's check this so invoice number is so let's check this so invoice number is inv whatever so see say inv30016 and so on okay you can take a look basically it has extracted

[13:55] the structured information from our document. As a next step, what if we extract all these individual elements in a data frame with different columns? Okay, so just imagine you have a data frame with these columns ID, content,

[14:12] frame with these columns ID, content, description, type, bbox and there you are listing all the elements. So in order to do that first we need to extract from this JSON document element. See document dot elements is an array.

[14:26] It's an array of different elements in the PDF file. So let's first extract just this document dot elements. Okay. So let's do that here in our code. And

[14:38] the way you do that is from your data frame you will say variant get

[14:54] this is how you're selecting the specific element. See document dot the specific element. See document dot elements. So document dot elements. Okay. and you're selecting as a string and the column name will be elements

[15:09] JSON. So let's just display this particular part. Okay, we we just going step by step. I can do all of this in one short also but just to keep things one short also but just to keep things simple, we will do things step by step.

[15:23] All right, so this is how it looks, right? You have path and you have element JSON is an array of all those elements. See this array it it is this Now from elements JSON let's extract all

[15:41] from elements JSON let's extract all these elements. So every object in elements array. Okay. So this is one object. This is the second object and so on. Every object has all these properties B box content description ID

[15:56] and type. So now we'll create a data frame which has this five different columns. Okay. So here we are going to first define the schema. I can let it infer also but I want to

[16:13] define a schema. So what is my schema? The schema has ID, type, content, page ID, B box and description. Let's see here here ID type content description BB box and

[16:28] so on. So that is what I'm specifying here in this schema. Okay, this is here in this schema. Okay, this is basically the syntax for doing this and basically the syntax for doing this and then I will create elements data frame.

[16:41] Now folks in the world where AI can write code, you don't need to remember the syntax. You can take help of AI here. You know if you just do control I and you can run even AI here and you can get the help on the code syntax. Anyways

[16:59] get the help on the code syntax. Anyways so elements JSON data frame is this correct? It has a column called elements JSON and we are working on that. So from elements JSON using element schema see using this element schema explode those

[17:15] JSON property. Explore is a function which will take the JSON object and it will explode those properties into columns. So into the columns okay and you are giving alias as E. And here you are renaming the columns. So E do ID to

[17:31] element ID, E dot type to element type and so on. And you're also keeping the original column from data frame. This new data frame elements data frame has columns from JSON object as well as the parent data frame. I I hope you

[17:46] understand that part. It's pretty straightforward. So, display elements df. df. Let's run this. All right. Look at this.

[17:59] I have all these element ids. Okay. The first one, let's look at the first one. So, the first element that we have, the ID is zero. Type is section header. Content is inc. ID is zero. Type is section header. content is invoice

[18:17] and then you have all these tables too. Okay. So see now look at this data Okay. So see now look at this data frame. This data frame has every PDF file. See this PDF file has total seven elements. Okay. Total seven elements as

[18:32] in let's say for example this PDF file has all these elements 1 2 3 4 5 you know. So that kind of elements it is extracting and it is then extracting extracting and it is then extracting them in different rows. So here the

[18:47] first one is a section header then text then you have three tables. See you have like three different tables and then the footer and so on. My primary interest now is to extract these main monetary fields where you have subtotal shipping

[19:05] text and total due. I want to get this four main fields extracted from all the PDF documents. So, so far our data frame contains all these different headers.

[19:18] contains all these different headers. Okay, somehow we need to identify that subtotal etc. See if you look at this table, it has this keyword subtotal. Okay, that could be a shipping and so on. Subtotal. See if you look at this

[19:35] one subtotal shipping uh total due and so on. So maybe we can go through all these elements and we can look at these elements and we can look at these keywords and extract only uh that much

[19:50] data. Okay. So we can have different keywords. See these keywords might be different in different invoices. So we are going to take most commonly used

[20:02] keywords such as total subtotal shipping amount due tax see uh tax total due shipping subtotal subtotal we have balance due amount due

[20:15] subtotal we have balance due amount due total due and so on. So now let's import this particular function and then from elements df see what is the name of this data frame elements df from this data frame I want to first of all lower all

[20:33] the content so that my keyword extraction becomes easier okay so this extraction becomes easier okay so this thing is is a simple operation which is thing is is a simple operation which is it is taking content and this content

[20:45] okay this column and it is making everything lower case. After that you want to perform where close where you want to perform where close where or. Okay. So we are going to go through

[20:59] or. Okay. So we are going to go through all these keywords. See here for key in all these keywords. See here for key in keywords and you will say content like this. Okay. So content lowerase is like

[21:13] lowerase is like u total or subtotal. See it will do something like this uh content l like total or subtotal or

[21:28] text and so on. So it will perform this kind of query. Okay. And then for all the records which has any of these keywords, it will take those records and

[21:42] it will create this particular data frame. Okay. So let me just uh run this so that you get a better idea. See I got disconnected actually from my server. So that's why I'm getting the error. So what I'll do is I will just say run all.

[21:59] what I'll do is I will just say run all. Now in this particular data frame we want to extract all these monetary fields. Okay. Subtotal is this. Shipping fields. Okay. Subtotal is this. Shipping is this. Total due is this. So imagine

[22:11] you have a table where you have field name. Field name is subtotal. shipping, tax, etc. And the amount will be the actual amount. See, right now it's a content. It's a text field. It's kind of mixed up. So, we want to extract this

[22:24] this. Okay. So, if I use regular expression, I can take out this part and I can just extract 94 from this, 89 from this and so on. We are going to also

[22:36] handle all these. See there are some rows which has these HTML tags. So we rows which has these HTML tags. So we will get rid of all these HTML tags.

[22:48] Okay. So let's get rid of all these HTML tags. Let's get rid of if there is any extra space and so on. Okay. So let me create a new cell here. And by writing this code, see we are getting rid of all the HTML tags. We are replacing them

[23:03] the HTML tags. We are replacing them with space. Okay. So if you have something like let's say this, okay, hello space. If you have something like hello space. If you have something like this, just giving you an example, then

[23:17] this line, this particular line, right? This regular expression is saying that remove all the HTML tags, replace them with space. So this will create

[23:29] with space. So this will create something called hello world. Okay. And the next line is to remove the white spaces essentially to tabs, spaces and so on. and then you are converting that to the lower case. Okay. After this

[23:45] step, what you will do is you will use regular expression to extract 94. Let's say from this shipping and this shipping you will put it as a field. Okay. So

[23:57] there is a field is like key value. There is a field there is a value. So There is a field there is a value. So here you are doing that subtotal. This will extract all that value and put it as a subtotal shipping text and then

[24:13] balance due you know this is an or condition and after that you are converting wide columns into long field format. So what it means is like what what is the purpose of this is that if you have a table right let's say

[24:29] this is a table where you have table 45.3 let's say this is my invoice one 45.3 45.3 okay I'm executing it by mistake so inv2

[24:45] let's say you have text which is 6.2 two 6.2 two and then let's keep it in V1 and you have total right so total is whatever let's say 38

[25:08] uh whatever okay so in this case uh you want to convert this uh to a format which looks something like this inv1 then you have 45 then you have 45 5.3 And this column see this column will

[25:23] 5.3 And this column see this column will say something like essentially like this right it's like a table uh like this okay thanks autocomplete field and amount so we won then field invoice field and amount uh not invoice field

[25:39] field and amount uh not invoice field and amount actually I want uh sub total and amount actually I want uh sub total and then tax and total. So what if we and then tax and total. So what if we get something like this 6.2

[25:53] get something like this 6.2 and uh 38.7 just imagine that from this table you want to convert it to this format. Okay. want to convert it to this format. Okay. So that is done by this remaining code

[26:08] we stacking all this. Okay. So let's just run this control enter and then you will say display invoices.

[26:20] invoices. There's a lazy execution. So it executes the code only when you call display or any few of the other functions. When you are calling this it will not actually call uh that code you know it will not

[26:35] run the code on your actual data. We have covered that in data bricks fundamental uh video. So once again if you have not seen it uh just go watch it out when you say display invoice amounts that's when it is executing this code.

[26:49] All right. So by the way the example that I gave earlier for doing that you know separating fields into column headers we'll do that later. I think what this code is doing is just extracting the sub field and the amount.

[27:04] See just look at this transformation. Previously it was a string. Now using regular expression we separated it out. Okay. So we have field which is total due and the amount is 2131. And we did some data cleaning. Remove the HTML tags

[27:17] white spaces and so on. And this is the kind of problem that you face in real life. In real life you have all this data which is messy. It will have white spaces, any unnecessary fields which you want to

[27:30] clean and you want to get it into a format which looks something like this. It's a clean data right. So for invoice 102 here you have three fields subtotal, shipping total and these are the actual values. See now right now this says

[27:46] values. See now right now this says double this is string. So you have much clean uh format of data at this stage. Now we want to do aggregation. I explained earlier right? See these three rows for example you want to get to a

[28:01] rows for example you want to get to a stage where you have chemicals_13.pdf shipping text three columns and the values will be this. So for doing this

[28:13] you need a key. So your key is this invoice correct? So let's select distinct invoices from this particular data frame. Okay, let's get distinct invoices. So I'm saying elements df dot select path. Okay, path is this and I'm

[28:29] select path. Okay, path is this and I'm calling distinct. Okay, so and when I do calling distinct. Okay, so and when I do that, see here I need to I think display then only it will display it. So it will show you only the distinct

[28:44] uh invoices. See that these are the unique files. Okay. And here what you will do is you will take invoice amounts. Okay. And then you will group by path. So let's look at this. See this is invoice amounts. Okay. You are

[28:59] grouping by path. So grouping by path means let's say you are working on this particular invoice. So you get this as a one group. Okay. And after that you are one group. Okay. And after that you are doing a pivot. So pivot this field field

[29:13] doing a pivot. So pivot this field field column into these four columns. Okay. So column into these four columns. Okay. So field here if you look at it. So what pivot will do is it will take this field column which has all these values it

[29:25] will convert the columns out of it. So it will create one to three columns. Okay. And it will put all these values here. So that is the purpose of this pivot and then you are doing aggregation on the max amount. Okay. And then

[29:40] on the max amount. Okay. And then invoice financials um you are just essentially doing a join. Okay. So this will give you only the amount and you will give you only the amount and you then do the join uh using all parts.

[29:54] Okay. So let's execute this and see how it looks. Look at this folks. This is so good. Now for every invoice you have subtotal, shipping text and total. Now

[30:09] we extracted this text. It might have made a mistake during the OCR phase. So it is better that we do some kind of validation and the way you do validation is you will make sure that subtotal plus shipping plus tax should be equal to

[30:25] this. Okay. So that is the validation that we're going to do and for that we that we're going to do and for that we will write this particular code. So here you are calculating the total by summing these three up. Okay. Subtotal, shipping

[30:41] and tax you sum them up. You you compute this calculated total column and then extracted total column. This is what we have extracted from invoice and we match

[30:53] it with calculated. So 1 2 3 you sum it up. You call it calculated total. You compare it with this total by taking a difference. Okay. So this is a difference. Okay. So this is a difference and there is some tolerance

[31:06] here. And if this difference is less than 0.05 then this is consistent which than 0.05 then this is consistent which means the record looks good. If not uh it will put some of these values. Okay. If not it will put basically false as an

[31:21] If not it will put basically false as an expression and then status is going to expression and then status is going to be this. Okay. So let's uh do this invoice gold. Okay. So invoice gold. Control

[31:36] gold. Okay. So invoice gold. Control enter. Look at this folks. You have validated data. Now see if you look at this particular thing uh it has matched your calculated total with the actual total. And let's say in

[31:51] this case the difference is zero which means the data is good. It's true. It's validated. In this case there is a difference. Okay difference of $220. So it says total mismatch. Here also it

[32:04] So it says total mismatch. Here also it says total mismatch. So you not only extracted the data from invoices, you have validated them. And the beauty have validated them. And the beauty about spark and data frame is that it

[32:17] will run all of this in a distributed cluster. So you can have thousands and thousands and millions of millions of invoices and it will process them really well in a robust way in efficient way because it is using the distributed

[32:32] computing that distributed computing is abstracted away from you uh via datab abstracted away from you uh via datab bricks platform via pi spark via spark essentially so that you can focus on your business logic. If I run this same

[32:48] thing in on my local computer, my computer will probably hang if I have too much data here in in case of data bricks data volume doesn't matter. It bricks data volume doesn't matter. It can scale it really well. All right. Now

[33:02] my table is ready. I'm going to write it to my gold table. So here I will say uh spark dotsql create schema if not exist gold. Okay.

[33:18] So we have created a gold schema and let's verify here. So in my default okay not in my default workspace but in

[33:31] here I got the gold schema and there I'm going to and there I'm going to write this. So invoice is gold.right dot write this. So invoice is gold.right dot mode override save as table gold dot

[33:48] mode override save as table gold dot invoice goal dot I will say invoice data control enter and that should write the data in my goal schema. It will create a new table called invoice data and it will write it

[34:01] there. It took some time but my goal table is ready. So if I go to my code table is ready. So if I go to my code schema, I have this invoice data table and I'm just loading the sample data. So while it loads, just imagine

[34:16] you can put this on uh some kind of job. Okay, so this is the data pipeline that we have created via this notebook. You can run it through some schedule job or you can run it on automated trigger.

[34:29] Let's say if our documents are in S3 then when new file gets dropped there the pipeline will run automatically and it will extract all the data from the invoices and it will create uh this gold table and the gold table has see

[34:45] table and the gold table has see subtotal shipping tax total all that amazing information. See so far organizations were having issues dealing organizations were having issues dealing with unstructured data. I myself uh have

[34:58] a friend who is also our client uh who lives in Washington DC and they have huge amount of patients medical records you know prescriptions and doctor notes and so on and all this data is stored in cloud and they have the manual data

[35:14] extraction processes but with data bricks now you can auto extract all this important fields and this pipeline can you know keep on extracting that data

[35:26] from unstructured document and this data can become a gold mine for your AI agent. See AI agents will not work well if you don't have a high quality data and what this platform data bricks and AI parse document which is also known as

[35:42] intelligent document processing. What this is enabling uh for businesses is uh this extraction of information from unstructured sources such as PDF at a

[35:54] very low cost. See you might have argument okay you can do it with Gemini. argument okay you can do it with Gemini. Uh fine you can do it but with data bricks the the cost is much lower. See this is the quadrant you can you can

[36:06] look at this picture. The cost pricing dollar per 1k pages is much lower in datab bricks compared to Gemini. Once we have gold table ready, we can use genie

[36:19] for doing our analytics. So let's click on genie here and create a new uh space genie space where we are connecting with our invoice data. Okay. So this is the

[36:33] table in workspace gold. So let's select that, hit create and just give your space some name. Okay, invoice analytics. Okay, now this is an AI

[36:46] analytics. Okay, now this is an AI powered feature where you can ask questions in a natural language. Let's say I want to know what is the total due for this company by your group. Okay, so I will hit that. Now it is using AI to

[37:01] parse my question in a natural language and it will essentially run a query on and it will essentially run a query on our gold table. See the calculated answer is 16,178. Okay. So you can also look at the code

[37:17] Okay. So you can also look at the code here and in case if you want to modify a query uh you can modify it here. Okay. So you can use this genie to ask this suggestion. Okay, what is the total amount of all invoices in a data set?

[37:33] amount of all invoices in a data set? Okay, what is the total due from all the invoices? Okay, so your business manager can now go use Genie ask questions in a natural language and it will run the queries on your gold table. That's it

[37:48] folks. I have shared all the code in the video description below. Please try it out. If you have any questions, there is a comment box below. Thank you for a comment box below. Thank you for watching.