bug(engine): an incremental replication records its watermark to whole seconds, so a sub-second timestamp re-copies the newest rows on every run
- Dominant language
- Rust
- Stars
- 298
- Forks
- 18
- Avg merge
- 8h 10m
- Merged PRs (30d)
- 329
Description
An `incremental` replication records its watermark to whole seconds. When the newest source row's timestamp has a fractional second, run 2 reads a watermark that is older than that row, copies it again, and the `row_count` check fails.
## Reproduce (dev build of `main` at `494e081b`, 1.74.0 line, DuckDB)
```toml
[adapter]
type = "duckdb"
path = "warehouse.duckdb"
[pipeline.main]
strategy = "incremental"
timestamp_column = "_loaded_at"
[pipeline.main.source.discovery]
adapter = "default"
[pipeline.main.source.schema_pattern]
prefix = "src__"
separator = "__"
components = ["source"]
[pipeline.main.target]
catalog_template = "warehouse"
schema_template = "raw__{source}"
[pipeline.main.target.governance]
auto_create_schemas = true
[pipeline.main.checks]
row_count = true
```
```bash
duckdb warehouse.duckdb -c "CREATE SCHEMA src__demo; CREATE TABLE src__demo.t AS SELECT 1 AS id, TIMESTAMP '2026-09-15 10:00:00.250' AS _loaded_at;"
rocky --config rocky.toml --output json run # run 1
rocky --config rocky.toml --output json run # run 2, nothing changed in the source
```
```text
_loaded_at = 10:00:00.250 run 1 exit 0 Success raw__demo.t = 1 row watermark "2026-09-15T10:00:00Z"
run 2 exit 2 PartialFailure raw__demo.t = 2 rows row_count 1 != 2
_loaded_at = 10:00:00 run 1 exit 0 Success 1 row
run 2 exit 0 Success 1 row
```
The same source, untouched between runs, duplicates its row on every run after the first whenever the timestamp carries sub-second precision. `now()` on every warehouse does, so any source loaded with a `now()`-stamped column hits this on run 2.
## Why
The recorded watermark is `2026-09-15T10:00:00Z` for a source row stamped `10:00:00.250`: the fraction is dropped between reading `MAX(_loaded_at)` and persisting it. Run 2 then filters `_loaded_at > '2026-09-15T10:00:00'`, which the `.250` row passes again. The parse site in `engine/crates/rocky-cli/src/commands/run.rs` (around the `%.f` format arms) accepts fractions, so the loss is either in the value the warehouse hands back as a string or in how the watermark literal is rendered before persisting. Not traced further.
## Who hits it
Every incremental replication whose timestamp column has fractional seconds: run 1 is right, run 2 and every later run re-append the newest second's rows and fail `row_count`. The `dagster-rocky` scaffold starter was going to ship `incremental` on `_loaded_at`; it ships `full_refresh` instead until this is fixed (#1998).
## Fix
Persist the watermark at the precision the source column has (at least microseconds), and add a test that seeds a `.250` timestamp, runs twice, and asserts one target row and a passing `row_count`.
Refs #1930 (the freshness check reads the same column).
Contributor guide
Research direction
Start in engine/crates/rocky-cli/src/commands/run.rs around the %.f format arms, then trace how MAX(_loaded_at) is returned and how the watermark literal is rendered and persisted. Seed a fractional-second timestamp such as .250, run the incremental replication twice, and add coverage showing one target row and a passing row_count check on the second run.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- rust, sql
- Domain
- data-engineering, databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 68/100