The Null That Wouldn't Die - A ClickHouse ReplacingMergeTree Debugging Story

The setup

We run an ingestion pipeline that reads clinical-style form data out of a Spark/Delta layer and writes it into ClickHouse, where downstream tooling queries it. One of the target tables looked roughly like this (names and columns simplified):

CREATE TABLE analytics.form_data
(
    `record_key`        String,
    `coded_term`        Nullable(String),   -- the column that misbehaved
    `coded_term_nd`     Nullable(String),
    `event_name`        Nullable(String),
    `subject_id`        Nullable(String),
    `modified_at`       DateTime64(3),
    `last_modification` DateTime64(3),
    -- ... ~100 more columns ...
)
ENGINE = ReplicatedReplacingMergeTree(
    '/clickhouse/tables/{shard}/analytics/form_data',
    '{replica}'
)
ORDER BY record_key;

Look closely at that engine line. We'll come back to it — it's the whole story.

The symptom

A support thread lit up: for 13 specific rows, one column (coded_term) was NULL in ClickHouse, even though the source clearly had a value.

The reporter was confident:

From a functionality perspective everything works. On the Spark side we're inserting the correct values, and inside the job we can see those values going into ClickHouse. But in ClickHouse the column is still null for these rows.

I'm pretty sure if I just drop and recreate the table, new data comes in correctly. But we need to understand what's actually wrong.

Two things in that message are worth flagging early, because they shaped hours of the investigation:

1. "I can see the values going into ClickHouse." (We'll find out this wasn't quite true.)
2. "If I drop the table it fixes itself." (True — and exactly why it was tempting to do the wrong thing.)

The source (the Spark/Delta side) genuinely had the coded values — think of a dictionary-coded term that arrives after the raw row, like TERM_A47, TERM_B12, TERM_C09, and so on. ClickHouse had NULL. Same rows, same keys.

The wrong turns

Before the real cause surfaced, the thread chased several ghosts. This part is worth documenting, because these are the natural first guesses and each one felt plausible.

Wrong turn 1: "It's a merge/dedup bug in ClickHouse"

The first instinct was that ClickHouse's merge was choosing the wrong row — keeping an old NULL version instead of the newer populated one. Reasonable, for a ReplacingMergeTree. But it turned out to be exactly backwards, as we'll see.

Wrong turn 2: "The migration broke it"

A few weeks earlier, someone had run a bulk migration that recreated thousands of tables with a simple:

INSERT INTO analytics.form_data_new SELECT * FROM analytics.form_data_old;

Suspicion fell on this: maybe SELECT * misaligned columns, or the new ZooKeeper path (.../form_data_new) was the problem.

This got ruled out cleanly. As the migration author pointed out: if SELECT * had misaligned columns, it would have thrown across thousands of tables, and it didn't. And the ZooKeeper path is just a replication coordination path — a wrong path throws a replication error, it doesn't silently null a single column.

Wrong turn 3: "A column type conversion did it"

There had also been an Int64 → UInt64 conversion on an unrelated column. But that doesn't explain a different column going null, and a type mismatch would have thrown an exception, not silently produced NULL.

Wrong turn 4: Time travel

The natural move: use Delta time travel to see what the source looked like when the bad rows were written. Blocked:

[DELTA_UNSUPPORTED_TIME_TRAVEL_BEYOND_DELETED_FILE_RETENTION_DURATION]
Cannot time travel beyond delta.deletedFileRetentionDuration (168 HOURS)

Seven-day retention. The bad writes were from over a month prior. Dead end.

The lesson from all four: stop theorizing about history you can't observe, and go look at the physical state you can observe right now.

The diagnosis

Two queries cracked it.

Step 1 — FINAL vs no FINAL

ReplacingMergeTree deduplicates rows with the same sorting key at merge time (and at query time if you add FINAL). So the physical rows on disk and the "logical" deduplicated view can differ. That gap is diagnostic gold.

First, look at the physical rows — no FINAL:

SELECT record_key, coded_term, modified_at, last_modification, _part
FROM analytics.form_data
WHERE record_key IN (/* the 13 keys */)
ORDER BY record_key, modified_at;

The result was the key finding:

13 keys → 13 rows. Exactly one physical row per key. All NULL.

This single result killed the merge-bug theory. If dedup were picking the wrong row, we'd see at least two physical rows for some keys — an old NULL and a newer populated one, competing. There was no second row. The populated value had never been written to this table at all.

The _part column reinforced it: one row per key, each in a single part, with no second part carrying a competing value waiting to be merged away. There was simply nothing else on disk for these keys.

