“What we like about Coalesce is that we can now bring employees up to speed and make transformations to data in just a couple of weeks, compared to months.”
Growing pains
Challenges
Rapid growth from acquisitions resulted in numerous ERPs and reporting tools
Data siloed across various departments
Legacy data automation solution was too slow and cumbersome to meet the needs of the business
PetIQ experienced tremendous growth over the past few years, including through acquisitions, resulting in multiple ERP systems and four different reporting tools, none of them enterprise level. These circumstances created data silos across the organization, which the data team identified as the core problem they needed to solve first. “Our team lead sold the need for a data warehouse here at PetIQ,” says Davidson. “It was a big push for our organization.”
Originally, the data stack consisted of a legacy data automation platform, a visualization tool, and eventually Snowflake as a central data repository. But the legacy data automation solution was cumbersome. It was hosted on virtual machines on the team’s computers rather than being a SaaS solution, which only added to the friction. “We quickly discovered that the business needed data much faster than we were able to provide it,” says Davidson. “We needed to move faster than our existing data automation solution allowed us to.”
To solve these issues, the team had to find a better way to load data into Snowflake as their existing system didn’t have the connections they needed. “We brought in Fivetran first, and then we started getting all this data into Snowflake,” recalls Davidson. “People got really excited because they could see the data and they could query it for the first time ever.” But even though the team was moving more data into Snowflake, they still couldn’t move fast enough in modeling that data and making it available for their end users.
Simple solution to a thorny problem
Solution
POC conducted on sales data from retail partners
Coalesce adopted as data transformation solution for new data stack
Migration from legacy data modeling solution to Coalesce
Davidson and team decided to bring on Coalesce for a proof of concept to see if it could help them solve a seemingly simple problem that had proved to be much more time-consuming and unwieldy than expected. With their previous solution, this kind of project would regularly take the team several months to complete.
“We decided it wasn’t something that was manageable,” says Siron. “But with Coalesce, we were able to tie data from different source systems into one project in literally 20 minutes. To get it all in there and just using something simple like the multisource stages was incredible. We didn’t even delve into any complicated Data Vault principles—it was just a very basic solution.”
Impressed by the results they saw from the POC, the team decided to adopt Coalesce and make it an integral part of the PetIQ data stack. They were able to complete the migration in just 6 to 8 weeks. “Nearly 90% of everything was done within 6 weeks,” Davidson says. “And then when we had everything migrated it was more of a cleanup, removing things we determined we didn’t need in our warehouse. It was really impressive how fast we were able to migrate our existing model to be managed by Coalesce.”
“Another thing that was really appealing when we were making this decision was the partnership that Coalesce has with Snowflake,” adds Davidson. “Being a strategic partner and using the best, most optimized Snowflake functions as compared to doing something in an agnostic format that is not optimized for Snowflake compute.”
Getting the Data Vault out of the doghouse
Results
Team now better able to track changes and find potential issues
Cleaned up Data Vault thanks to column-level testing
Visualizations help other teams gain a deeper understanding of the data
Back in the days of the original data stack, the team needed to work from three different systems to develop the data pipeline and did not have the ability to look at everything in one place, which made their work difficult. After adopting Coalesce, this is now a problem of the past. “We can now look at the visualization and those subgraphs all within Coalesce,” says Siron. “We can do any of the development in the tool itself, which immediately writes to Snowflake.”
In addition, the previous solution offered only a very manual form of version control. Today, Coalesce’s robust version control capabilities make it easier to track changes to data and help uncover potential problems in the data pipeline.“The integration with Git has been incredibly helpful for tracking down where there are issues, tracking back what we do,” Siron says.
The team had previously struggled to make changes to metadata inside of their existing data automation platform. This is why Coalesce’s column-level lineage was a huge selling point for PetIQ. “The column lineage feature has been extremely helpful for those modifications, just having that propagate down into the objects that feed off of that,” Siron says.
Before bringing on Coalesce, the data team struggled to help other teams across the company understand what was going on with the data—and, in fact, to explain what it was they were doing to help the larger business. “I don’t think you should have to be an experienced developer to understand data or understand what’s happening to the data,” says Davidson. “When somebody hears us say, ‘We’re gonna model this and do this’ when we’re talking about our day-to-day work, it’s just so abstract. That’s why it’s so helpful to be able to connect the dots for our end users and the business units that we’re supporting.”
Siron agrees that the visualization aspect of Coalesce has been an enormous benefit as the team strives to make PetIQ a more data-driven company: “You know how we can break apart the data into its individual pieces with the Data Vault methodology? You cannot explain the Data Vault methodology very well, but you can visualize it really well in Coalesce. It’s hard to explain that, but being able to show it very quickly with just a screen share demo in Coalesce is huge.”
The next big step for the team is to begin working on data modeling for the legacy ERP systems they need to consolidate, something they had tried to accomplish with their previous solution but that was constantly being put on the back burner. One of the biggest ERP systems that contains the most data was written in COBOL, so it’s very old and presents a big challenge, but it’s one the data team finally feels ready to tackle. “This is going to be a huge project for us, and something that will be a very big win for our team,” says Siron. “We’ll be like celebrities around here if we can get that data modeled and available to people.”
No matter how popular the team’s heroic data wrangling might make them in the company, they’ll still have a hard time stealing the spotlight away from the real stars of the office: the many adorable pets they share their workspace with. “When I first had my interview, there was a dog that just wandered into the interview room, which was hilarious,” recalls Siron. “We have dogs come into our meetings sometimes and try to lick your face when you’re talking about something important. It relaxes you immediately and takes the pressure off, reminding you not to take everything so seriously.”
