Derived events and scheduled rollups

Some analytics answers are expensive or awkward to derive from raw events on every page load:

• daily account-health features
• five-minute service summaries
• month-end recurring-revenue state
• message-campaign delivery summaries
• public metrics with a controlled disclosure boundary

GraphJSON does not currently run scheduled transformations or maintain materialized views for you. Compute the result in an application worker, orchestrator, or warehouse, then log a compact derived event into a separate collection.

authoritative facts
      ↓
versioned calculation on a settled window
      ↓
derived event or snapshot
      ↓
GraphJSON dashboard, embed, or supported alert

Keep raw facts and derived results distinguishable. A derived event is a published calculation with lineage, not a replacement for the source of truth.

Decide whether to derive#

Do not precompute every metric. Each derived collection introduces a schedule, version, recovery path, and risk that two definitions drift.

Derive when at least one is true:

• the calculation has many consumers
• source joins or scans are predictably expensive
• a dashboard needs a compact, stable result
• an alert needs a supported Single Line visualization
• a customer-facing surface needs a narrow data contract
• the calculation must freeze a completeness boundary

Choose the correct grain#

State the row grain before writing code:

account_id × UTC calendar day × metric_version
service × five-minute window × evaluator_version
campaign_id × delivery date × channel × definition_version

One event must represent exactly one grain. Avoid a payload whose fields mix daily, monthly, per-account, and global totals.

For a daily account summary:

{
  "event": "account_activity_rollup",
  "rollup_id": "account_activity:acct_7:2026-07-25:v3",
  "account_id": "acct_7",
  "window_start": 1784966400,
  "window_end": 1785052800,
  "data_complete_through": 1785056400,
  "definition_version": 3,
  "source_collections": ["product_events"],
  "active_users": 12,
  "completed_workflows": 47,
  "error_rate": 0.013,
  "computed_at": 1785060000
}

Send the GraphJSON request timestamp as the documented analytical time: usually window_end for a completed rollup or snapshot_at for a stock. Do not let worker execution time silently become the metric date.

Use half-open windows#

Define every calculation as:

window_start <= event_time < window_end

Half-open windows compose without double-counting the boundary. Use an IANA time zone when the business definition is local, and record it with the metric contract.

Distinguish:

• window_end: the end of the period measured
• data_complete_through: source time believed settled
• computed_at: when the job produced the result

These times answer different questions. A job can run at 02:00 for a day that ended at midnight and wait until 01:30 for late data.

Wait for a completeness watermark#

A timer does not make a window complete. Define the watermark from source behavior:

eligible window end
  = min(source checkpoints) - allowed lateness

For several inputs, the least complete source controls the result. If billing is complete through 01:00 but product events only through 00:40, a join that needs both is complete only through 00:40.

Publish provisional results only if consumers can identify them:

{
  "status": "provisional",
  "data_complete_through": 1785051000
}

Do not show provisional and final values as separate observations in the same sum. Select the latest version of the same rollup identity.

Create deterministic identities#

Build a stable logical ID from:

metric + grain keys + window + definition version

Examples:

daily_active_accounts:2026-07-25:v2
account_health:acct_7:2026-07-25:v3
api_availability:reports:2026-07-25T09:00Z:5m:v4

Retries for the same calculation use the same ID. A changed definition uses a new version. Keep run IDs separate:

{
  "rollup_id": "daily_active_accounts:2026-07-25:v2",
  "run_id": "run_01J...",
  "attempt": 2
}

Because GraphJSON ingestion does not enforce event-ID uniqueness, select the latest accepted result for a logical rollup:

SELECT
  toDateTime(JSONExtractInt(json, 'window_end')) AS window_end,
  argMax(
    JSONExtractInt(json, 'value'),
    tuple(
      JSONExtractInt(json, 'computed_at'),
      JSONExtractString(json, 'run_id')
    )
  ) AS value
