Missing Data and Gaps in Health Metrics

Pick the policy before you write the schema, because the schema is where the mistake becomes permanent. A daily-metric row with a value column and no companion column has already decided that absence is zero, and once you have written a few million of those rows you cannot recover which of them meant "this person did not move" and which meant "we never saw this day". Those are different facts about a human being. Storing both as 0 destroys the difference, and no amount of downstream cleverness gets it back.

The failure users actually report looks like this. A user's phone is off for two days. Your rollup writes 0 for both. Your weekly summary divides by seven, reports a 29% drop in activity, and sends a push notification about it. Their streak — the thing they actually cared about — resets. Nothing happened to this person's behaviour; something happened to your pipeline, and you told them it was about them. That is a trust event, and it is not recoverable by fixing the bug next sprint, because the streak is gone.

The three states, and why two are not enough

Every (user, metric, local_date) cell is in one of three states, and most schemas only model two:

"Measured zero" is real and you should not collapse it either. A user whose phone sat on a desk all day genuinely took very few steps; a user who logged no workout on a day their watch was worn continuously genuinely rested. Those are facts worth keeping. What makes them facts is coverage — evidence that a source was present and reporting during that day. Without coverage, a zero is fiction.

So the minimum model is a value plus a status, not a value alone:

CREATE TABLE daily_metric (
  user_id        uuid        NOT NULL,
  metric         text        NOT NULL,
  local_date     date        NOT NULL,
  status         text        NOT NULL,      -- 'measured' | 'unknown'
  value          numeric,                   -- NULL unless status = 'measured'
  unknown_reason text,                      -- NULL unless status = 'unknown'
  covering_sources int       NOT NULL DEFAULT 0,
  computed_at    timestamptz NOT NULL,
  PRIMARY KEY (user_id, metric, local_date),
  CHECK ((status = 'measured') = (value IS NOT NULL))
);

The CHECK is the point of the whole table. It makes "a number with no provenance" unrepresentable. Note also that local_date is a written column, not a function of the timestamp — see timezones and day boundaries for why a daily rollup bucketed on a UTC instant is wrong by several hours of activity for most of your users.

Why interpolation is worse here than in other domains

Interpolation is a reasonable default in plenty of systems. It is a bad default in this one, for a reason specific to health data: the gaps are not missing at random, and the reason for the gap is correlated with the value you are trying to guess.

Think about when people stop wearing a tracker. They are ill. They are travelling. They are in hospital. The charger stayed at home. The week collapsed. These are precisely the weeks whose values differ most from the neighbouring weeks you would interpolate from. Linear interpolation across a sick week does not produce a slightly-noisy estimate; it produces a confidently normal week that did not happen. In a domain where the numbers describe a person's body, that is a different category of error than a missing telemetry point on a server graph.

The second problem is contamination. An interpolated value that is stored is indistinguishable from a measured one within about two hops. It flows into a 30-day baseline, into a trend line, into the "readiness" figure your coaching logic branches on, into the training-load model, into whatever context you hand an LLM to write a weekly summary — and every one of those consumers reports it with the same confidence as a real measurement. If you interpolate at all, do it at render time, in code that has no write path, and never let the interpolated number reach storage or a derived feature.

There is one place we are honestly undecided: a body-weight chart. Weight is measured intermittently by design — nobody stands on the scale daily — and a scatter of unconnected dots reads badly. Drawing a dashed connector between real measurements is a defensible display choice, because the dashes tell the user which points are data. Drawing a solid line, or storing the daily interpolated weight, is not.

The platform traps that manufacture fake zeros

This is where the abstract policy meets two specific, documented behaviours you have to code around.

Apple: denied read permission is indistinguishable from no data. By design. Apple states it flatly: "your app cannot determine whether or not a user has granted permission to read data. If you are not given permission, it simply appears as if there is no data of the requested type in the HealthKit store." It is restated on the privacy page as "From the app's point of view, no data of that type exists." So the empty array your step query returns is at minimum four-way ambiguous: genuinely no steps, read permission denied, a limited-authorization window that excludes the range you asked for, or the device being locked. Only the last of those surfaces as an error — Apple documents errorDatabaseInaccessible for a query run while the device is locked, and it is retryable, not empty. Diagnosing one specific empty query belongs to HealthKit returns no data; what follows is what your schema does about the ambiguity in general.

