Isaac Newton said, “If I have seen farther than others it is because I have stood on the shoulders of giants.” He meant that in order to explain the Law of Gravity, he used the work of major thinkers who came before him in order to make intellectual progress (as-in giving credit). In the 1640’s (yes, the time of the English Civil War and the start of the mini Ice Age), we can say the Age of Reason began and the word “data” was also (re)born in a way.

Why? Hard to assert a specific reason, it could be apropos of all those events of that decade, and somewhere amidst all that, the scientists and Churchmen (rather Churchman, Henry Hammond who really coined the term data) started to pen down their credit in books and there was a continued proliferation of data to reason their finding (and most times to reason why their work was better than that of the others). And thus comes to us the word datum from the Latin verb dare (it means “to give”, not the English dare).

Dare to Recap History?

Data is history captured through language (it has become the future as well, but that is for another day). Now we all like history (well, most of us). But it is highly likely the context gets lost in the complexity and style of definition. One way to mitigate that risk is to have a clear set of definitions (language), sustained hold of events (history), a clean process of capture (extract) and a scalable process for translation and aggregation (transform). If we want our data to be successful and rise to the occasion, then we need to keep these ways to mitigate the risk of complexity in mind.

And this is exactly what we discussed in the Part 1 of this blog series, Data Modeling Basics—the various business attributes, technical aspects, design questions, and considerations for designing your database model.

In this blog…

We will look into the different databases and storage options in Google Cloud, a brief note on each one of them, when to choose one over the other, interesting alternatives, exceptions and if you make it to the end of the blog, a fun challenge to make sure we put this little tech nugget to an ACID test (see what I did there?). If you are a cloud enthusiast, a database practitioner, a data geek, or a general wonderer of life with computing, you may find this engaging…

Google Cloud Storage Options

We at Google Cloud, have realized how hard it is to go through these laundry list assessment aspects and have made it simpler for you with a Decision Tree. (Of course, It ain’t Christmas if not for the tree):

If only the world was always “Structured”

In a structured world, you will know all the attributes on a first-name basis (I mean to say that you will have a well defined fixed set of attributes that can be modeled in a table of rows and columns), and the applications are transactional or analytical in orientation. Transactional Structured Data operate one row at a time generally and they need to adhere to ACID compliance. (Ah. Now you connect the dots, if not already.) ACID properties are Atomicity, Consistency, Isolation, and Durability. Cloud SQL and Cloud Spanner are our Google Cloud choices for Transactional Structured Data use cases.

Let’s look at the below aspects for each type and structure of data:

• Why that option? (highlights and key features)
• When to choose?
• When not to choose?
• Security aspects

Cloud SQL

• Fully Managed, cloud-native RDBMS (Relational DataBase Management System) that offers both MySQL, PostgreSQL, SQL Server engines
• Cloud SQL is accessible from apps running on App Engine, GKE, or Compute Engine

Note: A managed database is one that does not require as much administration and operational support (creating databases, performing backups, updating the operating system of database instances) as an unmanaged database.

When to use Cloud SQL?

• Typical online transaction processing (OLTP) workloads
• Lift and shift of on-premise SQL databases (or from anywhere else) to cloud
• Regional applications that do not need to store > 30 TB of data in a single instance

When not to use Cloud SQL?

Cloud SQL is not an appropriate storage system for online analytical processing (OLAP) workloads or data that requires dynamic schemas on a per-object basis.

Security

Data stored is encrypted both in transit and at rest. Have built-in support for access control, using network firewalls to manage database access.

Cloud Spanner

• 
  Relational, horizontally scalable, global database with strong consistency
• Supports schemas, ACID transactions, and SQL queries (ANSI 2011)
• Scales horizontally in regions, but can also scale across regions for workloads that have more stringent availability requirements

When to use Cloud Spanner?

• For large amounts of data and when you require high transactional consistency
• When you require sharding for higher throughput, access and low latency

When not to use Cloud Spanner?

Cloud Spanner is not an appropriate storage system for online analytical processing (OLAP) workloads

Security

Security features in Spanner include data-layer encryption, audit logging, and Identity and Access Management (IAM) integration.

Analytical Structure is when we want the data to tell us an aggregated or enhanced story, for which we use limited columns and multiple rows and hence mostly use a Column-Oriented storage mechanism. Column-oriented storage is if we want to store the data in the tables by columns instead of by rows, and this column-oriented storage is done to efficiently access only a subset of columns for querying. BigQuery is the data warehouse option for analytics needs.

