Coming from dlt

This guide moves dlt pipelines to SQLLoad: what each dlt idea becomes, a REST API and a folder of files carried across, tables that keep the names and row ids dlt gave them, and running loads from an orchestrator. It needs SQLLoad 0.40.0 or later. Every SQLLoad example on this page is a project that SQLLoad’s test suite loads twice, and the files example’s tables and row ids are checked against what dlt 1.32.0 loads from the same files. For how the two differ in design, see SQLLoad, dlt and ingestr.

What each dlt idea becomes

dlt	SQLLoad
dlt.pipeline(destination=..., dataset_name="shop")	A target in sqlload.toml, with loader_schema = "shop". There is no pipeline working directory.
@dlt.source	sl.source(...) in Python, or a [sources.<name>] entry for a database or files.
@dlt.resource	@source.table in Python, or a table of a source.
A resource’s table name	{source}__{table} by default. table_name = "{table}" on the source names it as dlt does.
write_disposition="append" or "replace"	write_strategy = "append" or "replace".
write_disposition="merge" with primary_key	write_strategy = "merge" with unique_key. See merge.
A sentinel such as "" filled into a key column that can be empty, as dlt makes primary_key columns NOT NULL	Not needed: from 0.51.0 merge matches a NULL in a key column with a NULL. See NULLs in keys.
merge_key, dlt’s delete-insert of the partitions a load holds	From 0.51.0, write_strategy = "delete_insert" with replace_key set to the merge_key columns: each commit deletes the rows holding a key value it read, then inserts. See Replacing rows by a key. Without a cursor_column: each load reads the whole source, as plan refuses the two together. Where the partition is a cursor range, cursor_column alone does it.
dlt.sources.incremental("updated_at", initial_value=...)	cursor_column = "updated_at" and cursor_start, with ctx.cursor_value in a Python table. lag is lookback. See Cursors.
incremental(..., on_cursor_value_missing=...)	null_cursor: "include" is null_cursor = "include" and "exclude" is null_cursor = "ignore". dlt’s default, "raise", raises on a row it reads with no cursor, but its sql_database filters later runs with cursor >= last_value in SQL, so it never reads those rows to raise on (helpers.py). SQLLoad’s default, fail, counts them and fails the load. See NULL cursor values.
Pipeline state (_dlt_pipeline_state)	The destination’s own maximum of cursor_column, read each run. state = "logged" keeps a position for the tables that need one.
The stored schema (_dlt_version)	The live table, read each run. See Contracts.
max_table_nesting and child tables	unnest and max_depth. See Nested data.
rest_api	sl.http, below.
filesystem with a reader	A files source, below.
A transformer, or add_map, that reshapes each file’s rows	From 0.52.0, a files source’s transform, an @sl.transform function. See Transforming each file.
add_map on a rest_api resource, reshaping each page’s records	From 0.52.0, transform= on paginate or fan_out. See Transforming each page.
secrets.toml and config.toml	${ENV:NAME} in sqlload.toml.
pipeline.run() from a task	sqlload load --json, below.

REST APIs: rest_api to sl.http

A rest_api source like this one, with a bearer token, a JSON next link, an incremental parameter and a child resource per order (see dlt’s rest_api docs):

# dlt, for comparison
source = rest_api_source({
    "client": {"base_url": "https://api.example.com/v1", "auth": {"type": "bearer", "token": token}},
    "resource_defaults": {"write_disposition": "merge", "primary_key": "id"},
    "resources": [
        {
            "name": "orders",
            "endpoint": {
                "path": "orders",
                "data_selector": "items",
                "paginator": {"type": "json_link", "next_url_path": "next"},
                "params": {"updated_after": {"type": "incremental", "cursor_path": "updatedAt",
                                             "initial_value": "2026-01-01T00:00:00Z"}},
            },
        },
        {
            "name": "payments",
            "endpoint": {
                "path": "orders/{resources.orders.id}/payments",
                "data_selector": "items",
                "response_actions": [{"status_code": 404, "action": "ignore"}],
            },
        },
    ],
})

becomes a connection and a Python module. Every request runs in Rust; no row passes through Python.

sqlload.toml

python_paths = ["sources"]
default_target = "warehouse"

