“The ease of use and intuitiveness of Coalesce stood out for us. When you’re trying to figure out what’s going on, it’s much easier to understand things visually as opposed to looking through hundreds of lines of code.”
Many problems on their plate
Challenges
Team slowed down by limitations of legacy data platform
Time wasted manually coding SQL and creating huge numbers of tables and procedures
Legacy integration solution difficult to learn, adding unnecessary friction
While Denny’s servers are delivering plates of delicious food to hungry restaurant customers, employees on the Denny’s Business Intelligence team are serving up data insights to help move the company forward. Chris Elliott is a Cloud Data Architect in Denny’s corporate division, and his team of five is responsible for any and all data that comes in or out of the company, including data from outside vendors such as third-party food delivery services.
The Denny’s BI team fields data requests from a number of other teams across the organization: marketing, finance, field operations, legal, and even the franchises. “Basically anybody and everybody, including our own internal customers here in IT,” says Elliott. “We’re the primary, most comprehensive source of data within the organization.”
One key thing people ask for is help with new integrations, such as when the company engages with a new vendor and needs to send the last several years of data. “We also manage the ingestion of data from our point-of-sale (POS) systems and integrate it into the data warehouse to create a comprehensive picture of Denny’s, and provide data from a single source as opposed to five different sources,” Elliott says.
In addition to POS systems, the team receives data from ServiceNow and Oracle, as well as IoT devices such as smart ovens, which are in nearly all Denny’s restaurant locations, and stream events throughout the day such as temperature changes, what is being cooked, the length of time it cooks, and so on.
The BI team uses SnapLogic to bulk load data into internal and external shares, but does not rely solely on it for ingestion into Snowflake. Instead, they use Confluent Kafka-customized connectors to bring a lot of their streaming data into Snowflake, as well as another customized connector they wrote themselves. The team also uses Power BI for dashboarding, and produces general data reports for the Denny’s franchises.
Initially, Elliott and team were using Azure SQL Database Hyperscale as their primary data platform. But Hyperscale couldn’t process the data quickly enough—if at all—for the needs of the business. They were wasting cycles by manually coding SQL and creating a huge number of tables. This, coupled with data integration challenges that added unnecessary friction to their work, further affirmed that Snowflake would be the best platform for their data needs. Says Elliott, “With Snowflake, the sky’s the limit.”
Choosing from a menu of better options
Solution
Began migrating company data from Hyperscale to Snowflake
Requested a proof of concept (POC) with Coalesce after comparing it to other solutions in the market
Adopted Coalesce as data transformation solution for new data stack
Migrating to Snowflake from Hyperscale promised to solve a lot of the problems Elliot’s team was struggling with. However, they had yet to find a transformation solution that worked seamlessly with Snowflake so the team could easily and quickly deliver data insights to its customers. After getting a high-level look at a number of solutions on the market—including widely used code-first transformation tools—he decided to move forward with a POC with Coalesce.
Right away Elliott appreciated how Coalesce’s visual interface made it easier to onboard new team members who were not as adept at writing SQL as more experience coders. And beyond just developers, he recognized that Coalesce would make it easier for all employees—even those from other teams—to quickly see the big picture.
Elliott says that the ease of use and intuitiveness of the Coalesce platform was something that impressed him right away. He knew users were better able to understand things visually rather than searching through hundreds of lines of code to troubleshoot a problem. “As any developer knows, people don’t always comment code the way they’re supposed to, so even if you and I read the same thing, we might get two different interpretations of what it’s actually doing,” he says. “So the visual aspect of Coalesce was a big draw for us because it’s so much easier to get other people involved, and for them to follow what’s going on.”
Serving up a grand slam of data
Results
3-4 days to migrate 25 dimension tables to Snowflake versus 2 weeks
Greatly reduced tech debt by no longer needing to hand-code ETL transformations
For each team member, 4-5 hours spent weekly writing documentation reduced to mere minutes
With Denny’s POS system, Elliott had a number of dimension tables—what the team calls reference tables, with data on different food delivery methods, including dine in, carry out, DoorDash, etc.—that had to be moved to Snowflake from Hyperscale around the time of the initial Coalesce POC. While it had taken two weeks to move 25 tables to Snowflake using the team’s legacy processes, it took only three to four days with Coalesce. “It cut the time down 60% compared to what I was previously doing freehand by using Snowflake SQL and SnapLogic,” says Elliott.
When Elliott realized he had made an error in a particular column, he was amazed how quick and easy it was to correct the problem using Coalesce. “Before it would have meant going back to change the data or the data type, the column name, redoing a lot of things, and then reloading it,” he explains. “With Coalesce it was literally going to the subgraph, changing the name, recreating the table, and then loading it—that was it. It cut down the time needed to fix it to a third of what it used to take.”
Today, instead of manually writing the SQL code to handle all of their ETL processing, the team is transitioning as much of that logic as possible into Coalesce to cut down its code debt. Elliott appreciates the efficiency Coalesce provides since the SQL code the platform creates is perfectly optimized for Snowflake: “Now we have it in a platform that’s more easily understandable for everybody, with documentation, lineage, and everything that goes with it, which is something we didn’t have before.”
Before Coalesce, Elliott estimates each team member was spending up to 4 or 5 hours a week doing documentation by hand, using tools such as Microsoft Word and Visio. But Coalesce’s ability to automate much of the documentation process has freed them from such time-consuming toil, turning a task that took several hours into something that could be completed in mere minutes.
“I’ve gotten to the point where I’ll ask myself, ‘Can I do it in Coalesce first?’ Not only because of the efficiency, but also because of the documentation,” says Elliott. “As a large company, we’re regularly audited by third-party auditors like KPMG. We can literally just provision them access to the documentation to provide a high-level overview of everything they’re looking for—this data came from here, this transformation changed it there, whatever they need to review. That’s documentation that we don’t have to spend hours writing, so the turnaround time goes way down for us.”
Coalesce’s JSON parsing is another big time-saver for the team. Their cloud-based Xenial POS system regularly hits rest APIs, and they were using their previous integration tool to drop that raw data into Snowflake. This resulted in the challenge of parsing out the JSON by opening up the JSON file and investigating its structure. Using Coalesce simplified this process considerably. “Coalesce has increased dramatically what I can do,” says Elliott. “The JSON parsing is huge. It’s a whole lot faster to be able to explode out all the different objects that are available and modify them right there.”
Elliott also likes how easy Coalesce makes it to collaborate on projects with other teams or third-party agencies thanks to Git version control. He has used Coalesce Projects to provision access to an agency that is helping with the Snowflake migration, and has appreciated the transparency and ease of use. “Throughout the process I could just hop into their projects and see what’s going on,” he says.
Going forward, Elliott and team have big plans to re-architect their entire data model to be better suited for growing technologies such as artificial intelligence and machine learning. As Elliott explains, “The goal is to re-architect our data model into a methodology where we can more easily add sources, have the change tracking that we want, and then build out that business layer and the other data marts for various domains for a true comprehensive data model that covers everything. Coalesce will help us tremendously with this effort.”