BigQuery

• 
  BigQuery is a fully managed Data Warehouse for analytics with built-in data transfer service
• Peta-byte scale, low-cost warehouse that supports loading data through the web interface, command line tools, and REST API calls
• Incorporates features for machine learning, business intelligence, and geospatial analysis that are provided through BigQuery ML, BI Engine, and GIS.

Note: A data warehouse stores large quantities of data for query and analysis instead of transactional processing.

When to use BigQuery?

For use cases that cover process analytics and optimization, big data (Petabyte scale) processing and analytics, data warehouse modernization, machine learning-based behavioral analytics, and predictions

When not to use BigQuery?

BigQuery is not a Transactional database and is oriented on running analytical queries, not for simple CRUD operations and queries.

Security

BigQuery provides encryption at rest and in transit. Cloud Data Loss Prevention (Cloud DLP) can be used to scan the BigQuery tables and to protect sensitive data and meet compliance requirements. BigQuery supports access control of datasets and tables using Identity and Access Management (IAM).

And then we have the Semi-structured and the Unstructured world of data that we will address in the below sections.

Cloud Firestore (Cloud Datastore)

Firestore is the next major version of Datastore and a re-branding of the product. Taking the best of Datastore and the Firebase Realtime Database, Firestore is a NoSQL document database built for automatic scaling, high performance, and ease of application development.

• A fully managed, serverless NoSQL Google Cloud database designed for the development of serverless apps that stores JSON data
• Can be used to store, sync, and query data for web, mobile, and IoT applications
• Automatically handles sharding and replication making it highly available, durable, and scalable
• Provides ACID transactions, SQL-like queries, indexes, and more
• If a client does not have network connectivity, the Firestore API lets your app persist data to a local disk and synchronizes itself with the current server state once connectivity is reestablished

When to use?

For use cases of app development, live synchronization, offline support, multi-user collaborative applications, leader board, etc.

When not to use?

Not a relational database so not meant for relational structured data use cases.

Security

Firestore Security Rules support serverless authentication and authorization for the mobile and web client libraries. Identity and Access Management (IAM) manages database access.

Cloud Bigtable

• Bigtable is a wide-column, fully managed, high-performance NoSQL database service designed for terabyte- to petabyte-scale workloads
• Bigtable is battle tested on Google internal Bigtable database infrastructure that powers Google Search, Google Analytics, Google Maps, and Gmail
• Provides consistent, low-latency, and high-throughput storage for large-scale NoSQL data

When to use?

• For large amounts of single key data and is preferable for low-latency, high throughput workloads
• For real-time app serving workloads and large-scale analytical workloads

When not to use?

While Bigtable is considered an OLTP system, it doesn’t support multi-row transactions, SQL queries or joins. For those use cases, consider either Cloud SQL or Datastore.

Security

• All the data at rest in Cloud Bigtable is encrypted using Google’s default encryption, by default.
• Instead of Google managing the encryption keys that protect your data, your Bigtable instance can also be protected using a key that you manage (customer-managed encryption keys (CMEK)) in Cloud Key Management Service (Cloud KMS).

Cloud Storage

• Google Cloud Storage is an object storage system that is durable and highly available, persists unstructured data like images, videos, data files, videos, backup, and other data
• It is unstructured and so the files in the cloud storage are atomic that you read the entire file but you cannot access specific blocks in the files
• Cloud Storage is available in multiple classes, depending on the availability and performance required for apps and services
• Standard – Offers the highest levels of availability and is appropriate for storing data that requires low-latency access
  Nearline – Low-cost, highly durable, fast-access storage service for storing data that you access less than once per month
  Coldline – Very-low-cost, highly durable, fast-access storage service for storing data that you intend to access less than once per quarter
  Archive – Lowest-cost, highly durable, fast-access storage service for storing data that you intend to access less than once per year
  

Security

Files in Cloud Storage are organized by project into individual buckets. These buckets can support either custom access control lists (ACLs) or centralized identity and access management (IAM) controls.

Firebase Realtime Database

• Firebase is a realtime, NoSQL, Google Cloud database that is a part of the Firebase platform that allows you to store and sync data in real-time and includes caching capabilities for offline use
• Data is stored as JSON and synchronized in real-time to every connected client and remains available when app goes offline

When to use?

