“We have rewritten one of the point of sale transaction log feeds using Coalesce, and the load time has been reduced from over 60 minutes to under 8 minutes.”
Ordering up optimization
Challenges
Data coming in from many different sources, in many formats
No easy way for developers to model tables using legacy solution
Existing data stack was inefficient, impacting performance and resulting in additional costs
McConathy’s 10-person team—a mix of AWS developers, Informatica developers, and data modelers—is responsible for bringing all the company’s data into their warehouse and transforming it so it becomes consumable by the business. One key type of data the team analyzes is restaurant drive-thru data, which is not about what customers are ordering, but rather how long the drive-thru ordering process takes—from arrival to ordering to pick up at the order window—based on data gathered by a mix of IoT devices, cameras, and other hardware. In addition, the team deals with data such as transaction logs, sales data, food costs, HR data, online ordering events, and customer loyalty program data from CKE’s proprietary customer data platform.
McConathy and team support many groups across the company, their biggest internal customers being finance and accounting, marketing, operations, and HR. But another big customer group they serve are the many franchisee store owners. Some franchises are owned by large conglomerates and are so big they have their own IT departments, and don’t necessarily need to rely much on support from the parent company; others are small enough that they do lean on CKE for support. The reporting McConathy’s team provides is crucial to the continued success of both franchisee and franchisor. “For us to be successful, we have to help the franchisees be successful,” he says.
McConathy’s manager, Senior Director of Corporate Applications David Collins, charged the team with finding a solution addressing their primary challenges: scalability, reliability, and cost. “There were too many points in the data flow where the data had to flow back to our internal network and then back out again to our data warehouse,” says McConathy. “This impacted performance and also exposed it to system outages when our internal servers were undergoing maintenance. Trying to ‘push down’ the code into the data warehouse layer required us to either write complicated stored procedures or pay Informatica additional fees based on data volume.”
Additionally, the legacy ETL platform McConathy’s team was using to get data into the system and orchestrate movement between dimensions and fact tables didn’t really offer a way to model the data as a part of the development landscape. He was seeking a way to optimize the performance of their integrations, and to tread the line between being able to develop quickly versus efficiently.
“We really wanted to be efficient, but writing a lot of stored procedures wasn’t fast enough and resulted in a lot of the logic being hidden and not easily accessible to non-Informatica developers,” he explains. “Scheduling it was kind of tricky—you didn’t know what you had out there a lot of times. So there was always a choice between getting something out there quickly versus something that was very efficient.”
A new way of working
Solution
Migrated data into Snowflake using AWS service for ingestion
Brought on Coalesce for data transformation
Adopted new tooling to support pipeline development and data modeling best practices
Eric and team are currently migrating off of Informatica completely, an enormous project he predicts will last most of the next year. They decided to use AWS-based services to ingest the data into Snowflake, but still needed a good data transformation solution. “Adopting Coalesce was a result of our CTO coming to us and asking, ‘How can we be faster? How can we do more?’”
McConathy was impressed by the ease with which his team was able to get onboarded with Coalesce. If there were initial challenges, they came from thinking through new, more efficient ways the team could operate with the new systems in place. Says McConathy, “I think the biggest part was really understanding the relationship between workspaces and the development branches, and how we wanted the team to work together in Coalesce.”
For example, they set up Okta to control access into their dev environment, and invested some time learning how to use macros in Coalesce so that they could implement standard auditing of their transformation jobs. “I like where we are now,” he says. “I think we’re more mature in the way we’re doing things—there was definitely some growth for us.”
Fast food, faster teams
Results
Data transformation jobs run seamlessly without any issues
Team able to transform JSON and XML data easily without writing complicated code
Load time for point of sale transaction log feed reduced from 60+ minutes to under 8 minutes
Today, the team is able to work much faster, and much of that stems from the visual aspect of Coalesce’s graphical user interface (GUI). “We chose Coalesce largely because we felt that being able to design our processes visually would speed our development time,” says McConathy. “Having to mostly hand-code our transformations and only get a visual view of the process later seemed counterintuitive to us. I like that we can transform JSON data very simply, and read XML easily without writing a lot of complicated code. You eliminate a lot of mistakes when it’s all pre-built for you.”
The team isn’t the only thing moving faster these days thanks to Coalesce. “We have rewritten one of the point of sale transaction log feeds using Coalesce, and the load time has been reduced from over 60 minutes to under 8 minutes,” says McConathy.
With Coalesce, his team is now better able to serve their internal customers with faster, more accurate reporting. “We’ve done a lot of work for the finance team so far, getting their profit and loss and some other things in there,” he says. “We’re working on doing real-time transaction streaming into Snowflake. It’s really helping us a lot on our sales interfaces that we’re rewriting.”
McConathy says that a big reason his team made the switch to AWS and Coalesce was to reduce operational costs by making their entire data stack cloud-based. “I feel like between Coalesce and AWS, I’m going to save us a ton of money,” he says.
As for future plans, McConathy hopes to use Coalesce to build more customer data-type interfaces, better connecting CKE’s customer loyalty information and credit card data. “We’ll probably be doing more on customer sentiment data and those types of trends. We’ll be building a lot more data marts for our business intelligence team. We’ve got folks on that side who are very interested in leveraging AI, so we’re looking at the data structures that will be required for that.”
The parent company that owns CKE is also bullish on leveraging AI more and more in the future. But one challenge to that, McConathy acknowledges, has been that historically they had gaps in how frequently certain franchisees were reporting data and the granularity of that data.
“You can’t meaningfully do AI until you’ve got the level of data granularity that you need,” McConathy says. “At the end of the day, generative AI is just a really cool text processor. It does it really quickly and it’ll give you answers that look like the right answers, but there’s no way for it to know if it’s a good answer; you have to continually train it. And that’s how we’re going to approach the problem, initially at least—take the business metrics out of the reporting semantic layer and try to put it into the database itself, and hopefully at that point it can be better digested by AI.”
“Coalesce has helped speed up our development process by providing a single place to create new dimensions and fact tables as well as implement the data transformations. We have seen excellent performance of our data transformation jobs—the jobs simply run with no issues.”
