Skip to content

Sources

Python API

The sqlload package's API for Python sources: declaring tables and their settings, what a table function yields, the run's context, sl.http jobs, logged state and other functions.

The sqlload package declares Python sources: tables whose rows a Python function yields. To set them up, and for HTTP APIs, see Load from Python and HTTP APIs.

import sqlload as sl
# A source: the settings its tables share.
shop = sl.source("shop", write_strategy="merge")
# A table: one function, one table.
@shop.table(unique_key="order_id", cursor_column="updated_at")
def orders(ctx: sl.Context):
"""Orders from the shop, one row per order."""
# ctx.cursor_value is where the last load ended.
for page in fetch_orders(updated_after=ctx.cursor_value):
# Dicts, Arrow data, JSON bytes or an sl.http job.
yield page
Name What it is
sl.source A source: its connection, target and the settings its tables share.
@source.table A table, from a function. Declaring it never runs it; the function runs when the table loads.
ctx The run’s context: ctx.cursor_value and ctx.cursor_end for the cursor window, ctx.state for a logged position, ctx.client and ctx.connection.
What a function yields Dicts, Arrow data, sl.json and sl.ndjson bytes, sl.http jobs and sl.checkpoint positions, in any mix.
sl.http REST jobs that run entirely in Rust.
Function Returns
sl.source(name, *, description=None, **settings) A Source. settings are its connection, destination, schema and the defaults its tables share.
@source.table or @source.table(*, name=None, description=None, **settings) A Table attached to the source.
@sl.table or @sl.table(...) A standalone Table, loaded into the target’s schema; it sets its own write_strategy.
@source.client Declares a client the source’s tables share as ctx.client, built once per run.

A table’s description, its destination table’s comment, is description=, or else the function’s docstring. Both are optional. Unknown settings raise TypeError when the module is imported.

Setting On Meaning
write_strategy, unique_key, on_duplicate_keys, null_keys, newest_by, cursor_column, cursor_start, lookback, append_cursor_inclusive, batch_size, on_cursor_out_of_range source or table As in sqlload.toml, such as newest_by="updated_at", or lookback=1 on an integer cursor. Python tables don’t take lookahead or lookback_from. Every table needs a write_strategy, from its source or its own decorator, and a merge table names its unique_key columns: a Python source reports no primary key, so "{primary_key}" is refused.
contract, on_schema_change, auto_widen, type_change_rebuild source or table See Contracts.
infer_types source or table False loads every column of dict rows as text, except pinned columns, objects and lists; Arrow tables and DataFrames keep their types. See Loading every column as text.
on_nonfinite source or table "null" loads NaN and infinite floats in dict rows as NULL; the default, "keep", keeps them. See NaN and infinities.
checks source or table Checks on the staged rows, such as {"unique": ["order_id"]}. See Checks before publish.
constant_columns source or table Columns with the same value in every row, such as {"tenant_id": "acme"} or {"shard": {"value": "7", "type": "int"}}; a table’s replace its source’s. A yielded field of the same name, ignoring case, fails the table before anything is written. See Several tenants in one table.
allow_empty source or table Let replace empty a table when the function yields no rows.
state source or table "logged" to keep a position in ctx.state. See Logged state.
send source or table "at_commit" or "per_batch": when the rows reach the target. See Sending rows at commit.
columns table Pinned column types, such as {"total": "decimal(18,2)"}.
flatten table Objects whose keys load as <object>__<key> columns: dotted paths, or "all". See Flattening objects.
naming, compat, on_duplicate_names source or table As in sqlload.toml. A field yielded as rowNumber loads as row_number; naming="source" keeps it. See Naming.
rename source or table A map such as {"orderId": "order_ref"}, the name an @sl.rename function was registered under, or the function itself (rename=erp_names). See rename.
unnest, max_depth table As in sqlload.toml: the list fields whose items become child tables, such as unnest=["items"] or "all", and how many levels deep. See Nested data.
mask, exclude_columns, allow_columns table (the last two also source) As for other sources.
table_name, destination_path source or table Destination name template, and folder in a files target.
connection, destination, schema, mask_key, parallel_tables source Its connection, target, schema, HMAC key, and whether tables sharing a client may load at once.

A table function is a generator (or returns an iterable). It can yield, in any mix:

Yield Becomes
A dict, or a list of dicts Rows, typed like JSON.
Arrow data: a pyarrow table or batch, a polars or pandas DataFrame Rows, keeping their Arrow types.
sl.json(data, path=None, *, fields=None, keep_record=None) Rows from a JSON document: an array of objects (at a dotted path, such as "data.items"), or objects. Parsed in Rust. See Fields for fields and keep_record.
sl.ndjson(data, *, fields=None, keep_record=None) Rows from newline-delimited JSON. Parsed in Rust.
An sl.http job Rows the job fetches, entirely in Rust. See HTTP APIs.
sl.checkpoint(position) A logged position covering the rows yielded before it.

