Enterprise Data Warehouse

Lessons learnt manipulating BigQuery export schemas in Snowflake

Balancing schema flexibility and ease of use within your data warehouse

James White
Oct 7 · 9 min read

Consider Firebase, what is the structure of a raw event in BigQuery?

BigQuery UI — Example ‘event parameters’
BigQuery — ‘event_params’ underlying data structure

It sounds like Google have it sorted, what seems to be the problem?

Example: Product of the ‘UNNEST’ function is 8 rows (a row for each index in the event_params array)
Example: Flattening event_params via the raw Firebase BigQuery export structure in Snowflake
SELECT event_date,
event_timestamp,
event_name,
(
SELECT value.int_value
FROM Unnest(event_params)
WHERE KEY = 'ga_session_id') AS ga_session_id,
(
SELECT value.string_value
FROM Unnest(event_params)
WHERE KEY = 'firebase_screen_class') AS firebase_screen_class
FROM `<db>.<schema>.events_YYYYMMDD`
WHERE event_timestamp = 1631068354343000
AND event_name = 'screen_view'

Going nuclear, a fully flattened approach…

Simplified model — Fully flattened Google Analytics schema

To the rescue… OBJECT_AGG() & downstream data products

‘What, exactly, is dbt?’ https://blog.getdbt.com/what-exactly-is-dbt/

Enter… OBJECT_AGG()!

Example: Product of using OBJECT_AGG() & FLATTEN functions
SELECT "event_date",
"event_timestamp",
"event_name",
"event_params":"ga_session_id":"int_value",
"event_params":"firebase_screen_class":"string_value"
FROM <database>.<schema>.<table>
WHERE "event_timestamp" = 1631068354343000
AND "event_name" = 'screen_view'

To summarize, how has OBJECT_AGG() helped us?

Alternative Solutions

DAZN Engineering

Revolutionising the sport industry