Flattening Nested JSON API Responses

26.1 How dlt Normalises Nested Data (20 min)

On Day 8 you flattened JSON by hand. dlt does it automatically using two rules:

Rule 1 — nested dictionaries become columns, joined with a double underscore:

{"series": "V39079", "detail": {"label": "Overnight", "unit": "%"}}
      ↓
table: series | detail__label | detail__unit

Rule 2 — lists become child tables, linked to their parent:

{"series": "V39079", "obs": [{"d": "2026-07-01", "v": 2.25}, {"d": "2026-08-01", "v": 2.25}]}
      ↓
parent table:            series | _dlt_id
child table parent__obs: d | v | _dlt_parent_id | _dlt_list_idx

This is relational normalisation: the same idea as the star schema of Day 12, applied automatically to documents.

Create day26/nesting_basics.py:

from common import practice_pipeline, reset

reset("nest_lab")
p = practice_pipeline("nest_lab", "lab")

doc = {
    "series": "V39079",
    "detail": {"label": "Target for the overnight rate", "unit": "percent"},
    "obs": [{"d": "2026-07-01", "v": 2.25}, {"d": "2026-08-01", "v": 2.25}],
}
p.run([doc], table_name="nested_demo")

duckdb nest_lab.duckdb

.mode box
SHOW TABLES FROM lab;              -- or: SELECT table_name FROM information_schema.tables
SELECT * FROM lab.nested_demo;
SELECT * FROM lab.nested_demo__obs;

-- Re-join child to parent
SELECT p.series, p.detail__label, c.d, c.v
FROM lab.nested_demo      AS p
JOIN lab.nested_demo__obs AS c ON c._dlt_parent_id = p._dlt_id
ORDER BY c._dlt_list_idx;

26.2 Loading a Raw Valet Response As-Is (25 min)

Let's load the complete Valet response for two series with no parsing at all. Create day26/valet_raw.py:

import dlt
from dlt.sources.helpers import requests
from common import practice_pipeline, reset


@dlt.resource(name="valet_raw", write_disposition="replace")
def valet_raw(series_ids, recent=5):
    url = f"https://www.bankofcanada.ca/valet/observations/{','.join(series_ids)}/json"
    r = requests.get(url, params={"recent": recent}, timeout=30)
    r.raise_for_status()
    yield r.json()                       # the whole payload: one document


reset("nest_lab")
p = practice_pipeline("nest_lab", "lab")
p.run(valet_raw(["V39079", "FXCADUSD"]))
print(p.default_schema.to_pretty_yaml())

Inspect what dlt built:

SELECT table_name FROM information_schema.tables
WHERE table_schema = 'lab' AND table_name LIKE 'valet%';

DESCRIBE lab.valet_raw;
DESCRIBE lab.valet_raw__observations;
SELECT * FROM lab.valet_raw__observations LIMIT 5;

What happened:

• terms (a dict) became columns such as terms__url in the root table.
• seriesDetail (a dict keyed by series ID) became columns like series_detail__v39079__label, series_detail__fxcadusd__label, … one set of columns per series.
• observations (a list) became the child table valet_raw__observations, with columns such as d, v39079__v, fxcadusd__v.

The list was handled beautifully. The dict keyed by IDs exploded into columns. With 50 series you'd get hundreds of columns, and a new series would change the schema (which a frozen contract would block!).

Loading raw payloads is still valuable. Keeping untouched responses in a raw table (with evolve) is your Day 9 landing zone, now queryable in SQL. For modelling, though, reshape the data first.

26.3 "Keys as Data": Reshaping Before Loading (23 min)

The rule of thumb:

If a JSON key is actually a value (a series ID, a date, a country code), turn it into a row.

Valet uses series IDs as keys in two places. Let's fix both with small Python functions and add_map:

def reshape_valet(payload):
    """Turn Valet's ID-keyed structures into lists of records."""
    series = [
        {"series_id": sid, "label": info.get("label"), "description": info.get("description")}
        for sid, info in payload.get("seriesDetail", {}).items()
    ]
    observations = []
    for obs in payload.get("observations", []):
        for sid in payload.get("seriesDetail", {}):
            cell = obs.get(sid)
            if isinstance(cell, dict):
                observations.append({"date": obs["d"], "series_id": sid, "v": cell.get("v")})
    return {"series": series, "observations": observations}


