Fabric data ingestion best practice - data factory vs notebook vs dataflow

Hi eurenergy ,

the circumstances you described pose a perfect example where a metadata driven framework can be implemented to satisfy your requirements. 

In your case, my suggestions would be as follows.

Set up a SQL database item insight fabric which inherits your metadata tables. For ingest, you can create a table (e. g. called "ImportConfig), in which you save information about your data source. This information can be the data source name, path in the datalake gen2 and most importantly, you want to add a column which contains a watermark value. The watermark value should provide the information on when you last loaded the data successfully, most often as timestamp.

Next, in a data pipeline, create three activities:

• Lookup Activity: This lookup is connected to your SQL database item and retrieves the last available load data of your ImportConfig table
• Copy Activity: This copy activity will do the actual copying activity. In it, you can use the retrieved watermark value in the Source settings under Advanced if you are copying files or in the Query field directly as parameter when you are loading from a table. 
• Lookup/Stored Procedure Activity: After the copy activity, set up an activity which sends an update statement to your metadata table to update the watermark value, e. g. to @utcnow(). Make sure to execute this activity after you loaded the data successfully. This is important because you do not want to update the watermark value if the load wasn't successful.

While the copying can also be done with a notebook, I think it makes sense to first try it with a copy activity since they inherit the connector you need for your source. 

I hope this helps! Let me know if you have additional questions.

If this solves your request, make sure to Accept it as solution, so other people can find it quickly. 🙂 

Kind regards,

Niels