DEV Community: tbalcewicz

The silent zero: the worst bug your monitoring system can have

tbalcewicz — Sat, 15 Aug 2026 17:12:29 +0000

A crash is a good outcome. Somebody gets paged, somebody fixes it, everyone moves on.

The bad outcome is the one where your system finishes cleanly, reports nothing, and is wrong. No exception. No alert. A perfectly normal-looking run that says "0 new items" - when the truth is "I could not reach the source and I have no idea what is out there."

I call it the silent zero, and it is the most expensive bug I know of in any system whose job is to watch something.

Why it is so easy to write

Nobody sets out to build this. It appears through reasonable-looking defensive coding:

def fetch_notices():
    try:
        return api.get_notices()
    except Exception:
        return []          # here

Every instinct says this is the responsible version. It does not crash. It degrades gracefully. The report still goes out.

But look at what the caller sees. fetch_notices() returned an empty list. There are exactly two situations that produce an empty list:

the source is up, and genuinely published nothing today
the source is down, unreachable, rate-limiting you, or changed its response format

These are opposite situations and the code cannot distinguish them. One means "all clear", the other means "you are blind". They arrive as the same value.

How it actually plays out

A monitoring system is trusted in proportion to how boring it is. After a few quiet weeks, "nothing today" stops being read as information and starts being read as absence of information. That is exactly when it becomes dangerous.

The failure has a shape. A portal changes its HTML, or rotates a certificate, or starts requiring a header you do not send. Your collector throws. Your except swallows it. The report says zero. It says zero the next day too, and the day after.

Nobody investigates a quiet inbox. Weeks later someone asks why you missed something obvious, and you discover the source has been dark since a date you have to reconstruct from git log.

The cost is not the outage. It is that you cannot say when it started, so you cannot say what you missed.

The rule

A failure and an empty result must never be representable by the same value.

Three things follow.

1. Count failures explicitly, and make them travel

def collect(sources):
    notices, failures = [], []
    for source in sources:
        try:
            notices.extend(source.fetch())
        except (URLError, TimeoutError, JSONDecodeError) as exc:
            failures.append((source.name, repr(exc)))
    return notices, failures

Two return values. The caller cannot ignore the second without deciding to.

2. Put the failure in the output humans read

Not in a log file nobody opens. In the artefact itself - the email subject, the top of the spreadsheet, the first line of the report:

subject = f"Tender report - {len(rows)} new"
if failures:
    subject = f"[PARTIAL - {len(failures)} source(s) down] " + subject

A recipient who sees [PARTIAL - 3 sources down] in their inbox knows to distrust the zero. That is the whole goal.

3. Catch narrow, and let the unknown crash

except Exception:          # hides bugs in your own code
except (URLError, TimeoutError, JSONDecodeError):   # only expected failures

Broad except catches your KeyError from a renamed field just as happily as a network timeout. A schema change silently becomes "no results", which is the same silent zero wearing a different hat. If you did not anticipate it, you want the crash.

4. Isolate per source, not per run

Wrapping the whole collection loop in one try/catch means the first failing source kills the other nine. Wrap each one. A run with seven of ten sources plus a loud warning is genuinely useful. A run that aborted at source one is not.

Alert on silence, not just on errors

The strongest version of this does not wait for a failure to be detected at all. It treats prolonged silence as a signal in its own right:

if days_since_last_result(source) > source.expected_quiet_days * 2:
    warn(f"{source.name}: nothing in {days} days - verify it still works")

Sources have a normal rhythm. If one usually yields something every couple of days and has been silent for two weeks, that is worth a human glance - regardless of whether anything technically threw. Sometimes the answer is "quiet season". Sometimes it is "we have been scraping a 404 page since March".

The check that costs nothing

Open the last report your monitoring produced and ask one question:

If every single source had been down, would this report look different?

If the answer is no, you do not have a monitoring system. You have a system that sends emails.

I build and maintain automation and monitoring systems - mostly Python and UiPath, against public data sources that were never designed to be read by machines.

Generating Excel reports on a server that has no Excel

tbalcewicz — Sat, 15 Aug 2026 17:08:46 +0000

Sooner or later someone asks for "just a spreadsheet, like the one we make by hand." You write it, it works on your laptop, you deploy it, and it dies - because the server has no Microsoft Office installed and never will.

This is one of those problems that looks trivial and has about six sharp edges. Here is the full set, in the order you will hit them.

Why the obvious approach fails

The tempting solution is COM automation - driving Excel through its object model:

import win32com.client
excel = win32com.client.Dispatch("Excel.Application")   # needs Excel installed

This is a trap even when Excel is installed. COM automation of Office is explicitly unsupported by Microsoft in unattended server contexts. It leaves zombie EXCEL.EXE processes, deadlocks when a modal dialog appears with nobody to click OK, and behaves differently depending on which user session it runs under.