[connections.shop_api]
kind = "http"
base_url = "${ENV:SHOP_API_URL}"
token = "${ENV:SHOP_TOKEN}"

[connections.duckdb]
kind = "duckdb"
database = "warehouse.duckdb"

[targets.warehouse]
connection = "duckdb"
loader_schema = "shop"
naming = "dlt"

sources/shop.py

import sqlload as sl

shop = sl.source(
    "shop", connection="shop_api", table_name="{table}", write_strategy="merge"
)
api = sl.http(shop, auth=sl.bearer(), retries=5)


@shop.table(
    description="Orders, one row per order.",
    unique_key="id",
    cursor_column="updated_at",
    cursor_start="2026-01-01T00:00:00Z",
    columns={"updated_at": "timestamp_tz"},
    unnest="all",
)
def orders(ctx):
    return api.paginate(
        "/orders",
        params={"updated_after": ctx.cursor_value},
        paginator=sl.next_link("$.next"),
        select="$.items",
    )


@shop.table(description="Payments, one row per payment.", unique_key="id")
def payments(ctx):
    order_ids = api.paginate(
        "/orders", paginator=sl.next_link("$.next"), select="$.items[*].id"
    )
    return api.fan_out(
        "/orders/{id}/payments",
        over=order_ids,
        select="$.items",
        on_status={404: "skip"},
    )

$ export SHOP_API_URL=https://api.example.com/v1 SHOP_TOKEN=...
$ sqlload load

It loads these tables into shop, each line a table and its columns:

Tables

orders: id, status, updated_at, _dlt_load_id, _sqlload_loaded_at, _dlt_id
orders__lines: sku, qty, _dlt_root_id, _dlt_parent_id, _dlt_list_idx, _dlt_load_id, _sqlload_loaded_at, _dlt_id
payments: id, amount, _dlt_load_id, _sqlload_loaded_at, _dlt_id

On the next run, orders asks only for orders updated after the newest updated_at it holds, and merges them on id. A merged order keeps its _dlt_id, and its lines are replaced by the ones it holds now. payments reads every order’s payments again and merges them on their own id.

How rest_api settings map:

• client.base_url and the credentials go in the http connection; auth is an argument of sl.http. bearer is sl.bearer(), api_key is sl.api_key(header=...) or sl.api_key(query=...), http_basic is sl.basic(), and oauth2_client_credentials is sl.oauth2_client_credentials(token_url). The OAuth2 password grant, which dlt’s rest_api has no auth type for (config_setup.py), is sl.oauth2_password(token_url); see OAuth2.
• Paginators: json_link is sl.next_link("$.next"), header_link is sl.link_header(), offset is sl.offset(limit=...), page_number is sl.page_number(), cursor is sl.cursor(pointer, param), and single_page is sl.single(). dlt’s auto guesses one (config_setup.py); SQLLoad never does.
• data_selector is select, as a path from $.
• An incremental parameter is params={"updated_after": ctx.cursor_value} with the table’s cursor_column. The value comes from the destination, so nothing needs to be kept between runs.
• A resource that resolves another ({resources.orders.id}) is api.fan_out(path, over=...), whose over is a paginate selecting the keys. Fan-outs nest as many levels deep as needed; see Multi-level fan-out.
• A resource whose parent’s keys go in a POST body, rather than the path, is api.fan_out(path, over=..., method="POST", json_body={"order": {"id": "{id}"}}). batch=1000 sends up to 1,000 keys per request as a list (json_body={"ids": "{id}"}), each batch retried as one request, where dlt needs Python that fetches the keys and yields one request per batch; see Keys in a POST body.
• method: POST with a json body is method="POST", json_body={...}, and paginators that page in the body take location="body"; see POST with a JSON body.
• A resource reshaped with add_map (each record split, unpivoted or cleaned in Python) is transform= on the job: a function called once per page with the page’s rows, from 0.52.0; see Transforming each page.
• response_actions that ignore a status are on_status={404: "skip"}. 429, 5xx and dropped connections are retried, honouring Retry-After; retries= on sl.http sets how many times.
• An API that returns each row as a list of cells, such as a spreadsheet’s, is cells=sl.cells(...); see Rows of cells.
• include_from_parent is sl.key(name) in a fan-out’s fields. Records an adapter reshapes in Python, such as one flat row per Jira change history with its issue’s ID, are a select through both lists with sl.parent: select="$.issueChangeLogs[*].changeHistories[*]" and fields={"issue_id": sl.parent("$.issueId"), "created": sl.field("$.created", parse="epoch_ms"), ...}, with key_path=sl.parent("$.issueId") to check each history belongs to an issue the request asked for. See Values from around the row.

