# 121. Databricks | Pyspark| AutoLoader: Incremental Data Load

**Channel:** Raja's Data Engineering
**Source:** https://www.youtube.com/watch?v=GjV2m8b9fNY
**Transcript page:** https://www.withtranscript.ai/video/GjV2m8b9fNY

## Chapters

- 0:05 — Autoloader and Incremental Loading Basics
- 3:49 — Traditional Incremental Loading Approaches
- 9:04 — Autoloader Architecture and File Detection Modes
- 21:17 — Databricks Demo and Configuration Options

## Transcript

**[0:05]:** Hello friends welcome to rajas data Engineering in this video I'm going to talk about autoloader in data breakes autoloader is one of the advanced future provided by data breaks even though this is Advanced concept this is mostly used in most of the data engineering data braks related projects so I'm going to give idea of what is autoloader when to use that how to use that autoloader is mainly used for incremental data loading so in order to understand autoloader first we have to understand what is incremental data load so let me start from the basics of incremental data load so incremental data load is one of the most commonly used data loading pattern in data engineering ETL pipelines so as per this concept we have to process only the adding new or modified data to the existing data set without reloading the entire data set which which means whenever we are processing whenever we are ingesting

**[1:02]:** data from multiple Source systems we have to process only the modified or newly added data we should not process the entire data so this uh incremental data loading pattern this is one of the complex and most challenging in most of the data engineering projects so there are multiple approaches in order to handle this incremental data load but each and every uh pattern is having certain problem so coming to autoloader this is going to resolve those problems so that is what we are going to discuss in this video so you understood what is incremental data load let me explain what is the ideal incremental data load or what are the characteristics of incremental data load so as for the uh thump rule of incremental data loading we have to process only the newly added or modified data files we should not process the entire data or we should not process non-modified data right that is one

**[2:01]:** of the important quality of this incremental data load and the next one we have to process the data as soon as it has been deducted or when we receive new file immediately it should be processed that is the second characteristics of incremental data load and coming to coming to the third one you you should not miss any new files we should not pro process uh the previously processed to file that is what we have seen in the first point same at the same time we should not miss any new files that is also one of the important characteristics and it should provide good performance even though we are having multiple uh different patterns to handle incremental data load this is one of the place where we are having a challenges uh we we have to design a incremental data load Pipeline with good performance even though we are going to have huge number of files

**[2:55]:** under large number of directories still there should not be any performance bottleneck and the last quality is it should be repeatable pattern which means we should be able to schedule then it should be able to process uh the same thing uh in a repeated uh manner so these are the ideal characteristics of incremental data load even though we are having many uh incremental data pipelines in the world now you know we are having millions or billions of pipelines running across the world to handle incremental data load but each and every pipeline they are suffering from some of the issue you they are getting deviated from these ideal characteristics so in order to avoid that we are going to introduce autoloader so that we will come to know later now before getting into autoloader again I want to highlight few common patterns which is used to handle incremental data loading right so coming to the first

**[3:49]:** button this is one of the traditional and most popular uh method which is Watermark method so as for this method what we are going to do is we will um when we in data from The Source system we are going to capture the maximum time stamp or one of the incremental ID column what is the maximum ID we are going to capture that value and we are going to store it in one of the control table which is called Watermark table then as per our um ETL pipeline logic when uh the pipeline is getting triggered next time it will first go to the control table and it will get the value of Maximum time stamp or maximum ID then it will go back to the source system then it will compare whatever the new records either modified or added uh greater than this particular Watermark time stamp or ID it is going to pull only

**[4:37]:** those value so this is the watermark method and this is very common and traditional approach and it is implemented in most of the uh traditional relational databases and data warehousing but we have certain problem with this method once we have created this uh solution then we will uh create triggers based on particular interval which is a schedule based or we can go with even based trigger right but coming to the downside of this approach even though there is no changes to the source system there is no modified or new data but still at the scheduled interval the program will start executing it will go back to the source system and it will just compare with the watermark time stamp and it will check if there is any new or modified record when it it come it comes to know that there is no changes in the source system then it won't execute anything then the