Dict values are typed like JSON: numbers, booleans and text keep their types, and nested dicts and lists become JSON columns. A list column pinned as array(T) loads as an array instead. Also:

Python value Column type
Decimal decimal, with room for at least 19 digits before the point. More than 38 digits in all become text; NaN and infinity, NULL.
date date
naive datetime timestamp_ntz
datetime with a UTC offset timestamp_tz (a column mixing both becomes text)
bytes, bytearray, memoryview binary. A column mixing them with text, pinned as text, or under infer_types = false holds them as base64 text, as does a value nested in a dict or list.
Enum Its value.
dataclass A JSON object.
numpy numbers Numbers.
Other values, including subclasses such as pandas.Timestamp Text.

columns overrides inference. A first load that yields no rows creates nothing unless columns are declared. Inferred columns don’t change type by themselves; see Inferred types.

A float that is NaN, inf or -inf keeps its value in a float column, as it does in the lists of a column pinned array(float). Where the column can’t hold it, the table fails naming the column and the row: a column pinned as a decimal or an integer, a nested dict or list that loads as json, which JSON can’t hold, and an array(float) into Snowflake, which holds arrays as JSON. Set on_nonfinite="null" on the table or source to load them as NULL, in nested values too; plan then says so in the table’s schema lines. Arrow tables and DataFrames keep their values either way.

Nested values stay one JSON column (VARIANT on Snowflake) unless the table sets flatten:

from decimal import Decimal
import sqlload as sl
store = sl.source("store", write_strategy="replace")
@store.table(
description="Orders",
flatten=["customer", "customer.address"],
columns={"total": "decimal(18,2)"},
)
def store_orders(ctx):
yield {
"order_id": 7,
"total": Decimal("12.50"),
"customer": {"country": "GB", "address": {"city": "Leeds"}},
"line_totals": {"sku-14": 12.5},
}

That loads customer__country and customer__address__city, and line_totals as JSON. Dicts, sl.json bytes and Arrow structs flatten the same way, and a typed child of an Arrow struct or a Decimal or date in a dict keeps its type. Paths, "all", json pins and what plan shows are in Flattening objects.

Without fields, each key of a JSON row object loads as a column under its own name, as written: a key updated-at makes a column updated-at, which the destination quotes, so queries must quote it too. fields loads only the values it names, under the names it gives. It works on sl.json, sl.ndjson, and an sl.http job’s paginate and fan_out, all in Rust:

@shop.table(
description="Accounts.",
write_strategy="merge",
unique_key="id",
cursor_column="updated_at",
columns={"updated_at": "timestamp_tz"},
)
def accounts(ctx):
return api.paginate(
"/accounts",
paginator=sl.offset(limit=50),
select="$.items",
fields={
"id": sl.field("$.id", required=True),
"account_code": "$.code",
"updated_at": "$.updated-at",
"city": "$.address.city",
},
keep_record="raw_json",
)
  • A path is $ followed by keys, such as $.address.city. A key may hold any character but ., [ and ]. $ alone is the whole object. This is the path syntax for every field.
  • A missing key, or a path that runs through a value that isn’t an object, loads NULL. sl.field(path, required=True) fails the table instead, naming the row and the path.
  • keep_record adds the whole row object as a JSON column. It needs fields, and its name can’t be a field’s. A table with a mask refuses it, because the record would carry the masked values.
  • Types are inferred as for any JSON. Pin them with the table’s columns, which plan shows. columns, flatten and mask name the projected columns, not the source’s keys.
  • A table with a cursor_column must load it, as a field, as keep_record, or as a column that flattening a field makes. A job whose fields leave it out fails before any request; sl.json and sl.ndjson data fails before it is parsed.
  • A fan-out’s over picks keys with its select and takes no fields.
  • sl.field(path, parse=...) converts the value as a table’s parse does, and takes the same entry: "epoch_s", "epoch_ms" or "epoch_us", or {"timestamp": [...]} or {"date": [...]} with a format or a list of formats tried in turn, and "tz". For example, sl.field("$.updated", parse={"timestamp": ["iso", "%m/%d/%Y %H:%M"], "tz": "Europe/Paris"}). A timestamp loads as timestamp_tz, a time without an offset read in tz, else the table’s timezone, else UTC. A value outside the years 1 to 9999 doesn’t convert. A value that doesn’t convert fails the table, naming the row, the field and the value, or loads NULL with on_error="null". A table’s parse reaches any column without listing it in fields. Other conversions belong in a transform.

A path in fields (a plain string, sl.field or sl.parent), in key_path and in sl.json(..., fields=) takes exactly this, as JSONPath, jq and dlt write it:

