“My team loves using Coalesce. Thus far, we have built nearly 1,000 database objects and counting. The speed at which we can develop a pipeline is astounding.”
A model in need of repair
Challenges
Numerous company acquisitions had resulted in a fractured data landscape, making it impossible to establish a single source of truth
The team was running large analytical queries against a transactional database that wasn’t designed to support these workloads, leading to performance and stability issues
Existing BI tool enforced data limits, resulting in steep price hikes when the team wasn’t able to stay under a certain gigabyte threshold
The company had brought on its current BI tool, Sisense, 10 years earlier. When thinking back to the state of the company’s previous data stack, Fry admits, “I’d hesitate to even call it a data stack, to be honest. It consisted of various read replicas of production databases that we could query for ad hoc analysis.”
This was sufficient given the size of the company at that point, explains Fry, “But over time it had become increasingly difficult to operate in that fashion—especially with the amount of data we were eventually loading in there.”
Because the company had grown rapidly from a number of company acquisitions and the state of reporting for each varied greatly, the team struggled to gain access to a single source of truth for its data. Any reports needing to encompass all banner companies usually meant that they had to be manually compiled. “Data could be in four different places, or there could be five different ways to calculate it,” he says.
One big challenge the team faced was the issue of performance and stability: “At the end of the day, we were still running these massive analytical queries against a transactional database that wasn’t designed to support the workload, so we’d often experience build failures. It could take hours to get back up, and we’d have to cancel all these other builds that were supposed to be going on intraday.”
Another problem, adds Stanley, was that “our business intelligence tool was limiting us on data. So the price was going to go up if we couldn’t get under a certain threshold of gigabytes.”
Rebuilding for the road ahead
Solution
Selected Snowflake as a data warehouse to store all of its business data
Brought on Coalesce as the data transformation solution
Engaged with the Coalesce Jumpstart program for faster onboarding
The team decided it needed to modernize its data stack, and the first step was choosing a platform to store all of the company’s data. After they eventually landed on Snowflake, Fry and Stanley began looking for the right solution to handle data transformation.
“What really drew me to Coalesce was that it does all the things other tools on the market can, but it gives you a better interface to do it with,” says Fry. “You don’t have to hand code everything. Coalesce is not only generating SQL, it’s generating the YAML files, the projects, and everything that you would otherwise have to do by hand.”
Fry and Stanley appreciated that Coalesce’s ease of use made it easy to start using the platform right away, without a steep learning curve to get up and running. This meant it would make it easier for any new team members to get onboarded with it in the future. “It couldn’t have been easier to implement Coalesce,” says Fry. “The documentation describing how to get connected and set up the account was spot on. And thanks to the Coalesce Jumpstart program, we were able to hit the ground running.”
Another important factor in their decision was knowing Coalesce’s ease of use would benefit other teams throughout the company who could leverage it for their own needs. “There are some data people in our other banner companies, and Coalesce provides a way for less-experienced analytics developers to jump in and contribute without having a ton of knowledge,” explains Fry. “This is helpful because they know their data better than we do as they’re working with it every day. It’s easy for us to teach them how to use Coalesce just to get the ball rolling.”
Racing toward a successful future
Results
Migrated massive data sets out of previous solution into Snowflake 4x faster using Coalesce
1,000 database objects built in just over 4 months with only two engineers working on the project part time
Enabled the data team to adopt software development lifecycle (SDLC) best practices
The team first began using Coalesce to help migrate its biggest data sets out of Sisense. “Before Coalesce, all BI development was hand-coded SQL and it wasn’t version controlled,” Fry says. “If we had done the migration by hand, it probably would have taken us a month or two just to trace the lineage and figure out what we needed to build.”
“Coalesce helped us stop doing the same work more than once,” he adds. “And the fact that we’re not hand writing code anymore, we’re instead having it auto-generated, has also been immensely helpful.”
Stanley stresses that some of these were really big data sets that took weeks and weeks to migrate. “But it would have taken us so much longer without Coalesce—probably about four times as long,” he says. “We basically finished one of our biggest data sets in roughly four to five weeks. Without Coalesce, it would have taken us a month just to look at the data. Instead, we finished the whole project in that time.”
According to Fry, “In the past four and half months we’ve been able to build nearly 1,000 database objects using Coalesce, with only two people working on the migration part time. If we had done that by hand, it would have taken at least a year if not longer.”
Fry and Stanley appreciate how quick and agile their team can be when troubleshooting and fixing data issues with Coalesce. “We can hotfix nearly anything in 10 minutes if we can identify the problem,” says Fry. “For example, say the calculation for weeks of supply is off because we misplaced a comma or a decimal—we can fix that pretty quickly. So it’s much easier for us to keep fresh data in the hands of the business.”
Fry appreciates how Coalesce allows the team to spend less time coding, and combined with Snowflake, provides them with a very powerful analytics engine at their fingertips: “We have already fielded a few new development requests that we wouldn’t have been able to accomplish with our previous platform. The necessary tools just weren’t available. As we continue our journey, I have no doubt that we will be able to continue to level up, and provide insights previously thought impossible.”
What makes Coalesce an ideal solution for users of any skill level, says Fry, is not only the fact that it automatically generates optimized SQL code behind the scenes when you’re working in the easy-to-use visual interface, but that it also allows those data engineers with more experience writing SQL to go into that code and customize it if needed.
“I’m a firm believer that it’s always going to be faster to generate code than to write it by hand. I think the real power of Coalesce is that you can do both,” says Fry. “You can go in and modify anything that Coalesce generates if you need to, and push it into your repo. But you also have very fine-grained control over the way the code is generated because you can write your own node definitions and it will do exactly what you want it to do—it really is the best of both worlds.”
He also credits Coalesce with helping the team become more disciplined in following development best practices when deploying code, something they weren’t doing before they brought on the platform. “Coalesce has easily enabled us to adopt practices more consistent with SDLC best practices. In the past we were editing things in a live environment, and there were times when things would break because we did something poorly. Following best practices around having a dev environment and testing things before we post them was something we wouldn’t have been able to do before. Being able to do that with Coalesce has really changed how we work.”
As for his team’s future plans, says Fry, the next phase of their project will be to build out their data warehouse: “The data we’ve migrated up to this point is modeled poorly—it just grew out of the transactional schema our data existed in. So we really want to put a focus on modeling for analytics and designing from the top down, rather than from the database up, with a real focus on organization-wide reporting. And Coalesce is going to be central to this effort.”
“We’re still in the early stages of modernizing our data platform, but with Coalesce and Snowflake at the heart of our strategy, we have already seen increased stability and can provide valuable data to our end users more often,” he says. “In addition, Coalesce has enabled us to take on the monumental task of rebuilding all existing pipelines in Snowflake without much impact to the current platform—all while continuing to field new data requests from the business.”
As Fry puts it, “Coalesce just works. Given the fact that it’s very focus-built for Snowflake and for the task at hand, I really don’t know that there’s a better tool on the market. If there is, I’d love to see it.”
