In today’s data-driven world, ensuring that information is consistent across various systems is crucial. For one of Cloudorizon’s customers, the reconciliation process is vital in maintaining data integrity. Recently, James Lawrence, one of Cloudorizon’s Senior Engineers, shared insights into the current challenges and how he has utilised Workato to improve this customer’s reconciliation process. Here’s a closer look at his discussion’s key themes and notable quotes.

The Importance of Reconciliation

Reconciliation is essential for ensuring that data matches across different systems. As James highlighted, “You don’t want mismatches where you think you’ve got one contact in one system and a different contact in another system.” This process helps avoid discrepancies that can lead to significant issues down the line.

Current Challenge

Our customer’s existing reconciliation process had become cumbersome and inefficient. James described the situation, saying, “As functionality has been added to solve each problem as it arose (as opposed to aligning to the goals of the original design) the system has become harder to maintain and more susceptible to issues”. Some of the main issues included:

• Database Access for Interventions: Every intervention requires someone with database access.
• Inability to Run Simultaneously: Reconciliations cannot be run concurrently, leading to deadlocks.
• Manual Data Cleanup: Old data must be manually deleted before rerunning reconciliations.

Proposed Improvements

To address these challenges, a new design has been proposed. James emphasised, “The initial design is good. Let’s not change what already works right as a core principle.” The key improvements have included:

• Concurrent Reconciliations: The new design allows reconciliations to run concurrently without deadlocks.
• Reduced Database Interventions: The process is designed to minimise the need for database access.
• Extendable and Supportable: The new approach makes extending the process easier and more supportable.

Technical Details

The technical implementation involves several steps:

• Staging Data: Data is staged in a table and stored as JSON (JavaScript Object Notation) to make the process agnostic to the underlying data.
• ETL Operations: All ETL processes are moved explicitly out of the database and are now done within Workato. Along with the staging of the data as JSON, this allows the same process to perform a reconciliation no matter the objects that are being reconciled.
• Configuration-Driven Process: With this in mind, at a database level, all new reconciliations are now driven by configuration rather than writing new code for each reconciliation. This removes the need for a SQL resource in setting up a new reconciliation, reducing the technical cost of implementation.
• Pub-Sub Model: ETL processes are controlled using a pub-sub model, allowing for better concurrency management. In addition, by using a pub sub model to control the processes, each stage of the reconciliation is forcibly decoupled, which makes it easier to keep to the overall design when implementing new reconciliations.

Conclusion

The proposed improvements to our customer’s reconciliation process promise to make it more efficient and supportable. By addressing the current challenges and leveraging a robust technical design in the Workato platform, we have helped our customer achieve its goals of streamlining data reconciliation and ensuring data integrity across its systems.

As James concluded, “A primary goal of any design is to make sure it’s easy to extend and support” This new approach not only simplifies the process but also sets the stage for future enhancements, ensuring that our customer can continue to maintain high standards of data accuracy and consistency.

About Us

Cloudorizon is an integration company that is purpose-built for today’s tech challenges. We deliver transformative solutions tailored to our customer’s unique needs. Our expertisein integration and end-to-end process automation services not only cuts costs and boosts efficiency for our customers but also increases their business agility, surfaces valuable insights, and enables business resilience. This, in turn, empowers our customers to gain a competitive edge. We offer unparalleled expertise and a results-driven approach for measurable improvements to our customer’s operations. Get in touch today to see how we can help your business.