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 tables: Source tables receive frequent inserts or updates
• Frequent syncs: Exports run multiple times per day or on a schedule
• Cost efficiency: You want to minimize network and compute usage
• Time-series data: Tables have timestamp columns tracking record creation or modification
• Monotonic identifiers: Tables 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 to track new rows 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.table:column:type

Parameters:

• schema.table - Fully qualified table name
• column - Column to track for watermark (should be indexed)
• type - Column type: date, 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.table:column:type[:i|:e][@start_value][!strategy]

Extended Parameters:

• :i or :e - Watermark comparison bound, not an include/exclude toggle for the table:
  • :i - inclusive lower bound (WHERE column >= watermark)
  • :e - exclusive lower bound (WHERE column > watermark) — this is the default when :i/:e is omitted
• @start_value - Override the initial watermark value
• !strategy - Loading strategy: append (default) or upsert

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.sales - Exports only new records since last watermark
• All other tables in the schema - Fully exported on each sync

See Also

• Loading Strategies - Append vs upsert strategies
• Watermarks - Watermark tracking, safety lag, querying, and resetting
• Incremental Sync recipes - Complete step-by-step examples and real-world scenarios
• Troubleshooting - Common issues, performance tips, and advanced topics
• Quick Start Guide - First export walkthrough
• CLI Reference - All available options
• Examples & Recipes - Real-world usage examples
• Intermediate Storage - Cloud storage configuration