**[5:29]:** process will be terminated so the conclusion is even though there is no changes but still our program is getting executed that is unnecessary because each each execution that is associated with particular cost uh because of computing platform infrastructure uh you know for these reasons you know it is uh uh it is costing something so even though there is no necessity to execute but still uh it is getting executed at the regular interval so this is one of the the downside of this particular pattern right so let us go to the next pattern which is checkpointing this is most commonly used in spark structured streaming so as per spark structured streaming it is uh not going to check the source system when there is no change okay that issue is getting resolved but um the thing is it is going to collect uh the offset value for the processed data and it is going to store

**[6:26]:** those offset value under the checkpoint location with the help of this checkpoint location spark structure structured streaming can compare the source and Target easily and it can process only the newly modified or uh added data fine but the problem with this pattern is whenever we are having huge number of files uh millions or billions of files across thousands or millions of folders then it is going to recognize uh performance bottle neck it is not going to uh it's not going to be efficient in order to process handle huge amount of data files so that that is a problem with this pattern checkpointing location and apart from that we can go with our own approaches like we can use assure logic apps aure functions we can go with our own approaches also different you know patterns we can design but each and every pattern it is coming up with some shortcoming there is some problem with

**[7:18]:** each and every pattern so I'm not going to dive into different patterns here I have covered just two patterns which are very commonly used in today's data engineering projects right now we have seen now there are multiple patterns to handle the incremental data load but each and every pattern it is coming up with its own disadvantages so what is the best solution so best solution is we can go with autoloader so autoloader that is uh giving the feature of processing the incremental data continuously efficiently automatically as soon as new files arrive so this is the concept of autoloader so at high level you understood what is autoloader why we are using using autoloader is one of the future provided by data breaks in order to handle incremental data load right now let us try to understand more about autoloader so autoloader we we understood the purpose it is um uh used to process incremental data

**[8:17]:** efficiently and automatically and there are certain uh other limitations like this is currently uh supporting only cloud storage systems like assure data Lake storage in assure or Amazon P S3 pcket or Google Cloud Storage dbfs now these are the different cloud storage system we are having in the market currently it is supporting only cloud storage system and um coming to file format autoloader is supporting only certain list of file formats those are Json CSV Park AO orc text and binary file these are the file formats currently supported by autoloader right we understood what is autoloader and U you know for which sour systems for which file formats we can go with autoloader right now let

**[9:04]:** us try to understand the internal components of autoloader basically autoloader is having three major internal components the first one is internal database so whenever autoloader is processing ingesting data from different Source systems it is uh storing the metadata of processed files within internal database which is rocks TB this is one of the major component and second component is in order to process the incremental data it is using spark structured streaming concept so coming to Smart structured streaming it is processing the incremental data but it is having certain problem with a file listing whenever it has to handle huge amount of data then it is having certain performance botel neck that is actually eliminated with the help of this first component and three third component but uh coming to processing processing engine it's quite similar to Smart structured streaming and the third component is autoloader is using Advanced and optimized Cloud native components in order to

**[10:06]:** identify new files as soon as they arrive so with the help of this processed data and also newly available data know with with the help of these components autoloader is uh performing better than spark structured streaming right these are the components of autoloader we understood now coming to processing you know when autoloader is processing the data this is actually having two parts one is cloud files data reader this is nothing but identifying the new files and how it is reading from The Source system and the second one is cloud notification services this is optional coming to Cloud notification Services now there are two internal components one is there are two internal options one is uh directory listing that is a default one if you don't specify any option then it is going to act as a de directory listing but apart from that one more option which is file notification method even we can choose

