Loading Strategies

The strategy is set per table, at the end of --incremental_table: !append, !upsert or !delete_insert. It decides how the new rows of each sync are written to the target platform.

Strategy	What it does	Primary key
append (default)	Adds the new rows	Not needed
upsert	Updates the rows that already exist (matched on the primary key) and inserts the others	Required
delete_insert	Deletes the rows whose primary key is in the new batch, then inserts the batch	Required

Support by Target Platform

Target	append	upsert	delete_insert	Table used for upsert / delete_insert
Snowflake	Supported	Supported	Supported	Internal table, created automatically
Amazon Redshift	Supported	Supported	Supported	Internal table, created automatically
Databricks	Supported	Supported	Supported	Managed table: requires --publish_method internal
BigQuery	Supported	Supported	Supported	Native table: requires --publish_method internal
Microsoft Fabric (Warehouse)	Supported	Supported	Supported	Warehouse tables are always internal
MotherDuck	Supported	Supported	Supported	Internal table, created automatically
DuckLake	Supported	Supported	Supported	DuckLake table, created automatically
Microsoft Fabric (Lakehouse)	Supported	Not supported	Not supported	—
AWS Glue	Supported	Not supported	Not supported	—

On Snowflake, Redshift, MotherDuck and DuckLake, the tables that use upsert or delete_insert are always created as persistent internal tables, whatever --publish_method is. The other tables of the same sync keep the table type set by --publish_method.

MotherDuck and DuckLake have no MERGE statement: on these targets, upsert is performed as a delete followed by an insert, which gives the same result for the whole-row Parquet exports LakeXpress produces.

Fabric uses the Warehouse target unless the credentials entry sets "fabric_target": "lakehouse" (see Microsoft Fabric).

LakeXpress checks every table before publishing. If a strategy is not supported by the target, or a table using upsert or delete_insert has no primary key, the publish step stops with an error that names the table.

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

Updates the existing rows and inserts the new ones, so the target always reflects the current state of the source, without duplicates.

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

How it works:

1. The new rows are loaded into a staging table
2. MERGE runs: matching rows are updated, new rows are inserted (on MotherDuck and DuckLake: matching rows are deleted, then all new rows are inserted)
3. The staging table is dropped

Requirements:

• The source table must have a primary key. LakeXpress detects it during schema discovery.
• The target must support it (see the table above). On Databricks and BigQuery, use --publish_method internal.

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 gcs_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 Snowflake (the upsert tables are created as internal tables, even with the default external publish method):

./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 snowflake_prod \
  --n_jobs 4

Delete-Insert Strategy

Deletes the rows of the target whose primary key appears in the new batch, then inserts the whole batch. Each changed row is replaced as a whole.

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

Same requirements as upsert: a primary key on the source table, and a target that supports it.

Choosing a Strategy

Scenario	Recommended Strategy
Append-only event logs	append
Transaction history	append
Time-series metrics	append
CDC (Change Data Capture)	upsert
Customer/product master data	upsert
Order status updates	upsert
Rows rewritten as a whole by the source	delete_insert

See Also

• Incremental Sync OverviewConfiguration syntax and supported column types
• Incremental Sync recipesComplete step-by-step examples and real-world scenarios
• TroubleshootingHandling duplicates and other common issues