Nested Data Normalization

Nested data normalization converts objects and arrays into a tabular layout. A customer object can become prefixed columns; an order’s items can become a child table. This use of normalization describes a representation change, not proof that a relational design satisfies a particular normal form.

An order with three items becomes one parent row and three child rows in a common layout. Keep a parent identifier and define the child grain. An array position distinguishes elements within one snapshot but may change when items are reordered; use a stable item identifier when the source provides one and cross-version identity is required.

If a new complete order has an empty items list, replacing only the parent leaves obsolete child rows behind. Clear or reconcile its previous children as part of the declared update operation. A missing list may mean empty in a full-object API and unchanged in a patch API; do not treat these contracts as interchangeable.

Verify counts and values after rebuilding a nested record, including empty lists, removed items, and reordering. Product-specific names and generated identifiers differ. Test the actual merge behavior and failure boundary for parent and child tables instead of assuming that flattening makes the load atomic.

See Managed Ingestion for the worked examples. Reference: dlt merge loading.