Put a Contract Between OLTP and Analytics

A column can keep its name while changing its meaning. A service that once wrote paid after payment authorization may start writing it after settlement. The analytics pipeline stays green. Revenue moves. Neither schema validation nor replication lag explains why.

The boundary between OLTP and analytics therefore needs more than a transport. It needs a contract describing which business facts cross it, how those facts evolve, and how consumers learn that something has changed. Treat the contract as an engineering interface with owners and tests, not as a catalog entry somebody updates after an incident.

Start with one decision

Choose an analytical decision that matters, such as whether yesterday's paid orders met a sales target. Work backward from that decision. Define the grain as one order, the monetary unit as integer minor units plus currency, and the inclusion rule as settlement completed before the reporting cutoff. Specify whether refunds revise the original day or appear as adjustments on the refund day.

Those details prevent different teams from implementing plausible but incompatible metrics. A raw copy of the orders table cannot settle the question because the application table exists to support operational workflows, not necessarily financial interpretation.

For this illustrative contract, use these fields:

contract: settled_order.v1
key: [merchant_id, order_id]
amount: integer_minor_units
currency: ISO_4217_code
inclusion: settlement_confirmed
corrections: replace_order_state
removal: explicit_delete_event
owner: commerce_platform
consumer: revenue_reporting

Add a human-readable definition of each field. Define nullability, precision, allowed status transitions, deletion policy, and retention. State whether the interface is a change stream or a periodically replaced snapshot. A replacement snapshot needs a completeness marker; otherwise a partially loaded file can look like a sudden decline in sales.

Separate physical compatibility from semantic compatibility

An additive nullable column is often easier to deploy than a column rename, but neither label determines semantic safety. Changing the currency interpretation of an existing integer is structurally compatible and financially disastrous. Changing a status enumeration can break a consumer that assumes every unknown value means cancellation.

Require producers to supply before-and-after examples for every meaning change. Require consumers to test those examples against their aggregates. Review physical schema compatibility and business meaning as separate checks, with separate sign-offs when the downstream decision is consequential.

Do not assume replication performs schema governance. PostgreSQL's logical replication does not replicate DDL, and incompatible subscriber schemas can stop replication. The documentation recommends considering additive changes on the subscriber first. That is a deployment mechanism, not a guarantee that analytics still means the same thing. PostgreSQL logical replication restrictions

Make reconciliation part of the interface

For a selected cutoff, compare source and destination by merchant, currency, and settlement date. Check both order count and amount sum. Equal counts do not detect altered amounts; equal totals can hide compensating errors. Add sampled key-level comparisons and an explicit list of missing or unexpected order IDs.

The comparison must use a consistent boundary. Comparing a live source query with yesterday's destination snapshot creates apparent discrepancies even when every record was copied correctly. Record the source position or export identifier used by the pipeline, then compare data representing that boundary.

Define what happens on failure. A small delay may show a freshness warning. A failed currency mapping should quarantine the affected records and block the relevant financial aggregate. A quarantine queue without an owner and age limit is simply an alternative place to lose data.

Rehearse a version change

Suppose settlements become partial. The old contract promises one settled amount per order; the new workflow has several settlements. Do not quietly change the grain. Introduce a settlement-level interface with a settlement identifier and maintain a documented order-level projection while consumers migrate.

Backfill into an isolated destination, compare both versions over the same closed reporting interval, and review explained differences. Publish an overlap period and retirement date. Avoid running both versions into the same aggregate without a discriminator, which doubles the facts rather than increasing confidence.

Measure contract health with rejected records, unexplained reconciliation differences, consumer version adoption, and time to repair. Transport uptime remains useful, but it cannot substitute for these measures.

The contract is the product boundary

A useful contract is small enough to test and specific enough to reject an incorrect interpretation. Begin with one business decision, one owner, and one reconciliation query. Expand only when those checks operate reliably. The objective is not to freeze the application schema. It is to let operational systems evolve without silently rewriting the meaning of analytical results.