Design an interface between systems
Data must cross a boundary: a partner's nightly files, an API another team calls, a sync from a SaaS tool into the warehouse. Unlike inside one system, the two sides cannot be changed at the same moment, and the producer may be gone by the time the data is read. The question shifts from "what is fastest?" to "what can both sides agree on?". How do you design the interface?
You will end with the schema's home, a failure contract, a resumable position, a written agreement on meaning, and a deprecation plan.
Work through these in order.
Work through it
Classify the boundary
Two things decide which tools apply: the time gap between writing and reading, and whether the reader can talk back to the writer. Unbounded gap, no channel back: a file format. Short gap, a full conversation: an access protocol (SQL, REST, RPC). An indefinite relationship, resumed again and again: an integration pattern (connectors, syncs, file drops).
Decide where the schema lives
Nowhere (readers infer it, and pay for parsing and guessing on every read); in the bytes (each file carries it, which sets a minimum sensible file size); or in a registry (tiny messages, but the registry is now on the critical path, and losing it makes the data undecodable). Files that must outlive the registry should carry their own.
Decide what happens when a transfer dies mid-way
A sender whose reply is lost cannot know whether the action happened, so make every operation safe to receive twice (idempotency keys, set rather than add). Retry by error class: retry throttling and unavailable errors with backoff; never retry a bad request. For file drops, write under a temporary name, rename atomically, and write a manifest last as the commit: file count, row count and totals.
Give the relationship a position it can resume from
A sync can resume only from a position that is total (any two can be compared) and only increases. A log offset qualifies; a timestamp does not, if writes arrive out of order or share a millisecond. For APIs, paginate by a unique, ordered key (keyset), never by offset, which silently skips or repeats rows under concurrent writes.
Write down what the data means
Both sides can parse correctly and still disagree. Agree in writing on: character encoding; decimal versus float; null versus empty versus absent; timestamps and time zones; integer width; and sort order for text. A "not null" check catches none of these.
Detect absence with a clock
Every failure produces a signal except a file or batch that never arrives. Set an expected time, and alert when it has passed.
Plan how the interface will change
Changing a public interface costs the time both versions run × the number of callers: 400 integrations over a 12-month sunset is 4,800 integration-months. Agree a compatibility rule and a sunset period before the first change.
Name who pays
A boundary gives work to the party with less power to refuse. Say which side pays each cost above (parsing, retries, reconciliation, migration), and whether that side agreed to it.