Stage 1 · Theory & Documentation

Records and formats· 3 of 3

Shapes that nest

JSON carries types and nests, which solves the quoting problem and creates the flattening problem. Deciding how a document becomes a row is a design decision, not a mechanical one.

25 minute read

⚡

For the best experience: Download the native Atlas Desktop app for instant test running, native IDE sync, and 100% offline practice.

Download Desktop App→

Flat files treat every column as a scalar, often serialized as text. API payloads frequently deliver structured, hierarchical documents:

{
  "order_id": 1003,
  "customer": {"email": "lin@example.com", "country": "SG"},
  "items": [
    {"sku": "A-1", "quantity": 2},
    {"sku": "B-7", "quantity": 1}
  ],
  "total": 1499.00
}

This format provides two distinct advantages over CSV. Numeric values such as 1499.00 retain native numeric semantics without delimiter ambiguity. Sub-objects such as customer and nested collections such as items maintain explicit boundaries.

Converting hierarchical documents into tabular records requires specific structural decisions.

Flattening is a structural decision

Transforming nested structures into relational rows requires selecting an appropriate representation strategy:

Dotted key flattening: Nested dictionary keys become flat column names, producing columns such as customer.email and customer.country. This strategy handles single nested objects, but fails on array fields like items.
Record explosion: Unnesting arrays into multiple rows emits one record per array element. For order 1003, this produces two rows, one for A-1 and one for B-7. While item details remain distinct, root-level attributes like total are duplicated across rows. An unweighted sum across total yields 2998 instead of 1499.
Serialized storage: Storing the array as raw JSON text within a single column retains root-level cardinality, but obscures nested attributes from relational query engines.

The standard relational pattern splits this data across two related entities: an orders parent table and an order_items child table linked by foreign key. When flattening occurs earlier in a pipeline, data engineers must track aggregation levels to prevent double-counting.

Type preservation and edge cases in JSON

JSON supports strings, numbers, booleans, arrays, objects, and null. While this reduces string-parsing ambiguities, type handling still presents edge cases:

Missing keys versus null values: A missing key indicates the attribute was omitted at the source, while an explicit null indicates the value was known to be empty. Flattening transformations often collapse this distinction into a single null representation.
Floating-point precision: JSON numbers do not enforce fixed decimal precision. Monetary quantities deserialized into floating-point variables can introduce fractional rounding errors. Financial applications store monetary amounts as string representations of exact decimals or as integers in minor currency units (such as cents).

What the exercise asks

Implement three operations:

Flatten nested dictionaries using dotted key paths.
Explode nested list fields into distinct rows while preserving parent identifiers.
Construct a validation check that identifies inflation when summing parent attributes across exploded rows.

Detecting grain mismatches early prevents silent aggregation bugs in downstream transformations.

Records and formats

Shapes that nest

JSON carries types and nests, which solves the quoting problem and creates the flattening problem. Deciding how a document becomes a row is a design decision, not a mechanical one.

25 minute read

⚡

For the best experience: Download the native Atlas Desktop app for instant test running, native IDE sync, and 100% offline practice.

Download Desktop App→

Coding Objective

Implement your solution in the editor on the right or work in your local IDE. All unit tests run against your code in real-time.

Language: Python 3.10+Runner: Local / Pyodide

Exercise

flatten-records

25 tests, graded on your machine in about a second. Stuck for 25 minutes? The desktop app will offer you the reference solution.

solution.py

"""
Shapes that nest.

Turning a document into rows is a design decision, and the wrong one is not
detectable later. These four functions are the decision, written down.

Run `python3 test.py` when you want to see how you are doing.
"""

from __future__ import annotations

def flatten(document: dict, separator: str = ".") -> dict:
    """
    Flatten nested dicts into one level, joining keys with a separator.

Args:
        document: a decoded JSON object. Values may be dicts, lists, or scalars.
        separator: what joins a parent key to a child key.

Returns:
        A flat dict. A nested dict becomes several keys:

{"customer": {"email": "a@b.c"}} -> {"customer.email": "a@b.c"}

A list is left alone, as a list, under its own key. Flattening a list
        into `items.0.sku`, `items.1.sku` is possible and is almost always
        wrong: the column names then depend on how many items an order happened
        to have, so two orders produce two different schemas. `explode` below is
        the honest way to handle a list.

An empty dict as a value disappears, because it contributes no leaves.
        That is worth knowing rather than discovering: a document whose customer
        object is `{}` flattens to a row with no customer keys at all.

Examples:
        >>> flatten({"a": 1, "b": {"c": 2}})
        {'a': 1, 'b.c': 2}
    """
    raise NotImplementedError

def explode(document: dict, list_field: str) -> list[dict]:
    """
    One row per element of a list field, each carrying the parent's other fields.

Args:
        document: a decoded JSON object.
        list_field: the key holding a list.

Returns:
        One dict per element. Each is the parent's fields minus `list_field`,
        with the element's fields merged in and flattened.

A key collision resolves in favour of the element, since the element is
        the more specific claim.

An empty list gives [], not one row of parent fields. An order with no
        items produced no item rows, and inventing one would put a row in the
        table for something that does not exist.

A missing `list_field` also gives [], for the same reason.

Examples:
        >>> explode({"id": 1, "items": [{"sku": "A"}, {"sku": "B"}]}, "items")
        [{'id': 1, 'sku': 'A'}, {'id': 1, 'sku': 'B'}]
    """
    raise NotImplementedError

def double_counted(rows: list[dict], key_field: str, value_field: str) -> dict:
    """
    Find values that would be counted more than once by a naive sum.

This is the check that catches the classic explode bug. After exploding an
    order into one row per item, the order's total appears on every one of those
    rows, so summing it gives a number that is too big and looks plausible.

Args:
        rows: exploded rows.
        key_field: the parent's identity, for example "order_id".
        value_field: a parent-level value that got copied onto each row.

Returns:
        A dict shaped:

{"keys": ["1003"], "naive_sum": 2998.0, "true_sum": 1499.0}

`keys` holds every key that appears on more than one row, sorted, as
        strings. `naive_sum` is what summing the column gives. `true_sum` counts
        each key's value once.

Rows whose key is absent/None or whose value is missing or not a number
        are skipped for both sums rather than treated as zero. A value nobody can
        read is not evidence that the value was nothing.

Returns zeroes and an empty key list for empty input.
    """
    raise NotImplementedError

def schema_of(rows: list[dict]) -> dict:
    """
    What fields these rows have, and how consistently.

Args:
        rows: any list of flat dicts.

Returns:
        A dict mapping field name to the number of rows whose mapping contains
        that field, including fields whose value is None. Sorted is not required;
        equality on dicts ignores order.

This is the cheapest useful thing you can say about an unfamiliar
        extract, and it is how you notice that a field you assumed was always
        there is present on 9,998 rows out of 10,000.

Empty rows give an empty dict.
    """
    raise NotImplementedError

Or use the desktop app

Flatten this order for a table. The nested customer becomes columns; the items list stays where it is.

Flattening is a structural decision

Type preservation and edge cases in JSON

What the exercise asks

flatten-records