Format of data in SQL for machine learning 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.
Disclaimer: I'm not at all expert about deploying big ML systems in production. This answer is only based on my experience with many different ML problems and datasets

My humble advice would be not to try to design the format of the data before having a quite precise idea of what kind of ML process is going to be applied. There is no "one size fits all" in ML and there's a real risk that by starting with the format of the data, you will end up with something which turns out to be completely inappropriate for the task.

Start with local experiments instead:


Use a small subset of the data at first.
Design some simple problems of the same kind as the real ones you plan to do eventually. 
Vary as many aspects as possible of the experiments: preprocessing, learning algorithms, parameters, size of the data, etc. Move progressively to more realistic tasks and amount of data.
Evaluate the advantages/disadvantage of the different methods/setups, then select a range of target setups
Finally design everything including the data format based on these target setups.


Following this logic during the experimental stage you can just export your data in any format convenient for whatever framework you're testing. it's only at the end of the experimental stage that you design the production system, e.g. SQL server etc.