The allowlist middle ground is fine in theory, but in practice, it just kicks the can. Now your data team owns the eternal backlog of reviewing and adding new "allowed" keys every time product ships something.
And that ballooning warehouse cost? It still happens. You're just moving the flattening job from one line item to another. The queries are still slow, the dashboards still time out. You haven't solved cardinality, you've just outsourced it to a different budget.
CRM is a necessary evil
That raw S3 stream backup is crucial. I've seen teams get burned by being too aggressive with the filter, then needing that one oddball field for a compliance audit six months later. You can't rebuild it.
But be careful with that metadata_json column. Even as a string, if the CDP is trying to index or search within it, you can still hit performance issues on massive datasets. It's better than high-cardinality columns, but it's not a free pass.
Are you compressing those S3 objects, or just dumping raw JSON? The storage cost can sneak up on you.
You raise a valid concern about inconsistent reporting. In our case, the pushback was mitigated by making the raw S3 stream a deliberate and documented "cold storage" tier, not a parallel active data source. We created a separate BI environment specifically for queries needing that raw data, with clear labeling and a mandatory step requiring justification for its use. This made it a controlled exception rather than an alternative.
The real issue wasn't the existence of two sources, but governance. We found that without this enforced friction, teams would naturally gravitate to the faster, cleaner CDP for most queries and only venture into the raw data for specific investigations. The inconsistency risk arises when the same business question is answered using different sources without a clear rationale. A strict policy defining the "when and why" for each stream is essential.
Your ERP example of `custom_field_203` is perfect. That normalization is low-hanging fruit, but you must also establish a process for when a new pattern emerges. Otherwise, you're back to square one.
Your suspicion about `user_properties` is almost certainly correct. The CDP's attempt to map each unique key to a discrete column is a known failure mode for semi-structured data. I've seen this exact pattern with `clicked_element` keys containing unique IDs inflating cardinality by orders of magnitude.
While a lambda pre-processor is the immediate tactical fix, I'd strongly advise against just filtering keys. You need to analyze a sample of those 20+ dynamic keys for patterns. Use a simple profiling query on your raw data to see if, for example, 80% of the keys are variants of `clicked_element_*` or `utm_*`. That will tell you if you can apply a regex normalization, like stripping the suffix from `button_xyz_847`, which can dramatically reduce cardinality without losing meaningful semantics.
The strategic question is whether your new CDP supports a proper JSON column type. If it does, lobbying to store the entire object as a queryable JSON string is a more sustainable path, though it shifts the parsing complexity downstream. If it doesn't, the lambda with key normalization is your only real short-term lever.
Garbage in, garbage out.