How I close a monthly dataset, and the month I found out mine had been lying

说明与用途

作者说明了如何关闭每月数据集，检测并修复规范化错误，并实施准确聚合、分母披露和故事限制的规则。

问题

Ensuring monthly audit reports are based on fully closed, accurate datasets and preventing misleading aggregates caused by data artifacts.

结果

The author discovered a normalization bug that inflated audit counts; by enforcing strict closing rules and aggregate safeguards, they eliminated misleading numbers and improved report integrity.

工作流

• 1. Wait until the last day of the month to close the dataset. 2. Verify that all data for the window is present and no rows are pending. 3. Count total rows and distinct domains; compare the ratio to expected values. 4. Apply a DISTINCT ON query to keep one observation per domain per window. 5. Compute aggregates using count(score) as the denominator for averages. 6. Publish each figure with its denominator. 7. Enforce a minimum sample size before reporting a segment. 8. Cap the number of derived stories per edition. 9. Exclude runaway rows, self‑audits, single‑domain outliers, and any figures that cannot be recomputed from the closed window.

证据

I found a normalization bug: two spellings of the same URL living as separate rows, and a scheduled job that read one and wrote the other, so it re‑audited forever.

来源

86.9% of the rows in the audit table were produced by that loop.

来源

No numerical findings before the data exists.

来源

Distinct entities, not rows. Every aggregate counts distinct domains, never raw rows.

来源

Publish the denominator. Every figure ships with the count behind it — and it has to be the count of values that fed that figure.

来源