When should a wide analytics table nest child records in an array column instead of one row per child?

## Two ways to carry a parent-child relationship in one table A wide mart that must hold orders and their line items has two shapes available. **Nested** — one row per order, with the children in a repeated column of structured values: ```text order_id | order_ts | order_total | items 1001 | 2026-07-02 | 120.00 | [ {sku:'A', qty:2, line_amt:80.00}, {sku:'B', qty:1, line_amt:40.00} ] ``` **Flattened** — one row per order line, with the parent's columns repeated: ```text order_id | order_ts | order_total | sku | qty | line_amt 1001 | 2026-07-02 | 120.00 | A | 2 | 80.00 1001 | 2026-07-02 | 120.00 | B | 1 | 40.00 ``` Both hold the same information. They differ in what one row means, and therefore in what a careless aggregate returns. ## Nesting preserves the grain In the nested shape the grain is one row per order. `SUM(order_total)` is 120.00 and cannot be anything else; `COUNT(*)` is the number of orders. The child data rides along, addressable when a consumer asks for it, invisible when they do not. Nobody can accidentally fan the parent out, because there is only ever one row per parent. This is the strongest argument for nesting in a one-big-table design: it lets a single table serve the parent grain honestly while still carrying detail, which is exactly what a flat wide table cannot do. ## Flattening changes the grain and endangers parent measures In the flattened shape the grain is one row per line. `order_total` is now a **parent-grain measure sitting at child grain**, and summing it double-counts — the classic wide-table defect. The measures that are safe at this grain are the line-level ones (`line_amt`, `qty`); the parent's totals must either be dropped, or recomputed from the line measures, or guarded so that they are counted once per parent. If you find yourself writing tricks to count `order_total` once, that is the model telling you the grain is wrong for that measure. Flattening is nevertheless the right answer often: line-level analysis (product mix, discount by SKU, basket composition) is a real business process with its own dimensions and its own audience. The clean expression of that is a second fact table at line grain — the two grains are two facts, not one table forced to be both. ## When to nest - Children are **always** consumed with the parent and rarely analysed on their own. - The child collection is **bounded** — a handful of items, not an unbounded event stream that grows forever inside one row. Unbounded arrays turn a row into an ever-growing object and make rewrites expensive. - The parent grain is what consumers overwhelmingly query, and you want it protected from accidental fan-out. - The children have no dimensions of their own that consumers need to filter on across all parents. ## When to flatten or split - Child-level measures are the point of the table. - Consumers filter or group by child attributes across the whole dataset ("revenue by SKU"), which is clumsy through a nested column and natural at child grain. - The consuming tools cannot express nested access — many BI layers and SQL-light users handle flat columns only. - The child cardinality is unbounded or highly skewed. ## What nesting costs - **Consumer friction.** Reading inside an array requires unnesting, which is exactly the join-shaped complexity an OBT was meant to remove. You have not eliminated the join so much as renamed it — and the payoff is that the un-unnested reader is safe by default. - **Tooling support varies.** Not every downstream consumer models repeated structured columns well; some flatten them for you, badly. - **Evolution is harder.** Adding a field inside a struct is a schema change to a nested type, generally more awkward than adding a top-level column, and older rows carry the old shape. - **Testing is harder.** Grain tests on a flat table are a `GROUP BY … HAVING COUNT(*) > 1`; testing that every array is well-formed and non-duplicated takes more work. ## A rule of thumb Decide the grain of the table first, from the questions it must answer. If that grain is the parent, nesting is how you keep detail without betraying it. If the questions are genuinely about the children, do not nest and do not flatten a parent-grain table either — publish the child grain as its own fact, and let a consumer who needs both join or query them separately. The failure to avoid is the middle: a flattened table that still advertises parent-grain measures as if they were additive.