ClickHouse + Iceberg + Nessie — Consistent Writes in a Two-Writer System

The Problem

You have ClickHouse backed by Iceberg as its data source.

Two things write to that Iceberg table:

1. ClickHouse itself — streaming inserts (continuous ingestion pipeline)
2. An external transactional service — direct Iceberg mutations (read-check-then-write, e.g. a balance transfer)

Both write to the same partitions. Both need to be correct. And after any write, a reader should see the committed state.

What breaks without a design:

Three rules that fix all of this:

Rule 1 — All writes go through Nessie OCC. Both CH and the external service commit through Nessie with base_snapshot = current_HEAD. Whoever commits second gets a 409 conflict and retries. No distributed locks needed.

Rule 2 — Transactional reads bypass CH. Go to Iceberg directly. CH's cached file list lags Nessie by up to one coordinator poll interval. For any read-check-then-write, read from Nessie directly — never from CH.

# Wrong for transactional reads:
balance = ch.execute("SELECT balance FROM accounts WHERE id='A'")

# Right — read at the current committed snapshot:
snap    = nessie.current_snapshot(table)
balance = iceberg.scan(snap).filter("id='A'").collect()

Rule 3 — Read-after-write uses the snapshot ID. After committing, you have the new snapshot ID. Pass it to the coordinator — it reloads CH to that snapshot before serving any downstream read.

snap_id = catalog.commit(...)            # returns S_n+1
coordinator.wait_for_ch_at(snap_id)      # blocks until CH reflects S_n+1
result   = ch.execute("SELECT ...")      # now guaranteed to see S_n+1

ClickHouse is your query layer. Iceberg + Nessie is your transaction layer. Transactional reads go to Iceberg directly. CH reads are eventually consistent, bounded by the coordinator reload interval. Nessie OCC resolves all write conflicts.

Architecture

Transactional reads bypass CH entirely — the External Txn Service reads Nessie HEAD and scans S3 parquet via the Iceberg SDK.

Read-after-write is bounded by the wait_for_ch_at(snapshot_id) call on the coordinator — it blocks until CH's file list reflects the committed snapshot before releasing the downstream read.

Nessie: The Transaction Coordinator

Nessie gives Iceberg what a WAL (write-ahead log) gives a relational DB — an authoritative, atomic record of what is committed.

Every Iceberg table has a current snapshot pointer in Nessie. A commit is a compare-and-swap on that pointer:

BEFORE:  Nessie HEAD = S_n
         S3: A.parquet(bal=200), B.parquet(bal=50)

Writer:
  1. Writes new-A.parquet(bal=100), new-B.parquet(bal=150) to S3
  2. Calls catalog.commit(base_snapshot=S_n, add=[new-A, new-B], delete=[A, B])
  3. Nessie: "if HEAD is still S_n → swap to S_n+1"