For mobile and web app development, development of apps that work across devices

When not to use?

Not in relational dataset use cases. The Realtime Database is a NoSQL database and as such has different optimizations and functionality compared to a relational database. The Realtime Database API is designed to only allow operations that can be executed quickly.

Security

The Realtime Database provides a flexible, expression-based rules language, called Firebase Realtime Database Security Rules, to define how your data should be structured and when data can be read from or written to. When integrated with Firebase Authentication, developers can define who has access to what data, and how they can access it.

That’s a rather packed read. But I hope you find this useful to understand comprehensively the basics of data, storage options and databases in Google Cloud Platform.

Next Steps, before I go…

In the blog part 1 of the series, I ended with an action item – “How would you model a NoSQL solution for an application that needs to query the lineage between individual entities that are represented in pairs?”.

Well, my answer is Firestore. As part of this episode, why don’t you take some time to go over the options and key aspects that attribute to this.

This is a guest post authored by Firebase customer, Anton Ivanov, Founder & CEO of DealCheck

Real estate investing is a fantastic way to build a stream of passive income and grow your wealth. Numerous studies have pointed out that real estate investing has created more millionaires throughout history than any other form of investing (like this one and this one). So why don’t more people do it?

I asked myself this very question a few years ago after talking to a group of friends about the success I’ve had with real estate, and listening to their reasons why they think it’s out of their reach.

A common theme among them was that they viewed it as something too difficult to learn and master. There were too many steps, the learning curve was steep and there was a lot of room for mistakes for somebody just starting out, especially when analyzing the financial performance of potential investment properties.

Traditionally, most investors used spreadsheets to do the math – which works only if you know what and how you’re calculating something. But if you don’t know that, it’s very easy to make mistakes and overlook things. And no one wants to make mathematical errors before a huge purchase like an investment property.

Where do I even begin?!

And that’s when I had the idea to build DealCheck – a cloud-based, easy-to-use property analysis tool for real estate investors and agents. I wanted to create a platform that would help new investors learn the ropes and avoid costly mistakes, but at the same time provide the flexibility to perform more advanced analysis with a click of a button.

Making real estate investing easier and more accessible.

The Challenges of Solo Development

I was working as a front-end engineer at the time, so I knew I could build the UI myself, but what about the back-end, data storage, authentication, and a bunch of other things you need for a full-functioning cloud app?

I didn’t know anybody I could bring on as a co-founder, so I set out to research what technologies and platforms I could leverage to help me with the back-end and server infrastructure.

Firebase kept popping up again and again and I began to look at it in more detail. It was then recently acquired by Google and its collection of BaaS (backend-as-a-service) modules seemed to offer the exact solution I needed to build DealCheck.

I was especially impressed with the documentation for each feature and how well all of the different technologies could be tied together to create one unified platform.

It wasn’t long before I signed up and started building the first MVP of the app.

Using Firebase to Quickly Build a Scalable Backend

As the only developer on the project, I had limited time and resources to spend on building the back-end, so I set out to use every Firebase feature that was available at the time to my advantage.

My goal was actually to write as little server-side code as possible and instead focus on leveraging the different Firebase modules to solve three specific challenges:

Challenge #1 – Authentication and User Management

The first one was authentication and user management. DealCheck’s users needed the ability to create their accounts so they can view and analyze properties on any device (more on that later). I wanted to have the ability to sign in with email, Facebook or a Google account.

Firebase Authentication was designed specifically for this purpose and I used it to handle pretty much the entire authentication flow. Out-of-the-box, it has support for all the major social networks, cross-network credential linking and the basic account management operations like email changes, password resets and account deletions.

There was no server-side code required at all – I just needed to build the UI on the front-end.

Email, Facebook and Google sign in powered by Firebase.

And as an added benefit, Firebase Authentication ties directly into the Realtime Database product to create a declarative permissions and access control framework that’s easy to implement and maintain. This helped me make sure user data was protected from unauthorized access, but also facilitate data sharing among users.

Challenge #2 – Cloud Storage with Cross-Device Sync

Next up was data storage. I knew that I wanted DealCheck’s users to be able to use the app and analyze properties online, on iOS and Android. So I needed a real-time, cloud-based database solution that could sync data across any device.

Syncing data across web and mobile is not easy!

Firebase Realtime Database is a NoSQL, JSON-based database solution that was designed exactly for this purpose, and I was actually surprised how great it worked. I used the official AngularJS bindings for Firebase on the front-end to read and write to it directly from the client.

