Numbers rarely break loudly. They drift, and the chart keeps drawing. This lesson is about the ways that happens, the tests that catch them, and what to do once you find out a published number was wrong.
How numbers go wrong quietly
Six failures account for most of it, and none of them produces an error:
1. An upstream column changes meaning. status gains a new value, region is renamed, a currency becomes minor units. The query runs; the filter silently matches less.
import pandas as pd
before = pd.DataFrame({'status': ['complete'] * 80 + ['cancelled'] * 20})
after = pd.DataFrame({'status': ['completed'] * 78 + ['cancelled'] * 20 + ['refunded'] * 2})
for label, frame in [('before', before), ('after', after)]:
matched = (frame['status'] == 'complete').sum()
print(f'{label:<7} rows {len(frame):>3} matching "complete" {matched:>3} '
f'rate {matched / len(frame):.0%}')
print()
print('A one-letter change upstream and the metric reads zero. No error.')
Open this example in the editor and change it. Nothing you type is saved.
2. A join starts multiplying. A lookup table gains a duplicate key and every total inflates.
3. Late-arriving data. Events for Monday arrive on Wednesday, so Monday looks low until it is restated - and a chart built on Tuesday is wrong.
4. A partial load. The pipeline failed halfway, wrote what it had, and the day looks like a 40% drop.
5. A timezone shift. A source starts sending UTC instead of local time, and every day boundary moves by hours.
6. The definition changed. Somebody improved the query. The step in the chart looks like news.
Tests that catch them
The useful tests are boring, run every load, and fail loudly.
import numpy as np
import pandas as pd
def check(frame: pd.DataFrame, expectations: dict) -> list:
"""Returns a list of failures, empty if everything passed."""
failures = []
rows = len(frame)
low, high = expectations['row_range']
if not low <= rows <= high:
failures.append(f'row count {rows:,} outside expected {low:,}-{high:,}')
for column in expectations['not_null']:
missing = int(frame[column].isna().sum())
if missing:
failures.append(f'{column} has {missing} null(s)')
for column in expectations['unique']:
duplicates = int(frame[column].duplicated().sum())
if duplicates:
failures.append(f'{column} has {duplicates} duplicate(s)')
for column, allowed in expectations['values'].items():
unexpected = set(frame[column].dropna().unique()) - set(allowed)
if unexpected:
failures.append(f'{column} has unexpected values {sorted(unexpected)}')
for column, (low, high) in expectations['ranges'].items():
out = int(((frame[column] < low) | (frame[column] > high)).sum())
if out:
failures.append(f'{column} has {out} value(s) outside [{low}, {high}]')
return failures
EXPECTATIONS = {
'row_range': (900, 1200),
'not_null': ['order_id', 'placed_on', 'region', 'revenue'],
'unique': ['order_id'],
'values': {'region': ['North', 'South', 'East', 'West'],
'status': ['complete', 'cancelled', 'pending']},
'ranges': {'revenue': (0, 100_000), 'units': (1, 1000)},
}
rng = np.random.default_rng(5)
good = pd.DataFrame({
'order_id': np.arange(1000),
'placed_on': pd.Timestamp('2026-03-01'),
'region': rng.choice(['North', 'South', 'East', 'West'], 1000),
'status': rng.choice(['complete', 'cancelled', 'pending'], 1000),
'units': rng.integers(1, 60, 1000),
'revenue': rng.gamma(4, 120, 1000).round(2),
})
print('good load:', check(good, EXPECTATIONS) or 'all checks passed')
print()
broken = good.copy()
broken.loc[0, 'region'] = 'Nord' # a renamed value
broken.loc[1, 'revenue'] = np.nan # a null
broken.loc[2, 'order_id'] = broken.loc[3, 'order_id'] # a duplicate key
broken = broken.iloc[:500] # a partial load
for failure in check(broken, EXPECTATIONS):
print(' -', failure)
Open this example in the editor and change it. Nothing you type is saved.
Five checks, forty lines, and they catch four of the six failure modes on the way in. Run them on every load and fail the pipeline rather than publishing.
The other two - late data and definition changes - need a different kind of check:
import numpy as np
import pandas as pd
rng = np.random.default_rng(11)
# A daily volume series, with a partial load on one day and a
# genuine drop on another.
days = pd.date_range('2026-02-01', periods=40, freq='D')
volume = pd.Series(rng.normal(4800, 180, 40).round(), index=days)
volume.iloc[25] = 1900 # a partial load
volume.iloc[33] = 4100 # a real but modest dip
# A robust band from the trailing median and MAD, which a couple of
# bad days cannot widen the way a mean and std would.
median = volume.rolling(14, min_periods=7).median().shift(1)
mad = (volume - median).abs().rolling(14, min_periods=7).median().shift(1)
score = (volume - median) / (1.4826 * mad)
alerts = volume[score.abs() > 4].dropna()
print('days flagged as anomalous:')
for day, value in alerts.items():
print(f' {day.date()} {value:,.0f} '
f'(expected about {median[day]:,.0f}, z={score[day]:+.1f})')
print()
print('The partial load is flagged. The genuine 15% dip is not, which')
print('is correct: a volume alert is for breakage, not for business.')
Open this example in the editor and change it. Nothing you type is saved.
The volume check is the single highest-value test there is, because a partial load is the commonest failure and it is invisible in any rate or average.
Version the definitions
import pandas as pd
history = pd.DataFrame([
{'version': 1, 'effective': '2025-06-01',
'change': 'first definition: any session counts as active'},
{'version': 2, 'effective': '2025-11-15',
'change': 'excluded sessions under 3 seconds (bot traffic)'},
{'version': 3, 'effective': '2026-02-01',
'change': 'excluded internal accounts'},
])
print(history.to_string(index=False))
print()
print('A step in the chart on any of those dates is a definition')
print('change, not news - and the only way to know is this table.')
Open this example in the editor and change it. Nothing you type is saved.
Keep the definition in version control next to the query, with a date on every change. Then when somebody asks why the number stepped on 15 November, the answer takes ten seconds rather than a day.
Where possible, restate history under the new definition so the series is comparable, and keep the old series alongside for one period so the two can be compared.
Annotate the charts
import numpy as np
import pandas as pd
rng = np.random.default_rng(17)
days = pd.date_range('2026-01-01', periods=90, freq='D')
series = pd.Series(np.r_[rng.normal(1000, 40, 45), rng.normal(880, 40, 45)].round(),
index=days, name='active')
ANNOTATIONS = {
'2026-02-15': 'definition v3: internal accounts excluded',
'2026-03-02': 'marketing campaign started',
}
print('daily actives, with annotations:')
for day in days[::10]:
key = day.strftime('%Y-%m-%d')
note = ANNOTATIONS.get(key, '')
bar = '#' * int(series[day] / 40)
print(f'{key} {series[day]:>6,.0f} {bar} {note}')
print()
print('The step at 2026-02-15 is a definition change. Without the')
print('annotation it is an incident, and somebody spends a day on it.')
Open this example in the editor and change it. Nothing you type is saved.
An unexplained step in a chart costs somebody a day, every time, until it is annotated. The annotation costs one line.
When you find out it was wrong
This happens, and how it is handled matters more than the error.
import pandas as pd
print(pd.DataFrame([
{'step': '1. stop the bleeding',
'action': 'pause the dashboard or mark it stale before anyone else uses it'},
{'step': '2. establish the blast radius',
'action': 'which numbers, which dates, which decisions used them'},
{'step': '3. tell people, early',
'action': 'a short note with the wrong number, the right one, and why'},
{'step': '4. fix forward',
'action': 'correct the pipeline and restate the affected history'},
{'step': '5. add the test',
'action': 'the check that would have caught it, before closing the issue'},
]).to_string(index=False))
Open this example in the editor and change it. Nothing you type is saved.
Three things about step three. Say the size of the error, not just that there was one - "actives were overstated by 12% from 1 February" is useful; "there was a data issue" is not. Name the decisions that may be affected, so people can revisit them. And do it before somebody else finds it, because the credibility cost of self-reporting is a fraction of the cost of being caught.
Step five is the one teams skip. An incident without a new test is an incident that will recur.
A worked example
import numpy as np
import pandas as pd
rng = np.random.default_rng(seed=227)
# Sixty days of loads, with three deliberate faults planted.
days = pd.date_range('2026-01-01', periods=60, freq='D')
frames = []
for index, day in enumerate(days):
n = int(rng.normal(1000, 60))
frame = pd.DataFrame({
'order_id': np.arange(index * 2000, index * 2000 + n),
'placed_on': day,
'region': rng.choice(['North', 'South', 'East', 'West'], n),
'status': rng.choice(['complete', 'cancelled', 'pending'], n, p=[.8, .1, .1]),
'units': rng.integers(1, 60, n),
'revenue': rng.gamma(4, 120, n).round(2),
})
# Fault 1: a partial load on day 20.
if index == 20:
frame = frame.iloc[:380]
# Fault 2: a renamed category from day 35.
if index >= 35:
frame.loc[frame['region'] == 'North', 'region'] = 'Nord'
# Fault 3: duplicated rows on day 48.
if index == 48:
frame = pd.concat([frame, frame.head(120)], ignore_index=True)
frames.append(frame)
EXPECTED_REGIONS = {'North', 'South', 'East', 'West'}
EXPECTED_STATUSES = {'complete', 'cancelled', 'pending'}
# ---- Per-load checks. ----------------------------------------------
report = []
for frame in frames:
day = frame['placed_on'].iloc[0]
failures = []
if frame['order_id'].duplicated().any():
failures.append(f'{int(frame["order_id"].duplicated().sum())} duplicate ids')
unexpected = set(frame['region'].unique()) - EXPECTED_REGIONS
if unexpected:
failures.append(f'unexpected regions {sorted(unexpected)}')
if set(frame['status'].unique()) - EXPECTED_STATUSES:
failures.append('unexpected status')
if frame['revenue'].isna().any():
failures.append('null revenue')
report.append({'day': day, 'rows': len(frame), 'failures': failures})
checks = pd.DataFrame(report).set_index('day')
# ---- Volume check, against a robust trailing band. ------------------
volume = checks['rows']
median = volume.rolling(14, min_periods=7).median().shift(1)
mad = (volume - median).abs().rolling(14, min_periods=7).median().shift(1)
checks['z'] = ((volume - median) / (1.4826 * mad)).round(1)
flagged = checks[(checks['failures'].str.len() > 0) | (checks['z'].abs() > 4)]
print(f'{len(flagged)} of {len(checks)} loads flagged:')
for day, row in flagged.iterrows():
reasons = list(row['failures'])
if abs(row['z']) > 4:
reasons.append(f'volume z={row["z"]:+.1f} (rows {row["rows"]:,}, '
f'expected about {median[day]:,.0f})')
print(f' {day.date()} ' + '; '.join(reasons))
print()
# ---- What the fault cost, if it had gone unnoticed. -----------------
combined = pd.concat(frames, ignore_index=True)
daily = combined.groupby('placed_on').agg(
rows=('order_id', 'size'),
unique_orders=('order_id', 'nunique'),
north=('region', lambda s: int((s == 'North').sum())),
revenue=('revenue', 'sum'),
)
print('the three faults, as a reader of the dashboard would see them:')
print()
print('1. partial load')
print(f' {days[20].date()}: {daily.loc[days[20], "rows"]:,} rows against a '
f'typical {int(daily["rows"].median()):,} - a 62% "drop" in orders')
print()
print('2. renamed category')
north_before = daily.loc[days[30], 'north']
north_after = daily.loc[days[40], 'north']
print(f' North orders: {north_before} on {days[30].date()}, '
f'{north_after} on {days[40].date()}')
print(' The North region vanished from every chart that filters on it,')
print(' and the total was unaffected - so no volume alert would fire.')
print()
print('3. duplicated rows')
day48 = daily.loc[days[48]]
inflation = day48['rows'] / day48['unique_orders'] - 1
print(f' {days[48].date()}: {day48["rows"]:,} rows for '
f'{day48["unique_orders"]:,} distinct orders ({inflation:+.0%} inflation)')
print(f' revenue overstated by roughly {inflation:.0%} for that day')
print()
# ---- The lesson: which check caught which. --------------------------
print('which test caught which fault:')
print(' partial load -> the volume band')
print(' renamed value -> the allowed-values check (the volume band missed it)')
print(' duplicate rows -> the uniqueness check (the volume band was marginal)')
print()
print('No single test catches all three. That is why there are five.')
Open this example in the editor and change it. Nothing you type is saved.
The second fault is the one worth dwelling on. Renaming North to Nord changed no totals at all - the row count is normal, the revenue is normal, every aggregate is correct. Only the allowed-values check notices, and without it the North region silently reads zero on every filtered chart for as long as it takes somebody to ask.
When it goes wrong
| Symptom | Cause |
|---|---|
| A metric went to zero | An upstream value was renamed |
| Yesterday looks terrible every morning | Late-arriving data; state the restatement window |
| One day is 40% down | A partial load; add a volume check |
| Totals jumped with no new customers | A join multiplied; add a uniqueness check |
| A step appeared on a round date | A definition change; annotate it |
| Day boundaries moved by hours | A timezone change upstream |
| The same incident happened twice | No test was added the first time |
| Nobody trusts the dashboard | A wrong number that was never acknowledged |
A check you can run
Pick your most important daily table and write down three numbers: the typical row count, the set of allowed values for your main categorical column, and the primary key.
Then write the three assertions and run them over the last ninety days of history. In my experience this finds at least one day that was already wrong and nobody noticed - and that day is in somebody's quarterly chart.