Compliant Web Scraping

Flattening Nested JSON Into Tabular Rows #

The gap between a nested API payload and a queryable table is a handful of decisions, and each one has a wrong answer that works on a sample and fails at scale. This guide implements a flattener that produces a parent row plus child tables without Cartesian explosion, preserves array order, and quarantines what it cannot handle, as part of normalizing nested JSON responses in the Data Parsing & Transformation Pipelines section.

Problem Framing #

The naive recursive flatten works until a payload contains two sibling arrays. A record with 8 images and 12 variants, flattened by cross-joining both, becomes 96 rows — and the same record next month with 20 and 30 becomes 600. Row counts stop meaning anything, aggregates skew, and the storage bill grows quadratically in something nobody is tracking.

Sibling arrays: two ways to flattenThe explosion only becomes visible at production scale.Sibling arrays: two ways to flattenCross-join both arrays8 images and 12 variantsbecome 96 parent rowsRow counts stop meaning anythingGrowth is quadraticChild tables per arrayOne parent row, two child tables8 rows and 12 rowsRow counts track realityGrowth is linear
The explosion only becomes visible at production scale.

Three further decisions matter. Array order is frequently meaningful — the first image is the primary one, the first address is the billing one — and a flatten that discards position loses information the source considered important. Key collisions happen when a nested path and a literal dotted key produce the same column name, and the second write silently wins. And unbounded depth turns a recursive or unexpectedly deep payload into a stack overflow at three in the morning.

Step-by-Step Implementation #

1. Objects become columns, arrays of objects become child tables #

One payload through the flattenerLineage is part of the flatten contract, not a later join.One payload through the flattener1Walk objects intodotted columns2Split object arraysinto child tables3Check for columncollisions4Attach lineage toparent and children
Lineage is part of the flatten contract, not a later join.
from typing import Any

RESERVED = {"source_url", "fetched_at", "schema_version", "_index", "_parent_key"}

def flatten(node: Any, prefix: str = "", depth: int = 0, max_depth: int = 8
            ) -> tuple[dict, dict[str, list[dict]]]:
    """Return (scalar columns, {child_table: [rows]}).

    - dict           → dotted column prefix
    - list[scalar]   → joined into one column (order preserved)
    - list[dict]     → its own child table with an _index column
    """
    if depth > max_depth:
        raise ValueError(f"payload exceeds max depth {max_depth} at {prefix or '<root>'}")

    columns: dict = {}
    children: dict[str, list[dict]] = {}

    for key, value in (node or {}).items():
        safe_key = f"src_{key}" if key in RESERVED else key
        path = f"{prefix}{safe_key}"

        if isinstance(value, dict):
            sub_cols, sub_children = flatten(value, f"{path}.", depth + 1, max_depth)
            columns.update(sub_cols)
            children.update(sub_children)

        elif isinstance(value, list):
            if value and isinstance(value[0], dict):
                rows = []
                for index, item in enumerate(value):
                    row, nested = flatten(item, "", depth + 1, max_depth)
                    row["_index"] = index                 # array order is data
                    rows.append(row)
                    for name, nested_rows in nested.items():
                        children.setdefault(f"{path}.{name}", []).extend(nested_rows)
                children[path] = rows
            else:
                columns[path] = "|".join("" if v is None else str(v) for v in value)

        else:
            columns[path] = value

    return columns, children

Renaming keys that collide with reserved names is the small detail that prevents upstream data from overwriting your lineage columns — a payload containing a field literally called source_url would otherwise silently replace the URL the record was fetched from.

2. Detect collisions rather than letting the last write win #

def check_collisions(columns: dict, sink_normalise=str.lower) -> list[str]:
    """Distinct source paths that collapse to the same sink column name."""
    seen: dict[str, list[str]] = {}
    for path in columns:
        seen.setdefault(sink_normalise(path), []).append(path)
    return [paths for paths in seen.values() if len(paths) > 1]

Sinks normalise identifiers — lowercasing, replacing dots, truncating to a length limit — and two paths that differ only in a way the sink removes become one column. Passing the sink’s own normaliser in means the check reflects the actual destination rather than a guess about it.

3. Propagate lineage into child rows #

Child rows are useless without a link back to their parent and to the fetch that produced them.

import uuid

def assemble(payload: dict, context: dict) -> tuple[dict, dict[str, list[dict]]]:
    columns, children = flatten(payload)

    collisions = check_collisions(columns)
    if collisions:
        raise ValueError(f"column collisions: {collisions}")

    parent_key = context.get("natural_key") or str(uuid.uuid4())
    lineage = {
        "source_url": context["source_url"],
        "fetched_at": context["fetched_at"],
        "schema_version": context["schema_version"],
    }
    parent = {**columns, **lineage, "natural_key": parent_key}

    for table, rows in children.items():
        for row in rows:
            row["_parent_key"] = parent_key
            row.update(lineage)                 # children carry the same lineage as the parent
    return parent, children

Making lineage propagation part of the flatten step’s contract, rather than a later join, is what keeps it intact — a later join is precisely the operation that tends to drop it.

4. Track types per path #

A field that is an integer on most records and a string on a few is upstream drift, and the sink’s column type is decided by whichever record arrived first.

