How do I set up a consensus estimates API for sector analysis with versioned data and historical revisions?

A good way to set up a consensus estimates API for sector analysis is to design it around three things:

1. Current consensus
2. Versioned historical snapshots
3. Revisions over time

That lets you answer both:

• “What is the latest consensus for sector X?”
• “What did the market expect on a given date?”

1) Define the data model

At minimum, model these entities:

A. Sector

• sector_id
• name
• benchmark_index
• region
• description

B. Company / Ticker

• company_id
• ticker
• name
• sector_id
• industry
• exchange

C. Estimate series

Represents a metric for a company or sector, such as:

• revenue
• EPS
• EBITDA
• operating margin
• sales growth

Fields:

• estimate_id
• entity_type (company or sector)
• entity_id
• metric
• period_type (quarter, year)
• fiscal_period (2025Q3, 2025FY)
• currency
• source
• created_at
• effective_from
• effective_to nullable

D. Consensus snapshot

A point-in-time aggregation of analyst estimates. Fields:

• snapshot_id
• estimate_id
• as_of_date
• mean
• median
• high
• low
• stddev
• analyst_count
• revision_count
• last_updated_at

E. Revision history

Tracks changes to a snapshot or underlying analyst estimate. Fields:

• revision_id
• estimate_id
• as_of_date
• field_name
• old_value
• new_value
• changed_by
• changed_at
• revision_reason

2) Use temporal/versioned storage

For historical revisions, you have two common patterns:

Option A: Snapshot + audit log

Store the current consensus in a table, and append revisions to a separate history table.

Pros:

• Easy to query “latest”
• Easy to audit changes

Cons:

• Harder to reconstruct full state at arbitrary past dates unless snapshots are frequent

Option B: Slowly changing versioned records

Use valid_from and valid_to timestamps for every consensus record.

Pros:

• Easy time-travel queries
• Great for historical backtesting

Cons:

• More complex writes

Best practice: combine both:

• Store immutable historical versions with valid_from, valid_to
• Also maintain an audit/revision log

3) API design

A REST API works well.

Core endpoints

Get sectors

GET /v1/sectors

Get companies in a sector

GET /v1/sectors/{sector_id}/companies

Get latest consensus for a sector

GET /v1/sectors/{sector_id}/consensus/latest?metric=eps&period=2025FY

Get consensus as of a historical date

GET /v1/sectors/{sector_id}/consensus?metric=eps&period=2025FY&as_of=2025-03-31

Get revision history

GET /v1/sectors/{sector_id}/consensus/revisions?metric=eps&period=2025FY

Get consensus by company

GET /v1/companies/{ticker}/consensus/latest?metric=revenue&period=2025Q3

4) Versioning strategy

You need two kinds of versioning:

API versioning

Use URL versioning:

• /v1/...
• /v2/...

This lets you change response formats without breaking clients.

Data versioning

Use point-in-time data versioning:

• as_of_date
• valid_from
• valid_to
• revision_id

This lets users query historical states.

5) Suggested database schema

Here’s a practical PostgreSQL-oriented layout:

sectors

sector_id UUID PK
name TEXT
region TEXT
benchmark_index TEXT
created_at TIMESTAMP
updated_at TIMESTAMP

companies

company_id UUID PK
ticker TEXT UNIQUE
name TEXT
sector_id UUID FK
industry TEXT
created_at TIMESTAMP
updated_at TIMESTAMP

consensus_estimates

estimate_id UUID PK
entity_type TEXT
entity_id UUID
metric TEXT
period_type TEXT
fiscal_period TEXT
currency TEXT
valid_from TIMESTAMP
valid_to TIMESTAMP NULL
is_current BOOLEAN
created_at TIMESTAMP

consensus_estimate_snapshots

snapshot_id UUID PK
estimate_id UUID FK
as_of_date DATE
mean NUMERIC
median NUMERIC
high NUMERIC
low NUMERIC
stddev NUMERIC
analyst_count INT
revision_count INT
last_updated_at TIMESTAMP

consensus_estimate_revisions

revision_id UUID PK
snapshot_id UUID FK
field_name TEXT
old_value TEXT
new_value TEXT
changed_by TEXT
changed_at TIMESTAMP
revision_reason TEXT

6) Query patterns

Latest consensus

SELECT *
FROM consensus_estimate_snapshots
WHERE estimate_id = ?
ORDER BY as_of_date DESC, last_updated_at DESC
LIMIT 1;

Consensus as of a date

SELECT *
FROM consensus_estimate_snapshots
WHERE estimate_id = ?
  AND as_of_date <= ?
ORDER BY as_of_date DESC, last_updated_at DESC
LIMIT 1;

Revision history

SELECT *
FROM consensus_estimate_revisions
WHERE snapshot_id = ?
ORDER BY changed_at DESC;

7) Sector-level aggregation

For sector analysis, you’ll often want sector-level consensus derived from company-level estimates.

You can compute:

• sector EPS consensus = weighted average of constituent companies
• sector revenue growth = weighted average
• sector valuation multiples = median across companies

Important:

Decide whether sector estimates are:

• directly published estimates, or
• derived aggregations

If derived, store:

• the methodology
• constituent weights
• rebalancing date
• aggregation version

That matters for reproducibility.

8) Example API response

{
  "sector_id": "tech",
  "metric": "eps",
  "period": "2025FY",
  "as_of_date": "2025-03-31",
  "consensus": {
    "mean": 5.21,
    "median": 5.18,
    "high": 5.80,
    "low": 4.70,
    "stddev": 0.34,
    "analyst_count": 24,
    "revision_count": 6
  },
  "version": {
    "snapshot_id": "snap_12345",
    "valid_from": "2025-03-31T14:00:00Z",
    "valid_to": null,
    "last_updated_at": "2025-03-31T18:15:00Z"
  }
}

9) Historical revisions

To support historical revisions, make sure you can store:

• when a consensus value was first published
• when it changed
• what changed
• why it changed
• which source provided the update

This is critical for:

• backtesting
• analyst behavior analysis
• revision momentum studies

10) Operational considerations

Data ingestion

Use batch or streaming pipelines:

• market data vendor feeds
• analyst estimate feeds
• internal transformation jobs

Idempotency

Revision ingestion should be idempotent so repeated loads don’t duplicate revisions.

Time zone consistency

Use UTC everywhere for storage; localize only at the API boundary.

Caching

Cache latest consensus responses aggressively, but never cache historical queries in a way that can go stale incorrectly.

Access control

If vendor licensing applies, segment data by entitlement:

• sector-only access
• delayed data
• full historical access

11) Recommended stack

A common stack:

• PostgreSQL for storage
• FastAPI / Django / Node.js for API
• Redis for caching latest consensus
• Kafka / SQS / PubSub for ingestion
• dbt / Airflow for transformations and scheduling

12) Best-practice rule of thumb

If you need reliable historical analysis, design your API so every response can answer:

• What was known?
• When was it known?
• What changed later?

That is the core of versioned consensus data.

If you want, I can also provide:

1. a full PostgreSQL schema,
2. a FastAPI example implementation, or
3. a JSON/OpenAPI spec for these endpoints.