LEARN. PRACTICE. GROW.1. Choose the destination before restoring
Storage recovery starts with a destination decision. Do you want a copy for investigation, replacement of the dataset served to an application, or reconstruction of a lost dependency? These intentions have different acceptance criteria. In a fictional fund-position case, an isolated copy can support reconciliation without authorizing the application to change databases. Record backup provenance, the selected point in time, destination and dependent consumers. The existence of a file or snapshot does not answer these questions. In Spanner, backup restoration creates a new database. Do not plan to run the same procedure over an existing database. If the destination uses another project or instance configuration, first prepare a backup copy at a compatible destination. Include that copy time in the window rather than discovering it during an incident. The new database also needs capacity for storage and traffic. The cutover plan must explain how the application starts using the destination, how the result is measured and under which conditions the current source remains in use.
2. Distinguish availability, optimization and dependencies
A restored database can be usable before reaching the intended operational state. In READY_OPTIMIZING, Spanner permits use while data copying and optimization continue. A successful query does not prove the latency required by the business under load. Storage metrics may not yet represent the complete dataset, and the database still depends on the mounted backup. Define an acceptance workload with representative volumes and concurrency rather than relying on a single-row read. Recovery also has components that do not automatically return with the data. Database-specific permissions and internal change-stream data require separate attention; TTL row-deletion policies must be reconfigured. Distinguish permissions inherited from the instance from controls applied directly to the database. For CMEK backups, confirm that the key and version needed to read the backup remain available. Choosing another destination key does not reconstruct lost source material. The recovery report should separate reconciled data, authorized application access, consumer continuity and accepted performance. Each conclusion needs corresponding evidence.
3. Recover tables without confusing operations
A BigQuery table snapshot is a read image. To correct data, create a writable table from that image and preserve the evidence supporting the recovery point. Creating a snapshot and restoring a table from it are different operations: the restriction against replacing an existing name during creation does not prevent restoration from being configured to overwrite a table. Before authorizing that overwrite, identify current content that would be lost and alternatives for isolated recovery. Do not transfer cost assumptions between mechanisms. A destination in another region involves a copy; it should not be budgeted as metadata with no additional storage. Even when a snapshot shares blocks, base-table changes can increase the blocks billed to the snapshot. Reclustering can rewrite more data than a small logical change suggests. Also check partition expiry applied at the destination: the snapshot does not preserve that information as many runbooks assume. Choosing a snapshot, copy or writable table should reflect retention, cost, isolation and the work allowed on recovered data.
4. Protect versions in a data lake
An object name is insufficient to identify the version an intervention intends to use. Between listing, validating and copying, another process may change the source or destination. Preconditions must accompany the operation: protecting only the source does not prevent overwriting new destination work; protecting only the destination does not prevent copying a changed source. For metadata, use the expected generation together with metageneration. An equal metageneration can exist on a different object generation. For Cloud Storage data writes, generation condition zero represents absence of a live version. Noncurrent versions alone do not prevent satisfying this special case. Do not interpret zero as disabling the check. In soft-delete restoration, the new copy becomes live and can overwrite a live version already occupying the name; planning must account for that concurrency. The restored copy is Standard, so post-recovery cost can differ from the original archive. Record observed versions, expected content and the decision taken when a condition fails rather than retrying without understanding current state.
5. Prepare local publication with SQLite
The lab executes real SQL in an in-memory SQLite database. It does not write to the cloud or open a user-supplied database. Input contains objects, the initial state, and batches, the ordered requests. Each object has key, generation and payload. Each change contains the key, expectedGeneration, payload and the expected SHA-256 of its UTF-8 bytes. Generation is a local counter, not a Google Cloud identifier. Zero in the expectation means the key must be absent from the local catalogue. Each batch has an identifier and between one and twenty changes with distinct keys. The program validates the entire structure before starting work. Within BEGIN IMMEDIATE, it compares hashes and generations, writes changes and records the request fingerprint in the ledger. Only then does it commit. If a check fails, it rolls back, including earlier changes from the same batch. This transactional contract belongs to the exercise’s SQLite database. It does not demonstrate that a set of Cloud Storage objects can be published atomically through the same sequence of calls. A real platform must explicitly design atomicity boundaries and its promotion protocol.
6. Retry without undoing later work
Run python3 content/labs/pde-atomic-publish/run.py < content/labs/pde-atomic-publish/case.json. The first request publishes A and B. The second changes A inside the transaction but fails because it expects B to be absent when B already exists. Rollback restores A and the second request is not added to the ledger. Repeating the first request returns already-applied. Final state retains A at generation 5 and B at generation 1. Read both the batch outcomes and the objects and identifiers actually committed. Now introduce a valid publication between the first request and its retry. The already-applied response must not write the old payload over the newer publication. The ledger recognizes a completed intent; it is not a rollback command. Reusing the identifier with different content produces operation-id-conflict. A new identifier also does not bypass an old generation expectation. A failed batch is not recorded as applied and a corrected version can be attempted after reassessment. Ledger persistence ends with execution, so the exercise does not promise idempotency across restarts. Try reversing the changes within one request: its fingerprint remains equal because the program sorts by key. However, do not reverse dependent requests and expect the same result. A request creating A must precede another expecting that newly created generation. This distinction prevents confusing transport retries with reordering intentions. When inspecting the result, compare the ledger, generation and payload rather than only the final status message. Keep the original input alongside the output so a colleague can reproduce the exercise.
7. Serve the intended dataset again
Recovering files does not guarantee that the platform queries the intended dataset. A BigLake table can continue using cached metadata after its URI changes to a recovered prefix. Cutover needs an explicit cache refresh and evidence from the resulting query. When configuration is automatic and refresh is needed after a URI change, follow the documented procedure of temporarily switching to manual, refreshing and restoring the mode. Also confirm execution location and the refresh job result. Cryptographic protection has similar boundaries. The BigQuery table and cache CMEK does not automatically configure underlying Cloud Storage files. In the recovery inventory, separate data, metadata, identities, keys and path references. The catalogue team can differ from the bucket team, but the plan must assign someone end-to-end validation. A successful query by an administrator does not replace a rehearsal using application identity and permissions. The objective is to serve the approved dataset again with approved access, rather than merely making resources visible.
8. Turn checks into acceptance criteria
In the exercise, change B’s hash and confirm that A is not partially published. Then retain valid hashes but use exchange rates from the wrong date. The technical result can be applied even though the dataset is unsuitable for close. A hash confirms correspondence with declared bytes, not source truth, the correct date or business coherence. Add semantic acceptance criteria: temporal cut, expected keys, reconciled totals and relationships between positions and rates. In handover, present what was executed, what was verified and what remains pending separately. For example: database recovered and balances reconciled; application load test outstanding; historical consumers covered by a separate plan; lake publication conditional on cache refresh. Retain links to results and artifact versions. The central rule is to preserve concurrent work and expose failures before cutover. Lab tests demonstrate local properties, including rollback and retry behavior, but do not prove durability, real concurrency between processes, cloud atomicity or production authorization. Those requirements need rehearsal and decisions in the organization’s environment.
"""Original in-memory SQLite publication exercise. No cloud or file writes."""
import hashlib
import json
import re
import sqlite3
import sys
def exact(value, fields):
if type(value) is not dict or set(value)!= set(fields):
raise ValueError('unexpected or missing fields')
def ident(value):
if type(value) is not str or not re.fullmatch(r'[A-Za-z0-9_-]{1,48}', value):
raise ValueError('invalid identifier')
def integer(value, low=0):
if type(value) is not int or not low <= value <= 10**9:
raise ValueError('generation outside contract')
def payload(value):
if type(value) is not str or len(value.encode('utf-8')) > 1000:
raise ValueError('payload must contain at most 1000 UTF-8 bytes')
def digest(value):
return hashlib.sha256(value.encode('utf-8')).hexdigest
def evaluate(document):
exact(document, ['objects', 'batches'])
objects, batches = document['objects'], document['batches']
if type(objects) is not list or len(objects) > 100:
raise ValueError('at most 100 initial objects')
if type(batches) is not list or len(batches) > 20:
raise ValueError('at most 20 ordered batches')
keys = set
for obj in objects:
exact(obj, ['key', 'generation', 'payload'])
ident(obj['key']); integer(obj['generation'], 1); payload(obj['payload'])
if obj['key'] in keys:
raise ValueError('duplicate initial key')
keys.add(obj['key'])
for batch in batches:
exact(batch, ['id', 'changes']); ident(batch['id'])
if type(batch['changes']) is not list or not 1 <= len(batch['changes']) <= 20:
raise ValueError('each batch needs 1 to 20 changes')
keys = set
for change in batch['changes']:
exact(change, ['key', 'expectedGeneration', 'payload', 'sha256'])
ident(change['key']); integer(change['expectedGeneration']); payload(change['payload'])
if type(change['sha256']) is not str or not re.fullmatch(r'[0-9a-f]{64}', change['sha256']):
raise ValueError('invalid SHA-256 syntax')
if change['key'] in keys:
raise ValueError('duplicate key within batch')
keys.add(change['key'])
db = sqlite3.connect(':memory:', isolation_level=None)
results = []
try:
db.execute('CREATE TABLE objects (key TEXT PRIMARY KEY, generation INTEGER NOT NULL, payload TEXT NOT NULL)')
db.execute('CREATE TABLE ledger (id TEXT PRIMARY KEY, fingerprint TEXT NOT NULL)')
db.executemany('INSERT INTO objects VALUES (?,?,?)', [(x['key'], x['generation'], x['payload']) for x in objects])
for batch in batches:
changes = sorted(batch['changes'], key=lambda x: x['key'])
fingerprint = digest(json.dumps(changes, sort_keys=True, separators=(',', ':'), ensure_ascii=True))
db.execute('BEGIN IMMEDIATE')
try:
previous = db.execute('SELECT fingerprint FROM ledger WHERE id=?', (batch['id'],)).fetchone
if previous:
status = 'already-applied' if previous[0] == fingerprint else 'operation-id-conflict'
db.execute('ROLLBACK')
results.append({'id': batch['id'], 'status': status, 'blockedKey': None})
continue
blocked = None
for change in changes:
key = change['key']
current = db.execute('SELECT generation FROM objects WHERE key=?', (key,)).fetchone
generation = current[0] if current else 0
if digest(change['payload'])!= change['sha256']:
blocked = ('hash-mismatch', key)
elif generation!= change['expectedGeneration']:
blocked = ('generation-mismatch', key)
elif generation == 10**9:
blocked = ('generation-limit', key)
if blocked:
break
db.execute('INSERT INTO objects VALUES (?,?,?) ON CONFLICT(key) DO UPDATE SET generation=excluded.generation,payload=excluded.payload', (key, generation + 1, change['payload']))
if blocked:
db.execute('ROLLBACK')
results.append({'id': batch['id'], 'status': blocked[0], 'blockedKey': blocked[1]})
else:
db.execute('INSERT INTO ledger VALUES (?,?)', (batch['id'], fingerprint))
db.execute('COMMIT')
results.append({'id': batch['id'], 'status': 'applied', 'blockedKey': None})
except Exception:
if db.in_transaction:
db.execute('ROLLBACK')
raise
state = [{'key': k, 'generation': g, 'payload': v} for k, g, v in db.execute('SELECT key,generation,payload FROM objects ORDER BY key')]
ledger = [r[0] for r in db.execute('SELECT id FROM ledger ORDER BY id')]
return {'objects': state, 'batches': results, 'committedBatchIds': ledger,
'sqliteVersion': sqlite3.sqlite_version, 'stateLifetime': 'this invocation only',
'cloudAtomicityProven': False, 'businessCorrectnessProven': False,
'productionPublicationApproved': False}
finally:
db.close
def unique_object(pairs):
result = {}
for key, value in pairs:
if key in result:
raise ValueError('duplicate JSON field')
result[key] = value
return result
if __name__ == '__main__':
try:
raw = sys.stdin.read(2_000_001)
if len(raw) > 2_000_000:
raise ValueError('input exceeds local limit')
print(json.dumps(evaluate(json.loads(raw, object_pairs_hook=unique_object)), sort_keys=True))
except (ValueError, TypeError, RecursionError, UnicodeError, sqlite3.Error) as error:
print(json.dumps({'error': str(error)}), file=sys.stderr)
sys.exit(2)
Fictional case: recovered positions and rates may replace the current set only if approved versions still match and business reconciliation passes.
Common pitfalls
Confusing snapshots with restoration; ignoring IAM and consumers; removing preconditions after conflict; treating hashes as proof of correct data; inferring cloud atomicity from a local transaction.
Related topics: Preconditions and concurrency · Snapshots and copies · Transactions and idempotency
Restore, reconcile and publish are related decisions, each needing its own evidence. Preserve versions and concurrent work before cutover.
Reference: Professional Data Engineer standard exam guide · Current linked standard guide (document title v4.2); edition date unconfirmed (2026-09-30 inspection)