AFTER:   Nessie HEAD = S_n+1
         S3: new-A.parquet(100), new-B.parquet(150)
         Old files: still on S3, marked deleted in metadata (GC'd later)

The commit is all-or-nothing. No file is half-visible. If the writer crashes before calling commit, S_n is still the HEAD — nothing changed. The staged parquet files on S3 are orphaned and cleaned up by Iceberg's GC job.

OCC Mechanics (How the 409 Actually Works)

POST /api/v1/trees/main/commit
{
  "expectedHash": "abc123",
  "operations": [{
    "type": "PUT",
    "key": { "elements": ["accounts"] },
    "content": {
      "metadataLocation": "s3://bucket/.../v43.metadata.json",
      "snapshotId": "S_n+1"
    }
  }]
}

Nessie server (simplified):

def commit(branch, expected_hash, operations):
    with database_transaction():
        current = get_branch_head(branch)
        if current != expected_hash:
            raise HTTP409("Concurrent modification — retry")
        persist_new_commit(...)
        update_branch_head(branch, new_hash)

The check + update is one atomic database operation:

• PostgreSQL backend: UPDATE branches SET head = $new WHERE head = $expected — 0 rows updated = 409
• DynamoDB: conditional write with ConditionExpression

No window between check and write. This is why OCC is sufficient — it's not "check then act", it's a single atomic CAS.

When a writer gets a 409, it must:

1. Re-read current HEAD from Nessie
2. Re-scan the partition at the new HEAD (to get fresh base data)
3. Re-compute the upsert
4. Write fresh parquet files (new UUIDs — never reuse)
5. Retry the commit with the new expectedHash

Use exponential backoff with jitter for retries. Under sustained contention, consider partitioned writes or a serialization queue so writers avoid racing on the same partition.

How ClickHouse Writes to Iceberg via Nessie

When CH has an Iceberg-backed table and you run INSERT INTO, here is what CH does internally:

Step 1: CH writes parquet files to S3. CH's Iceberg writer batches the incoming rows and writes one or more parquet files to the table's S3 path. File names are UUIDs — globally unique.

Step 2: CH creates Iceberg metadata. CH constructs the Iceberg metadata chain:

• Manifest file: lists the new parquet files (add entries) and any deleted rows
• Manifest list: references all manifests for the new snapshot
• New metadata.json: points to the new manifest list, sets the new snapshot ID

All of this is written to S3 before any catalog call.

Step 3: CH commits to Nessie.

POST /api/v1/trees/main/commit
{
  "expectedHash": "<current HEAD hash CH read at insert start>",
  "operations": [{
    "type": "PUT",
    "key": { "elements": ["accounts"] },
    "content": {
      "metadataLocation": "s3://bucket/.../v43.metadata.json",
      "snapshotId": "S_n+1"
    }
  }]
}

If CH gets a 409, it retries from step 1 with the new HEAD. This is automatic inside CH's Iceberg writer.

Key point: CH's write path is indistinguishable from an external writer's write path at the Nessie level. They use the same REST API, the same OCC commit protocol, the same parquet format. Nessie has no concept of "this commit came from CH vs. the external service."

ClickHouse Iceberg Table Config

This config is what makes CH and the external service structural peers: both point at the same Nessie REST catalog, so Nessie sees them as two identical clients with no special privilege.

CREATE TABLE accounts
ENGINE = Iceberg(
  's3://bucket/warehouse/accounts/',
  catalog_type = 'rest',
  catalog_uri  = 'http://nessie:19120/api/v1',
  catalog_credential = '',
  warehouse    = 's3://bucket/warehouse'
)

How CH SYSTEM RELOAD TABLE Works

What CH Caches Internally

When CH first accesses an Iceberg table, it:

1. Calls Nessie: GET /api/v1/trees/main/contents/accounts → gets metadataLocation
2. Reads metadata.json from S3 → gets the current snapshot ID and manifest list location
3. Reads the manifest list → gets the list of manifest files
4. Reads each manifest → gets the list of parquet data files

CH caches this file list in memory. CH does not re-read Nessie on every query.

What SYSTEM RELOAD TABLE Does

SYSTEM RELOAD TABLE accounts;

Forces CH to re-execute steps 1–4 from scratch. The file list swap is atomic within CH — in-flight queries finish on the old snapshot (snapshot isolation). New queries after the reload see the new snapshot.

It is safe to call SYSTEM RELOAD TABLE multiple times — it's idempotent.

Latency

• Small tables (< 100 manifest entries): < 100ms
• Large tables (thousands of parquet files): 1–5s depending on S3 latency and manifest count

This determines your consistency lag — the window between a Nessie commit and CH seeing it.

Push vs. Poll Reload

CH also supports a metadata_refresh_interval setting — CH auto-reloads in the background every N seconds. The coordinator pattern gives you push-based refresh (reload triggered immediately after each Nessie commit), which is better for transactional workloads: you reload only when needed and you know exactly when CH is caught up.

ACID Properties

Failure Modes

Write Fails Before Catalog Commit

Parquet files written to S3, but the process crashes before calling Nessie. Result: orphaned files on S3, Nessie HEAD unchanged. Zero data corruption. Iceberg's GC job (expireSnapshots) removes unreferenced files. Retry the full transaction from scratch.

Nessie Commit Gets a 409

Another writer committed between your read and your commit. Result: your staged parquet files are orphaned, transaction fails cleanly. Retry: re-read Nessie HEAD, re-scan, re-compute, write fresh parquet, commit again.

Nessie Crashes During Commit

Ambiguous result — may or may not have committed. After recovery, query Nessie for the current snapshot. If your expected S_n+1 is now HEAD, commit succeeded. If HEAD is still S_n, commit failed. Act accordingly.

Coordinator Crashes After Nessie Commit

CH stays on S_n. When coordinator restarts, it queries Nessie for HEAD. If HEAD > last-known-CH-snapshot, it reloads CH. Data is not lost — Nessie has the committed state. Only CH's view is stale until coordinator recovers.

The coordinator is a single point of failure for read-after-write guarantees but not for data durability. For HA, run two coordinator replicas with a distributed lock (e.g. a Postgres advisory lock) so only one calls SYSTEM RELOAD TABLE at a time.

Snapshot Expiry During Active CH Query

CH holds a reference to its current snapshot for the duration of a scan. If expireSnapshots runs and deletes parquet files that CH's in-flight query needs, CH gets S3 404 errors. Fix: never expire snapshots younger than 2 × max_expected_query_duration. Coordinator tracks which snapshot CH is on; GC jobs check this before expiring.

When You'd Need More

For a single Iceberg table with two writers and analytical reads: Nessie OCC + coordinator reload is sufficient. No Saga. No 2PC.