Loading Strategies

Append Strategy (Default)

Inserts new rows into the target table. Suitable when:

• Your target handles duplicates (e.g., external tables)
• You're building an append-only data lake
• You dedup at query time

--incremental_table "events.pageviews:event_time:datetime!append"

Upsert Strategy (MERGE)

Uses MERGE to update existing rows and insert new ones. Requires:

• Primary keys defined on the table (stored in the LakeXpress DB)
• A target publisher that actually wires up MERGE: Databricks, Fabric, and BigQuery
• Managed/internal tables (not external tables)

caution

Snowflake is not in the list above. snowflake_ddl_generator.py contains a generate_merge_ddl() function, but no publisher calls it — snowflake_publisher.py never invokes it, so !upsert has no MERGE path on Snowflake today. MotherDuck is unsupported for the same reason: DuckDB has no MERGE statement, so motherduck_catalog_generator.py only offers generate_delete_insert_ddl() (itself currently uncalled by any publisher — see the note below). Until this is implemented, use !append for Snowflake and MotherDuck targets, or dedupe at query time.

--incremental_table "sales.orders:updated_at:datetime!upsert"

How it works:

1. New data loads into a staging table
2. MERGE runs: matching rows are updated, new rows are inserted
3. Staging table is dropped

Benefits:

• No duplicate rows in target
• Handles both inserts and updates
• Target always reflects current state

Requirements:

• The source table must have a primary key — LakeXpress detects it automatically during schema discovery; MERGE fails if no primary key columns were found
• Use --publish_method internal (the only value that selects managed/internal tables; the CLI's --publish_method choices are external and internal — there is no separate managed value)
• Target is Databricks, Fabric, or BigQuery (see caution above)

Example with BigQuery:

./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:updated_at:datetime!upsert" \
  --incremental_table "public.customers:updated_at:datetime!upsert" \
  --publish_target bigquery_prod \
  --publish_method internal \
  --n_jobs 4

Example with Databricks:

./LakeXpress config create \
  -a credentials.json \
  --lxdb_auth_id lxdb_sqlite \
  --source_db_auth_id source_mssql \
  --source_db_name sales \
  --source_schema_name dbo \
  --target_storage_id s3_01 \
  --incremental_table "dbo.products:modified_date:datetime!upsert" \
  --publish_target databricks_prod \
  --publish_method internal \
  --n_jobs 4

There is no per-target flag like --databricks_table_type or --snowflake_table_type — the generic --publish_method internal flag selects managed/internal tables for whichever --publish_target is configured (Databricks maps internal to a managed Delta table).

Choosing a Strategy

Scenario	Recommended Strategy
Append-only event logs	append
CDC (Change Data Capture)	upsert
Transaction history	append
Customer/product master data	upsert
Time-series metrics	append
Order status updates	upsert

See Also

• Incremental Sync Overview - Configuration syntax and supported column types
• Incremental Sync recipes - Complete step-by-step examples and real-world scenarios
• Troubleshooting - Handling duplicates and other common issues