“There are a lot of tools that promise that they can make Data Vault easier, but Coalesce is in the sweet spot of control and ease of use. We were really impressed by that on day one.”
Overloaded with legacy systems
Challenges
Frequent company acquisitions led to rapid growth and many new systems that needed to be integrated and aligned
Having to hand code everything ate up a lot of the team’s workday
Team struggled with performance issues with their legacy tooling
Ivo Goudzwaard is a Data Engineering Specialist on the Boels data engineering team, which is part of the analytics department. The team consists of eight data engineers, a master data management specialist, and a data architect. “Our job is to get data from our various source systems, create curated and sustainable data models from that data, and make those models available to our consumers within the business—especially the six business intelligence developers who make up the business analytics team,” he says.
Prior to our new solution, the data engineering team built dashboards in Qlik Sense that are used by employees throughout the company, from depot managers to board members. Qlik Sense powered the company’s analytics stack, and it had a large ETL component running in QlikView. “We’ve had a lot of different systems within Boels,” says Goudzwaard. “We also started in 2021 with the implementation of a data warehouse solution based on SQL Server. But we quickly came to the conclusion that we were pulling in way too much data for SQL Server to handle in a stable and performant way without a lot of tweaking and fine-tuning of the database.”
In 2020 Boels acquired equipment rental company Cramo, which is headquartered in Sweden and active in a lot of European countries. This acquisition nearly doubled both the company’s size and its revenue, as well as added yet another system for the team to manage. “Cramo has its own stack based on a SQL Server data warehouse and IBM Cognos reporting,” he says.
On top of needing to integrate and align all of these different systems, the team was facing a number of other challenges. With their previous solution, they wasted a lot of cycles having to hand code everything, which slowed them down considerably. “We were scripting everything by hand, which is time-consuming, error-prone, and requires a lot more time for testing and validating,” Goudzwaard says. “This was something we hoped to solve by automating all the boilerplate code and recurring logic.”
Goudzwaard and team were also struggling with performance issues with their legacy tooling: “The legacy systems were all on prem, so weren’t easily scalable,” he says. In addition to the various data architectures Goudzwaard was already trying to wrangle, the acquisition of Cramo introduced even more complexity to the mix. He realized that he needed to take an entirely different approach and build a new, modern data stack from the ground up. Goudzwaard decided to bring on Managing Data and Analytics Consultant Ralph Knoops of Nextview Consulting to help him design the best possible solution.
Constructing a better stack
Solution
Engaged Nextview Consulting to help design and develop a new data stack
Selected Snowflake as the platform on which to build their data stack
Adopted Coalesce as the data transformation platform and Fivetran for data ingestion
Goudzwaard and Knoops decided on Snowflake as the core data repository to build their new data stack around, brought on Fivetran for data ingestion and Coalesce for data transformation, and began migrating from their legacy architecture. They had decided early on to start modeling with Data Vault for a number of reasons, including improved performance, the need for a common data model, and to better align with the company’s M&A strategy, which meant that new companies (and their legacy systems) would continually need to be absorbed into the larger organization.
They were pleased to discover that Coalesce has a Data Vault package, developed by Scalefree, automating Data Vault development and significantly speeding up implementation. After spending more than a day deliberating about how the data model should look and designing it, the engineers used Coalesce to build it in just half an hour.
When assessing the different vendors and tools for possible inclusion in their new data stack, Goudzwaard and Knoops had one major goal: “Automate the boring stuff,” says Goudzwaard. “So everything that’s boilerplate, everything that’s repetitive, everything that pops up every time, we wanted to automate all that so we could focus on the important aspects. And we could see that Coalesce really delivers that.”
The two also appreciated that Coalesce automatically generates SQL code that’s perfectly optimized for Snowflake, but also allows more experienced developers to view the code and tinker with it as needed. This feature offered the best of both worlds, and stood in contrast to other popular solutions on the market designed to offer only one approach.
“We all love to code, but you don’t want to write all the same code over and over again,” says Goudzwaard. “You also don’t want a black box where you just drag and drop stuff and hopefully everything goes well. We really wanted something that enabled us to do the drag-and-drop thing while still knowing exactly what’s going on under the hood and being in full control of it. That’s where Coalesce was a strength.”
Better equipped for the future
Results
10x reduction in Data Vault development time with Data Vault package by Scalefree and Coalesce
Ability to create views on 50 Snowflake tables in 2 hours versus 2 days
400 Snowflake objects created within 10 minutes with Coalesce Nodes
For now, the data engineers are the only ones at Boels using Coalesce because they possess the knowledge and experience required to build the Data Vault. “We have one rule,” explains Knoops. “Everything needs to flow via the Data Vault, and the Data Vault needs to be done via Coalesce.” There are always exceptions, adds Goudzwaard, “but we try to limit those as much as possible because everything that we cannot build in the Data Vault using Coalesce is seen as technical debt.”
Because Coalesce automatically creates Snowflake-optimized code, Goudzwaard and Knoops have been able to standardize their workflow and avoid the time-consuming, error-prone practice of manually scripting everything and having to continually adjust it. Says Goudzwaard, “How many times do you get a strange error when executing a query in Snowflake, and it turns out you just missed a comma or semicolon somewhere? We don’t have that problem with Coalesce—we just add the node, change some settings, and we’re done.”
“If I do a new extraction in Fivetran, we get the new schema in Snowflake—say it’s 100 tables—then with Coalesce I am able to create views on all these objects to use for data consumption, but also the Snowflake stream nodes and the persistent staging nodes, within 10 minutes,” he says. “When you’re working with plain Snowflake code, if you make a mistake and need to adjust it, it can take you more like two days.”
Goudzwaard and Knoops appreciate how Coalesce helps the team to scale by democratizing development. No matter which team member is creating something, whether it’s a data engineer or a non-engineer, Coalesce generates exactly the same code. “You can imagine all those different architects, different tools, different people with different capabilities,” says Knoops. “That’s a lot to maintain, but moving toward one tool stack that includes Coalesce gives you repeatable pattern blocks that you can constantly reuse—whoever the engineer is. This means the output is always the same. And that’s the standard way of working or at least the standard patterns that we really like with Coalesce.”
Goudzwaard agrees: “With the drag-and-drop system, you enforce a certain way of working. You no longer need to type all the code yourself, and if you want to know what’s happening, you can just check out the node definition. You get all of the benefits with none of the work.”
As for future plans, Goudzwaard says that the team will strive to expand on what they already have: “We’re going to add to the Data Vault, add more data models, build out the common data model, and in the meantime also start integrating new companies. With Data Vault, the larger your vault is and the more tables you have modeled, the easier it will be to create new data models. And that’s something that Coalesce can really help us with.”
For newly acquired companies located in countries where Boels does not yet have a presence, the team will let those companies continue to use their legacy systems at first. “Of course, all those companies have new source systems, so that’s something that you need to harmonize—that’s Data Vault work. But, of course, our management wants to see that revenue in their dashboards. So we need to get that data from the source system and add it to the data model.”
Goudzwaard also sees Coalesce playing a key role in helping the data science team with their predictive modeling initiatives. Today, his team supplies data sets to the data science team, who are also migrating their environment to Snowflake and starting to use Snowpark. “We use Coalesce to create the data model for them and set everything up for them to use,” he says. “And hopefully in the future we can also start deploying models using Coalesce.”
Some use cases for predictive modeling would be product recommendations or additional equipment recommendations based on what other customers are renting and using for their projects. “Of course, with such a big fleet of equipment, the Holy Grail would be predictive maintenance,” adds Knoops. “Use all that IoT and telematics data to fix items in your fleet before they get rented out, which becomes costly. That’s one of the exciting features we hope to develop in the near future.”