Path Reads
$ The whole row object.
$.id, $.address.city A key, and keys below it.
$.items[0].id Item 0 of a list, the first, then a key below it.
$.statuses[-1].name Counting from the end: [-1] is the last item, [-2] the one before.
$.grid[1][0] A list inside a list.
  • A missing key, an index past either end of its list, or an index on a value that isn’t a list loads NULL, as a missing key does; required=True fails the table instead, naming the row, the field and the path.
  • Anything else is refused, naming the field and the path: [*] and .* (one column can’t hold every item; select makes each item a row), filters such as [?(@.type == "email")], slices such as [0:2], recursive descent .., and bracketed keys such as ['id']. An sl.http job fails before any request, and sl.json when it is called.

plan checks the paths an sl.http job writes as literals, so a bad one fails plan instead of the load. It reads the table function’s source and never calls it, so it sends no request. It checks the fields= values of paginate and fan_out, the paths in their sl.field and sl.parent, and key_path=, with the same checker as load:

$ sqlload plan
Errors (1)
└── raw.shop__orders
└── reason fields["sku"]: field path "$.lines[*].sku" uses the wildcard [*], which one column can't hold; take one item with [n] or [-1], or select the list's items as rows
✗ Plan failed 1 error
  • Only calls whose names lead to sqlload count, however it was imported (import sqlload as sl, from sqlload import parent), on a client from sl.http(...) made at module level or in the function. A paginate method of another library isn’t checked.

  • plan checks every literal path in the table function, including those in branches a load may not take, such as an if on ctx.cursor_value; a bad path there fails plan too.

  • A path plan can’t read as a literal, such as a variable, an f-string or a fields dict built elsewhere, is checked at load, before any request. So is every path of a function whose source can’t be read. plan says so on that table:

    $ sqlload plan
    …
    └── raw.shop__orders replace
    ├── source python sources/shop.py (shop)
    …
    └── paths checked at load, as they're computed: fields["sku"]
    …
  • A job built in another function that the table function calls isn’t in the table function’s source, so only load checks it, and plan shows nothing for it.

  • A job passed as another job’s over= takes no fields or key_path: load refuses them before any request, whatever their paths, so plan leaves its paths alone.

An sl.http job’s fields can read beyond the row itself, all in Rust. This loads one row per Jira change history, each with its issue’s ID, without unnest or a downstream view:

api.fan_out(
"/rest/api/3/changelog/bulkfetch", over=updated, method="POST",
json_body={"issueIdsOrKeys": "{id}"}, batch=1000,
paginator=sl.cursor("$.nextPageToken", "$.nextPageToken", location="body"),
select="$.issueChangeLogs[*].changeHistories[*]",
fields={
"issue_id": sl.parent("$.issueId", required=True),
"history_id": sl.field("$.id", required=True),
"created": sl.field("$.created", parse="epoch_ms"),
"items": "$.items",
},
key_path=sl.parent("$.issueId"),
)
  • select can go through more than one list: $.issueChangeLogs[*].changeHistories[*] loads each history of each issue. A select ending in one name after a list, such as $.items[*].id, still picks keys for a fan-out’s over.
  • sl.parent(path, levels=1) reads from the nearest object around the row that select passes through by name, here the issue; levels=2 reads from the next one out, and the outermost is the response itself, so sl.parent("$.total", levels=2) reads the page’s envelope. It takes required, parse and on_error as sl.field does. A job asking for more levels than its select has fails before any request.
  • sl.key(name) is the value of key field name that the row’s fan-out request was made for, from any level above, for an API whose records don’t repeat it: fields={"version_id": sl.key("version_id"), "activity_id": "$.id"}. A name no level provides fails before any request, and so does sl.key with batch=, where one request carries many keys; read the key from a response field or sl.parent instead.
  • key_path on fan_out (a path from each record, or sl.parent(...), in the path syntax) fails the table when a record’s key isn’t among the keys its request sent (with batch=, the batch’s), naming the key, for an API that answers for other keys. A request filling more than one placeholder refuses key_path, as a record’s key could match another placeholder’s value.
  • sl.parent and sl.key read an sl.http job’s rows; sl.json and sl.ndjson refuse them, and cells can’t combine with them.

A table function receives a sl.Context:

Attribute Meaning
ctx.cursor_value Where to read from: the destination’s maximum minus lookback, cursor_start, or --start-cursor (or the typed flag of the cursor’s kind). None reads everything.
ctx.cursor_end Where the window ends, with batch_size or --end-cursor.
ctx.cursor_column, ctx.cursor_inclusive The cursor column, and whether the read includes cursor_value. False when append reads after the destination’s maximum.
ctx.is_reload True under --reload.
ctx.state The logged position, with state="logged". Assign it to move the position.
ctx.connection The source’s connection settings, as a mapping. ctx.connection("name") gives another connection’s, except connections a target writes to.
ctx.client The source’s @source.client, built on first use.
ctx.table, ctx.source, ctx.logger The table and source names, and a logger.

