Incremental Sync

Sync only changed data using watermark-based incremental exports.

What is Incremental Sync?

LakeXpress tracks a "high watermark" (the highest value in a timestamp or numeric column) and only exports rows above that watermark on subsequent syncs, instead of exporting entire tables every time.

When to Use Incremental Sync

Use incremental sync when:

• Regularly updated tablesSource tables receive frequent inserts or updates
• Frequent syncsExports run multiple times per day or on a schedule
• Cost efficiencyYou want to minimize network and compute usage
• Time-series dataTables have timestamp columns tracking record creation or modification
• Monotonic identifiersTables have an auto-incrementing or sequential key (identity, serial, or sequence-backed ID) that reliably increases with each insert, usable as an int/bigint watermark even when no timestamp column exists

Examples:

• Daily sales order updates to a data lake
• Event log aggregation from production systems
• Time-series metrics collection
• Transaction processing pipelines
• Append-only tables keyed by an auto-increment ID (no modified-at column)

Supported Column Types

Type	Format	Example	Use Case
date	YYYY-MM-DD	2025-01-08	Daily transactions, order dates
datetime	YYYY-MM-DD HH:MM:SS	2025-01-08 14:30:25	Precise time tracking, creation/modification timestamps
int	Numeric sequence	1000001	Monotonic ID columns, batch IDs
bigint	Large numeric sequence	9000000001	High-volume monotonic ID columns
auto	Numeric (treated like int/bigint)	—	Let LakeXpress determine the watermark via MAX(column) without specifying int vs bigint

caution

These five tokens are the only ones accepted. timestamp and integer are not valid — passing either raises IncrementalTableParseError (sync_config_parser.py, VALID_INCREMENTAL_TYPES = {"date", "datetime", "int", "bigint", "auto"}). Use datetime instead of timestamp, and int/bigint instead of integer.

Configuration Syntax

Define incremental tables with --incremental_table:

--incremental_table "schema.table:column:type"

Basic Syntax

schema.tableTableFully qualified table name:columnWatermark columnColumn to track, preferably indexed:typeColumn typedate, datetime, int, bigint or auto

Example:

--incremental_table "sales.orders:created_date:date"

Multiple Tables

Repeat the --incremental_table flag for each table:

--incremental_table "sales.orders:created_date:date"
--incremental_table "sales.returns:return_date:date"

Advanced Syntax (Optional)

schema.tableTable:columnWatermark column:typeColumn type:i | :eBoundoptional@start_valueStart valueoptional!strategyStrategyoptional

:ior:edefault :e

The watermark comparison bound, not an include/exclude toggle for the table. :i is an inclusive lower bound (WHERE column >= watermark), :e an exclusive one (WHERE column > watermark).

@start_value

Start the first (baseline) export at this value: the baseline exports column >= start_value AND column <= d_new, always inclusive whatever :i/:e says. Later runs start from the stored watermark.

!strategydefault append

Loading strategy: append, upsert or delete_insert. See Loading Strategies.

caution

:i/:e only changes the >= vs > comparison used against the watermark column — every table listed under --incremental_table is always synced incrementally. There is no per-table "exclude from incremental sync" option; a table is either listed with --incremental_table (incremental) or left out (fully re-exported every run).

Examples:

# Inclusive lower bound: WHERE created_date >= watermark
--incremental_table "sales.orders:created_date:date:i"

# Exclusive lower bound (default, shown explicitly): WHERE return_date > watermark
--incremental_table "sales.returns:return_date:date:e"

# Set initial watermark
--incremental_table "sales.orders:created_date:date@2025-01-01"

# Use UPSERT/MERGE strategy (updates existing rows)
--incremental_table "sales.orders:created_date:date!upsert"

# Combined: inclusive bound + start value + upsert
--incremental_table "sales.orders:created_date:date:i@2025-01-01!upsert"

Non-incremental Tables

Tables not configured with --incremental_table are fully exported on each sync. Useful for small dimension or reference tables.

--incremental_table "fact.sales:sale_date:date"
--n_jobs 4

• fact.salesExports only the new records since the last watermark
• All other tables in the schemaFully exported on each sync

See Also

• Loading StrategiesAppend vs upsert strategies
• WatermarksWatermark tracking, safety lag, querying, and resetting
• Incremental Sync recipesComplete step-by-step examples and real-world scenarios
• TroubleshootingCommon issues, performance tips, and advanced topics
• Quick Start GuideFirst export walkthrough
• CLI ReferenceAll available options
• Examples & RecipesReal-world usage examples
• Intermediate StorageCloud storage configuration