trying to perform incremental ETL using ADF, ADLS Gen2, Azure Databricks

[young tangle]

hi all, need some help with incremental load ETL process. So the flow is:
Sap s4 hana data source -> adls gen2 using ADF and stores as bronze layer parquet files (i assume?) and this runs everyday with incremntal load. Then using azure databricks I have to convert those bronze files into silver layer and store in adls gen2
So how can i implement incremental load from bronze to silver In azure databricks notebook?

basically, whenever adf pipeline triggers, azure databricks notebook should just read newly added files from bronze, do some processing and store them again in adls silver layer

[real path]

quite the broad question.

[strong pawn]

can you explain the steps in more detail, and which bit you need help with please?

[sacred osprey]

You need to write a notebook which reads the parquet from storage and stores your data in delta tables in a star schema. That is broad guidance but generally the idea

[young tangle]

so this is planned architecture

bronze layer stores data in parquet format.
using notebook, I have to read just latest data from bronze folder everyday and copy it into silver folder. that is what I need to do.
help I need with is, how can I read just new data from bronze folder.

[sacred osprey]

If the bronze layer is parquet it should be partitioned into folders by year=2023/month=10/day=20231024 or something to that effect. If not hopefully the file naming convention or the modified date on the blob metadata can help you

[latent valley]

hey Steve, I would have a question regarding your comment.

if parquet is partitioned like you described then it is possible to run queries in which we filter for like [year] = 2023 -> in this case only the 2023 parquet files are read right? not the whole data set and then filtering that to the 2023 date? so lets say only 10MB of data

because if we were to just filter for a column, like DATEPART(year, timestamp) = 2023, then the whole dataset is queried in this case, lets say like 50MB and then it is filtered down to the 2023 results, right?

I would expect the first use case would query faster, but in practise there is no difference in time between the two, is this normal and expected? shouldn't the first use case be faster?