“Once we started to use Coalesce, it cut down the time to parse JSON data in half if not threefold. When I found out how much easier it was, I was blown away.”
Building a data foundation from scratch
Challenges
Data everywhere with no data warehouse to consolidate and manage it all
Improvised, makeshift solution was unstable and did not function well
Challenging to grow team because abundance of raw data required employees with advanced skills to manage it
When Reinagel was first tasked with developing the data team, she was really starting from scratch. “We didn’t have a data warehouse, and data was pretty much everywhere,” she says. “Our central application was our industry-specific ERP, which was very slow and very old. There were thousands of tables in there and many weren’t relevant to us. I couldn’t go in and optimize them. Some tables weren’t indexed correctly and there was no data dictionary—it was a big mess.”
Back then Reinagel was a one-person shop with no employees, and so she had to get creative, cobbling together an improvised, makeshift solution just to get the ball rolling, using pay-as-you-go Azure storage as well as Alteryx, the one analytics tool the company had at the time. “When Manny, my lead engineer, first started, he had to roll with the punches as we were still working through the infrastructure,” recalls Reinagel. “This really wasn’t a data warehouse—we were using an Azure storage container and working from raw data all the time.”
This made it very hard to hire new employees to scale her department, because it required people with advanced skills who could work with all this unstructured data all over the place. “Data was everywhere and we had no way to normalize it,” she says. “We were having to build one-offs. Because we had no data warehouse in which to model and curate data, if we had to create a report, we couldn’t hand it off to an analyst because we were always working with raw data. Not to mention, it was also really challenging to put security around all this data.”
A scalable, flexible solution
Solution
All data consolidated in one place with Snowflake
Worked with consulting firm phData to architect a new data stack
Brought on Coalesce as the data transformation solution
A big initiative for Alterman’s new incoming CIO was to tame the chaos by setting up a proper data warehouse to better manage the company’s siloed and disorganized data. After researching the best-in-class solutions on the market and assessing all the options, he decided to go with Snowflake. “I had already put in my pitch that Snowflake was my preference,” says Reinagel. “I’d vetted it myself at a previous job and felt it was best suited to our needs. We’re an older company but we’re growing quickly, moving into new markets, and doing exciting new things—we needed a solution that was going to be scalable and flexible.”
Once Snowflake was in place, the team started working with data services company phData to help build out their new data architecture. It was the phData consultant who recommended Reinagel’s team adopt Coalesce as the data transformation component of their new stack. The platform’s ease of use immediately appealed to the team, who realized they could get a lot done very quickly without worrying about a steep learning curve.
“The initial reason we chose Coalesce was because at that time we were very small—just my lead engineer, myself, and an analyst,” explains Reinagel. “I was worried about time to deliver given that the integration company was only going to build eight pipelines for us, and we still had a good amount of data that we were bringing in from two main sources: our ERP and some API data from our HR system.”
Reinagel says that her main goal at that point was to have a way to store the code and be able to scale data quickly, so she wanted something that integrated well with Snowflake. “We’d also been missing documentation, and I wanted a solution that would build that out for us,” she adds. “I didn’t want limited bandwidth or resources to continue to be an excuse for not having documentation.”
Sparking a new appreciation for data
Results
Engineer now able to model data and create several tables in just hours compared to several days or weeks
A week's worth of manual work reduced to a couple of clicks with Coalesce's column-level lineage feature
3x increase in data team productivity
According to Amanuel “Manny” Dandena, the team’s lead engineer, now that Coalesce is part of the Alterman data stack, it is much easier to parse the data they ingest, especially the API data coming in as JSON: “Before we had to create a long workflow process just to normalize that data, but with Coalesce, it’s just the click of a button.” Adds Reinagel, “Once we started to use Coalesce instead, it cut down the time in half if not threefold. When I found out how much easier it was, I was blown away.”
According to Dandena, “We currently have 830 tables refreshing in Coalesce across all our different schemas, so between me and one other engineer, we have a lot that we’re refreshing and managing. I’m at the point where I can work on modeling and creating several tables in just a few hours—before that, it would take us several days, weeks, or even longer. The fact that it can all run natively inside of Snowflake makes it so much faster. It’s almost instantaneous whereas before it seemed to take forever.”
Dandena also appreciates how the ability to customize nodes helps him streamline his work: “For example, applying masking policies is a lot easier because we just build that into our nodes. The only thing an engineer needs to do when modeling or curating data is put the kind of mask they want, whether full or partial, into the node field.” With Coalesce enabling the engineers to apply masking policies upstream to their columns, the data is clean by the time the business analysts see it in Snowflake. “We’re more productive now because we’re starting with Coalesce,” says Reinagel. “We’re able to think things through and nobody sees raw data anymore.”
Reinagel especially appreciates Coalesce’s column lineage feature, which allows the team to easily see all the impacts of any changes they make to a table. “Before when we would make a change, we had to hunt down every workflow, every BI report, every query that could have been written,” she says. “This could eat up an entire day of work or more.”
She shares the example of a key cost center field in the company’s ERP, a 10-character hex field with each character representing a different mapping to where that data goes. “Security is based off this field and it integrates with other applications—it’s really important,” she says. “We’ve had to revise this field twice in the four years I’ve been here. It used to be seven digits but we increased it to the full capacity of what that field allowed. Since there was no rule in place, that was actual logic that had to be written to every BI report or every Alteryx workflow. So when that changed, I had to go into every BI report out there and make those changes, which took about a week—and I don’t even know if I caught everything! But now with Coalesce we can do all this with just a couple of clicks. It saves us hours of time.”
According to Dandena, Coalesce also helps the team field various ad hoc requests from other teams more quickly. “Most of our data refreshes daily, so sometimes it can be a day old,” he explains. “But there are times when we need something more instant, with up-to-the-minute data. Being able to refresh the tables on the backend of the Coalesce workflow makes it a lot easier to bring in the current table data. For example, for many of the fact and dimension tables created from an upstream table, we can just create a one-off job to refresh it once.”
Reinagel and her team are working to change the culture at Alterman and help other teams across the organization better appreciate the value of data, as evidenced by the steady increase in data requests coming across their desks. “We’ve got more and more data requests coming in—I feel like they’re snowballing,” says Reinagel. “What’s changed is that there’s now a separation of duties and responsibilities. If we get a new request and we’re bringing in data, it’s an engineering process first.”
Reinagel notes she’s always checking in with the team to make sure they have the necessary resources and they’re not having any problems. “From my perspective as a manager, I see how much more work we’re getting done,” she says. “I feel like compared to last year, Manny’s capacity alone to build out things has tripled.”
According to Reinagel, sometimes the team gets data requests for projects that have already started. “That’s why having a product like Coalesce is what we need because it helps us to move quickly,” she says. “For example, we’re working on a new data acquisition project with a tool company, whose API is brand new and developed specifically for us. This initiative came from the CFO directly, and needs to get done in just a few months. A year ago I would have said, ‘No way, we’re not able to do that.’ But thanks to our new data stack, we’re ahead of the game—we already have our part done and are just waiting on operations. Last year, it would have been the other way around.”
“We’re still a small group in my opinion, so I’m looking for any kind of automation that can give us a leg up,” she says. “Right now the team is trying to get more cohesive and be more efficient, and trying to build some new processes now that we understand how to use our new tech stack. Executive leadership has given me the tools I need—and allowed me to build the team I need—to deliver what Alterman ultimately wants: to become a data-driven company.”