Google Cloud Data Management Solutions

Study guide for Cloud Digital Leader, Unit 2 · Topic 2. This is the topic's lecture in reading form — every slide's teaching, figures and worked examples, in order — followed by the official Google Cloud pages its claims rest on.

What the exam guide asks. Determine which Google Cloud data management products are applicable to different business use cases.

Objectives, quoted from the exam guide:

1. Differentiate between Google Cloud data management options including data type and common business use case, including: Cloud Storage; Cloud Spanner; Cloud SQL; Cloud Bigtable; BigQuery; Firestore.
2. Define key data management concepts and terms, including: relational; non-relational; object storage; structured query language (SQL); NoSQL.
3. Describe the benefits of using BigQuery as a serverless, managed data warehouse and analytics engine that can be used in a multicloud environment.
4. Differentiate between storage classes in Cloud Storage regarding cost and frequency of access, including: Standard; Nearline; Coldline; Archive.
5. Describe the ways that an organization can migrate or modernize their current database in the cloud.

Google Cloud data management solutions

Six products, one vocabulary, four storage classes, one move

Which product fits the data? Cloud Storage, Cloud SQL, Spanner, Bigtable, BigQuery or Firestore. What do the words mean? relational, non-relational, object storage, SQL and NoSQL. Why BigQuery? serverless, managed, and able to reach data in other clouds. How cold is the data? Standard, Nearline, Coldline or Archive. How does an existing database get here? migrate as-is, or modernize on the way.

The guide's summary for this topic is a single instruction: determine which Google Cloud data management products are applicable to different business use cases. That is a matching exercise, and every objective supplies either the options or the vocabulary to match with. The first objective names six products — Cloud Storage, Cloud SQL, Spanner, Bigtable, BigQuery and Firestore — and asks what kind of data and which use case each is for. The guide still writes two of them with an older prefix, Cloud Spanner and Cloud Bigtable; Google's documentation now calls them simply Spanner and Bigtable. The second objective is the vocabulary that makes the matching possible: relational and non-relational, object storage, and the query languages. The third singles out BigQuery. The fourth is about Cloud Storage's four storage classes, which are a trade between how often data is read and what it costs to keep. And the fifth asks how an organization gets its existing database into the cloud. Five objectives, one question: what shape is the data, and what will be done with it?

Six products, six kinds of data

What each one stores, and the use case it is for

Worked example (synthetic). A streaming service stores its film files, its subscriber accounts, its viewing events and its quarterly analysis. That is four products — Cloud Storage, Cloud SQL, Bigtable and BigQuery — because it is four kinds of data.

Here are the six, read across the two columns the objective asks about: data type and common business use. Cloud Storage is object storage — Google calls it a scalable and managed storage service that lets you store data as objects in containers called buckets, and its own example is a photos bucket for the image files an app generates. Cloud SQL is a fully managed relational database service for MySQL, PostgreSQL and SQL Server, and it handles backups, high availability and failover, and maintenance and updates for you: the familiar database engines, without running the server. Spanner is also relational, but it brings together relational, graph, key-value and search, and offers transactional consistency at global scale. Bigtable is the wide-column one — ideal for storing large amounts of single-keyed data with low latency, and Google lists time-series data, Internet of Things data and financial data among its uses. BigQuery is the analytical one: a fully managed data platform that combines a cloud-based data warehouse and powerful analytic tools. And Firestore is a fully managed document database, which also supports, but does not require, integration with the Firebase mobile and web app development platform. Two pairs are worth separating now. Cloud SQL and Spanner are both relational; the difference is global scale. Bigtable and BigQuery both hold enormous datasets; the difference is that Bigtable serves an application low-latency reads and BigQuery answers analytical questions.

Choosing a product: two questions

What shape is the data, and what is done with it?

Figure: A decision tree. The first question is the shape of the data: files and media lead to Cloud Storage; documents for an application lead to Firestore; huge single-keyed series lead to Bigtable; data held for analysis leads to BigQuery; tables and relations lead to a second question — must transactions stay consistent at global scale — where no leads to Cloud SQL and yes leads to Spanner.

Worked example (synthetic). An item describes a bank whose account ledger must stay correct across three continents at once. The relational branch, then the global-consistency question, gives Spanner; Cloud SQL is the distractor.

