Exporting to a warehouse

A question the reports can't answer

Firebase's built-in reports answer common questions. Theo, the studio's data analyst, has an uncommon one: do players who build in the editor defend more gardens the week after?

That needs every raw event in a data warehouse, a database built for questions across millions of rows. The Observe stage sent telemetry to storage tools and kept it only as long as needed. Analytics events need a home too.

Rows or columns

Shard Garden's game database loads one garden in milliseconds. Like most traditional databases, it stores each row's fields together. Google's BigQuery docs say that is efficient for looking up single records, but every field gets read.

BigQuery, Google's warehouse, stores each column separately. A question that needs two columns over a billion rows reads just those two. Values in one column are often alike, so they compress well too.

Linking Firebase to BigQuery

Google Analytics can export raw events to BigQuery:

• Daily export: once a day, a table named events_YYYYMMDD.
• Streaming export: today's events within a few minutes, in events_intraday_YYYYMMDD. It is best-effort, may have gaps, and is replaced by the daily table.

Standard properties can export up to 1 million events a day, and exported data can't be exported again. Check the latest limits before planning.

Inside the export

Some columns of the export:

• event_name, like level_shared.
• event_timestamp: microseconds, in UTC.
• event_date: the date, in the app's registered time zone.
• user_pseudo_id: a pseudonymous ID per install.
• event_params: a repeated list of keys and values, like level_id.

Theo writes one query that flattens this into a simpler table, events: one row per event, one column per parameter.

Read less, pay less

In BigQuery, reading less data makes a query faster and cheaper. Its best-practice guide:

• Query only the columns you need.
• LIMIT on a SELECT * query does not reduce the data read.
• On a table split by date, a partitioned table, a query reads only the partitions in its date range.

The free BigQuery sandbox needs no credit card. It gives 10 GiB of storage and 1 TiB of queries a month, and its tables expire after 60 days.

Keep it only as long as needed

The raw tables hold a pseudonymous ID for every install. Theo sets them to expire after 14 months. Counts with no IDs, like shares per day, can stay longer.

Asking the warehouse

The events are in one place, in one simple table. The language for asking it questions is older than the web.

Bolt says: Next: SQL, starting with SELECT and WHERE.