from collections import defaultdict

class TypeObserver:
    def __init__(self):
        self.seen: dict[str, set[str]] = defaultdict(set)
        self.examples: dict[str, dict[str, object]] = defaultdict(dict)

    def observe(self, columns: dict) -> None:
        for path, value in columns.items():
            name = type(value).__name__
            self.seen[path].add(name)
            self.examples[path].setdefault(name, value)

    def drift(self) -> dict[str, dict[str, object]]:
        """Paths with more than one non-null type, with an example of each."""
        return {p: self.examples[p] for p, types in self.seen.items()
                if len(types - {"NoneType"}) > 1}

Treating NoneType as compatible with everything keeps the report focused: a nullable field appearing as null is normal, whereas a field that is both an integer and a string is a real change. Including an example of each observed type makes the report actionable without a query against the raw payloads.

Verification & Testing #

Flattener guaranteesEach guarantee corresponds to a failure that is otherwise silent.Flattener guarantees✓Array order is preserved as an index column✓Depth beyond the limit quarantines rather than recurses✓Reserved lineage names cannot be overwritten✓Column collisions are detected, not resolved by last write✓Type drift per path is reported with examples
Each guarantee corresponds to a failure that is otherwise silent.
def test_sibling_arrays_do_not_multiply():
    payload = {"id": 1,
               "images": [{"url": "a"}, {"url": "b"}, {"url": "c"}],
               "variants": [{"sku": "x"}, {"sku": "y"}]}
    parent, children = flatten(payload)
    assert len(children["images"]) == 3
    assert len(children["variants"]) == 2
    assert "images" not in parent                     # arrays never become parent columns

def test_array_order_is_preserved():
    _, children = flatten({"images": [{"url": "first"}, {"url": "second"}]})
    assert [row["_index"] for row in children["images"]] == [0, 1]

def test_depth_limit_raises():
    deep = {"a": {"b": {"c": {"d": {"e": {"f": {"g": {"h": {"i": 1}}}}}}}}}
    with pytest.raises(ValueError, match="max depth"):
        flatten(deep, max_depth=5)

def test_reserved_keys_are_renamed():
    columns, _ = flatten({"source_url": "http://upstream.example/x"})
    assert "src_source_url" in columns
    assert "source_url" not in columns

def test_scalar_arrays_join_in_order():
    columns, _ = flatten({"tags": ["b", "a", "c"]})
    assert columns["tags"] == "b|a|c"

The first test is the one that catches the failure this design exists to prevent, and it should be written before the flattener.

In production, alert on two things: the quarantine rate for depth-limit violations, and any entry appearing in the drift report for a critical field. Both indicate that the upstream shape has moved.

Compliance & Operational Guardrails #

  • Child rows inherit the parent’s lineage columns as part of the flatten contract.
  • Personal-data paths are tagged before flattening so the tag stays attached to the right column.
  • Payloads exceeding the depth limit are quarantined with the offending path recorded.
  • Unknown keys are captured, never silently discarded.
  • Reserved lineage column names cannot be overwritten by upstream data.

Common Mistakes #

  1. Cross-joining sibling arrays. Row counts multiply and the growth is invisible until the storage bill arrives.
  2. Dropping array order. “First image is primary” is information the source encoded and the flatten discarded.
  3. Unbounded recursion. A recursive payload takes the worker down instead of being quarantined.

Frequently Asked Questions #

Should child tables be separate tables or a JSON column? #

Separate tables when the children are queried, filtered or joined; a JSON column when they are only ever read back with the parent. The separate-table form costs more to write and far less to query, and the choice is easier to make early than to change later. Either way the child rows need lineage columns.

How deep should the depth limit be? #

Eight covers essentially every real payload while still catching runaway recursion. Set it low enough that a genuinely pathological document is quarantined rather than processed, and record the offending path so a legitimate deep structure can be accommodated deliberately rather than by raising the limit globally.

What about arrays of arrays? #

Rare, and worth quarantining rather than guessing. The correct flattening depends entirely on what the nesting means — a matrix, a grouped list, a list of coordinate pairs — and a general rule will be wrong for most of them. Quarantine, look at a sample, and add an explicit handler for the shape if it turns out to be common on that source.

How should child tables be named? #

From the path that produced them, not from the entity they appear to describe. A child table called images is ambiguous when two different parents both have an images array with different shapes; one called product.images is not. Path-derived names also make the schema self-documenting, since the column list plus the table name reconstruct the original payload structure without needing a separate diagram.

What should happen when a parent has no children at all? #

Write the parent and no child rows — never a child row of nulls. A placeholder row is indistinguishable from a genuine child whose fields happened to be empty, and downstream counts become wrong in a way nobody can unpick later. Where the absence itself is meaningful, record it as a count column on the parent rather than as a phantom row.

Where should the flattener sit relative to schema validation? #

After validation of the payload’s overall shape and before validation of the individual rows. Validating the nested payload catches a response that is not what you expected at all; validating the flattened rows catches a field whose type or range is wrong. Running only one of the two leaves a gap: a structurally valid payload can still flatten into rows with impossible values, and a structurally broken payload produces rows that are individually plausible.