Exam items on this objective never ask you to recite a product's description — they describe a business and its data and ask which product fits. So rehearse it as a decision. The first question is the shape of the data. Files and media — images, video, backups — are objects, and objects go to Cloud Storage. Documents that an application reads and writes go to Firestore, a document database that auto scales to match your load. Very large volumes of single-keyed data — readings over time, events keyed by device — go to Bigtable, which supports high read and write throughput at low latency. Data kept for analysis, whatever its source, goes to BigQuery. Only the relational branch needs a second question: must transactions stay consistent at global scale? If not, Cloud SQL, the fully managed service for MySQL, PostgreSQL and SQL Server. If so, Spanner, which offers transactional consistency at global scale with automatic, synchronous replication for high availability. The tree is deliberately short. If you find yourself needing a third question, re-read the stem for the shape of the data.

The words underneath the choice

Relational, non-relational, object storage, SQL and NoSQL

• Relational: tables of rows and columns, linked by primary and foreign keys
• SQL: the query language relational databases share
• NoSQL (not only SQL): non-relational, non-tabular, flexible schema
• Object storage: data plus metadata plus a unique identifier, in a flat space

Worked example (synthetic). An online shop keeps customers and orders in two tables joined by customer ID — relational. Its product reviews arrive as documents of varying fields — a NoSQL fit. Its product photos are objects.

The second objective is five terms, and they sort into two families: how structured data is organized, and how everything else is stored. A relational database, in Google's words, organizes data in predefined relationships where data is stored in one or more tables of columns and rows. The relationships are made with keys: every table has a primary key, a unique identifier of a row, and a foreign key in one table refers to a primary key in another — which is how a customer is joined to their orders. Structured query language, or SQL, is the language these databases share, and Google notes it is easy to run complex queries using SQL, even for non-technical users. The opposite family is NoSQL, which Google expands as not only SQL: non-relational databases that store data in a non-tabular format, with a flexible schema model that supports documents, key-value, wide columns and graphs. There is a cost to that flexibility — NoSQL has no lingua franca like SQL, and each database may have its own query language. Google names Bigtable, Memorystore and Firestore as its NoSQL databases. The last term is object storage: an architecture for unstructured data that sections it into objects and stores them in a structurally flat data environment, each object carrying the data, metadata and a unique identifier.

Three ways to hold data

Tables, flexible records, and objects

Figure. Three cards. Relational: tables of rows and columns in predefined relationships, queried with SQL — Cloud SQL and Spanner. Non-relational or NoSQL: non-tabular with a flexible schema — documents, key-value, wide columns and graphs — Firestore and Bigtable. Object storage: data plus metadata plus a unique identifier in a flat space for unstructured data — Cloud Storage.

Worked example (synthetic). Asked where email attachments, audio files and web pages belong, the graded answer is object storage — Google lists exactly these as unstructured data that does not fit easily into traditional databases.

Side by side, the three families differ in one word each. Relational data sits in predefined relationships — the schema comes first — and Cloud SQL and Spanner are Google Cloud's relational products. Non-relational data has a flexible schema: Google describes NoSQL — not only SQL — databases as supporting documents, key-value, wide columns and graphs, and names Firestore and Bigtable among its NoSQL databases. Object storage is flat: each object carries its data, metadata and a unique identifier, and there is no table and no hierarchy to fit it into. That is why it suits unstructured data, which Google lists as email, media and audio files, web pages, sensor data and other digital content that does not fit easily into traditional databases. One caution on the middle card: BigQuery is absent on purpose. It is a warehouse for analysis rather than a place an application keeps its records, and it is the subject of the next objective.

BigQuery: serverless, managed, multicloud

A warehouse you query, not a server you run

• Serverless: no resources to provision or manually scale
• Managed: Google's engineering team handles updates and maintenance
• Storage and compute are separate layers, each scaling on its own
• Multicloud: BigQuery Omni analyzes data held in other public clouds

Worked example (synthetic). A retailer keeps sales data in Google Cloud and its web logs in another provider's object store. With BigQuery Omni it queries both from one interface instead of copying the logs across first.

