I have a personal project to create predictions for tennis matches.
It currently consists of a Python application and a MySQL database. I extract data from various websites and APIs and store it in the database. I've also developed a feature pipeline that calculates a number of features from data. As the features can take a a considerable time to calculate I've also created a feature store in the database. For the purposes of this question let's assume I store all of the info in three tables:
match
contains details of the players and tournament (including location)weather
contains details of the latest weather forecast for a given locationfeature_store
is my current feature store
weather
is joined to match
via a location_id
field and there can be multiple weather
records for each match
record.
feature_store
is joined to match
via a match_id
field and there can be multiple feature_store
records for each match
record as a new set of features need to be engineered every time there is a new weather
record added for the match location.
As a further bit of info - I use the primary key of the feature_store
table as a foreign key elsewhere in the database. This is so I can track back to see what features were engineered and when. For brevity I've not included details of the associated tables.
Everything has been working fine but I'm now wanting to add to the number of features. However, as I need to preserve the existing feature_store
records as is I can't simply add a column and then add values to the existing rows.
Consequently, in order to add a new feature and maintain my audit trail I will need to add a new column and then create a new row where I copy all of the values for each existing feature_store
record and then add the value for the new feature. This really isn't a great solution because lots of data is duplicated.
I've thought about creating a new feature store table where I put all the new features but creating a new table every time I want to add new features doesn't seem sustainable. That said I did come across a post considering a solution where each feature was stored in a separate table!
I've read lots of feature store articles but none of them go into any depth about data structures and how to manage changes.
Would anyone here have any experience of how to design a feature store that has some form of version tracking built into the data structure? Do I just have to deal with the fact that lots of feature values need to be duplicated or is there a different structure I could consider?
feature_store
row there are circa 300 features in the actual database and they're all added at once through a singleINSERT
statement. I do log the time but only once per row. Are you suggesting I have a column for each feature value and a secondary column for when the feature value was added? The challenge will then be if I want to remove or update a feature but retain my audit trail of what features were associated with a given row at a point in time. The second datetime column wouldn't enable this? $\endgroup$