Time windows and cutoffs

Comparing two live systems without inventing differences that are not there

Most “the data is wrong” findings are not data problems. They are comparison-window problems: two systems read at slightly different moments, or a target that has not finished catching up. This page is how you rule that out.

Reach for these in order:

Symptom	Use
The target trails the source, and its tail looks like missing rows	cutoff
Only a recent window is worth comparing	window
A query needs a date or an id boundary	Template variables
You want to compare against a point in time	as_of

Template variables

A variable is resolved once per run and interpolated into both sides, so the two queries are guaranteed to use the same boundary.

variables:
  start_date: "{{ today - 7d }}"
  end_date: "{{ today }}"

reconciliations:
  orders-recent:
    source:
      connection: gateway
      query: |
        SELECT order_id, customer_id, total_amount
        FROM public.orders
        WHERE order_date >= '{{ start_date }}' AND order_date < '{{ end_date }}'
    target:
      connection: warehouse
      query: |
        SELECT order_id, customer_id, total_amount
        FROM ANALYTICS.PUBLIC.ORDERS
        WHERE order_date >= '{{ start_date }}' AND order_date < '{{ end_date }}'
    key_columns: [order_id]
    mode: row_checksum

Variables also interpolate into a table dataset’s where:, so you do not have to switch to raw SQL to use them.

What you can write inside {{ }}

Expression	Resolves to	Example output
{{ today }}	Today’s date	2026-03-14
{{ yesterday }}	Yesterday’s date	2026-03-13
{{ now }}	The run’s reference instant, RFC 3339	2026-03-14T09:21:44Z
{{ now(2006-01-02 15:04) }}	now, in a Go time layout	2026-03-14 09:21
{{ today - 7d }}	A date base, minus an offset	2026-03-07
{{ now - 2h }}	A timestamp base, minus an offset	2026-03-14T07:21:44Z
{{ my_variable }}	Another variable you defined	—

Offsets subtract from now, today or yesterday, in units of d (days), h (hours), m (minutes) or w (weeks). Only subtraction is supported — a comparison window that reaches into the future is not a thing you want.

Override any variable for a single run:

synq-recon run-check suite.yaml --var start_date=2026-01-01 --var end_date=2026-02-01

The resolved values are recorded in the audit log, which is what makes a re-check compare the same window as the original run.

NOW(), CURRENT_DATE and GETDATE() in a query are a bug, not a shortcut. Each side evaluates them at its own moment, on its own clock. Any row written between the two evaluations appears on one side and not the other, and you get a mismatch that has nothing to do with your data — reliably, on every run, for a different row each time.

check-config warns when it finds one. Set strict_time_references: true at suite level to make it an error, which is worth doing in CI.

Windows

A window: restricts a reconciliation to a recent period without you writing the predicate:

reconciliations:
  events-recent:
    source:
      connection: app
      table: public.events
    target:
      connection: warehouse
      table: ANALYTICS.PUBLIC.EVENTS
    key_columns: [event_id]
    mode: row_checksum
    window:
      column: created_at
      lookback: "14d"
      strategy: sliding

• lookback is required: "14d", "2h", "1w".
• strategy: sliding (the default) measures back from the run’s reference time, so each run compares a moving fortnight. fixed anchors the window to a period boundary, so consecutive runs inside the same period compare exactly the same rows.

Use a window when history is immutable and re-comparing it every hour is a waste of warehouse spend. Use fixed when you want two runs to be comparable to each other, not just each correct in isolation.

Cutoffs for a lagging target

This is the important one, and the one people reach for last when they should reach for it first.

If the target trails the source — a CDC stream, a batch load, a read replica — then rows written recently are on the source and not yet on the target. They are not missing. They are in flight. A reconciliation with no cutoff reports them as a difference, every time, and the difference moves as the lag moves.

A cutoff: derives a watermark from the data itself, then filters both sides to at-or-below it. Rows in flight are excluded from the comparison rather than reported as errors.

reconciliations:
  orders-replicated:
    source:
      connection: primary
      table: app_orders
      columns: [order_id, amount, updated_at]
    target:
      connection: replica
      table: replica_orders
      columns: [order_id, amount, updated_at]
    key_columns: [order_id]
    mode: row_checksum
    cutoff:
      column: updated_at
      truncate: HOUR
      offset: "-5m"

