Tinybird sources are append logs: dedupe with ReplacingMergeTree

TL;DR

When syncing mutable source data into Tinybird, never query the raw landing source for current state: build the ReplacingMergeTree dedup layer with a sorting key, version column, and delete flag, and read it with FINAL. Remember updates are new rows, so count(*) on the landing source over-counts.

Steps

1. When syncing mutable source data into Tinybird, never query the raw landing source for current state: build the ReplacingMergeTree dedup layer with a sorting key, version column, and delete flag, and read it with FINAL. Agents generating Tinybird datafiles should include ENGINE_VER from the start; adding dedup later means backfilling. Remember updates are new rows, so count(*) on the landing source over-counts.

When to use

You are seeing this: Agents generating Tinybird datafiles should include ENGINE_VER from the start; adding dedup later means backfilling. Use this skill when you run into "Tinybird sources are append logs: dedupe with ReplacingMergeTree".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

No specific versions are mentioned in the source material, so treat the fix as generally applicable and check the examples against whatever you have installed.

Why this happens

The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.

Edge cases

• Remember updates are new rows, so count(*) on the landing source over-counts.

Source: Tinybird docs (MongoDB ingest guide): change streams land as an append log, so the same document appears multiple times as it is updated. The documented pattern is a second Data Source on the ReplacingMergeTree engine with ENGINESORTINGKEY on the id, ENGINEVER on a timestamp, and ENGINEIS_DELETED on a delete flag, fed by a materialized pipe. Query the deduped source with FINAL. Without this, every update is a duplicate row in your analytics.

Verified resolution: When syncing mutable source data into Tinybird, never query the raw landing source for current state: build the ReplacingMergeTree dedup layer with a sorting key, version column, and delete flag, and read it with FINAL. Agents generating Tinybird datafiles should include ENGINE_VER from the start; adding dedup later means backfilling. Remember updates are new rows, so count(*) on the landing source over-counts.

Source URL: https://www.tinybird.co/docs/forward/guides/ingest-from-mongodb