Write strategies

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}"

From 0.46.0, 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

Before 0.46.0, a table without write_strategy took replace without a cursor and merge with a cursor and a key, and merge used the primary key its source reported. Neither showed in the config, so a driver that reported the wrong key, or none, changed how a table was written without anyone seeing it. Before 0.44.0 a table with a cursor and no key took delete_insert, which loads a row updated after an earlier load again, as its old version is outside the cursor range it replaces; with a driver that reports no key for a table that has one, as MariaDB Connector/ODBC 3.1 can, that happened unnoticed.

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}).

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 SQLLoad did before 0.25.0 and 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

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

From 0.39.0, 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 dedup_sort, the row read last wins: the last one the source returned, or the last row of a file.
• With dedup_sort = { updated_at = "desc" }, the row with the highest updated_at wins, and "asc" keeps the lowest. A NULL loses to any value, and a tie goes to the row read later. dedup_sort takes one column, which can’t be in the key, masked, JSON or an array.
• From 0.51.0, 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. Before 0.51.0 each of them was inserted.
• Rows are ranked within one commit. A load that commits more than once, by batch_size window, about every 1 GB, or one file at a time, merges each commit on its own. So with dedup_sort, a later batch in the same load still overwrites an earlier one, whatever its dedup_sort value. plan warns when a table can commit more than once, and batch_size = "none" loads a database or Python table in one commit.
• 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. From 0.51.0 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 dedup_sort, or the reverse.

[sources.app.tables."public.readings"]
write_strategy = "merge"
unique_key = ["sensor_id"]
dedup_sort = { read_at = "desc" }

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

NULLs in keys

From 0.51.0, 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. Before 0.51.0 a NULL matched nothing, as NULL = NULL is never true in SQL, so such a row was inserted again by every load that read it.

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

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.

Tables loaded before 0.51.0

A table merged before 0.51.0 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.

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

From 0.51.0, 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

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.

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__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.

Several tenants in one table

From 0.37.0, 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.