LEARN. PRACTICE. GROW.1. Define what must remain true
A data migration needs a testable contract. Start with the unit recognized by the business: movement, position, balance, portfolio or document version. A table can have the expected number of rows while assigning values to the wrong portfolio. Define the key, relevant fields, data window, transformation rules and consumers that must keep working. Also record who decides on a discrepancy and what evidence is required before cutover. A snapshot or change-boundary identifier should accompany results so that comparisons do not silently mix different states. In a fictional APS project, the objective is to migrate movement reporting before monthly close. The business requires preserving values per movement, separating portfolios and delivering the report by the agreed time. These conditions lead to different checks: content reconciliation, access testing with representative identities and execution of the consuming process. Copy success replaces none of them. Divide the plan into extraction, transfer, transformation, loading, reconciliation and consumer acceptance, each with explicit inputs and outputs. This lets you locate the failing stage and assign concrete work instead of merely reporting that migration is red.
2. Protect the entire data path
Design security across the complete path. The source, intermediate bucket, target dataset and validation results can contain sensitive information. For each copy, identify readers, writers, retention and ownership. Use synthetic data or an approved transformation when a rehearsal does not need real values. A need to compare data does not automatically authorize copying all production data into development. Difference reports also need protection: they may reveal precisely the values that final tables restrict. In BigQuery, an authorized view can expose a projection without granting direct source access, but you must review other consumer access paths and who can change the view definition. An administrator-only test is not evidence of isolation. For encryption, configuring a dataset CMEK default does not change tables that already existed. Acceptance inventory must distinguish the default from each earlier resource’s configuration. Tie every claim to a resource and observation: “protected dataset” is too broad unless it identifies the copy, identity and control actually checked.
3. Compare compatible states and grains
First choose the logical state to compare. If the source keeps receiving movements while you validate an earlier copy, differences may represent later changes or actual failures. You need a shared boundary, such as a controlled snapshot or documented change position. An identical label in two files is a useful declaration, but it does not authenticate extraction or establish that the producer respected the boundary. BigQuery transaction snapshot guarantees must not be extended to an external source that changes during the transaction. Next choose the grain. An account key is insufficient when several movements belong to one account. Verify uniqueness before matching rows. A declared BigQuery PRIMARY KEY NOT ENFORCED does not reject duplicates for you. Identical and conflicting duplicates should remain visible until an approved handling rule exists. Finally, combine checks: counts, key sets, record values and aggregates by relevant dimension. With A=10 and B=20 versus A=11 and B=19, total 30 passes while both rows fail. The correct result retains both observations rather than selecting only the favorable metric.
4. Make permitted equivalences explicit
Define normalization field by field. If 0017 and 17 identify different portfolios, integer conversion loses information. If NULL means unknown while empty text means present without content, replacing both with one marker can hide an error. A hash summarizes its supplied representation; it cannot recover distinctions discarded before calculation. Validation tools allow transformations and field comparisons, so rule configuration belongs in review and should be versioned with results. For amounts, define currency, scale and tolerance before comparing. In this lesson’s exercise, amounts are exact to two decimal places and no currency conversion exists. Combining EUR and USD into one total does not establish preservation per currency. Do not round away a discrepancy merely to pass the check. For dates, distinguish a global instant from civil time. 10:00Z and 11:00+01:00 represent the same instant; removing the offset destroys the information supporting that equivalence. A time without a timezone needs explicit context. Exercise rules reject it rather than assuming the computer’s timezone, but a real system can have another contract that must be defined and tested.
5. Prepare portability and migration dependencies
Portability includes format, types, queries, identities and operation. A file exported from a service still needs interpretation by its consumer. Direct CSV export does not preserve a nested or repeated-field design; choose a compatible format or explicit transformation and validate the result. Avoid testing only one simple row when the schema permits empty arrays, multiple elements and nulls. SQL translation also needs appropriate metadata and semantic tests: absence of translation errors does not measure monthly-process results or duration. For dependency inventory, ask which cycles fell outside the observed window. Two weeks without reads do not exclude a table used at quarterly close. Include reports, schedules, integrations and manual processes that actually depend on the data. Evaluate location and recovery separately too. Selecting EU in BigQuery does not establish cross-region redundancy. Do not infer a disaster capability from a location name. In the project plan, turn these points into owners, acceptance tests and schedule dependencies. Incremental migration can reduce each decision’s scope but still requires clear boundaries between migrated consumers and those depending on the source.
6. Run local reconciliation
The Python program below is an original exercise with no Google Cloud calls. It reads JSON from standard input and writes a JSON report. Each side contains snapshot, complete and rows. Every row requires id, currency, amount, at and reference. The id is the unique key in this fictional contract. amount is decimal text with at most two decimal places; the program converts it into integer cents. at is a whole-second timestamp with an explicit offset. reference can be text or null, preserving spaces and case. Missing and extra fields are rejected. Save the code as run.py and prepare two rows per side: source A with 10.00 and B with 20.00; target A with 11.00 and B with 19.00. Use EUR, the same instant and snapshot cut-17, declaring complete=true. Run python3 run.py < case.json. Expect counts [2,2] and EUR totals of 3000 on both sides, but equivalentUnderDeclaredContract=false and cents differences for A and B. Correct the amounts and repeat. Then add an identical duplicate to both sides: comparison should still reject equivalence through nonunique-keys. The exercise does not arbitrarily select one row to hide duplicates.
7. Explore counterexamples and limits
Create four file variants. First replace key B with C only at the target: the report should show B in missing and C in unexpected even with equal totals. Second change reference from null to empty text on one side: that field should differ. Third represent the same instant with Z on one side and an equivalent offset on the other: that textual difference should disappear after time interpretation. Fourth keep identical rows but use different snapshots or complete=false: scope blockers should prevent a positive conclusion. Read outputs literally. The program sums supplied records by currency and shows duplicates; it does not prove inventory completeness. Two empty sides declared complete can be equivalent under this contract, but that does not establish that production extraction should have been empty. snapshotAuthenticityVerified, accessControlsVerified and productionCutoverApproved remain false. There is no BigQuery client, DVT implementation, latency measurement, permission analysis or guarantee about remote systems. The learning value is finding counterexamples and bounding what a comparison proves. Before applying a similar rule in a real project, define type contracts and collect evidence of data provenance.
8. Decide cutover and prepare RUN
Prepare the committee with three groups of information: what was compared and passed, what differed and what has not yet been observed. For each gap, present consumer impact, ownership and the next test. Keep approved criteria visible. If the target has already accepted writes, redirecting readers to a frozen source does not guarantee preservation of those new records. The rollback plan must handle that boundary and demonstrate reconciliation of data present only at the target. Successful routing change is not fidelity validation. For RUN handover, deliver the comparison-rule version, known failure examples, extract identifiers, discrepancy decision owners and escalation procedure. Support should distinguish update lag, schema change, access failure and content error. The next lesson should deepen ingestion and processing while retaining this contract. Keep five questions for any design: what unit is compared, which logical state applies, which equivalences are authorized, what access paths exist and which consumer was actually tested? Explicit answers make architecture and operational decisions more testable.
"""Original offline DR exercise. Declared record equivalence is not migration approval."""
import json
import re
import sys
from collections import Counter
from datetime import datetime, timezone
def fields(value, expected):
if type(value) is not dict or set(value)!= set(expected):
raise ValueError('Unexpected or missing fields')
def label(value):
if type(value) is not str or not value or value!= value.strip:
raise ValueError('Expected a nonempty label without surrounding whitespace')
return value
def amount_cents(value):
# Exact integer arithmetic. No binary float conversion or implicit rounding.
if type(value) is not str or not re.fullmatch(r'-?(0|[1-9][0-9]{0,15})(\.[0-9]{1,2})?', value):
raise ValueError('Expected a bounded decimal string with at most two decimal places')
negative = value.startswith('-')
whole, _, fraction = value.lstrip('-').partition('.')
cents = int(whole) * 100 + int(fraction.ljust(2, '0'))
return -cents if negative else cents
def instant(value):
if type(value) is not str or not re.fullmatch(r'\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}(Z|[+-]\d{2}:\d{2})', value):
raise ValueError('Expected whole-second ISO timestamp with explicit offset')
if value.endswith('-00:00'):
raise ValueError('Unknown local offset is not an established instant')
if not value.endswith('Z'):
hours, minutes = map(int, value[-5:].split(':'))
if hours > 23 or minutes > 59:
raise ValueError('Invalid offset')
parsed = datetime.fromisoformat(value.replace('Z', '+00:00'))
return parsed.astimezone(timezone.utc).isoformat
def dataset(value):
fields(value, ['snapshot', 'complete', 'rows'])
label(value['snapshot'])
if type(value['complete']) is not bool or type(value['rows']) is not list:
raise ValueError('Expected explicit completeness and row list')
rows = []
for row in value['rows']:
fields(row, ['id', 'currency', 'amount', 'at', 'reference'])
rid = label(row['id'])
if type(row['currency']) is not str or not re.fullmatch(r'[A-Z]{3}', row['currency']):
raise ValueError('Currency must be a three-letter code; no conversion is performed')
if row['reference'] is not None and type(row['reference']) is not str:
raise ValueError('Reference must be string or null')
rows.append({'id': rid, 'currency': row['currency'], 'cents': amount_cents(row['amount']),
'instant': instant(row['at']), 'reference': row['reference']})
return rows
def reconcile(value):
fields(value, ['source', 'target'])
left, right = dataset(value['source']), dataset(value['target'])
counts = [Counter(row['id'] for row in rows) for rows in [left, right]]
duplicates = {'source': sorted(k for k, n in counts[0].items if n > 1),
'target': sorted(k for k, n in counts[1].items if n > 1)}
maps = [{row['id']: row for row in rows if count[row['id']] == 1}
for rows, count in zip([left, right], counts)]
source_ids, target_ids = set(counts[0]), set(counts[1])
changed = []
for rid in sorted(set(maps[0]) & set(maps[1])):
differing = [k for k in ['currency', 'cents', 'instant', 'reference'] if maps[0][rid][k]!= maps[1][rid][k]]
if differing:
changed.append({'id': rid, 'fields': differing})
totals = []
for rows in [left, right]:
result = {}
for row in rows:
result[row['currency']] = result.get(row['currency'], 0) + row['cents']
totals.append(dict(sorted(result.items)))
missing, extra = sorted(source_ids-target_ids), sorted(target_ids-source_ids)
blockers = []
if value['source']['snapshot']!= value['target']['snapshot']:
blockers.append('different-declared-snapshots')
if not value['source']['complete'] or not value['target']['complete']:
blockers.append('incomplete-declared-scope')
if any(duplicates.values):
blockers.append('nonunique-keys')
if missing or extra or changed:
blockers.append('record-differences')
return {'equivalentUnderDeclaredContract': not blockers, 'blockers': blockers,
'missing': missing, 'unexpected': extra, 'changed': changed, 'duplicates': duplicates,
'rowCounts': [len(left), len(right)], 'totalsCentsByCurrency': totals,
'snapshotAuthenticityVerified': False, 'accessControlsVerified': False,
'productionCutoverApproved': False}
if __name__ == '__main__':
try:
result = reconcile(json.load(sys.stdin))
print(json.dumps(result, sort_keys=True))
except (ValueError, TypeError, OverflowError) as error:
print(json.dumps({'error': str(error)}))
sys.exit(2)
Two rows change from 10/20 to 11/19: count and total pass, but both keys differ.
Common pitfalls
Count as fidelity; hashing after destructive normalization; declared snapshot as proof; administrator access as consumer access.
Related topics: Ingestion and reprocessing · Modeling and storage · Pipeline observability
Compare compatible states and grains, keeping discrepancies and evidence limits visible.
Reference: Database migration concepts and principles · Current linked standard guide (document title v4.2); edition date unconfirmed (2026-09-30 inspection)