Designing a financial data API customers can keep in sync

A customer downloads a financial dataset and builds a local database. A week later, the provider corrects an old observation. The customer’s next request asks for dates after the last downloaded date, so it never sees the correction. Every request succeeds. The local copy stays wrong.

I start an API design with that customer’s job: load the data once, keep it current, and retain enough history to reproduce earlier research. That job gives a reason to choose identifiers and delivery rules before endpoint names.

A provider whose API doesn’t support that job tends to find out indirectly. Customers re-download the full history every night because they can’t trust an incremental update. Their local numbers disagree with the API, and nobody can say which is right. Or a customer asks why a result from last quarter changed, and the only answer is that the data was overwritten.

Take a fictional monthly activity index; its values and times are invented to illustrate the contract. The August 2026 value is published as 102.4 on September 3 and revised to 102.1 on September 10.

The initial record might look like this:

{
  "sequence": 36,
  "series_id": "activity-index",
  "observation_id": "obs_aug26",
  "revision_id": "rev_aug26_1",
  "revision_number": 1,
  "change_type": "initial_release",
  "period_start": "2026-08-01",
  "period_end": "2026-09-01",
  "value": "102.4",
  "missing_reason": null,
  "unit": "index_points",
  "published_at": "2026-09-03T12:30:00Z",
  "received_at": "2026-09-03T12:30:04Z",
  "available_at": "2026-09-03T12:31:10Z"
}

The September 10 revision keeps observation_id and gets a new revision_id. Its revision_number of 2 records that it supersedes the first version, whichever order the two reach the customer in. The customer can recognize a changed observation without overwriting the version used in earlier work. Renaming a series also leaves its stable identity intact; changing what the series measures may require a new identity and a catalog entry explaining the relationship.

The period runs from August 1 up to September 1, with the end excluded. A decimal string preserves the value’s representation across clients. index_points identifies the unit, while the series catalog supplies the base period, source, and adjustment convention needed to interpret it. published_at records the source’s release, received_at the provider’s acquisition, and available_at the time an entitled customer could first retrieve this version through the API. sequence is the record’s position in the dataset’s change stream, which the next section relies on.

The export and update cursor must describe the same boundary

For a large dataset, the customer will probably begin with an export. Suppose the provider selects a snapshot at 12:30:20 UTC on September 10 and finishes writing the file at 12:30:50. The revision becomes available through the API at 12:30:40, during the export.

If the provider waits until the file is finished to obtain a “start updates here” position, that position may fall after the revision. The revision is absent from the snapshot and already behind the update cursor. The customer can follow the documentation exactly and still miss it.

The export must therefore identify both the snapshot and the corresponding position in the change stream, and the two have to describe one consistent point in the dataset’s history. An export manifest can carry that pair along with coverage and file checksums:

{
  "export_id": "exp_5f0c9a7e2b14d6c3",
  "position": 36,
  "created_at": "2026-09-10T12:30:20Z",
  "coverage": {
    "observation_count": 32,
    "revision_count": 36,
    "period_start": "2024-01-01",
    "period_end": "2026-09-01"
  },
  "files": [
    {
      "name": "revisions.jsonl",
      "record_count": 36,
      "sha256": "e75e2d61e8ded1e54e210e7d854c394c9e880dcacafd2c6423ddfe5b81652469"
    }
  ]
}

The snapshot is every revision with a sequence at or below 36. The revision that arrived during the export has sequence 37, so it is never in the file, however long the file takes to write. After loading the file and checking its counts and checksum, the customer reads changes from the manifest’s position:

GET /v1/datasets/core-indicators/changes?after=36

The first event returned is the September 10 revision. The export and the change stream together contain every revision exactly once.

That change stream follows delivery order. It includes releases, revisions, and withdrawals, even when they refer to old observation periods. Each event identifies the observation and version it affects. Its change type distinguishes a correction to the provider’s normalization from a new estimate published by the source, so the customer can preserve both histories. Delivery order is not precedence, though: a release delayed by the provider’s validation can arrive after its revision, so the consumer decides which version is current by revision number, not by arrival.

The consumer applies a page of events and saves its next position in one local transaction. If it stops before commit, it can read the page again. Event identities allow repeated deliveries to be recognized. Positions also need a retention policy: a customer who has been offline longer than the retained history may have to load a fresh snapshot. An expired position must report that condition instead of skipping ahead.

How the provider produces that consistent pair depends on its storage, and it is one of the first things to establish. If the database can read a snapshot and report the change-stream position of that snapshot in one transaction, the export uses both directly. Change data capture tools rely on the same handoff for database replication: Debezium’s PostgreSQL connector reads the log position, takes its snapshot, and then streams changes from that position. If the database cannot report both together, the export can record a position taken before the snapshot begins. The customer then replays a few changes the files already contain, which event identities and revision numbers make harmless. The API documentation should say which approach the provider uses, because it tells the customer whether a repeated change after an export is expected.

A partial export needs similar care. Retrying a download should fetch the same retained files. Regenerating an export creates a new snapshot identity and update position; otherwise the customer could combine half an old export with half a new one.

The same issue appears when the customer loads the dataset through paginated queries. If the second page reads a newer state than the first, the combined result may never have existed at the provider. The continuation token should be opaque and validated, and refer to the original snapshot, filters, API version, and position. An expired snapshot requires a restart. The storage implementation must actually preserve that view; putting a snapshot ID in a token doesn’t create one.

Access has to be checked on the resumed request. A token issued yesterday cannot grant rights the customer lost today. Exports need equivalent protection, including any restrictions on historical depth or fields. An authenticated query endpoint is little help if its bulk files remain available at predictable, unprotected URLs.

A revision must preserve the answer to an earlier query

Once the local copy includes the revision, current research can use 102.1. Reproducing September 4 research still requires 102.4. The API can expose that choice explicitly:

GET /v1/observations?series_id=activity-index&period_start=2026-08-01&period_end=2026-09-01&available_as_of=2026-09-04T00:00:00Z

The query considers versions available through the API at or before the cutoff, then selects the one with the highest revision number for each observation. That answers what a customer of this API could have retrieved. Research keyed on release dates asks a slightly different question, and an API that records publication times can answer it too, with a published_as_of cutoff that selects among versions the source had published by then.

Withdrawals participate in that history as events too. A cutoff after a withdrawal returns the observation marked as withdrawn, without a value, so a customer who syncs by querying still learns of it; an earlier cutoff returns the original value. Deleting the original row would destroy the answer to that earlier query.

Each time field’s meaning is part of the contract. Changing available_at to mean source publication would alter these query results even if the JSON schema stayed identical, so a change like that needs an explicit migration.

The local database also needs to preserve missing values. A null observation with a reason is different from zero. A query matching no observations is different from a denied request. If an access error becomes an empty response, the customer’s synchronization job may record a successful run while leaving its data stale. Transient errors need enough information to retry within a limit and trace the failed request.

Write down expected results like these before implementation, so the tests don’t depend on whatever the code happens to return:

Then exercise the customer’s workflow against them. Interrupt a customer at the export/update handoff and compare its recovered database with the expected records. Deliver the revision while the export is being written. Expire a position, revoke access, and deliver a page twice. After all of those changes, the September 4 query must still return 102.4. That tests whether a customer can maintain the dataset using the interface the provider has promised.