“Coalesce has enabled a collaborative environment in which we can have a much quicker turnaround, more stability, and trust for our pipelines to contribute back to the business. It really has provided the team great automation tools with robust controls and documentation to deliver work that used to take us much longer or we were never able to do before.”
A data stack in need of renovation
Challenges
Previous data stack consisted of many disparate databases and data consolidation tools, which made it difficult to integrate data sources and was often reliant on individuals or locally installed software
Only available data had narrow focus, which didn’t offer much broader context as to how to improve business performance
Data team had queues of weeks or months for activities that needed to get done
Adam Courtier is Head of Data & Insights at Mitre 10 NZ. On a day-to-day basis, his team is tasked with maintaining operational excellence around the company’s data, making sure the right data turns up where it’s meant to and that the reports they produce are reliable. On top of that, the team is always seeking out new data and insights to share with the company’s store networks to help them provide better customer experiences. For example, how can customers and team members more easily find products in each store? How can the customer journey be better optimized based on products that are commonly purchased together?
“We strive to make the data accessible and reliable, but also work with our key stakeholders to make sure they can use that data,” says Courtier. “We have a very clear view that we should be using any data assets we’re consolidating across the organization as a whole. The ideal scenario for us is a multi-use case—create one data asset and use it many times across the business.”
Courtier explains that to live up to the company’s value of being customer obsessed, he and his team endeavor to look at not only how customers are spending in the physical stores and online, but also look for any discernible patterns in customer activity, such as particular times of the year in which they shop or particular types of shopping missions.
“For example, if somebody is building a fence, we try to determine what their next likely project will be,” he says. “Or is there a big trade customer that has stopped visiting our store for some reason? That’s when we might highlight that the account manager give a call to see if there’s anything we could help with, or if there was any kind of issue that was left unresolved. We’re trying to be more proactive, so rather than just reporting the numbers, we’re providing recommendations that our 7,000 store members can act on in a sensible way.”
Mitre 10’s previous data stack included a number of databases and data consolidation tools, and this complexity kept the team from integrating its many data sources. “The biggest challenge was that we weren’t able to integrate new data sets efficiently, so generally those data sets were not added to our data warehouse,” explains Courtier. As a result, the existing data warehouse focused solely on sales data and not much else: “We could slice and dice anything related to a product or customer as long as the dimensions we were looking at were sales or margin or account.” But without a more robust data set, the team was unable to glean more useful insights that could have a positive impact on business performance.
In addition, Courtier’s team was hampered by a huge backlog. “We would have queues of weeks or months for activities to be done,” he says. The team needed to find the right solution to solve all these challenges and help the company renovate its own foundation.
Best of both worlds
Solution
All data in one place with Snowflake
Coalesce adopted as a data transformation solution to help the team deliver quickly, but with flexibility to drill down into the code if necessary
Development of a “one kitchen/multiple dining rooms” model using Snowflake and Coalesce to serve data and insights to whichever team needs it
Courtier says that before adopting Coalesce, his team “had tried almost everything.” His goal was to find a solution that worked as seamlessly as possible with Snowflake, the data platform they had decided to build their data strategy on. “We were looking for something that worked well with a Snowflake-based data approach,” he says. “When assessing a new solution, we always look for a combination of usability and business performance more than just technical performance.”
Courtier and team decided against a proprietary solution because they worried there was too much variability in style and maintainability with handwritten code. Instead, adopting Coalesce better aligned with the team’s principles of looking for something that was both easy to use and would quickly increase the team’s productivity. “A key focus for us was to determine how we could deliver at pace with these tools,” says Courtier. “In one real use case, we can now deliver something in an hour as opposed to five to six weeks. We hit a new record of 80 end-to-end pipelines in one week with two data engineers!”
“We now deliver on the core principles of managing data as code and have a three-tier environment—quite different to our previous ad hoc approach,” he says. “We have source controls all delivered through Coalesce, so we still have change control in releases and versioning.” Courtier believes this gives his team the best of both worlds, and if they choose to cut code for a regular expression or a complex transformation, they can still do that in Coalesce even if it’s not their default position: “The hundreds of pipelines and nearly 5,000 tables/views that we run through Coalesce are all managed in a GUI-based model, but with that flexibility of being able to drill down to code should there be a weird and wonderful scenario that suddenly comes up, or if we need to create new templates for scenarios like machine learning.”
After adopting Snowflake in September 2022, the team set a goal to get the nearly 80 data sources from its existing data warehouse ingested into Snowflake within 30 days. In the previous configuration, data was being pushed directly from one source into Tableau data sets, so no other system could use it. “Now we use a multi-channel approach, so anything that can connect to Snowflake can use the data—whether that’s Excel, Power BI, Tableau, or SAP Analytics Cloud,” says Courtier. “We operate a kind of a ‘one kitchen/multiple dining rooms’ strategy, so all of the data sits within the kitchen and can be served in any way you choose from Snowflake with the same consistent results.”
Seeing the bigger picture
Results
Pipelines that may have taken up to 30 days to build before can now be built and unit tested in one day
Analysts able to see data lineage at a column and table level
Positive mindset shift across the technical teams, with data engineers inspired to do more thoughtful work and consider how their work relates to the larger team’s goals
One of the benefits Coalesce has brought to the team is increased reliability. “We can run a three-tier environment in our data warehouse, fully code managed, whereas in the past that was very limited and also high risk,” says Courtier. “We would often have pipelines fail when they shouldn’t have. So a significant advantage for us has been improved trust around data and data flows.” That sense of trust extends beyond just his team as well, as the improved insight into data lineage that Coalesce provides ensures that everyone in the organization can clearly see where data has moved across the business and merged.
Coalesce has also made Courtier’s team much faster and more efficient. As he says, “We had pipelines that previously may have taken 30 days to build, but can now be built in one day, and within a testing cycle they can all be delivered within a 7-day release window, which is a massive shift from where we were.”
Courtier’s team started out small—in just one day, the team was able to fully complete seven end-to-end pipelines and partially complete five. “The estimates we had from a consulting firm was that each one of these pipelines was going to take around eight weeks. So if you think, that’s a phenomenal shift in our ability to deliver.”
Whereas originally the only available data told a very one-dimensional story about things like average sales, today’s rich data sets and improved insight gives the company the ability to leverage data to really see the bigger picture. “Now we manage thousands of tables across our organization with a huge focus on customer interactions, but also internal and external data,” he explains. “How do we understand how we’re performing in the market, how do we understand how much share of wallet we have of our customers, what are the opportunities from online shopping behaviors? We’re creating much more complex and enriched data assets to figure out how we can improve business performance.”
Courtier also says that adopting Coalesce has led to a positive cultural shift in how Mitre 10’s other technical teams approach their work. “The majority of our teams who’ve never used Coalesce before were very impressed by how easy it was to use,” he says. “End-to-end DevOps has improved massively, but it has also meant that our data engineers have had to do more thoughtful work. They’re not working more hours, but they’re having to think about how their project relates to the overall outcome, where that’s going to be released, and branching code. We’re seeing much more of a blend of developer, analyst, and data engineer than we probably first anticipated, and that’s a good thing—it gives us more rigor, more control.”
He recalls one instance when the company’s CEO and CFO met to analyze store growth, the credit risk, and surrounding market to understand sustainability of growth and future store planning. Rather than asking an analyst or an engineer to analyze the data for them, they were able to serve themselves from the insights his team had developed. “Using a visual dashboard with data that flowed through multiple areas through Coalesce, they were able to confidently see almost immediately each store’s growth, profitability, share of wallet, and credit risk,” says Courtier. “When you’re able to help an executive get these kinds of insights for themselves, that’s a big win.”