I had to do some extra work on mobile to implement an offline mode with syncing after reconnections, but all-together the code required to make everything work was minimal.

As I mentioned, Firebase Authentication tied directly to the database to facilitate access control, so I really didn’t need to do anything extra there. And I was able to set up automatic daily backups of all the data with a click of a button.

Challenge #3 – Third-Party Integrations

Up to now, I had written exactly 0 lines of server-side code and everything was handled by the client directly. As DealCheck’s development progressed, however, I knew that I would need a server to handle some operations that could not be done in the client.

I wasn’t very experienced with server maintenance and DevOps, but fortunately the Firebase Cloud Functions product was able to solve all of my needs. Cloud Functions are essentially single-purpose functions that can be triggered (or executed) based on a specific HTTP request or events coming from the Authentication, Realtime Database or other Firebase products.

Each function can be run once based on a specific event trigger to perform its prescribed task. You don’t have to worry about provisioning a server instance or managing load – everything is done automatically for you by Firebase.

What’s even cooler, is that Cloud Functions can access the Realtime Database and Cloud Storage buckets of the same project, performing operations on them server-side, as needed.

This is how DealCheck processes subscription payments through Stripe, validates Apple and Google Play mobile subscription receipts, integrates with third-party APIs and updates database records without user interaction.

Bringing in sales comparable data from third-party providers into DealCheck.

Cloud Functions became the “glue” that tied the entire back-end infrastructure together.

Growing from an MVP to 100,000 Users with Firebase

The first version of the DealCheck app was built and launched in less than 5 months with just me on the development team. I definitely don’t think that would have been possible without Firebase powering the back-end infrastructure. Maybe the project wouldn’t have ever launched at all.

While Firebase is awesome for quick MVP development, it’s definitely designed to power production applications at scale as well. As DealCheck grew from a small side-project to one of the most popular real estate apps with over 100k users, all of the Firebase products that we use scaled to support the increasing load.

Moreover, the fantastic interoperability of all Firebase modules allows us to develop and release new features much faster because of the reduced coding requirements and ease of configuration.

So next time you’re looking to build an ambitious project with a small team – take a look at how Firebase can help you reduce development time and provide a suite of powerful tools that scale as your business grows.

This is exactly how DealCheck grew from a simple idea to make property analysis easier and faster, to an app that is helping tens of thousands of people grow their wealth and passive income through real estate investing. It’s a truly awesome and fulfilling experience to see your work positively impact so many people and it wouldn’t have been possible without Firebase.

As Google Cloud has become a choice for more startups, I’ve experienced an increase in founders asking how they should think about cloud services. Though each startup is different and requirements may vary across industries and regions, I’ve seen a few core best practices that help startups to succeed—as well as several traps to avoid. 

For example, If you go with a public cloud provider, you’re ideally not starting from ground zero like you would running your own data center, but it’s important not to introduce similar complexity in a virtualized environment. Just because you’re using public cloud infrastructure doesn’t mean you want to manage it.

Instead, you want to leverage platforms that abstract away complexity so your team can focus on delivering value to customers. That’s where serverless comes in. Serverless platforms are fully managed by the provider, offering automatic scaling for workloads, as well as provisioning, configuring, patching, and management of servers and clusters. Freed from these resource-intensive tasks, your technical talent can focus on the things that differentiate your business, not on IT curation. 

This applies to not only running stateless applications, but also managing and analyzing data. You should be collecting data points about which features are most popular on your platform, what people are buying, the types of activities that help your customers get their jobs done, and so on. This information is essential to building and executing on a product roadmap that will serve your customers. However, it’s not enough to collect this data, you need to make your data accessible, secure, and easy for your team to analyze—so where do you run and host it? 

On Google Cloud, this is where options like Spanner and Cloud SQL can play a large role, as can BigQuery for analysis. You won’t have to worry about standing up infrastructure or patching servers—you can just stream your data to our data management platforms, where it’s available whenever someone needs to run a query. By leveraging a serverless architecture, your startup can be data-driven without having to invest in the traditional complexity of database administration and management—and that can significantly change your playing field. 

Serverless is just one of the factors you should consider as you build out your tech stack. To hear my thoughts on a range of other topics relevant to startups — such as security, cloud credits, and the differences among managed services — check out the below video or visit our Build and Grow page.

