Migrating from SQL to OneLake with Trilogy Care

In today's fast-evolving healthcare landscape, the ability to manage and analyse vast amounts of data has become a critical asset for companies like Trilogy Care. As an organisation committed to providing innovative, patient-centric care, Trilogy Care recently undertook a transformative journey: migrating from a legacy MySQL database to a more advanced, scalable, and future-proof data solution powered by Microsoft's OneLake, using a "Medallion data infrastructure."

This blog post will walk through the step-by-step process of that migration and highlight how Trilogy Care is preparing for the future, with seamless integration with Xplainable's model-serving and tracking capabilities across the entire data lifecycle.

Why Migrate from MySQL?

For many companies, MySQL has been a workhorse - efficient, reliable, and simple to implement. However, as data grows in volume and complexity, traditional relational databases like MySQL often fall short when it comes to the scalability and advanced analytics needed in modern healthcare.

At Trilogy Care, the primary drivers for the migration included:

• Scalability: The need to handle large volumes of medical and operational data whilst maintaining performance.
• Real-time analytics: Moving from batch-processing workflows to real-time data analysis to improve patient outcomes and internal efficiency.
• Seamless integration: Preparing the infrastructure for more advanced model-serving capabilities to support predictive analytics and personalised care.

The Medallion Data Infrastructure

The Medallion architecture forms the backbone of Trilogy Care's new data ecosystem. It's an approach that divides data into distinct layers (Bronze, Silver, and Gold), ensuring structured, reliable data pipelines that facilitate everything from raw data ingestion to high-quality analytics-ready datasets.

1. Bronze Layer (Raw Data Ingestion): All source data from MySQL was first transferred to the Bronze layer in OneLake. This stage was essential for capturing and storing raw data as-is, providing a durable record of historical information.
2. Silver Layer (Cleaning and Enrichment): In the Silver layer, Trilogy Care's data engineers cleaned, transformed, and enriched the raw data. This is where critical data processing took place, such as de-duplication, standardisation, and applying business logic to prepare data for deeper analysis.
3. Gold Layer (Aggregated, Analytics-Ready Data): Finally, data in the Gold layer is fully refined and ready for advanced analytics, machine learning, and reporting. This is the layer most utilised by business analysts, data scientists, and other stakeholders for decision-making and real-time applications.

By adopting this structured Medallion architecture, Trilogy Care ensures that its data pipelines are not only efficient but also reliable and maintainable over time.

The Migration Process

Migrating from MySQL to OneLake required careful planning and execution. Here's a breakdown of the process that ensured a smooth transition:

1. Data Audit & Mapping: The first step involved conducting a thorough audit of Trilogy Care's MySQL schema and data structures. This allowed the team to map the legacy system to the new OneLake environment effectively.
2. Data Extraction: A set of ETL (Extract, Transform, Load) pipelines were created to extract raw data from MySQL, ensuring no data loss or corruption. These pipelines were also responsible for loading the data into OneLake's Bronze layer.
3. Data Transformation & Quality Control: The next step involved transforming and validating the extracted data. At this stage, the data was cleaned and enriched, ensuring it conformed to Trilogy Care's internal data quality standards before moving to the Silver and Gold layers.
4. Testing & Validation: The team employed rigorous testing to validate the integrity and accuracy of the data post-migration. This involved comparing old MySQL data with the OneLake system to ensure consistency.
5. Implementation of Medallion Structure: With the data validated, the next step was to implement the Medallion architecture to handle different layers of data. This allowed Trilogy Care to gain a unified view of all its information, from raw medical data to insights-driven analytics.
6. Integration with Xplainable: To prepare for future data science initiatives, Trilogy Care seamlessly integrated with Xplainable's model-serving and tracking platforms. This integration allows models to be developed, tested, and deployed on clean, validated data from the Gold layer, ensuring a streamlined machine learning lifecycle—from data preparation to monitoring deployed models.
7. Staff Training and Change Management: A comprehensive training programme was implemented to ensure all staff were comfortable with the new system. This included workshops, hands-on sessions, and the creation of detailed documentation.

Benefits of the Migration

The benefits of migrating to OneLake with a Medallion infrastructure extend beyond scalability and analytics readiness. Some key benefits include:

• Unified Data Architecture: Trilogy Care now has a single source of truth, ensuring consistency across departments.
• Advanced Analytics: The ability to easily plug into AI and machine learning models, powered by Xplainable, allows Trilogy Care to leverage predictive analytics to provide personalised patient care.
• Scalable & Future-Proof: With the Medallion architecture in place, Trilogy Care's data infrastructure can scale as the company grows, supporting larger datasets and more complex analytics workflows.
• Real-Time Insights: Faster access to analytics-ready data allows Trilogy Care to monitor patient outcomes and operational efficiency in real-time, leading to quicker and better decision-making.
• Enhanced Data Governance: The structured approach of the Medallion architecture facilitates better data governance, ensuring compliance with healthcare regulations such as GDPR and NHS data protection standards.

Future State with Xplainable Integration

The integration with Xplainable further enhances the potential of this migration. The combination of Trilogy Care's rich, curated data and Xplainable's advanced model-serving capabilities ensures that predictive models are easily deployed and tracked, improving transparency across the lifecycle of AI initiatives. This paves the way for Trilogy Care to harness AI responsibly and at scale, empowering them to meet their goal of continuously innovating patient care services.

"The migration to OneLake has been a game-changer for us. It's given us the flexibility to scale our data operations and future-proof our data roadmap. We're now able to integrate advanced AI and machine learning workflows effortlessly, which is helping us unlock new insights for better patient care. Moreover, the enhanced data governance capabilities have significantly streamlined our compliance processes."

Hossein Jafari Majd, Data Lead·Trilogy Care

Challenges and Lessons Learned

While the migration was ultimately successful, it wasn't without its challenges. Some key lessons learned include:

• Importance of thorough planning: The initial planning phase was crucial to the project's success. It helped identify potential pitfalls and allowed for contingency planning.
• Communication is key: Regular updates to all stakeholders helped manage expectations and ensure everyone was aligned throughout the process.
• Phased approach: Implementing the migration in phases allowed for iterative improvements and minimised disruption to day-to-day operations.

Conclusion

Trilogy Care's migration from MySQL to OneLake highlights how modernising data infrastructure can transform healthcare operations. With their new setup, they’ve significantly improved data management efficiency and laid the groundwork to harness advanced analytics and AI for more accurate, real-time patient care. By integrating Xplainable into this process, they’ve also ensured their systems are ready to grow and adapt in the future.

If you’re aiming to improve your own data capabilities, Xplainable offers a practical, comprehensive solution:

• Auto-Training & Model Insights: Xplainable’s platform allows you to seamlessly prepare data, train models, and monitor performance all within one application. You can quickly build and evaluate machine learning models, simplifying deployment while maintaining full control and visibility at every stage.
• Fractional Data Science: For more complex data challenges, our expert data scientists are available on a flexible, as-needed basis. This means even organisations without in-house data teams can access specialist support to solve their unique problems and drive insights from their data.

By combining an easy-to-use platform with on-demand expertise, Xplainable helps businesses of all sizes unlock the potential of advanced analytics and AI—without needing extensive internal resources.