Managing storage and database solutions

Study guide for Associate Cloud Engineer, Unit 4 · Topic 4. 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, quoted. Managing storage and database solutions. Considerations include:

1. Managing and securing objects in Cloud Storage buckets
2. Setting object lifecycle management policies for Cloud Storage buckets
3. Executing queries to retrieve data from data instances (e.g., Cloud SQL, BigQuery, Spanner, Firestore, AlloyDB)
4. Estimating costs of data storage resources
5. Backing up and restoring database instances (e.g., Cloud SQL, Firestore)
6. Reviewing job status (e.g., Dataflow, BigQuery)

Managing storage and database solutions

The data exists — now secure it, age it, query it, protect it, watch it

Objects — who can reach them, and how they move. Lifecycle — rules that change or delete objects on their own. Queries — the command for each product. Cost — estimated before it is spent. Backups — and what a restore overwrites. Jobs — how to read their state.

Unit two chose the data products and topic four of unit three created and filled them. This topic is what an engineer does afterwards, and the guide lists six considerations: managing and securing objects in Cloud Storage buckets; setting object lifecycle management policies; executing queries against the data products; estimating the costs of data storage resources; backing up and restoring database instances; and reviewing job status. Every one of them is a day-two task, which is why the exam asks about consequences as much as commands — a move that is really a copy and a delete, a restore that overwrites, a lifecycle change that takes a day to settle. Each slide names the command and then the consequence.

Managing and securing objects

Who can reach an object, and what really happens when it moves

• Uniform bucket-level access: IAM alone grants access; ACLs are disabled
• Fine-grained access: IAM and ACLs together — harder to audit
• Signed URL: limited permission, for a limited time, to anyone holding it
• Soft delete protects against deletion by default; Google prefers it to versioning

Worked example (synthetic). A finance bucket grew up with per-object ACLs, and nobody can say who can read what. The team enables uniform bucket-level access, so identity and access management alone decides — and has 90 days to switch back if something breaks.

Securing objects starts with a choice Cloud Storage makes explicit: there are two systems for granting access, identity and access management, or IAM, and access control lists, or ACLs. Google marks one option recommended — uniform bucket-level access, which lets you use IAM alone to manage permissions. When you enable it, ACLs are disabled and only bucket-level IAM permissions grant access to the bucket and the objects in it. The alternative, fine-grained access, uses IAM and ACLs together, and Google names its cost: coordinating two systems means an increased chance of unintentional data exposure, and auditing who has access is more complicated. The switch is not immediately final — you have 90 days to switch back to fine-grained before uniform access becomes permanent. For someone who should reach one object briefly, there is the signed uniform resource locator, or URL: a URL that provides limited permission and time to make a request. You set its expiration when you create it, and anyone who knows it can use it until then. Two protections cover deletion. Soft delete retains deleted or overwritten objects for a specified period and is on by default, with a default retention of 7 days. Object Versioning keeps deleted objects as noncurrent versions until explicitly removed — but Google recommends soft delete instead of versioning to protect against permanent data loss.

Three ways to let someone reach an object

Match the need to the mechanism

Worked example (synthetic). A contractor with no Google account needs one invoice for an afternoon. No role grant fits a person with no identity — a signed URL that expires that evening does.

Put side by side, the three access mechanisms answer different questions. Uniform bucket-level access answers who, through identity and access management roles on the bucket; it is Google's recommended option, it disables access control lists, and it can be reverted for 90 days. Fine-grained access answers who at the level of a single object, by adding per-object grants on top of the roles — which is exactly why Google warns it is harder to audit. A signed uniform resource locator answers when: it gives limited permission for a limited time, and it reaches anyone who holds it until the expiration you set, or until the key that signed it is rotated. When a scenario gives you a person with no Google identity and a deadline, the signed URL is the only one of the three that fits.

Moving and reclassifying objects

A move may be a copy and a delete; a class change is a rewrite

Figure. Two cards and a band. Move: gcloud storage mv, where some tools move atomically and others copy then delete the original. Change one object's class: gcloud storage objects update with the storage-class flag, which rewrites the object and cannot be done in the console. Beneath: for more than one terabyte between buckets, use Storage Transfer Service.

Worked example (synthetic). An engineer opens the console to switch one archived object to Standard and cannot find the setting. It is not missing — Google says individual object classes cannot be set there; the command line does it.