On a cursor of times, ctx.cursor_value and ctx.cursor_end arrive in the table’s timezone, such as datetime(2026, 3, 1, 0, 0, tzinfo=zoneinfo.ZoneInfo(key='Europe/Paris')). Without it, a timestamp_tz cursor’s arrive in UTC and a timestamp_ntz cursor’s arrive naive. With it, a timestamp_ntz cursor’s carry the zone too, keeping their wall-clock time. Python can’t compare those with naive datetimes, so a function that does must make one side match, such as since = ctx.cursor_value.replace(tzinfo=None). See Times without an offset.

Rows the function returns outside the window it was asked for fail the table, naming how many and their likely cause. on_cursor_out_of_range="skip" drops them and "warn" loads them. See Rows outside the window.

Table functions never get credentials of a connection a [targets] entry writes to: a source using such a connection fails at plan.

Tables of a source with a @source.client load one at a time, because clients are rarely thread-safe. Set parallel_tables=True on the source when its client is.

sl.http describes REST jobs that run entirely in Rust. For worked examples, see HTTP APIs in the guide. The connection is an http connection. The table’s flatten applies to the rows a job reads.

Function Meaning
sl.http(source, *, headers=None, connection=None) Requests to the source’s http connection, or to connection, adding headers to the connection’s. How requests authenticate, their rate_limit and max_in_flight, and their retries are set on the connection; passing auth, rate_limit, max_in_flight or retries to sl.http fails, naming the connection key to set.
api.paginate(path, *, paginator, params=None, select=None, on_status=None, fields=None, keep_record=None, method="GET", json_body=None, key=None, cells=None, select_missing="fail", error_path=None, transform=None) Every page of path. select is where the rows are: $, or a dotted path such as $.data.items. A response without the select path fails the load, naming what the response holds; select_missing="empty" reads it as no rows instead. See A missing select path. error_path is where the API reports an error in a successful response; see Errors in a successful response. fields and keep_record pick and rename each row’s values; see Fields. For method and json_body, see POST with a JSON body. cells= turns each row’s list of cells into columns; see Rows of cells. transform reshapes each page’s rows in Python before they load; see Transforming each page.
api.fan_out(path, *, over, concurrency=8, paginator=None, params=None, select=None, on_status=None, fields=None, keep_record=None, method="GET", json_body=None, key=None, cells=None, batch=None, select_missing="fail", error_path=None, transform=None) path once per key of over, up to concurrency at a time, while keys still arrive. select_missing and error_path are as for paginate. over is a paginate whose select picks keys ($.items[*].id fills {id}), or a list: a value fills the path’s one placeholder, and a dict fills placeholders by name. over can also be another fan_out; see Multi-level fan-out. method and json_body are as for paginate. Keys also fill {placeholders} in json_body, and batch sends several keys per request; see Keys in a POST body. transform is as for paginate, called once per page of each key.
Paginator Next page
sl.single() None: one request.
sl.next_link(pointer) The URL at pointer in the body, such as "$.next". A link already followed fails the load; see Repeated pages.
sl.link_header() The Link header’s rel="next". A link already followed fails the load.
sl.offset(param="offset", *, limit, limit_param="limit", location="query", read_ahead=None) Counts rows by limit; ends at a short page. For read_ahead, see Reading pages ahead. When a first page holds rows but fewer than limit, one more request at the next offset tells an API that caps its pages below limit (rows come back: the load fails, naming the page size to set as limit) from a table that really ended (no rows: the run ends). A fan-out checks a key’s short first page only until its requests have shown the API sends pages that long.
sl.page_number(param="page", *, start=1, page_size=None, page_size_param="page_size", location="query", read_ahead=None) Counts pages; ends at an empty page, or with page_size at a short page. See Ending at a short page.
sl.cursor(pointer, param, *, location="query") The token at pointer goes back as param. A token already sent fails the load.
Cells Columns
sl.cells(pointer="$.cells", *, value="$.value", name) Each row’s cells at pointer, one column per name. name is a path in each cell, such as "$.FIELDNAME", when each cell names its own column, or one of the two below. See Rows of cells.
sl.listed(path, *, key="columnId", id_key="id", name_key="title", by_position=False) The columns the response lists at path, such as "$.columns". Each cell’s key matches a listed column’s id_key, and name_key names it; by_position=True matches cells to columns by position.
sl.header_row() The first row’s cells name the columns, by position, and that row doesn’t load.

The connection’s auth says how requests authenticate: bearer, api_key in a header or query parameter, basic, or one of three OAuth2 grants. See the http connection for each and the credentials it uses.

  • Every request to the connection in a run shares its rate_limit and max_in_flight, whichever table, page, fan-out key or retry it belongs to, so two tables reading one API at once stay under one limit together. Load one API’s tables in one sqlload load: separate processes don’t share the limits.

  • Every paginate names its paginator; nothing is guessed.

  • Credentials go only to the connection’s own scheme, host and port, never to a next-page link elsewhere. The one exception is OAuth2: the client secret and refresh token go to its token_url.

  • Transport errors, 429 and 5xx are retried with backoff and jitter, honouring Retry-After. Another status fails, unless on_status maps it to "skip".

  • The job is checked before any request. Errors name the request, the status and the start of the body, with the connection’s secrets replaced by ***.

  • A job’s rows never come back into Python one at a time. Reading a row from a job fails (rule python_row_loop); filter rows downstream, or reshape each page with transform.