**[11:01]:** that explicitly but in order to choose that we have to Grant certain access that I will cover in the next slide so coming to processing in autoloader it is having two major parts right coming to file detection mode as I mentioned in the previous slide you know we are having two different file detection mode one is directory listing mode another one is file notification mode coming to directory listing mode that is a default one we don't need to specify anything explicitly but coming to file notification mode for that we have to specify explicitly and coming to directory listing mode that is more suitable in order to process the data in batch mode which means know we are getting list of files you know for every 1 hour or every six hours every one day every week something like that we can go with directory listing mode but if you're are getting real-time data in a

**[11:52]:** huge number of files uh per uh second or per minute then it's better to go with file notification mode right I will dive into details for these two modes so that you can get better understanding first we can understand directory listing mode so coming to directory listing mode as I mentioned this is the default mode we don't need to specify that um explicitly in our programming and coming to permission setup know there is no specific permission needed in order to use directory listing mode except access to storage account access to storage account this is not only for this autoloader even we have to uh create data we have to read uh certain file from any storage then we need uh we need uh access to that particular storage account it is coming in the form of Mount point or we can directly access with certain key so even in order to create Mount point we

**[12:43]:** are having different approaches like we can use a service principle or access key or assure active directory now there are various approach so with uh with the help of those approaches we need to Grant access to the storage account that is the only thing apart from that we don't need to uh configure any specific permission right and uh this directory listing that is more optimized and efficient directory listing program compared to spark option even spark structured streaming that is also uh comparing the list of files under directory and it is processing but this is completely different from spark structure streaming so I'm going to give some example then you will understand the power of autoloader compared to spark structured uh spark structured structured streaming right in order to give that example in order to get better understanding you can uh know you can try to understand this example let's say you are processing data from

**[13:38]:** multiple Source systems and you Areo data Lake storage that is divided as per this hierarchy uh at top level we are having Source systems and within that we are having year within that we are having month within that we are having Day within that we are having hour so in real time we might have multiple Source systems but in this example I'm just considering only one source system and One S system let us assume we are having multiple years 2021 2022 2023 let us assume only one year I'm having currently 2022 and 2022 that is having multiple uh monthly folders internally so there will be 12 folders per year folder and each month that is divided into multiple subfolders for each day for example for the month of Jan I will have 31 subfolders for the month of February I will have 28 or 29 subfolders fers coming to March again it will have 31

**[14:30]:** subfolders so overall we are going to have 365 unique folders under this layer under this level and coming to each day again it is sub subdivided into 24 subfolders one for each hour so when we go to this particular level the number of folders would be above we are having 365 and for each folder we are having 24 subfolders which means 365 * by 24 we are going to have 8,7 61 folders per one year just for Simplicity I'm going to understand I'm going to assume I'm having 10 files under each folder so we are having 8,761 folder 10 files per F per folder which means overall I'm having

**[15:27]:** 87,134 this should be multiplied by two more and in case I'm going to add um multiple Source systems then we even we have to multiply uh those many number of times then we can understand how many number of files we are going to have and just in this example I have considered only 10 files on an average but in real time we can have thousand or millions of files under each folder then we can understand the complexity of this uh storage The Source system right now coming to this uh structure in order to process in order to process this uh files know from the root fold from the you know the lowest folders you know to process these files let us assume we are going to use Spar structure streaming in uh one use case and we are going to use autoloader in another use case then we are going to compare so as per

**[16:15]:** spark structured streaming and it is having the concept of one AP call per folder in order to process uh the newly or newly added or modified files so it will uh it will send one AP call per folder so in this case we are having 8,761 folders which means in order to process uh 8,761 folders it will uh send these many number of API calls and in order to process

**[16:46]:** 87,134 API calls but coming to autol loer this is following different powerful algorithm as per this one it is going to consider the entire folder uh in flatten structure so it is basically flattening the entire structure and as per ADLs future it is supporting 5,000 files for single APA call which means in order to process

**[17:20]:** 87,134

**[17:27]:** that is a limit and in order to process

