Data Engineering

Decoupled
by design

Two halves of the same job can have completely different limits. Building the data needs processing power; saving it is limited by how fast a single database will accept writes. Join them together and the slower half sets the pace for everything.

System: Passenger profiling platform
Scale: 535M rows, written continuously
Role: Sole architect and engineer
Stack: Python / Parquet / S3 / PostgreSQL

The challenge

Profile generation and database writes were bound together, so the fleet spent most of its time waiting on write locks instead of working.

The approach

Put a durable buffer between them and make the handoff a single atomic write, so neither side has to trust the other to be available.

The result

Compute and storage now scale independently, a failure is contained to one file, and the buffer became the foundation of the analytical layer.

01GenerateBuilding a passenger profile is arithmetic. It needs processing power and nothing else, thousands can be built at the same time, and adding machines simply makes it finish sooner.Python / Celery / SQLAlchemy 02Part filesThe finished profiles are written out as plain files in cloud storage, split into groups small enough to be handled one at a time. Nothing has touched the database yet, so nothing can be held up by it.PyArrow / Parquet / zstd-3 03ManifestA small index file, written last, listing everything the batch just produced. Cloud storage guarantees that a single file either appears complete or does not appear at all, so publishing this one file is what declares the batch ready. If anything went wrong earlier the manifest never appears, and the leftover files are simply ignored.JSON manifest / single-object PUT atomicity 04ClaimBefore touching a batch, a worker takes ownership of it. Two workers waking at the same moment cannot both succeed, and if one dies halfway through, its claim expires so another can pick the batch up. Retrying is always safe.PostgreSQL / INSERT ... ON CONFLICT lease 05Merge and receiptThe data is written into the database and the note recording that this batch is done is saved in the same single operation. Either both happen or neither does, which closes the gap where work is finished but unrecorded — the gap duplicates normally slip through.COPY (binary) / MERGE / UNLOGGED staging

Fig. 01 — The manifest is the only atomic write in the handoff

Hover any step to read what it doesTap any step to read what it does

Decoupledby design

The challenge

The approach

The result

Decoupled
by design