Data quality

Play this lesson

Good news, too good

On Tuesday, level shares jump 80% overnight. Kai is ready to celebrate. Theo, the studio's data analyst, checks first: the new Android build sends every share twice.

Bad data looks exactly like good news, or bad news. Data quality is the work of making sure numbers mean what everyone thinks they mean, before anyone decides anything with them.

Five ways data goes wrong

Spot the problem

level_shared per day
Date    Events Distinct IDsOct 13   5,120        5,118Oct 14   9,240        4,630

What happened on 14 October?

  1. Most shares were logged twice
  2. Players shared twice as many levels
  3. Half of the shares were lost
Show the answer

Most shares were logged twice

9,240 events but only 4,630 different event IDs: nearly every share arrived twice. Deduplicating on the event ID brings the count back to about 4,630.

Tests for data

Code has tests, and so can data. In dbt, an open-source tool for building warehouse tables, a data test is a SELECT query that looks for rows breaking a rule. Zero rows back means it passes.

dbt ships four ready-made ones:

Quick check

Which test catches level_shared events whose level_id doesn't exist in the levels table?

  1. relationships
  2. unique
  3. not_null
Show the answer

relationships

A relationships test checks that every value in one table exists in another, which dbt calls referential integrity. unique and not_null can't know what the levels table holds.

Keep the test phones out

Remy's test phone runs in debug mode, sending events at once. Firebase's docs say debug events are still included in the daily BigQuery export by default, and recommend a developer traffic filter to keep test traffic out of reports.

Theo does both: the filter for reports, and a rule in the warehouse that drops events from the studio's test devices.

Whose midnight?

In Google Analytics' BigQuery export, event_timestamp is in UTC, but event_date uses the app's registered time zone. A garden defended at 9 a.m. in Sydney happened on the day before in UTC.

Theo picks one rule, writes it into every metric's definition, and uses it everywhere: daily numbers use UTC days.

Quick check

In the export, event_date and event_timestamp always agree about which day an event happened.

Show the answer

False

event_timestamp is in UTC and event_date in the app's registered time zone. Near midnight, the two can point to different days.

True story: the lost Mars orbiter

NASA's Mars Climate Orbiter launched on 11 December 1998. On 23 September 1999, as it arrived at Mars, contact was lost.

NASA says the cause was a navigation error: a failure to translate English units to metric. Software on the ground used imperial measurements while the spacecraft's software used metric, so the trajectory calculations went wrong and the spacecraft came far too close to Mars.

A number without its unit is a guess.

Source: NASA: Mars Climate Orbiter, science.nasa.gov

Bug hunt

garden.js
log('garden_defended', {  garden_id: g.id,  duration:    g.endMs - g.startMs,});

Theo's dashboard says an average defence takes 412,000 seconds. Which line is the problem?

  1. Line 3
  2. Line 2
  3. Line 1
Show the answer

Line 3

Line 3 sends milliseconds in a field whose name gives no unit, and the dashboard reads it as seconds. Name it duration_seconds and divide by 1,000.

Right, and also fair

Clean data is only half the job. The other half is collecting no more than the studio needs, from players who are often children.

Bolt says: Next: privacy-safe analytics.

Fact-checked 11 October 2026