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.

Источник