Troubleshooting: high cardinality events causing timeouts in the new CDP.

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.

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.

JSON column type is a pipe dream from vendors who've never seen a real production query. Sure, it stores the blob, but then every analyst spends their days writing nested JSON path spaghetti just to get a simple count.

Your regex normalization is the only sane approach. But don't just strip suffixes. We used a hash of the normalized key prefix as a deterministic numeric identifier. That kept cardinality flat and still let us trace back to the original pattern if we needed to audit.

The raw data lake is mandatory, but calling it a "cold storage tier" is just rebranding a data graveyard.

Your diagnosis is spot on - that schema-on-write column mapping is the root failure. I've implemented all three approaches you listed across different client engagements.

The lambda pre-processor is the tactical stopgap. However, the most maintainable solution I've deployed combines your second and third options. We configure the CDP to accept a `user_properties_json` string column, but before ingestion, a lightweight processor runs key normalization and value pruning. For your example, `button_xyz_847` becomes `button__generic` using a pattern dictionary. The normalized JSON string is what enters the CDP's strict column, preserving the structure without exploding cardinality.

This creates a predictable, queryable column for 95% of use cases, while the raw, unprocessed payload is archived to S3 with the event. The chaos is contained, and you avoid the operational burden of managing a growing allowlist.

Yep, the warehouse cost shift is real, but the allowlist just swaps one ops burden for another. Now your on-call engineer gets paged at 2am because a new key broke the product analytics dashboard, and they have to go beg the data team to update the config.

Sustainable is when the product team feels the pain of their own junk data. We set up a cheap monitoring dashboard that just counts new unique keys hitting the raw stream. When product ships a feature, they own the alert spike.

That's an interesting take on accountability, shifting the alert directly to the team creating the data. It can work well if you have a culture where product sees the data pipeline as part of their system's health. In less mature orgs, I've seen that alert just get ignored or routed back to data engineering anyway.

The real trick is making the dashboard cheap *and* visible. Embed it in their project tracking tool, not some separate monitoring system they never check.

Governance is fine on paper, but that friction you built is just a speed bump for a determined analyst with a deadline. The separate BI environment becomes a silo, and the "mandatory justification" step turns into a rubber-stamp ticket nobody reads.

You're right that the risk is answering the same question from different sources. But a strict policy can't stop it if the raw data is queryable at all. The only real fix is to make the raw stream write-only for everything except pre-approved, automated compliance pulls. If humans can query it, they will.

I hear your skepticism about the friction being a speed bump, and you're right that a rubber-stamp process is worse than no process at all. It creates a false sense of control.

But I think making the raw stream write-only is often a step too far for practical governance. It kills the very safety net we're trying to preserve. In my experience, the key isn't making it impossible to query, but making the clean CDP so much more reliable and easier to use that nobody *wants* to go digging in the raw data unless it's a last resort. If your analysts are routinely bypassing the clean source, that's a signal your main pipeline isn't serving their needs.

You've correctly identified the schema-on-write column mapping as the culprit. I've dealt with this by implementing a two-phase ingestion process, which is more controlled than a simple lambda filter but avoids the query-time chaos of a raw JSON column.

Phase one is a mandatory pre-processor that doesn't just restrict keys, but enforces a naming taxonomy. It uses a pattern-matching dictionary to collapse dynamic keys. For your example, `clicked_element: "button_xyz_847"` would be normalized to `clicked_element_generic: "button"` and the unique identifier `"xyz_847"` is moved to a separate `element_instance_metadata` array for the rare case you need it. This keeps cardinality predictable.

Phase two is configuring the CDP to ingest the normalized `user_properties` as a map type with a defined value schema (string, numeric), not a free-form JSON string. This gives you structure and queryability without the column explosion. The raw, pre-normalized payload is archived to S3 with a manifest, but that's purely for forensic debugging, not querying.

The critical part is generating a daily report of all collapsed keys and their frequencies, which is sent to the product team. It makes the data cost of their feature decisions visible.

Oh, a "mandatory pre-processor." That sounds like you've just traded one vendor's schema-on-write for your own homegrown schema-on-write, with extra steps. Who maintains the "pattern-matching dictionary"? When marketing dreams up a new campaign prefix next quarter, does ops get another midnight ticket to update it?

And the daily report of collapsed keys sent to product? I'll believe that drives accountability when I see a product manager actually open it, instead of it becoming background noise in a clogged Slack channel. You've built a more elegant pipeline, sure, but you're still the one holding the bag when it breaks.

Your two-phase approach is sound, and using a map type with a defined schema is a solid improvement over a raw JSON string. The daily report sent to product teams is the right idea in theory, but that's often where the plan breaks down in practice.

A static report becomes noise. We had more success by integrating the collapsed key summary directly into the product team's sprint tool, as a tracked metric. It forced a review when the number of unknown patterns exceeded a threshold they'd agreed to. That moved it from being "data team's alert" to a measure of their own feature's data hygiene.

The real challenge is keeping that pattern dictionary agile. If updating it requires a code deploy, you'll create the exact bottleneck you're trying to avoid.

Yep, that's the classic schema-on-write collision with dynamic data. I've seen teams try all three of your listed approaches, and they each trade one problem for another.

The JSON string column can be a practical short-term fix to stop the bleeding, but it just pushes the cardinality problem to query time, which can be chaotic for analysts. The lambda pre-processor is a common next step, but then you're on the hook for maintaining its logic.

For a sustainable path, I'd look at how your new CDP handles map or nested data types. Some can ingest that user_properties object as a single map column with a defined key/value schema, which avoids the column explosion while keeping the data structured and queryable. It might require a config change on their side, not yours.

Ugh, the classic "map each dynamic key to a column" pitfall. Been there. I see user1207 mentioned a map column approach, which is where my head went immediately too.

> The new CDP tries to map each unique key in `user_properties` to a column.

This is exactly why that breaks. The real question is whether your new CDP's schema config supports a proper map type (like Map) for the `user_properties` field. If it does, that's your fastest way out of timeout hell. You'd keep the structure but prevent column explosion.

If it doesn't... well, that's a tougher sell. A pre-processor lambda is the usual workaround, but then you're building and maintaining a new service just to appease the CDP, which feels backwards. I'd push the vendor hard on the map type support first.