Columnar Storage & Nesting
Concepts covered: Columnar Storage
How Nested Data Interacts with Columnar Engines
Modern analytical databases (BigQuery, Snowflake, Databricks) use columnar storage: data is stored column-by-column, not row-by-row. A query that reads only 3 columns out of 50 only scans those 3 columns. This is why analytical queries are fast. But nested data adds complexity.
STRUCT sub-fields are stored as individual columns in columnar format. address.city is stored alongside address.state and address.zip as separate column chunks. Accessing one sub-field does not read the others. This is efficient.
ARRAY elements are stored using repetition and definition levels (Parquet's Dremel encoding). This is more complex than flat columns. Deeply nested arrays (arrays of structs of arrays) create encoding overhead that degrades scan performance.
Performance Implications
- STRUCT: sub-fields are independent columns. Same performance as flat columns for individual field access.
- ARRAY with few elements: minimal overhead. A tags array with 3-5 elements is fine.
- ARRAY with many elements: encoding overhead grows. A 10,000-element array per row defeats the purpose of columnar storage.
- Deeply nested structures (ARRAY<STRUCT<ARRAY<...>>>): each nesting level adds encoding complexity. Flatten if possible.
- MAP/VARIANT: stored as semi-structured blobs. Cannot use columnar encoding, predicate pushdown, or min/max statistics.
Best Practices
- Use STRUCTs freely for logical grouping. They have zero performance cost in columnar engines.
- Keep arrays small (under ~100 elements). Large arrays should be normalized into separate tables.
- Extract frequently-filtered fields from MAPs into typed columns for predicate pushdown.
- Limit nesting depth to 2-3 levels. Deeper nesting degrades both query performance and developer comprehension.
The general rule: columnar engines are optimized for flat, typed columns. Nesting is a convenience feature that trades some query performance for schema flexibility. Use it where it makes the data model cleaner, but do not over-nest. When in doubt, flatten.
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.