https://www.youtube.com/embed/gn4RjLbs8Hc?enablejsapi=1&

Today at Google Cloud Next we are announcing innovations that will enable data teams to simplify how they work with data and derive value from it faster. These new solutions will help organizations build modern data architectures with real-time analytics to power innovative, mission-critical, data-driven applications. 

Too often, even the best minds in data are constrained by ineffective systems and technologies. A recent study showed that only 32% of companies surveyed gained value from their data investments. Previous approaches have resulted in difficult to access, slow, unreliable, complex, and fragmented systems. 

At Google Cloud, we are committed to changing this reality by helping customers simplify their approach to data to build their data clouds. Google Cloud’s data platform is simply unmatched for speed, scale, security, and reliability for any size organization with built-in, industry-leading machine learning (ML) and artificial intelligence (AI), and an open standards-based approach.

Vertex AI and data platform services unlock rapid ML modeling 

With the launch of Vertex AI in May 2021, we empowered data scientists and engineers to build reliable, standardized AI pipelines that take advantage of the power of Google Cloud’s data pipelines. Today, we are taking this a step further with the launch of Vertex AI Workbench, a unified user experience to build and deploy ML models faster, accelerating time-to-value for data scientists and their organizations. We’ve integrated data engineering capabilities directly into the data science environment, which lets you ingest and analyze data, and deploy and manage ML models, all from a single interface.

Data scientists can now build and train models 5X faster on Vertex AI than on traditional notebooks. This is primarily enabled by integrations across data services (like Dataproc, BigQuery, Dataplex, and Looker), which significantly reduce context switching. The unified experience of Vertex AI let’s data scientists coordinate, transform, secure and monitor Machine Learning Operations (MLOps) from within a single interface, for their long-running, self-improving, and safely-managed AI services.

“As per IDC’s AI StrategiesView 2021, model development duration, scalable deployment, and model management are three of the top five challenges in scaling AI initiatives,” said Ritu Jyoti, Group Vice President, AI and Automation Research Practice at IDC. “Vertex AI Workbench provides a collaborative development environment for the entire ML workflow – connecting data services such as BigQuery and Spark on Google Cloud, to Vertex AI and MLOps services. As such, data scientists and engineers will be able to deploy and manage more models, more easily and quickly, from within one interface.”

Ecommerce company, Wayfair, has transformed its merchandising capabilities with data and AI services. “At Wayfair, data is at the center of our business. With more than 22 million products from more than 16,000 suppliers, the process of helping customers find the exact right item for their needs across our vast ecosystem presents exciting challenges,” said Matt Ferrari, Head of Ad Tech, Customer Intelligence, and Machine Learning; Engineering and Product at Wayfair. “From managing our online catalog and inventory, to building a strong logistics network, to making it easier to share product data with suppliers, we rely on services including BigQuery to ensure that we are able to access high-performance, low-maintenance data at scale. Vertex AI Workbench and Vertex AI Training accelerate our adoption of highly scalable model development and training capabilities.”

BigQuery Omni: Breaking data silos with cross-cloud analytics and governance

Businesses across a variety of industries are choosing Google Cloud to develop their data cloud strategies and better predict business outcomes — BigQuery is a key part of that solution portfolio. To address complex data management across hybrid and multicloud environments, this month we are announcing the general availability of BigQuery Omni, which allows customers to analyze data across Google Cloud, AWS, and Azure. Healthcare provider, Johnson and Johnson was able to combine data in Google Cloud and AWS S3 with BigQuery Omni without needing data to migrate. 

This flexible, fully-managed, cross-cloud analytics solution allows you to cost-effectively and securely answer questions and share results from a single pane of glass across your datasets, wherever you are. In addition to these multicloud capabilities, Dataplex will be generally available this quarter to provide an intelligent data fabric that enables you to keep your data distributed while making it securely accessible to all your analytics tools.

Spark on Google Cloud simplifies data engineering 

To help make data engineering even easier, we are announcing the general availability of Spark on Google Cloud, the world’s first autoscaling and serverless Spark service for the Google Cloud data platform. This allows data engineers, data scientists, and data analysts to use Spark from their preferred interfaces without data replication or custom integrations. Using this capability, developers can write applications and pipelines that autoscale without any manual infrastructure provisioning or tuning. This new service makes Spark a first class citizen on Google Cloud, and enables customers to get started in seconds and scale infinitely, regardless if you start in BigQuery, Dataproc, Dataplex, or Vertex AI.