A related trap sits inside the query API most people use for daily totals. Apple documents that when you enumerate a statistics collection, "if the time interval doesn't contain any samples, the provided statistics's sumQuantity() method returns nil." That nil is the platform telling you unknown. A Swift optional-coalescing operator that turns it into 0 on the way into your payload is the single line of code that creates most of these bugs.

The one positive signal Apple gives you is getEarliestAuthorizedSampleDate(for:completion:), and Apple is explicit that it is the only one: "Limited authorization is the only authorization state your app can positively identify." A type is omitted from that dictionary when your app has no read access, when it has full read access, or when it has limited access with no specific earliest date — three very different situations, one identical response. Where it does return a date, Apple's own instruction is the thesis of this page, in their words: "Treat all data before that date as unknown — not an absence of data," and "Avoid interpreting the absence of older samples as evidence they don't exist." Call it after authorization, persist it per type, and clamp your query windows to the later of your intended start and that date, which is what Apple's sample code does.

Keep that separate from earliestPermittedSampleDate(), which Apple describes as "a systemwide platform constraint that applies equally to all apps" and "isn't related to what the person chooses to share with your app." One is a permission boundary, one is a platform floor, and they need different reason codes.

Google: the history window is a hole, not a zero — but at least it is loud. Google documents that "By default, all applications can read data from Health Connect for up to 30 days prior to when any permission was first granted," and that reading beyond it needs PERMISSION_READ_HEALTH_DATA_HISTORY: "without this permission, an attempt to read records older than 30 days results in an error." That error is a gift. Unlike Apple's silence, Android tells you the boundary exists, so you can label everything past it outside_history_window with certainty rather than inference.

Two wrinkles that produce gaps you will otherwise misread:

• The window is anchored to first permission grant and resets on reinstall. Google's worked example: an app deleted on 10 May 2023 and reinstalled with permission granted on 15 May 2023 can read back only to 15 April 2023. A returning user therefore develops a fresh hole in the middle of a history you may already hold from their previous install. Which leads to the rule in the next section: a re-sync must never let unknown overwrite measured.
• The version split matters. On Android 14 and higher there is no historical limit on an app reading its own data and a 30-day limit on other data; on Android 13 and lower the 30-day limit applies to any data. Same user, same app, different absence semantics depending on OS version.

Health Connect also has no push mechanism at all — Google states plainly that "your app can't get notified of new data," and that "access to Health Connect may be interrupted at any point," with the documented expectation that you "continue the next time the app is opened." So on Android a third of your gaps are simply "we have not looked yet", and that deserves its own reason code, because it resolves itself and the others do not. Shrinking that third is a client-scheduling problem rather than a schema one — designing for the wake that never comes is where it is solved.

One more Android-specific gap that is entirely your own doing: Google documents that from the June 2026 update, steps counted by Health Connect itself are attributed to a per-device, per-app Synthetic Package Name, while "Historical step data recorded before June 2026 retains the android package name." If your read filters by DataOrigin against a hardcoded list, your step series develops a clean cliff at that date that looks exactly like a user who stopped walking. Google's guidance is to query for both android and the SPN resolved at runtime via getCurrentDeviceDataSource(), and notes that apps which aggregate without filtering by DataOrigin are unaffected. Relatedly, on-device step counting "is active only when at least one application on the device has been granted the READ_STEPS permission" — so on a fresh device there may be no on-device step history before any app asked, and that absence is structural, not behavioural.

Absence is per-metric, not global

The most useful thing you can do after adding a status column is stop treating all metrics the same. A missing step day and a missing sleep night carry completely different amounts of information.

The sleep row is the one worth arguing about with your team. A missing sleep night is close to self-explanatory — the device was not on the wrist — which means it is one of the few metrics where you can honestly show the user a specific message ("your ring wasn't worn on Tuesday") instead of a vague one. Steps are the opposite: both platforms give you the same silence for four different causes, so honest copy is vague copy.

What we would actually build

Five rules, in the order they save you the most pain.

