Skip to content

Reference

Troubleshooting

Common errors from plan and load, what they mean, and the fix for each.

Most problems show up in sqlload plan, under Errors, with the fix. The table below lists the common ones; each message is copied from SQLLoad.

Message Fix
error: environment variable PGPASSWORD is not set Set the variable a ${ENV:NAME} in sqlload.toml refers to.
load_method is required: how should data get into Snowflake? Add a load_method to the Snowflake connection. See Snowflake.
n_free: numeric without a precision has no fixed scale; pin a type such as "decimal(38,10)" or "text" Pin the column in columns, such as columns = { amount = "decimal(38,18)" }.
columns "total" is not a column of the source table (names are case-sensitive); did you mean "email"? Fix the column name, or move the entry to the right table.
total has type numeric(12,2), which cannot be read as int Pin a type the source column can be read as, usually the same or wider.
public.events (kind = "postgres"): no write_strategy is set on the table or its source, and SQLLoad doesn't choose one Every table needs a write_strategy. Add the line the error shows under the source, for all its tables, or under the table’s entry. See Write strategies.
write_strategy = "merge" needs a unique_key: the key columns, such as unique_key = ["id"], or unique_key = "{primary_key}" ... A merge table names its key. Set unique_key to the columns, or to "{primary_key}" for the primary key the source reports.
items (kind = "odbc"): unique_key = "{primary_key}", but the source reports no primary key for the table. ... Name the key columns. An ODBC table whose error adds that INFORMATION_SCHEMA lists a primary key has one its driver didn’t report; see MariaDB Connector/ODBC 3.1.
an ODBC fetch may hold 954 MiB, more than the 176 MiB a fetch gets of each table's 703 MiB share of the memory budget; ... A warning. Lower max_batch_memory on the named source, or raise memory_limit. See Memory.
the 600 MiB memory budget leaves the tables 11.7 MiB after DuckDB's 488 MiB, under the 64 MiB one table loads in; raise memory_limit to at least 653MiB plan fails when the memory budget can’t hold one table. Raise memory_limit or pass --memory-limit.
attachments: the rows of one k value hold 300 MiB of values over 85 KiB, which a delete_insert replaces in one commit, ...; raise memory_limit to at least 23.5GiB One key, cursor value or partition holds more large values than DuckDB commits at once. Raise memory_limit as named, or choose a finer replace_key. See Large rows in DuckDB.
documents: its last row group would hold 366 MiB of strings, 366 MiB of them values over 85 KiB from this load (rows of about 1 MiB each), ... DuckDB couldn’t checkpoint the table’s open row group within its memory_limit. Raise memory_limit as named. See Large rows in DuckDB.
raw.attachments stopped: the load held 1 GiB of its 1.1 GiB memory budget (91%); ... The watchdog stopped the table holding the most large values at 90% of the budget. Raise memory_limit, or lower batch_size or concurrency.
stopped: the run held 1.1 GiB of its 1.1 GiB memory budget (96%), and from 95% it stops as no table held large values to stop; ... The watchdog stopped the whole run: the running tables failed, the others didn’t start, and the load exited 1, not retryable. Raise memory_limit, or lower concurrency.
column body holds a value of 9000 bytes, longer than max_text_size = 8000; ... Raise max_text_size on the ODBC source, or remove it: a long column is read value by value without it. See Value sizes.
write_strategy = "replace" reloads the whole table, so it takes no cursor_column Remove cursor_column, or choose merge, delete_insert or append.
lookback re-inserts the window on every run with append; use delete_insert (no key needed) or merge Change the strategy, or drop lookback.
merge needs a table format to update rows in place; a files target takes append, replace or delete_insert Use one of those strategies on a files target.
... was rewritten in place since it was loaded ...; load it again with --reload, or use delete_insert on a partition cursor A files source file changed after it loaded under append. Reload, or partition the source.
--reload rebuilds ... confirm it in a terminal, or add --yes Add --yes when running without a terminal.
tags: kind = "postgres_cdc" streams text[] values as the source wrote them ...; pin it with columns = { tags = "text" }, or exclude it Pin array and json columns to text. See Postgres changes.
slot sqlload_app is gone ... or slot sqlload_app lost WAL it had not confirmed ... Run sqlload load --resnapshot. See The slot and WAL.
column tier of app__customers was added with a default ... or ... now loads column region, which the table already had at its snapshot ... A schema change the stream can’t carry. Run sqlload load --resnapshot. See Schema changes.
publication app_changes does not publish "public"."refunds"; add them with ALTER PUBLICATION ... A table joined the source. Add it to the named publication as the tables’ owner. See Adding tables.
rows captured before slot sqlload_app was recreated are still in app__refunds ... A table rejoined the source. Run sqlload load --resnapshot, or drop its destination table and run sqlload apply. See Adding tables.
a files target takes kind = "postgres_cdc" only with cdc_mode = "changelog" ... Set cdc_mode = "changelog". See Into Parquet files.
a postgres_cdc table is copied again with --resnapshot, not --reload Use sqlload load --resnapshot for the captured tables, and --exclude the postgres_cdc source to reload the others. See Reloading.
cannot connect to Postgres at ... under [sources.NAME] That source’s server can’t be reached. Commands that don’t select it still run; see Selecting tables.
load --follow follows one postgres_cdc source and nothing else; ... is not captured, so load them without --follow, or leave them out: --exclude ... Select a postgres_cdc source, --exclude the other tables, or load them without --follow.
load --follow did not start: it follows only when every selected source and table plans ... Fix the errors the plan lists above it, or leave them out as the line says: --exclude another source, or exclude the table under its postgres_cdc source. See Keep streaming.
following stopped: planning again after ... found errors ... A table stopped planning while it followed, such as one that lost its primary key. Fix it, or exclude it under the source, and load again. Nothing unwritten was confirmed.
source app does not follow while app__orders is blocked: its tables share one slot ... load --follow follows a whole postgres_cdc source. Select the source, not some of its tables. See Keep streaming.
[sources.app] batch_interval must be at least 1s ... Set batch_interval to 1s or more.
Postgres query failed: permission denied for database postgres (SQLSTATE 42501) The user can’t create the publication. Create it as the tables’ owner and name it with publication. See Requirements.
Python source shop uses connection wh, which target dev loads into Give the Python source its own connection; table functions never get warehouse credentials.
Python table orders is declared twice (...); table names are unique across the project Rename one of the functions, or pass name= to the decorator.
write_strategy = "append" cannot follow the date cursor visit_day: ... append refuses a date cursor. Use delete_insert or merge. See append.
append_cursor_inclusive = true reads from the maximum order_id itself, so every run appends the rows at that value again ... A warning. Leave append_cursor_inclusive unset to read after the maximum.
[sources.app] reads legacy-db.example.com:5432 in plain text: ... A warning: the server doesn’t offer TLS and sslmode = "prefer" fell back. Enable TLS on the server and set sslmode = "verify-full", or set sslmode = "disable" to accept it. See TLS.
error performing TLS handshake: invalid peer certificate: UnknownIssuer The server’s certificate isn’t signed by a CA SQLLoad trusts. Set sslrootcert to its CA.
invalid peer certificate: certificate not valid for name "..." verify-full connected to a host the certificate doesn’t name. Use the host name in its subject alternative names.
error performing TLS handshake: server does not support TLS require or verify-* against a server without TLS.
sslcert and sslkey must be set together Set both, or neither.
the function returned 24 rows outside the window it was asked for, ... A Python table’s function returned rows its window doesn’t hold. The message names the likely cause and its fix: send ctx.cursor_value and ctx.cursor_end with the request, set timezone to the zone the API reads times in, or, for an API that filters by whole days, set on_cursor_out_of_range = "skip". See Rows outside the window.
  • It doesn’t match include, or matches exclude.
  • It has a column matching skip_tables_with_columns; plan lists it under Skipped.
  • It appeared after the source’s first load, and new_tables = "skip". Name it exactly in include.