reset("nest_lab")
p = practice_pipeline("nest_lab", "lab")
p.run(valet_raw(["V39079", "FXCADUSD", "BD.CDN.10YR.DQ.YLD"]).add_map(reshape_valet),
      table_name="valet_tidy")

DESCRIBE lab.valet_tidy__series;         -- series_id | label | description
DESCRIBE lab.valet_tidy__observations;   -- date | series_id | v
SELECT series_id, COUNT(*) FROM lab.valet_tidy__observations GROUP BY 1;

Now adding a series adds rows, not columns. The schema is stable, which is exactly what a frozen contract needs.

26.4 StatCan WDS: Letting dlt Flatten for You (20 min)

StatCan's vector response is a list of {status, object: {vectorId, …, vectorDataPoint: [...]}}. Its structure is already relational (lists, not ID-keyed dicts), so we can let dlt do all the work:

@dlt.resource(name="wds_vectors", write_disposition="replace")
def wds_vectors(vectors, latest_n=6):
    r = requests.post(
        "https://www150.statcan.gc.ca/t1/wds/rest/getDataFromVectorsAndLatestNPeriods",
        json=[{"vectorId": v, "latestN": latest_n} for v in vectors], timeout=30)
    r.raise_for_status()
    yield r.json()                  # a list of documents; each becomes a root row


reset("nest_lab")
p = practice_pipeline("nest_lab", "lab")
p.run(wds_vectors([41690973, 2062815]))

SELECT table_name FROM information_schema.tables
WHERE table_schema = 'lab' AND table_name LIKE 'wds%';

-- The data points live in a child table; join back to the vector ID
SELECT v.object__vector_id                 AS vector_id,
       dp.ref_per,
       dp.value
FROM lab.wds_vectors                              AS v
JOIN lab.wds_vectors__object__vector_data_point   AS dp
     ON dp._dlt_parent_id = v._dlt_id
ORDER BY vector_id, dp.ref_per;

(Check exact column names with DESCRIBE: normalisation turns vectorDataPoint into vector_data_point and refPer into ref_per.)

Compare with Day 22's hand-written statcan_source: same result, zero parsing code, and every field StatCan sends (decimals, scalarFactorCode, symbolCode, …) is preserved.

Try it 26.4

Using only SQL on the tables above, produce our standard long format (date, series_id, value, source) with series_id like v41690973.

SELECT CAST(dp.ref_per AS DATE)             AS date,
       'v' || v.object__vector_id           AS series_id,
       CAST(dp.value AS DOUBLE)             AS value,
       'statcan'                            AS source
FROM lab.wds_vectors                            AS v
JOIN lab.wds_vectors__object__vector_data_point AS dp ON dp._dlt_parent_id = v._dlt_id
WHERE dp.value IS NOT NULL
ORDER BY series_id, date;

26.5 Controlling Depth: max_table_nesting and JSON Columns (20 min)

Deeply nested APIs can produce many child tables. max_table_nesting sets how many levels become tables. Anything deeper is stored as a JSON column instead.

@dlt.resource(name="wds_shallow", write_disposition="replace", max_table_nesting=0)
def wds_shallow(vectors, latest_n=6):
    yield from wds_vectors(vectors, latest_n)

yield from passes along every item another generator produces. It's a neat way to reuse a resource.

With max_table_nesting=0, nested data isn't flattened at all: no child tables are created, and nested structures are stored as JSON in the root table.

DESCRIBE lab.wds_shallow;

-- DuckDB can query JSON directly
SELECT object ->> '$.vectorId'                           AS vector_id,
       json_array_length(object -> '$.vectorDataPoint')  AS n_points,
       object -> '$.vectorDataPoint[0]' ->> '$.refPer'   AS first_ref_per
FROM lab.wds_shallow;

When is a JSON column the right choice?

