HSBC Overcomes Capacity Challenges and Innovation-Blocks With Google BigQuery
4001
Of your peers have already read this article.
3:45 Minutes
The most insightful time you'll spend today!
At HSBC, we serve 39 million customers, in-person and online, from consumers to businesses, in 66 countries. We maintain data centers in 21 countries, with more than 94,000 servers. With an on-premises infrastructure supporting our business, we kept running into capacity challenges, which really became an innovation blocker and ultimately a business constraint.
Our teams wanted to do more with data to create better products and services, but the technology tools we had weren’t letting us grow and explore. And that data was growing continually. Just one of our data warehouses had grown 300% from 2014 to 2018.
We had a huge amount of data, but what’s the point of having all that data if we couldn’t get insights and business value from it? We wanted to serve our customers flexibly, in the ways that work best for them.
We knew moving to cloud would let us store and process more data, but as a global bank, we were moving complex systems that needed to also be secure.
It was a team effort to create the project scope and strategy up front, and it paid off in the end. Our cloud migration now enables us to use an agile, DevOps mindset, so we can fail fast and deliver smaller workloads, with automation built in along the way.
This migration also helped us eliminate technical debt and build a data platform that lets us focus on innovation, not managing infrastructure. Along the way, we invented new technology and built processes that we can use as we continue migrating.
Planning for a cloud move
We chose cloud migration because we knew we needed cloud capabilities for our business to really reach its digital potential. We picked Google Cloud, specifically BigQuery, because it’s super fast over small and large datasets, and because we could use both a SQL interface and Connected Sheets to interact with it.
We had to move our data and its schema into the cloud—without having to manually manage every detail and miss the timelines we had set. Our data warehouse is huge, complex, and mission-critical, and didn’t easily lend itself to fit into existing reference architectures. We needed to plan ahead and automate to make sure the migration was efficient, and to ensure we could simplify data and processes along the way.
The first legacy data warehouse we migrated had been built over a period of 15 years, with 30 years worth of data comprising millions of transactions and 180 TB of data.
It ran 6,500 extract, transform, load (ETL) jobs and more than 2,500 reports, getting data from about 100 sources. Cloud migration choices usually involves either re-engineering or lift-and-shift, but we decided on a different strategy for ours: move and improve.
This allowed us to take full advantage of BigQuery’s capabilities, including its capacity and elasticity, to help solve our essential problem of capacity constraints.
Taking the first steps to cloud
We started creating our cloud strategy through a mapping exercise, which also helped start the change management process among internal teams. We chose architecture decision records as our migration approach, basing those on technical user journeys, which we mapped out using an agile board. User journeys included things like “change data capture,” “product event handling,” or “slowly changing dimensions.”
These are typical data warehouse topics that have to be addressed when going through a migration, and we had others more specific to the financial services industry, too.
For example, we needed to make sure the data warehouse would have a consistent, golden source of data at a specific point in time. We considered business impacts as well, so we prioritized initially moving archival and historical data to immediately take load off of the old system.
We also worked to establish metrics early on and introduce new concepts, like managing queries and quotas rather than managing hardware, so that data warehouse users would be prepared for the shift to cloud.
To simplify as we went, we examined what we currently had stored in our data warehouse to see what was used or unused. We worked with stakeholders to assess reports, and identified about 600-plus reports that weren’t being used that we could deprecate. We also examined how we could simplify our ETL jobs to remove the technical debt added by previous migrations, giving our production support teams a bit more sleep at night.
We used a three-step migration strategy for our data: first, migrating schema to BigQuery; second, migrating the reporting load to BigQuery, adding metadata tagging and performing the reconciliation process; and third, moving historical data by converting all the SQL script into data into BigQuery-compliant scripts.
Creating new tools for migration automation
In keeping with our automation mantra, we invented multiple accelerators to speed up migration. We developed these to meet the timelines we’d set, and to eliminate human error.
The schema parser and data reconciliation tool helped us migrate our data layer onto BigQuery. SQL parser helped migrate the data access layer onto Google Cloud Platform (GCP) without having to individually migrate 3,500 SQL instances that don’t have data lineage or documentation. This helped us to prioritize workloads.
And the data lineage tool identified components across layers to find dependencies. This was essential for finding and eliminating integration issues during the planning stage, and for identifying application owners during the migration. Finally, the data reconciliation tool reconciles any discrepancies between the data source and the cloud data target.
Building a cloud future
We used this first migration in our UK data center as a template, so we now have a tailored process and custom tools that we’re confident using going forward.
Our careful approach has paid off for our teams and our customers. We’re enjoying better development and testing procedures. We’ve created an onboarding path for applications, we have a single source of truth in our data warehouse, and we use authorized views for secure data access. The flexibility and scalable capacity of BigQuery means that users can explore data without constraints and our customers get the information they need, faster.
2991
Of your peers have already watched this video.
34:30 Minutes
The most insightful time you'll spend today!
Reinventing geospatial analytics as only Google can do: Intelligent platform
Geospatial analytics is undergoing a fundamental change with the application of cloud computing products. Every day, data analysts need to understand the geospatial impact on their data and GIS professionals are finding themselves constrained by legacy technology that can’t keep pace with increasing data volume.
Google Cloud’s geospatial analytics platform brings the full suite of Google’s Geo offerings to both sets of users to unblock workloads and free them from legacy capacity constraints.
Listen to Joe Bettridge, Consulting Lead, TELUS Insights, TELUS and Chad Jennings, Product Manager and GIS Lead, Google Cloud, and learn about Google Cloud’s uniquely powerful, full–platform solution for geospatial analytics and how organizations are using it to reduce query times from hours, days, or months to minutes. These workloads can span both structured and imagery data.
In this session, they will cover:
- An overview of BigQuery GIS
- Some use cases
- Examples of algorithms TELUS is using
7677
Of your peers have already downloaded this article
4:30 Minutes
The most insightful time you'll spend today!
AI has the power to revolutionize every industry—from retail to agriculture, and education to healthcare. Yet many businesses still haven’t begun to adopt AI.
There are a number of factors, including the need for specialized talent and hardware, the right types and quantities of data for training and refining machine learning models, and of course, the broader complexities of introducing a new way of working inside an organization.
But above all, AI can be challenging to apply to very specific business needs.
Google wants to change that, which is why it’s introducing a number of AI solutions aimed at making it easier for businesses to use AI to address many of these specific use cases.
Google’s AI solutions fall into two categories. The first is a set of pre-packaged AI solutions that can be easily integrated into existing workflows. To make these solutions as easy to implement as possible, Google works with popular, trusted enterprise partners who have deep-domain expertise in these workflows.
The majority of Google’s pre-packaged solutions will be delivered through these partners, although it will be offering some
The second category of AI solutions is comprised of reference architectures that businesses can use to create highly-custom AI tools. These require more development work than Google’s pre-packaged
Additionally, businesses can build on these architectures and deploy them to market more quickly, helping your business take advantage of AI, faster.
Learn more about all Google’s AI solutions. Download the whitepaper now!
This Chart, from Home Depot, Dramatically Demonstrates the Power of a Cloud Data Warehouse
7584
Of your peers have already read this article.
2:30 Minutes
The most insightful time you'll spend today!
The Home Depot (THD) is the world’s largest home-improvement chain, growing to more than 2,200 stores and 700,000 products in four decades. Much of that success was driven through the analysis of data. This included developing sales forecasts, replenishing inventory through the supply chain network, and providing timely performance scorecards.
However, to compete in today’s business world, THD has taken this data-driven approach to an entirely new level of success on Google Cloud, providing capabilities not practical on legacy technologies.
The pressures of contemporary growth that drove much of the work are familiar to many businesses. In addition to everything it was doing, THD needed to better integrate the complexities in its related businesses, like tool rental and home services. It needed to better empower teams, including a fast-growing data analysis staff and store associates with mobile computing devices. It wanted to better use online commerce and artificial intelligence to meet customer needs, while maintaining better security.
Even before addressing these new challenges, THD’s existing on-premises data warehouse was under stress as more data was required for analytics and data analysts were utilizing the data with increasingly complex use cases. This drove rapid growth of the data warehouse, but also created constant challenges for the team in managing priorities, performance, and cost.
In order to add capacity to the environment, it was a major planning, architecture, and testing effort. In one case, adding on-premises capacity took six months of planning and a three-day service outage. Within a year, capacity was again scarce, impacting performance and ability to execute all the reporting and analytics workloads required. The capacity refresh cycles were shrinking, and the expecations for data were growing. There had to be a better way.
Still, THD did not take its move to the cloud lightly. A large-scale enterprise data warehouse migration involves tremendous effort among people, process, and technology. After careful consideration, THD chose Google Cloud’s BigQuery for its cloud enterprise data warehouse.
BigQuery, a scalable serverless data warehouse, was better on cost, infrastructure agility, and analytics capability, driving better insights with improved performance. There are no service interruptions when capacity is added, and that capacity can be added within a week (and soon same day). It doesn’t require complex system administration, and its standard SQL support means people can easily ramp up quickly. Valuable BigQuery products like Identity and Access Management meant THD could create many separate Google Cloud projects, while ensuring that different teams weren’t interfering with each other or accessing protected data.
THD also utilizes BigQuery’s flat-rate monthly pricing model that allows teams to budget their capacity based on need and provides billing predictability. The capacity not being used by a given project is available for enterprise use. This ensures no surprises when the monthly bill arrives and provides all analytical users access to significant computing power.
While THD’s legacy data warehouse contained 450 terabytes of data, the BigQuery enterprise data warehouse has over 15 petabytes. That means better decision-making by utilizing new datasets like website clickstream data and by analyzing additional years of data.
As for performance, look at this chart:
With the cloud EDW migration complete, and the legacy on-premises data warehouse retired, analysts now execute more complex and demanding workloads that they would not have been able to complete before, such as utilizing Datalab for orchestrating analytics through Python Notebooks, utilizing BigQuery ML for machine learning directly against the BigQuery data (no movement of large datasets), and AutoML to help determine the best model for predictions.
Additionally, engineers at THD have adapted BigQuery to monitor, analyze, and act on application performance data across all its stores and warehouses in real time, something that was not practical in the on-premises system.
With over 600 projects that THD now has on Google Cloud, the BigQuery story is just one of the many ways that Google Cloud is working with THD to deliver meaningful business results, every day.
How Domino’s Increased Monthly Revenue By 6% with Google Marketing Platform
DOWNLOAD CASE STUDY5757
Of your peers have already downloaded this article
1:30 Minutes
The most insightful time you'll spend today!
Pizza purveyor Domino’s is dominating delivery sales around the world. Today, Domino’s is the most popular pizza delivery chain operating in the U.K., the Republic of Ireland, Germany, and Switzerland — and sales just keep growing.
In these regions in 2014, Domino’s sold 76 million pizzas and generated £766.6 million (1.02 billion USD) in revenue — a 14.6% increase from the previous year.
In the U.K. and Ireland, online sales are increasing 30% year over year and currently account for almost 70% of all sales. Notably, 44% of those online sales are now made via mobile devices.
Multi-Device Purchasing Means Fresh Opportunities
Domino’s is a consistent digital innovator. Much of the company’s success stems from early investments in ecommerce and mobile commerce platforms that help people easily purchase pizzas from different devices.
Domino’s sold its first pizza online in 1999. It then launched an iPhone app in 2010, quickly followed by apps for Android and iPad in 2011, and a Windows app in 2012. By late 2014, Domino’s customers could even order pizzas from Xboxes.
The Domino’s marketing team had assembled a variety of tools to measure marketing performance, keeping pace with the company’s rapid innovations. Unfortunately, measuring siloed analytics and channel-focused tools restricted the team’s ability to fully understand all of the different paths to purchase.
Find out how they worked around this challenge with Google Marketing Platform. Download the case study!
Making Mothers’ Day Special: How Google Cloud Migration for 1-800-FLOWERS.COM, Inc Impacts CX
6816
Of your peers have already read this article.
2:30 Minutes
The most insightful time you'll spend today!
Editor’s note: In honor of Mother’s Day, we look at how 1-800-FLOWERS.COM, Inc. migrated to Google Cloud as part of its digital transformation to quickly deploy seamless and convenient customer experiences across multiple brands on Mother’s Day and every day.
As a leading provider of gifts designed to help customers express, connect, and celebrate, 1-800-FLOWERS.COM, Inc. has embraced cloud technologies to grow and transform its business through constant innovation. As part of our digital transformation, we recently completed the migration of our ecommerce platform and other services to Google Cloud. We’ve transitioned from a monolithic to a microservices platform, moved many workloads from our on-premises data centers to our Google Cloud environment, and scaled both horizontally and vertically.
Since our migration, we’ve developed efficient processes to launch new brands, improved the customer experience across all brands, and seen significantly increased site traffic.
Nurturing a more delightful customer journey
Customer delight is at the core of everything we do. Whether it be with a flower bouquet, a sweet treat, or a personalized keepsake, our mission is to deliver smiles. With the rise of the COVID-19 pandemic, we’ve all been challenged to find unique and safe ways to continue honoring the special connections in our lives and celebrating occasions with loved ones. Our customers have adapted by doing things such as sending gifts to isolated loved ones, sharing the same meal together virtually, or using video to engage with others through group activities like flower arranging and building charcuterie boards.
The customer experience is a top priority for us, and we constantly look for innovative ways to enhance the customer journey across our ecommerce platform of more than a dozen brands. As a result, we’ve continued to see a rise in demand as customers enjoy the ease and convenience of our site and discover our full family of brands.
Migrating to a cloud-first mindset
As we’ve continued to innovate and iterate on the customer experience, we knew we wanted to evolve our platform. We wanted to shift to a microservices platform, which would allow our team the opportunity to release updates to our site more often and set up the right continuous integration/continuous deployment (CI/CD) practices.
Working with Google Cloud, we were able to move our ecommerce platform to the cloud and standardize our site and brand deployment by building one release that could then be repeated across all of our brands. We built everything in a modular fashion, including microservices and code libraries, so that sites could be easily constructed and replicated for each brand. And because of this, we were able to launch Shari’s Berries extremely quickly after we acquired the brand in 2019.
Moving our platform to a completely homegrown solution of microservices was a daunting task. But our team handled it beautifully through load-testing, stress-testing, and building new monitoring tools. And with Google Cloud supporting us all along the way, managing the migration process was simple and easy from start to finish.
Arranging a better bouquet of services
Currently, we’ve migrated every customer-facing touchpoint for all of our brands to Google Cloud—whether it’s on the web or mobile, our AI bots, or our chat interfaces.
- We run on Google Kubernetes Engine and Istio.
- We have nearly 200 microservices built to help power our entire ecommerce stack across several cloud services running on Google Cloud.
- We’re utilizing BigQuery for our offline intelligence.
Results are coming up roses
Our new stack on Google Cloud has benefits for both us and our customers. We moved from a session-based to a token-based system, which provides enhanced security as well as a consistent, convenient experience across all our brands. Using service workers and a single-page app, we are able to download all the relevant site content to the browser in under two seconds to create an instant-click experience for each and every customer. We also use Google Analytics to measure our user interactions and provide personalized results to each customer. Our hope is that with this new system, we can learn from customer behavior to offer gift givers a more personalized shopping experience during each visit.
The benefits of our new tech stack have not only helped us enhance the solutions we offer to customers today, they’ve also enabled us to offer new ones at lightning speed. With our legacy system, we used to release new code once a week or once a month. Now, even during our peak periods, we’re able to release 10 to 15 times a day and can deploy and pivot quickly to create new microservices and microsites on the fly—often without having to touch any code.
Efficiencies abound
The benefits of moving our platform to Google Cloud have extended to our internal teams as well. Before the migration, we had only two environments for developing and testing, which made it time-consuming to test updates before they went into production. Now with Google Cloud, we have several different journey teams—which are made up of developers, product owners, and technical owners—all working in several different environments, solving problems, and creating new solutions together.
Everyone is now empowered to be self-sufficient, developing and releasing microservices on their own when they’re ready. This has given our developers more time to take part in continued development and learning opportunities. For example, we offer lunch-and-learn sessions as well as other resources for everyone to take advantage of so they can continue to learn and refine their skills.
Planting the seeds for future growth
As we look to the future and think about how we help our customers express, connect, and celebrate, we’ll continue to collaborate across teams to deliver solutions that spread smiles. Specifically, we’re exploring additional use of AI to help us better serve our customers across all our brands.
We’ve enjoyed the ongoing support we’ve received from the Google Cloud team as they help us build new solutions and design a road map for the future. Their support has helped the 1-800-FLOWERS.COM, Inc. team to realize the power of the cloud and bring the very best experience to our customers.
Learn more about 1-800-FLOWERS.COM, Inc., or check out our recent blog about cloud migration for the real world.
More Relevant Stories for Your Company
Getting Started with BigQuery: 10 Quick Steps
BigQuery is Google's fully managed, petabyte scale, low cost analytics data warehouse. BigQuery is NoOps—there is no infrastructure to manage and you don't need a database administrator—so you can focus on analyzing data to find meaningful insights, use familiar SQL, and take advantage of our pay-as-you-go model. That's great, right? So
Google Cloud Named Leader of AI Infrastructure: Forrester Research
Forrester Research has named Google Cloud a Leader in The Forrester Wave™: AI Infrastructure, Q4 2021 report authored by Mike Gualtieri and Tracy Woo. In the report, Forrester evaluated dimensions of AI architecture, training, inference and management against a set of pre-defined criteria. Forrester’s analysis and recognition gives customers the confidence they
Upgrade Your Contact Center with Knowlarity’s AI-powered Speech Analytics for Higher CX
Did you know, everyday about 56 million hours worth of phone conversations, equalling to 420 billion spoken words are handled by contact centers? Knowlarity, a renowned cloud business communication service provider with nearly 6,000 customers and over a million virtual users, leverages AI-powered speech analytics that offer insights to gauge
Geospatial Data for Business Apps Drive Sustainable and Accurate Decision-making
Organizations that collect geospatial data can use that information to understand their operations, help make better business decisions, and power innovation. Traditionally, organizations have required deep GIS expertise and tooling in order to deliver geospatial insights. In this post, we outline some ways that geospatial data can be used in
