Hey everyone! So many CDPs handle JSON events differently, and nested properties are where things get tricky. I'm helping a B2B SaaS team migrate their user events from one platform to another, and their old schema has deeply nested custom attributes.
The main challenge? Their old CDP flattens everything (e.g., `product.details.sku` becomes `product_details_sku`), but the new one supports nested objects natively. I want to preserve that structure during the migration, especially for historical data backfill.
How have you all approached transforming flattened keys back into proper nested JSON? Did you run into data type issues or null values? Any tools or scripts you'd recommend for the mapping step?
Would love your war stories and tips! xo
Happy customers, happy life.
Oh, this is such a common pain point when moving between systems with different data philosophies. Your example of `product_details_sku` is spot on. I've seen teams write a custom parser that uses the delimiter, often an underscore, to recursively rebuild the nested object. The tricky part is always deciding how to handle keys that *might* have the delimiter in the original value, which can create real ambiguity.
On the data type front, yes, absolutely. When everything is a flattened string, you lose integer/boolean/null context. We ended up maintaining a separate schema definition to cast types during the rebuild process, but it required a manual audit pass first. It was a bit tedious, but it prevented nasty surprises later.
For tools, a lot of folks here have had good results with JQ for scripting the transformations, especially for backfills. It's powerful for this exact pattern-matching task. But your mileage may vary depending on the data volume. Has your team estimated the scale of the historical data? That might push you toward a more batch-oriented approach.
Let's keep it real.
Great point about the delimiter in the original values, that's a real gotcha. I've seen teams try using a multi-character delimiter or even a special sequence that gets escaped, but it adds complexity. Sometimes a simpler route is to first export a sample of the *original* nested data from the old system, if you still have API access, to see how it handled those edge cases itself.
Your nod toward data volume is crucial, too. For a large backfill, JQ in a shell loop can choke. Pushing the logic into the transformation step of your batch job, or using a stream processor if it's a live migration, tends to hold up better. The manual audit for types is tedious, but I agree it's non-negotiable for data integrity.
Keep it constructive.
Totally agree that getting a sample of the original nested structure is the best first move if you can. I had to do something similar moving Jira issue data once, where the original custom field structure was key.
Your point about stream processing for volume is spot on. We tried a Python script with a simple recursive function first, but it fell over after a few thousand events. Pushing the rebuild logic into a dedicated transform job in the pipeline was the only way it scaled. The manual type mapping still hurt, though.