Hi,
When trying to add one context as enrichment to an event, the mutator creates a new REPEATED column. Is there a way to restrict this to become a NON REPEATED column. NULLABLE is what I’m looking for.
Column:
What I want (something like this):
I’m planning to add a property to the atomic schema file, would that break anything in the pipeline?
Thank you!
Hi @siv, there is no way to do that, no.
Contexts can always contain multiple entries, so their data type reflects that. The business logic can determine that you’ll only ever have one, but the data type is still such that it must be compatible with many.
There is an idea here that we could have a different type of context which can only be attached once, but that’s not something that exists at the moment.
If your context only ever has one entry, however, it’s quite easy to deal with that at modeling stage. You can just query the first item in that field - contexts_com_mcd_japan_utc_to_jst_1_0_0[offset(0)] gets you the first element.
You can also use an UNNEST if you prefer. (I’ve assumed BQ here since that’s what your screenshots look like but it’s the same concept in Snowflake).
Note that the usage pattern for Snowplow in general is that the atomic data is a historical record, which isn’t the most efficient source for directly querying. Rather, generally the model would be to have a SQL job which models/aggregates the data to a more useful level for business purposes - so unnesting arrays would generally be handled there.