A new key in an event payload should cost nothing on the day it ships. It should start costing something on the day a dashboard depends on it, and not before.
Most arguments I hear about semi-structured data skip that distinction. One side wants every event field declared up front, with a migration per field. The other side lands everything in a JSON column and types values inside whichever query happens to need them. I think both are wrong. Keeping the raw payload and typing some keys go together, and every team that runs event data for long enough ends up doing both. So the question I’d spend time on is narrower: which keys get promoted to real columns, and what triggers it.
Why I keep the payload forever
The storage engines have made this cheap. Postgres has had JSONB since 9.4. Warehouses have semi-structured column types, Snowflake’s VARIANT being the one I’ll use in the SQL below. Newer work goes further: ClickHouse shipped a reworked JSON type this summer that stores paths as subcolumns, and Delta Lake’s 4.0 preview has a variant type. Both are still experimental or preview, so I wouldn’t plan around them yet. They do point the same way, though: engines want you to keep the document.
I want to keep it too, for a plain reason. The raw payload is the only copy of what the producer sent. If I type duration as a number and throw away the original, I can never find out that it arrived as text for three weeks. Typed columns are an interpretation. The payload is the evidence.
What the payload is bad at is being queried. A key read out of a document has no declared type and no test, and the engine often can’t prune on it. Every analyst writes their own cast, and two of them will disagree.
The rule, in two lines
Promote a key when it appears in a WHERE, JOIN or GROUP BY in at least 10 queries over 30 days,
or when it is present in 95 percent of events and feeds a metric.
The first line is about cost and correctness under load: a key people filter and join on is a column in everything but name, and it’s being re-cast in every query. The second line catches the quiet ones, like a plan tier that only one revenue metric reads, where a wrong type is expensive even if the query count is low.
Ten queries and 95 percent are where I’d start; a team with a noisy query log should raise the first one. What I’d keep is that promotion is triggered by measured use and not by a guess at design time. Anything that doesn’t meet the rule stays in the payload, and nobody needs a ticket to add a key there.
Profiling what’s in there
Two inputs feed the rule. Usage comes from the warehouse’s query history: search the query text for the payload paths and count the queries per key. It’s a crude text match, and it’s good enough to rank keys.
The other input is what the keys look like. This query gives frequency and type drift per key over 30 days:
with recent as (
select event_id, properties
from raw.app_events
where received_at >= dateadd(day, -30, current_timestamp())
),
total as (
select count(*) as n from recent
)
select
f.key as key_name,
count(*) as events_with_key,
round(100 * count(*) / max(t.n), 2) as pct_of_events,
count(distinct typeof(f.value)) as distinct_types,
listagg(distinct typeof(f.value), ', ')
within group (order by typeof(f.value)) as types_seen
from recent r
cross join total t,
lateral flatten(input => r.properties) f
group by f.key
order by events_with_key desc;
Any key with distinct_types above one deserves a look before it gets promoted. An INTEGER and DECIMAL mix is usually harmless. VARCHAR next to a number means a producer disagrees with itself.
Casting without losing rows
Promotion happens in the staging model. The cast has to be safe, meaning a bad value becomes a null plus a flag and the row survives. A hard cast that fails the whole load at 2 a.m. teaches people to stop typing things.
-- staging.stg_app_events
with src as (
select
event_id,
received_at,
properties,
properties:user_id::varchar as user_id_raw,
properties:duration_s::varchar as duration_raw
from raw.app_events
)
select
event_id,
received_at,
user_id_raw as user_id,
try_to_double(duration_raw) as duration_s,
(duration_raw is not null
and try_to_double(duration_raw) is null) as duration_rejected,
properties
from src;
-- the test: how many values did the cast refuse yesterday?
select
count(*) as rows_checked,
count_if(duration_rejected) as rejected,
round(100 * count_if(duration_rejected)
/ nullif(count(*), 0), 2) as pct_rejected
from staging.stg_app_events
where received_at >= dateadd(day, -1, current_timestamp());
Casting to text first matters in this dialect: it turns a JSON null into a SQL null, so a missing value and a refused value stay distinguishable. I pass the payload through whole and don’t strip the promoted keys out of it. Keys that weren’t promoted stay one path away, and the typed columns can always be rebuilt from the document sitting next to them.
Fail the build when pct_rejected crosses a threshold you choose per column, and warn below it. If you use dbt, put a model contract on the staging model as well, so the promoted column’s type is enforced at build time and can’t drift because someone edited a cast.
Eleven keys out of 212
To put numbers on it, I made up a workout app called Vireo Fitness. Its events carry a properties payload with 212 distinct keys. The team runs the usage count over the query log and finds that 11 of those keys account for 94 percent of all key reads in filters and joins: user, session, platform, app version, workout type, duration, and a few like them.
They promote the 11 into a staging model built as a table. The daily active users query, which had been digging user_id and platform out of the document on every run, goes from 96 seconds to 7, and its scan drops from 410 GB to 22 GB. That’s the result everyone expected.
Nobody expected what the reject count showed. On the first run, 1.6 percent of rows fail the cast on duration_s. Tracing them by app version shows one Android release that sent duration as a formatted string, something like “41:07”, for three weeks before a later release fixed it. Every average-workout-length chart for those weeks had either dropped those rows without a word or errored until someone wrapped the cast in a try, depending on who wrote the query. The payload still holds the strings, so the fix is a parsing branch in staging and a backfill.
The other 201 keys stay in the payload, where they were already fine.
When to demote
Promotion shouldn’t be permanent. I’d rerun the usage count every quarter and look for promoted columns nobody has read in 90 days. Dropping one is cheap precisely because the payload is still there. If the key gets popular again, it passes the rule again and comes back with its type and its test.