Reconcile analytics with source systems

A successful ingestion response proves that one request was accepted. It does not prove that every source fact was produced, mapped correctly, delivered once, retained, and interpreted with the intended definition.

Reconciliation compares an analytical result with an independent authoritative source over the same settled population:

source-of-truth facts ── calculate A ──┐
                                      ├── compare → certify or investigate
GraphJSON events ────── calculate B ──┘

Use it for revenue, billing, usage, entitlement, customer-facing reports, critical funnels, and any metric that triggers a material decision.

Define the contract before the query#

Record:

“Compare revenue daily” is not enough. State which date, payment state, currency treatment, exclusions, and refund policy both sides use.

Compare only settled windows#

Live systems are rarely complete at the same instant. Define:

comparison_end
  = min(source_complete_through, graphjson_complete_through)
    - settling_delay

Use half-open intervals:

window_start <= occurred_at < window_end

Do not compare database creation time with GraphJSON delivery time. Both sides must use the same business occurrence time.

Publish these boundaries with every run:

{
  "window_start": 1784966400,
  "window_end": 1785052800,
  "source_complete_through": 1785070800,
  "graphjson_complete_through": 1785069900,
  "settling_delay_seconds": 21600
}

If completeness is unknown, the run is not certified merely because totals happen to match.

Reconcile in layers#

Begin with cheap structural checks, then move toward business meaning:

A matching final total can hide two offsetting errors. Compare the components and segments that could cancel each other.

Use stable source identity#

Carry a stable source ID:

{
  "event": "payment_succeeded",
  "event_id": "stripe:evt_123",
  "source_record_id": "ledger_payment_91",
  "account_id": "acct_7",
  "amount_minor": 4900,
  "currency": "usd"
}

Do not log a database log position, internal storage path, or complete source row merely for reconciliation. Keep sensitive manifests in a restricted control store.

For exact event-level checks, compare a hash or sorted list of safe stable IDs outside GraphJSON. Aggregate comparisons alone cannot identify the missing record.

Calculate the GraphJSON side reproducibly#

Example settled daily payment control:

WITH deduplicated AS (
  SELECT
    JSONExtractString(json, 'event_id') AS event_id,
    argMax(JSONExtractString(json, 'currency'), timestamp) AS currency,
    argMax(JSONExtractInt(json, 'amount_minor'), timestamp) AS amount_minor,
    min(timestamp) AS occurred_at
  FROM billing_events
  WHERE
    JSONExtractString(json, 'event') = 'payment_succeeded'
    AND timestamp >= 1784966400
    AND timestamp < 1785052800
  GROUP BY event_id
)
SELECT
  currency,
  count() AS event_count,
  sum(amount_minor) AS amount_minor,
  min(occurred_at) AS first_event_at,
  max(occurred_at) AS last_event_at
FROM deduplicated
GROUP BY currency
ORDER BY currency

Pin the SQL and definition version used by the reconciliation worker. Do not copy a dashboard number by hand and call it a control.

Calculate the source side independently. If both results come from the same transformation or staging table, they share the same failure mode.

Store a compact control result#

The authoritative reconciliation job should keep its full run state outside GraphJSON. It can also emit a compact result for dashboards:

{
  "event": "analytics_reconciliation_completed",
  "control_id": "daily_payments_usd",
  "run_id": "recon_01J...",
  "window_start": 1784966400,
  "window_end": 1785052800,
  "source_count": 11842,
  "graphjson_count": 11840,
  "source_value_minor": 92840122,
  "graphjson_value_minor": 92835222,
  "count_difference": -2,
  "value_difference_minor": -4900,
  "relative_count_difference": -0.000169,
  "status": "failed",
  "definition_version": 4,
  "completed_at": 1785074400
}

This record supports a dashboard or a native threshold alert on a numeric difference. GraphJSON does not coordinate the source comparison or certify the metric automatically.

Choose tolerances intentionally#

Possible policies:

money posted to a ledger: exact after settlement
provider webhook count: exact distinct provider IDs
high-volume disposable diagnostics: documented percentage
recent product events: small lateness tolerance, then exact after finalization
sampled telemetry: compare weighted estimates and sampling metadata

Tolerance is not a way to hide unexplained loss. Record:

• absolute tolerance
• relative tolerance
• minimum denominator
• settling delay
• excluded states
• expiration date for temporary exceptions

For low counts, an absolute threshold is usually clearer than a percentage. A one-row difference out of two is 50%; one row out of two million may still be a critical missing payment.

Separate missing, duplicate, and transformed differences#

When the control fails, classify:

source fact absent from GraphJSON
GraphJSON event absent from source population
stable ID repeated
same ID with different value
event in wrong time window
event routed to wrong collection
schema version mapped differently
definition or eligibility mismatch

This classification determines the fix. Replaying does not correct a bad definition, and changing SQL does not restore a missing source event.

Use a drill-down process:

1. compare by day or hour
2. compare by source partition or producer
3. compare by schema and mapping version
4. compare by bounded business segment
5. identify safe missing or extra IDs
6. sample the exact source and destination records

Avoid grouping first by account ID on a customer-facing dashboard. Use a restricted investigation surface.

Handle late and corrected facts#

A late source record can change a previously matched window. Choose whether to:

• keep the window provisional for a fixed delay
• reopen recent controls automatically
• append a correction and revoke certification
• preserve the original published value and show a restatement

Do not silently change a certified financial or external metric. Use Derived events and scheduled rollups for restatement identity and lineage.

Certify with states, not a boolean#

Recommended states:

Show the window, completion time, definition version, and denominator beside the state. A green badge with no freshness is not evidence.

Certification should apply to a specific control and interval:

daily_payments_usd v4
2026-07-25 UTC
passed at 2026-07-26 06:00 UTC

It is not a permanent claim that an entire dashboard is correct.

Respond to a failed control#

1. stop or label affected external publication when required
2. preserve source and destination evidence
3. determine the last passed settled interval
4. classify missing, duplicate, time, contract, or definition mismatch
5. contain the responsible producer or transform
6. repair through a versioned replay or query change
7. rerun structural and business controls
8. migrate downstream assets if meaning changed
9. document the affected intervals and consumers

Do not delete suspicious analytical rows before preserving their stable IDs, source positions, and downstream impact.

Follow Bad-data containment, correction, and replay for the recovery workflow.

Operate a reconciliation dashboard#

Include:

• latest state by control
• last passed window
• source and GraphJSON completeness watermarks
• absolute and relative differences
• run lateness
• repeated failures
• controls missing an owner
• definition versions in transition

Alert on both failed and stale controls. A job that stops running must not look like continuous success.

Keep urgent paging in a monitoring system with acknowledgment and escalation when the business consequence requires it. Native GraphJSON alerts have the limits documented in Create and operate alerts.

Launch checklist#

• Every critical control names an independent authority.
• Both sides use the same grain, time, currency, and eligibility.
• Comparison windows are settled and half-open.
• Stable IDs distinguish missing rows from duplicates.
• Structural checks precede final metric comparison.
• Tolerances have units, owners, and rationale.
• Full run state survives outside GraphJSON.
• Failed, stale, and revoked states are visible.
• A response procedure protects affected consumers.
• Reconciliation SQL and definitions are version controlled.