Two everyday operations hide a mechanism the exam tests. Moving an object uses the gcloud storage mv command, but Google is explicit that not every tool moves the same way: some perform atomic moves, while others simulate a move by copying the object to the new location and then deleting the original. That deletion matters for cost, which objective four returns to. For large moves, more than 1 terabyte between buckets, Google recommends Storage Transfer Service instead. Changing a single object's storage class is the second operation, and it is a rewrite of the object: you use the gcloud storage objects update command with the storage-class flag. The trap is the console — Google states that individual object storage classes cannot be set through the Google Cloud console.

Setting object lifecycle management policies

Rules on the bucket that act on objects by themselves

• A lifecycle configuration is set on a bucket and applies to current and future objects
• Each rule: exactly one action, one or more conditions, all of which must match
• Actions: Delete, SetStorageClass, AbortIncompleteMultipartUpload
• Apply a JSON file with gcloud storage buckets update --lifecycle-file

Worked example (synthetic). A media team writes one rule to move objects older than a year to Coldline and another to delete them after three. The rules sit on the bucket, so next year's uploads age the same way without anyone touching them.

Object Lifecycle Management moves the work of ageing data from people to the bucket. To use it you define a lifecycle configuration, which must be set on a bucket, and its rules apply to current and future objects there. Each rule contains exactly one action and one or more conditions, and an object has to match all of a rule's conditions for the action to be taken. There are three actions: Delete, SetStorageClass, and AbortIncompleteMultipartUpload. On the command line you write the rules into a JavaScript Object Notation, or JSON, file and apply it with the gcloud storage buckets update command and the lifecycle-file flag. Two cautions come straight from Google. Test rules on development data before production, so they don't act under conditions you did not intend. And be patient after a change: changes to a lifecycle configuration can take up to 24 hours to go into effect, and the old configuration may still act during that time.

One object, ageing under a policy

Each step is a rule; the last one deletes

Figure: A flow from Standard to Nearline to Coldline to Archive, each step a SetStorageClass rule with an age condition, ending in a Delete rule. A dashed line shows that an object hold or an unmet retention policy blocks the delete.

Worked example (synthetic). A legal hold is placed on one object in a bucket whose policy deletes everything after three years. The policy still stands, but that object stays until the hold is lifted.

Drawn as a sequence, a typical policy is a staircase: SetStorageClass rules walk an object down from Standard to Nearline, Coldline and Archive as its age passes each condition, and a Delete rule ends it. Each arrow is one rule, with one action and its conditions. The dashed line is the exception the exam likes: the Delete action does not take effect on an object while it has an object hold placed on it, or a retention policy it has not yet fulfilled. The policy is not wrong in that case — it simply waits.

When several rules match at once

Cloud Storage performs one action, and the order is fixed

Worked example (synthetic). Two rules on the same age condition move objects to Nearline and to Coldline. Every matching object lands in Coldline — the colder class always wins the tie.

When rules conflict, Cloud Storage performs the action of only one rule, and the tie-breaks are fixed. A Delete action takes precedence over any SetStorageClass action. Between two SetStorageClass actions, the one that switches the object to the class with the lowest at-rest storage pricing wins — so a Nearline rule and a Coldline rule on the same condition always end in Coldline. A Delete that matches an object under a hold or an unmet retention policy waits. And a freshly changed configuration can take up to 24 hours to take effect, with the old rules still able to act in the meantime.

Executing queries against each product

One tool per product — and the one condition each carries

Worked example (synthetic). An engineer tries gcloud sql connect from Cloud Shell to an instance with only a private address, and it fails. The command is right; the place is wrong — Google says Cloud Shell doesn't work with a private IP address.

The third objective lists five products, and each is queried a different way. BigQuery uses the bq query command in the bq command-line tool, and the flag to remember is use_legacy_sql set to false, which lets you use GoogleSQL syntax. By default BigQuery runs queries as interactive jobs, intended to start as quickly as possible; the batch flag runs them as batch queries, which have lower priority. Cloud SQL is reached with the gcloud sql connect command, from Cloud Shell in Google's quickstart — with the condition that Cloud Shell doesn't work with a private internet protocol address. Spanner runs SQL through the gcloud spanner databases execute-sql command, in GoogleSQL or PostgreSQL syntax. Firestore is not SQL at all: it provides query functionality for choosing which documents to retrieve from a collection or collection group. And AlloyDB is queried with the psql client from a Compute Engine virtual machine, or VM, that connects over private services access.

Estimating costs of data storage resources

Estimate before you spend: bytes, assumptions and access patterns

