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 versionplan 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}).
append
Section titled “append”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.
Repeated keys
Section titled “Repeated keys”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 laterbatch_sizewindow, 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_bycolumns can’t be in the key, masked, JSON or an array, or repeated, and need amergetable. A higher value is always newer, sonewest_bytakes no direction.planalso refuses anewest_bycolumn of a native type the target’s documentation says it can’t order, or orders inconsistently:ARRAY,STRUCT,GEOGRAPHY,JSONand graph types on BigQuery,MAP,VARIANTandOBJECTon Databricks, semi-structuredVARIANT,OBJECTandARRAYon Snowflake,VariantandDynamicon ClickHouse, andjsonandxmlon Postgres. Pin a portable type for it incolumns, or name another column. A Python table’snewest_bycolumn 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
mergetables only. A table that sets one of them drops the other from its source, so a table can choose"fail"under a source’snewest_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).
NULLs in keys
Section titled “NULLs in keys”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 itsmergetables 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.
Removing copies of a key
Section titled “Removing copies of a key”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__stockWHERE 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.
Rows deleted at the source
Section titled “Rows deleted at the source”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 markedchecks = { 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.
softsets_sqlload_deleted_atto the time the scan started, the columnpostgres_cdcsets. When a key comes back, the merge clears it. The table gets the column when it first loads withdeletes = "soft".hardremoves the row.- Only rows loaded before the key read started are marked (
_sqlload_loaded_atearlier 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_dropof 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-deletesfailsplanwhen none of the selected tables setsdeletes.- A table with
unnest, or one sharing its destination with other tables throughconstant_columns, can’t takedeletes. deletesanddeletes_fromload only into DuckDB, Postgres, Snowflake, MotherDuck, BigQuery and Databricks, the targets their mark statements have run on.planrefuses 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.3sDeletes from a tombstone table
Section titled “Deletes from a tombstone table”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 theunique_keycolumns anddeleted_at, read from where the last load stopped, as for anycursor_column. It loads bydelete_insert, so the range a load reads again replaces its rows rather than repeating them.planlists 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 asdeletessays. A tombstone read in a run marks rows in that run.deletes = "soft"sets_sqlload_deleted_atto 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_atalready loaded. A tombstone that becomes visible later with an earlier time, such as one written by a long transaction, is read only with alookbackthat 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. planfails whendeleted_atand the table’scursor_columndon’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_keycolumn, under the same names;planfails otherwise.deletes_fromneedsdeletesand acursor_column, and the tombstone table can’t also be listed undertables. Underdeletes = "soft"itscursor_columnmust be a timestamp;hardtakes 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.1sOn a postgres_cdc source, deletes is how captured deletes are
applied instead, soft by default.
delete_insert
Section titled “delete_insert”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.
Replacing rows by a key
Section titled “Replacing rows by a key”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_columnis refused withreplace_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. Dropcursor_column, so each run reads the whole source, or usemerge. Acursor_columnset on the source applies to itsreplace_keytables too, and is refused there: set the cursor on each table that uses it instead. - A NULL in a
replace_keycolumn matches a NULL, as in NULLs in keys.null_keys = "fail"refuses a commit holding one instead. load --jsonlists the values each table replaced inreplaced_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
Section titled “replace”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=Trueon 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.
Reloading a table
Section titled “Reloading a table”--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__customerserror: --reload rebuilds app__customers from the whole source, replacing what they hold; confirm it in a terminal, or add --yesA 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.
Several tenants in one table
Section titled “Several tenants in one table”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:
replaceordelete_inserton any of them, and--reload, which would remove the other tenants’ rows;- a
mergewhoseunique_keyholds 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.