Concepts
Nested data
List fields as child tables with unnest, objects as columns with flatten: how child rows link to their parents, what each write strategy does with them, what unnest = "all" and flatten = "all" choose, and how names load.
unnest turns list fields into child tables, the way dlt does. A
field that isn’t unnested stays a JSON column.
[sources.orders.tables.orders]unnest = ["items", "items.parts", "tags"]With this, {"id": 1, "items": [{"sku": "a", "parts": [1, 2]}], "tags": ["x"]} loads:
orders:id, withoutitemsandtags, and an id for each row in_sqlload_row_id.orders__items: one row per item (sku), with_sqlload_parent_id(its order’s row id),_sqlload_list_idx(its place in the list, from 0) and its own_sqlload_row_id.orders__items__partsandorders__tags: a list of scalars gives avaluecolumn. A list inside a list gives its item alistfield, a level further down.
Each child row also carries the load’s _sqlload_load_id and _sqlload_loaded_at. A NULL, missing
or empty list gives no child rows. Child tables take their columns from their rows, as a Python
table does, and follow on_schema_change like any other table.
Settings
Section titled “Settings”Set in a table entry:
unnest: the dotted paths of the list fields, each with its parent’s path also listed (itemsbeforeitems.parts). Or"all", described below.max_depth: how many levels of child tables to make at most, 1000 by default. A list deeper than that stays JSON in its parent. It limitsflattentoo.columns: a child table’s column is named by its dotted path, such as"items.sku" = "text", to pin its type. Undercontract = "enforced", each child table loads only the columns named so, with its id and link columns, and leaves out the rest. These keys aren’t checked against the data, since a field may be missing from the first rows: a misspelled one is ignored, and undercontract = "enforced"the field it meant is left out.row_id(also on a source): every table withunnestgives its rows an id, so it can’t befalse.
Write strategies
Section titled “Write strategies”append: each run’s child rows link to that run’s rows.replaceand--reload: child tables are replaced with their parent. A child table no row reaches any more is emptied.merge: a row’s id comes from itsunique_key, so a merged row keeps its id and its items carry it in_sqlload_root_id. At each commit, the merged rows’ items are deleted from every child table, then their new items are inserted. A row whose list shrank, emptied or went missing keeps only what it holds now. When a commit repeats a key, only the kept row’s items load (see Repeated keys). A merged row whose id didn’t come from its key, as one dlt loaded with itsdelete-insertmerge, also loses the items its child rows tie to its old id in_sqlload_root_id(_dlt_root_id). Child rows anappendloaded hold no root id, so they stay when the table later switches tomerge.delete_insert: each row’s items carry its id in_sqlload_root_id. Before a commit deletes its window or partitions from the parent, the items of the rows it deletes leave every child table, then the commit’s new items are inserted. As undermerge, a failure between the two leaves those rows without items until a rerun loads the window again.
Child tables load into DuckDB, MotherDuck, Postgres, BigQuery, Snowflake, ClickHouse and
Databricks targets. Databricks child
tables are staged and copied in as their parent is; so far they are tested against a stand-in for
the Databricks API, not a live warehouse.
state = "logged" and contract = "enforced" tables write child tables. A logged
table’s child rows commit just before the commit that records its state. When that commit fails, a
rerun reads from the state last recorded and loads those rows and their items again. Under
append and delete_insert, whose row ids are random, the failed commit’s child rows stay behind
with a parent id no row holds, so a join on the parent id never shows them. Under merge a row’s
id comes from its key, so the rerun clears those items and loads them once.
Files targets write child tables, each in a folder of its own beside its parent’s
(orders__items/ beside orders/), under append and replace with the paths to unnest listed.
The target’s path names the folder by {table} and is not partitioned by the cursor. "all",
merge and delete_insert are refused on files targets. Each commit stores the child tables’
files before its parent’s, so a failure between the two leaves files whose parent rows are not
there, which a join by _sqlload_parent_id leaves out.
postgres_cdc sources write child tables, except into files targets, from a json or
jsonb column whose values are lists. Each row’s id is the hash of its key (in a
changelog, of the change’s _sqlload_lsn, _sqlload_seq and key), in the snapshot as in the
changes. Each captured insert or update replaces its row’s items, and a delete removes them under
deletes = "hard"; under soft deletes a deleted row keeps its items, as it keeps its row. An
update that left a TOASTed list out, unchanged, keeps the row’s items. A changelog’s change rows
each get their own items. A batch replayed after a stop before the slot confirmed it writes the
same items again rather than more.
ClickHouse has no transactions, so its child tables are published the way their parent is: by
swapping in whole tables or whole partitions. Under merge, the merged rows’ old items leave each
child table in a partition publish of their own, and the new items are then added. Child tables
are created with ClickHouse’s default engine and no sorting key or partitions, whatever layout
their parent sets, so each such publish rebuilds the whole child table.
A commit’s checks (checks, and on_duplicate_keys = "fail" under merge) run on the parent’s
rows before any child table is written, so a refused commit leaves the child tables as they were.
Each commit then writes the child tables before their parent. If a load fails between the two, child
tables can hold rows whose parent row was never committed, or under merge, a merged row’s old
items can be gone before its new ones land. A rerun loads those rows again and puts them right.
Join child rows to their parent by _sqlload_parent_id rather than reading child tables on their
own.
On a files source, SQLLoad reads each file itself to split its rows, rather than having the
warehouse read the files directly. Loads with unnest are slower than without it.
unnest = “all”
Section titled “unnest = “all””unnest = "all" unnests every list field, at every level down to max_depth:
- A field becomes a child table when the values seen so far are lists, from the sampled files or a Python table’s first rows. An object stays a JSON column.
- NULL, missing and empty values decide nothing. A field that held only those in what was sampled isn’t unnested until a list appears in it, and then gets its child table.
- Once a field is a child table, a later value that is neither a list nor NULL fails the load, naming the field. Its rows are never stored as JSON or dropped. To keep such a field as JSON, list the paths to unnest without it.
- A field with a child table from an earlier load stays one. A table counts as one only when it is
named
<table>__<field>and has a parent-id column (_sqlload_parent_idor_dlt_parent_id, whatever thecompat), and is not another table’s child table (orders__notes__itemswhen a tableorders__notesholds no parent-id column). Other tables named so, such as a plainorders__notesbesideorders, are left as they are. - A list field first seen after a table’s first load is a new child table, which
on_schema_changedecides:append_new_columnscreates it,ignoreleaves its items out with a warning, andfailstops the load.
Flattening objects
Section titled “Flattening objects”flatten loads an object’s keys as columns of their own, named <object>__<key>. An object it
doesn’t flatten stays one JSON column (VARIANT on Snowflake). It works on every source whose rows
hold objects: Python tables and their sl.http jobs, JSON and NDJSON files, and a files or Google
Sheets table with a transform, whose output it
flattens. Any other source fails plan.
flatten takes the same shape as unnest: dotted paths, or "all".
[sources.orders.tables.orders]unnest = ["items"]flatten = ["customer", "customer.address", "items.dimensions"] # paths reach inside unnested items- A path’s parent must be flattened or unnested too (
customerbeforecustomer.address). "all"flattens every object at every level, down tomax_depth. An object field that a later load brings becomes new columns, whichon_schema_changedecides.- A path or name may use the source’s spelling or the loaded one (
LineTotalsorline_totals). - A NULL or missing object leaves its columns NULL. Lists are never flattened;
unnestmakes them child tables. - Flattened names keep the
parent__keyform, and then go throughnaming(customer__address__city).
Under "all", plan lists each object it flattens, with its column count and first keys, and the
nested columns it keeps as JSON. It warns once when an object looks like a map whose keys are data
(more than 20 keys, or most of them holding a digit or not a plain name):
├── flatten every object, 1000 levels down at most │ customer → 3 columns (name, address__city, …) │ customer.address → 2 columns (city, zip) │ line_totals → 3 columns (sku_14, sku_99, …) │ ⚠ an object whose keys are data adds a column per key on every load; │ keep it as one column with columns = { line_totals = "json" }A list of paths names its objects with no warning. On a Python table, plan can’t see the rows, so
it says the objects flatten as rows arrive at load.
Keeping an object whole
Section titled “Keeping an object whole”A map whose keys are data ("line_totals": {"sku-14": 12.5}) makes one column per key. Pin it as
json to keep it, and everything under it, as one column:
[sources.orders.tables.orders]flatten = "all"columns = { line_totals = "json" }The pinned object leaves the list, and plan shows kept as JSON: line_totals. An object a mask,
or an exclude_columns or allow_columns name (not a pattern) names is kept whole too, so the
setting applies to all of it. Pinning a flattened object as any other type fails plan:
columns line_totals = "text": flatten replaces line_totals with its keys' columns; pin it "json" to keep it wholeplan also fails on a listed path that a json pin, mask or name keeps whole, and on an
exclude_columns or allow_columns pattern that matches an object but none of its columns. Under
"all", where a Python table’s objects only show as it loads, such an exclude_columns pattern
fails the load instead. The load also fails when a listed path holds a scalar or a list, or when a
key’s column is also a field of the rows or comes from another object.
Turning flatten on for a table that already holds the object as a JSON column leaves that column
NULL from then on, next to the new key columns.
Naming
Section titled “Naming”Every table and column loads under a name the warehouse takes without quoting.
naming chooses the case, rename names columns yourself, on_duplicate_names decides what
happens when two columns end up with one name, and compat chooses the names of the columns a load
adds. Each is set on a source or a table, and naming and compat also on the target; the closest
one wins.
Each column’s name goes through these steps in turn: its source name, then naming, then rename,
then on_duplicate_names. For a CSV file whose header is Order ID,OrderDate,Qty,Qty,Unit Price,
with rename = { "Order ID" = "order_ref" }, plan shows:
$ sqlload plan -v…Objects (1) sqlload apply└── raw.erp create table ├── order_ref int -> BIGINT ├── order_date text -> VARCHAR ├── qty int -> BIGINT ├── qty_1 int -> BIGINT ├── unit_price float -> DOUBLE └── + _sqlload_source_file, _source_version, _source_row, _load_id, _loaded_at
Loads (1) sqlload load└── raw.erp append ├── source files ./drop/ csv, compression by file name ├── route read csv into Arrow -> DuckDB takes Arrow ├── columns 5, 3 named by snake_case: OrderDate -> order_date, Qty -> qty, ... ├── names Order ID -> order_ref (rename) │ Qty (column 4) -> qty_1 (numbered)…The columns line counts the columns naming alone changed and shows the first few. The names
lines list every column rename named or numbering numbered, since those names depend on the
columns’ order. load prints the same names lines under each table, and its
JSON result gives them in each table’s names.
naming
Section titled “naming”snake_case, the default, names tables and columns as dlt’s defaultsnake_casenaming does:Orders-V2loads asorders_v2,OrderIDasorder_idandUnit Priceasunit_price. A name longer than the destination allows (63 characters on Postgres) is shortened with a tag, as dlt shortens it.sourcekeeps the source’s names, quoting them where the warehouse needs it. Table names made from atable_nametemplate are in lower case.
Settings may name a column either way (unique_key = ["OrderID"] or ["order_id"]), and plan
shows the loaded name, and so may flatten paths. Any other spelling, including another case
(orderid), fails plan, which suggests the nearest column in both spellings: unique_key column "orderid" is not a column of the table; did you mean OrderID (loads as order_id)?.
exclude_columns, allow_columns and skip_tables_with_columns match the source’s own names: a
Python table’s fields as yielded (accessToken, or api_secret__key once flattened), before naming
changes them. A Python table’s ctx.cursor_column is the cursor’s loaded name (updated_at for a
field yielded as UpdatedAt). The target schema keeps the name it is given.
rename
Section titled “rename”rename is a map of source names to loaded names, or the name of a Python function:
[sources.erp.tables.orders]rename = { "Order ID" = "order_ref" } # a map; a key is the source name or the snake_case onepython_paths = ["renames"]
[sources.erp.tables.orders]rename = "erp_names" # a function registered with @sl.renameimport sqlload as sl
@sl.rename # registered as "erp_names", the function's namedef erp_names(columns, ctx): return {c.position: "qty_returned" for c in columns if c.source == "Qty" and c.position > 2}-
The function gets every column in order, each an
sl.Columnwith itsposition(from 1), itssourcename and itsdefaultname (afternaming), andctx, ansl.RenameContextwith thesourceand thetable. It returns only the names it changes, keyed by position, so it can tell two repeated headers apart. It runs once per table atplanand atload. -
@sl.rename("erp")registers a function under another name. Functions are found onpython_paths, the same way Python sources are. A Python table may pass the function itself:@shop.table(rename=erp_names). -
planfails on a name no function has, listing the registered ones, and on a name two functions have, naming both files:rename erp_names: 2 functions are registered as erp_names, in renames/erp.py (erp_names) and renames/other.py (other_names); give one another name with @sl.rename("...") -
A function
renameruns only in thesqlloadPython package’s command (uv tool install sqlload); the standalone binary failsplanand asks for a map instead. -
A rename that gives a column a name another column has fails
plan, whateveron_duplicate_namessays:rename gives column "Qty" the name order_id, which column "Order ID" loads as too; give each a name of its own
on_duplicate_names
Section titled “on_duplicate_names”on_duplicate_names decides what happens when columns meet under one name, ignoring case: a
header repeated in a file or sheet, or names that collide after naming (OrderID and
Order ID).
-
number, the default, keeps the first column’s name and numbers the others_1,_2, …, skipping names the table already has: a headerqty, qty_1, qtyloads asqty, qty_1, qty_2. -
failstops the plan, naming the columns:columns "OrderID" and "Order ID" both load as order_id; give them names of their own with rename, or set on_duplicate_names = "number"
The order of the numbers on a new table:
- A repeated header is numbered by its place: the first
Qtykeepsqty, the next isqty_1. - Different source names are numbered in the order of their names, not their places, so files
that list the columns in another order load each into the same column.
Order IDsorts beforeOrderID, soOrder IDkeepsorder_idandOrderIDloads asorder_id_1.
Once a table exists, a number stays with the source name it was given to. The table’s run log
records them, for every kind of table, state = "logged" Python tables included. A later file that
adds ORDER_ID, which sorts first, gets the next free number, order_id_2, and the other two keep
their columns. A clash on a column the table has, when no load recorded which source name it holds,
fails rather than number the columns afresh. When the source name a numbered column holds is gone
and another one would take its column, plan fails rather than load other data into it:
Errors (1)└── raw.orders └── reason orders: column order_id holds "Order ID" from earlier loads, which the source no longer has, and "ORDER_ID" would now load into it; give "ORDER_ID" a name of its own with rename, or rename it to order_id if it is the same column under a new name--reload rebuilds the table and numbers it afresh, and its record replaces the earlier ones.
A Python table’s fields are named as they arrive. A field that first shows in a later batch keeps clear of the names earlier batches gave, taking the next free number.
compat
Section titled “compat”compat chooses the names of the columns a load adds and of the child tables unnest makes.
sqlload, the default, uses SQLLoad’s own. dlt uses the ones dlt gives them, so tables a dlt
pipeline loaded can carry on under SQLLoad. compat doesn’t change the case of the source’s
names; naming’s default already matches dlt’s.
| What it holds | compat = "sqlload" |
compat = "dlt" |
|---|---|---|
| The load that wrote the row | _sqlload_load_id |
_dlt_load_id |
| When that load started | _sqlload_loaded_at |
_sqlload_loaded_at (dlt has none) |
| The row’s own id | _sqlload_row_id, with row_id or unnest |
_dlt_id, always |
| A child row’s parent row | _sqlload_parent_id |
_dlt_parent_id |
| A child row’s place in its list, from 0 | _sqlload_list_idx |
_dlt_list_idx |
A child row’s root row, under merge |
_sqlload_root_id |
_dlt_root_id |
Under compat = "dlt", plan refuses row_id = false. Row ids are made as dlt makes them: random
for a root row; under merge, a hash of the unique_key, as dlt’s upsert merge gives it (its
default delete-insert merge gives random ones); and for a child row a hash of its parent’s id, its
table and its place in the list, so a merged row’s children keep their ids on every load. dlt’s own
tables (_dlt_loads, _dlt_pipeline_state, _dlt_version) are not written.
Where SQLLoad and dlt still differ under compat = "dlt":
-
Two columns whose names meet after
naming(OrderIDandOrder ID) are numbered, as above; dlt keeps the last value of a row it is given as a dictionary. Two keys of one child item that meet still stop the load, naming both. Two tables of one source whose names meet (order-itemsandorder_items) stop the plan; give one its ownname. Apostgres_cdcsource loads under the same names too, its snapshot and change stream matching each column by the source’s spelling. -
JSON and NDJSON files default to dlt’s shape:
flatten = "all"and, on a warehouse,unnest = "all". A table’s ownflattenorunnestreplaces the default;[]turns it off. An object from a Python table or a database’s JSON column stays a JSON column unless its table setsflatten.- A flattened object’s keys are columns like any other: a key first seen after the sampled
rows fails the load, naming its column (
customer__vip); pin it incolumnsto load it.
- A flattened object’s keys are columns like any other: a key first seen after the sampled
rows fails the load, naming its column (
-
A field that is only NULL in the sampled rows is a text column, beside the
parent__keycolumns of its objects; dlt makes no column for it. -
A field listed in
unnestalways goes to its child table, even when an XML element appears only once; dlt keeps a single match as a column of the parent.