• A BigQuery dry run reports the bytes a query would process — and is not charged
• The pricing calculator estimates from assumptions you supply
• A storage class trades at-rest cost for access costs and a minimum duration
• Deleting an object early, even by a copy-and-delete move, can be charged

Worked example (synthetic). Before a quarterly report query, an analyst runs it with the dry-run flag and sees how much data it would scan. The query is rewritten to touch fewer columns before it ever runs for real.

The fourth objective is estimation, and every tool here reports a quantity or an assumption, not a verdict. For queries, the tool is the BigQuery dry run, which lets you understand how much data a query will process before execution. You add the dry-run flag; dry runs don't use query slots, and you are not charged for performing one. Google suggests feeding the estimate into the pricing calculator. One exception is worth knowing: on tables masked by row-level security, a dry run always returns 0 bytes, so it cannot be relied on there. For exploring data rather than querying it, table preview shows data at no charge. For whole workloads, the Google Cloud pricing calculator estimates the costs of hypothetical workloads — but it creates estimates from assumptions you provide, and Google warns they may not reflect your final bill. Storage itself is estimated by class. The storage class affects an object's availability and pricing model: Nearline, for example, trades slightly lower availability, a 30-day minimum storage duration and costs for data access for lower at-rest costs.

What each estimate can and cannot tell you

An estimate is only as good as what it measures

Figure. Three cards and a band. Dry run: bytes a query would process, free, but reports zero bytes on row-level-security tables. Pricing calculator: a whole workload from supplied assumptions, which may not match the bill. Storage class: colder classes cost less at rest but add access costs and minimum durations. Beneath: a copy-then-delete move can incur early deletion charges on Nearline, Coldline or Archive objects; an atomic move does not.

Worked example (synthetic). A team moves a year's Coldline backups to a new bucket a few weeks after writing them, using a tool that copies then deletes. The estimate that mattered was not the storage bill but the early deletion charge.

Each estimate measures one thing and is blind to another. A dry run measures bytes processed, is free, and is blind on tables masked by row-level security. The pricing calculator covers a whole workload, but only as well as the assumptions you give it. A storage class estimate has to include both halves of the trade — lower at-rest cost, against access costs and a minimum storage duration. And the band at the bottom is the cost no estimate shows: because moving objects by copying them deletes the originals, moving Nearline, Coldline or Archive objects that way can incur early deletion charges. If you move objects atomically, Google says no early deletion charges are incurred, regardless of storage class.

Backing up and restoring database instances

Know what each restore overwrites before you run it

• Cloud SQL: on-demand or scheduled backups; gcloud sql backups create
• Cloud SQL restore overwrites the target instance's current data
• Point-in-time recovery always creates a new instance
• Firestore: daily or weekly backup schedules; restores go to a new database

Worked example (synthetic). A developer drops a table in production. Restoring last night's backup over the instance would also discard every good write since; a point-in-time recovery to a new instance recovers the table without touching the live one.

Cloud SQL lets you back up an instance on demand, or automatically on a backup schedule. The on-demand command is gcloud sql backups create, and for automated backups you edit the instance to set how many to retain. One warning comes first: all data on an instance, including its backups, is permanently lost when the instance is deleted. Restoring is where the exam probes. The gcloud sql backups restore command restores an instance from a backup, and Google warns that the restore process overwrites all current data on the instance, including previous point-in-time recovery logs. Replicas of the target must be deleted first. Restoring to a different instance updates that target to the state of the source when the backup was taken. Point-in-time recovery is the other path, and it always creates a new instance — you cannot perform one onto an existing instance. Firestore works differently. Its backups are scheduled — up to one daily and one weekly schedule per database — created with the gcloud firestore backups schedules create command, and a restore, with gcloud firestore databases restore, writes the backup to a new Firestore database rather than over the old one. Separately, Firestore's managed export and import service can recover from accidental deletion.

Where each restore writes

Overwrite, or a new resource — the question to ask first

Worked example (synthetic). An engineer expects a Firestore restore to repair the live database in place, and waits for it to change. It never will — the restore wrote a new database, and the application must be pointed at it.

The single question to ask before any restore is where it writes. A Cloud SQL backup restored to its own instance overwrites that instance — all current data and previous point-in-time recovery logs. The same command aimed at another instance sets that target to the state the source had when the backup was taken. Cloud SQL point-in-time recovery always creates a new instance. And a Firestore restore always writes to a new database. Two of the four leave the live resource untouched, and the exam likes to ask which ones.

Reviewing job status

Read the state, then decide whether to wait, cancel or drain

