“Coalesce just works.”
Hitting the firewall
Challenges
Previous data transformation solution was costly and inefficient
No standardization across development environments
Limited visibility into data pipelines
N-able’s Data Services team based out of Edinburgh, Scotland, includes a specialized set of data engineers and Microsoft Power BI engineers. One of the team’s chief responsibilities is managing data ingestion from its main product, N-central, gathering data from about 900 servers into Snowflake. This includes semi-structured JSON data about device information, software patch updates, server availability, backup data — any kind of data associated with hardware management. The team is also tasked with building data models and pipelines, which the Power BI engineers use to build dashboards for N-able’s end users to offer them insight into things such as patch compliance or system health across their own clients’ technology estates.
When team lead and Senior Data Architect Lachlan Macpherson first came onboard in 2021, the Data Services team was facing a number of challenges. One of the biggest was that their existing data transformation solution was proving to be too expensive to justify any longer. “It was hard for the business to sink that cost in terms of the ROI,” says Macpherson. “So there was a business buy-in to look for an alternative solution.”
In addition, he says, they were struggling with a number of pain points from an engineering perspective: “Our environments were entirely decoupled, so there was no standardization across environments. It was a very manual process to do deployments, which were done per environment. It was a major challenge to get visibility into the pipelines and what each step was.” All of this affected the team’s productivity and how quickly they could make changes. Says Macpherson, “All businesses want things done yesterday, so we needed to find a way to increase our productivity and efficiency.”
A big source of friction, explains Macpherson, was that their legacy platform wasn’t really designed for the cloud or for Snowflake, so they were facing constraints with things such as naming conventions, table lengths, and so on. “We felt we were hitting the wall quite a lot with the things we were trying to achieve,” he recalls.
Ready for an upgrade
Solution
Coalesce adopted as data transformation platform
Professional Services team helped prepare data for the migration
Migrated nearly 900 database objects from legacy platform into Coalesce in one month
To address these challenges, Macpherson led N-able’s search for a solution that could help improve the productivity of his developers. “Integration with some sort of well-understood source control was a key requirement,” he says. “We really wanted a modern SaaS solution so we wouldn’t have to manage the infrastructure, something we had been doing with the previous tool.” When N-able was introduced to Coalesce, it seemed like the data transformation platform would be the perfect fit.
“The migration into Coalesce from our previous solution, which is very opaque and difficult to get visibility into, went a lot smoother than I expected,” says Macpherson, noting that a big part of that was thanks to help from the Coalesce team. The Data Services team was able to migrate 380 database objects from N-central, and 500 database objects from its other core endpoint management solution, N-sight, in just one month of work hours. “I suspect that without that initial help, it could well have taken several months — it certainly made a difference in terms of getting up and running quickly in the environment.”
Because the team had already been using an automation tool, getting accustomed to using Coalesce did not require a huge mindset switch. “Having that migration piece was huge in terms of getting people up to speed, and now everybody is very comfortable using Coalesce,” says Macpherson. “We’re starting to notice things as we use it, gaining a deeper understanding into how deployments actually work, what the steps are, so we can get that fine-grained control of improving and extending what we’re deploying each time. The configurability of Coalesce really makes this possible.”
Optimized and operational
Results
Time to make fixes to SQL and deploy across all environments has gone from weeks to just 1–2 days, and now done with full confidence
Risk of data regression following deployments has dropped from 70% to zero
Coalesce provides the team with the four Cs: consistency, clarity, configurability, and confidence
A more productive team, operating with fewer resources than before
Not only has Coalesce helped to relieve the company’s earlier concerns about costs, now the team has deep insight into their overall spending. “One thing we have now that we didn’t before is much more granular visibility into cost, in terms of the instrumentation that’s provided down to job level and query level,” says Macpherson. “We can see exactly what our costs are across our pipelines and across all our jobs.”
Another big benefit Macpherson and team are seeing with Coalesce is a newfound consistency in their deployments: “As an example, we found a few defects recently — more to do with the SQL and how it was produced — and a team member was able to go into Coalesce, make the change, get it reviewed in GitHub with pull requests, and deploy across all environments in just a few days. Previously, this would have taken us weeks — and with little confidence. Now we can do it much more quickly with full confidence.” He points out that with the previous inconsistency problem, there was always some kind of data regression. “Now the chance of regression has decreased from 70% all the way down to zero,” he says.
In addition to consistency, Macpherson especially appreciates the visibility Coalesce provides into deployments: “During the migration we identified some bugs that we had never noticed before in our previous production environment, but thanks to Coalesce, we caught and resolved them. Since it’s source-controlled and standardized across all environments, the same version is deployed consistently. This was a significant quick win for us. We also noticed some discrepancies across regions, but having a unified deployment mechanism solves that problem as well. You deploy it, it deploys what it needs to deploy to every region, and it’s consistent — Coalesce just works.”
Macpherson explains that while N-sight uses a Data Vault approach and N-central does not, the team is planning to move away from Data Vault in the future. He notes a broader shift in the industry toward building more streamlined, well-structured models earlier in the process. Tools like Coalesce, he adds, make it easy to support both approaches within a single solution, which is a major advantage.
Macpherson credits Coalesce for providing the Data Services team with three important Cs: consistency, clarity, and confidence. “In fact, there’s probably a fourth C in there as well,” he adds. “Configurability, because the ability to have custom node types, as well as access to Coalesce Marketplace, are major benefits for us. For example, I wanted to get stored procedures and tasks into Coalesce for source control, and there wasn’t a specific package for that in the format I wanted. But I was able to create a node type template myself very easily to create those paths and packages within Coalesce.”
Macpherson can now customize, adapt, and push the boundaries of what the Data Services team can achieve, helping to ensure every client that depends on N-able operates at full signal strength.