usage signals missing from health score: data join failed

TL;DR

When a score component empties across many accounts at once, the join broke: keys changed format, the usage table's grain shifted from account-day to user-day, or late rows miss the scoring snapshot. Inspect the join with row counts on both sides and unmatched-key samples; joins confess quickly under counts.

usage signals missing from health score: data join failed

Use this when

• The usage component is zero or null for a broad set of accounts
• Usage dashboards outside the score look healthy
• A key, schema, or pipeline change touched either side of the join

Not for this skill when

• Only new accounts are missing, suggesting provisioning lag
• Usage itself is missing upstream, a pipeline problem not a join problem

Steps

1. Count rows and distinct account keys in the usage table and in the scoring input for the same window. Verify: the size of the unmatched population is quantified
2. Sample 20 unmatched keys from each side and compare formats character by character. Verify: the key mismatch (prefix, case, type, or id system) is visible
3. Check grain: confirm both sides aggregate to the same account and day grain before joining. Verify: any grain mismatch that fans out or drops rows is found
4. Fix the key mapping or grain, re-run the join, and compare component coverage before and after. Verify: usage components populate for the previously empty accounts
5. Add a join-coverage check (matched percentage) to the scoring run with an alert below threshold. Verify: the next join break stops the run loudly instead of scoring zeros

Variant phrasings

usage component empty in health score

A related phrasing for the same underlying problem.

join between usage and accounts failing

A related phrasing for the same underlying problem.

unmatched account keys in scoring

A related phrasing for the same underlying problem.

Compatibility: Compatibility: warehouse joins across product analytics, CRM account tables, and scoring models (dbt tests, SQL pipelines), current versions.

Why it happens

Joins fail silently by design: unmatched rows simply vanish. The tell is a component that empties broadly while its source table stays healthy. Counting both sides and reading raw unmatched keys turns the silent failure into a formatting fact.

Edge cases / pitfalls

• Inner versus left join choices decide whether a break zeroes scores or drops accounts entirely. Know which symptom you have before searching.
• Late-arriving usage needs a scoring delay or a restatement pass; a key fix will not cure a timing gap.
• Merged accounts legitimately produce many-to-one keys. Maintain the merge map as data, not as join conditions scattered in SQL.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_u3Tu2XROLuMLtAbtbwQwvw