Choosing a database

Two questions choose among five databases

Relational?tables and SQLyes: relational, queried with SQLno: non-relational, NoSQLa familiar engine, in one regionCloud SQLMySQL, PostgreSQL, SQL ServerPostgreSQL, with heavy demandsAlloyDBfast transactions and analyticsglobal scale, always consistentSpannerrelational, across regionsdocuments for web and mobile appsFirestorelive sync, works offlinehuge volume, fast simple lookupsBigtablesensor, time-series and ad dataTwo questions choose the database: is the data relational, and how far must it scale?Relational databases keep data in tables and guarantee transactions. The three differ in reach: Cloud SQLfor most applications, AlloyDB for demanding PostgreSQL work, Spanner for the whole world at once.Non-relational databases give up joins for a simpler shape that scales easily.

Relational: Cloud SQL, AlloyDB or Spanner. Non-relational: Firestore or Bigtable.

Choosing a database · Going deeper · 1 of 2

The seven data products

Seven data products in the guideProductWhat it isReach for it whenCloud StorageObject storage for files of any kindimages, backups, archives, a data lakeCloud SQLManaged MySQL, PostgreSQL, SQL Servermoving an existing relational applicationAlloyDBPostgreSQL-compatible, built for speeddemanding transactions with analytics on topSpannerRelational, distributed across regionsglobal consistency and near-limitless scaleFirestoreServerless NoSQL document databasemobile and web apps that sync liveBigtableNoSQL wide-column databasevery high volume with low delay: sensors, adsBigQueryServerless data warehouseanalysing large data with SQL, and ML on itSix of the seven store data for applications. BigQuery is the one built for analysis.If the question is about reports, trends or analysts, the answer is BigQuery whatever the database is.

Six store data for applications. BigQuery is the one built for analysis.

Choosing a database · Going deeper · 2 of 2

Relational, non-relational, object

Three kinds of storage, and two words for queryingHow data is heldProductsRelationalTables with fixed columns, joined by keysCloud SQL, AlloyDB, SpannerNon-relationalDocuments, key and value, or wide rows; no joinsFirestore, BigtableObject storageWhole files with a name, in buckets; not queriedCloud StorageSQL is the language of relational data. NoSQL means "not only SQL": a non-relational database.BigQuery is queried with SQL too, but it is a warehouse for analysis, not a database for an application.Structured data usually goes to a relational database, semi-structured to NoSQL, unstructured to objects.

SQL for relational tables, NoSQL for the rest, object storage for whole files.

Cloud Storage classes

Colder storage is cheaper to keep and dearer to read

To keep 1 TB for a monthTo read that 1 TB backStandardread often$20$0Nearlineabout once a month$10$10Coldlineabout once a quarter$4$20Archiveless than once a year$1.20$50Autoclass: Cloud Storage moves each object to the right class as its use changesThe colder the class, the less it costs to keep and the more it costs to read.All four classes hold data equally safely and return it in milliseconds; only the price differs. So the choiceis about how often the data is read. Bars use 2025 list prices in one United States region, as an example ofthe pattern. The exam asks which class fits a need, never a price.

From Standard to Archive, keeping 1 TB falls from $20 to $1.20, and reading it rises from $0 to $50.

Cloud Storage classes · Going deeper

The classes and their shortest stay

The storage classesClassReadShortest stayTypical contentsStandardread oftennoneweb site files, video being watchedNearlineabout once a month30 daysbackups you may restoreColdlineabout once a quarter90 daysdisaster recovery copiesArchiveless than once a year365 daysrecords kept for regulationAutoclassunknown or changingmanaged for youdata whose pattern you cannot predictA colder class charges if data is removed before its shortest stay is over.That is why cold classes suit data you are sure to keep. When you cannot predict the pattern, Autoclassdecides object by object.

Nearline 30 days, Coldline 90, Archive 365. Autoclass decides for you.

Check yourself

JanuaryDecember4 reads in the year

Each mark is a time the backups were read during one year. Which storage class is designed for this pattern?

Analytics and BI

BigQuery finds the answer, and Looker shares it

sales systemsthe web sitespreadsheetsBigQueryone warehouse, askedin SQL, no servers to sizeLookerone agreed meaningfor every measurelive dashboardsscheduled reportsinside other appsBigQuery answers the question. Looker puts the same answer in front of everyone who needs it.The guide says Looker "democratizes" data: people who cannot write SQL can explore it themselves. Becausemeasures such as revenue are defined once in Looker, two reports can no longer disagree. Looker readsBigQuery directly, so dashboards show current data, not last night's copy.