The third objective asks for BigQuery's benefits as a serverless, managed data warehouse and analytics engine that can be used in a multicloud environment — and each of those words has a mechanism behind it. Serverless: Google says BigQuery's serverless architecture lets you answer your organization's biggest questions with zero infrastructure management, so you do not need to provision or manually scale resources and can focus on delivering value instead of traditional database management tasks. Managed: it is a fully managed serverless data warehouse in which the BigQuery engineering team handles updates and maintenance. Under both sits the architecture. BigQuery has a storage layer that ingests, stores and optimizes data, and a compute layer that provides analytics, and their separation lets each allocate resources without impacting the performance or availability of the other. Google contrasts this with legacy databases, which usually have to share resources between read and write operations and analytical operations, slowing queries. Multicloud: many organizations store data in multiple public clouds, and it ends up siloed. With BigQuery Omni, you can run BigQuery analytics on data stored in Amazon Simple Storage Service or Azure Blob Storage, using BigLake tables.

Why separation matters

Storage and compute scale apart — and the data need not move

Figure: A flow from analysts writing SQL and Python to the BigQuery compute layer. The compute layer reads the BigQuery storage layer, and through BigQuery Omni and BigLake tables it also reaches data held in another public cloud.

Worked example (synthetic). A quarter-end reporting rush multiplies the number of queries but adds almost no data. Because compute and storage are separate layers, the query side absorbs the rush without the storage side changing.

Drawn out, the benefits stop being adjectives. On the left, analysts use languages like SQL and Python. Their queries run in the compute layer, which reads the storage layer — and because the two are separate, each can allocate resources without impacting the performance or availability of the other. That is the practical reason BigQuery can be serverless: nobody sizes a server that must hold both the data and the peak query load. The lower branch is the multicloud one. The compute layer can also reach data that never moved into Google Cloud, because BigQuery Omni runs BigQuery analytics on data stored in Amazon Simple Storage Service or Azure Blob Storage, using BigLake tables. So the answer to siloed data across clouds is not always to copy it; sometimes it is to query it where it sits.

The four Cloud Storage classes

Read less often, pay less to keep — and more to read

Worked example (synthetic). A law firm keeps closed case files it expects to open perhaps once in several years. Archive is the class built for that; Standard would charge it the hot-data rate for data nobody reads.

The fourth objective is Cloud Storage's storage classes, and the exam asks about them in terms of cost and frequency of access — so learn them as a ladder of access frequency. Standard storage is best for data that is frequently accessed — hot data — as well as data stored for only brief periods. Nearline storage is ideal for data you plan to read or modify on average once per month or less. Coldline storage is ideal for data you plan to read or modify at most once a quarter. Archive storage is the best choice for data you plan to access less than once a year, and Google describes it as the lowest-cost, highly durable storage service for data archiving, online backup and disaster recovery. Each step down the ladder carries a longer minimum storage duration: thirty days for Nearline, ninety for Coldline, three hundred and sixty-five for Archive. That minimum is part of the exam's cost question — a class chosen for data that will be deleted next week is a class chosen wrongly. Cloud Storage also now offers a Rapid storage class, a high-performance class optimized for input and output intensive workloads; it is not one of the four the guide lists.

What each step colder trades

Cheaper to keep, dearer to read, longer to commit

Figure. Four classes left to right from hot to cold: Standard, Nearline (monthly), Coldline (quarterly), Archive (yearly or less). Beneath them, three rows: at-rest storage cost falls with each step; data access costs and minimum storage duration rise with each step; every class keeps low latency with no offline retrieval, while colder classes have slightly lower availability.

Worked example (synthetic). An item claims Archive data must be restored offline before it can be read. That is false — every class has low latency with no offline data retrieval; what Archive changes is the cost of reading.

The table tells you where each class sits; this figure tells you what moving one step colder trades, in Google's own terms. Google describes Nearline as a better choice than Standard where slightly lower availability, a thirty-day minimum storage duration and costs for data access are acceptable trade-offs for lowered at-rest storage costs. It describes Coldline the same way against both warmer classes, with a ninety-day minimum and higher costs for data access. And Archive has higher costs for data access and operations, and a three-hundred-and-sixty-five-day minimum. So the pattern is one sentence: each step colder is cheaper to keep, more expensive to read, and a longer commitment. The row that candidates get wrong is the bottom one. Google lists low latency with no offline data retrieval as a feature of every storage class — Archive is not tape, and reading it is costlier, not slower. If an access pattern is unpredictable, Google offers Autoclass, which lets Cloud Storage manage storage class transitions automatically.

Migrating or modernizing a database

