Ensuring the successful operation of a cloud solution · Topic 5 of 8

Managing Databases and Data Jobs

Querying Cloud SQL, BigQuery, Bigtable, Spanner, Firestore and AlloyDB, backing up and restoring databases, checking Dataflow and BigQuery jobs, and Database Center.

4 min

In a nutshell

Query tools: gcloud sql connect, bq query, cbt, gcloud spanner databases execute-sql, client libraries and Studio tools in the console.
BigQuery on-demand cost depends on bytes processed; dry runs estimate it.
Cloud SQL: automated and on-demand backups, point-in-time recovery; others have backups and PITR options too.

What it is

Engineers run queries to check data, protect databases with backups and test restores, check the status of data jobs in Dataflow and BigQuery, and get a fleet-wide view with Database Center.

How it works

Querying data

Product	How to query
Cloud SQL	`gcloud sql connect INSTANCE --user=USER` opens a client; or connect apps through the Cloud SQL Auth Proxy or connectors; Cloud SQL Studio in the console
BigQuery	`bq query --use_legacy_sql=false 'SELECT ...'` or the BigQuery console
Bigtable	The `cbt` command-line tool (`cbt read TABLE`), client libraries, or Bigtable Studio
Spanner	`gcloud spanner databases execute-sql DB --instance=INSTANCE --sql="SELECT ..."` or Spanner Studio
Firestore	Console data viewer and client libraries; queries filter documents in a collection
AlloyDB	Standard PostgreSQL clients through the AlloyDB Auth Proxy, or AlloyDB Studio

bq query --use_legacy_sql=false \
  'SELECT region, SUM(total) AS revenue FROM shop.orders GROUP BY region ORDER BY revenue DESC'

BigQuery charges on-demand queries by bytes processed. Use --dry_run to estimate bytes before running, select only needed columns, and use partitioned and clustered tables.

Exam tip

"Estimate how much data a BigQuery query will scan before running it" is a dry run (bq query --dry_run or the console's validator).

Backing up and restoring

Product	Backup options	Restore
Cloud SQL	Automated daily backups with retention, on-demand backups, point-in-time recovery (using logs)	Restore to the same or a new instance; clone to a point in time
Firestore	Scheduled backups, managed exports to Cloud Storage, point-in-time recovery	Restore into a new database or import an export
Spanner	Backups (on demand or scheduled), point-in-time recovery within the retention period	Restore into a new database
AlloyDB	Automated and on-demand backups, continuous backup and recovery to a point in time	Restore into a new cluster
Bigtable	Table backups	Restore into a new table

gcloud sql backups create --instance=orders-db
gcloud sql backups list --instance=orders-db
gcloud sql backups restore BACKUP_ID --restore-instance=orders-db

Test restores regularly; a backup that cannot be restored is not a backup.

Reviewing job status

Job type	Where to check
Dataflow	Dataflow console job graph, metrics and logs; `gcloud dataflow jobs list` and `describe`
BigQuery	Job history in the console; `bq ls -j` lists jobs and `bq show -j JOB_ID` shows details and errors

Check a failed job's error messages and logs, and watch Dataflow metrics such as system lag and throughput for streaming jobs.

Database Center

Database Center gives a single view of the organization's database fleet across Cloud SQL, AlloyDB, Spanner, Bigtable, Firestore and Memorystore. It highlights issues and recommendations for availability, data protection (such as missing backups), security and cost, and includes Gemini-powered assistance to ask questions about the fleet.

A worked scenario

An engineer must confirm yesterday's sales numbers, protect the orders database and investigate a stuck pipeline. She runs a bq query after a dry run shows 2 GB scanned, confirms Cloud SQL automated backups and point-in-time recovery are on and takes an on-demand backup before a schema change, checks the Dataflow job graph to find the failing step, and uses Database Center to find two Cloud SQL instances without backups.

How the exam asks about this

"Restore a Cloud SQL database to a moment just before a bad change." Point-in-time recovery.
"Take a backup before maintenance." An on-demand backup (gcloud sql backups create).
"See why a BigQuery load failed." bq show -j JOB_ID or job history.
"Find databases across the organization without backups." Database Center.

What to remember for the exam

Query tools: gcloud sql connect, bq query, cbt, gcloud spanner databases execute-sql, client libraries and Studio tools in the console.
BigQuery on-demand cost depends on bytes processed; dry runs estimate it.
Cloud SQL: automated and on-demand backups, point-in-time recovery; others have backups and PITR options too.
Job status: Dataflow job graph and gcloud dataflow; BigQuery job history and bq show -j.
Database Center: fleet-wide health, protection, security and cost view.

Watch out

Point-in-time recovery requires it to be enabled before the incident. Turn it on when the database is created.

Common mistakes

Running large BigQuery queries without checking bytes. Dry run first.

Never testing restores. Verify backups by restoring them.

Checking databases one by one for issues. Database Center shows the fleet.

Check yourself

Which feature restores a Cloud SQL database to a specific moment?
Point-in-time recovery.
How can you see how much data a BigQuery query will process before running it?
Run it as a dry run.
What does Database Center provide?
A single view of the database fleet with issues and recommendations for availability, data protection, security and cost.

Topic 5 of 8 in Ensuring the successful operation of a cloud solution

Test yourself on Ensuring the successful operation of a cloud solution

The full Google Cloud Associate Cloud Engineer question bank has 661 questions, with timed practice exams and an explanation for every answer.

Subscribe Try Sample Exam

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Google Cloud. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.