The difference it makes, on the same pair of tables — a primary with 500 orders and a replica that has caught up to 470 of them:

Reconciliation: Without a cutoff (orders-no-cutoff)

             Source              Target
Row Count    500                 470

Status: MISMATCH
  - Source has 30 more rows than target

---

Reconciliation: With a cutoff on the replication watermark (orders-with-cutoff)

             Source              Target
Row Count    415                 415

Status: MATCH

Same data, same query, two different conclusions. The second one is the true one: the replica is not missing anything, it is simply thirty rows behind, and the comparison now says so by agreeing about the 415 rows both sides have definitely seen.

Notice the cutoff is stricter than the lag — 415 rows, not 470. truncate: HOUR snaps the watermark down to an hour boundary and offset: "-5m" pulls it back further. Both are deliberate margin, because a pipeline that writes slightly out of order can land a row below a watermark it has already passed.

Three levels of control

Simple

One column, both sides:

cutoff:
  column: created_at

Each side’s watermark is MAX(created_at) over its own dataset; the lower of the two becomes the cutoff.

Different column per side

Common when the target records when it was loaded, not when the event happened:

cutoff:
  source_column: created_at
  target_column: synced_at

Full control

cutoff:
  source:
    column: created_at
    aggregate: MAX          # MAX (default) or MIN
  target:
    column: synced_at
    query: |                # derive it yourself instead
      SELECT MAX(synced_at) AS watermark FROM warehouse.orders
  combine: min              # min (default), max, source, target
  truncate: HOUR            # HOUR, DAY, WEEK, MONTH, QUARTER, YEAR
  offset: "-30m"            # applied after truncation
  apply:                    # filter a different column than the derived one
    source: { column: created_at, operator: "<=" }
    target: { column: created_at, operator: "<=" }

A custom query: must return a single row with a column named watermark.

How a cutoff resolves

1. Derive a watermark per configured side

   MAX (or MIN) of the named column, or the result of your custom query.

2. Combine the two into one value

   combine: min by default.

3. Truncate to a boundary

   Optional. Skipped when the watermark is not a timestamp.

4. Apply the offset

   Optional, after truncation. Negative buys margin.

5. Build the WHERE clause for each side

   Against the derived column, or whatever apply: names.

Every one of those intermediate values — both watermarks, the combined cutoff, and the final WHERE clause per side — is recorded in the audit log. A run’s comparison window is always recoverable after the fact.

combine: min is the default and almost always the right answer. The lower of the two watermarks is the point both sides have certainly reached.

max, source and target each keep rows that one side has and the other may not — which is a mismatch you created. They exist for cases where you know the asymmetry is real; if you are not sure, you want min.

The watermark does not have to be a timestamp. A monotonically increasing sequence or id works exactly the same way, in which case truncate and offset are ignored rather than applied.

Time travel

as_of compares against a point-in-time snapshot rather than the current state, on platforms that support it — Snowflake, BigQuery and Databricks:

source:
  connection: warehouse
  table: ANALYTICS.PUBLIC.ORDERS
  as_of: "2026-02-01 00:00:00"

This is the tool for “was it right yesterday, before the deploy?” and for pinning both sides to the same instant when one of them is being written to continuously.

A re-check replays the original run’s queries, which for an as_of reconciliation means it re-compares the same frozen snapshot and can therefore never turn green. Pass --reresolve to re-derive the queries against current data.

Choosing between them

Use a cutoff when

The two systems are the same data at different times. You want to compare what both sides have, and ignore what is still on its way.

Use a window when

History is settled and you only care about recent data — usually for cost, not correctness.

Use variables when

You need an explicit, reproducible boundary in your own SQL, identical on both sides.

Use `as_of` when

You need a specific historical state, not “now”.

They compose. A cutoff on top of a window is a normal thing to write: compare the last fortnight, up to the point the target has caught up to.

Next

Running locally

Overriding variables per run, and reading what a run resolved them to.

Investigating results

Confirming a difference is real, and re-checking after a fix.