Best storage Option for Flat files

[PowerBI_Mallow]

As our data size is growing in large volume, we are planning to purge the data and keep a backup as flat files and keep only the recent years of data in the main database.

We would like to know which file format(csv, excel or parquet) will be better to connect with Power BI and which storage location will be better for the same.

We have considered storing it in a remote server or sharepoint. which will be the suitable one ore is there any other option?

[PowerBI_Mallow]

Please suggest a best storage location, whether it will be sharepoint or we can use a folder in remote desktop, is there any limitation on storing huge amount data on sharepoint ?

[Idrissshatila · Accepted solution]

@PowerBI_Mallow ,

 SharePoint impose the limitations on file sizes of 250 GB, I would recommend you have a server to store your data.

[Idrissshatila]

Hello @PowerBI_Mallow ,

I thinl parquet would be a better option, microsoft fabric is built on a parquet database so the flow of the data would go smoothly with a huge amount of data.

[PowerBI_Mallow]

Hi @CoreyP , We have our rds in AWS and it has 250GB of data. Database type is MYSQL. Since our database is connected to web application, to reduce load to our data base we have planned for purging. 

[Nithinr]

Csv is not recommended if your row data have ',' it would be big effort to query and get data from csv. Parquet is best option to store it has good compression which reduces file size and smaller files makes it easy to read and write.

[CoreyP]

How much data are we talking? What is your database type? Is it an on-premise SQL server? Cloud-based Azure? I'm definitely more of a front-end developer than a data architect or engineer, but I think the best solution is to not purge your old data and keep it in the database. 

What is the motivation to purge the old data and archive into flat files if you're still wanting to access it for analysis?