Move it as it is, or improve it on the way

• Rehost (lift and shift): minor or no changes; quickest, not cloud-optimized
• Replatform or refactor: change the workload to use cloud capabilities
• Homogeneous: same engine both sides; heterogeneous: different engines
• Database Migration Service: into Cloud SQL or AlloyDB for PostgreSQL

Worked example (synthetic). A retailer's MySQL database moves to Cloud SQL for MySQL unchanged — a homogeneous rehost. Its Oracle database moves to Cloud SQL for PostgreSQL — heterogeneous, and a modernization rather than a move.

The fifth objective asks how an organization can migrate or modernize its current database, and Google gives the vocabulary for both halves. For the approach, Google's migration guide defines the major types. In a rehost migration — lift and shift — you move workloads with minor or no modifications; it is the quickest, but afterwards the workloads aren't optimized for the cloud. In a replatform migration — lift and optimize — you lift the existing workloads and then optimize them for the new environment. In a refactor migration — move and improve — you modify the workloads to take advantage of cloud capabilities. For the database itself, Google's Database Migration Service helps you migrate data to Google Cloud and supports migrations into Cloud SQL and AlloyDB for PostgreSQL. It distinguishes two kinds of move. Homogeneous migrations take place between the same database technology. Heterogeneous migrations, such as Oracle to Cloud SQL for PostgreSQL, change the technology — which makes a heterogeneous migration a modernization as well as a move. And it offers two timings: a one-time migration, a single point-in-time snapshot, or a continuous migration, which keeps changes flowing after an initial full dump and load so that switching over while source and destination are in sync gives minimal downtime.

Two independent choices in every database move

How much to change, and how to cut over

Worked example (synthetic). A payments company cannot take its database offline for a weekend. Whatever engine it lands on, the timing choice is continuous migration, switched over when source and destination are in sync.

A database move is two decisions, and the exam likes to test one while describing the other. The first decision is how much changes. A homogeneous migration keeps the same database technology on both sides, and pairs naturally with a rehost, which Google calls the quickest migration because the refactoring is kept to a minimum. A heterogeneous migration changes the technology — Google's example is Oracle to Cloud SQL for PostgreSQL — and is the modernization path. The second decision is how to cut over. A one-time migration is a single point-in-time snapshot of the database, which means the source stops changing while it is taken. A continuous migration is a continuous flow of changes from source to destination that follows an initial full dump and load, and doing the switch when the two are in sync gives minimal downtime. Any combination is possible: a heterogeneous, continuous migration is modernizing while staying online. When an item stresses downtime, it is asking the second question, whatever it says about engines.

What this topic actually tests

Five discriminations, five objectives

Which shape is the data? objects, relational, documents, keyed series, or analysis. Relational at what scale? Cloud SQL, or Spanner for global consistency. Serve or analyze? Bigtable serves; BigQuery analyzes. How often is it read? monthly, quarterly, yearly — and colder costs more to read, never offline. What changes in the move? the engine, and separately the downtime.

Close on the five discriminations this topic leans on. First, the shape of the data picks the product family: objects to Cloud Storage, relational tables to Cloud SQL or Spanner, documents to Firestore, huge single-keyed series to Bigtable, and analysis to BigQuery. Second, relational at what scale: Cloud SQL for the familiar engines, fully managed; Spanner when transactions must stay consistent at global scale. Third, serve or analyze: Bigtable gives applications low-latency reads, and BigQuery answers questions across the business, with its storage and compute separated and its reach extended to other clouds by BigQuery Omni. Fourth, how often the data is read: Nearline monthly, Coldline quarterly, Archive less than yearly — cheaper to keep, costlier to read, and never offline. Fifth, what changes in a migration: the engine, which is homogeneous or heterogeneous, and separately the cutover, which is one-time or continuous. The next topic asks how that data becomes useful to the people who need it.

Official sources for this topic

• Cloud Digital Leader exam guide — Section 2
• Spanner documentation
• Bigtable overview
• About Cloud Storage
• Cloud SQL overview
• BigQuery overview
• Firestore overview
• What is a relational database? — Google Cloud
• What is a NoSQL database? — Google Cloud
• What is object storage? — Google Cloud
• Introduction to BigQuery Omni
• Storage classes — Cloud Storage
• Migrate to Google Cloud: Get started
• Database Migration Service overview