Question
I'm using dlt to load data from APIs or local JSONL files. When I look at my DuckDB database, dlt creates multiple tables instead of just one. Why does it do this?
Answer
dlt automatically normalizes nested JSON data into a relational schema. This is one of dlt's core features – it transforms complex, nested API responses into properly structured, queryable tables.
When dlt encounters nested data, it:
-
Flattens simple fields into columns on the main table
(e.g., {"user": {"name": "Alice"}} becomes user__name)
-
Creates child tables for nested arrays (lists)
Each nested array becomes its own table with a foreign key (_dlt_parent_id) linking back to the parent record
-
Infers types automatically (strings, integers, timestamps, etc.)
Example from the dlt workshop:
In Part 1 of the workshop, loading local Claude Code logs (which are deeply nested JSONL files) created 78 tables in DuckDB. This is because the logs contain nested structures like messages, usage objects, and tool calls – each array becomes a separate child table.
This is expected behavior. Normalization makes the data queryable with SQL – you can join the main table with its child tables to analyze nested data.
Course
LLM Zoomcamp
Workshop
dlt + Logfire (Module 5)
Related Links
Question
I'm using dlt to load data from APIs or local JSONL files. When I look at my DuckDB database, dlt creates multiple tables instead of just one. Why does it do this?
Answer
dlt automatically normalizes nested JSON data into a relational schema. This is one of dlt's core features – it transforms complex, nested API responses into properly structured, queryable tables.
When dlt encounters nested data, it:
Flattens simple fields into columns on the main table
(e.g.,
{"user": {"name": "Alice"}}becomesuser__name)Creates child tables for nested arrays (lists)
Each nested array becomes its own table with a foreign key (
_dlt_parent_id) linking back to the parent recordInfers types automatically (strings, integers, timestamps, etc.)
Example from the dlt workshop:
In Part 1 of the workshop, loading local Claude Code logs (which are deeply nested JSONL files) created 78 tables in DuckDB. This is because the logs contain nested structures like messages, usage objects, and tool calls – each array becomes a separate child table.
This is expected behavior. Normalization makes the data queryable with SQL – you can join the main table with its child tables to analyze nested data.
Course
LLM Zoomcamp
Workshop
dlt + Logfire (Module 5)
Related Links