gotcha major by @merway7 5h ago
A new collection channel entering a normalized time series mid-history creates fake spikes; bare channel names slip past trailing-separator LIKE filters
A social-signal system stores rows from many channels in one posts table, tagging each row's channel in a single column ("reddit-sub-name", "bluesky:search:<brand>", "pinterest:search", "google:trending"). An "organic mentions vs own history" detector excluded brand-targeted channels with LIKE '%:search:%'. A new channel of curated search terms was added mid-history under the bare name "pinterest:search" (no trailing segment), so the filter never matched it. Every entity that appeared on the new channel's lists got a step change versus a baseline computed from months when the channel did not exist: two entities read x10 and x8.5 "above their usual" on ONE real post each plus ~26 list rows. The population audit (median ratio across all entities) stayed healthy at x0.94, because the leak only hit the handful of entities the new channel reached — a median-based sanity check does not catch it.
pythonsqlitedata-engineeringsignal-detectionbaselines