The fake zero: when a missing value must be NULL, not 0

Background

I run the social-analytics pipeline for a media group: Facebook, Instagram, Threads and YouTube pulled into BigQuery every morning, then modeled with dbt and surfaced on a Streamlit dashboard the editorial and marketing teams actually open. One of the Instagram charts is net daily follows — new follows minus unfollows, a green bar on a net-positive day, red on a net-negative one.

One afternoon someone asked why that chart looked dead. From around 06-12 onward it was just flat — every bar sitting exactly on zero, as if the account had stopped gaining and losing followers entirely.

The tell

My first guess was the boring one — no recent data. A flat tail usually means exactly that. It didn’t hold up, though, because over the same stretch the account’s follower count was up 32. If the total was climbing, the daily net couldn’t be zero every day; the net change is what moves the total in the first place.

One quiet day reading zero, fine. Two weeks of perfectly flat zeros sitting next to a rising follower count is not a quiet account — it’s a bug. So I went looking for it.

Root cause

Instagram’s follows_and_unfollows insight doesn’t return a plain number. You request it with breakdown=follow_type and it comes back split into FOLLOWER (new follows) and NON_FOLLOWER (unfollows); the net value is FOLLOWER − NON_FOLLOWER. So far, fine.

What changed — Meta rolled this out around June 2026 — is when those breakdown results finalize. For roughly the last ten days, the API returns a shell: the breakdowns structure is present, but the results array inside it is missing entirely. The data simply isn’t computed yet.

// finalized day (older than ~D-10): results are present
{ "name": "follows_and_unfollows",
"total_value": { "breakdowns": [ { "dimension_keys": ["follow_type"],
  "results": [ { "dimension_values": ["FOLLOWER"],     "value": 5 },
               { "dimension_values": ["NON_FOLLOWER"], "value": 2 } ] } ] } }

// recent day (within ~D-10): structure present, results ABSENT
{ "name": "follows_and_unfollows",
"total_value": { "breakdowns": [ { "dimension_keys": ["follow_type"] } ] } }
      Instagram follows_and_unfollows — two response shapes
    

My normalizer handled the first shape and walked straight into the second. It read results with a default of empty list, built an empty lookup, and did the subtraction anyway:

results = tv["breakdowns"][0].get("results", [])   # absent → []
breakdown_map = {r["dimension_values"][0]: r["value"] for r in results}  # → {}
value = breakdown_map.get("FOLLOWER", 0) - breakdown_map.get("NON_FOLLOWER", 0)
#       0  -  0  =  0   ← a number we never measured
      what the old code actually computed
    

So every not-yet-final day came back as 0 — not as missing data, but as an actual measured value. The chart can’t tell that apart from a genuinely quiet day, so it drew a zero bar and moved on.

Why a fake 0 is worse than no data

Storing 0 here instead of NULL caused two separate problems. NULL would have said “we don’t know yet.” The 0 said “we checked, and the answer was zero” — a claim I couldn’t actually stand behind. In an append-only warehouse that small lie cost me twice.

It poisoned the chart. The dashboard can drop NULLs and leave an honest gap at the tail. It can’t drop a 0 — a 0 is a perfectly valid measurement, so it renders, and you can’t tell it apart from a real no-activity day.

It froze itself in. My raw tables are append-only, and the dbt staging layer keeps the latest row per day with QUALIFY ROW_NUMBER() OVER (… ORDER BY ingested_at DESC) = 1. That’s meant to be self-healing: re-fetch a day, the newer row wins, the real value overwrites the placeholder. But only if you re-fetch it — and this metric was being pulled in the same 7-day window as the fast ones, while it doesn’t finalize until ~D-10. By the time the real number existed upstream, the day had already dropped out of the window and nothing was asking for it again. The fake 0 was there to stay.

The fix

Two parts, because the bug had two halves. First, stop manufacturing the zero — an absent or empty results means not finalized, which is NULL, not 0 − 0:

Second, make the self-healing actually reach this metric. The re-fetch window has to outlast the upstream finalization lag, or the NULL freezes in exactly the way the 0 used to. So this one metric gets its own, longer window:

# fast-finalizing metrics (views, interactions, reach…): 7 days is plenty
for date in last_n_days(days_back):            # days_back = 7
  rows.append(normalize_total_value(fetch_total(date), slug, date))

# follows_and_unfollows finalizes ~D-10, so re-fetch further back than that
FOLLOWS_BACKFILL_DAYS = 14                      # 14 > 10, with margin
for date in last_n_days(FOLLOWS_BACKFILL_DAYS):
  rows.append(normalize_total_value(fetch_follows(date), slug, date))
      the window has to outlast the lag
    

Now each recent day is written as NULL, then re-fetched on every subsequent run while it’s still inside the 14-day window. The day it finalizes upstream (~D-10), the next run pulls the real number, the newer ingested_at wins the dedup, and NULL → real value with no manual backfill. After the one-time correction (06-14 / 06-15 recovered their true values, 06-16 onward set to NULL instead of fake 0), the pipeline heals the tail on its own from here.

Pinning it down with tests

The catch is that a real 0 still has to survive: an explicit FOLLOWER 0 / NON_FOLLOWER 0 day is genuine zero activity and should stay 0, not turn into NULL. So the two new tests cover only the unfinalized shapes, and the original real-zero test still covers the case on the other side:

• breakdowns present but no results key → NULL
• results present but an empty list → NULL
• explicit FOLLOWER 0 / NON_FOLLOWER 0 → still 0 (unchanged — real measured zero)

176 tests pass. The dashboard caption now says the quiet part out loud:

Takeaways