sl.page_number without page_size ends only at an empty page, as it can’t tell a short page from a full one. A fan-out whose keys mostly fit in one page then sends two requests per key. With page_size, a page holding fewer rows ends the run, as for sl.offset:

api.fan_out("/proofs/{id}/activities", over=ids,
paginator=sl.page_number("page", page_size=100, page_size_param="count"), select="$.items")

page_size goes with every request as page_size_param, so leave it out of params; page_size_param=None sends nothing, for an API whose page size is fixed. An API can send fewer rows a page than asked, so a short page ends the run only once the table’s requests have shown that the API sends that many:

  • After a page holding the whole page_size, every short page is the last.
  • Before that, a short page is checked with the next page. An empty one shows the short page was the last, and later keys end at once on a shorter page. Rows there show the API caps its pages: paging goes on to an empty page, so every row loads, and a warning names the page_size to set.

A fan-out over 3,121 keys of about 19 rows each sends 3,153 requests rather than 6,127.

read_ahead=N on sl.offset or sl.page_number keeps up to N page requests open at once (at most 100), which shortens a backfill over an API with slow pages:

api.paginate("/invoices", paginator=sl.offset(limit=50, read_ahead=20))

The first page is read alone, so a short first page is checked as without read_ahead. After a full first page, the pages after it are requested two at a time, then four, eight and so on up to N, the open requests doubling each time the pages of the last round have loaded. Each goes through the connection’s rate_limit, max_in_flight and retries, and their rows load strictly in page order. The run ends at the first short (or, for sl.page_number without page_size, empty) page, and the requests already sent past it are dropped, so a table or cursor window of a few pages sends only a few requests more than it loads, and a long one soon has N open. sl.cursor, sl.next_link and sl.link_header refuse read_ahead, as each page’s address comes from the page before it.

Without read_ahead, paging waits on the API one page at a time, so its speed is the API’s latency. Against a mock API answering each page of 100 rows in 80 ms, 1,000 pages took:

Paginator Wall time Pages per second
no read_ahead 83 s 12
read_ahead=8 12 s 85
read_ahead=20 6 s 171

Raise read_ahead until the API’s own limits, or the connection’s rate_limit or max_in_flight you set for them, become the bound. Every table reading at once adds its own open requests, up to concurrency × read_ahead; max_in_flight caps them all together.

Every job of a load shares one pool of connections, so the cursor windows of a backfill reuse the connections the first window opened instead of each opening its own.

A response without the select path, as opposed to one where it holds an empty list or null, fails the load. The error names the request, the path, and the response’s top-level keys, so an API that answers an error with a 200 status, such as {"error": {"message": "daily quota used"}} on page 4, fails there rather than ending the read with the rows so far:

GET https://api.shop.example/v1/orders?p=4: select "$.items" is not in the response, which holds the keys error; an API can answer an error with a 200 status, so check the response. For an API that leaves an empty list out, set select_missing="empty" to read such a page as no rows

Some APIs leave an empty list out instead of sending [], as Google’s do: an empty grid has no rowData, and a list with no results has no list at all. For those, select_missing="empty" reads a response without the path as no rows.

sl.next_link, sl.cursor and sl.link_header end at a response without a next page: a missing, null or empty pointer, or no rel="next" link. That is how an API says it has no more pages, so SQLLoad can’t tell it from an error response that also leaves the pointer out; with select set, such a response usually lacks the rows’ path too, and fails as above.

Some APIs answer an error with a 200 status and the error in the body, beside the rows or instead of them, such as EAM’s ErrorAlert, GraphQL’s errors or Slack’s error. error_path on paginate or fan_out names where: any value there other than null, false, "", [] or {} fails the table with the API’s message, before any row of that page loads. 0 counts as an error. The message is the value as text (JSON for a list or an object), with the connection’s secrets replaced by ***, cut at 200 characters:

POST https://eam.example/axis/restservices/grids: the API reported an error at $.ErrorAlert: [{"Message":"Grid WSJOBS: you are not allowed to run this grid"}]

The path takes the same form as select, without [*]. It is checked on every response that yields rows or keys, including a fan-out’s over and its pages, but not an OAuth2 token response.

A paginator that follows the response, sl.cursor, sl.next_link or sl.link_header, fails the load when the next position is one it already requested for the same request (in a fan-out, for the same key), instead of reading that page again and loading its rows twice. The error names the value and the page that returned it:

POST https://eam.example/axis/restservices/grids: cursor 2 repeated after page 2, so paging would read page 2 again; check the paginator's pointer

