“Coalesce Node Graph allows us to get a bird’s-eye view of our complicated data transformation pipelines, and from there double-click and zoom into detailed column-level lineage. With the reusable Node Types, we can build data pipelines significantly faster, and the executed code in Snowflake has drastically improved query performance and accuracy.”
Battling creeping costs
Challenges
Initial data transformation solution led to ever-increasing Snowflake compute costs
High costs forced team to cut back to running daily jobs instead of hourly
Previous solution caused a number of ongoing data integration issues with Snowflake
Hilda Olekangal, Senior Manager of Data and Architecture, leads the team tasked with supporting and managing the data sources and data integrations for all of Q2’s business operational data. Her U.S. team includes a business intelligence analyst who specializes in Microsoft Power BI, which Q2 uses for reporting; two developers focused on Snowflake integrations; and a developer tasked with all other application integrations. In addition, there are two additional members from the India team supporting the various data pipelines.
Olekangal’s team supports nearly every division across the organization. “Our customers are the different departments within Q2: sales, marketing, finance, customer support, implementations, product operations,” she explains. “Data stewards are scattered across the company, and one of our primary responsibilities is getting data to them and making sure that data is accurate and complete.”
In addition to building their data warehouse in Snowflake, the team is also responsible for data synced between various SaaS applications such as Salesforce, Netsuite, Workday, and more. “We are very SaaS-heavy on the business operational side—we bring in data from multiple SaaS applications,” Olekangal explains. “We also support the reporting infrastructure for our company, which is on Power BI.”
Q2 initially built its data warehouse on SQL Server, with the team using Dell Boomi to bring in data from different applications, SSIS (SQL Server Integration Services) for data transformation, and Power BI for analytics. Three years earlier, the team had launched a project to migrate off of SQL Server to a cloud data platform. After evaluating multiple options, they landed on Snowflake, and brought on another vendor for integrations and data transformation.
However, this vendor turned out to be very expensive—hourly jobs took a full hour to run, so by the time one finished, it was time to kick off the next job. “This effectively meant the warehouse was running 24 hours a day, and our Snowflake bill started hiking up,” Olekangal says. At this point, she realized they could no longer run hourly jobs, and so cut back to daily jobs. But even then, she says, “There were still a lot of issues with integrating the data to Snowflake. We started to see the consumption—and the resulting costs—keep going up and up. Because we weren’t seeing the value-add for the price we were paying, we were left with the choice of rip and replace. So we started looking for a way to get our plumbing in order.”
Banking on a better solution
Solution
Adopted Fivetran for data ingestion
Brought on Coalesce as a data transformation platform
At the start of 2024, the team began the process of rebuilding their tech stack with Fivetran for data ingestion and Coalesce for data transformation. A big factor helping Olekangal make her decision to use Coalesce was the fact that the solution the team had been using previously also had a GUI interface, so Coalesce’s approach would not be a completely new way of working for them.
Says Olekangal, “I liked the fact that Coalesce had a user-friendly platform that generated the SQL code in the background, making it an easy transition from our old ETL tool to a newer solution.” Another big plus was that the platform also allowed those developers who did have SQL skills to toggle over into the code and customize or change it directly if needed. “Coalesce seemed to provide the best of both worlds,” she says.
“If you are coming from a traditional data warehouse background, Coalesce is definitely a different way of doing things,” says Olekangal. “It’s more efficient. It’s aligned with a data engineering paradigm but with a graphical user interface. So it’s a good transition product for people who are coming from traditional ETL.”
Free to focus on bigger things
Results
Migrated 400 objects in just over two months using Coalesce
Runtime for all jobs reduced by 60%, greatly reducing Snowflake costs
Reliability of Coalesce means team has more time to focus on bigger initiatives to move the business forward
The initial project was focused on migrating all of the data pipelines over from the old ETL tool, which they completed in just a few months. “The team did need to adapt to a new way of working at first,” says Olekangal. “Using things like GitHub and CI/CD (continuous integration and continuous delivery) were new to them, but they were able to migrate around 400 objects in a short period of time with Coalesce. We had huge help from the Coalesce Client Services team, who were awesome in supporting us during our migration efforts and made the project a big success.”
For Olekangal, two of the most important things Coalesce provides are lower costs and increased team efficiency. “A big benefit I see with Coalesce is the push-down queries to Snowflake, which have reduced the runtime of all our jobs by 60%,” she says. “We definitely see a big reduction in the cost of just the data transformations, and latency has also reduced quite a bit. Our jobs run much faster than with our previous solution, and we’re definitely seeing the results much more quickly in Snowflake.”
Coalesce’s ease of use ensures that all members on Olekangal’s team, no matter their skill level, can use the platform. “We wanted to make sure that every team member was participating in every aspect of this project,” she explains. “The great thing is that everybody on the team can work with the Coalesce platform—it’s not a specific skill set that’s limited to just one or two people. We’ve also done a lot of cross-training and today the team is more collaborative, and no longer siloed in their own technical areas. That’s definitely a plus with Coalesce.”
Coalesce’s reliability means the team no longer needs to worry about deployment errors and can focus their time and attention on more important work that moves the business forward. As Olekangal says, “Once Coalesce jobs are deployed, they just run. We hardly ever get any email notifications of errors. That’s a big savings for us in terms of operational support costs. The team is now free to focus on the next big thing because the Coalesce part just runs without any issues.”
As for Olekangal’s plans for the future, two pressing projects on her roadmap are to launch both customer 360 and product 360 views for the company. “This will require data sets from the business operational side,” she explains. “Data quality will be an important aspect to this, as we want to ensure we’re giving accurate and complete data sets to our business users, and that data lineage is visible to the users—and Coalesce is going to play a big role in all of this.”