0
$\begingroup$

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:

  1. match contains details of the players and tournament (including location)
  2. weather contains details of the latest weather forecast for a given location
  3. feature_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?

$\endgroup$
7
  • $\begingroup$ Just use Git? Not GitHub just Git. $\endgroup$
    – M__
    Commented May 22, 2023 at 23:04
  • $\begingroup$ Maybe I don’t know the full extent of git but I’ve always used it as a tool to manage my code base - how would I use it to manage GBs of feature data? $\endgroup$
    – Jossy
    Commented May 23, 2023 at 2:27
  • $\begingroup$ There's one point in your question that's not clear to me. If the only time you update existing records in the feature store table is to populate new feature columns, why can't you simply keep a record of when each feature was added? Then to retrieve the records as they were at a particular time, you simply use that information to filter out any features that have been added since then. What's the reason a solution along these lines doesn't work? $\endgroup$
    – Lynn
    Commented May 24, 2023 at 9:49
  • $\begingroup$ Hi @Lynn - in each feature_store row there are circa 300 features in the actual database and they're all added at once through a single INSERT 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$
    – Jossy
    Commented May 24, 2023 at 14:47
  • $\begingroup$ @Jossy - I was thinking more of a separate table for your audit trail. I'll explain in an answer if I think it might help you. But first I have a couple of other questions. When you remove or update features, do you only do this for all rows at once? Also, in this case, do you need to save the existing rows values for the feature, or do you just need to know that the feature was updated or removed? $\endgroup$
    – Lynn
    Commented May 25, 2023 at 0:14

1 Answer 1

1
$\begingroup$

A common approach is to keep audit information separate from the current version of the data. So in your case, the feature_store table would contain just the current version of each row and another table (say feature_store_audit) would contain the information needed to reconstruct past versions of the rows. This solution allows you to efficiently access the current data.

There are a few ways of setting up the feature_store_audit table, depending on your requirements.

  1. The most obvious one is that the feature_store_audit table contains a complete copy of every version of the row, plus versioning information such as operation, start_date and end_date. This allows you to easily find historical data, but is inefficient storage-wise especially if the feature_store has a lot of columns.

  2. Another solution is to make the feature_store_audit table more like a logging table. It should have columns like primary_key, action_date, action, feature_name, old_value, new_value. Each time you add, delete, or update features you add a row to the audit table for every row and feature combination changed. This solution is more efficient storage-wise (unless you update a lot features at once) than the first solution and makes it easy to see changes to individual features, but it's harder to reconstruct historical versions of entire rows.

These first two solutions are fairly standard auditing methods and can be maintained using database triggers, if you want to automate the auditing.

A third solution is a sort of combination of both. This would only work if you always add/delete/update features/columns for all rows in the feature_store table at the same time. This solution needs two extra tables, called (say) feature_store_audit and feature_store_column_hist. feature_store_column_hist keeps a log of column changes - so it records when a column is added, deleted, or updated. It needs columns such as audit_column_name, feature_name, start_date, and end_date. The feature_store_audit table then contains only one row for each feature_store record, but a new column (called the same as the audit_column_name) is added each time a feature is added or updated (deleting a feature would result in the end date in feature_store_column_hist being updated, but would not change the feature_store_audit table). The feature_store_column_hist is maintain as follows:

  • to add a feature_store column, create a new record for the column.
  • to update a feature_store column, set the end date of the existing column record and create a new record for the updated column using a new audit_column name.
  • to delete a feature_store column, set the end date of the existing column record.

To reconstruct historical records, you would get a list of columns that were "active" at the required time, then select just those columns from the feature_store_audit table. It's a more complicated solution, but avoids the main disadvantages of the previous two solutions.

$\endgroup$
1
  • $\begingroup$ Thanks a million for this Lynn. Had a scan through and definitely seeing some SCD concepts in there - been reading about these! I'm actually heading off on holiday again tomorrow so will digest things and get back to you asap! $\endgroup$
    – Jossy
    Commented Jul 18, 2023 at 21:05

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.