> ## Documentation Index
> Fetch the complete documentation index at: https://docs.synq.io/llms.txt
> Use this file to discover all available pages before exploring further.

# 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`](#cutoffs-for-a-lagging-target) |
| Only a recent window is worth comparing                            | [`window`](#windows)                      |
| A query needs a date or an id boundary                             | [Template variables](#template-variables) |
| You want to compare against a point in time                        | [`as_of`](#time-travel)                   |

## 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.

```yaml theme={null}
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](https://pkg.go.dev/time#pkg-constants) | `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:

```bash theme={null}
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](/reconciliation/investigating-results#re-check-and-drill-deeper) compare the same window as the original run.

<Warning>
  **`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.
</Warning>

## Windows

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

```yaml theme={null}
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.

```yaml theme={null}
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.

<Note>
  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.
</Note>

### Three levels of control

<Tabs>
  <Tab title="Simple">
    One column, both sides:

    ```yaml theme={null}
    cutoff:
      column: created_at
    ```

    Each side's watermark is `MAX(created_at)` over its own dataset; the lower of the two becomes the cutoff.
  </Tab>

  <Tab title="Different column per side">
    Common when the target records when it was loaded, not when the event happened:

    ```yaml theme={null}
    cutoff:
      source_column: created_at
      target_column: synced_at
    ```
  </Tab>

  <Tab title="Full control">
    ```yaml theme={null}
    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`.
  </Tab>
</Tabs>

### How a cutoff resolves

<Steps>
  <Step title="Derive a watermark per configured side">
    `MAX` (or `MIN`) of the named column, or the result of your custom query.
  </Step>

  <Step title="Combine the two into one value">
    `combine: min` by default.
  </Step>

  <Step title="Truncate to a boundary">
    Optional. Skipped when the watermark is not a timestamp.
  </Step>

  <Step title="Apply the offset">
    Optional, after truncation. Negative buys margin.
  </Step>

  <Step title="Build the WHERE clause for each side">
    Against the derived column, or whatever `apply:` names.
  </Step>
</Steps>

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.

<Warning>
  **`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`.
</Warning>

<Tip>
  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.
</Tip>

## 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:

```yaml theme={null}
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.

<Note>
  A [re-check](/reconciliation/investigating-results#re-check-and-drill-deeper) 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.
</Note>

## Choosing between them

<CardGroup cols={2}>
  <Card title="Use a cutoff when" icon="water">
    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.
  </Card>

  <Card title="Use a window when" icon="crop">
    History is settled and you only care about recent data — usually for cost, not correctness.
  </Card>

  <Card title="Use variables when" icon="code">
    You need an explicit, reproducible boundary in your own SQL, identical on both sides.
  </Card>

  <Card title="Use `as_of` when" icon="clock-rotate-left">
    You need a specific historical state, not "now".
  </Card>
</CardGroup>

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

<CardGroup cols={2}>
  <Card title="Running locally" icon="terminal" href="/reconciliation/running-locally">
    Overriding variables per run, and reading what a run resolved them to.
  </Card>

  <Card title="Investigating results" icon="magnifying-glass" href="/reconciliation/investigating-results">
    Confirming a difference is real, and re-checking after a fix.
  </Card>
</CardGroup>