Generate table based on CSV and on-premise Data

If you already have CSV files you don't need a dataflow.   Keep ingesting them directly into your semantic model.

If you are considering OneLake or Fabric then another option would be to convert the CSVs to Parquet. Then you could access them via Direct Lake connection.

I would upload the historical CSV files to OneLake. To bring in current data, I would connect the on-premises SQL Server using a Data Gateway, loading the data into the same Lakehouse or dataflow environment. Once both historical and current data are available, you can union and store the results as a delta table or a Warehouse table, allowing for scalable, long-term querying.

I think this method is well-suited for large datasets, as OneLake offers better performance and scalability compared to SharePoint. It also provides unified storage, meaning all your data resides in a single, centralized environment, which simplifies data management. With delta format support, this setup is ideal for incremental data processing and advanced analytics. Additionally, it is more future-proof, since the data can easily integrate with other Fabric capabilities such as Spark and SQL analytics tools.