Files: filesystem to a files source

dlt’s filesystem source with read_jsonl and a resource merged on OrderID with write_disposition={"disposition": "merge", "strategy": "upsert"} becomes one [sources] entry. Its table is named after the source:

sqlload.toml

default_target = "warehouse"

[connections.landing]
kind = "filesystem"
root = "landing"

[connections.duckdb]
kind = "duckdb"
database = "warehouse.duckdb"

[targets.warehouse]
connection = "duckdb"
loader_schema = "shop"
naming = "dlt"

[sources.orders]
kind = "files"
connection = "landing"
path = "orders/"
format = "ndjson"
description = "Orders the shop exports, one row per order."
write_strategy = "merge"
unique_key = "OrderID"
after_load = { move_to = "loaded" }

[sources.orders.tables.orders]
unnest = "all"

• merge merges each new file once, oldest modified first, and records it in _sqlload_runs, so no file is read twice and none needs incremental on its modification date. See Merging files.
• after_load = { move_to = "loaded" } moves each file once its rows are committed; "delete" deletes it. See Deleting or moving loaded files.
• format is parquet, csv, json, ndjson, xlsx or xml, and the files can be on S3 or an SFTP server.
• An Excel template read as a cell range without a header row, with its own column names (sheet_range: A:C and column_names: [metric, category, value] in a pipeline’s settings), is xlsx = { range = "A:C", header = false } and column_names = ["metric", "category", "value"] from 0.49.0. See Naming headerless columns.
• A file that needs reshaping before it loads (title rows, a header split over two rows, months across), read in dlt by a custom reader or reshaped by add_map, takes a transform from 0.52.0: a Python function under python_paths, decorated with @sl.transform and named in transform, that gets each file’s rows as a pyarrow.Table and returns or yields the rows to load. See Transforming each file. See Load files.

Tables that look like dlt’s

Three settings make SQLLoad’s tables look like the ones dlt made, so the models and queries over them keep working:

• naming = "dlt" on the target names tables, columns and child tables as dlt’s default snake_case naming does (OrderID is order_id), and the columns a load adds as dlt names them (_dlt_load_id, _dlt_id, _dlt_parent_id, _dlt_list_idx, _dlt_root_id).
• unnest = "all" makes a child table of every list field, as dlt does.
• max_depth is dlt’s max_table_nesting.

For the files example above, after its second file, dlt 1.32.0 (with the upsert merge) and SQLLoad load these tables:

dlt

orders: order_id, status, _dlt_load_id, _dlt_id
orders__lines: sku, qty, _dlt_root_id, _dlt_parent_id, _dlt_list_idx, _dlt_id
orders__tags: value, _dlt_root_id, _dlt_parent_id, _dlt_list_idx, _dlt_id

SQLLoad

orders: order_id, status, _sqlload_source_file, _sqlload_source_version, _sqlload_source_row, _dlt_load_id, _sqlload_loaded_at, _dlt_id
orders__lines: sku, qty, _dlt_root_id, _dlt_parent_id, _dlt_list_idx, _dlt_load_id, _sqlload_loaded_at, _dlt_id
orders__tags: value, _dlt_root_id, _dlt_parent_id, _dlt_list_idx, _dlt_load_id, _sqlload_loaded_at, _dlt_id

The rows and every _dlt_id, _dlt_parent_id, _dlt_root_id and _dlt_list_idx are the same. SQLLoad adds _sqlload_loaded_at, and on a files source the file, version and line each row came from. Its child tables also carry _dlt_load_id, which dlt writes on root rows only (relational.py).

