Start with a policy
Use the mini source in Cobalt 1. Target grain is one row per order; a larger version replaces a smaller version. Same version and identical payload is a no-op; same version and different payload fails. Source feed is immutable for the exercise. Retain deletion versions. The supporter project adds a complete runnable fixture, independent rebuild checks and failure injections.
Your task
Sketch a parameterised loader that validates a whole batch, owns a transaction, checks version conflicts, applies changes, reconciles and commits. Predict a second b2 run and a stale b1 run. Explain why max(order_day) cannot safely identify all new arrivals or corrected records. A lookback reduces some misses but does not prove coverage for unbounded lateness.
Reference SQL pattern
In SQLite 3.24+, a primary-key conflict can update only if the incoming version is larger. This statement is not a whole pipeline: validate payloads and equal-version conflicts first. An ignored equal version must not silently discard a different payload.
INSERT INTO current_orders
(order_id,version,order_day,amount_minor,status) VALUES (?,?,?,?,?)
ON CONFLICT(order_id) DO UPDATE SET
version=excluded.version,order_day=excluded.order_day,
amount_minor=excluded.amount_minor,status=excluded.status
WHERE excluded.version>current_orders.version;Worked solution
b2 replay stays at two completed orders and 15,000. b1 replay also stays unchanged because versions cannot decrease. Removing the o2 target row would lose its deletion version and allow stale b1 to resurrect it. Append-only loading would double-count changes. Current-state storage cannot audit conflicting payloads at every already-superseded version; keep an immutable source ledger if that guarantee is required.
Failure rehearsal
Create a copy of your scratch database and force a write error after o1 updates but before o4 inserts. Verify rollback leaves the entire target equal to its pre-batch snapshot. Run the repaired b2 again. A transaction protects this local batch, not cross-system exactly-once delivery. This reference design owns BEGIN/COMMIT; it must reject use inside a caller-owned open transaction.
Independent rebuild
Build a latest-version result from the complete immutable source, after checking same-version conflicts. Compare every key and field to your incremental result. Include cancelled and deleted rows in this comparison, then reconcile completed metrics separately. Reverse arrival order as an additional check. It should not change the greatest-version target when the source contract holds.
Mutation solution
An identical duplicate causes no target change; a different payload at the current equal version must fail before commit; an unknown batch must fail visibly; stale versions cannot resurrect deleted orders; an injected mid-batch exception must roll back all target writes. Record expected and observed behavior rather than a blanket “pipeline works” statement.
Interview defense
Explain why replay-safe local upsert is weaker than a distributed exactly-once guarantee. Identify what would change for physical deletes, non-monotonic versions, competing writers, schema evolution or late historical corrections. Score 0–2 for contract, idempotence, tombstones, transaction boundary and independent comparison.
Authoritative reference
SQLite authors, UPSERT. Review excluded values and conditional DO UPDATE behavior. Next: Cobalt 3, business-valid historical joins.
Try the activity before reading its reference. Keep your sources, commands and assumptions with your work. Companion activities are unsaved self-practice and do not award a certificate or change lesson progress.