A useful logging design starts with a question precise enough to become a query. “Show errors” is different from “How many checkout requests finally failed between ten and eleven UTC?” The first locates diagnostic events. The second needs a population of completed requests, a definition of failure, and a time rule. Collecting more text does not supply these missing semantics automatically.

For a request-outcome question, define a terminal event emitted once per logical request. Keep separate attempt events when retries matter, and give events stable identities so delivery duplicates can be recognized. A request identifier joins its events; an event identifier distinguishes one event from another. Reusing the request identifier as every event’s deduplication key would discard legitimate attempts and the final outcome together.

Make the query contract visible

Choose whether the window uses event occurrence time or arrival time. These differ when a collector is delayed. A half-open interval includes the start and excludes the end, allowing adjacent windows to meet without double-counting their boundary. Normalize timestamps to an unambiguous time basis before comparing them. A local clock string without its offset is insufficient evidence for a UTC incident window.

Define which terminal outcomes count as failures. Server errors, client cancellations, and rejected input need not belong to the same reliability measure. Also state whether every relevant request emits a terminal event and whether the collection path can lose records. A query can be perfectly correct for stored events while giving an incomplete account of the service. Retention and selection policies belong beside the interpretation, not just in collector configuration.

When it breaks

A request that fails an attempt and then succeeds produces a real error event without being a failed completed request. Counting every error line inflates the final failure count. Delivery duplication can inflate it again. Deduplication must use an immutable event identity; two different payloads claiming the same identity are a data-integrity problem, not evidence that either can safely be ignored.

An empty window presents a different trap. Zero recorded failures divided by zero recorded requests is not a measured zero-percent failure rate. Preserve the absence of a denominator. Otherwise a broken collector can make a reliability chart appear healthy. When an incident requires one request’s explanation, retrieve its ordered events and dependency details after establishing the aggregate; a count alone cannot explain which retry recovered or why another request failed.

Check it quickly

Save this script as log-questions.py and run it with Python. It was executed on Python 3.14.4 and uses the standard SQLite module with an in-memory database. The eight supplied delivery records contain one exact duplicate. Event times are constructed around a fixed UTC hour, including one event just before the window and one exactly at its end. No live collector or external database is involved.

import json
import sqlite3
from datetime import datetime


def timestamp(value):
    parsed = datetime.fromisoformat(value)
    assert parsed.tzinfo is not None
    return int(parsed.timestamp())


start = timestamp("2026-09-01T10:00:00+00:00")
end = timestamp("2026-09-01T11:00:00+00:00")
rows = [
    ("e0", "r0", "request.completed", start - 1, 503),
    ("e1", "r1", "attempt.failed", start + 600, 503),
    ("e2", "r1", "request.completed", start + 660, 200),
    ("e3", "r2", "attempt.failed", start + 900, 503),
    ("e4", "r2", "request.completed", start + 960, 503),
    ("e4", "r2", "request.completed", start + 960, 503),
    ("e5", "r3", "request.completed", start + 1080, 200),
    ("e6", "r4", "request.completed", end, 503),
]
db = sqlite3.connect(":memory:")
db.execute("""CREATE TABLE events (
    event_id TEXT PRIMARY KEY, request_id TEXT,
    event_type TEXT, occurred_at INTEGER, status INTEGER)""")
db.executemany("""INSERT INTO events VALUES (?, ?, ?, ?, ?)
    ON CONFLICT(event_id) DO NOTHING""", rows)
stored = db.execute("SELECT count(*) FROM events").fetchone()[0]
assert stored == 7
error_events = db.execute("""SELECT count(*) FROM events
    WHERE occurred_at >= ? AND occurred_at < ? AND status >= 500""",
    (start, end)).fetchone()[0]
query = """SELECT count(*), coalesce(sum(status >= 500), 0)
    FROM events WHERE event_type = 'request.completed'
    AND occurred_at >= ? AND occurred_at < ?"""
total, failed = db.execute(query, (start, end)).fetchone()
completed = db.execute("""SELECT request_id, status FROM events
    WHERE event_type = 'request.completed' AND occurred_at >= ? AND occurred_at < ?
    ORDER BY request_id""", (start, end)).fetchall()
assert completed == [("r1", 200), ("r2", 503), ("r3", 200)]
assert (error_events, total, failed) == (3, 3, 1)
empty_total, empty_failed = db.execute(query, (end + 1, end + 3600)).fetchone()
empty_rate = empty_failed / empty_total if empty_total else None
assert (empty_total, empty_failed, empty_rate) == (0, 0, None)
print(json.dumps({"received_events": len(rows), "deduplicated_events": stored,
                  "error_events_in_window": error_events, "completed_requests": total,
                  "failed_requests": failed, "terminal_failure_rate": failed / total,
                  "empty_window_rate": empty_rate}, indent=2))
db.close()

Seven unique events remain after deduplication. Within the window, the broad status query finds three error events: a failed attempt for request one, a failed attempt for request two, and request two’s failed completion. The terminal query instead finds three completed requests, of which one failed. Request one recovered successfully, request three succeeded immediately, and the boundary events are excluded. The final failure fraction is therefore one third.

The second query targets an empty later interval. Its count is zero, its failure sum is normalized to zero, and the derived rate remains null because there is no denominator. These assertions establish the intended query semantics for complete, controlled records. For production use, verify event emission and delivery coverage, inspect conflicting identities, and compare terminal totals with independent request counters. The useful outcome is a question another operator can reproduce with the same definitions, rather than an unexplained number attached to a large pile of logs.

Further reading

Sources