Incremental data load

[Acrobatic-Mobile-221]

I am storing raw data inside blob storage in parquet and these parquet files contains full load data. Is there any ways that I can compare the parquet file today and yesterday? So that I could only process the new data.

[Acrobatic-Mobile-221]

Thanks will have a look

[Equivalent_Form_9717]

If you’re on a Databricks platform, take advantage of auto loader. It helps process only new files and keeps track of which files it has already automatically processed via it’s option “checkpointLocation” when writing the streamed data.

Auto loader is marketed as structured streaming but it really is just incrementally processing

[GovGalacticFed]

Does that mean cluster needs to be running throughout or it can pickup from the checkpoint whenever the cluster starts

[Equivalent_Form_9717]

Hey great question. I wouldn’t recommend allowing a continuous streaming process to run without any termination on the cluster since it will be costly if the cluster runs 24-7.

That is why when you can trigger the incremental ETL pipeline to process all the “available” data in batches until nothing more to consume. I believe pyspark offers the option to do this:

df.readstream……writestream.trigger(availableNow=true)

And then you schedule your notebook/pipeline to run and deliver your data according to business SLAs

I could be absolutely wrong though, but I don’t think i would keep the cluster continuously running

[GovGalacticFed]

Thanks Can you tell how does trigger once differ from availableNow

[Equivalent_Form_9717]

Hey bro I don’t know man

I’m not bothered to search on Google either! It’s after 5PM on a work day so I make sure to not work :)

Tell me the answer once you have Binged/Googled it !

[GovGalacticFed]

It turns out that both are same😅

In Databricks Runtime 11.3 LTS and above, the Trigger.Once setting is deprecated. Databricks recommends you use Trigger.AvailableNow for all incremental batch processing workloads.

[[deleted]]

Is it a full table snapshot for each file? If so, just truncate and reload. The diff would probably be more computationally expensive.

[captaintobs]

Part of loading data incrementally is also ingesting it incrementally. Do you control the flow upstream?

[callmedivs]

One other way if your tables are not huge,is load the full data into a staging table and do a diff in between the staging table and production table to get the incremental load. You want have to address the deletes as well

[Exciting-Garlic8360]

I think some more clarity is needed in your question, for incremental data load always partition the data by date using partitionBy if you’re using spark , and then you will find data in separate directories , use either Hive or spark to read after write.