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.

  • $\begingroup$ Present versions of gradient boosting like LightGBM or Catboost work better with features declared as categoric that with OHE, what additionally minimizes the amount of RAM needed. $\endgroup$ Commented Dec 20, 2019 at 12:41
  • $\begingroup$ What this means is that I need to maintain the 'original' category format in case I want to make use of those tools, but it doesn't seem to disqualify a OHE approach as an addition to at least some of the category columns? Column selection would then wary depending on modelling. $\endgroup$ Commented Dec 20, 2019 at 16:17

1 Answer 1


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.

  • $\begingroup$ Thanks a lot for this, definitely helpful and deserves an upvote. I am not entirely sure about the extent to which it addresses my concern though. Being new to the field I would prefer not to have to explore the full spectrum but rather find a likely path and explore variations of that path. I have for example decided on SQL rather than files or something else. Now I am looking for a useful mindset when building the database structure and populating it specifically for ML. $\endgroup$ Commented Dec 21, 2019 at 19:50
  • $\begingroup$ The problem is that you don't know which specific ML process you're going to apply, and the way to store and/or pre-process the data will depend on that a lot, especially with a massive dataset. For example it's not uncommon to store several variants of a single original dataset pre-processed for different applications or with different parameters. But anyway if you don't have any specifics your best bet is probably to keep the data in its original form and/or in a way which is semantically meaningful, imho you shouldn't try to optimize the format if you don't know what to optimize for. $\endgroup$
    – Erwan
    Commented Dec 21, 2019 at 23:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.