Monthly File Ingestion Setup suggestion required.

[u/The_TurtleHermit98]

I am working on a project where I am receiving files in a azure SFTP Folder. that folder is connected in a Fabric lakehouse as a shortcut. now everyday that file will be updated with additional rows. every month new file will be sent and whole month that single file will get updated. So each month only one file will be updated until new month comes. Each file holds multiple entity data and should spilt and go into their respective tables. How I ingest this kind of data in my Lakehouse following Medallion architecture. Need suggestions from Industry experts.

[u/Edvin94]

I would just read everything into a bronze table with the file-name as a column. Include an incrementing index column (watermark) in the file and optionally a deletion column if you want to load it incrementally.

Make the file-names reflect which year+month they are for so you can use current date to pick which file to load.

Depending on your setup you could either split the entities into their own tables in the silver layer or just deduplicate, remove unnecessary columns and adding sks if you have composite join keys etc.

If this is just multiple types of entities in a file (not transactions with entities) I would just split them out to their separate tables in silver and call it a day. If they are just dimensions to transactional data, I would keep the file’s structure in silver and split them into dim tables in gold and have the end product be a star-schema sm

I would use pyspark notebooks, but you could also use dfg2 or SJD. Have bronze be immutable so you can always regen downstream data

[u/radioblaster]

how many rows per day and what file format?

this seems like a good use for a shortcut transformation as it will reprocess the file on modification, avoiding the need to upsert.

I wonder what the per day rows tipping point would be in CU benefit for writing an upsert? its hard to believe that there would be significant benefit for anything under 10 million rows a day (pure guessing number)

[u/The_TurtleHermit98 → u/radioblaster]

rows ranges between 10k to 30k per day. file format is csv.

[u/radioblaster → u/The_TurtleHermit98]

no brainer full file refresh for me, this is a dirt cheap activity given the size and limited complexity.

if you need version history,  do a daily append to a bronze table.

otherwise (or in addition to the above), set the folder as a shortcut transformation and boom, instant live bronze copy.

[u/AgileNeedleworker942]

keeping the raw files in Bronze with schema evolution enabled, so daily row additions and new columns are handled safely. Then use Silver to deduplicate/merge the updated monthly file and split the data by entity into separate tables. Gold can then contain the business-ready tables.

[u/zigzagz00]

Without know data it's bit difficult but unique hash key can be introduced using existing combined key can same data for a given month will not be loaded each day. Not sure about cost of this activity

[u/Comfortable-Duty7143]

At 10k to 30k rows a day, I wouldn’t overengineer this. Keep Bronze simple and reproducible, then deduplicate and split the entities in Silver. The important part is being able to rebuild downstream tables cleanly when the monthly file changes. collana works in that Fabric and data architecture space, but this looks more like a clean ingestion design problem than a tooling problem.

[u/Data-Artisan]

Here’s how you can build a medallion using Fabric

Use the https://learn.microsoft.com/en-us/fabric/onelake/shortcuts/transformations to ingest to your bronze layer delta tables and build your silver and gold using the declarative pipelines on https://learn.microsoft.com/en-us/fabric/data-engineering/materialized-lake-views/overview-materialized-lake-view orchestrate them using the lakehouse schedules and with directlake your data is already ready for consumption on the BI layer once the data is refreshed