Time windows and cutoffs - Coalesce Quality Documentation

Many “the data is wrong” findings come from the comparison window, not from the data. The two systems were read at slightly different moments, or the target has not caught up yet. This page shows how to rule that out. Use this table to choose:

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 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, because a comparison window must not go into the future. 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.

Do not use NOW(), CURRENT_DATE or GETDATE() in a query. Each side evaluates them at its own moment, on its own clock. A row written between the two evaluations is on one side and not on the other. The result is a mismatch that is not in your data. It occurs on every run, each time for a different row.check-config warns when it finds one. Set strict_time_references: true at suite level to make it an error. We recommend this 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, and positive: how far back from the run the window reaches, in any of the duration forms ("14d", "P14D", "14 days", {count: 14, period: day}). A zero or negative lookback is ignored, with a warning, and every row is compared.
column is the timestamp or date column each table side is filtered on. The filter is written for the column’s type, so a DATE column is compared with a date, and it is added to any where: you wrote rather than replacing it.
strategy: sliding (the default) measures back from the run’s reference time, so each run compares a moving fortnight. fixed ends the window at the last period boundary in UTC and starts it one lookback earlier, so consecutive runs inside the same period compare exactly the same rows. The period follows the lookback: midnight for a whole number of days, the start of the hour for whole hours, otherwise the minute.

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. Every window also defines two template variables for its own reconciliation: {{ window_start }}, and {{ window_end }} (the run’s reference time, or the period boundary for a fixed window). Use them where the column filter does not reach:

A query: side is never filtered on the column. You wrote its SQL, so filter it yourself:

source:
  connection: app
  query: |
    SELECT event_id, payload FROM public.events
    WHERE created_at >= '{{ window_start }}'

A where: that already references {{ window_start }} or {{ window_end }} is kept exactly as written, and the column is not filtered a second time on that side. A window without a column filters nothing on its own and only defines the variables.

A cutoff on top of a window reads its watermark from inside the window, so a MAX() over a large table scans only the recent rows.

Durations

window.lookback, cutoff.offset, the timeouts and error_handling durations all take the same forms:

Form	Examples
A whole number and a unit: `s`, `m`, `h`, `d` or `w`	`14d`, `-30m`, `90s`, `2w`
A Go duration	`1h30m`, `250ms`
ISO 8601, with weeks, days, hours, minutes and seconds	`P14D`, `PT6H`, `P1DT12H`, `-PT30M`
Words	`14 days`, `-2 hours`, `1 hour 30 minutes`
dbt’s `{count, period}`, as in a source’s `warn_after`	`{count: 14, period: day}`, `{count: -30, period: minute}`

A leading - makes a duration negative, which is what a cutoff offset usually is. Years and months are refused, because they are not a fixed length: write the days instead, for example P30D. A duration longer than the time since 1970 loads with a warning, because it is usually a wrong unit or a typo. synq-recon suite yaml and the app write durations back in the short forms of the first two rows (14d, 1h30m), which every CLI release reads.

Cutoffs for a lagging target

If the target is behind the source (a CDC stream, a batch load, a read replica), the most recent rows are on the source and not yet on the target. These rows are not missing, they are in flight. Without a cutoff, every run reports them as a difference, and the difference moves as the lag moves. A cutoff: derives a watermark from the data, then filters both sides to rows at or below it. Rows in flight are not compared, so they are not reported as differences.

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 result on the same pair of tables, a primary with 500 orders and a replica that has 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

The data and the query are the same, but the results are different. The second result is correct: the replica is thirty rows behind. The cutoff compares only the 415 rows that both sides certainly have.

The cutoff is stricter than the lag: 415 rows, not 470. truncate: HOUR moves the watermark down to the start of the hour, and offset: "-5m" moves it 5 minutes further back. Both give a safety margin, because a pipeline that writes slightly out of order can write a row below a watermark that it already passed.

Three levels of control

Simple
Different column per side
Full control

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.

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

cutoff:
  source_column: created_at
  target_column: synced_at

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

Derive a watermark per configured side

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

Combine the two into one value

combine: min by default.

Truncate to a boundary

Optional. Only for a timestamp watermark.

Apply the offset

Optional, after truncation. Negative buys margin.

Build the WHERE clause for each side

Against the derived column, or whatever apply: names.

The audit log records each of these values: the two watermarks, the combined cutoff, and the final WHERE clause of each side. Thus you can always find the comparison window of a run later. In the app, the Overview of a run shows them under Cutoff. This is the quickest way to check that an offset or truncate does what you want.

combine: min is the default, and it is correct in most cases. The lower of the two watermarks is the point that both sides have certainly reached.max, source and target can each keep rows that one side has and the other side does not have yet. These rows cause a mismatch. Use them only when you know that the difference between the sides is real. If you are not sure, use min.

The watermark does not have to be a timestamp. A sequence or an id that only increases works in the same way. With such a watermark, do not set truncate or offset: on a value that is not a timestamp, either one makes the reconciliation fail.

Time travel

as_of compares a snapshot at a point in time, not the current state. Snowflake, BigQuery and Databricks support it:

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

Use it to check if the data was correct at a given time, for example before a deploy. You can also use it to read both sides at the same instant when one of them gets writes all the time. A window with a column still filters a table side that has as_of, applied to the snapshot. If the side lists columns, the window column has to be one of them.

A re-check replays the queries of the original run. For an as_of reconciliation, it compares the same snapshot again and can never pass. Pass --reresolve to derive the queries again 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 does not change and only recent data is important to you. This is usually for cost, not for 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”.

You can use them together. A cutoff on a window is a usual combination: it compares the last 14 days, up to the point that the target has reached.

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.

​Template variables

​What you can write inside {{ }}

​Windows

​Durations

​Cutoffs for a lagging target

​Three levels of control

​How a cutoff resolves

​Time travel

​Choosing between them

Use a cutoff when

Use a window when

Use variables when

Use as_of when

​Next

Running locally

Investigating results

Template variables

What you can write inside `{{ }}`

Windows

Durations

Cutoffs for a lagging target

Three levels of control

How a cutoff resolves

Time travel

Choosing between them

Use `as_of` when

Next