Replay and Lineage

SQLTide records normalized boundary columns so a model graph can be populated from retained stream history. Boundary mode belongs to each source declaration.

Boundary modes

Mode	Source types	Boundary
offsets	managed Kafka, adopted table	maximum offset per partition
timestamp	managed Kafka, adopted table	timestamp cutoff
landed_at	managed Kafka only	ClickHouse landing time cutoff
cursor	adopted table only	monotonically increasing cursor cutoff

The normalized columns are _replay_partition, _replay_offset, _replay_timestamp, _replay_landed_at, and _replay_cursor.

Inclusive, at-least-once cutoffs

Every replay boundary is inclusive, and a build starts the live materialized view before it captures the boundary. Every row that arrives between those two steps, not only the row at boundary B, can reach the target twice: once through the live view and once through replay. This way no row is missed, but SQLTide does not remove the duplicates. The number of duplicated rows grows with the arrival rate and the time the build waits for the live view to stabilise. For example, with one order arriving every 20 ms during a build, 135 of 189 orders reached the new target twice.

A plain MergeTree target keeps every duplicate, so its row counts and sums are inflated. Model SQL or the engine, such as ReplacingMergeTree or idempotent aggregates, must handle them.

Repeated offsets

When a direct build captures an offset replay, it compares the anchor’s row count with the number of distinct offsets it captured. Only a managed raw landing table is deduplicated, because it holds one row per Kafka message; a downstream model can legitimately hold several rows per offset. If the anchor holds repeated copies of an offset, the replay reads each offset once with LIMIT 1 BY, which holds every offset key in memory. Without repeated copies the replay SQL is unchanged.

Virtual-environment replays render before their boundaries are captured, so the build checks the anchor first and uses the same LIMIT 1 BY when any partition holds more rows than its offset span. That check is cheap, but gaps in the offsets can hide a small number of repeats.

Replay anchors

A replay starts at a source or eligible upstream model and flows through materialized views into the selected closure. A model can be an anchor when it:

• preserves the lineage required by its source mode
• is non-aggregate
• has no mutable side reference
• does not set replay_anchor never
• in a virtual pipeline with offsets lineage, also outputs _replay_landed_at, which bounds the replay

SQLTide derives anchors from typed graph edges. A selected model also includes its descendants. When the nearest anchor is further up than the table a changed model reads, the models between them are rebuilt too, so the replay runs through their logic.

Direct mode

Direct builds replay retained history for the selected closure. There is no bounded change policy: the selected resources are rebuilt each time. Before teardown, SQLTide verifies the source still covers any required historical range recorded by prior builds.

Virtual-environment replay

Virtual environments may seed older rows from the active table and replay only a newer range. Change policies use:

[defaults]
bounded_replay_fallback = "full"

[defaults.replay_on_change]
breaking = "full"
non_breaking = "bounded-7d"

The same fields can appear in an optional pipeline.toml or MODEL() header; model wins over pipeline, which wins over project. Durations accept d, h, m, or s.

When a model cannot preserve older seeded history, typically because aggregation prevents a clean split, bounded_replay_fallback controls the result:

Value	Behavior
full	Replay all available history
bounded_without_history	Replay only the bounded window and omit older history

--full-refresh forces full replay for selected virtual-environment models. --start-time requests a bounded replay from an explicit time. Virtual environments require active published roots and may seed pre-window history. Direct mode drops and rebuilds the selected models, leaves sources unchanged, and leaves the model tables without rows from before the start time.

Start-time boundaries are UTC instants, not warehouse-local wall times. Generated replay SQL uses explicit UTC DateTime64 literals so server timezone and daylight-saving transitions cannot shift or collapse the requested boundary.