**[17:41]:** 87,134 AP hits then we can easily compare here just 18 that is uh compared with 8,761 now we can understand the power of autoloader how powerful it is now how quickly it can finish the same data injection compared to SP structured streaming I hope you understood the power of autoloader in directory listing mode right now let me uh go to the next um uh file detection mode which is file notification mode so uh coming to file notification mode now this is uh this is mostly suitable whenever we are processing millions of files under huge number of directories and also the data is coming in real time you know for each and every minute or every second we are receiving uh many files so for those scenarios file file notification mode is more powerful than directory listing mode for file notification mode we have to Grant set access like for assure data L Storage account we

**[18:39]:** have to Grant contributor access same for storage que and also for uh even subscription okay uh we need to Grant the contributor access for these Services once it is done then autoloader will automatically set up the notification and uh Q services for us then it will start using that we don't need to manually set up anything but in the program you know in order to uh Grant access to Q storage we have to create a service principle and we have to give certain configuration details through our code so I will explain in the demo but before that we have to understand you know what are the different um parameters we need to pass for file detection file notification mode so we have to keep uh these information Sandy subscription subscription ID Resource Group tenant ID client ID and client secret client ID and client secret that is nothing but service principal credentials right I hope

**[19:33]:** you understood uh file notification mode as well now finally I can summarize uh the architecture of autoloader so for whatever we have discussed I'm going to summarize in this diagram so we are having aure data Lake storage so we have to process the data into one of the metallion architecture in our Delta Lake uh so for that we can go with the spark structured streaming but that is not powerful that's the reason we want to go with autoloader so coming to autoloader there are two different options either we can use uh even grit and Azure CU this is nothing but file notification mode so for that these services will be created by autoloader itself automatically but we have to configure certain things and we need to Grant certain access that's it so for this method uh autoloader will consume the metadata from the ADLs and it will just store the processed metadata within the rock

**[20:25]:** TB and with the help of this rock TB and the source system smart structured streaming will be able to process the data efficiently into our Medallion architecture this is one approach the second approach is this is the default one option two in this option we don't need to explicitly mention uh the type by default it will be using directory listing so in the directory listing basically it is going to list down all the you know files under the different folders and it is going to maintain the processed data within the rock TB and with the help of this rock TB and directory listing spark structured streaming will be able to um pull the unprocessed or modified newly added data efficiently from The Source system and that can be processed and pushed into metalian architecture within the Delta Lake this is the summary architecture of autoloader I hope you understood now let me get into demo

**[21:17]:** in data breaks I have logged into my data breaks environment so in this notebook I'm going to explain different features of autoloader how to use that practically so as I mentioned uh previously the first step is we have to Grant access to the storage account to autoloader so for that we can use spark configuration settings so here what I'm doing is I'm retrieving my assur storage Account Details along with access key from the keyal even we can hardcode that directly but it's recommended to use uh keyal because uh keeping the sensitive information directly in the notebook is not recommended I have already posted couple of videos in my Channel about assure keyal integration ation in case you don't understand how to integrate assure keyword with datab bricks you can watch those videos right so I I am retrieving the values then using Spar configuration uh command just I'm uh uh configure my assure data L

**[22:13]:** Storage Account Details once this is done then autoloader will be able to injust data from The Source system right this is one of the preer site once this is done in the next step I'm going to show the basic configuration now the basic way of using autoloader so for that I'm going to use the syntax Spock do readstream then within the format usually we used to give the file format like CSV Json but when you have decided to use autoloader then we have to use uh the format which is cloud files whenever we are using cloud files it means it is going to use autoloader internally right this is how we can invoke autoloader and one of the one of the mandatory parameter is option is we have to specify which file format we are going to process in our ASU data Lake storage we might have CSV file we might have Json file or

