LEARN. PRACTICE. GROW.Start with the contract the user observes
Storage design requires describing what someone can conclude from a read. In a fictional fund-operations service, writing a correction and immediately showing the previous value can cause a wrong decision even when infrastructure has no incident. Record four boundaries: when a write is acknowledged, where it will be read, how long it remains valid and which unit must change together. Use two workshop examples: updating an individual preference and confirming two inseparable parts of a decision. Do not automatically impose the same solution on both. The business owner should explain the consequences of observing stale or partial state; the architect translates those consequences into testable criteria. During a trial, retain the operation identifier, write destination, read destination and expected result. A screenshot showing the right value supports one case, rather than demonstrating every failover path. Architecture documentation should make explicit the conditions under which the promise to the user holds.
Separate eligibility, visibility and deletion
A cleanup policy and a functional rule answer different questions. In Bigtable, a cell eligible for garbage collection can remain readable; filter reads to the interval the consumer accepts. In Firestore, TTL is not authorization with punctual expiration and does not remove subcollections by deleting their parent. Turn these differences into an exercise: a session ends at 16:00, still exists at 16:01 and has associated events. The backend must reject the expired action, while the data plan handles events separately. Ask the team to enumerate every access path, including direct calls bypassing the interface. For a Bigtable policy with two conditions, draw a truth table before configuration. Intersection requires both; union accepts either. Then check whether that choice matches the data owner’s intent. Do not declare a legal deletion obligation satisfied merely because you configured TTL; define the required operational evidence with the appropriate owners.
Choose the read destination and change boundary
Replication should not be treated as a box that removes every consistency problem. In Bigtable, routing relevant reads and writes to one cluster supports reading your own writes; moving to another cluster requires considering updates not yet replicated. A Cloud SQL read replica can also lag. For a sensitive confirmation, reading the primary can be a simple choice while lag-tolerant reporting stays on the replica. Document the cost and load this decision creates. Atomicity is another dimension: Bigtable’s MutateRows contract applies per entry, rather than to the complete batch. If the requirement spans two rows, do not declare it satisfied merely because there is one network request. A small set of known Firestore writes can instead use an atomic batch within applicable limits. In a review exercise, the learner should mark each operation’s boundary and explain a possible partial failure. The final choice should follow the observed requirement rather than familiarity with an API.
Preserve population and reference state
A seemingly visual change can alter the data population. Adding orderBy(priority) in Firestore excludes documents where that field does not exist. In a fictional queue of 120 tasks, showing 90 does not prove the other 30 were completed. Decide whether there should be a required value, a supplementary query or another approved representation; never invent an operational priority merely to make counts pass. Retention also needs an explicit time reference. In a BigQuery table partitioned by business date, inserting an old row today does not restart that partition’s expiration. To retain a queryable reference without changes, consider a snapshot with suitable expiration; to trial edits in an independent copy, consider a clone. Identify owner, permissions, lifetime and validation method for each copy. The learner should explain why saving a query alone does not freeze the data it will find in future. The exercise ends when they can choose an option and state evidence that would invalidate it.
Execute a counterexample before trusting a query
This lesson’s Python exercise creates an SQLite database entirely in memory. Before running it, predict three results: candidates [21,22] against blocked [21,NULL] using NOT IN; two (X,40) occurrences compared with one through EXCEPT; and totals for three events with two equal ordering values. Execution shows an unknown condition, hidden loss of multiplicity and the difference between RANGE and ROWS boundaries. SQLite uses EXCEPT without the DISTINCT keyword in this demonstration. The code also compares 81 count combinations with a separately constructed Python Counter. These trials validate the local results shown; they do not emulate BigQuery, costs, IAM or scalability. Consult the GoogleSQL references for the behavior taught in cloud questions. Then add a NULL candidate: NOT EXISTS with equality does not automatically block it, so the policy for missing IDs must be explicit. Discussion should finish with a testable statement of the contract, rather than merely replacing one operator mechanically with another.
Define ordering and resolve multiple matches
A rule for selecting the winning revision should exist before query optimization. ROW_NUMBER with a tied timestamp does not by itself define which revision wins. If the contract specifies revision_seq unique per trade, use that criterion as a tie-break; sorting the final result after selecting a row does not correct the earlier choice. In a GoogleSQL MERGE with UPDATE, two source rows matching one target row require resolving multiplicity instead of expecting an automatic choice. Give the learner two contexts: revisions replacing one another and occurrences that must all be retained. Deduplication may fit the first when there is a rule; in the second it destroys information. The archive case deliberately uses identical rows representing separate occurrences. Compare frequencies per tuple, specify NULL handling and preserve a source reference. A query finishing without errors is only a successful execution; acceptance requires demonstrating the requested meaning.
Measure the path being sized
A benchmark should represent the work whose capacity you intend to purchase. A BigQuery query with cacheHit=true did not measure computation over new data; trial it without result reuse and record plan, volume, duration and load context. Identify the job project too: reservation assignment does not move to the data project merely because the query reads a table there. Under on-demand pricing, an upper-bound estimate on a clustered table can exceed maximum_bytes_billed before execution; that does not prove the actual consumption assumed by the sponsor. Review filters and the authorized cap rather than removing the control without discussion. In another service, a Spanner index with STORING can avoid fetching an additional column from the table, using extra storage. An indexed Firestore timestamp that is never queried also deserves assessment for an index exemption. For each proposal, write the hypothesis, expected result, introduced cost and reversal condition. The aim is to justify a measurable change in the real workload, rather than accumulate enabled features.
Close the decision with evidence and ownership
At a migration committee, present the functional promise, observations and gaps separately. Normal promotion of a Cloud SQL read replica ends its previous replication; do not leave writers on the old primary assuming continuous synchronization. A filesystem transfer into Cloud Storage requires the agents and access for that path. Disabling Object Versioning does not remove existing noncurrent versions. These three examples expose dependencies that can disappear from a schedule listing only console buttons. Use this lesson’s cases to rehearse a meeting: someone presents a green test, another person identifies what that test actually proves and the owner decides against defined criteria. The summary is an explicit read contract, an appropriate atomicity boundary, retention with clear scope and SQL validation sensitive to NULL, ordering and multiplicity. Connect these decisions to migration reconciliation, application security and functional recovery. Record open questions and who resolves them before accepting the service.
"""Original, local SQL teaching fixture. No cloud connection or persistent database."""
import json
import platform
import sqlite3
from collections import Counter
from itertools import product
def run:
db = sqlite3.connect(':memory:')
checks = []
def check(name, actual, expected):
if actual!= expected:
raise AssertionError((name, actual, expected))
checks.append(name)
def query(sql, args=):
return db.execute(sql, args).fetchall
try:
db.executescript('''
CREATE TABLE candidates(id INTEGER NOT NULL);
CREATE TABLE blocked(id INTEGER);
INSERT INTO candidates VALUES(21),(22);
INSERT INTO blocked VALUES(21),(NULL);
CREATE TABLE original(k TEXT NOT NULL, amount INTEGER NOT NULL);
CREATE TABLE migrated(k TEXT NOT NULL, amount INTEGER NOT NULL);
INSERT INTO original VALUES('X',40),('X',40),('Y',20);
INSERT INTO migrated VALUES('X',40),('Y',20);
CREATE TABLE events(id TEXT PRIMARY KEY, bucket INTEGER, amount INTEGER);
INSERT INTO events VALUES('A',1,10),('B',1,20),('C',2,5);
CREATE TABLE revisions(trade TEXT, stamp INTEGER, seq INTEGER, value TEXT,
UNIQUE(trade,seq));
INSERT INTO revisions VALUES('T',100,1,'old'),('T',100,2,'new');
''')
check('matching NOT IN is false', query('SELECT 21 NOT IN (SELECT id FROM blocked)'), [(0,)])
check('nonmatching NOT IN with NULL is unknown', query('SELECT 22 NOT IN (SELECT id FROM blocked)'), [(None,)])
check('WHERE drops false and unknown', query('SELECT id FROM candidates WHERE id NOT IN (SELECT id FROM blocked)'), [])
check('explicit nonnull blocklist finds 22', query('SELECT id FROM candidates WHERE id NOT IN (SELECT id FROM blocked WHERE id IS NOT NULL)'), [(22,)])
check('NOT EXISTS equality finds 22', query('SELECT c.id FROM candidates c WHERE NOT EXISTS (SELECT 1 FROM blocked b WHERE b.id=c.id)'), [(22,)])
check('NULL candidate excluded by explicit policy', query('SELECT NULL WHERE NULL IS NOT NULL AND NOT EXISTS (SELECT 1 FROM blocked WHERE id=NULL)'), [])
check('NULL is not automatically equality matched', query('SELECT NULL WHERE NOT EXISTS (SELECT 1 FROM blocked WHERE id=NULL)'), [(None,)])
forward = query('SELECT k,amount FROM original EXCEPT SELECT k,amount FROM migrated')
reverse = query('SELECT k,amount FROM migrated EXCEPT SELECT k,amount FROM original')
check('set comparison hides lost duplicate forward', forward, [])
check('set comparison hides lost duplicate reverse', reverse, [])
check('source count is three', query('SELECT COUNT(*) FROM original'), [(3,)])
check('target count is two', query('SELECT COUNT(*) FROM migrated'), [(2,)])
source_counts = query('SELECT k,amount,COUNT(*) FROM original GROUP BY k,amount ORDER BY k,amount')
target_counts = query('SELECT k,amount,COUNT(*) FROM migrated GROUP BY k,amount ORDER BY k,amount')
check('source frequencies retain occurrences', source_counts, [('X',40,2),('Y',20,1)])
check('target frequencies expose lost occurrence', target_counts, [('X',40,1),('Y',20,1)])
ranges = query('SELECT id,SUM(amount) OVER(ORDER BY bucket RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM events ORDER BY id')
rows = query('SELECT id,SUM(amount) OVER(ORDER BY bucket,id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM events ORDER BY id')
check('RANGE groups peers at current boundary', ranges, [('A',30),('B',30),('C',35)])
check('ROWS with total order accumulates by event', rows, [('A',10),('B',30),('C',35)])
check('explicit tie break selects business revision', query('SELECT value FROM (SELECT value,ROW_NUMBER OVER(PARTITION BY trade ORDER BY stamp DESC,seq DESC) AS rn FROM revisions) WHERE rn=1'), [('new',)])
check('empty blocklist admits nonnull candidate', query('SELECT 22 WHERE 22 NOT IN (SELECT id FROM blocked WHERE 0)'), [(22,)])
check('no NULL and no match gives true', query('SELECT 22 NOT IN (SELECT id FROM blocked WHERE id IS NOT NULL)'), [(1,)])
# Compare SQL frequencies against independently constructed Python multisets.
# All 3^4 count assignments for X/Y in source/target; repeated rows are meaningful.
properties = 0
hidden_losses = 0
for sx, sy, tx, ty in product(range(3), repeat=4):
left = [('X',40)] * sx + [('Y',20)] * sy
right = [('X',40)] * tx + [('Y',20)] * ty
db.execute('DELETE FROM original')
db.execute('DELETE FROM migrated')
db.executemany('INSERT INTO original VALUES(?,?)', left)
db.executemany('INSERT INTO migrated VALUES(?,?)', right)
lf = query('SELECT k,amount,COUNT(*) FROM original GROUP BY k,amount ORDER BY k,amount')
rf = query('SELECT k,amount,COUNT(*) FROM migrated GROUP BY k,amount ORDER BY k,amount')
expected = Counter(left) == Counter(right)
if (lf == rf)!= expected:
raise AssertionError(('frequency comparison', sx, sy, tx, ty))
set_same = not query('SELECT k,amount FROM original EXCEPT SELECT k,amount FROM migrated') and not query('SELECT k,amount FROM migrated EXCEPT SELECT k,amount FROM original')
if set_same!= (set(left) == set(right)):
raise AssertionError(('set comparison', sx, sy, tx, ty))
hidden_losses += bool(set_same and not expected)
properties += 1
check('all finite multiplicity assignments checked', properties, 81)
check('set comparisons can hide multiplicity differences', hidden_losses > 0, True)
return dict(passed=len(checks), checks=checks, propertyCases=properties,
hiddenMultiplicityMismatches=hidden_losses,
example=dict(sourceCounts=source_counts,targetCounts=target_counts,rangeTotals=ranges,rowTotals=rows),
python=platform.python_version,sqlite=sqlite3.sqlite_version,
vendorExecution=False,network=False,persistentWrites=False)
finally:
db.close
if __name__ == '__main__':
print(json.dumps(run, ensure_ascii=False))
A set comparison passes despite losing an occurrence; an exclusion containing NULL also hides a difference the team expected to find.
Common pitfalls
Confusing cleanup with authorization, batching with cross-row atomicity, caching with measured capacity and equal sets with preserved occurrences.
Related topics: Migration, reconciliation and analytical results · Data, consistency and event publication · Observability and functional recovery
Specify population, validity, consistency and multiplicity; use counterexamples and measure the path that actually supports the requirement.
Reference: Bigtable garbage collection overview · Current linked standard guide; edition date unconfirmed (2026-09-30 inspection)