Study guide
12.3 Querying, Estimating Costs, Backup & Job Monitoring for Databases (Cloud SQL, BigQuery, Spanner, Dataflow)
Google Cloud Associate Cloud Engineer ACE
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Introduction & Exam Overview
1.1 About the GCP Associate Cloud Engineer Exam 1.2 How to Use This Study Guide & Build a Study Plan
2 Chapter 2: Resource Hierarchy, IAM Basics & Cloud Identity
2.1 The GCP Resource Hierarchy: Organizations, Folders & Projects 2.2 Organization Policies & Granting IAM Roles 2.3 Cloud Identity: Managing Users and Groups
3 Chapter 3: APIs, Quotas & Billing Configuration
3.1 Enabling APIs and Provisioning Cloud Operations Suite 3.2 Assessing and Requesting Quota Increases 3.3 Billing Accounts, Project Linking & Billing Exports 3.4 Billing Budgets and Alerts
4 Chapter 4: Planning Compute & Storage Resources
4.1 Choosing the Right Compute Option: GCE vs. GKE vs. Cloud Run vs. Cloud Functions 4.2 Spot VMs, Custom Machine Types & Cost-Optimized Compute Planning 4.3 Choosing a Data Storage Product: Cloud SQL, BigQuery, Firestore, Spanner & Bigtable 4.4 Cloud Storage Classes & Persistent Disk Types
5 Chapter 5: Planning Network Resources
5.1 Load Balancing Options and Use Cases 5.2 Regional Resource Placement & High Availability Planning 5.3 Network Service Tiers: Premium vs. Standard
6 Chapter 6: Deploying Compute Engine Workloads
6.1 Launching Compute Engine Instances: Disks, SSH Keys & Availability Policies 6.2 Managed Instance Groups, Instance Templates & Autoscaling 6.3 OS Login and VM Manager for Fleet Configuration
7 Chapter 7: Deploying Google Kubernetes Engine Workloads
7.1 Installing and Configuring kubectl 7.2 Deploying GKE Clusters: Autopilot, Regional, Private & GKE Enterprise 7.3 Deploying Containerized Applications to GKE
8 Chapter 8: Deploying Serverless & Event-Driven Applications
8.1 Deploying Applications to Cloud Run and Cloud Functions 8.2 Event-Driven Architectures: Pub/Sub, Cloud Storage Triggers & Eventarc
9 Chapter 9: Deploying Data Solutions
9.1 Deploying Data Products: Cloud SQL, Firestore, Spanner, BigQuery & AlloyDB 9.2 Deploying Pub/Sub, Dataflow & Loading Data into Google Cloud
10 Chapter 10: Deploying Networking Resources & Infrastructure as Code
10.1 Creating VPCs, Subnets & Shared VPC 10.2 Firewall Rules, Cloud VPN & VPC Network Peering 10.3 Infrastructure as Code: Terraform, Config Connector, Cloud Foundation Toolkit & Helm
11 Chapter 11: Managing Compute Engine & GKE in Production
11.1 Connecting to and Managing Running Compute Engine Instances 11.2 Working with Snapshots and Custom Images 11.3 Managing GKE Clusters: Node Pools, Workloads & Artifact Registry Access 11.4 Horizontal and Vertical Pod Autoscaling in GKE
12 Chapter 12: Managing Cloud Run, Storage & Database Operations
12.1 Managing Cloud Run Revisions, Traffic Splitting & Scaling 12.2 Managing and Securing Cloud Storage Objects & Lifecycle Policies 12.3 Querying, Estimating Costs, Backup & Job Monitoring for Databases (Cloud SQL, BigQuery, Spanner, Dataflow)
13 Chapter 13: Networking Operations, Monitoring & Logging
13.1 Networking Operations: Subnets, Static IPs, Cloud DNS & Cloud NAT 13.2 Cloud Monitoring: Alerts, Custom Metrics & Dashboards 13.3 Cloud Logging: Buckets, Log Analytics, Routers & Filtering 13.4 Diagnostics, Ops Agent, Managed Service for Prometheus & Audit Logs
14 Chapter 14: IAM Policies & Role Types
14.1 IAM Policies: Structure, Bindings & Policy Hierarchy 14.2 IAM Role Types: Basic, Predefined & Custom Roles
15 Chapter 15: Service Accounts & Security Configuration
15.1 Creating Service Accounts & Assigning Least-Privilege Permissions 15.2 Assigning Service Accounts to Resources & Managing SA IAM Policies 15.3 Service Account Impersonation & Short-Lived Credentials
16 Chapter 16: Exam Readiness — Scenarios & Test-Day Strategy
16.1 Common ACE Exam Scenarios & Decision Frameworks 16.2 Test-Day Logistics, Time Management & Final Review Checklist
12.3 Querying, Estimating Costs, Backup & Job Monitoring for Databases (Cloud SQL, BigQuery, Spanner, Dataflow)
Key Takeaways Explain This Topic AI
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.
Last updated: July 2026
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 a mysql or psql session 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.JOBS is 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.