sl.offset and sl.page_number count their own positions, so they never repeat one; instead, a page whose response repeats the previous page’s fails, as an API ignoring the parameter would loop.

transform= on paginate or fan_out calls a Python function once per page, in page order, on the page’s rows after select, fields and cells. SQLLoad still makes every request, with its paging, retries and read-ahead; the function only reshapes what a page returned. It’s the same kind of function a files table’s transform runs: f(rows, ctx), with the page’s rows as a pyarrow.Table, returning or yielding dicts, lists of dicts, a pyarrow.Table, or a polars or pandas DataFrame. Pass the function itself, or the name @sl.transform registered it under. A function passed itself is the one called, even when another file registers a function of the same name, and it needn’t be registered at all:

sources/shop.py
import json
import sqlload as sl
shop = sl.source("shop", connection="shop", write_strategy="replace")
api = sl.http(shop)
@sl.transform
def order_lines(rows, ctx):
for order in rows.to_pylist():
for line in json.loads(order["lines"]):
yield {"region": ctx.key["region"], "order_id": order["id"], **line}
@shop.table(description="Order lines, one row per line, by region.")
def lines(ctx):
return api.fan_out("/regions/{region}/orders", over=["north", "south"], select="$.orders",
transform=order_lines)
$ sqlload load
✓ Plan complete 1 object to create, 1 to load (sqlload plan shows the tree)
created raw.shop__lines 6 columns
loaded raw.shop__lines 6 rows 0.1s
✓ Completed 1 loaded, 6 rows, 0.2s
├── slowest raw.shop__lines 0.1s
└── peak memory 133 MiB
  • ctx.unit is "page", ctx.page the page’s number within its request (from 1), and ctx.key the fields of the key a fan-out requested it for (None in a paginate or with batch=). ctx.table is the table’s name.

  • A page’s nested objects and lists arrive as JSON text, as lines does above: json.loads them, or pick their values with fields first.

  • Every page that’s read goes through the function once, including an empty last page, such as the one that ends sl.page_number paging without page_size. What it returns or yields loads in page order, batch by batch, and flatten, columns and mask apply to it.

  • A function that raises, or returns or yields something else, fails the table, naming the request: GET .../regions/north/orders: transform order_lines failed: ....

  • plan never calls the table function, so it checks a transform="name" written as a literal and fails when no function, or two, are registered under it, as for field paths:

    $ sqlload plan
    Errors (1)
    └── raw.shop__lines
    └── reason transform order_line: no function is registered as order_line with @sl.transform; the registered ones are order_lines
    ✗ Plan failed 1 error
  • transform is for the job whose rows load; a job given as a fan-out’s over= refuses it.

A fan_out whose select picks keys can be the over of another fan_out, as many levels deep as they nest: list → detail → sub-detail. Each key keeps the fields of the keys above it, so a third level’s path can use {order_id} as well as {item_id}. When a level’s field has the same name as one above it (every level calls it id), name it apart with key=, as in select="$.items[*].id", key="item_id".

@shop.table(description="Order notes.", write_strategy="append", cursor_column="order_id")
def notes(ctx):
orders = api.paginate("/orders", paginator=sl.page_number("p"),
params={"after": ctx.cursor_value}, select="$.items[*].order_id")
items = api.fan_out("/orders/{order_id}/items", over=orders, select="$.items[*].item_id")
return api.fan_out("/orders/{order_id}/items/{item_id}/notes", over=items,
paginator=sl.page_number("page"), select="$.items")
  • Every level has its own paginator, concurrency and on_status; retries and limits are the connection’s. Up to one more request than the sum of the fan-out levels’ concurrency is in flight at once (the top paginate’s); the connection’s max_in_flight and rate_limit cap them.
  • An incremental cursor goes on the top level, as above; lower levels only see the keys it picks.
  • Only the last level’s rows load. A level that should be a table of its own is a table function whose job stops at that level, so the levels above it are requested again for that table.
  • Rows load as the API returns them: a key’s fields are not added to them, so select an ID the response repeats, such as order_id above.
  • An error names the keys that led to it, such as /orders order_id=5 → /orders/5/items item_id=51 → GET .../notes?page=2: HTTP 500.
  • plan checks every level before any request: each placeholder must come from a level above.

APIs that look items up by POST take the keys in the body rather than the path. A fan_out’s keys fill {placeholders} in the string values of json_body as they fill the path’s: a value that is just a placeholder, such as "{order_id}", takes the key as the API returned it, so a number stays a number; a placeholder inside other text, such as "order {order_id}", is filled as text.

@shop.table(description="Items of each order.", write_strategy="append", cursor_column="order_id")
def items(ctx):
orders = api.paginate("/orders", paginator=sl.page_number("p"),
params={"after": ctx.cursor_value}, select="$.items[*].order_id")
return api.fan_out("/items/lookup", over=orders, method="POST",
json_body={"order": {"id": "{order_id}"}}, select="$.items")

