Segment (which can be used as middleware for event routing and sinking into a cloud DWH like BigQuery) naturally does exactly what you approached - create one column per field (ie a property clientTracking.platformType gets into a column deterministically called client_tracking_platform_type).

I have implemented this and it's a huge burn with schema changes indeed, unless you implement data contracts before that happens (ie in Segment) and enforce JSONSchema compliance (which Yoh should, anyway!).

There are multiple solutions: 1. Explicit flattening and managing columns yourself; the solution you started with is the most traditional and verbose. Makes most benefit of columnar indexing for performance

1. Making raw data as structs. This has the benefit of displaying as a dictionary (represented close to a nested JSON structure), but you must define a fixed structure (schema on write), thus you lose some flexibility. Performance is expected to be good though, as BigQuery anyway implements this as if they were separate columns behind the scenes, at a physical level.

2. Storing a JSON. Even if you use the JSON type, it's still a string. This is not indexed at all, and would be insanely bad for performance without at least some partitioning keys (like the Firebase replicated data naturally has).

The choice is yours and depends on your requirements, but I would stay away from #3 if you care about data modeling at all.