Skip to main content

Using multi-valued entities

When building custom derived models you may find yourself needing to use a multi-valued entity, i.e. an entity with multiple entries in the array. These fields should not be extracted as part of the event this run table because it would lead to duplicate rows, and so are only extracted as part of the custom model where you are either keeping the rows at that level or aggregating back up.

In this page we will just showcase the SQL required, including some of our specialist macros, to extract out these entities into a 1-row per value table, for you to then use in any specific model.

SQL to extract each valueโ€‹

To make use of a multi-valued entity in BigQuery, you can make use of the LATERAL FLATTEN function.

r.value:myField::varchar, -- field will be variant type so important to cast
{{ ref('snowplow_<package_name>_base_events_this_run') }} t,
LATERAL FLATTEN(input => t.contexts_my_entity_1) r

This table can then be used to do any further analysis required, either in the same model or for use in another model.

Was this page helpful?