1. Unknown never overwrites measured. A re-read that returns nothing must not blank out a day you already have. Permission revocation, a lapsed history window, an interrupted sync and a device left at home all produce the same empty response, and none of them is evidence that the earlier data was wrong. The only thing that may remove a measured value is an explicit deletion signal — Apple's HKDeletedObject via an anchored query, or Health Connect's DeletionChange. Both are id-only, which is why you must have persisted the platform record id at ingest to act on them at all. Everything else is downgraded to a no-op.

2. Compute coverage as a first-class value, not as a side effect. For each (user, metric, day), record how many distinct sources reported anything at all. That integer is what licenses a stored zero, it is what your averages divide by, and it is the natural input to a monitoring alert — a provider-wide drop in mean coverage is visible days before it shows up as missing rows. That belongs in data quality monitoring alongside freshness.

3. Make the reason code specific enough to act on. not_authorized_or_no_data (iOS, honestly compound — do not pretend you can split it), before_authorized_history, outside_history_window, not_yet_synced, provider_error, no_source_coverage. The value of the taxonomy is that each one implies a different action: retry, prompt for permission, show a history boundary, wait, alert you.

4. Gate derived numbers on coverage, not on row count. Averages divide by covered days and the UI states the denominator — "5,900 steps, average of the 5 days we have data for" beats a silently wrong seven-day mean. Streaks suspend on an unknown day rather than breaking; telling a user "we don't have data for Tuesday" is recoverable, telling them they broke a 90-day streak is not. Personal bests, "best ever" badges and trend arrows stay suppressed while historical backfill is still running, because a partial history produces a wrong PR that the user will see and remember.

5. Chart the absence. Break lines at gaps instead of connecting across them; most charting defaults will happily draw a straight segment over a two-week hole, which is interpolation you did not decide to do. Give measured-zero and unknown different marks. Label the earliest date you actually hold, so the empty left-hand side of a chart reads as "not imported yet" or "before you granted access" rather than as a sedentary March.

The honest limits

On iOS, this design does not solve the core ambiguity — nothing does. Apple has decided, deliberately and for good privacy reasons, that you cannot distinguish a denied read from an empty store. The best available move is behavioural rather than architectural: if a metric returns nothing for an unbroken stretch that is implausible for an active user, prompt them to check permissions in the Health app. That is a heuristic. You will occasionally nag someone who was genuinely on holiday, and you cannot ever prove which case you were in.

Coverage is itself an inference. "The watch wrote a heart-rate sample at 03:00, therefore it was worn" is a good heuristic, not a fact, and neither platform publishes a wear-time signal you can read directly. Treat coverage as a confidence input, not as ground truth, and do not build a feature that depends on it being precise.

Absence is revisable, which most schemas forget. A day that is unknown today can become measured tomorrow — late watch sync, a user granting the history permission, a backfill reaching further back. Apple additionally documents that HealthKit condenses old workout data and deletes the originals, so even settled history churns. Your unknown rows therefore need a re-check policy and a computed_at, not a one-time write. This is the same machinery as metric versioning and recompute; do not build it twice.

On buying instead of building. For most teams, buying an aggregator is the right call, and we would say so even though this page is about building the layer yourself. Maintaining a fleet of connectors — OAuth refresh, per-vendor schema drift, webhook reliability — is a permanent tax that a vendor absorbs better than you will; see health data aggregator APIs and what a health data aggregator is. But understand what buying does and does not move here. Absence semantics are yours regardless of who fetches the bytes. A normalized daily payload can just as easily hand you a 0 for a day the vendor never saw, and now the fake zero is upstream of you and harder to detect. Make this an explicit evaluation question in any vendor call: for a day with no data, does your API return 0, return null, or omit the key entirely — and do you distinguish "user has not connected" from "user connected but no data"? The answers vary, they are frequently undocumented, and a vendor that has a crisp answer has thought about the problem. We would build this layer ourselves when the product's core claim depends on the numbers being defensible — clinical, insurance, or research contexts where you may have to explain a specific figure to someone — and buy otherwise.

In short

Zero is a measurement. Null is a fact about your pipeline. Interpolation is a guess about a person. Only the first two belong in storage, and only if you can say which one you meant. Add the status column before you add the rows.

For the Android symptom-level version of this — a Health Connect read that comes back empty — see Health Connect returns no data. For what a step count is in the first place across platforms, step counting APIs.