The fix is not to install Office on the server. It is to write the file format directly.

Option 1: CSV, and the two details everyone gets wrong

If the recipient just wants to open it, sort it, and filter it, CSV is enough - and it has no dependencies at all. But naive CSV arrives broken for most European users:

import csv

def write_csv(rows, path, cols):
    with open(path, "w", newline="", encoding="utf-8-sig") as fh:
        w = csv.DictWriter(fh, fieldnames=cols, delimiter=";",
                           extrasaction="ignore")
        w.writeheader()
        w.writerows(rows)

Three things there are doing real work:

encoding="utf-8-sig" writes a byte-order mark. Excel does not sniff UTF-8; without the BOM it falls back to the system codepage and every accented character turns to mojibake. This is the single most common "your export is broken" bug report.

delimiter=";" matches the list separator on locales where the decimal separator is a comma - most of continental Europe. With a comma delimiter, the entire row lands in column A.

newline="" stops Python's universal newlines from doubling up \r\n on Windows, which otherwise gives you a blank row between every record.

If your audience is mixed-locale, you cannot satisfy both with one file. Either ship two, or move to a real workbook - which brings us to:

Option 2: a real .xlsx with openpyxl

When you need multiple sheets, formatting, or frozen headers, write the actual format:

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter

def write_xlsx(rows, cols, path, sheet_name="Report"):
    wb = Workbook()
    ws = wb.active
    ws.title = sheet_name

    header_font = Font(bold=True, color="FFFFFF")
    header_fill = PatternFill("solid", fgColor="12324F")

    ws.append(cols)
    for cell in ws[1]:
        cell.font = header_font
        cell.fill = header_fill

    for row in rows:
        ws.append([row.get(c, "") for c in cols])

    ws.freeze_panes = "A2"
    ws.auto_filter.ref = ws.dimensions

    wb.save(path)

No Excel required - openpyxl writes the XML that lives inside an .xlsx (it is a zip file of XML, which you can verify by renaming one to .zip).

Column widths, because auto-fit does not exist

There is no "autofit" in the file format - Excel computes it at render time, and openpyxl is not Excel. You approximate it:

def autosize(ws, cols, max_width=60):
    for i, col in enumerate(cols, start=1):
        longest = max(
            [len(str(col))] +
            [len(str(c.value)) for c in ws[get_column_letter(i)] if c.value]
        )
        ws.column_dimensions[get_column_letter(i)].width = min(longest + 2, max_width)

Cap the width. One long description field will otherwise produce a column three screens wide.

The gotchas that will actually bite you

Timezone-aware datetimes raise. The xlsx format has no timezone concept:

value = value.replace(tzinfo=None) if isinstance(value, datetime) else value

Strings starting with = become formulas. If your data contains user-supplied text, this is a CSV/formula injection risk as well as a rendering bug:

if isinstance(value, str) and value[:1] in ("=", "+", "-", "@"):    value = "'" + value

Illegal control characters raise, not warn. Scraped text loves to carry them:

import re
ILLEGAL = re.compile(r"[\000-\010\013\014\016-\037]")
value = ILLEGAL.sub("", value) if isinstance(value, str) else value

31 characters, and no []:*?/\ in sheet names. Exceed it and the file is silently corrupt in some readers.

Memory. openpyxl holds the whole workbook in RAM. Past ~100k rows, use write-only mode:

wb = Workbook(write_only=True)
ws = wb.create_sheet("Report")
for row in rows:
    ws.append([row.get(c, "") for c in cols])   # streams, never fully in memory

Option 3: when the config file is the real problem

A subtler version of the same trap: reading configuration from a spreadsheet. It feels convenient - non-technical colleagues can edit it - and it fails the same way, because reading .xlsx through Office automation needs Office.

Move configuration to JSON and keep the spreadsheet for output only:

{
  "recipients": ["ops@example.com"],  "min_score": 3,
  "send_email": true
}

Two notes from having got this wrong. First, be consistent about types - if a flag is the string "true" in one place and boolean true in another, someone will eventually write 1 and spend an afternoon on it. Pick one and document it. Second, fields your code does not actually read should be deleted, not left in. A config file with dead keys is worse than no config file: someone will change one, nothing will happen, and they will lose an hour before concluding the system is broken.

The rule underneath all of this

Never depend on a desktop application being present on a server. Not Excel, not Word, not Outlook, not a PDF printer driver. Every one of those has a library that writes the format directly, and the library version will run headless, in parallel, without a user session, inside a container, and at 3am when nobody can dismiss a dialog box.

The five minutes you save with Dispatch("Excel.Application") cost a day the first time it hangs in production - and it hangs at the worst possible moment, because the worst possible moment is exactly when nobody is logged in to click OK.

