“It’s so much easier to use data than it has been in the past. Coalesce has been a game-changer for us.”
Rethinking an outdated approach
Challenges
Insurance brokers were building monthly Excel reports, which took each of them 80 to 100 hours a month to create
Pilot program using a workflow management tool proved unsuccessful because solution was difficult to use and not widely adopted
Employees were doing data transformations on their own using Microsoft Power Query, which was a slow and inefficient process
Clinical informatics is a field that combines healthcare data, information technology, and data science to improve healthcare outcomes, patient care, and health information management. Dr. Kryijztoff Novotnaj is the Chief Clinical Informatics Officer at HUB International, overseeing the clinical, pharmacy, and analytics teams, all of which work closely together and rely on the same data to serve different verticals. His focus at HUB is to think creatively about how to use data to improve the lives of the people HUB insures, as well as help HUB’s clients choose the right insurance plans at the best rates.
After having grown rapidly through a number of mergers and acquisitions, HUB International found itself at the point where it needed to harmonize a number of different processes, procedures, and protocols, of which analytics was a crucial part. “Each firm we acquired had its own analytics systems, its own compliance, its own pharmacy programs,” says Novotnaj. “But as we’ve grown into this large organization, it’s become very inefficient to manage many systems. There are 10 to 15 different warehouses out there, and it’s hard for a small team to manage them all, let alone make them talk.”
Novotnaj explains that the venerable insurance industry, which has been around for centuries, tends to be slow to embrace change. HUB insurance consultants were relying heavily on manual tools such as Excel to do their reporting. They would take monthly data from insurance carriers, which they either received via email or downloaded from the carriers’ websites, and then spend upwards of 80 to 100 hours a month creating Excel reports from that data.
“The insurance industry is where banking was 20 years ago,” he says. “People have a hard time emancipating themselves from Excel and PowerPoint, and they still rely on very outdated forms of technology—we still use abacuses! And so we here at HUB are taking a leap to do things bigger and better to change our industry. We have to do it because the world’s changing quickly around us.”
Having access to reliable and accurate data is essential so that HUB’s consultants and actuaries have better insights into the employee population they are helping to secure benefits coverage for. They need to be able to answer a slew of questions in order to paint an accurate overall picture. What are the general demographics of the group? What is their average age, income level, education level? Are there specific environmental factors where they are located that could affect their health?
Novotnaj has seven medical and pharmacy clinicians on his team as well as one health actuary and one data scientist, who keep abreast of health trends, offer insights, and help build health profiles. If a number of employees has a certain medical condition, they help determine the severity and frequency of the risk and the costs for doing nothing to address that risk. They also review the data to answer the hard questions: “Does this group of individuals have high morbidity or not? How healthy are they collectively? Are there solutions to mitigate the exposures?”
With huge amounts of data coming in from hundreds of sources—each often formatted in a different way—Novotnaj knew the company needed to move away from manual manipulation of data using outdated tools and methods. In addition, the strict data privacy rules around patient PII and PHI enforced by regulations such as HIPAA made the data picture even more complex.
Initially, Novotnaj and team attempted a pilot program with a workflow management tool, but the team didn’t find it very user-friendly. “Instead, everyone was doing their data transformations by taking the data and putting Power Query on top of it to normalize the data,” says Novotnaj. “This was a very inefficient process and they were just wasting bandwidth.” To really turn things around, Novotnaj knew he needed to rethink his approach from the ground up.
Telling a story with data
Solution
Partnered with solutions provider DigBI Consulting to develop a more agile, modern data stack
Adopted Snowflake as central data lake repository and Coalesce as data transformation solution
Brought a number of new solutions together to develop innovative “Infused Analytics” system
Novotnaj had ambitious plans for rebuilding the company’s data strategy. He envisioned a new approach to data reporting and analysis that would be called “Infused Analytics,” a proprietary system that would leverage a host of technologies to redefine the landscape of analytics with a blend of human intuition, clinical predictive modeling, and actuarial and data science.
But knowing how overburdened with projects the company’s corporate IT was, and how long it generally took to make decisions and new implementations, Novotnaj decided to bring Lee Derks from DigBI Consulting on board to help re-architect the data stack.
Novotnaj envisioned building a next-generation analytics system that would offer advanced clinical predictive modeling, hyper-automated financial reporting, multi-data synthesis, and instant and interactive visualizations, among other things. To begin building this Infused Analytics system, Derks introduced Snowflake to HUB to serve as its central data lake repository, along with Coalesce to handle the data transformation component.
Together, Novotnaj and Derks began the work of harmonizing all the aggregate systems from HUB’s many acquisitions so they could put them all under Cedar Gate, HUB’s main healthcare platform that is specifically architected for their industry. Then, they sought to enhance the data by adding a number of new sources, including health data from organizations such as Johns Hopkins University. “We had an initial data pipeline set up with Coalesce within a week,” Derks says. “Implementation was quick and smooth.”
Faster pipelines, faster decisions
Results
Built entire data warehouse in under 100 hours, saving HUB approx. $54k in labor costs
Now only takes 1–2 minutes to run complex ETL jobs versus 3–5 hours
Launched innovative new Infused Analytics system across the organization, built upon Snowflake, Coalesce, and several other technologies
Today, as data comes in from hundreds of insurance carriers and other sources, Novotnaj and Derks can leverage Coalesce to transform all that data into a single format for consumption, which allows them to produce visualizations within seconds of data upload in Fusion, HUB’s proprietary informatics dashboard and visualization tool.
“Once we get the data from the insurance providers, it’s available in analytics within a few minutes—almost in near real time,” says Derks. “This removes the need for HUB employees to spend countless hours building Excel reports. It’s fantastic.”
Derks continues to be impressed by the speed with which he can now work thanks to Coalesce. Recently, he needed to build a new data pipeline related to some of the RFP features he had built for HUB. The old pipeline was ingesting large amounts of unstructured pharmacy data, which was saved in a JSON format. “The time it took us to do this in Coalesce was incredible—just about 2 hours or so to create the transformations for all these numerous unstructured files,” he says. “If we were to do that by hand, it would be a number of days if not weeks. So that’s a huge time savings.”
Derks is also struck by the speed with which ETL jobs run: “They’re complex jobs, dealing with large amounts of data. One job calculates more than 20 million rows of data every hour, and that completes in less than 2 minutes in Coalesce. I’m used to ETL taking anywhere from 3 to 5 hours, but because Coalesce allows us to process multiple nodes in parallel, everything’s very fast.” Most ETL jobs now run in less than a minute with Coalesce, with the longest, most complex job taking only five minutes to complete.
Another improvement Novotnaj and Derks have made to HUB’s operations was to automate the insurance renewal process. Before the Infused Analytics system was in place, actuaries and others at HUB would have to manually pore through past data in spreadsheets to figure out the best insurance renewal rates. Now determining renewal rates is automated thanks to Snowflake and Coalesce.
Using Coalesce has resulted in a huge ROI and cost savings for Hub International. When compared to the time it would take for an experienced data engineer to handwrite the DML, Derks estimates that using Coalesce has resulted in approximately $54,000 in savings so far to build the data platform for his client. “We built the entire data warehouse, which includes four supporting data marts, in less than 100 hours,” says Derks. “Again, this data is not simple—it’s complex. But due to the ease of Coalesce, we’re able to build it quickly, resulting in significant cost savings for Kryz when it comes to development of ETLs and transformations. He’s now able to take those dollars he would have spent on that and apply them elsewhere.”
While Derks and his team have been the initial users of Coalesce within the larger organization, the benefits the platform is bringing to HUB are becoming evident to those who are seeing the positive changes. “HUB’s Director of Analytics sees Coalesce and he calls it magic. It’s like voodoo—he can’t believe it’s so easy,” says Derks. “Just right-click on a node and it creates a dimension or a fact. He’s used to traditional methods of writing out the SQL, so he loves it.”
This is noteworthy, stresses Novotnaj, because this director had always been an advocate of Excel and Power Query: “He came from that world, so to have him see the power of Snowflake and Coalesce, his perspective has changed completely. I don’t know if he’ll ever go back to his old way of doing things because of what we’ve been able to show him.”
As for future plans, the team is working on additional AI capabilities and is looking into the custom nodes from Coalesce that enable implementing ML functions with Snowflake Cortex. They are also looking into using Document AI to scan and extract data from PDFs and potentially improve the accuracy and speed of renewals even more.
Novotnaj acknowledges that what he’s trying to do, especially around AI, is not common at all in the insurance industry, and this is not just simply innovation or another data system. Rather, HUB International is taking a leap into the future of data analytics and insurance and healthcare consulting.
“This is very transformative for our industry. We’re not transforming this piece by piece, we’re blowing it up and being a disruptor to insurance,” he says. “A lot of this is possible because of Snowflake and Coalesce. It’s so much easier to use the data than it has been in the past. Coalesce has been a game-changer for us.”