A table dlt loaded can carry on under SQLLoad with the same settings: the first load adds SQLLoad’s columns to it and merges into the rows dlt wrote. dlt’s default delete-insert merge gives root rows random ids, where SQLLoad’s merge (like dlt’s upsert) derives them from the key (helpers.py). From 0.41.1, SQLLoad’s merge also finds a merged row’s child rows by the id it held before, so the items dlt loaded are replaced like any others, and the row keeps its new, key-derived id from then on. Before 0.41.1 those items stay beside the new ones: load such a table from the start once instead, with sqlload load --reload --yes <table> for a Python table, or for a files source by dropping its tables and merging every file again, moving files after_load moved back under path first. Postgres targets refuse --reload (Reloading a table), so there drop the table and its child tables and load it again.

How _dlt_id is made, and the other ways names can differ, are under Naming.

A _dlt_loads view

SQLLoad doesn’t write _dlt_loads, _dlt_pipeline_state or _dlt_version. Positions come from the destination’s tables and the schema from the live tables, so neither is needed. For models that read _dlt_loads, such as a dbt source filtering on status = 0, a view over the source’s root tables gives the same columns. Where dlt loaded the dataset before, its _dlt_loads table is still there: rename it first, and UNION ALL it into the view to keep its loads. Rows dlt wrote have no _sqlload_loaded_at, so their loads show a NULL inserted_at in the view.

dlt_loads.sql

CREATE OR REPLACE VIEW shop._dlt_loads AS
SELECT
    _dlt_load_id AS load_id,
    'shop' AS schema_name,
    0 AS status,
    MIN(_sqlload_loaded_at) AS inserted_at
FROM (
    SELECT _dlt_load_id, _sqlload_loaded_at FROM shop.orders
    UNION ALL
    SELECT _dlt_load_id, _sqlload_loaded_at FROM shop.payments
) AS loads
GROUP BY _dlt_load_id;

Add a SELECT for each root table of the source. Child tables need none, since every load that writes child rows also writes their parents. The same statement works on DuckDB, MotherDuck, Postgres, Snowflake, BigQuery and ClickHouse. On BigQuery, shop is the dataset; on Snowflake, the unquoted names match the upper-case names SQLLoad stores.

The view differs from dlt’s table in what it can know:

• It lists the loads whose rows are still there. A load whose rows were all replaced, by replace or by merging every row again, drops out. In the example above, the first run’s payments load is gone once the second run merges every payment again.
• Each table’s load in a run has its own load_id, a UUID. dlt gives one per run, as a timestamp (load_package.py), so order by inserted_at rather than by load_id.
• status is always 0, since the view sees only committed rows. A load that failed after committing some batches shows with those batches. There is no schema_version_hash.

Running loads from an orchestrator

A task that ran pipeline.run() runs sqlload load --json instead, and needs no pipeline folder kept between runs, so it can run on workers whose disks are thrown away.

• The exit code says what to do: 0 when every table loaded, 75 when every failure may pass on a second try (connections, throttling, 5xx, locks), and 1 otherwise (configuration, credentials, permissions, data that doesn’t fit). Retry on 75 and alert on 1. See Retrying failed runs.
• Standard output holds one JSON document, with ok, retryable, and each table’s status, rows and error; logs go to standard error. See Results as JSON.
• A rerun after a failure loads no row twice and loses none: committed batches stay, and the next run carries on from the destination.

Known differences

Under naming = "dlt", SQLLoad’s tables still differ from dlt’s in a few ways:

• An object from a Python table or a database’s JSON column stays a JSON column, where dlt flattens it into parent__key columns (relational.py). A Python table’s flatten expands objects one level. A JSON or NDJSON files source flattens them as dlt does (added in 0.42).
• A value that doesn’t fit its column’s type fails the load, or widens the column as the table’s contract allows. dlt adds a variant column such as amount__v_text (schema.md).
• A field listed in unnest is always a child table, even when an XML element appears only once; dlt keeps a single match as a column of the parent.
• Two columns whose names normalise to one stop the plan, naming both, where dlt keeps one of them.
• Before 0.42.0, unnest is refused on Databricks and files targets, under delete_insert, state = "logged" and contract = "enforced", and for postgres_cdc sources, and plan names the setting. ClickHouse targets take it from 0.41.0.

The full list, with how row ids are derived, is under Naming.