Test metrics and SQL in CI

A query can be valid SQL and still calculate the wrong business meaning. Instrumentation tests protect the event shape; metric tests protect the answer.

Use both:

Do not make a live analytics workspace the only place a metric is tested. Live data changes while a test runs, which makes failures hard to reproduce.

Keep the metric in source control#

A metric package should contain:

analytics/
  weekly_active_accounts/
    definition.md
    query.sql
    fixtures.ndjson
    expected.json

definition.md records the unit, population, exclusions, time zone, window, deduplication key, owner, and version. query.sql uses stable output aliases. Fixtures contain only synthetic entities.

Review changes to all four files together. A query change without an updated definition is a warning; an expectation changed merely to make CI green deserves an explanation.

Design a golden fixture#

Use fixed Unix timestamps and deliberately boring identifiers:

{"timestamp":1782864000,"event":"account_activated","event_id":"evt_1","account_id":"acct_a","test_run_id":"metric_v3"}
{"timestamp":1782867600,"event":"report_published","event_id":"evt_2","account_id":"acct_a","test_run_id":"metric_v3"}
{"timestamp":1782950399,"event":"report_published","event_id":"evt_3","account_id":"acct_b","test_run_id":"metric_v3"}
{"timestamp":1782950400,"event":"report_published","event_id":"evt_3","account_id":"acct_b","test_run_id":"metric_v3"}

The last two rows intentionally share an event ID across a day boundary. They force the query to make its boundary and deduplication behavior explicit.

Every fixture should include:

• one included entity
• one excluded entity
• a duplicate delivery
• a timestamp exactly at each important boundary
• a missing optional field
• an old and current schema version when both remain queryable
• an empty-result period
• an amount in integer minor units when money is involved

Add late-arriving events, state changes, identity merges, or canceled outcomes when the definition depends on them.

Make the fixture query-selectable#

Give every row a unique test_run_id, then require it in the test query:

WITH deduplicated AS (
  SELECT
    JSONExtractString(json, 'account_id') AS account_id,
    JSONExtractString(json, 'event_id') AS event_id,
    argMax(
      JSONExtractString(json, 'event'),
      timestamp
    ) AS event,
    max(timestamp) AS event_at
  FROM ci_product_events
  WHERE JSONExtractString(json, 'test_run_id') = 'metric_v3'
  GROUP BY account_id, event_id
)
SELECT
  account_id,
  countIf(event = 'report_published') AS reports_published
FROM deduplicated
GROUP BY account_id
ORDER BY account_id

This filter prevents two concurrent CI jobs from reading each other’s rows. Generate a run ID per job for live smoke tests. For a checked-in golden fixture, use a stable ID so the expectation is readable.

Never put CI fixtures in a production collection.

Use a disposable CI boundary#

The strongest isolation is a dedicated non-production workspace and API key. Inside it, use collections such as:

ci_product_events
ci_billing_events
ci_account_state_events

GraphJSON is append-oriented and does not expose row mutation through the query surface. Plan cleanup through short retention and periodic collection lifecycle maintenance rather than assuming a test can delete individual rows.

Keep credentials in the CI secret store. Restrict who can use the workspace and rotate the key on the same schedule as other non-production credentials.

Load fixtures through the real ingestion contract#

The bulk endpoint accepts up to 50 rows in one collection:

async function loadFixture(
  collection: string,
  rows: Array<{ timestamp: number; body: Record<string, unknown> }>
) {
  const response = await fetch(
    "https://api.graphjson.com/api/bulk-log",
    {
      method: "POST",
      headers: { "Content-Type": "application/json" },
      body: JSON.stringify({
        api_key: process.env.GRAPHJSON_CI_API_KEY,
        collection,
        timestamps: rows.map((row) => row.timestamp),
        jsons: rows.map((row) => JSON.stringify(row.body))
      })
    }
  );

  if (!response.ok) {
    throw new Error(`fixture_ingest_failed:${response.status}`);
  }
}

Validate fixtures locally before sending them. A malformed fixture should fail before it consumes network time.

Ingestion and query visibility are not one atomic operation. Poll the exact run ID with bounded exponential backoff until all expected event IDs are visible, or fail after a documented timeout. Do not wait a fixed number of seconds and hope.

Execute SQL through the Data API#

For an integration-level assertion, send the source-controlled SQL with a Table result:

async function runMetric(sql: string) {
  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_CI_API_KEY,
        sql_query: sql,
        graph_type: "Table"
      })
    }
  );

  const body = await response.json();
  if (!response.ok || !Array.isArray(body.result)) {
    throw new Error(`metric_query_failed:${response.status}`);
  }
  return body.result;
}

The SQL API returns at most 2,000 rows. Golden expectations should be much smaller. A fixture that needs thousands of output rows is not explaining one metric clearly.

Compare canonical results#

Sort in SQL and compare parsed values, not a screenshot or formatted CSV:

[
  {"account_id":"acct_a","reports_published":1},
  {"account_id":"acct_b","reports_published":1}
]

Normalize only values whose representation is intentionally flexible. For example, compare a currency amount as an integer; do not coerce every numeric string and accidentally hide a contract regression.

For floating-point ratios, define a tolerance in the metric package:

absolute tolerance: 0.0001

Do not round the query merely to make the test pass unless rounded output is itself the product contract.

Separate deterministic and live tests#

Run fast deterministic tests on every change:

• fixture schema validation
• SQL formatting or static policy checks
• local calculation tests for transformations your application owns
• exact result-contract tests for report normalization

Run a smaller live smoke test against GraphJSON on merge or on a schedule:

1. generate a unique run ID
2. send three to five synthetic events
3. poll until visible
4. execute one representative query
5. compare the result
6. record the run ID and request diagnostics

A third-party outage should not block every local commit. A live path should still be exercised often enough to catch authentication, endpoint, and SQL compatibility changes.

Test failure behavior#

Include negative assertions:

• an unknown schema version is rejected by the producer
• an omitted required tenant key cannot enter a customer report
• a zero denominator returns the documented empty or null state
• a missing optional dimension maps to unknown, not an existing category
• duplicate event IDs do not inflate a deduplicated metric
• a query with no rows renders an empty state, not zero success
• a Data API error never becomes a valid-looking metric

Security tests should prove the CI or application report cannot escape its allowed account, environment, collection, or date boundary.

Promote queries deliberately#

A recommended review sequence:

1. change definition, SQL, fixtures, and expectation
2. run deterministic tests
3. run against the CI workspace
4. inspect the result table and query plan for material changes
5. compare the new definition with a recent production period
6. obtain approval from the metric owner
7. update saved queries, dashboards, alerts, and embeds
8. record the effective version and rollback path

The final two steps are operational changes, not just code deployment. Follow analytics asset change management before renaming result columns or replacing a shared query.

CI checklist#

• Metric definition and SQL are versioned together.
• Fixtures are synthetic, fixed-time, and selectable by run ID.
• Boundary, duplicate, missing, legacy, and empty cases are present.
• CI uses a non-production workspace and key.
• Live ingestion polling is bounded and diagnostic.
• Results are sorted and compared as typed data.
• Numeric tolerances are documented, narrow, and intentional.
• Negative authorization and failure cases are covered.
• A metric owner approves semantic changes.
• Saved assets and downstream consumers are included in rollout.

Continue with executable event contracts, environments and instrumentation testing, and metric governance.