It’s not uncommon for customers to spend several years and tens of millions of dollars trying to migrate from one data warehouse to another. It is also not uncommon for these exercises to fail. But for the Co-op group in the UK, the migration from a Teradata warehouse to Azure SQL Data Warehouse not only happened on time and on budget, but also in the midst of the worst pandemic of the century.
The Co-Op Group is a cooperative organization in the UK which was originally founded in 1844. Today the cooperative (as it is known in the UK) spans over 7,000 locations across the country, offering a range of grocery, retail, insurance, legal and funeral services. In 2020, it generated revenues of £ 11.5 billion (or $ 15.4 at current exchange rates) and had 4.3 million active members.
Like any modern organization, the cooperative relies on a mixture of IT to automate its business processes. For years, she has used a Teradata data warehouse to inform decisions in her retail operations. The system contained 25 years of data detailing who, what, how and when the activities of Co-op customers.
“It was pretty much the lifeblood of our retail business,” says Charlotte Lock, Co-op’s director of data, digital and loyalty.
However, the on-premises Teradata system was nearing end of life for Co-op, so the organization’s data team began exploring new options during the 2018-2019 period. The cloud was important to Co-op, as it has been to many businesses who are looking for more flexibility and the power to scale their data warehousing operations in a short period of time.
“The decision to migrate was to take advantage of the cloud,” says Lock Datanami. “So really, the processing speed, the cost and the storage of the data that has been kept on the local systems, but also to be able to process millions of lines of data, sometimes billions of lines of data, at a fraction of the cost. time that we would need to do in our on-premise solutions.
Then COVID-19 hit in early 2020, and that dramatically increased the time horizon, Lock says. If the Manchester-based organization envisioned a quiet migration five to six years before COVID-19, that was certainly not part of the plan afterwards.
“We were able to see even before COVID – and of course COVID accelerated it – that the requirement for even more data to be able to perform real-time actions when we tapped, for example, e-commerce,” said Lock said.. “We didn’t really have a significant e-commerce operation before COVID. But in fact the demand meant we had to get something up and running very quickly. “
One of the concerns with the old system was that the cooperative did not have a big picture of its customers, which meant that customer activity in retail could not easily be compared to client activity in the legal or insurance field. This limited the cross-sell and up-sell opportunities that are so important to ecommerce operations, like the one the co-op was looking to create in a short period of time. It also limited the cooperative’s ability to operate omnichannel, which it was heading towards.
“Where an organization that was primarily face-to-face… more and more, we see our customers wanting to transact through omnichannel,” Lock says. “The move to the cloud coincides with our business ambition to become more digital and more personal. “
The combination of these factors – the cloud’s greater processing flexibility and COVID’s omnichannel demands – led Co-op to decide to retire its Teradata system and replace it with Microsoft Azure Synapse Analytics in early 2020, said Lock.
However, given the importance of the Teradata system to the co-op’s retail operations, he couldn’t just unplug. He also couldn’t languish with a forever migration schedule that might or might not fully unfold. It was then that the company considered calling on another player to help with the migration: Datometry.
Datometry is a San Francisco company that is developing a database virtualization product that can make one database look like another. Its emulator, called Hyper-Q, was developed to help companies migrate from two of the most popular analytical databases on the market: Teradata and Oracle.
Datometry CEO Mike Waas described how Hyper-Q works in a July interview with Datanami:
“We intercept application communication, decompress queries, remove SQL, then do what looks pretty much like what the top half of any database does, which is create a template Complete algebraic for the incoming query, optimize it, and then synthesize what the optimized SQL means for that destination, ”Waas said.
It was exactly what the doctor ordered for the co-op. Since they didn’t have to rewrite their Teradata code, the organization could essentially just take the 10,000 tables and 800-900 automated processes that they developed for Teradata, and move them right into Azure Synapse, and leave Hyper -Q handle any minor incompatibilities or incongruities between the two systems.
“This virtualizes it to a pretty successful degree, which means we were able to maintain our Teradata system,” Lock explains. The Datometry software allowed Co-op to “build this emulation layer in parallel, then be able to shut down Teradata and effectively have the same code running.”
The actual migration from Teradata to Azure Synapse was done in batches, starting with the largest and most complex batch runs. After extensive User Acceptance Testing (UAT) to ensure that the new system gives the same response as the old system and meets Service Level Agreements (SLAs), it would move on to another batch, and so on. away, until everything has been moved.
“We’ve invested a lot in quality assurance because what you don’t want to do is kind of turn off the backup plan until you’re really, really confident,” she says. “So we went over and over again. “
With the help of the Microsoft Azure team and Datometry, the cooperative was able to complete the migration to Azure Synapse Analytics in just a few months. It now runs its data warehouse entirely in the Azure cloud. The Teradata system has not yet been decommissioned, but it is no longer in the loop, Lock says.
While it wasn’t necessary, the group took the opportunity to repoin about 15% of the Teradata code to run more efficiently in the Azure Synapse environment, Lock says. “Not because it didn’t necessarily work at the mechanic’s,” she says. “But because teams spend a lot of time on this data, and actually when these processes were written in the first place, it was a different environment. “
The ROI of the migration has been good, according to Lock, who says the group saved millions of pounds by running in Azure rather than Teradata on-premises. Another factor is the ability to speed up data processing operations to ensure that reports (designed in PowerBI or MicroStrategy) are ready for use and on analyst desks at 7 am Monday morning.
“In retail, you need to have data at your fingertips. It’s a very real and responsive environment, ”says Lock. “And that really meant that this lack of downtime [and] the ability to speed things up is a huge benefit to the efficiency of the data team. This did not necessarily mean that our stakeholders saw a huge benefit. This meant that our team could function more efficiently.
The fact that Co-op was able to complete the migration on time and on budget during the COVID-19 lockdown is something Lock says he is very proud of. After the project was completed, some of the team got together which made it even more special.
“There were no great dramas. I didn’t have to ask for more budget. I didn’t have to expand the team to complete the transformation, ”she says. “So it was a very smooth process, which I think is partly a testament to the partnership with Microsoft and Datométrie. But I also think you know how to be realistic about how long the UAT must have taken, without trying to transition and migrate too early. I think that’s the critical thing.
Is it time to virtualize databases?
Microsoft launches AI-based semantic search on Azure
Cloud Data Warehousing: Understanding Your Options