CDC replication platform with four operating modes and its own monitoring stack (City of Lausanne)

A city wanted its analysts to stop querying the systems that run the city. That is a one-line brief and a year of interesting problems.

The brief

Lausanne's operational systems — utility networks, worksite coordination, geographic data in PostGIS — were also the systems people ran reports against. Analytics competed with operations for the same database, and any new dashboard was a negotiation about load. They wanted the data mirrored somewhere else, in near real time, on open-source technology their own teams could operate. No managed CDC service, no vendor lock-in.

Why log-based capture, not polling

The obvious approach is to poll: query each table for rows changed since last time, copy them over. It fails in three ways that only show up in production. Polling misses deletes entirely — a row that disappears is indistinguishable from a row that was never there. It drifts under write load, because the window you query and the window that actually changed are never quite the same. And it puts read pressure straight back onto the operational database, which is the exact problem you were hired to remove.

Debezium reads the PostgreSQL write-ahead log instead. Every insert, update and delete is already there, in commit order, because the database needs it for its own recovery. Capture becomes a matter of reading a log rather than interrogating a system.

flowchart LR
    SRC[(Operational<br/>PostgreSQL + PostGIS)] -- WAL --> DBZ[Debezium<br/>Kafka Connect]
    BAK[(Backup<br/>URL or MinIO S3)] -. degraded path .-> STG[(Staging<br/>replication DB)]
    STG -. WAL .-> DBZ
    DBZ --> K[(Kafka)]
    K --> SINK[Sink<br/>data · indexes · sequences · views]
    SINK --> TGT[(Analytics target)]
    SRC --> DIFF{{db-compare<br/>column-by-column diff}}
    TGT --> DIFF

The part nobody warns you about

Debezium replicates rows. An analytics target that anyone will actually use needs considerably more than rows: the indexes that make it queryable, the sequences that keep identity columns sane, and the views that analysts have been writing against for years and have no intention of rewriting.

None of that rides on the change stream. So the pipeline grew separate replication passes — data, indexes, sequences, views — each with its own run.

Views turned out to be the awkward one. A view can be built on other views, several levels deep, and CREATE OR REPLACE fails if what it depends on does not exist yet. Replaying them in alphabetical order, or in the order the catalogue happens to return them, breaks on the first nested dependency. So the runner reads the dependency graph out of the database, ranks the views topologically, and replays them in an order that is guaranteed to resolve — with an explicit exclusion list for the ones that should never be mirrored.

That ranking step is perhaps two hundred lines. It is also the difference between "the data is over there" and "your existing reports work against the copy".

Four modes, because a pipeline that stops is not a platform

Live capture assumes you can reach the WAL. Sometimes you cannot: the source is being migrated, credentials lapse, a replication slot has to be dropped. A CDC pipeline whose only answer to that is to stop is not something a city can run on.

The runner therefore has four modes, declared in a single JSON configuration rather than hand-wired per environment:

The degraded path is not a fallback bolted on afterwards. It is the same pipeline pointed at a different origin, which is why it keeps working when you need it.

Nobody adopts a mirror they cannot audit

The technical work was the streaming. The adoption work was proving the two sides matched.

So I built a comparison tool: extract a summary of both databases with the same SQL, then diff them table by table and column by column, reporting how many values differ per column. Not "the replication is healthy" — an actual list of where the two sides disagree, if they disagree.

This did more for trust than the architecture did. A team will not move its reporting onto a copy on the strength of an assurance. It will move once it can run a command and read the answer.

Operating it

• Custom Prometheus metrics for the CDC system, packaged as an image published to the artifact registry by CI, so monitoring shipped the same way the code did.
• A local development stack with test doubles — a fake backup endpoint, a fake target database, staging replication databases — so a change could be exercised end to end without touching anything real.
• A growth simulator for one of the network domains, to watch how the pipeline behaved as volume climbed rather than discovering it in production.
• Multiple environments, each Kafka cluster with its own declared properties, plus a development cluster.

Two things did not land. A WAL-size tracker — logical replication slots quietly accumulate WAL when a consumer falls behind, and a full disk on the source database is the worst possible failure — was started and abandoned. A module to manage alerts downstream of Prometheus stayed in development. Both were the right ideas; neither reached production before the engagement ended.

What I would defend in an interview

That the diff tool was worth as much as the streaming architecture. It is tempting to judge this kind of work by the sophistication of its capture path. But a replication platform is only useful once people trust it enough to move their reports, and trust is not a property of the architecture — it is a property of what you can demonstrate on demand.

Stack: Apache Kafka · Debezium · Kafka Connect · PostgreSQL/PostGIS · Docker · Python · MinIO · Prometheus · Grafana · GitLab CI/CD · JFrog Artifactory