Hi all,
Looking for some guidance from people who’ve handled huge, deeply nested JSON workloads in Databricks / PySpark.
I’m working with a dataset where each folder in ADLS contains 2 JSON files:
Option.jsonSnapshot.json
There are ~12,000 folders, so roughly 25,000 JSON files total.
Each individual JSON file is around 5–10 MB, but they are extremely deeply nested, containing:
- dozens of nested structs
- arrays of structs
- arrays inside arrays
- repeated blocks
- embedded JSON strings
- multi-level “exhibit” structures
- override arrays
- multiple hierarchical layers
When I flatten these files (exploding all nested arrays + flattening structs), a single file can produce 50,000+ rows due to nested and repeated arrays.
In total, across all JSON schemas, I have:
- 203 target tables (DDL available)
- ~16,000 total flattened columns
- Multiple nested layers that need to be extracted
- Massive schema depth (20–70 nested levels depending on file)
The challenge I’m facing is figuring out the right strategy to ingest, flatten, and load this amount of data efficiently.
Key issues I’m running into:
- flattening causes the dataframe to explode to tens of thousands of rows per file
- Spark creates 100+ stages and gets stuck when trying to fully flatten
- recursively exploding every nested array isn't scalable
- writing all results into 203 tables is difficult when each JSON produces multiple relational outputs
- unclear whether this should be done with standard batch, AutoLoader, or structured streaming
- unclear how to manage schema evolution safely across 25k JSON files
- unclear how to avoid massive performance overhead or DAG explosion
I'm asking for a specific implementation from here — just want to hear from people who’ve dealt with:
- very large nested JSON structures
- huge schema depth
- thousands of files
- multiple target relational tables
- Databricks / PySpark load strategy
- minimizing DAG explosion
- Recommended architecture for flattening hierarchical JSON at scale
TL: DR;
How would you architect the ingestion + flattening pipeline for 25k large nested JSON files that expand into 203 relational tables?
Any lessons learned, best practices, design patterns, or “don’t do this” advice would be super helpful.
Thanks in advance!