06 / UNDER THE HOOD
Write Many, Merge on Read
Our daily pipeline runs in Dagster. It computes values for each site, each day, so most assets are partitioned by site and date. One partition is one site-day.
The new version has no PostgreSQL in it. Nothing is updated in place. Every run appends what it computed, and the reader works out which version is the current one.
Write conflicts
In the old pipeline, several steps wrote into the same Postgres tables. A daily overview table had one wide row per site-day, and different steps upserted different columns of it.
Worse, two steps wrote the same value. The performance step and the loss step both wrote a curtailment value, into the same two tables. Whichever ran last won. To make the result predictable, we added an ordering edge in Dagster so the performance write always ran before the loss write. Then another edge so the reporting step ran after both.
Every new step that touched those tables needed more edges. Get one wrong and two runs write the same row in the wrong order, and nothing fails. The number is just from the wrong step. A rerun or a backfill has the same problem: it overwrites the row, and the old value is gone.
Two kinds of merge on read
“Merge on read” already means something in Apache Iceberg, and it’s worth getting straight first.
Files in object storage can’t be edited. So when you UPDATE or DELETE rows in an Iceberg table, the engine has two choices, which Dremio explains well in copy-on-write vs merge-on-read:
- Copy-on-write rewrites every data file that has a changed row. Writes are expensive, reads stay clean.
- Merge-on-read leaves the data files alone and writes small delete files next to them. Writes are cheap, and every reader merges the deletes in until compaction rewrites the files.
Iceberg lets you pick per operation with table properties like write.update.mode. Either way, there’s still an update, and an update is where our conflict lived.
We took the idea one step further: don’t update at all. Every write is an append. A new value for a site-day is a new row with a newer timestamp, not a change to the old row. The merge happens in the query, and the “delete” is just a newer row that hides the older one.
Streams, pipelines and sinks
The write side is Basin Pipelines (formerly Cloudflare Pipelines; Cloudflare renamed its data platform to Basin). It has three parts:
- A stream is a durable, buffered queue. You send it JSON events over HTTP or from a Worker binding.
- A pipeline is SQL that reads from a stream and can filter, transform and validate events as they come in.
- A sink is where the pipeline writes: an Iceberg table in Basin Catalog, or Parquet and JSON files in R2. Cloudflare promises exactly-once delivery to it.
So instead of an INSERT ... ON CONFLICT DO UPDATE, a Dagster step does a POST:
# Simplified from our Basin resource
await client.post(
stream_url,
headers={"Authorization": f"Bearer {token}", "Content-Type": "application/json"},
content=json.dumps(events),
)
Each event carries where it came from: a snapshot_id made from the Dagster run id and the partition key, the run_id, the partition_key, the site, the date, computed_at, and the computed row as payload_json with a payload_hash. The event_id is a hash of the snapshot id, the event type and the row’s identity. Send the same event twice and it has the same id, so a retry that lands twice can be spotted and dropped.
A step sends its data events first, split so no request goes over 4.5 MB. Then it sends one last run event with the batch_count. A snapshot only counts once its run event has arrived. If a step dies halfway, its half-sent snapshot has no run event, and readers skip it.
A failed POST retries on a 429 or a 5xx, twice, with a short backoff. If it still fails, the step logs it and carries on, so the stream never blocks the calculation.
Merge on read
Two runs of the same site-day no longer fight. They both append, and the table has both snapshots. The reader picks:
-- Illustrative: the shape of the read, not our real columns
WITH complete AS (
SELECT snapshot_id FROM events WHERE event_type = 'run'
),
ranked AS (
SELECT e.*,
ROW_NUMBER() OVER (
PARTITION BY site_id, calculation_date
ORDER BY computed_at DESC
) AS rn
FROM events e
JOIN complete USING (snapshot_id)
WHERE e.event_type <> 'run'
)
SELECT * FROM ranked WHERE rn = 1
Latest complete snapshot wins. The older snapshots are still there, so “what did we report for this site on that day, before the backfill?” is a WHERE snapshot_id = ..., not a question nobody can answer.
Basin SQL can run window functions and joins like this straight on the catalog, and so can DuckDB. The same idea works across steps: the performance step and the loss step each append their own events, and the query decides which curtailment value to show. Nobody needs an ordering edge to be correct.
Postgres or columnar
This only works because the storage underneath is built for it.
Postgres stores data by row. Reading one row or upserting one row with INSERT ... ON CONFLICT DO UPDATE is cheap. Many writers can hit the same row, row locks sort them out, and the last write wins. Nothing errors, which is exactly how our conflict stayed quiet.
Iceberg stores data in Parquet files, by column. Appending is cheap. A query that wants one value across every site and every day reads only that column, compressed. Changing one row in place is the expensive part.
| Postgres | Iceberg in Basin | |
|---|---|---|
| Stored by | row | column (Parquet) |
| Good at | reading or upserting one row | appending, and scanning a few columns over many rows |
| A new value | overwrites the row | a new row, the old one stays |
| Two writers, same site-day | last write wins, silently | both kept, the reader picks the latest |
| History | gone after the update | every snapshot, until it expires |
| Runs on | a database server | files in R2, plus a catalog |
Hot storage
Keeping every version would be a bad idea in Postgres. Postgres keeps every row on the database’s disk, which is hot storage, and old site-days cost the same as today’s.
For a sense of scale, here’s what the storage alone costs on a DigitalOcean Volume, a common disk for a self-run Postgres, against R2, from the DigitalOcean Volumes and R2 pricing pages as of October 2026:
| Data kept | DigitalOcean Volume ($0.10 per GiB) | R2 Standard ($0.015 per GB) | R2 Infrequent Access ($0.01 per GB) |
|---|---|---|---|
| 100 GB | $10 / month | $1.50 / month | $1.00 / month |
| 500 GB | $50 / month | $7.50 / month | $5.00 / month |
| 1 TB | $100 / month | $15 / month | $10 / month |
A few things the table leaves out, in both directions:
- A Volume is billed by the size you provision, not what you use, so in practice you pay for headroom too.
- R2 charges per request: $4.50 per million writes and $0.36 per million reads on Standard, with a free tier. Egress is free.
- Basin Catalog adds catalog operations ($9 per million after the first million) and compaction when it’s on. Snapshot expiration is free.
- Infrequent Access charges $0.01 per GB to read back, so it only suits data you rarely query.
- The Postgres server itself costs money on top of its disk. Basin has no server to run.
Per GB, R2 Standard is about a sixth of the Volume price. Writing many versions is affordable there.
Dagster’s own tables
Dagster also needs somewhere to keep what each step returns, for the next step to load. That goes through an IO manager, and ours writes one Iceberg table per asset in the same catalog, through DuckDB attached as an Iceberg REST catalog.
These tables are the one place we do replace rows. A rerun of a site-day should give the next step the new frame, not both. So a write is one MERGE that deletes the rows of the run’s site-days and inserts the new ones:
MERGE INTO lake.ns.asset AS t
USING staged AS s ON FALSE
WHEN NOT MATCHED BY TARGET THEN INSERT BY NAME
WHEN NOT MATCHED BY SOURCE AND (t.partition_key) IN
(SELECT partition_key FROM batch_keys) THEN DELETE
Each asset has one writer, and a backfill runs as batches of 10 site-days with BackfillPolicy.multi_run(10), so one batch is one commit. Two runs on the same table can still collide, because Iceberg commits are optimistic and DuckDB doesn’t retry a losing commit. The IO manager catches the conflict, waits a growing, jittered time, reads the table again and runs the MERGE again.
Old snapshots expire after 30 days, and compaction is on, through the catalog’s table maintenance.
The cut-over
The old Postgres writes haven’t gone away yet. In the new pipeline those writes, and the stream sends, don’t go straight out. The step records each call and a publish step replays it, in record, shadow, or live mode.
Until cut-over everything runs in record. The new pipeline writes only its own tables, and the old Postgres tables keep being written by the old one until their readers move. Nothing in the new pipeline reads them.
That let us run both side by side. A dev backfill on 28 October over 9 sites gave the same rows, status, and flags as the old storage, for each of the 15 assets of the reference site-day.