rocky-data / rocky-data/rocky

bug(engine): an incremental replication records its watermark to whole seconds, so a sub-second timestamp re-copies the newest rows on every run

Open
#2,004 0 comments 0 reactions 0 assignees View on GitHub
bug engine
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.