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:

• For datetime/date columns, d_new is the current time minus the safety lag — it is not derived from MAX(column) in the source table.
• For int/bigint/auto columns, d_new is MAX(column) queried from the source table.

The generated WHERE clause is:

• Baseline (first run): WHERE column <= d_new
• 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 23:00:00  (= time of run, since safety_lag=0)
Query: WHERE o_orderdate <= '2025-12-31 23:00:00'
Records exported: 1,500,000
Watermark stored: last_exported_value = 2025-12-31 23:00:00

Second Sync (Incremental)

d_old: 2025-12-31 23:00:00  (previous watermark)
d_new: 2026-01-05 08:00:00  (= time of this run, since safety_lag=0)
Query: WHERE o_orderdate > '2025-12-31 23:00:00' AND o_orderdate <= '2026-01-05 08:00:00'
Expected records: 50,000 (new orders since the last run)
Watermark updated: last_exported_value = 2026-01-05 08:00:00

Safety Lag

The --incremental_safety_lag parameter handles late-arriving data by pulling the upper bound (d_new) back from the current time — it does not shift the previous watermark (d_old), which is always used as-is:

--incremental_table "events.raw_events:event_timestamp:datetime"
--incremental_safety_lag 3600

• --incremental_safety_lag INT - Lag in seconds, subtracted from current time when computing d_new (default: 0)

Example with 1-hour lag:

Current time (run start): 2025-01-08 14:00:00
Previous watermark (d_old): 2025-01-08 10:00:00
d_new = current time - safety_lag = 2025-01-08 14:00:00 - 1 hour = 2025-01-08 13:00:00
Query: WHERE event_timestamp > '2025-01-08 10:00:00' AND event_timestamp <= '2025-01-08 13:00:00'

The lag effectively excludes the last hour of source data from every run (it will be picked up on the next run, once it has had time to fully arrive) — it does not extend how far back the query looks.

When to use:

• Asynchronous systems with delayed writes
• Multi-region databases with replication lag
• Event streams with out-of-order processing
• Financial transactions with settlement delays

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 row(s)
-- directly in the LakeXpress DB for a single table. On the next sync,
-- get_or_create_watermark() will create a fresh watermark with
-- last_exported_value = NULL, so that table runs as a new baseline.
DELETE FROM incremental_watermarks
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

• Incremental Sync Overview - Configuration syntax and supported column types
• Incremental Sync recipes - Complete step-by-step examples and real-world scenarios
• Troubleshooting - Fixing watermark issues and missing data