Skip to content

Concepts

Write strategies

append, merge, delete_insert and replace: how each writes rows and what it needs.

write_strategy says how a load writes rows into the destination table. Set it on a source, for all its tables, or per table; a table’s own setting overrides its source’s. Every table needs one: SQLLoad doesn’t choose a strategy for you.

Strategy Writes Needs
append Inserts the rows read. Nothing.
merge Updates rows whose unique_key matches, inserts the rest. A unique_key: columns, or "{primary_key}".
delete_insert Deletes the cursor range it read, or the rows holding a replace_key value it read, then inserts the rows. A cursor_column or a replace_key, not both.
replace Builds a new table from the whole source, and swaps it in at the end. No cursor_column.

A strategy set on a source covers every table it matches, including those include patterns find, so one source can mix full reloads with incremental tables:

[sources.erp]
kind = "odbc"
connection = "erp"
include = ["pub.*"]
write_strategy = "replace"
[sources.erp.tables."pub.stock_item"]
write_strategy = "merge"
cursor_column = "updated_at"
unique_key = ["company_no", "item_no"]
[sources.erp.tables."pub.order_line"]
write_strategy = "merge"
cursor_column = "last_modified"
unique_key = "{primary_key}"

plan refuses a table that neither it nor its source gives a write_strategy, and a merge table without a unique_key, naming the table and the lines to add:

$ sqlload plan
…
Errors (1)
└── raw.app__page_views no write_strategy
├── reason public.page_views (kind = "postgres"): no write_strategy is set on the table or its
│ source, and SQLLoad doesn't choose one
└── fix add write_strategy = "merge" with unique_key = "{primary_key}" (or the key columns)
under [sources.app] for all its tables, or under
[sources.app.tables."public.page_views"] for this one; or choose "delete_insert" or
"append", which can load a row updated since an earlier load again beside its old
version

plan shows each table’s strategy next to its name, with its key or cursor, such as merge (unique_key: order_id), delete_insert (cursor: dt) or delete_insert (replace_key: le_version; NULLs in keys match). A key from "{primary_key}" shows where it came from, as in merge (unique_key: order_no, line_no ← {primary_key}).

Each run inserts what it reads. With a cursor_column, it reads strictly after the destination’s maximum, so a run with no new rows inserts nothing. A row that arrives later with a value at or below the maximum is not picked up; when that can happen, use delete_insert (no key needed) or merge with a lookback. The first run reads from cursor_start, including it.

append_cursor_inclusive = true reads from the maximum itself instead, as SQLBuild does by default. Each run then inserts the rows at the maximum again, and plan warns about it.

[sources.app.tables."public.events"]
description = "Events, one row per event."
write_strategy = "append"
cursor_column = "event_id"

append refuses a date cursor: rows added later to the last day would be lost, or, read inclusively, the whole day would be inserted again on every run. Use delete_insert, which replaces the last day. A files source is exempt, since it skips the files it already loaded rather than reading after the cursor.

append takes no lookback, which would insert the window again on every run. Use merge or delete_insert for that.

A files source under append loads each file once, by _sqlload_source_file.

merge upserts on unique_key, which every merge table sets: a column or a list of columns,

unique_key = ["order_id", "line_no"]

or "{primary_key}", the primary key the source reports for each table:

unique_key = "{primary_key}"

"{primary_key}" takes only the reported primary key, never a unique constraint, and stands for the whole key, so it can’t be listed with other columns. A table whose source reports no primary key fails plan; for an ODBC source, the error names the keys the database’s INFORMATION_SCHEMA lists, which shows a driver whose key lookup is wrong. Python, files and Google Sheets sources report no keys, so they refuse "{primary_key}": name the columns. A table entry’s columns override its source’s "{primary_key}". A table that doesn’t merge ignores its source’s "{primary_key}", so one source can set it for its merge tables while others replace or append, with or without a key.

With a cursor_column, merge reads from the destination’s maximum (minus any lookback); without one, it reads the whole table each run.

merge isn’t available for files targets. A files source merges one file at a time; see Merging files.