Merge wasn't choosing wrong. There was nothing to choose from.

Step 2 — SHOW CREATE TABLE

Then the engine line paid off:

SHOW CREATE TABLE analytics.form_data;

ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/...', '{replica}')
ORDER BY record_key

No version column. The ReplacingMergeTree(...) parentheses are empty — no modified_at, no version argument at all.

This matters enormously, because it defines the dedup rule. With no version column, ReplacingMergeTree keeps:

the row from the most recently inserted part for a given sorting key. Not "the highest modified_at." Not "the newest business timestamp." Purely insert/merge order.

So the earlier theory — "the null row wins because it has a higher modified_at" — didn't even apply. modified_at is just a normal column here; the engine never looks at it.

Putting it together

Three facts:

1. One physical row per key, all NULL (populated row never arrived).
2. The source has the value.
3. The job had run 2–3 times that day and nothing changed.

Conclusion: the pipeline was not sending these rows to ClickHouse at all. Not the populated value, not even an overwrite. Nothing.

Why? The job's change-detection was almost certainly incremental, keyed on the source modified_at. The likely sequence:

• On the original write date, these rows were sent while coded_term was genuinely still NULL in source (the coding/decoding step hadn't run yet).
• Later, coded_term got populated — but that coding step didn't bump modified_at.
• So the incremental job saw the rows as "unchanged" and never re-sent them.

The NULL froze. Not because ClickHouse corrupted it — because ClickHouse faithfully stored the only version it was ever given, and was never given another.

A note on honesty: the "coding didn't bump modified_at" step is inference, not something we could prove — time travel was gone. What we could prove: the value was never in the table (physical rows), merge works (below), and the source has it now. The mechanism upstream is the working theory; the CH side was exonerated by evidence.

Proving merge actually works

To close the loop and show merge itself was never the problem, we ran a sentinel test on a renamed copy of the broken table (renamed, not dropped — see below):

-- Insert a new row for one key, overwriting the null with a sentinel value.
-- * REPLACE keeps every other column identical, so no column-order risk.
INSERT INTO analytics.form_data_broken
SELECT * REPLACE ('MERGE_TEST' AS coded_term)
FROM analytics.form_data_broken
WHERE record_key = '<one_affected_key>';
 
-- Force the merge.
OPTIMIZE TABLE analytics.form_data_broken FINAL;
 
-- What survives?
SELECT record_key, coded_term
FROM analytics.form_data_broken FINAL
WHERE record_key = '<one_affected_key>';

Result: MERGE_TEST.

The newly inserted row won, exactly as expected for a version-less ReplacingMergeTree (latest insert / highest block number wins). Merge and dedup were healthy the whole time. The NULL existed purely because the populated row had never been inserted.

The reproduction: same command, different result

Here's the part that makes this a genuinely nasty class of bug. A colleague reproduced the fragility directly:

1. Snapshot the broken table into a frozen copy (_snap).
2. Overwrite the broken table from the fixed table → new (correct) data shows up. ✅
3. Overwrite the broken table again from the _snap (stale) copy → stale data shows up. ✅ (expected)
4. Repeat step 2 → the fixed data does not show up this time. ❌

Same operation as step 2, run twice, two different outcomes.

Why? With no version column, the winner is decided by block/insert order, not by any value in the data. The first overwrite wins because it produces the newest block. After you cycle a stale copy back in, the block ordering no longer favors the "fixed" write, so the old value wins. The result is non-deterministic with respect to the data — it depends entirely on the order of physical inserts.

That's the trap: a version-less ReplacingMergeTree doesn't just occasionally keep a stale row. It makes "which row wins" a function of insert history rather than of the data, so the same corrective command can succeed or silently no-op depending on what happened before it.

The fix, and why "drop the table" was a trap

There were really three layers to fixing this, and it's worth keeping them separate — because the tempting one-line fix only addresses the first.

Layer 1 — immediate repair: rename, don't drop

The quick fix everyone reached for — drop the table and let the job repopulate it — actually works. An empty target has no stale rows to lose the block-order race against, so a full reload writes every row fresh from source with the current (populated) values.

But dropping it would have destroyed the evidence and hidden the real cause. The safe move was to rename, not drop:

RENAME TABLE analytics.form_data TO analytics.form_data_broken_20990101;

That let the job recreate a clean table (fixing the immediate 13 rows) while keeping the broken table intact for the root-cause investigation and the sentinel/reproduction tests above. Renaming fixes the symptom and preserves the crime scene; dropping only does the first.

Layer 2 — make dedup deterministic: add a version column

The rename got the data correct right now, but it did nothing about the underlying fragility the reproduction exposed: with no version column, "which row wins" depends on insert order, so the same corrective write can succeed or silently no-op depending on history. That's a landmine left in the table.

The durable structural fix is to give the engine a version column, so the newest business version always wins regardless of insert/block order:

-- Before: winner = whatever was inserted into the newest block
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{shard}/analytics/form_data', '{replica}')
ORDER BY record_key
 
-- After: winner = the row with the highest modified_at, deterministically
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{shard}/analytics/form_data', '{replica}', modified_at)
ORDER BY record_key

With modified_at as the version, if a stale copy ever gets inserted after a fresh one — exactly the round-trip in the reproduction — the fresh row still wins, because dedup now compares the version value instead of trusting insert order.

Two honest caveats. First, this alone would not have populated the original 13 rows: their problem was that the correct row was never inserted at all, not that dedup picked the wrong one — so there was nothing for a version column to choose between. Second, changing the engine definition isn't a free in-place toggle; in practice it means recreating the table with the new engine and reloading (which the rename-and-reload already sets you up to do). The payoff is that every future correction becomes deterministic instead of a coin flip on block order.

While recreating the table, it's also worth adding an insertion-time column for traceability — it makes "which row won this merge, and when did CH actually receive it" answerable after the fact:

CREATE TABLE analytics.form_data
(
    record_key      String,
    coded_term      Nullable(String),
    modified_at     DateTime64(3),
    ch_inserted_at  DateTime MATERIALIZED now()   -- when CH actually got this row
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{shard}/analytics/form_data', '{replica}', modified_at)
ORDER BY record_key;

Layer 3 — the actual root cause is upstream

Even with a version column and a clean reload, the real bug lives outside ClickHouse. The coded values arrive late, after the raw row, without bumping the change-detection field the incremental job keys on — so the job treats those rows as unchanged and never re-sends them. Until the pipeline's change detection accounts for those late-arriving columns, the same stale-value situation will recur on any row that gets coded after its initial write, in any table.

So: rename+reload fixed the incident, the version column fixes the determinism of every future correction, and the upstream change-detection fix is the only thing that stops the problem from happening again in the first place. The first two are within ClickHouse; the third is where the bug actually is.

The outcome

After the rename, the job recreated a clean table and the 13 rows came back populated on the next full load — verified the same way we'd diagnosed them, with a no-FINAL read: one physical row per key, each now carrying its coded value instead of NULL. The renamed ..._broken copy stayed on disk, which is what made the sentinel and reproduction tests above possible after the fact rather than before.

Net result: the incident was closed by the rename, the reproduction turned a vague "ClickHouse is losing data" report into a precise, demonstrable statement about version-less dedup, and the two real follow-ups — a version column on the table and late-arriving-column handling in the pipeline — were filed with evidence behind them instead of a hunch.

Lessons

1. Give ReplacingMergeTree a version column. This is the headline. Without one, dedup is decided by insert/block order — fragile and non-deterministic with respect to your data. With a version column (ReplacingMergeTree(modified_at)), the newest business version wins deterministically, even if a stale copy is inserted afterward or merges run in an awkward order. As noted in the fix, this wouldn't have populated the missing rows here — but it turns every future correction from a coin flip on block order into a predictable result.

2. Add an insertion-time column for traceability. A materialized ch_inserted_at DateTime MATERIALIZED now() records when ClickHouse actually received each row, which makes "which physical row won this merge, and when did it arrive" answerable after the fact — the exact question we couldn't answer at the start of this investigation.

3. FINAL vs no-FINAL is your sharpest diagnostic. The difference between the physical rows and the deduplicated view tells you instantly whether you have a merge problem (multiple competing physical rows) or an ingestion problem (the row you expect simply isn't there). Here, "one physical row, all null" ended the entire merge-bug hypothesis in a single query.

4. Rename, don't drop, when something is broken in a way you don't understand yet. Dropping "fixes" the symptom and deletes the evidence. Renaming fixes the symptom and preserves the crime scene.

5. Separate "confirmed" from "inferred," out loud. Confirmed: the value was never physically in the table; merge works. Inferred: the coding step didn't bump the timestamp, so the incremental job skipped the rows. Being explicit about which is which kept the team from "fixing" the wrong layer — and kept the real root cause (upstream change detection) from being papered over by a table reload.

The immediate incident was resolved by renaming and reloading. The durable fix lives upstream: change detection has to notice late-arriving coded columns. And every affected ReplacingMergeTree gets a version column, so the next stale row loses deterministically instead of winning by luck of insert order.