Ingest CSV files daily from on-prem to Power BI/Fabric Lakehouse, Incremental refreshing from CSVs

[Surm]

Hello,

I have attached a diagram showing the architecture I am trying to set up. We are receiving daily CSV files (everyday) and we have a collection of 2 years worth of such files. We want to ingest this data to Power BI and create reports.

Ideally, I am looking at a solution that can transmit daily CSVs from on-prem network and make them append to their respective tables in Fabric Lakehouse. Is this possible?

What are the possible choices available to implement the architecture below? I have numbered the key data transmission tasks.

1) On-prem to Fabric Lakehouse: What is the common pattern to load on-prem data files to Fabric Lakehouse? Push from on-prem or pull from Fabric? If it is the latter, I assume Power BI gateway is required.

Can the daily CSVs be appended to the tables in Fabric Lakehouse?
Is it possible to incrementally refresh the Fabric semantic model from CSV files? 

2 & 3 vs 4) Option 4 is Direct Lake. If Direct Lake is not available, then 2 & 3 are required since a semantic model is required for Power BI reporting. How do I implement Lakehouse to Semantic model daily data refreshing? What are the choices (eg Notebooks vs Data Flows Gen 2 vs ADF Pipeline Copy task or something else?) available for setting up daily data ingestion tasks 2 and 4?

If someone has done something similar, I am keen to hear your experience.

[NandanHegde]

PaaS Azure data factroy is PaaS equivalent of Data pipelines in Fabric with additional features and for that you need an Azure subscription and below is the cost implications:

https://azure.microsoft.com/en-in/pricing/details/data-factory/data-pipeline/

That is not part of Fabric.

Unfortunately I am not aware as of now w.r.t mirroring the on prem data into fabric ; you might have to explicitly transfer it

[Surm]

Hey @NandanHegde ,

Is it possible for Power BI/Fabric to incrementally refresh its semantic model from CSV files?

That is, everyday a set of new CSV files are placed in the source folder. Can Power BI look at the timestamps of the CSV files to incrementally refresh?

[NandanHegde]

Hey,

As of today Fabric data pipelines doesnt support On Prem gateway.

You would have to leverage Dataflow Gen 2 to Copy the files from On Prem to lakehouse and integrate it within the fabric pipeline for trigger but there are certain challenges as of today w.r.t parameterization in dataflow Gen 2.

So in case if you want to have a proper meta data driven flow from On Prem to lakehouse , then would suggest using PaaS Azure data factroy as it has support for file system as a source and lakehouse as a sink and can be parameterized 

[Surm]

---

PaaS Azure data factroy

---

Hey @NandanHegde ,

What is exactly is "PaaS Azure data factroy" and how do I access this? Is it a part of Fabric? If this is not a part of Fabric, what additional licensing costs are associated with it? Does this need Power BI Gateway?

Moreover, it is said that "Fabric is one-drive for data". Is there a way we can mirror an on-prem folder to  Fabric  similar to how one-drive mirrors on-prem folders with one-drive cloud?