**[23:05]:** Park file so what kind of file format we have to process in this inje so for that we have to specify option within this option we have to tell cloud files start format that is gson in case you have to process a CSV file in your project you can give CSV right this is one of the very simple example how to use autoloader and apart from that we are having so many futures so many options available in autoloader processing that I'm going to explain in the subsequent steps so the next step I'm going to tell schema as well so this is similar to any data frame reader so in while reading data frame while creating data frame we used to specify this option we used to define the schema explicitly with the help of struct type and struct field then we used to mention that that is recommended for good performance in case we are

**[23:56]:** going with infer schema then un neily data breaks engine will scan the data files once in order to derive the schema but that is very costly operation in regular uh spark processing but coming to autoloader even that is not very costly because autoloader efficiently samples the data to infer the schema so even we can go with infer schema option for that we have to go with option cloud files infer schema type True by default it is false but if you want to go with infer scheme option then we can give this option and along with this option we should give schema location as well once we have created this infer schema where we have to store that information for that we can give location of our choice it could be ADLs or dbfs right these are the different options we can exp explicitly Define the schema and use or we can ask the autoloaded engine

**[24:47]:** to infer the schema right apart from that we can specify schema hints as well so in this example whenever I'm telling autoloader to infer the column type let's say I'm having one column employee ID which is having all the numerical values but uh data bricks autoloader might infer that as integer let us assume that column should be big integer instead of regular integer then what I can do is only for those particular columns I can give hints as well so here I'm giving schema hints then employee ID column it should be big integer so even though you are inferring as a integer don't use integer it should be big integer so this is one of the other option so so similarly you know here I have covered few options you know with the help of this option command we can specify so there are so many different options available we can use any option as

**[25:36]:** per our use case our requirement this is um so far I have talked about directory listing mode only because explicitly I didn't mention that notification service now in the next example I'm going to show you how to use this notification so for that file notification mode we have to use the syntax we have to give option Cloud files. use notification Pro Pro by default this is false so it will use directory listing mode so whenever we have to use file list file uh notification mode then we have to use this syntax and as I mentioned earlier whenever we have to use um um file notification mode we have to Grant access in assure portal um for assure data L Storage account assure q and uh even git once that is done and also we have created uh service principle then even we can pass these parameters along with autol loader so for that in my next example what I have done is I'm creating one variable here cloud files conf we can give any variable in

**[26:34]:** this example I have given this variable within that I'm defining all the parameters along with its value with the help of key value PA it's a kind of python dictionary it is having key value par so here one side I'm having all the parameters and other side I'm keeping all the values so here I have you know given all the parameters then while reading the data what I can do is just I can pause all these values in one go here I have defined the options in the above variable now I am using that variable so this variable is key value pair that's the reason we need to give star star in case it's array we can give only single star but it's a key value pair that's the reason we need to give star star and uh earlier I have given only option so far but here it's a multiple option that's the reason

**[27:19]:** here we have to give options not only option we have to give yes also right this is how we can use file notification mode as well I hope you understood and apart from that here I have summarized different um options autoloader options I'm not going to dive into details but quickly I can walk through first one is file format okay which type of file we have to use for our injection that we can specify and the next one is allow override in case there is some changes to to the existing record let's say in our existing value for one of the Val one of the employee the salary is 5,000 but in the source it's 6,000 there is some change then we have to overwrite the existing record card so this is a default property but in case you don't want you can change as well and coming to schema location in case we are

**[28:04]:** going with infer schema then uh where we have to store that schema information so for that we can Define schema location the next property is schema evaluation mode and today's Big Data World schema evaluation that is one of the important concept autoloader is efficiently handling schema evaluation so basically with this property we can uh play around this schema evaluation B basically it is giving different uh far different modes schema evaluation modes to handle schema evaluation so I have given different schema evaluation mode as well below by default it is going to use add new columns so let's say in our Target table we are having five columns in the source we are getting six column so one new column we are getting then what happens our streaming process streaming pipeline will fail then the new column will be added after that the streaming uh process will be restarted this is the default mode and coming