When the rows of one commit hold a unique_key value more than once, merge keeps one row for each value. The destination never gets the key twice, and the load never fails on the warehouse’s own error about it.

  • Without newest_by, the row read last wins, and every row overwrites the table’s row of its key: the last one the source returned, or of files, the last row of the newest file holding it.
  • newest_by = "updated_at" names the column whose higher value is the newer version of a row. Of a commit’s rows sharing a key, the newest wins; a NULL loses to any value, and a tie goes to the row read later. It also applies against the table: a row only overwrites the table’s row of its key when it isn’t older, so older data arriving late, such as a file coming back or a later batch_size window, never replaces newer data. A tie overwrites, any value overwrites a NULL, and a NULL never overwrites a value. The load says how many keys it left alone, as in (12 older rows skipped: newest_by updated_at).
  • newest_by = ["mod_date", "mod_time"] compares in order, for sources that keep a change’s date and time in two columns: the first column decides, and the next breaks a tie. NULLs follow the same rule in each column, two NULLs tying and moving on to the next.
  • newest_by columns can’t be in the key, masked, JSON or an array, or repeated, and need a merge table. A higher value is always newer, so newest_by takes no direction.
  • plan also refuses a newest_by column of a native type the target’s documentation says it can’t order, or orders inconsistently: ARRAY, STRUCT, GEOGRAPHY, JSON and graph types on BigQuery, MAP, VARIANT and OBJECT on Databricks, semi-structured VARIANT, OBJECT and ARRAY on Snowflake, Variant and Dynamic on ClickHouse, and json and xml on Postgres. Pin a portable type for it in columns, or name another column. A Python table’s newest_by column is checked once its rows give it a type, and at the latest when the last rows are in.
  • A NULL in a key column counts as a value: rows whose keys hold the same values and NULLs in the same columns are collapsed like any other repeat, by the same rule.
  • on_duplicate_keys = "fail" refuses a commit that repeats a key instead, naming the key’s columns and examples, before it writes the commit’s rows. Commits before it stay. It compares NULLs in the key too.
  • Set on a source, both apply to its merge tables only. A table that sets one of them drops the other from its source, so a table can choose "fail" under a source’s newest_by, or the reverse.
[sources.app.tables."public.readings"]
write_strategy = "merge"
unique_key = ["sensor_id"]
newest_by = "read_at"

The plan line names the rule when it isn’t the default, as in merge (unique_key: sensor_id; newest by read_at), newest by mod_date, then mod_time, or merge (unique_key: sensor_id; repeated keys fail).

merge matches a NULL in a key column with a NULL. A row keyed ("acme", "bolt", NULL) on unique_key = ["company", "item", "site"] updates the row the table holds for that key, and a commit holding it twice keeps one, as Repeated keys says.

Each target compares a key column that can be NULL its own way, and one the source declares NOT NULL with plain =:

Target A key column that can be NULL
DuckDB, MotherDuck, Snowflake IS NOT DISTINCT FROM
BigQuery (a = b OR (a IS NULL AND b IS NULL)), which BigQuery still runs as a hash join
Databricks <=>
ClickHouse the key’s IN lists the column as isNull(k), ifNull(k, <the type's default>)
Postgres ON CONFLICT on a unique index created NULLS NOT DISTINCT; joins use (a = b OR (a IS NULL AND b IS NULL)), which can use an index on the key

null_keys chooses what a NULL in a key column does:

  • "match" (the default) matches it as above.
  • "fail" refuses a commit holding a NULL in a key column, naming the columns and example keys, before it writes the commit’s rows. Commits before it stay. Set on a source, it applies to its merge tables only.
[sources.erp.tables."pub.stock"]
write_strategy = "merge"
unique_key = ["company", "item", "site"]
null_keys = "fail"

When a key column can be NULL, the plan line says so: merge (unique_key: company, item, site; NULLs in keys match).

A key from "{primary_key}" must be NOT NULL in every column. A source that lets a key column hold NULLs (such as an ODBC driver that reports a unique index on a nullable column as the primary key) can hold several rows with a NULL there that are different rows, and matching NULLs would merge them into one. plan refuses such a table, naming the column; name the unique_key columns instead. This also refuses most BigQuery tables, whose primary keys are NOT ENFORCED and whose key columns are usually NULLABLE.

On a Postgres target, matching NULLs needs Postgres 15 or later.

A table, such as one another tool wrote, may already hold several copies of a row whose key has a NULL. They stay: the next load’s row matches every copy and updates them all, so they hold the same values but are still separate rows. Remove them once, with sqlload load app__stock --reload (not on a Postgres target, which SQLLoad never rebuilds), or by deleting all but the copy from the newest load (_sqlload_load_id is a random id, so order by _sqlload_loaded_at). On DuckDB:

DELETE FROM raw.app__stock
WHERE rowid IN (
SELECT rowid
FROM (
SELECT rowid,
row_number() OVER (
PARTITION BY company, item, site
ORDER BY _sqlload_loaded_at DESC
) AS copy
FROM raw.app__stock
)
WHERE copy > 1
);

PARTITION BY puts NULLs together, so each key, NULLs included, keeps one row.

deletes makes a merge table drop or mark the rows whose key the source no longer holds. A merge alone never sees a deleted row: its key just stops arriving. Postgres, ODBC, Snowflake and BigQuery sources take it; files, Python and Google Sheets sources, which can’t list every key, don’t.

[sources.erp.tables."pub.stock_item"]
write_strategy = "merge"
cursor_column = "updated_at"
unique_key = ["company_no", "item_no"]
deletes = "soft" # or "hard"; unset, rows are never marked
checks = { max_row_drop = "10%" }

A plain load only merges. sqlload load --sync-deletes merges, then reads every key of the source table (from its sql query, when it has one) and marks the table’s rows of the keys it didn’t read. Your orchestrator decides how often to scan, such as a load every 15 minutes and a nightly --sync-deletes, so SQLLoad keeps no schedule.

  • soft sets _sqlload_deleted_at to the time the scan started, the column postgres_cdc sets. When a key comes back, the merge clears it. The table gets the column when it first loads with deletes = "soft".
  • hard removes the row.
  • Only rows loaded before the key read started are marked (_sqlload_loaded_at earlier than the scan’s start), so a row a concurrent load writes while the scan reads is never marked.
  • The scan marks at most checks.max_row_drop of the table’s rows (those not already marked), or 5% without it. A scan that would mark more, as an empty view or a changed permission would, fails the table and marks nothing.
  • --sync-deletes fails plan when none of the selected tables sets deletes.
  • A table with unnest, or one sharing its destination with other tables through constant_columns, can’t take deletes.
  • deletes and deletes_from load only into DuckDB, Postgres, Snowflake, MotherDuck, BigQuery and Databricks, the targets their mark statements have run on. plan refuses them on ClickHouse and files targets rather than run untested statements.

plan shows the cost on the table’s line, and the result shows how many rows were marked:

$ sqlload plan -v --sync-deletes
…
└── raw.erp__stock_item merge (unique_key: company_no, item_no), then a full key scan of pub.stock_item soft-deletes the rows of keys it no longer holds (at most 10% of the table)
…
$ sqlload load --sync-deletes
…
loaded raw.erp__stock_item 1,204 rows (key scan of 48,112 keys: 37 rows soft-deleted) 6.3s

A table too big to scan can take its deletes from a table where the source records them, such as one a trigger or the application writes each deleted key into, with the time:

[sources.erp.tables."pub.stock_item"]
write_strategy = "merge"
cursor_column = "updated_at"
unique_key = ["company_no", "item_no"]
deletes = "soft"
deletes_from = { table = "audit.deleted_items", cursor_column = "deleted_at" }
  • Every load, with no flag, loads the new tombstones into a table of their own beside the merged one, <table>__sqlload_deletes: only the unique_key columns and deleted_at, read from where the last load stopped, as for any cursor_column. It loads by delete_insert, so the range a load reads again replaces its rows rather than repeating them. plan lists it like any other table. It keeps every tombstone; trim it yourself if it grows too big.
  • The tombstone table loads first. Then, once the merge commits, the rows whose key a tombstone holds, deleted at or after the row’s own cursor_column (a coalesced one’s first non-NULL column), are marked as deletes says. A tombstone read in a run marks rows in that run. deletes = "soft" sets _sqlload_deleted_at to the latest such tombstone’s time.
  • A key deleted and then created again stays: its row’s cursor is after the tombstone. A row arriving late, after its tombstone loaded, is marked by the next load, as every tombstone is checked each time. Marking again changes nothing.
  • A marked row the merge reads again, such as one at the cursor’s maximum, is written by the merge and marked again in the same load, with the same time. Between the two it shows as live: under deletes = "hard" it is back in the table from the merge’s commit until the mark’s. This happens only while the source still holds a row its own tombstone says is deleted, such as a late row.
  • Tombstones are read from the latest deleted_at already loaded. A tombstone that becomes visible later with an earlier time, such as one written by a long transaction, is read only with a lookback that reaches it: deletes_from = { table = "audit.deleted_items", cursor_column = "deleted_at", lookback = "1h" } reads the last hour again on every load. Set it longer than the source’s longest transaction.
  • plan fails when deleted_at and the table’s cursor_column don’t compare exactly: both must be timestamps with a time zone, both dates or timestamps without one, both numbers, or both text.
  • The tombstone table must hold every unique_key column, under the same names; plan fails otherwise. deletes_from needs deletes and a cursor_column, and the tombstone table can’t also be listed under tables. Under deletes = "soft" its cursor_column must be a timestamp; hard takes any column that compares with the table’s cursor, such as a sequence number.
$ sqlload plan -v
…
├── raw.erp__stock_item merge (unique_key: company_no, item_no), then soft-deletes the rows whose key audit.deleted_items lists deleted at or after the row's updated_at (its tombstones load first)
└── raw.erp__stock_item__sqlload_deletes delete_insert (cursor: deleted_at)
…
$ sqlload load
…
loaded raw.erp__stock_item 1,204 rows (tombstones: 12 rows soft-deleted) 2.1s

On a postgres_cdc source, deletes is how captured deletes are applied instead, soft by default.

delete_insert replaces a cursor range: it deletes rows in the range it read, [start, end), then inserts the rows. It needs a cursor_column and no key, so it suits tables without one, and lookback windows that re-read recent data. A row updated after it was loaded is loaded again beside its old version unless the old one is in the range, so set it only where rows don’t change or a lookback covers their changes:

[sources.app.tables."public.page_views"]
description = "Page views, one row per view."
write_strategy = "delete_insert"
cursor_column = "viewed_at"
lookback = "2h"

To replace a fixed window around today on every run, such as 7 days back to 7 days ahead, see Reload a window.

On a files source partitioned by the cursor, it replaces whole partitions, and never splits one across commits. A file that changed since it loaded but whose partition lies below the range a load reads fails the table rather than being skipped; see Which files load.

replace_key names columns whose values delete_insert replaces whole. Each commit deletes every row holding a replace_key value that its rows hold, then inserts its rows, in one transaction. The key need not be unique: a value usually has many rows. Rows of other values stay.

[sources.finance.tables."le"]
write_strategy = "delete_insert"
replace_key = ["le_version"]

A load of LE-FY27-P01 with lines A and B, into a table holding P01 with lines A, B and C and P02, leaves P01 with A and B at the new amounts, and P02 as it was.

  • Without a cursor_column, a database or Python source reads every row on each run. A files source reads only new or changed files; see Replacing rows by a key.
  • A cursor_column is refused with replace_key: a run would read only the rows of a value inside its cursor range, while its commit deletes every row of that value, so the rows outside the range would be lost. Drop cursor_column, so each run reads the whole source, or use merge. A cursor_column set on the source applies to its replace_key tables too, and is refused there: set the cursor on each table that uses it instead.
  • A NULL in a replace_key column matches a NULL, as in NULLs in keys. null_keys = "fail" refuses a commit holding one instead.
  • load --json lists the values each table replaced in replaced_keys.

plan refuses a table where replace_key doesn’t apply, naming the fix:

Setting Why it’s refused
replace_key with merge, append or replace Only delete_insert deletes by it.
A replace_key column the table doesn’t load It names no column of the table.
delete_insert with neither cursor_column nor replace_key It has nothing to replace.
replace_key with a cursor_column The cursor reads part of a value’s rows, and the commit deletes all of them.
replace_key on a files target A files target replaces partition folders, not rows.
replace_key with unnest Child tables aren’t replaced with their rows yet.

Each target deletes the rows of the staged keys its own way:

Target The delete
DuckDB, MotherDuck, Snowflake, Postgres DELETE FROM t AS __target USING (SELECT DISTINCT key …) AS __source WHERE …, then INSERT, in one transaction
BigQuery, Databricks One MERGE that inserts every staged row and deletes the rows matching each distinct staged key
ClickHouse The delta’s partition publish displaces the rows whose key is in the delta

replace rebuilds the table from the whole source on every run. It writes a new table, about 1 GB at a time, and swaps it in once at the end, so readers see the old table until then.

  • A Python table that yields no rows into a table that has rows fails rather than emptying it. Set allow_empty=True on the table or source when an empty result is real.
  • On a files target, the new files are written first and the older ones deleted after.
  • On a Postgres target, the table is emptied and refilled in one transaction instead, so it keeps its grants, indexes and dependent views.

--reload rebuilds the selected tables from the whole source, with their current columns and types, whatever their strategy. On a terminal load --reload shows the plan and asks first; elsewhere it needs --yes:

$ sqlload load --reload app__customers
error: --reload rebuilds app__customers from the whole source, replacing what they hold; confirm it in a terminal, or add --yes

A postgres_cdc table is copied again with --resnapshot instead; --reload refuses it. See Reloading. --reload is refused on Postgres targets, which SQLLoad never drops or recreates.

constant_columns gives every row of a table the same values, so one table can hold several sources’ copies of the same data, such as one API pulled for each customer. Put the constant in the unique_key:

[sources.acme]
kind = "postgres"
connection = "acme_db"
include = ["public.orders"]
table_name = "{table}"
constant_columns = { tenant_id = "acme" }
write_strategy = "merge"
unique_key = ["tenant_id", "order_id"]
cursor_column = "updated_at"
[sources.globex]
kind = "postgres"
connection = "globex_db"
include = ["public.orders"]
table_name = "{table}"
constant_columns = { tenant_id = "globex" }
write_strategy = "merge"
unique_key = ["tenant_id", "order_id"]
cursor_column = "updated_at"

Both load into orders, and each tenant’s merge only matches its own rows. Each tenant’s cursor reads from the maximum of its own rows, WHERE tenant_id = 'acme', so a tenant whose data is older than another’s still loads it, and a tenant’s first load reads from its cursor_start. Within one run, tables that share a destination load one at a time. Separate runs at the same time (one per tenant, for example) don’t wait for each other, so run them one after another. Tables sharing a destination must differ in the value of at least one constant column, and a merge table in one that is in its unique_key. Their cursors are scoped only while they share it: adding a constant column to a table that already holds rows leaves those rows NULL in it, so the next run of a shared table starts from its cursor_start again. When a source can’t be read (its database is down, say), SQLLoad can’t tell which tables share with it, so every table with constant_columns reads its cursor within its own values for that run, and plan warns.

A value is a string (text), an integer, a float, a boolean, a TOML date or date and time, or { value = "7", type = "int" } for any other type. A table entry’s constant_columns replaces its source’s, and ${ENV:NAME} works in the values. The columns are added before merge, checks and unique_key see the rows, so they can be in either; they can’t be the cursor_column.

plan refuses, when tables with constant_columns share a destination table:

  • replace or delete_insert on any of them, and --reload, which would remove the other tenants’ rows;
  • a merge whose unique_key holds no constant column, which could overwrite them;
  • state = "logged", whose position is kept per destination table.

A source that already has a column of a constant’s name, ignoring case, fails plan, naming it, before anything is written; exclude the source’s column to load the constant instead. A Python table that yields such a field fails the same way. Files sources are the exception: a column in the files with the same name is replaced, as it always has been, and plan notes it. Files tenants sharing a destination need their files in distinct locations: which files are already loaded is read from the whole destination table. postgres_cdc sources refuse constant_columns.

See Cursors for how a run decides what to read.