Skip to main content
Version: 0.6 (Latest)

Watermarks

Watermark Tracking​

LakeXpress maintains watermarks in the LakeXpress DB. Every export — baseline or incremental — has a lower bound d_old (the previous watermark, NULL on the first run) and an upper bound d_new:

  • d_new is MAX(column), read from the source table at the start of the run, for every column type (datetime, date, int, bigint, auto).
  • For datetime and date columns with --incremental_safety_lag, d_new is capped at the source's current time (or date) minus the lag: d_new = min(MAX(column), now - lag). See Safety Lag.
  • d_new never goes below the stored watermark: if the lag puts it lower (for example after raising the lag), d_new is the watermark, the run exports no rows for that table, and the watermark stays where it is.
  • If the table is empty (MAX(column) is NULL), d_new is the source's current time (or date) minus --incremental_safety_lag for datetime/date columns.

The generated WHERE clause is:

  • Baseline (first run): WHERE column <= d_new, or WHERE column >= start_value AND column <= d_new when the table is configured with @start_value (always inclusive, whatever :i/:e says)
  • Incremental: WHERE column > d_old AND column <= d_new (or >= if the table was configured with :i)

First Sync (Baseline)​

Table: orders
Column: o_orderdate (datetime)
d_old: NULL
d_new: 2025-12-31 22:47:12  (= MAX(o_orderdate) at run start)
Query: WHERE o_orderdate <= '2025-12-31 22:47:12'
Records exported: 1,500,000
Watermark stored: last_exported_value = 2025-12-31 22:47:12

Second Sync (Incremental)​

d_old: 2025-12-31 22:47:12  (previous watermark)
d_new: 2026-01-05 07:58:30  (= MAX(o_orderdate) at run start)
Query: WHERE o_orderdate > '2025-12-31 22:47:12' AND o_orderdate <= '2026-01-05 07:58:30'
Expected records: 50,000 (new orders since the last run)
Watermark updated: last_exported_value = 2026-01-05 07:58:30

Late-Arriving Data​

Without a safety lag, d_new is the largest value present in the source when the run starts, so a row committed later with a value lower than or equal to the stored watermark is not exported by the following runs. This happens with long transactions, or when the application sets the timestamp before the commit.

For datetime and date columns, set --incremental_safety_lag to the longest delay between a row's timestamp and its commit: rows newer than now - lag are left for the next run. For other columns, choose an incremental column whose values only grow as rows are written (an identity column, or a load timestamp set by the database), or periodically reload the table (see Resetting Watermarks).

Safety Lag​

--incremental_safety_lag INT (seconds, default: 0) holds back the most recent rows of datetime and date columns:

  • d_new = min(MAX(column), now - lag). A row whose value is later than now - lag is exported by a later run, once it is older than the lag.
  • now is read from the source database (CURRENT_TIMESTAMP), because the column holds the source's clock, usually without a time zone. If it cannot be read, LakeXpress uses UTC and logs a warning.
  • For date columns, the cutoff is a date: a lag of 86400 (one day) exports up to yesterday.
  • int, bigint and auto columns are not affected.
MAX(updated_at):        2026-10-29 11:59:30
Source time:            2026-10-29 12:00:00
--incremental_safety_lag 300
d_new:                  2026-10-29 11:55:00

Querying Watermarks​

Inspect tracked watermarks by querying the LakeXpress DB directly. There is no sync_configurations table — incremental state lives across three real tables: incremental_tables (one row per configured table), incremental_watermarks (current state, one row per incremental_tables row), and incremental_export_history (one row per run):

-- View current watermark state for every incremental table in a sync
SELECT
    t.sync_id,
    t.source_schema,
    t.source_table,
    t.incremental_column,
    t.incremental_type,
    t.lower_bound_inclusive,   -- true = :i (>=), false = :e (>, default)
    t.strategy,
    w.last_exported_value,     -- d_old for the next run
    w.d_new,                   -- upper bound used by the last run
    w.last_export_at,
    w.rows_exported
FROM incremental_tables t
LEFT JOIN incremental_watermarks w ON w.incremental_table_id = t.incremental_table_id
WHERE t.sync_id = 'YOUR_SYNC_ID'
ORDER BY t.source_schema, t.source_table;

-- View recent export history (across all watermarks/tables)
SELECT
    h.run_id,
    t.source_schema,
    t.source_table,
    h.d_old,
    h.d_new,
    h.export_mode,       -- 'baseline' or 'incremental'
    h.rows_exported,
    h.status,            -- 'completed', 'failed', 'partial', 'empty'
    h.started_at,
    h.completed_at
FROM incremental_export_history h
INNER JOIN incremental_watermarks w ON w.watermark_id = h.watermark_id
INNER JOIN incremental_tables t ON t.incremental_table_id = w.incremental_table_id
ORDER BY h.completed_at DESC
LIMIT 10;

Resetting Watermarks​

There is no --reset-watermarks CLI flag — no such option exists in LakeXpress. To force a full reload:

# Option 1 (supported): delete and recreate the configuration.
# --confirm is required — without it, `config delete` only prints a dry-run
# summary of what would be deleted and does not touch the database.
./LakeXpress config delete \
  -a credentials.json \
  --lxdb_auth_id lxdb_postgres \
  --sync_id 20251208-xxxxx \
  --confirm

# Then create a new one with the same options: it gets a new sync_id and fresh watermarks
-- Option 2 (manual, not a first-class feature): clear the watermark of a single
-- table directly in the LakeXpress DB. With last_exported_value = NULL, the next
-- sync runs that table as a new baseline. Do not DELETE the row: once the table
-- has run, its export history references it (foreign key).
UPDATE incremental_watermarks
SET last_exported_value = NULL, d_new = NULL
WHERE incremental_table_id = (
    SELECT incremental_table_id
    FROM incremental_tables
    WHERE sync_id = 'YOUR_SYNC_ID'
      AND source_schema = 'sales'
      AND source_table = 'orders'
);

See Also​

Copyright © 2026 Architecture & Performance.