• The nested part is rarely queried (e.g. footnotes, audit metadata).
• Its structure is irregular, so a child table would be mostly NULLs.
• You want to defer decisions: keep it now, flatten in SQL later.

26.6 Mini-Project: StatCan Cube Metadata → a Product Hierarchy (28 min)

StatCan's getCubeMetadata endpoint describes a table's dimensions and their members, including each member's parent. For CPI, that's the product hierarchy: All-items → Food → Food purchased from stores → …. That hierarchy is exactly what the Day 19 stretch goal (diffusion over leaf-level components) needed.

Tasks:

1. Write a resource cube_metadata(product_ids) that POSTs [{"productId": pid}, ...] to https://www150.statcan.gc.ca/t1/wds/rest/getCubeMetadata and yields the response.
2. Load it for CPI (18100004) into the practice pipeline, with root_key=True on a small source wrapper so every child row carries _dlt_root_id.
3. Explore the tables dlt created (expect a dimension table and a nested member table).
4. In SQL, list the dimensions of the CPI table and the number of members in each.
5. For the Products and product groups dimension, list the members whose parent is All-items: the top level of the CPI basket.
6. Find the leaf products (members that are nobody's parent).

import dlt
from dlt.sources.helpers import requests
from common import practice_pipeline, reset

WDS = "https://www150.statcan.gc.ca/t1/wds/rest"


@dlt.source(name="statcan_meta", root_key=True)
def statcan_meta(product_ids):

    @dlt.resource(name="cube_metadata", write_disposition="replace")
    def cube_metadata():
        r = requests.post(f"{WDS}/getCubeMetadata",
                          json=[{"productId": pid} for pid in product_ids], timeout=60)
        r.raise_for_status()
        yield r.json()

    return cube_metadata


reset("nest_lab")
p = practice_pipeline("nest_lab", "lab")
p.run(statcan_meta([18100004]))

-- 3. What was created?
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'lab' AND table_name LIKE 'cube_metadata%';
DESCRIBE lab.cube_metadata__object__dimension;
DESCRIBE lab.cube_metadata__object__dimension__member;

-- 4. Dimensions and member counts
SELECT d.dimension_name_en, COUNT(*) AS n_members
FROM lab.cube_metadata__object__dimension          AS d
JOIN lab.cube_metadata__object__dimension__member  AS m ON m._dlt_parent_id = d._dlt_id
GROUP BY d.dimension_name_en;

-- 5. Top level of the product hierarchy
WITH members AS (
    SELECT m.member_id, m.parent_member_id, m.member_name_en
    FROM lab.cube_metadata__object__dimension          AS d
    JOIN lab.cube_metadata__object__dimension__member  AS m ON m._dlt_parent_id = d._dlt_id
    WHERE d.dimension_name_en = 'Products and product groups'
)
SELECT child.member_name_en
FROM members AS child
JOIN members AS parent ON child.parent_member_id = parent.member_id
WHERE parent.member_name_en = 'All-items'
ORDER BY 1;

-- 6. Leaves: members that are nobody's parent
WITH members AS (
    SELECT m.member_id, m.parent_member_id, m.member_name_en
    FROM lab.cube_metadata__object__dimension          AS d
    JOIN lab.cube_metadata__object__dimension__member  AS m ON m._dlt_parent_id = d._dlt_id
    WHERE d.dimension_name_en = 'Products and product groups'
)
SELECT COUNT(*) AS n_leaves
FROM members AS m
WHERE NOT EXISTS (SELECT 1 FROM members AS c WHERE c.parent_member_id = m.member_id);

Day 26 checklist

• I can predict how dlt turns nested dicts and lists into columns and child tables
• I can re-join child tables to parents with _dlt_parent_id, _dlt_list_idx and _dlt_root_id
• I recognise "keys as data" and reshape it into rows before loading
• I can choose between flattening in Python, in dlt, or in SQL
• I can limit nesting and query JSON columns in DuckDB
• I extracted StatCan's CPI product hierarchy from nested metadata

git add .
git commit -m "Day 26: nested JSON, child tables, JSON columns, CPI hierarchy"
git push