I build automation and reporting systems in Python and UiPath, mostly in places where the production environment is nothing like the developer's laptop.

Keyword search is the wrong tool for monitoring anything

tbalcewicz — Sat, 15 Aug 2026 17:04:17 +0000

There is a specific bug that shows up in almost every monitoring system I have seen, and it never announces itself. The system runs fine. It sends its reports on time. Nobody gets an error. And the whole time, it is quietly missing most of what it was built to find.

The bug is keyword search.

How it starts

You are asked to watch some external source for things that matter to your company. Job postings, public tenders, competitor announcements, regulatory filings, whatever. The source has an API with a q= parameter. So you do the obvious thing:

results = api.search(q="ERP", limit=50)

You run it. Fifty results come back. They look reasonable. You ship it.

Here is what actually happened. Your query matched sixteen thousand records. The API returned the first fifty, ordered by whatever the API felt like ordering by. The other 15,950 were silently discarded - not by you, by the pagination default you never looked at.

And critically: there is no error. No exception, no warning, no truncation flag in the response. The system reports success. You will not find out from your logs. You will find out six months later when someone asks why you missed the biggest opportunity of the quarter, and the honest answer is that you never saw it.

Why raising the limit does not fix it

The first instinct is to page through everything. Sometimes you can. Usually you discover one of these:

the API caps total pagination depth (first 1,000 results, then nothing)
rate limits make a full sweep take hours
the result set changes between pages, so you get duplicates and gaps
16,000 records is mostly noise anyway, and now you are paying to process it

You can fight this. I have. You end up with retry logic, cursor management, deduplication across pages, and a job that takes forty minutes and still cannot promise completeness.

The real problem is upstream: you are asking the wrong question. "Which records contain this string" is not the question. The question is "which records belong to the category I care about" - and on most serious data sources, somebody has already answered it.

Classification already exists, and it is free

Structured sources almost always carry an official classification, assigned at publication time by the publisher, because some regulation or process required it.

EU public procurement notices carry CPV codes - a controlled vocabulary the buyer must select from when publishing
Company filings carry industry classification codes
Scientific papers carry subject categories
Product feeds carry taxonomy IDs
Job boards carry role categories

These fields exist specifically so that machines can filter. They are usually indexed, usually exact-match, and - this is the important part - the result set is bounded. Filtering "category = IT services, published in the last 7 days" returns maybe 900 records. You can take all of them. There is no page two you are quietly dropping.

The switch looks like this:

# before: unbounded, silently truncated, unknowable loss
results = api.search(q="ERP", limit=50)

# after: bounded, complete, and you can prove it
results = api.search(
    query="classification-cpv IN (72000000, 48000000) AND publication-date >= today(-7)",
    limit=100,
)

You do not have to take my word for any of this - the TED API is public and needs no key, so you can measure it yourself in about five minutes. Run a full-text query for a common term like ERP, then compare the totalNoticeCount the API reports against how many records it will actually hand you. That gap is the part you were never seeing. Then run the category version and watch the total drop to something you can fetch completely.

Precision improves, which is nice. But the real prize is the thing that does not show up as a percentage: the stream becomes complete. Before, you cannot say what you are missing. After, you can.

The obvious objection

"Publishers misclassify things all the time."

They absolutely do. This is a real weakness and pretending otherwise would be dishonest. Someone publishing a major software procurement will occasionally file it under office supplies, and category filtering will sail straight past it.

The fix is not to go back to keyword search. It is to run a second, independent net:

primary   = fetch_by_category(CATEGORY_CODES)      # complete, bounded
secondary = fetch_by_product_names(["salesforce", "uipath", "databricks"])
results   = deduplicate(primary + secondary)

The second pass is narrow on purpose. It searches for proper nouns - product names, vendor names, specific systems. Proper nouns are rare enough that the result set stays small and you are not back to 16,000 hits. It exists purely to catch what the first net structurally cannot.

Two nets with different failure modes beat one net with unknown holes.

The general principle

The thing keyword search really costs you is not precision. It is knowing what you don't know.

A system that returns 50 of 16,000 matches and a system that returns 900 of 900 matches can look identical from the outside. Same shape of output, same runtime, same absence of errors. The difference only shows up in what never reached you, which is exactly the thing you cannot measure from the inside.

So when you build the next monitor, before you reach for q=, spend ten minutes reading the API docs for a classification field. Ask:

Is there an official taxonomy on this data? Usually yes. It is usually documented badly.
Is my filtered result set bounded? If you cannot fetch all of it, you have not solved the problem, you have moved it.
What does my system do when a source returns nothing? If the answer is "reports zero and exits successfully", you have a second silent failure waiting.

That last one deserves its own article, and it is the one that has bitten me hardest.

I build and maintain automation and monitoring systems - mostly Python and RPA, mostly against public data sources that were never designed to be read by machines.