BigQuery is the serverless warehouse. Looker defines each measure once and delivers it to everyone.

Analytics and BI · Going deeper

What Looker adds

What business intelligence with Looker addsAccess for everyonePeople explore data with clicks, not code: this is "democratising" accessOne definitionEach measure is defined once and reused, so every report agreesReal-time reportsLooker queries BigQuery directly, so figures are currentDashboardsA shared, live view of the measures a team runs onIn the workflowInsights appear inside the tools people already use, and can trigger actionsBusiness intelligence turns stored data into decisions made by many people, not a few specialists.On the exam, "dashboards", "self-service" and "a single source of truth" all point to Looker.

Self-service access, one definition of each measure, and real-time reports.

Streaming analytics

A stream is analysed while the event still matters

Eventspayments, clicks, sensorsPub/Subreceives and deliversDataflowprocesses on arrivalBigQuerystores, answersActionan alertA stolen card is used at 14:02. When does the bank know?streaming5 seconds laternightly batch11 h 58 min later, at 02:00Streaming analytics acts on an event while it still matters. A batch finds out after the damage.Pub/Sub takes in events from anywhere and delivers each to whoever subscribes. Dataflow processes thestream as it flows: cleaning, joining and totalling. The same pair serves fraud alerts, stock levels, livepricing and machines that report their own faults.

Pub/Sub carries events and Dataflow processes them: 5 seconds, against 11 hours for a nightly batch.

Streaming analytics · Going deeper

The pipeline products

Products for data pipelinesProductIts jobChoose it whenPub/SubReceives streams of events and delivers themmany sources send data continuouslyDataflowProcesses streams and batches, serverlessdata must be transformed as it arrivesManaged Service for Apache SparkRuns Spark and Hadoop jobs for youyou already have Spark or Hadoop workBigQueryStores the result and answers questions in SQLthe processed data is to be analysedPub/Sub carries the data. Dataflow changes it. They are partners, and are never the same answer.Managed Service for Apache Spark was called Dataproc until 2026; older study material uses that name.Its clue is an existing investment in open source Spark or Hadoop.

Pub/Sub carries, Dataflow transforms, Managed Service for Apache Spark runs existing Spark work.

Check yourself

thousandsof devices?receives every event,delivers it tosubscribersDataflowBigQuery

Which product belongs at the "?" in this pipeline?

Moving databases

Keep the engine and move, or change the engine and gain

MigrateMySQL in your datacentresame engineCloud SQL for MySQLlittle change, done in weeks;patching and backups handed overModernisean ageing commercial databasenew engineAlloyDB or Spannerthe application changes too;more scale, fewer licence feesDatabase Migration Service copies the data while the old database keeps running, then switches overMigrating keeps the engine and moves the work of running it. Modernising changes the engine to gain more.Both end with a managed database: Google patches it, backs it up and keeps it available. Migration is fasterand safer; modernisation costs more effort and returns more. Many organisations migrate first.

Migrate to the same engine on Cloud SQL, or modernise to AlloyDB or Spanner.

Moving databases · Going deeper

Clue words for data products

When the question saysThe product isBecause it is"move our MySQL or SQL Server as it is"Cloud SQLmanaged, same engine"PostgreSQL", "high performance"AlloyDBPostgreSQL-compatible, fast"global", "relational", "consistent"Spannerscales across regions"mobile app", "sync", "offline"Firestoreserverless documents"IoT", "time series", "low latency at scale"Bigtablewide-column NoSQL"analyse", "warehouse", "SQL on petabytes"BigQueryserverless warehouse"images, video, backups, any file"Cloud Storageobject storage"accessed less than once a year"Archive classcheapest to keep"access pattern unknown"Autoclasschooses the class for you"dashboards", "self-service"Lookerbusiness intelligence"ingest events from many sources"Pub/Submessaging"process a stream in real time"Dataflowstream and batch processingSection 2 questions are won by matching a phrase to a product.

Match the phrase to the product: twelve pairs cover section 2.

Check yourself

?player profiles kept as documents,changes appear on every phone at once,and the game still works offline

A game studio needs the store marked "?". Which product fits?

Lesson complete

You finished “Data products and analytics on Google Cloud”

Answer the questions along the way to see your score here.

Open in a new tab