BSG investigated the existing infrastructure, which relied on SQL server for data storage and manual data dumps between two separate teams of the data used for modelling. BSG’s data engineering team worked with the data warehouse on better consolidation of the data, running SQL scripts to clean the data, process it and store it in a consistent format. 

Next, the data engineering team tackled real-time data streaming using Java and Kafka, focusing on one stream as a point of contact for the new technology that would need to be introduced. Due to the large payloads involved this required some manual mapping. 

Using Python, they simulated a real-time data stream. A script was created that read from a SQL database every five minutes and stored the new data in a SQLite database. The team maintained a table of the delta for all records, to provide a historical view of the data for future enhancements. Initially, using Jenkins and Rancher, the team built and deployed to a docker container that would keep the script running indefinitely. However due to infrastructure constraints, the solution was simplified to an isolated Jupyter notebook that would run only the streaming and modelling scripts.

Ultimately the SQL server was retained as the means of data storage, as infrastructure constraints precluded upgrading at this time. 

The handling of data in SQL was enhanced, however, through:

• Introduction of an automated monthly job to move data required for modelling from the warehouse to the database;
• Data processing was moved from the modelling Python script to SQL for greater speed and to minimise the load on the model; and
• Indexes were added to the data to increase the efficiency of queries.

The final dataset was stored in the format expected for modelling, improving the data storage in SQL and reducing the number of operations required by the model.

The BSG team made recommendations for making the working environment more agile. The data warehouse team shared their scripts with the data science team, providing visibility around their processes and requirements for future success.

On completion, BSG recommended that the client move to a NoSQL setup in the long term, SQL Server not being the best option for large data processing in this context. Real-time data streaming, while possible, would require some significant changes to the client teams’ ways of working. The individual product houses would need to be engaged to provide the data required in a consistent format.