Load modes

The five modes#

An ETL task carries exactly one load_mode. The enum members are full_refresh, append, upsert, incremental and historical_upsert — those exact strings are what the API accepts and what the task record stores.

Load modes apply to the two writing task types (sql_extract_load and table_copy, plus the destination half of sql_transform). Only the ETL engine families have write adapters: the PostgreSQL family (postgres, cockroachdb) and the MySQL family (mysql, singlestore). Any other engine on the destination side raises an unsupported-engine error before any SQL runs.

Note. For sql_extract_load and table_copy the destination table must already exist — operators own destination schemas. sql_transform is the one path that creates its target.

Summary#

full_refresh#

Opens one transaction, clears the destination, then inserts every batch and commits. Readers see either the whole old dataset or the whole new one.

How the clear is issued differs by family, and the difference matters:

• PostgreSQL family — TRUNCATE TABLE <destination> inside the transaction. TRUNCATE is transactional on PostgreSQL, so a failure part-way rolls back to the previous contents.
• MySQL family — DELETE FROM <destination>, not TRUNCATE. TRUNCATE is DDL on MySQL and causes an implicit commit, which would leave the table empty if the reload then failed. DELETE keeps the whole operation in one transaction.

No staging or temp table is involved for sql_extract_load / table_copy: the destination is the operator's own object and the platform preserves its identity — grants, foreign keys, views and triggers all survive, which a staging-and-swap would destroy. The one place a staging table is used is the runner-managed create-table-as path inside sql_transform on the MySQL family, where the new dataset is built in a staging table and swapped, precisely because MySQL cannot do that DDL transactionally.

Existing rows: all removed. Rows the new read no longer returns are gone.

append#

INSERT INTO <destination> (...) VALUES ... per batch. No delete, no key lookup, no watermark. Re-running the task inserts the same rows a second time, so append is only correct when the source hands you strictly new records — or when the task is driven by incremental semantics instead.

Existing rows: untouched.

upsert#

Requires primary_key_columns — one or more destination columns that identify a row. The write is a single keyed statement per batch:

• PostgreSQL family — INSERT INTO <destination> (...) VALUES ... ON CONFLICT (<primary_key_columns>) DO UPDATE SET .... When there are no non-key columns left to set, it degrades to DO NOTHING.
• MySQL family — INSERT INTO <destination> (...) VALUES ... ON DUPLICATE KEY UPDATE <col> = VALUES(<col>).

The destination must actually enforce uniqueness on those columns (primary key or unique index); without a constraint neither engine can detect the conflict and every row is inserted as new.

upsert also accepts incremental_column together with lookback_seconds. When both are set, the source read is narrowed to rows at or after watermark - lookback_seconds, so late-arriving or restated rows inside the window are re-read. This is safe precisely because the write is keyed: a re-read row overwrites itself rather than duplicating.

Existing rows: matching keys overwritten, non-matching keys left alone. Nothing is ever deleted.

incremental#

Requires incremental_column. The runner reads the task's stored watermark and appends a predicate to the source query:

SELECT ... FROM <source> WHERE <incremental_column> > :watermark

After the batch lands, the watermark advances to the maximum incremental_column value among the rows just read. The first run has no watermark and therefore reads everything.

The write itself is an INSERT, unless primary_key_columns is also configured — in that case the same keyed statement as upsert is used, which makes a re-read idempotent.

In incremental mode the read boundary is exact — there is no look-back window. If your source can insert rows with a timestamp earlier than one already committed (clock skew, long transactions), use upsert with incremental_column and a lookback_seconds window instead, so the overlap is re-read and merged.

Existing rows: untouched without a key; overwritten on key match when primary_key_columns is set.

historical_upsert (SCD type 2)#

Requires natural_key_columns (what identifies the business entity) and tracked_columns (which column values, when they change, constitute a new version). A current row is never overwritten: it is expired and a new version row is inserted.

Per natural key, the runner compares a hash of the tracked column values against the current row's values and takes one of three actions:

The run reports rows_inserted, rows_expired, rows_unchanged and rows_failed.

historical_upsert is the one mode that materialises the source result rather than streaming it, because the diff has to deduplicate by natural key and compare against the destination's current rows. Every other mode reads through a server-side cursor and writes batches as they arrive, so memory stays bounded no matter how large the result is.

History columns#

history_columns_mode selects how the four bookkeeping columns are managed. The enum members are auto and custom.

• auto — Abrq DIP owns the column names and adds them if missing:

  Purpose	Column
  Current-version flag	is_current
  Version start	row_effective_dt
  Version end	row_expired_dt
  Tracked-column hash	record_hash

• custom — you map existing destination columns via is_current_column, effective_date_column and expired_date_column. No hash column is required in this mode; the hash is recomputed from tracked_columns on every run either way.

Existing rows: never overwritten. The table grows one row per version.

Task-level error handling#

Two fields on the task govern what a run does when something goes wrong mid-stream. They are independent of the load mode.

Warning. ETL uses its own two-value drift vocabulary. It is not the three-policy vocabulary the ingestion families use, and ETL never issues ALTER TABLE against an operator-owned destination. See Schema drift policies.