A service’s tables are the worst interface it has, and table-level CDC is very good at publishing them. Every column name and every half-finished migration lands in the warehouse within seconds. The data team then builds on all of it as if someone had promised it would hold still.
Nobody promised. The service team owns those tables and should be free to reshape them on a Tuesday afternoon. When they do, the warehouse turns one column change into a nine-day expand and contract. An outbox is how you stop paying that every time.
The outbox pattern is usually explained to backend engineers as a fix for dual writes. I think data people have the better reason to ask for it, so this walkthrough is written from the warehouse side.
Two familiar ways to get loan data wrong
To make this concrete I’ll use an invented lender, Copperleaf Lending. The company and its numbers are mine, picked to be easy to follow.
Copperleaf’s first attempt publishes events from application code. The loan service commits the approval, then calls the broker. Those are two systems and two writes, with nothing tying them together. If the broker is unreachable in the moment after commit, the loan is approved in the database and the event never exists. Say that happens 17 times in a quarter. Seventeen loans are in the books and missing from the funnel report, and nobody can list which ones without diffing the database against the topic.
So the team swings the other way and points log-based CDC at the loans table. Nothing gets lost now, because the change stream is the database’s own log. The data team builds 7 warehouse models on the raw change events, most of them filtering on loans.status.
Then the service team splits status into lifecycle_state and underwriting_decision. It’s a sensible refactor. It’s also 7 broken models, found the next morning.
Each approach fixed what the other got wrong. Publishing from code gave a deliberate event with no delivery guarantee. Table CDC gave the guarantee and no deliberate event.
One extra table, same commit
The outbox gets both. The service writes its business rows and one event row to an outbox table inside the same transaction, so they commit or roll back together. A relay reads the outbox from the database log and publishes it.
Here’s the table, in PostgreSQL:
CREATE TABLE outbox (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
aggregate_type text NOT NULL, -- 'loan'
aggregate_id text NOT NULL, -- the loan id
event_type text NOT NULL, -- 'LoanApproved'
schema_version integer NOT NULL,
payload jsonb NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX outbox_created_at_idx ON outbox (created_at);
And the write. The INSERT sits between the same BEGIN and COMMIT as the business change, so the event and the loan row can’t disagree:
BEGIN;
UPDATE loans
SET lifecycle_state = 'active',
underwriting_decision = 'approved'
WHERE loan_id = 'L-48213';
INSERT INTO outbox (aggregate_type, aggregate_id, event_type, schema_version, payload)
VALUES (
'loan', 'L-48213', 'LoanApproved', 2,
jsonb_build_object(
'loan_id', 'L-48213',
'borrower_id', 'B-90177',
'product_code', 'AUTO-60',
'principal_cents', 2450000,
'currency', 'USD',
'apr_bps', 689,
'term_months', 60,
'decision_source', 'underwriter',
'approved_at', '2024-10-03T16:42:07Z'
)
);
COMMIT;
LoanApproved version 2 carries 9 fields, and none of them is status. That payload is what the service team is promising. The loans table isn’t part of the promise, so the next refactor of it touches no downstream model. If you’ve been reading about data contracts, this is one of the few places where the contract is enforced by where the code lives: the event is built in the service’s own repo, next to the write it describes, and it changes in the same pull request.
I’d keep payloads fat enough that the loader never has to call back for missing fields. I went into thin and fat payloads in an earlier post on event anti-patterns, and the same reasoning applies here.
Routing by aggregate, keying by id
The relay shouldn’t poll the table. Reading the log gets you commit order for free and adds no query load. Debezium ships an outbox event router for this, and a connector config looks roughly like the sketch below.
{
"name": "loans-outbox-relay",
"config": {
"connector.class": "io.debezium.connector.postgresql.PostgresConnector",
"plugin.name": "pgoutput",
"database.hostname": "loans-db.internal",
"database.port": "5432",
"database.user": "outbox_relay",
"database.password": "${file:/secrets/loans-db.properties:password}",
"database.dbname": "loans",
"topic.prefix": "copperleaf",
"slot.name": "loans_outbox",
"publication.autocreate.mode": "filtered",
"table.include.list": "public.outbox",
"tombstones.on.delete": "false",
"transforms": "outbox",
"transforms.outbox.type": "io.debezium.transforms.outbox.EventRouter",
"transforms.outbox.route.by.field": "aggregate_type",
"transforms.outbox.route.topic.replacement": "lending.events.${routedByValue}",
"transforms.outbox.table.field.event.id": "id",
"transforms.outbox.table.field.event.key": "aggregate_id",
"transforms.outbox.table.field.event.payload": "payload",
"transforms.outbox.table.expand.json.payload": "true",
"transforms.outbox.table.fields.additional.placement": "event_type:header:eventType,schema_version:header:schemaVersion"
}
}
Two settings in there carry the design.
Routing on aggregate_type sends every loan event to lending.events.loan. One topic per aggregate, not per event type, so LoanApproved and LoanFunded for the same loan stay in one ordered stream.
Keying on aggregate_id puts all events for one loan on one partition, in the order they committed. The loader can apply them without sorting.
Only public.outbox is in the include list. The connector can’t see loans at all, so nobody can quietly build an eighth model on raw rows.
Delivery is at least once. After a relay restart the loader will see some events twice, so it dedupes on the event id, which the router passes along as a message header.
How long a row stays in the outbox
The relay reads inserts from the write-ahead log, and it never looks at the table again. Once a row is committed it has done its job. The router skips delete events on the outbox, so cleaning up doesn’t publish anything.
My retention rule is short. A cleaner job runs every minute:
DELETE FROM outbox
WHERE created_at < now() - interval '1 minute';
That keeps under 2 minutes of rows in the table at any moment, enough to eyeball during an incident. Replay belongs to the topic, where retention is a broker setting and a reader can rewind.
Don’t alert on outbox row count. Alert on replication slot lag. If the relay is down, PostgreSQL keeps the log for that slot, by default until the disk fills, and the cleaner will happily keep the table empty the whole time.
The price is asking for every field
The service team now writes an event for each change the data team cares about. That’s real work, and a field nobody put in the payload isn’t in the warehouse. With table CDC you got every column without asking.
I think that’s the right default anyway. Asking for a field is a five-minute conversation with a version bump at the end of it. Finding out about a schema change from a broken model costs a morning, 7 times over in Copperleaf’s case.
Keep table-level CDC for what it’s good at: replicas, and pipelines over tables you own yourself.