Incremental Sync

Commands use Linux syntax. See Examples & Recipes for the Windows equivalents and the arguments every sync needs.

Each sync exports only the rows added since the previous one, tracked with a watermark column per table. How it works: Incremental Sync.

Step by Step

Step 1: Create Configuration with Incremental Tables

./LakeXpress config create \
  -a credentials.json \
  --lxdb_auth_id lxdb_mssql \
  --source_db_auth_id source_postgres \
  --source_db_name tpch \
  --source_schema_name tpch_1_incremental \
  --target_storage_id s3_01 \
  --incremental_table "tpch_1_incremental.orders:o_orderdate:date" \
  --incremental_table "tpch_1_incremental.lineitem:l_shipdate:date" \
  --incremental_safety_lag 3600 \
  --generate_metadata \
  --n_jobs 4 \
  --fastbcp_p 2

Parameter	Meaning
--incremental_table "tpch_1_incremental.orders:o_orderdate:date"	Track orders by o_orderdate column
--incremental_table "tpch_1_incremental.lineitem:l_shipdate:date"	Track lineitem by l_shipdate column
--incremental_safety_lag 3600	Subtract 1 hour from the current time when computing the upper bound each run (handles late-arriving data)
--generate_metadata	Generate CDM metadata for exported tables

Step 2: Run First Sync

./LakeXpress sync \
  -a credentials.json \
  --lxdb_auth_id lxdb_mssql \
  --sync_id <SYNC_ID>

First sync behavior:

• Exports all rows up to the upper bound (o_orderdate <= d_new / l_shipdate <= d_new), where d_new is the run's start time minus the safety lag (not necessarily the actual highest value in the column)
• Records d_new as each table's last_exported_value watermark
• Non-incremental tables are fully exported
• Stores watermarks in the LakeXpress DB

Step 3: Subsequent Syncs

./LakeXpress sync \
  -a credentials.json \
  --lxdb_auth_id lxdb_mssql \
  --sync_id <SYNC_ID>

Subsequent sync behavior:

• Loads previous watermarks (d_old) from the LakeXpress DB, unchanged
• Computes a new upper bound d_new = current time - safety lag (for date/datetime columns)
• Exports rows where o_orderdate > d_old AND o_orderdate <= d_new
• Exports rows where l_shipdate > d_old AND l_shipdate <= d_new
• Updates each watermark's last_exported_value to the d_new used for that run
• Non-incremental tables are fully exported again

Daily Order Processing

./LakeXpress config create \
  -a credentials.json \
  --lxdb_auth_id lxdb_postgres \
  --source_db_auth_id source_postgres \
  --source_db_name ecommerce \
  --source_schema_name public \
  --target_storage_id s3_01 \
  --incremental_table "public.orders:created_at:datetime" \
  --incremental_table "public.order_items:created_at:datetime" \
  --publish_target snowflake_prod \
  --n_jobs 4

# Run daily via cron
./LakeXpress sync \
  -a credentials.json \
  --lxdb_auth_id lxdb_postgres \
  --sync_id <SYNC_ID>

• Day 1: Exports 1,000,000 orders (full load)
• Day 2: Exports ~5,000 new orders (incremental)
• Day 3: Exports ~4,800 new orders (incremental)
• Other tables (customers, products) fully exported daily

Event Log Ingestion

./LakeXpress config create \
  -a credentials.json \
  --lxdb_auth_id lxdb_mssql \
  --source_db_auth_id source_postgres \
  --source_db_name analytics \
  --source_schema_name events \
  --target_storage_id s3_01 \
  --incremental_table "events.pageviews:event_time:datetime" \
  --incremental_table "events.clicks:event_time:datetime" \
  --incremental_table "events.conversions:event_time:datetime" \
  --incremental_safety_lag 600 \
  --sub_path production/events \
  --n_jobs 8 \
  --fastbcp_p 4

# Run every 10 minutes
./LakeXpress sync \
  -a credentials.json \
  --lxdb_auth_id lxdb_mssql \
  --sync_id <SYNC_ID>

• Ingests events from multiple tables continuously
• 10-minute safety lag handles processing delays

Time-Series Metrics

./LakeXpress config create \
  -a credentials.json \
  --lxdb_auth_id lxdb_sqlite \
  --source_db_auth_id source_postgres \
  --source_db_name monitoring \
  --source_schema_name metrics \
  --target_storage_id azure_01 \
  --incremental_table "metrics.cpu_usage:recorded_at:datetime" \
  --incremental_table "metrics.memory_usage:recorded_at:datetime" \
  --incremental_table "metrics.disk_io:recorded_at:datetime" \
  --incremental_safety_lag 300 \
  --n_jobs 4 \
  --generate_metadata

# Run every 5 minutes
./LakeXpress sync \
  -a credentials.json \
  --lxdb_auth_id lxdb_sqlite \
  --sync_id <SYNC_ID>

• High-frequency metric collection to Azure storage
• Each sync captures the last 5+ minutes of data

Publish to Snowflake

./LakeXpress config create \
  -a credentials.json \
  --lxdb_auth_id lxdb_postgres \
  --source_db_auth_id source_postgres \
  --source_schema_name sales \
  --incremental_table "sales.orders:order_date:date" \
  --incremental_table "sales.returns:return_date:date" \
  --target_storage_id s3_01 \
  --publish_target snowflake_prod \
  --publish_method internal \
  --publish_schema_pattern "{schema}_incremental" \
  --n_jobs 4

./LakeXpress sync \
  -a credentials.json \
  --lxdb_auth_id lxdb_postgres \
  --sync_id <SYNC_ID>

Creates Snowflake tables continuously updated with new data. Non-incremental tables are fully exported and published on each sync.

Debug a Sync

Run with --log_level DEBUG for detailed logs:

./LakeXpress sync \
    -a credentials.json \
    --lxdb_auth_id lxdb_postgres \
    --sync_id <SYNC_ID> \
    --log_level DEBUG

See Also

• Incremental Sync: configuration syntax and supported column types
• Loading Strategies: append, upsert and delete-insert
• Watermarks: watermark tracking and safety lag