Test, Reconcile, Load-Test, Secure, Observe, Backfill, Recover, and Execute a Data Incident/DR Exercise

Learning outcomes

1. Problem frame: recovery plans that have never restored data are hypotheses

A capstone is not production-ready because it contains a “DR” section in a document. AtlasMart must show what happens when a derived historical aggregate is wrong, a metric silently drifts, an analyst attempts a bypass, or the primary local database loses fact rows. The exercise is intentionally destructive only inside atlasmart_ch30_lab.

2. Layered tests map to failure modes

3. Reconciliation is multi-dimensional, not row count only

The primary canonical tuple is (10 lines, 8 orders, 12 units, 820 revenue, 495 cost, 325 profit). It protects grain and additive controls simultaneously. History tests protect effective-dated meaning. Metric tests protect filters/time/unit semantics. Hashes protect deterministic row content for the local DR scope. No single check is sufficient on its own.

SELECT  COUNT(*)                              AS paid_lines,  COUNT(DISTINCT order_id)              AS orders,  SUM(qty)                              AS units,  SUM(revenue_usd)                      AS revenue_usd,  SUM(cost_usd)                         AS cost_usd,  SUM(revenue_usd - cost_usd)           AS profit_usdFROM fact_sales;-- (10, 8, 12, 820, 495, 325)

4. Backfill drill: repair the smallest derived historical scope

The current capstone creates agg_daily_sales from atomic facts. For 2026-09-20 the correct derived controls are (3 lines, 2 orders, 4 units, 185 revenue, 130 cost, 55 profit). The exercise corrupts only that aggregate row by adding 10 USD to cost, yielding 140 cost and 45 profit. It then deletes/rebuilds only 2026-09-20 from the atomic fact.

The capstone does not overwrite atomic facts to “match the aggregate.” Derived state is repaired from the governed source of truth. Rerunning the bounded backfill produces the same result because the partition is replaced deterministically.

5. Security drill: test bypass, not just masked output

The fixture asserts that bi_reader can read the semantic product and is denied direct fact_sales and customer PII. etl_service has fact-write scope but no semantic-consumer permission. This is a policy simulation because SQLite does not implement the managed role/row-policy features being taught.

assert access["bi_reader"]["semantic_sales"] == "read"assert access["bi_reader"]["fact_sales"] == "deny"assert access["bi_reader"]["customer_pii"] == "deny"assert access["etl_service"]["fact_sales"] == "write"assert access["etl_service"]["semantic_sales"] == "deny"

Production verification must use the target platform’s effective privileges, including inherited roles, service identities, exports, staging areas, backups, and alternate query paths.

6. Semantic incident drill: technically successful, analytically wrong

The deliberate semantic bug excludes the sales channel and returns 665 USD. Infrastructure telemetry can remain green, so a golden metric/reconciliation test must detect it. Lineage then identifies finance and executive consumers at risk. Communication must say revenue is understated on the certified product, not merely task semantic_model_42 failed.

08:12  pipeline technical tasks complete08:13  metric reconciliation: expected 820, observed 665 -> certification BLOCKED08:14  lineage blast radius: semantic metric -> finance mart -> executive/finance dashboards08:16  consumer notice: certified revenue is unavailable/incorrect; prior certified snapshot remains authoritative08:20  restore governed filter contract; rerun metric + downstream materialization08:23  result 820; certify after reconciliation and access checks08:25  close incident only after owner/postmortem action is recorded

7. DR drill: delete primary facts, restore backup, prove equivalence

The harness hashes the ordered fact_sales rows, closes the database, copies the known-good SQLite file, then deletes O1004/O1005 rows from the primary. The damaged primary drops to (7, 6, 8, 635, 365, 270). Restoring the backup returns (10, 8, 12, 820, 495, 325), and the pre/post SHA-256 of ordered fact rows is identical.

damaged primary: (7, 6, 8, 635, 365, 270)restored primary: (10, 8, 12, 820, 495, 325)fact-row SHA-256 equal before/after restore: True

This proves the local single-file fixture can be restored. It does not prove multi-region recovery, object-store/catalog restoration, external identity/config recovery, key management, or BI export recovery. Those remain explicit known limits.

8. Observability joins pipeline and data signals

Useful incident signals include source readiness, input manifest counts/bytes, rejects, task duration/retries, watermark lag, schema changes, metric reconciliation, freshness, certified version, consumer error rate, and lineage blast radius. Monitoring CPU alone cannot distinguish a late source from a semantic bug. Conversely, alerting on every natural volume variation creates noise.

9. Runbook: contain, preserve evidence, repair, replay, reconcile, communicate

1. Stop certification/publication for the affected scope; preserve raw evidence and run IDs.2. Classify source lateness, schema/contract failure, transform bug, semantic drift,   security exposure, or infrastructure failure.3. Use lineage to identify consumers and communicate DATA STATE.4. Repair the smallest causal component.5. Replay/backfill an idempotent bounded scope.6. Reconcile atomic controls, history, metrics, access paths, and freshness.7. Certify and communicate restoration.8. Record owner, due date, preventive action, and tested rollback/restore path.

10. Controlled failure: “we have backups”

Repair: execute restore and backfill drills in disposable environments, capture controls/hashes/timelines, define consumer communication, test the rollback route before cutover, and assign preventive actions to named owners with verification criteria.

11. Local lab: reproduce the failure/recovery evidence

assert baseline == (10, 8, 12, 820, 495, 325)assert quality_gate == (13, 10, 3)  # staged, accepted, quarantinedassert semantic_negative_case == (820, 665)assert backfill_before == (3, 2, 4, 185, 130, 55)assert backfill_bad    == (3, 2, 4, 185, 140, 45)assert backfill_after  == backfill_beforeassert damaged == (7, 6, 8, 635, 365, 270)assert restored == baselineassert pre_restore_hash == post_restore_hash

12. Verification checklist

• Tests protect business semantics, history, freshness, and security—not only SQL syntax.
• Severe defects block certification or are quarantined; waivers require owner/reason/expiry.
• Backfill is bounded, idempotent, and reconciled to atomic evidence.
• Security verification tests bypass paths and effective privileges.
• Incident communication describes consumer-visible data state.
• DR restoration was actually executed and reconciled; non-tested recovery domains are documented as limits.
• Postmortem prevention actions have accountable owners and verification criteria.

13. Production judgment and bridge to Lesson 5

AtlasMart has now demonstrated correctness under normal and failure conditions: quarantine, history, CDC restart, semantic drift detection, bounded backfill, access boundaries, workload evidence, and local restore. Lesson 5 packages those artifacts into a data-product handoff whose costs, limitations, ownership, lineage, runbooks, and evolution triggers remain visible after the demo ends.

Knowledge check

Authoritative references

• SQLite — Online Backup API for SQLite backup concepts; the capstone uses a closed-file copy for its disposable local drill.
• SQLite — Transactions.
• Google SRE Book — Managing Incidents for general incident-response discipline.
• OpenLineage documentation as an implementation reference for lineage metadata concepts; no OpenLineage service is required by the capstone.