**[28:56]:** to rescue in the rescue mode it is not going to U it is not going to add the new column to the Target but instead of that it is going to create one column which is called underscore rescue data within that it is going to document what are the new columns we have received and at the same time it is not going to fail the uh streaming process that is rescue and the next one fail on new columns let's say we are processing some sensitive data we are not expecting any new column to arrive but in case something is coming uh that is unexpected then it should fail so for that those scenarios we can go with fail on new columns and uh the streaming pipeline will be failed it will not restart automatically we have to fix the schema issue then only it will restart and coming to the last one none just totally

**[29:44]:** ignore whenever we are getting some uh new schema which means it is turning of schema evaluation future that's it these are the different options available for schema evaluation mode and uh coming to other um uh other uh options schema hint I have already covered in case for a particular column you want to specify particular data type then we can go with that and infer column column types in case you don't want to go with user defined schema then we can go with this one and include existing files which means whenever we are setting up this uh entire autoloader process for first time and you are pointing to some data folder but there are already files are available so the previously available file we have to consider or not that property is handled with the help of this option and the next one is Max pite per trigger so whenever we are processing uh the incremental

**[30:36]:** data basically it is triggered maybe it could be once or it could be micro batch or it could be continuous but you know for each trigger how much data it can fetch at a time that property is defined with the help of this option and the next one is maximum file age this is one of the important parameter in order to handle the duplicate files so let's say I'm going to uh set 1 hour is the maximum age for all the files So within 1 hour I'm going to get to two different data files with same name then it will be considered as duplicate but after 1 hour let's say last one hour I got one file and uh after completing uh next hour again I'm getting the same file then it will be treated as a new file so in order to define the duplicate file this property is very important and um coming

**[31:23]:** to Resource tack this is kind of uh uh you know this is helping to ident if y the right resources this is not very commonly used and uh this next one is very important property validate option so basically we have defined different options in the above step so we have to validate those options or not for example in your code for one of the options you might have given wrong value if you have given wrong value that will be ignored and it will be Pro you know it will be proceeded but uh you have given certain option it's very important option it should have received the right value that value should be considered so you want to validate then you can give this option which means whatever the options we have specified in our autoload injection everything will be validated in case certain value is incorrect then it is going to report so in that

**[32:08]:** aspect this is one of the important parameter and um we are having back fill interval you know for what duration we have to consider the back filling that is defined with the help of this parameter and the last one is partition column this is actually using Hive style partition so whenever we are ingesting the data from The Source system in case the source system is partitioned based on the folder now usually this is following this style country equal to uh UK or city equal to certain city so basically you know the hi style partition in case uh the source system is following High St partition then that can be uh used for autoloader injection so these are the different uh commonly used uh options in autoloader I hope you understood and and apart from that while processing data through autoloader we can consider wild card options also Wild Card option that is nothing new to

**[33:04]:** you because this is very common in most of the programming languages uh question mark which means ma matching any single character star which means matches zero or more number of characters so there are various options I have given so you can have a quick look on that this is uh nothing new this is similar to any programming uh languages this wild card option this is uh commonly available with most of the programming languages right so how to use that so while um reading the data using autoloader first we have to give a format cloud files then we can Define different options then we can Define scheme as well then here we can use the Wild Card path so for example under root folder I want to consider all the subfolders that's the reason star star means any so we can use any wild card option while reading the data from The Source system and finally

**[33:56]:** once we have ined the data from The Source system we have to write the data into our Target system in The Medallion architecture we have to write the data within bronze silver or cold layer so for that we can use right stream uh command so DF do right stream then trigger there are multiple options for trigger either we can use once or we can go for for each batch now there are different options we can use and finally we have to use Save and our Target location in The Medallion architecture this is how uh we can use autoloader in our real-time projects I hope you understood the concepts of autoloader how it is working internally how to use in our project practically I hope you understood hope you enjoyed this video if you like the content of this video please like and comment in the Channel Please Subscribe this Channel and don't forget to click

**[34:45]:** on the Bell button to get latest videos on data breaks thank

**[34:54]:** you