plan shows each table’s route. Warnings name routes that are much slower than the usual one, such as copy_to_internal_stage, hmac-masked files, or load_method = { type = "insert" }. For files into Snowflake, an external stage over the files’ bucket lets Snowflake read them in place. See Load methods.

For an ODBC table, plan’s fetch line says when a table is read value by value, which is slower than bulk. See Memory.

Finding a source that uses too much memory

Section titled “Finding a source that uses too much memory”

Every load ends with its peak memory: peak memory in the summary, and peak_memory_bytes in the JSON report and the run_finished event. It’s the peak for the whole run, all tables together, ODBC drivers, DuckDB and Python sources included. Under the Python package, it includes the Python interpreter.

To find the table behind it, run once on Linux with --memory-per-table. It loads one table at a time and gives each table’s peak:

$ sqlload load --memory-per-table
…
loaded raw.customers 500 rows 0.0s peak memory 79.8 MiB
loaded raw.orders 2,000,000 rows 8.5s peak memory 245 MiB
✓ Completed 2 loaded, 2,000,500 rows, 8.6s
├── slowest raw.orders 8.5s
└── peak memory 245 MiB

A table’s figure includes what the process already held when it started, so compare the tables of one run. Then, for the table it names:

  • Lower concurrency, or pass --concurrency, as tables loading at once add up.
  • Set a smaller batch_size on a cursor table, so it reads and commits smaller windows, one at a time.
  • On an ODBC source, lower max_batch_memory, its fetch buffer. When wide text or binary columns hold short values, lower long_column_threshold so they’re read value by value, which holds each value’s real size instead of the column’s whole width, but is slower.

Run it again with --memory-per-table to check. On macOS and Windows, load the table on its own (sqlload load TABLE) and compare its run’s peak with the others’.

A cursor table without an ordered read commits once for the whole read. For a large first load, set batch_size (such as batch_size = "1d") with cursor_start, so it commits window by window. A delete_insert, append or merge into ClickHouse still publishes once per run (a merge also every 10 million rows); split a long first load into runs with --end-cursor instead. See Batches.

sqlload load --reload --yes TABLE rebuilds it from the whole source. Deleting the destination table works too: SQLLoad keeps no other state, so the next run loads everything again. A postgres_cdc table starts over with sqlload load --resnapshot; see Re-snapshot.

--log-format json writes structured logs to standard error. sqlload plan --json prints the plan as one JSON document.