• BigQuery jobs: PENDING, RUNNING, DONE — list with bq ls -j, inspect with bq show
• Dataflow jobs list: running jobs and 30 days of history, with status
• gcloud dataflow jobs list --status=active or --status=terminated
• Stop Dataflow by cancel (drops buffered data) or drain (finishes it)

Worked example (synthetic). A nightly BigQuery load has sat in PENDING for an hour. That is not a failure — the job is scheduled and waiting to run — so the engineer checks capacity rather than resubmitting it.

Every BigQuery operation is a job, and a job is in one of three states: PENDING, scheduled and waiting to be run; RUNNING, in progress; and DONE, completed. On the command line, bq ls with the jobs flag, or its short form dash j, lists jobs; bq show with job set to true and a job identifier shows one; and bq cancel with the job identifier requests cancellation. Dataflow has its own monitoring interface. Its jobs list shows all running jobs and all jobs from the last 30 days, with status, region and elapsed time, and its status field reads Running, Succeeded or Failed; opening a job shows its job graph, with a job summary, a job log and information about each step. The gcloud dataflow jobs list command filters by status: active for jobs that are running, terminated for jobs that have finished. When a Dataflow job must stop, the choice is about buffered data. Cancelling stops processing all data, including what is buffered. Draining finishes processing the buffered data while ceasing to take in new data. Force cancel exists only for jobs stuck in a regular cancel.

Two job lifecycles, side by side

BigQuery counts to DONE; Dataflow must be stopped deliberately

Figure: Two flows. A BigQuery job moves from PENDING to RUNNING to DONE. A Dataflow job that is Running either Succeeds, Fails, or is stopped — by draining, which finishes buffered data, or by cancelling, which drops it.

Worked example (synthetic). A streaming Dataflow job must stop for an upgrade, and events already in flight must not be lost. Draining it is the answer; cancelling would drop the buffered data.

Drawn side by side, the two lifecycles differ in one way that decides exam answers. A BigQuery job counts forward on its own: pending, running, done. A Dataflow job that is running may succeed or fail, but a streaming job often has to be stopped on purpose, and there are two doors out. Draining lets the service finish processing buffered data while it stops taking in new data. Cancelling stops processing everything, buffered data included. If the scenario says in-flight data must not be lost, it is asking for drain.

What this topic actually tests

Four consequences hidden behind ordinary commands

A move can be a delete — and early deletion is charged. A lifecycle change is not instant — up to 24 hours. A restore may overwrite — Cloud SQL to itself does; Firestore and point-in-time recovery never do. Stopping a stream — drain keeps buffered data, cancel drops it.

Close on the four consequences this topic hides behind ordinary commands. A move can be a copy and a delete, and deleting Nearline, Coldline or Archive objects early can be charged; atomic moves avoid it. A lifecycle change can take up to 24 hours, and the old rules may act meanwhile. A restore may overwrite: a Cloud SQL backup restored to its own instance replaces all current data, while a Firestore restore and a Cloud SQL point-in-time recovery always write somewhere new. And stopping a streaming Dataflow job is a choice about buffered data — drain keeps it, cancel drops it. Topic five turns from data to networking and applies the same habit: find the consequence before running the command.

Official sources for this topic

• Associate Cloud Engineer exam guide — Section 4
• Overview of access control — Cloud Storage
• Uniform bucket-level access — Cloud Storage
• Signed URLs — Cloud Storage
• Soft delete — Cloud Storage
• Object Versioning — Cloud Storage
• Object Lifecycle Management — Cloud Storage
• Manage object lifecycles — Cloud Storage
• Run a query — BigQuery
• Connect to Cloud SQL for MySQL from Cloud Shell
• Create and query a database using the gcloud CLI — Spanner
• Query and filter data — Firestore
• Connect a psql client to an instance — AlloyDB for PostgreSQL
• Estimate and control costs — BigQuery
• Estimate costs — Cloud Billing
• Google Cloud pricing calculator
• Storage classes — Cloud Storage
• About Cloud SQL backups
• Create and manage on-demand and automatic backups — Cloud SQL
• Restore an instance — Cloud SQL
• Use point-in-time recovery — Cloud SQL
• Back up and restore data — Firestore
• Export and import data — Firestore
• Manage jobs — BigQuery
• Dataflow monitoring overview
• Troubleshoot your Dataflow pipeline
• gcloud dataflow jobs list — reference
• Stop a running pipeline — Dataflow
• Copy, rename, and move objects — Cloud Storage
• Change the storage class of an object — Cloud Storage