With batch=N, each request carries up to N keys: a value that is just a placeholder holds the list of the batch’s keys, so json_body={"ids": "{order_id}"} with batch=1000 sends {"ids": [1, 2, …]} with up to 1,000 keys, the last batch fewer. Each batch is one request, retried as a whole, and concurrency counts batches.

return api.fan_out("/items/lookup", over=orders, method="POST",
json_body={"ids": "{order_id}"}, batch=1000, select="$.items")
  • plan checks every placeholder in the path and the body before any request: each must come from a level above.
  • Braces around a bare name in a body string are a placeholder wherever they are, {id} in a compact GraphQL query such as "{orders{id}}" included. Braces with spaces or other characters inside, such as { id }, stay text.
  • An error names the keys of the request that failed, as in order_id=4, 5, 6 → POST .../items/lookup: HTTP 500.
  • A batch’s keys go only in the body, as whole values: plan refuses a batched fan-out with a placeholder in its path or inside other text, or without json_body.
  • A batched fan-out can’t be the over of another fan-out.

Some APIs return each row as a list of cells, such as [{"columnId": 7001, "value": "Bolt"}]. Each cell’s column name is in the cell itself (EAM’s grids), in a list of columns the response sends beside the rows (Smartsheet), or in the first row (Google’s grid data). cells=sl.cells(...) on paginate or fan_out turns each row into one column per name, in Rust and page by page, before the rows load. name says where the names are:

name Names come from Example
"$.FIELDNAME" A path in each cell: the cell names its own column. EAM
sl.listed("$.columns") The columns the response lists, matched by each cell’s columnId to a column’s id, named by its title. Smartsheet
sl.listed("$.columns", by_position=True) The columns the response lists, matched by position.
sl.header_row() The first row’s cells, by position; that row doesn’t load. Google’s grid data

An EAM grid is a POST paged by a cursor in the body, and each cell names its column:

{"Result": {"ResultData": {"NEXTCURSORPOSITION": 2, "DATARECORD": [
{"DATAFIELD": [{"FIELDNAME": "wo_code", "FIELDVALUE": "1001", "DATATYPE": "VARCHAR"},
{"FIELDNAME": "DueDate", "FIELDVALUE": "2026-11-01", "DATATYPE": "DATE"}]}]}}}
@eam.table(description="Work orders.")
def work_orders(ctx):
return api.paginate(
"/grids",
method="POST",
json_body={"GRID": {"NAME": "WSJOBS"}},
paginator=sl.cursor("$.Result.ResultData.NEXTCURSORPOSITION", "$.CURSORPOSITION", location="body"),
select="$.Result.ResultData.DATARECORD",
cells=sl.cells("$.DATAFIELD", value="$.FIELDVALUE", name="$.FIELDNAME"),
error_path="$.ErrorAlert",
)

The names then go through the table’s naming and rename like any other column’s, so wo_code, Description, DueDate and EstHours load as:

wo_code VARCHAR
description VARCHAR
due_date VARCHAR
est_hours VARCHAR

For Smartsheet, whose rows list cells by columnId and whose sheet lists its columns:

@sheets.table(description="Parts.")
def parts(ctx):
return api.paginate("/sheets/123", paginator=sl.page_number("page"), select="$.rows",
cells=sl.cells(value=["$.value", "$.displayValue"], name=sl.listed("$.columns")))

For Google’s grid data:

api.paginate("/spreadsheets/ID", paginator=sl.single(), select="$.sheets[0].data[0].rowData",
select_missing="empty",
cells=sl.cells("$.values", name=sl.header_row(),
value=["$.effectiveValue.stringValue", "$.effectiveValue.numberValue",
"$.effectiveValue.boolValue"]))
  • value is where each cell’s value is, or a list of places tried in turn; the first present and not null wins. Values are typed like any other JSON value: a number loads as a number, true as a boolean, and text as text. Paths in select and here may index a list, as in $.sheets[0].
  • A name given as a number or true/false is its text. A cell that names itself with nothing at name, or an empty name, fails the load, as in row 2: its cell 2 has no name at $.FIELDNAME.
  • Two cells for one column in a row fail the load, and so do two names that differ only in case (Qty and qty), as they would load as one column.
  • A row with no cell for a column loads NULL in it. The row’s other fields, such as its id or modifiedAt, load beside the cells’ columns; one with the name of a cells’ column fails the load.
  • With sl.listed, a cell whose column the response doesn’t list fails the load, naming its id, unless it has no value (Google returns formatted empty cells past the last column). Names are read again from every page that lists them; a page that doesn’t keeps the names of the page before it. A page whose rows have no field at pointer fails the load until a page had cells.
  • sl.header_row() reads the first row of a paginate’s first page. In a fan_out, that is each key’s first page, which suits one sheet per key; a fan-out that splits one sheet into ranges would lose each range’s first row, so list the names with sl.listed instead.
  • Cells that name themselves give each row the columns its cells name. A name that first appears on a later row or page is a new column, as for any JSON rows.
  • A column renamed in the source is a new column to the destination, so on_schema_change decides: by default it is added, and earlier rows hold NULL in it.
  • fields= picks from the pivoted columns, by their names as the API spells them ({"qty": "$.Qty"}), and keep_record keeps the pivoted row, not the cells as the API sent them.