Spanner meets PostgreSQL: global, relational scale with a popular interface

We’re continuing to make Cloud Spanner, our fully managed, globally scalable, relational database, available to more customers now with a PostgreSQL interface, now in preview. With this new PostgreSQL interface, enterprises can take advantage of Spanner’s unmatched global scale, 99.999% availability, and strong consistency using skills and tools from the popular PostgreSQL ecosystem. 

This interface supports Spanner’s rich feature set that uses the most popular PostgreSQL data types and SQL features to reduce the barrier to entry for building transformational applications. Using the tools and skills they already have, developer teams gain flexibility and peace of mind because the schemas and queries they build against the PostgreSQL interface can be easily ported to another Postgres environment. Complete this form to request access to the preview.

Our commitment to the PostgreSQL ecosystem has been long standing. Customers choose Cloud SQL for the flexibility to run PostgreSQL, MySQL and SQL Server workloads. Cloud SQL provides a rich extension collection, configuration flags, and open ecosystem, without the hassle of database provisioning, storage capacity management, or other time-consuming tasks.

Auto Trader has migrated approximately 65% of their Oracle footprint to Cloud SQL, which remains a strategic priority for the company. Using Cloud SQL, BigQuery, and Looker to facilitate access to data for their users, and with Cloud SQL’s fully managed services, Auto Trader’s release cadence has improved by over 140% (year-over-year), enabling an impressive peak of 458 releases to production in a single day.

Looker integrations make augmented analytics a reality

We are announcing a new integration between Tableau and Looker that will allow customers to operationalize analytics and more effectively scale their deployments with trusted, real-time data, and less maintenance for developers and administrators. Tableau customers will soon be able to leverage Looker’s semantic model, enabling new levels of data governance while democratizing access to data. They will also be able to pair their enterprise semantic layer with Tableau’s leading analytics platform. The future might be uncertain, but together with our partners we can help you plan for it. 

We remain committed to developing new ways to help organizations go beyond traditional business intelligence with Looker. In addition to innovating within Looker, we’re continuing to integrate within other parts of Google Cloud. Today, we are sharing new ways to help customers deliver trusted data experiences and leverage augmented analytics to take intelligent action. 

First, we’re enabling you to democratize access to trusted data in tools where you are already familiar. Connected Sheets already allows you to interactively explore BigQuery data in a familiar spreadsheet interface and will soon be able to leverage the governed data and business metrics in Looker’s semantic model. It will be available in preview by the end of this year. 

Another integration we’re announcing is Looker’s Solution for Contact Center AI, which helps you gain a deeper understanding and appreciation of your customers’ full journey by unlocking insights from all of your company’s first-party data, such as contextualizing support calls to make sure your most valuable customers receive the best service. 

We’re also sharing the new Looker Block for Healthcare NLP API, which provides simplified access to intelligent insights from unstructured medical text. Compatible with Fast Healthcare Interoperability Resources (FHIR), healthcare providers, payers, and pharma companies can quickly understand the context and relationships of medical concepts within the text, and in turn, can begin to link this to other clinical data sources for additional AI and ML actions. 

Bringing the best of Google together with Google Earth Engine and Google Cloud

We are thrilled to announce the preview of Google Earth Engine on Google Cloud. This launch makes Google Earth Engine’s 50+ petabyte catalog of satellite imagery and geospatial data sets available for planetary-scale analysis. Google Cloud customers will be able to integrate Earth Engine with BigQuery, Google Cloud’s ML technologies, and Google Maps Platform. This gives data teams a way to better understand how the world is changing and what actions they can take — from sustainable sourcing, to saving energy and materials costs, to understanding business risks, to serving new customer needs. 

For over a decade, Earth Engine has supported the work of researchers and NGOs from around the world, and this new integration brings the best of Google and Google Cloud together to empower enterprises to create a sustainable future for our planet and for your business.

At Google Cloud, we are deeply grateful to work with companies of all sizes, and across industries, to build their data clouds. Join my keynote session to hear how organizations are leveraging the full power of data, from databases to analytics that support decision making to AI and ML that predict and automate the future. We’ll also highlight our latest product innovations for BigQuery, Spanner, Looker, and Vertex AI.

I can’t wait to hear how you will turn data into intelligence and look forward to connecting with you.

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.