FROM metric_rollups
WHERE
  JSONExtractString(json, 'event') = 'metric_rollup'
  AND JSONExtractString(json, 'metric') = 'daily_active_accounts'
  AND JSONExtractInt(json, 'definition_version') = 2
GROUP BY window_end
ORDER BY window_end

Use a source-controlled query or report contract so every consumer applies the same selection rule.

Keep lineage with the result#

Every important rollup should identify:

For a financial or externally published value, keep a fuller run manifest in the orchestrator or governed storage:

{
  "run_id": "run_01J...",
  "definition_commit": "7fd32d1",
  "source_snapshot": "billing-2026-07-25T02:00Z",
  "rows_read": 194032,
  "rows_written": 1842,
  "started_at": 1785059400,
  "completed_at": 1785060000
}

GraphJSON can display compact lineage fields, but it is not the authoritative job-control database.

Separate flows from stocks#

Flows accumulate during a period:

orders placed
payments collected
messages delivered
workflows completed

Stocks describe state at a point:

active subscriptions
open seats
inventory on hand
current recurring revenue
queue backlog

Use a window rollup for flows and a snapshot for stocks. Never sum daily subscription or inventory snapshots across a month.

The distinction is explained in Event-time state and reference data.

Handle late data and corrections#

Choose a policy for events that arrive after finalization:

For a correction:

{
  "event": "metric_rollup_corrected",
  "rollup_id": "net_revenue:2026-07-25:usd:v4",
  "supersedes_run_id": "run_01J...",
  "run_id": "run_01K...",
  "previous_value_minor": 812400,
  "value_minor": 807900,
  "reason": "late_refund",
  "computed_at": 1785146400
}

Do not overwrite history invisibly in a public, financial, or contractual report. Record the correction reason and how consumers select the current value.

Version definition changes#

Increment the definition version when:

• eligibility changes
• a source collection changes
• time zone or window behavior changes
• identity grain changes
• currency, duration, or rate units change
• late-data or deduplication policy changes

Run old and new definitions in parallel over settled windows. Compare by segment, not only grand total, then migrate dashboards and embeds through the analytics asset change process.

Do not recompute older periods under a new definition unless the result is labeled consistently and stakeholders understand that published history will move.

Avoid double counting#

Keep derived records in a dedicated collection such as:

metric_rollups
account_snapshots
public_metric_snapshots

Do not use a union of product_events and metric_rollups unless the query explicitly selects one representation for each interval. A raw event and its rollup represent the same underlying activity at different grains.

Name units:

request_count
duration_ms_p95
revenue_minor
conversion_rate

A field called value is acceptable only when metric, unit, and definition version travel with it.

Operate the scheduled job#

Monitor:

• last successful window
• current source watermark
• schedule delay
• calculation duration
• rows read and written
• provisional windows
• repeated logical IDs
• rejected GraphJSON deliveries
• reconciliation difference

Emit an independent rollup_job_completed operational event after the job finishes. A missing completion event is not a successful zero-value window.

For urgent service reliability, keep authoritative scheduling and paging outside GraphJSON so the measured system is not its only monitor.

Validate before consumers switch#

For at least one settled range:

1. compute the source result independently
2. compare each grain key and value
3. verify the newest-run selection rule
4. simulate a retry
5. introduce one late source event
6. exercise the correction or reopen policy
7. verify zero and missing windows separately
8. test a definition-version cutover
9. inspect every downstream dashboard and embed

Use Metric and SQL tests in CI for fixed fixtures and exact expectations.

Launch checklist#

• The calculation grain and units are explicit.
• Windows are half-open and use a documented time zone.
• Source completeness, window end, and compute time are separate.
• Logical IDs are deterministic across retries.
• Consumers select one result per logical ID.
• Raw facts and derived records use separate collections.
• Late data and restatement have an owner and policy.
• Definition changes create a visible version.
• Job manifests and recovery state live outside GraphJSON.
• Critical results reconcile against their authoritative sources.