How to Design Data Pipelines for Reliable Reporting Systems

For almost three months, every Mermaid diagram disappeared from this blog.

The build passed. The pages returned HTTP 200.

The checks confirmed that something had been published, while missing that part of the content had vanished.

The same weakness appeared in the reporting: a partial result looked complete enough to trust.

The situation

The pipeline behind this site produces two kinds of output: the pages readers see and the reports used to understand what is happening to those pages.

The publishing path starts with Markdown posts and turns them into static HTML. The reporting path combines the live sitemap, Search Console inspection results and search-performance data.

These outputs support different decisions. Published pages need to contain the material written for readers. Index reports need to show which URLs were inspected, when they were inspected and what Google reported about them.

The site has an hourly rebuild process for scheduled publishing and a daily SEO process. The replacement full index snapshot is designed for fortnightly checks.

At the 11 September checkpoint, the saved index report covered 93 sitemap URLs. This was a small dataset. Volume was not the problem.

Reliability needed to mean four things: expected content survived the build, inspection coverage was complete, dates represented the observations correctly, and failures could not quietly replace useful output with something worse.

Those requirements became clearer through incidents than they had been in the original implementation.

What I tried first

The early checks focused on whether the process ran and whether the output was reachable.

That catches some useful failures. A missing page or a crashed build deserves attention. However, a successful response says little about whether the page contains everything it should.

The original Search Console inspection script had a similar limitation. By default it selected up to 25 blog URLs by priority and saved its results under the newest performance-data directory.

That could support a focused investigation. It could not provide a complete historical record of the site.

The distinction became important when pages started dropping out of the index. A URL absent from a report might have been uninspected rather than unindexed. A directory date might describe a performance export rather than the inspection itself.

The lesson was to define the claim behind each check. “The page responds” and “all expected content survived” require different evidence.

What I built

The current setup separates generating output from deciding whether that output is acceptable.

For publishing, a new build goes into a staging directory. Verification checks that directory before it replaces the live files.

For index reporting, the live sitemap defines the expected URL set. Successful inspections are saved to a dated snapshot, then compared with that expected set.

The diagram shows the two paths implemented in the repository.

flowchart TD
    A[Markdown posts] --> B[Clear rendered-content cache]
    B --> C[Build HTML in staging]
    C --> D[Compare expected content with built output]
    D --> E{Verification passed?}
    E -->|Yes| F[Replace live build and retain previous build]
    E -->|No| G[Stop before replacing live files]

    H[Live sitemap] --> I[Extract expected URLs]
    I --> J[Inspect URLs through Search Console]
    J --> K[Save dated records]
    K --> L[Normalise paths and summarise results]
    L --> M{Every expected URL inspected?}
    M -->|Yes| N[Complete report]
    M -->|No| O[Incomplete report and non-zero exit]

Validation happens at the boundaries where information could disappear.

The publishing check compares Mermaid blocks in visible source posts with diagram containers in the built pages. It also checks expected pages and other output properties.

The reporting check compares expected URLs with saved inspection records. A completed request is useful progress, but the report cannot declare the current run complete while expected URLs remain missing.

Making a rerun safe

Inspection results are stored as one JSON object per line. When a run resumes, the parser reconstructs one record per URL path.

This is the actual helper used by the reporting script:

export function parseSnapshot(jsonl) {
  const byPath = new Map()
  for (const line of jsonl.split('\n')) {
    if (!line.trim()) continue
    const rec = JSON.parse(line)
    byPath.set(rec.path, rec)
  }
  return byPath
}

The latest record for a path replaces the earlier record in the parsed snapshot. Appending another observation therefore does not count the same URL twice in the summary.

A local check replayed the saved 11 September snapshot twice through this function. The resulting snapshot still contained 93 unique URLs.

That proves a specific property of the parser. It does not establish that concurrent writers, interrupted file writes or every possible malformed response are handled safely.

What broke

The diagrams disappeared while the build stayed successful

From 25 May to 20 August, every Mermaid diagram was stripped from the built blog.

The cause was an interaction between two processing stages. The Mermaid plugin emitted a raw HTML node, and the sanitisation stage removed that node.

The browser-side renderer then found no diagram containers and returned quietly.

Each stage could finish without a reported failure. Together, they produced an incomplete page.

There was another complication: the content layer cached rendered HTML for individual posts. Fixing the Markdown processing code did not necessarily regenerate unchanged posts.

The source could look correct, and the plugin could be fixed, while an old rendered result remained in use.

This is why checking only the edited file or the plugin in isolation was insufficient. The verification needed to inspect the built artefact that readers would actually receive.

The index checks could not see the missing history

The index investigation exposed a separate gap.

The older script inspected no more than 25 blog URLs by default and filed results beneath whichever performance directory was newest. The documented diagnosis identified a roughly two-month blind spot between 17 May and 13 July.

That was exactly the period in which the historical record was needed to distinguish competing explanations.

The problem was not simply a missing report. There were reports, but their coverage and dating did not support the conclusions someone might draw from them.

