Nested data

From 0.40.0, 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, without items and tags, 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__parts and orders__tags: a list of scalars gives a value column. A list inside a list gives its item a list field, 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

Set in a table entry:

• unnest: the dotted paths of the list fields, each with its parent’s path also listed (items before items.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.
• columns (added in 0.42.0): a child table’s column is named by its dotted path, such as "items.sku" = "text", to pin its type. Under contract = "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 under contract = "enforced" the field it meant is left out.
• row_id (also on a source): every table with unnest gives its rows an id, so it can’t be false.

Write strategies

• append: each run’s child rows link to that run’s rows.
• replace and --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 its unique_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). From 0.41.1, a merged row whose id didn’t come from its key, as one dlt loaded with its delete-insert merge, also loses the items its child rows tie to its old id in _sqlload_root_id (_dlt_root_id). Child rows an append loaded hold no root id, so they stay when the table later switches to merge.
• delete_insert (added in 0.42.0): 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 under merge, 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 and Snowflake targets, and from 0.41.0 ClickHouse targets, and from 0.42.0 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 from 0.42.0. 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 from 0.42.0, 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 from 0.42.0, 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”

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_id or _dlt_parent_id, whatever the naming), and is not another table’s child table (orders__notes__items when a table orders__notes holds no parent-id column). From 0.41.1, other tables named so, such as a plain orders__notes beside orders, are left as they are. Before that, a replace emptied them and a merge could delete rows from them.
• A list field first seen after a table’s first load is a new child table, which on_schema_change decides: append_new_columns creates it, ignore leaves its items out with a warning, and fail stops the load.

Naming

naming on a target, source or table chooses the names of the columns a load adds and of the child tables unnest makes. sqlload, the default, leaves every existing table as it was. dlt uses the names dlt gives them, so tables a dlt pipeline loaded can carry on under SQLLoad.

Under naming = "dlt" the tables and columns themselves are also named as dlt’s default snake_case naming names them: Orders-V2 loads as orders_v2, OrderID as order_id and Unit Price as unit_price, and a name longer than the destination allows (63 characters on Postgres) is shortened with a tag, as dlt shortens it. Settings may name a column either way (unique_key = ["OrderID"] or ["order_id"]), and plan shows the loaded name. exclude_columns, allow_columns and skip_tables_with_columns match the source’s own names, and flatten the names a Python table yields. 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.

The columns a load adds:

What it holds	naming = "sqlload"	naming = "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

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 naming = "dlt":

• Two columns whose names normalise to one (OrderID and Order ID) stop the plan, naming both; dlt keeps the last value of a row it is given as a dictionary. Exclude one of them with exclude_columns. Two tables of one source whose names normalise to one (order-items and order_items) stop the plan too; give one its own name. Added in 0.42: a postgres_cdc source loads under dlt’s names too, its snapshot and change stream matching each column by the source’s spelling.

• Added in 0.42: a JSON or NDJSON files source flattens objects into parent__key columns as dlt does, at every level and inside unnested items, and a list inside an object becomes a child table such as orders__customer__tags. An object from a Python table or a database’s JSON column stays a JSON column; a Python table’s flatten expands objects one level.

  • An 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 in columns to load it. An object that is NULL in a row loads as NULLs in its columns.
  • columns, mask, exclude_columns and allow_columns name the flattened columns (customer__email, or customer__*); a setting naming only the object fails the plan, listing its columns.
  • Upgrading from 0.40 or 0.41: a files source’s object columns, which held JSON, stop receiving values, and its objects load into new parent__key columns instead. A child table whose list field holds __ is now named with each part (orders__a__b for a__B, not orders__a_b), as a dlt pipeline names it, so it loads into a new table.

• A field that is only NULL in the sampled rows is a text column, beside the parent__key columns of its objects; dlt makes no column for it.

• A field listed in unnest always goes to its child table, even when an XML element appears only once; dlt keeps a single match as a column of the parent.