Nested and repeated modeling
When should child rows be embedded in the parent, and what does nesting really cost in a columnar format?
Nested (or repeated) modeling puts child rows inside the parent row as an array or a struct. The decision is embed versus reference. The rule has four clauses, and each clause maps to a specific failure.
Embed when the child has:
- No independent identity: you never query children across parents.
- No independent lifecycle: it changes only when the parent changes.
- Always read with the parent.
- Bounded size.
| Violated clause | What goes wrong |
|---|---|
| 1. Independent identity | Every cross-parent query becomes a full scan plus unnest. (The support analyst's "all orders with stove 77".) |
| 2. Independent lifecycle | A child update rewrites the whole parent row: the granularity-mismatch law again. |
| 4. Bounded size | A single row grows without limit, breaking the row-group and page-size assumptions of the storage format (files are written in groups of rows of a planned size). |
-- Order with its items embedded
CREATE TABLE orders (
order_id BIGINT,
order_ts TIMESTAMP,
items ARRAY<STRUCT<product_id INT, qty INT, price DECIMAL(10,2)>>
);
-- Read the children back as rows
SELECT o.order_id, i.product_id, i.qty
FROM orders o, UNNEST(o.items) AS i;Two corrections to common belief
Nesting does not hurt analytical reads the way the folklore says. Columnar formats such as Parquet encode repeated fields with repetition and definition levels (small numbers stored beside the values that say where a new array starts and which optional levels are missing). So an array column is readable, filterable and aggregatable without materializing the parent. The surviving costs are write amplification (rewriting parents) and cross-parent access. Those are write-side and access-pattern costs, not scan costs.
Nesting pre-joins without losing grain, which distinguishes it sharply from flattening. A flat row at parent grain that has the item names concatenated into one text value has destroyed the child grain. An array column preserves it, and UNNEST recovers it. So nested modeling is strictly more expressive than flattened denormalization at the same width. The cost is that the consumer must know to unnest. That is a human-interface cost, not an information-theoretic one.