In a BigQuery dataset feeding our monthly KPI report, a new source added last week pushed nulls in customer_region from 0.6% to 7.8%, and downstream segment revenue skews by about 3%… I can add dbt tests and quarantine rules, but what thresholds do you enforce before failing a job versus just flagging, especially when SLAs require a noon PT publish?
I’d use a two-tier rule: alert when nulls move >1 percentage point or >2x the 4‑week median; fail only if >5% or if the modeled revenue impact >1% — so at 7.8%/3% I’d fail, quarantine that source, then backfill. Think smoke alarm vs sprinklers. Would a short ‘degraded’ publish with the region excluded still meet your noon SLA?
When a new feed spikes nulls, I put a kill‑switch in the model: if that source’s customer_region nulls clear 3%, the KPI model temporarily excludes just that source and marks the run as ‘degraded’, then we backfill when fixed. It keeps the noon publish and avoids the 3% revenue wobble; I only hard‑fail if dropping the source would wipe out more than about 15% of volume. Do you have a reliable per‑source key you can filter on?
Ship a ‘degraded mode’: carry forward last-known region 30 days; fail if revenue delta >2%. @ops okay?
But , with BigQuery and a ‘noon PT’ SLA, I add a 10:15 a.m. canary run that logs unknown_region_share and a PSI vs the 4‑week baseline; ship at noon unless share >2% or PSI >0.2, otherwise auto-bucket customer_region as ‘Unknown’ and mark the run degraded. That contains a 7.8% spike without failing the job and keeps the revenue skew obvious — can you squeeze in a canary window?
I’ve had better luck gating on impact, not raw nulls: in BigQuery I run a quick KS-test between today’s region mix and a 28‑day baseline and only fail if the test is significant and the estimated revenue bias >1%, otherwise I still hit the noon PT publish with a “low confidence” tag. Tiny caveat: at quarter close I tighten that to 0.5%; would that slot cleanly into your dbt tests?