The Nesting Decision
Concepts covered: Denormalization
When to Nest vs When to Normalize
The fundamental question: should related data live inside the parent row (nested) or in a separate table (normalized)? Both are valid. The choice depends on how the data is queried, how often it changes, and whether the nested data needs to be joined to independently.
Nesting works well when the child data is always read alongside the parent. A user's shipping addresses are almost always retrieved when loading the user record. Normalizing this into a separate addresses table forces a JOIN on every user load. Nesting the addresses as an ARRAY<STRUCT> inside the user row eliminates that JOIN.
- Nest: Best when child data is always read with the parent, rarely queried independently, and has bounded cardinality (a user has 1-5 addresses, not 10,000).
- Normalize: Best when child data is queried independently, has high cardinality, changes frequently, or is shared across multiple parent tables.
- Hybrid: Store normalized in the source system, nest during ETL into the analytical model. Common pattern for denormalized read-optimized tables.
Decision Framework
- Nest (Embed)
- Always read with parent
- Low cardinality (1-10 items)
- Rarely queried independently
- Preserves source JSON structure
- Eliminates JOINs for common queries
- Normalize (Separate Table)
- Queried independently of parent
- High cardinality (100+ items)
- Shared across multiple tables
- Changes independently of parent
- Needs FK constraints and indexing
A common mistake: nesting everything because the source data is JSON. Just because the API returns nested JSON does not mean your analytical table should be nested. If you frequently filter or JOIN on the nested fields, normalize them.
About this lesson
This section is part of the Nested Data lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.