HSBC Overcomes Capacity Challenges and Innovation-Blocks With Google BigQuery
3998
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.
Discover Latest Resources on Google Cloud’s Datasets Solution
6314
Of your peers have already read this article.
3:00 Minutes
The most insightful time you'll spend today!
Editor’s note: With Google Cloud’s datasets solution, you can access an ever-expanding resource of the newest datasets to support and empower your analyses and ML models, as well as frequently updated best practices on how to get the most out of any of our datasets. We will be regularly updating this blog with new datasets and announcements, so be sure to bookmark this link and check back often.
August 2021
New dataset: Google Cloud Release Notes
- Looking for a new way to access Google Cloud Release Notes besides the docs, the XML feed, and Cloud Console? Check out the Google Cloud Release Notes dataset. With up-to-date release notes for all generally available Google Cloud products, this dataset allows you to use BigQuery to programmatically analyze release notes across all products, exploring security bulletin updates, fixes, changes, and the latest feature releases.
- Access the dataset
July 2021
Best practice: Use Google Trends data for common business needs
- The Google Trends dataset represents the first time we’re adding Google-owned Search data into Datasets for Google Cloud. The Trends data allows users to measure interest in a particular topic or search term across Google Search, from around the United States, down to the city-level. You can learn more about the dataset here, and check out the Looker dashboard here! These tables are super valuable in their own right, but when you blend them with other actionable data you can unlock whole new areas of opportunity for your team. To learn how to make informed decisions with Google Trends data, keep reading.
- Access the dataset
New dataset: COVID-19 Vaccination Search Insights
- With COVID-19 vaccinations being a topic of interest around the United States, this dataset shows aggregated, anonymized trends in searches related to COVID-19 vaccination and is intended to help public health officials design, target, and evaluate public education campaigns. Check out this interactive dashboard to explore searches for COVID-19 vaccination topics by region.
- Access the dataset
June 2021
New dataset: Google Diversity Annual Report 2021
- Since 2014, Google has disclosed data on the diversity of its workforce in an effort to bring candid transparency to the challenges technology companies like Google face in recruitment and retention of underrepresented communities. In an effort to make this data more accessible and useful, we’ve loaded it into BigQuery for the first time ever. To view Google’s Diversity Annual Report and learn more, check it out.
- Access the dataset
New dataset: Google Trends Top 25 Search terms
- The most popular and surging Google Search terms are now available in BigQuery as a public dataset. View the Top 25 and Top 25 rising queries from Google Trends from the past 30-days, including 5 years of historical data across the 210 Designated Market Areas (DMAs) in the US. Keep reading.
- Access the dataset
New dataset: COVID-19 Vaccination Access
- With metrics quantifying travel times to COVID-19 vaccination sites, this dataset is intended to help Public Health officials, researchers, and Healthcare Providers to identify areas with insufficient access, deploy interventions, and research these issues. Check out how this data is being used in a number of new tools.
- Access the dataset
Best practice: Leveraging BigQuery Public Boundaries datasets for geospatial analytics
- Geospatial data is a critical component for a comprehensive analytics strategy. Whether you are trying to visualize data using geospatial parameters or do deeper analysis or modeling on customer distribution or proximity, most organizations have some type of geospatial data they would like to use – whether it be customer zipcodes, store locations, or shipping addresses. However, converting geographic data into the correct format for analysis and aggregation at different levels can be difficult. In this post, we’ll walk through some examples of how you can leverage the Google Cloud platform alongside Google Cloud Public Datasets to perform robust analytics on geographic data. Keep reading.
- Access the dataset
Get the metadata and try BigQuery sandbox
When you’ve learned about many of our datasets and pre-built solutions from across Google, you may be ready to start querying them. Check out the full dataset directory and read all the metadata at g.co/cloud/marketplace-datasets, then dig into the data with our free-to-use BigQuery sandbox account, or $300 in credits with our Google Cloud free trial.
3008
Of your peers have already watched this video.
25:00 Minutes
The most insightful time you'll spend today!
Data Catalog for Data Discovery and Metadata Management
Data discovery and metadata management is a common pain point for most enterprises. Data Catalog is a managed service that addresses this pain point with a scalable and performant solution.
“Data catalog, simply put, enables users to discover, understand, and manage all their data assets,” says Shekhar Bapat, Product Manager, Google Cloud.
Data Catalog also offers integration with DLP for auto detection and tagging of sensitive data and facilitates better data governance. Data Catalog provides support for non-Google Cloud data assets and enables customers to discover, understand, and manage ALL their data.
The session is co-presented with Shruti Thaker, Head of Alt Vendor Data and Alpha Capture, BlackRock, which has invested in Data Catalog. Thaker will share how BlackRock is using Data Catalog to create an effective metadata solution for BlackRock data assets.
How Domino’s Increased Monthly Revenue By 6% with Google Marketing Platform
DOWNLOAD CASE STUDY5756
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!
11 Google Cloud Analytics Tools, Each Explained Simply in Under 2 Minutes
3556
Of your peers have already read this article.
22:00 Minutes
The most insightful time you'll spend today!
Need a quick overview of Google Cloud analytics technologies? Quickly learn these 11 Google Cloud products—each explained in under two minutes.
BigQuery in a minute
Storing and querying massive datasets can be time consuming and expensive without the right infrastructure. This video gives you an overview of BigQuery, Google’s fully-managed data warehouse. Watch to learn how to ingest, store, analyze, and visualize big data with ease.
Firestore in a minute
Cloud Firestore is a NoSQL document database that lets you easily store, sync, and query data for your mobile and web apps, at global scale. In this video, learn how use Firestore and discover features that simplify app development without compromising security.
Cloud Spanner in a minute
Cloud Spanner is a fully managed relational database with unlimited scale, strong consistency, and up to 99.999% availability. In this video, you’ll learn how Cloud Spanner can help you create time-sensitive, mission critical applications at scale.
Cloud SQL in a minute
Cloud SQL is a fully-managed database service that helps you set up, maintain, manage, and administer your relational databases on Google Cloud. In this video you’ll learn how Cloud SQL can help you with time-consuming tasks such as patches updates, replicas, and backups so you can focus on designing your application.
Memorystore in a minute
Memorystore is a fully managed and highly available in-memory service for Google Cloud applications. This tool can automate complex tasks, while providing top-notch security by integrating IAM protocols without increasing latency. Watch to learn what Memorystore is and what it can do to help in your developer projects.
Bigtable in a minute
Cloud Bigtable is a fully managed, scalable NoSQL database service for large analytical and operational workloads. In this video, you’ll learn what Bigtable is and how this key-value store supports high read and write throughput, while maintaining low latency.
BigQuery ML in a minute
BigQuery ML lets you create and execute machine learning models in BigQuery by using standard SQL queries. In this video, learn how you can use BigQuery ML for your machine learning projects.
Dataflow in a minute
Dataflow is a fully managed streaming analytics service that minimizes latency, processing time, and cost through autoscaling and batch processing. In this video, learn how it can be used to deploy batch and streaming data processing pipelines.
Cloud Pub/Sub in a minute
Cloud Pub/Sub is an asynchronous messaging service that decouples services that produce events from services that process events. In this video, you’ll learn how you can use it for message storage, real-time message delivery, and much more, while still providing consistent performance at scale and high availability.
Dataproc in a minute
Dataproc is a managed service that lets you take advantage of open source data tools like Apache Spark, Flink and Presto for batch processing, SQL, streaming, and machine learning. In this video, you’ll learn what Dataproc is and how you can use it to simplify data and analytics processing.
Data Fusion in a minute
Cloud Data Fusion is a fully managed, cloud-native, enterprise data integration service for quickly building and managing data pipelines. In this video, you’ll learn how Cloud Data Fusion can help you build smarter data marts, data lakes, and data warehouses.
Cart.com to Transform e-Commerce for Brands Globally
8751
Of your peers have already read this article.
1:30 Minutes
The most insightful time you'll spend today!
The ecommerce playing field has been hard to navigate for most retailers, and Cart.com is on a mission to change that. Traditionally, retailers needing to run their online store, order fulfillment, customer service, marketing, and other essential activities have had to cobble together systems to get the capabilities they need – much less having access to analytics across these functions. The result is costly, siloed ecommerce operations that are difficult to manage and scale.
It’s clearly not a formula for success, yet that’s the reality facing most retailers. Cart.com, in contrast, has set out to democratize ecommerce by giving brands of all sizes the full capabilities they need to take on the world’s largest online retailers. Our end-to-end environment empowers retailers to keep more of their revenue, set up proven strategies for managing all aspects of their business, and act on valuable insights from customer data every step of the way.
Together with our talented team, we’re building a unified ecommerce platform that already provides value to many leading or up and coming brands including Whataburger, GUESS, Dr. Scholl’s, Rowing Blazers, and Howler Bros.
We’re excited about the opportunity ahead as we reimagine traditional approaches to online sales, fulfillment, marketing, accessing growth capital, providing a unified view of all ecommerce and marketing analytics, and other activities. Expectations for Cart.com are high, and we are building a company that can scale to $100B in revenue and beyond. Supported by the Startup Program by Google Cloud and Google Cloud solutions, we’re establishing a technology platform to transform all aspects of ecommerce for brands worldwide.
Partner in disruption
At Cart.com, we’re currently targeting an underserved market. Our ideal customer is beyond demonstrating product-market-fit and is now at an inflection point seeking a growth opportunity. Typically, those companies are generating between $1M and $100M in annual revenue. We’ve seen an enthusiastic response from brands and retailers as well as investors, with backing from investors in just over a year totaling $143 million in three funding rounds.
Our strategy is to build an integrated ecommerce model that combines best-of-breed solutions, many of which we gain through acquisitions and then build upon to provide a streamlined and fully integrated experience for our brands. We’ve made seven acquisitions so far to round out our online store, order fulfillment, marketing services, customer service, and we have launched some integral partnerships including easy access to growth capital through our relationship with Clearco and product protection for customers on every purchase with Extend. Instead of acquiring a data company, we’re building our data platform on Google Cloud, across each operating function for a single-view for brands to harness actionable data. We see Google Cloud as the leader for data management, analytics, machine learning (ML) and artificial intelligence (AI).
Other reasons why we’re building our business on Google Cloud include scalability, excellence, security, reach, and data analytics that are far superior to other environments.
We also feel a cultural and mission alignment with Google Cloud and envision leaning into a long-term partnership of marketing, selling, and disrupting the disruptors together. Equally important to us are the investments Google Cloud is willing to make in early-stage companies like ours. The support through the Google Cloud for Startups program has been outstanding.
Built on Google Cloud
A wide range of Google Cloud solutions provide the foundation for our platform. For instance, Cloud Pub/Sub keeps our services communicating with one another. We rely on fully managed relational databases, like Cloud SQL and Cloud Spanner, to securely handle the huge volume of brand and shopper data generated every day.
Cloud Run allowed us to develop inside of containers before our Kubernetes infrastructure was ready to go. Now, we are taking advantage of all the capabilities in Google Kubernetes Engine. BigQuery integrates with all Google Cloud solutions and offers true data streaming natively out of the box, along with Dataflow for advanced analytics. We also use Container Registry to store and manage our Docker container images. Right now, we’re testing Cloud Composer to evaluate using it for data workflow orchestration instead of Apache Airflow.
The openness of the Google Cloud environment is further enabled by Anthos, which we may deploy soon to perform data integrations quickly as we acquire more companies over the next year. For example, if we acquire a company using Azure, we can easily align it with our Google Cloud ecosystem.
Enabling ecommerce 2.0
Recently, our team has been experimenting with Google Cloud Vertex AI and the fully managed services of AI deployment and ML operations. The capabilities would save us substantial time in the management of the ML lifecycle which allows us to focus more on developing proprietary AI that will transform commerce at scale.
Because Google Cloud is so far ahead in data science, our teams benefit from deep Google Cloud expertise as we look to provide brands with unmatched insights into customers to improve services and revenue. We’re also planning to test Recommendations AI among other tools to deploy customer product recommendations and personalization as turnkey productized offerings. Moving forward, we will likely use Bigtable to aid in serving machine learning to hundreds of thousands of brands due to its low latency and scalability.
Fanatical about brand success
We know that our work with Google Cloud for Startups and use of Google Cloud solutions for best-in-class data management, analytics, ML, and AI will enable us to offer even more transformative services to brands.
We also see the opportunity to use our platform and customer insights to break down barriers between brands, enabling retailers to share information and work better together when it’s in their best interests. What we’re building today on Google Cloud is fundamentally changing what’s possible for retailers of any size everywhere.
As a startup, when recruiting talent or working with prospective customers, it helps to share our success with Google Cloud. We view them as an extension of the Cart.com team. It also validates our business as we continue building a more integrated, holistic approach to commerce that opens new opportunities and drives growth for brands worldwide.
For more details about Cart.com’s vision for unified ecommerce, check out our video.
If you want to learn more about how Google Cloud can help your startup, visit our page here to get more information about our program, and sign up for our communications to get a look at our community activities, digital events, special offers, and more.
More Relevant Stories for Your Company
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
This Diagnostic Company is Revolutionising Healthcare Delivery with AI
Dr. Elliot Smith cannot be accused of lacking ambition. A high achiever with a Ph.D. in Electrical Engineering and a specialist in magnetic resonance imaging (MRI) systems, Smith aims to deliver top quality healthcare to anyone in the world -- regardless of their location or wealth. Dr. Smith has already
Google Cloud Accelerates Financial Organizations’ Journey towards Digital Transformation
When I reflect back on the past year and the pandemic, I’m struck by how the reliance on remote work and operations has changed the fundamentals of business forever. For the financial services industry, this rings particularly true. Many conversations I’m having right now with organizations revolve around embracing a transformation
Smart analytics: Deep dive on roadmap
Data across organizations is growing and that organizations need a very strong analytics platform to leverage this data create insights and make real-time decisions on top of this data. That’s driving the advent of three large trends. First is the convergence of data lakes and data warehouses, that’s enable organizations
