Warehouse and BI handoff patterns

GraphJSON is optimized for interactive event analysis, dashboards, alerts, and embedded results. A warehouse or lakehouse is usually a better home for broad cross-company modeling, long-term raw history, regulatory reporting, and large transformation dependency graphs.

Use a deliberate handoff when a governed GraphJSON calculation must join a larger reporting workflow. Do not treat the Data API as an undocumented raw event export service.

Choose one of four patterns#

Avoid circular pipelines in which a result is exported, transformed, written back, and then becomes an undocumented input to its own definition.

Pattern 1: scheduled aggregate extract#

Use the Data API with a fixed, versioned SQL query:

const reports = {
  daily_active_accounts_v3: {
    sql_query: `
      SELECT
        toDate(toDateTime(timestamp)) AS day,
        uniqExact(JSONExtractString(json, 'account_id'))
          AS active_accounts
      FROM product_events
      WHERE
        JSONExtractString(json, 'event') = 'core_workflow_completed'
        AND timestamp >= toUnixTimestamp(toDateTime('2026-07-25 00:00:00'))
        AND timestamp < toUnixTimestamp(toDateTime('2026-07-26 00:00:00'))
      GROUP BY day
      ORDER BY day
    `,
    graph_type: "Table"
  }
};

Execute it from a trusted scheduler:

async function loadReport(report) {
  const response = await fetch(
    "https://api.graphjson.com/api/visualize/data",
    {
      method: "POST",
      headers: { "Content-Type": "application/json" },
      body: JSON.stringify({
        api_key: process.env.GRAPHJSON_API_KEY,
        ...report
      }),
      signal: AbortSignal.timeout(30_000)
    }
  );

  const body = await response.json();

  if (!response.ok) {
    throw new Error(body.error || `GraphJSON returned ${response.status}`);
  }

  return body.result;
}

Keep the report name, SQL, result schema, time zone, maturity delay, and owner in source control.

Design the output contract#

The warehouse table should not depend on a visualization's incidental labels.

Example:

dataset: product_daily_active_accounts
grain: UTC day
columns:
  day Date
  active_accounts UInt64
  source_window_end DateTime
  report_version String
  extracted_at DateTime
  extraction_run_id String
primary key:
  day + report_version
maturity:
  wait 24 hours after day end
restatement:
  rewrite the last 3 days
owner:
  Product Analytics

Validate types before loading. Reject unexpected columns rather than allowing a BI table to drift silently.

Use immutable extraction runs#

For every attempt, record:

extraction_run_id
report key and version
source window
started and completed time
row count
checksum or aggregate controls
status
error class

Load into staging, validate, then merge into the final table with an idempotent key. A retry must replace or reproduce the same logical snapshot rather than append another copy.

Freeze reporting windows#

Relative ranges such as "30 days ago" are convenient for live dashboards but weak extraction contracts. Resolve exact boundaries:

window_start = 2026-07-25T00:00:00Z
window_end   = 2026-07-26T00:00:00Z

Wait for the documented late-arrival period. Restate a small trailing interval when late events are expected, and record that policy.

Respect Data API boundaries#

The SQL result is capped at 2,000 rows. Design aggregate datasets that remain well below that limit.

The Data API is appropriate for:

• daily or hourly governed metrics
• compact segment tables
• bounded account-level operational lists
• versioned forecast or target outputs

It is not a documented path for:

• every raw event in a workspace
• unbounded row-level replication
• full backup or disaster recovery
• streaming change capture
• high-frequency BI queries with arbitrary SQL

For raw history, use a source-controlled durable archive and dual-write pipeline.

Pattern 2: customer-controlled dual write#

transactional outbox or durable event log
        ├─ GraphJSON consumer
        ├─ warehouse/lake consumer
        └─ raw archive consumer

Each consumer owns its checkpoint, retries, and reconciliation. One destination's success does not prove another is complete.

Use this pattern when:

• event-level history must outlive GraphJSON retention
• rebuilds must not depend on GraphJSON export
• the warehouse needs a different schema or enrichment
• contractual recovery requirements apply

Do not send a transaction to GraphJSON first and assume it can always become the warehouse recovery source later.

Pattern 3: publish warehouse results into GraphJSON#

When the warehouse calculates a stable aggregate needed for a GraphJSON dashboard, publish one versioned fact per grain:

{
  "event": "governed_metric_published",
  "event_id": "net_revenue:2026-07-25:v4",
  "metric_key": "net_revenue",
  "metric_version": 4,
  "window_start": "2026-07-25T00:00:00Z",
  "window_end": "2026-07-26T00:00:00Z",
  "value_minor": 4829100,
  "currency": "usd",
  "source_model": "finance.daily_net_revenue",
  "source_run_id": "run_20260726_04",
  "completeness": "final",
  "schema_version": 1
}

Use deterministic event identity and explicit restatement rules. Do not mix preliminary and final results without a status field.

Follow Derived events and scheduled rollups.

Pattern 4: direct BI request#

A small internal BI tool may call a fixed Data API report through a trusted server. Apply the same controls as embedded analytics:

• report allowlist
• no arbitrary browser SQL
• server-side API key
• fixed result schema
• private cache by authorization scope
• timeouts and failure states
• query-volume monitoring

Do not configure a desktop BI client with a shared workspace key if every user can reveal or reuse it.

Reconcile the handoff#

For every settled window compare:

GraphJSON result row count
staging row count
final table row count
sum or distinct-count controls
window bounds
report version
latest successful extraction time

For additive measures, compare totals. For non-additive distinct or percentile metrics, compare the published result itself and a checksum of the dimension keys; do not sum daily distinct counts into a monthly distinct count.

Handle corrections#

Choose one policy:

• overwrite the same report-version key
• append a new result version and select the latest
• publish correction deltas with explicit links

Do not silently mutate a final finance or contractual report. The warehouse governance process should decide whether a correction is allowed and how consumers see it.

GraphJSON remains analytical, not the accounting authority.

Secure the workflow#

• keep the API key in the scheduler or server secret manager
• restrict destination credentials separately
• avoid customer-level rows unless required
• redact query and response logging
• classify generated extracts
• expire temporary files
• isolate test and production
• keep report ownership current

If the dataset contains personal or customer-confidential data, apply the same deletion and access controls in both systems.

Handoff checklist#

• The warehouse and GraphJSON responsibilities are explicit.
• The selected pattern matches raw, aggregate, or reverse-publication needs.
• SQL, schema, grain, time zone, maturity, and owner are versioned.
• Extraction windows use exact half-open boundaries.
• Loads are staged, validated, and idempotent.
• The result stays within the Data API row boundary.
• Raw archival does not depend on the Data API.
• Reverse-published metrics carry source and calculation versions.
• Direct BI requests use fixed server-owned reports.
• Counts and metric controls reconcile on settled windows.
• Corrections have a documented restatement policy.
• Keys, extracts, logs, and destination access are protected.