I am a beginner at Machine Learning and am starting out on a ML project. I have a large chunk of the source material and have started extracting the data from it to be stored in SQL (initial test with SQLite, but that is going to be insufficient for production).
The question that I am now facing that I can't find any kind of answer to is to what extend to preprocess the data that I store for best performance?
For example, ML methods are usually bad at handling categories and need them to be more like 0/1 values in a lot of columns showing the category rather than the category as a string in a single column. As I have many different such cases for a single row it would mean a lot of extra columns in SQL to achieve this preprocessing. I will also be using different ML methods like regression and classification on the data so exact preprocessing requirementsmight be hard to predict.
The data consists of %, times, categories, string labels and more. I will have to do some additional processing after retrieval from database regardless of how much preprocessing I am doing beforehand as some preprocessing is just not feasible (or even possible) to store completely prepared in SQL. % is of course easy, but when to do what for many of the other forms of data still eludes me.
Setup is single machine for daily data retrieval (small updates), data extraction and storage, modelling (unknown update interval) and predictions (multiple daily). Since I will be using many different models and aggregate prediction results I am very keen to have high performance without having to go nuts about it. I work in Python but can shift c/Java-like language if significant gains can be shown. Current estimate is for around 10 million data points, but that could easily be 10 times that number when broken down into categories.
As I am new at this I think it fair that you ask for clarifications if I have left out anything of relevance in determining what the best format for data in the SQL should be. I realise that performance is not as clear as desired, but I don't know what the bottleneck is going to be. Small investments like some additional RAM is not really a bottleneck compared to the need for an additional machine to run some portion of the process.