Training dataset design for a recommendation service
Jake Tae-moon Jo · Engineering lead · https://jake-jo-portfolio.vercel.app/en/work/recsys-data-prep/
Designed the schemas and the cleaning and extraction SQL for three datasets to upload to a recommendation service, in three days.
- Period
- Jan 2021 (3 days)
Problem and context
A recommendation service needed training data. It took three datasets as CSV files: interactions, users and items. They had to be built from the member, order and product tables of a service in operation.
The source held values that were empty or outside the expected categories, so it had to be cleaned first. The work also needed a procedure for moving the source tables into a local database.
How the data was meant to reach the recommendation service
The work went as far as moving seven production tables locally, cleaning them and extracting from them; CSV generation and upload have no code.
- 7 production tablesExternal
Member, order and product sources
- Export and local loadPerson
A procedure for moving the source tables into a local database
- Cleaning SQLSystem
Turns empty and out-of-category values into null; 2 queries
- Extraction SQLSystem
Users and items written; interactions left as a TODO
- 3 CSV filesSystem
No generation code
- Upload to the recommendation serviceExternal
No record
What I built
- Designed three dataset schemas: interactions, users and items, with the categorical fields marked.
- Wrote two cleaning queries that turn empty and out-of-category values into null.
- Wrote two extraction queries, one for users and one for items. The interaction query was left as a TODO.
- Documented the procedure for moving the source tables into a local database.
- Started with an ORM and test tooling, then removed them to keep things simple, leaving only SQL and a DB client.
Project facts
- Period
- Jan 2021 (3 days)
- Role
- Sole developer (design and SQL)
- Team
- Me alone (8 of the commits)
- Platforms
- SQL and Node.js scripts, design document
- Stack
- SQL · Node.js · MySQL client
- Context
- Building training datasets for a recommendation service from the database of a service in operation
Results
I got as far as three schemas and the user and item extraction queries; the interaction query and the CSV generation were not built.
- Wrote three dataset schemas and the user and item extraction queries in three days.
- The interaction query was not written. The only runnable code checks the database connection, and no record shows a CSV being produced or uploaded to the recommendation service.
A short job that stopped at the design document and a skeleton. There is no figure to claim as a result, so none is given.
Three days of design work
The first day produced the design document and schemas, the second removed the ORM, and the third corrected the schemas, finishing in three days.
- Start
Design document and schemas
- ORM removed
Only the DB client and SQL left
- Schema corrections
Document links tidied