Re-running the old script could not recover observations that had never been collected.

The aggregate numbers suggested the wrong story

Search impressions introduced a different kind of uncertainty.

The investigation attributed an August spike to automated activity. Its evidence included a reported slice with 435 desktop impressions out of 445, low average search positions, zero clicks and concentrated patterns across countries.

Part of the earlier apparent decline also reflected the disappearance of automated activity. However, the notes found evidence of genuine index loss as well.

It would have been wrong to label the entire decline artificial.

The response was to inspect device, country and date breakdowns before interpreting the aggregate trend. Even that had limits: the documented page-by-country queries returned too little data to reconstruct every individual page’s history.

Segmentation improved the diagnosis. It did not create information the source withheld.

A failed build affected the live site

On 21 August, a build failed part-way through on the server. For 29 minutes the homepage returned a 404, while the blog continued to serve.

The build process cleared its output directory before writing the replacement. The web server served that directory directly.

Once the build failed part-way through, the live output was incomplete.

A build failure had become a publishing failure because the existing site and the work in progress shared the same destination.

What I changed afterwards

Compare the output with an explicit expectation

The diagram check now counts expected Mermaid blocks from source posts and compares them with containers in each built page.

A mismatch fails verification. Passing counts are printed too, making the successful path visible.

The verification run on 11 September reported 105/105 diagrams. That is a checkpoint, not a permanent target. The expected count should change when the visible source content changes.

The broader rule is to check data quality at every boundary, especially where a transformation can silently discard valid input.

Make incomplete reporting visibly incomplete

The replacement inspection script works from all sitemap URLs, records inspection timestamps and stores the snapshot under its own date.

Its current-run exit codes distinguish complete, incomplete and fatal outcomes. An incomplete run lists the missing URLs and can resume.

The saved report for 11 September includes this actual line:

**COMPLETE**: 93 inspected of 93 sitemap URLs.

A local check deliberately removed one URL from the parsed snapshot. The coverage helper identified one missing URL.

There is still a limitation in the implementation: the historical comparison selector currently accepts an earlier snapshot with up to 5% of today’s expected URLs missing. That is weaker than the strict completeness rule applied to the current run.

The reporting language should acknowledge that tolerance rather than imply that every historical comparison uses an identical, complete population.

Build separately before replacing live files

The deployment and hourly rebuild paths now use the staging build script.

It clears the rendered-content cache, builds into staging, runs verification and checks that the homepage exists before replacing the live directory. The previous build is retained for rollback.

This protects the serving files from build and verification failures occurring before the swap.

The implementation uses two directory renames. It should not be described as a fully atomic, zero-gap release mechanism. The important improvement is that the multi-minute build no longer happens directly over the live output.

This is a concrete form of failure recovery: retain a usable result while preparing and checking its replacement.

The result

The evidence shows better-defined checks and a complete saved observation. It does not yet establish a long-term incident reduction.

The four snapshot categories total 93. Replaying its records did not inflate that number.

Those checks establish internal consistency and the parser’s handling of repeated paths. They do not prove that Google’s observations are instantaneous or that every reported state is causally explained.

Likewise, the diagram incident does not establish why pages left the index. The repository’s diagnosis explicitly rejects that simple attribution because the timing and page-level evidence do not support it.

The available evidence also does not show a measured average detection time after the changes or a verified period of zero production incidents.

The stronger claim is narrower: specific omissions that previously looked successful now have explicit checks.

When not to do this

A weekly report read by one person may not need a separate database, orchestration service and alerting platform.

Start with the decision the report supports and the cost of a wrong answer. For a small batch reporting workflow, a clear script, dated output and a few meaningful checks may be sufficient.

A managed connector or BI refresh can be a better choice when it already handles source access, refresh history and failure notifications in a system the team understands.

Custom processing becomes useful when the required checks depend on your own content, expected population or publication rules.

Even then, keep the implementation proportionate. This site needed to know whether expected diagrams survived and whether every sitemap URL was inspected. Neither question required a large data platform.

For source preparation, How to Clean Messy Excel Data Using Python addresses a different part of the workflow. For presenting the result, see How to Build a Data Dashboard Without Manual Excel Work.

If you are doing this yourself

1. Define completeness. List the records, pages or sections the output must contain.
2. Check the delivered artefact. Inspect what the reader receives, not just whether processing finished.
3. Keep dates meaningful. Distinguish collection time, reporting period and publication time.
4. Test repeated input. Confirm that a resumed run does not inflate the reported population.
5. Separate progress from completion. Retain partial work, but label and signal it clearly.
6. Preserve the last usable output. Build and verify replacements before exposing them.
7. State what remains unknown. Missing history and incomplete source dimensions belong in the interpretation.

The changes here came from examining what each successful check actually proved. A reachable page did not prove complete content, and a saved inspection file did not prove complete coverage. Data & dashboard services include designing reporting around those distinctions. Get in touch if your reports finish successfully but still leave you unsure whether the result can be trusted.