method="POST" sends json_body with every request, as Content-Type: application/json; GET stays the default, and its requests don’t change. With location="body", sl.offset, sl.page_number and sl.cursor put their values in the body instead of the query string, and param (and limit_param) are paths such as "$.page.cursor". Each request starts from a fresh copy of json_body, so one page’s values never leak into the next, and a retry resends the same body. A POST without json_body starts from {}.

To send the incremental cursor in the body, put ctx.cursor_value in json_body, as you would in params. Dates, times and decimals go as text (ISO text for dates and times); wrap the value in int() or float() if the API wants a JSON number.

@shop.table(description="Orders.", write_strategy="append", cursor_column="updated_at")
def orders(ctx):
return api.paginate(
"/orders/search",
method="POST",
json_body={"filter": {"updated_after": ctx.cursor_value}},
paginator=sl.cursor("$.meta.next", "$.page.cursor", location="body"),
select="$.items",
)

json_body on a GET, location="body" without method="POST", or a body path that json_body already holds as a non-object (such as $.page.cursor with {"page": 1}) fails before any request. A POST follows only 307 and 308 redirects, which keep the method and body; a 301, 302 or 303 would resend it as a GET without its body, so it fails instead. sl.next_link and sl.link_header resend the same body to the next page, and fail if that page is on another origin, so json_body never leaves the connection’s origin.

The oauth2_client_credentials, oauth2_refresh_token and oauth2_password kinds of auth fetch short-lived access tokens from token_url. See OAuth2 in the guide for an example.

  • The token is fetched once per run and shared by every job on the connection. It is renewed before it expires (by expires_in), and once more when the API answers 401; the request is then retried with the new token. When on_status maps 401 to "skip", a 401 renews the token only if it is over 30 seconds old; otherwise the request is skipped.
  • client_auth = "basic" sends client_id and client_secret to the token URL with HTTP basic auth, the method RFC 6749 requires servers to support. As the RFC asks, both are form-encoded first, so a secret with characters such as + or / reaches the server encoded. Use "post" for a server that rejects that, or that wants them as form fields.
  • The client secret, refresh token and password go only to token_url, which must be https (http only on localhost). Access tokens go only to the connection’s own origin, like every other credential.
  • Token errors name the token URL, the status and the server’s error and error_description. The client secret, refresh token, password and every access token are shown as ***.
  • When the server rotates the refresh token, SQLLoad uses the new one for the rest of the run but does not save it, so each run starts from the connection’s refresh_token. An API that revokes the old refresh token on rotation therefore fails on the next run; use client credentials there when the API offers them.
  • oauth2_password posts grant_type=password with the connection’s username and password. When the response includes a refresh_token, later renewals in the run use it; when it doesn’t, or the server refuses it, SQLLoad asks with the password again. The password is shown as *** like the other secrets, and wrong credentials end the run with exit 1 (not retryable) and the server’s error.
[connections.shop]
kind = "http"
base_url = "https://api.example.com/v1"
username = "loader"
password = "${ENV:SHOP_PASSWORD}"
client_id = "sqlload"
auth = { kind = "oauth2_password", token_url = "https://auth.example.com/oauth/token", client_auth = "post" }

Most tables need no state: their position is the data’s own cursor_column. A table whose position the data can’t carry, such as an API’s sync token, opts in with state="logged":

@shop.table(write_strategy="append", state="logged")
def events(ctx: sl.Context):
"""Shop events, read page by page with a sync token."""
token = ctx.state
while True:
page = fetch_events(since=token)
yield from page.items
token = page.next_token or token
yield sl.checkpoint(token)
if not page.has_more:
break

sl.checkpoint(position) takes any JSON value and covers exactly the rows yielded before it. sl.checkpoint(None) clears the position. See Logged state. To inspect or correct a logged position, see sqlload logged-state.

Name Meaning
sl.__version__ The package version.
sl.executable() The path of the sqlload command installed with the package, else the one on PATH.
@sl.transform, @sl.transform("name") Registers a function under its own name, or the one given, as a transform for a files or Google Sheets table’s transform = "name", or an sl.http job’s transform=. Returns the function unchanged.
@sl.rename, @sl.rename("name") Registers a function under its own name, or the one given, for a table’s rename. It gets the columns as sl.Columns (position, source, default) and an sl.RenameContext (source, table), and returns the names it changes by position. See rename.
sl.TransformContext The ctx a transform gets: unit ("file", "range" or "page"), table, and the unit’s details.