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.

Strategy	Writes	Needs
append	Inserts the rows read.	Nothing.
merge	Updates rows whose unique_key matches, inserts the rest.	A unique_key; a Postgres table’s primary key is used when none is set.
delete_insert	Deletes the cursor range it read, then inserts the rows.	A cursor_column. No key.
replace	Builds a new table from the whole source, and swaps it in at the end.	No cursor_column.

Without write_strategy, a table takes:

Table has	Default
No cursor_column	replace
A cursor_column and a key	merge
A cursor_column and no key	delete_insert

plan shows each table’s strategy next to its name, with its key or cursor, such as merge (unique_key: order_id) or delete_insert (cursor: dt).

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, a column or a list of columns:

unique_key = ["order_id", "line_no"]

A Postgres table’s primary key is the default 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 sources or files targets.

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:

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

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 (not released yet), 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.