12.3 Querying, Estimating Costs, Backup & Job Monitoring for Databases (Cloud SQL, BigQuery, Spanner, Dataflow)
Key Takeaways
- BigQuery on-demand pricing is $6.25 per TiB processed with 1 TiB/month free; a free dry run reveals bytes processed before a query is billed.
- `INFORMATION_SCHEMA.JOBS` is the shared tool for BigQuery job-cost analysis and billing-spike investigation (exclude SCRIPT jobs to avoid double-counting).
- Cloud SQL uses automated/on-demand backups plus PITR via binary logs or WAL; Spanner backups always restore into a brand-new database, never in place.
- BigQuery has no traditional backup — time travel (default 7 days, minimum 2) and table snapshots serve that role instead.
- Dataflow jobs move through Running, Draining/Drained, and Cancelling/Cancelled states — draining finishes buffered data gracefully, cancelling stops immediately.
Why This Matters for the ACE Exam
The official exam guide groups four bullets under database and data-pipeline operations: executing queries against data instances, estimating the cost of data storage resources, backing up and restoring database instances, and reviewing job status for Dataflow and BigQuery. These bullets span four different services with four different operational models, which is exactly why ACE questions in this area are easy to mix up — Cloud SQL, BigQuery, and Spanner each back up and cost money in completely different ways.
Executing Queries
- Cloud SQL: connect interactively with
gcloud sql connect INSTANCE --user=USER, which authenticates and opens amysqlorpsqlsession through the Cloud SQL Auth Proxy without requiring you to whitelist an IP address. Cloud SQL Studio, built into the console, offers a browser-based query editor as an alternative. - BigQuery: run ad hoc SQL in the console's query editor, or from the command line with
bq query --use_legacy_sql=false 'SELECT ...'. - Spanner: run SQL from the console's query tab, or with
gcloud spanner databases execute-sql INSTANCE --database=DATABASE --sql="SELECT ...".
Estimating Costs
BigQuery is the service where cost estimation is most exam-relevant, because on-demand queries are billed by data scanned, not by a provisioned server. On-demand pricing is $6.25 per TiB processed, with the first 1 TiB of processing each month free. Before running an expensive query, a dry run (bq query --dry_run or the console's built-in query validator) reports exactly how many bytes the query would process — at no cost — so you can catch an accidental full-table scan before it bills you.
| Bytes a dry run reports | Approximate on-demand cost |
|---|---|
| 500 GB | Free (under the 1 TiB monthly allowance, if unused) |
| 8 TiB | 8 × $6.25 = $50 |
| 50 TiB | 50 × $6.25 = $312.50 (minus any remaining free allowance) |
For historical cost analysis rather than a single query, the INFORMATION_SCHEMA.JOBS view lets you query bytes billed, slot time, and the PRICE_PER_TIB used for each past job — a common way to investigate a sudden spike in the BigQuery line item on a bill. When aggregating job costs from this view, exclude SCRIPT statement types, since a script's child jobs can otherwise be double-counted alongside the script itself.
Cloud SQL and Spanner, by contrast, are priced by provisioned capacity (vCPUs, memory, storage, and for Spanner, compute capacity units or nodes) rather than by query, so their cost estimation work is closer to the compute-planning math from Chapter 4 than to a per-query calculation — the Google Cloud Pricing Calculator is the general-purpose tool for projecting that spend before deploying.
Backing Up and Restoring
The three managed databases protect data in three distinct ways, and confusing them is a favorite exam trap.
| Service | Backup mechanism | Key facts |
|---|---|---|
| Cloud SQL | Automated + on-demand backups | Automated backups run daily in a configurable 4-hour window; default retention keeps the most recent backups, configurable from 1-365. Point-in-time recovery (PITR) additionally requires binary logging (MySQL) or write-ahead logs (PostgreSQL), with log retention configurable from 1-7 days on Enterprise edition or up to 35 days on Enterprise Plus. |
| Spanner | On-demand + scheduled backups | A scheduled backup plan defaults to a 7-day retention window; any backup can be retained for up to one year. Restoring a backup always creates a new database populated from the backup data — you cannot restore over or into an existing database. |
| BigQuery | No traditional backup — time travel + table snapshots | Time travel lets you query a table exactly as it existed earlier, on by default, with a default 7-day window that is configurable down to 2 days but never lower. Table snapshots preserve a table's state indefinitely beyond the time-travel window, storing only the bytes that differ from the base table. A further 7-day fail-safe period retains data for disaster recovery after time travel expires, but recovering from it requires contacting Cloud Customer Care rather than a self-service action. |
Reviewing Job Status
Dataflow jobs move through a defined set of states, and the exam likes to test whether you know the difference between stopping immediately and stopping gracefully:
| State | Meaning |
|---|---|
| Running / Starting / Queued | Job is active or waiting for resources |
| Updating / Updated | An in-place pipeline update is in progress or complete |
| Draining / Drained | Job stops accepting new input but finishes processing data already in the pipeline, then shuts down cleanly |
| Cancelling / Cancelled | Job stops immediately, discarding any data still being processed |
| Succeeded / Failed | Job finished, either cleanly or with an error |
Monitor a job in the console's Dataflow monitoring interface (which shows the execution graph, watermarks, and autoscaling history) or from the command line with gcloud dataflow jobs list and gcloud dataflow jobs describe JOB_ID.
BigQuery jobs (queries, loads, exports, copies) are reviewed through the console's Query History / Job Details panel, the bq ls -j command, or programmatically through INFORMATION_SCHEMA.JOBS — the same view used for cost analysis above also shows job duration, slot-seconds consumed, and final status.
A Combined Scenario
A finance team notices BigQuery's line item on the monthly bill has tripled. The ACE-appropriate response: query INFORMATION_SCHEMA.JOBS for the last 30 days, sum bytes billed per user or per query pattern (excluding SCRIPT jobs to avoid double-counting), and cross-reference against the billing export configured back in Chapter 3. This combines two skills the exam treats as separate bullets — job-status review and cost estimation — into one realistic workflow.
Common Traps
- Assuming BigQuery has periodic backups the way Cloud SQL does — it does not; time travel and table snapshots serve that role instead.
- Forgetting the BigQuery time-travel window can be shortened but never below 2 days.
- Believing a Spanner restore can overwrite an existing database — it always creates a brand-new one.
- Treating "Draining" and "Cancelling" as the same outcome — draining finishes in-flight data first; cancelling stops immediately and can drop it.
Key Takeaways
- BigQuery on-demand pricing is $6.25/TiB scanned with 1 TiB/month free; a free dry run reveals bytes processed before you pay anything.
- Cloud SQL backs up via automated + on-demand backups and PITR (binary logs/WAL); Spanner backs up via on-demand/scheduled backups that always restore into a new database; BigQuery instead relies on time travel (default 7 days) and table snapshots.
INFORMATION_SCHEMA.JOBSis the shared tool for reviewing BigQuery job cost and history, and for investigating billing spikes.- Dataflow jobs move through Running, Draining/Drained, Cancelling/Cancelled, and Succeeded/Failed states — draining is a graceful stop, cancelling is not.
A BigQuery dry run reports that a query will process 8 TiB of data, and the project has no remaining free monthly allowance. At the on-demand rate of $6.25 per TiB, what is the approximate cost if the query is run?
Which BigQuery feature lets an analyst query a table's contents exactly as they existed a few days ago, without maintaining a manually created backup?
An administrator restores a Cloud Spanner database from a backup. What is true about the result?
A Dataflow job's status changes to "Draining." What does this mean for the pipeline?