Level UpPharma series

The reconciliation problem hiding behind your dashboard: why a chart that renders proves nothing about completeness.

A dashboard can be missing records and still look healthy, because it shows what arrived and has no way of knowing what was supposed to. Here is what reconciliation adds, when a dashboard doesn't need it, and where the checking itself can fail.

  • Article
  • Pharma
  • Manufacturing

November 20267 min read

The reconciliation problem hiding behind your dashboard: why a chart that renders proves nothing about completeness.

Production, sales and quality figures get pushed into central reporting and search platforms so leadership and quality teams have one place to look. That dashboard can be missing records and still look healthy. A nested structure gets flattened on the way into the index. One slice of a load fails and the pipeline carries on. A field arrives in a format the index doesn't expect and is left out. The chart renders in every one of these cases, because a dashboard shows what arrived and has no way of knowing what was supposed to.

For a pharma or regulated manufacturing audience, that matters more than it first appears. Data integrity, in the regulators' own definitions, starts with completeness. MHRA describes data integrity as the degree to which data are complete, consistent and accurate throughout their life cycle, and lists complete among the ALCOA+ attributes (8). FDA's data integrity guidance opens with the same three words (12). If a dashboard feeds a GMP decision, such as the trending behind a product quality review (10), whether it is complete stops being a reporting question.

Why a healthy-looking dashboard can still be wrong

None of this comes from a broken product. Search and analytics engines are built for fast queries over denormalised documents, and they document their behaviour clearly. Elasticsearch's own documentation explains that it has no concept of inner objects, so arrays of objects are flattened by default and the association between fields inside each object is lost; its remedy is the nested field type, which comes with limits of its own (1). OpenSearch documents the same behaviour (7). The same documentation advises against using its join field like a relational join and recommends denormalising data for performance (2).

Failures are reported too, to whoever checks. A bulk load can succeed as a request while individual items within it fail, each with its own status and error (3). A field in the wrong format rejects the whole document by default, but with the ignore_malformed setting the document is indexed without that field, and the default template for log data streams turns that setting on (4). Logstash only keeps rejected events if its dead letter queue is enabled, and it is disabled by default (5). Versions 9.0 and later can route failed documents to a separate failure store and return a successful response with a flag, so the document is safe but not in the index the dashboard reads (6).

Every one of those behaviours is documented and configurable. The gap opens when the integration feeding the engine doesn't check what it gets back.

The case for leaving the dashboard alone

Three objections deserve a fair hearing.

The first is that dashboards show trends, and the source system remains the record. A handful of missing rows won't move a monthly trend, and nobody releases a batch from a chart. For a directional leadership view, that is right, and reconciliation may cost more than it is worth.

The second is that the platform already tells you when something fails: per-item bulk errors, dead letter queues, failure stores and the record of ignored fields all exist for exactly this (3, 4, 5, 6). Configure them properly and the problem is handled.

The third is that a validated pipeline can be trusted. Regulators already expect interfaces to be assessed during validation for correct and complete transfer (9), and data transfer to be validated (8). Validate it once and move on.

All three hold, up to a point. The failure modes above aren't random. A format change or mapping conflict hits every record with that shape from that source, so the loss follows a pattern, one site, one product, one record type, and a systematic gap biases a trend in a way a few random gaps would not. The platform's failure signals are real, but they report to whoever is listening, and several are off by default or report success with a flag. And validation proves a design at a point in time, while sources change after it: a new field, a new site, a new release of the ERP. Google's SRE guidance makes the same point about data pipelines, warning that measuring correctness only stage by stage can miss end-to-end corruption (13). Our position is that once a dashboard informs a GMP decision, or its gaps could follow a pattern, each run needs to prove it is complete.

Verify the reporting layer as a destination

The design we propose treats the index as a destination to verify. Every record carries an identifier that follows it from source to chart, using standard trace context so it can be correlated across services (14). A reconciliation step counts what each source sent against what the index accepted, by site, product and time window, and raises an alert when the two disagree. Where the engine can't hold the shape of the data, a deliberate synchronisation step reshapes it beforehand, into nested documents or denormalised records as the engine's own guidance suggests (1, 2), and checks the result, so the translation is a design decision with its own checks. This is our approach; no regulation prescribes a reconciliation job, though it lines up with the expectation of built-in checks on data exchanged between systems (11) and with the data flow mapping PIC/S recommends for interfaced systems (9).

  1. SourceCount sent.
  2. SyncReshape, check.
  3. IndexCount accepted.
  4. ReconcileCompare, alert.

What this looks like in practice

Take an illustrative case. A quality dashboard trends deviations by site and month. One site upgrades its quality system, and the date field starts arriving in a new format. The index has ignore_malformed switched on, so each record is stored without its date. Every query filtered by date now skips that site's records, and the chart shows its deviations falling to zero. The site looks like it improved overnight.

With reconciliation, the count of deviations sent by that site for the month no longer matches the count the index can return for the same window, and the alert fires the next morning. The ignored-field record shows exactly which field was dropped (4). The fix is a format change in the synchronisation step, made before anyone presents the trend.

Where it actually breaks

Engines built for flat data are where this gets hard. Arrays, joins and one-to-many relationships are the shapes that need the most deliberate handling, and each remedy has a price: nested documents have default limits on how many nested fields and objects a document can carry, and joins add a significant tax to query performance, in the engine's own words (1, 2). Denormalising duplicates data, which then has to be kept consistent. In our experience, a small synchronisation job whose only purpose is keeping the two ends in agreement is the least glamorous and most useful part of the build.

Our own design has failure modes too. Counts can match while content is wrong, because a document stored without one field still counts as one document, so the reconciliation should also check the record of ignored fields and compare a sample of known records end to end, the golden-data approach the SRE workbook describes (13). The reconciler can fail silently itself, so it needs its own heartbeat and an alert when it doesn't run. Data that arrives late will trigger false alarms unless the comparison runs over windows with an agreed grace period, and too many false alarms teach people to ignore the real one. And if the dashboard is in GxP scope, the reconciliation job is part of the validated system, with the documentation that implies.

Point of view

A chart that renders proves the query ran. Count what left each source against what reached the index, and alert when they disagree, so missing records turn up in your monitoring and not in front of an auditor.

What this looks like when it is done right

We have built reconciliation into reporting pipelines behind dashboards that leadership and quality teams rely on (15). What made the difference was a small job nobody sees, counting both ends and raising an alert the moment they disagree, built on the same structured logging and error handling we use in every integration. We integrate with the reporting platform and the source systems; we don't replace either.

If your dashboard has never been reconciled against its own sources, that is worth checking before an auditor checks it for you.

Talk to an integration expert

This article is general information about integration design. It is not legal or regulatory advice, and validation, data integrity and compliance decisions rest with each company's quality unit and its regulators. Product behaviour is described from vendor documentation current at the time of writing and varies by version and configuration.

Sources

  1. Elastic documentation, Nested field type: no concept of inner objects; object hierarchies flattened and the association between fields lost; use the nested type instead; default limits index.mapping.nested_fields.limit 100 and nested_objects.limit 10,000. elastic.co
  2. Elastic documentation, Join field type: should not be used like joins in a relational database; denormalising data is the key to good performance; join queries add a significant tax to query performance. elastic.co
  3. Elastic documentation, Bulk API (8.19): the errors flag is true if one or more operations did not complete; individual operations succeed or fail independently, each with its own status and error object. elastic.co
  4. Elastic documentation, ignore_malformed: by default a wrong data type rejects the whole document; with ignore_malformed the field is not indexed and the rest is processed; the _ignored field records ignored fields; the logs index template sets ignore_malformed to true for logs data streams. elastic.co
  5. Elastic documentation, Logstash dead letter queues: used for documents with 400 or 404 responses; disabled by default; without one, unsuccessful events are dropped or the pipeline hangs. elastic.co
  6. Elastic documentation, Failure store (9.0 and later): failed documents stored in separate indices within a data stream; clients receive a successful response flagged as redirected; some failure types are not captured. elastic.co
  7. OpenSearch documentation, Nested field type (2.16): arrays of objects stored in flattened form and the relation between fields lost; nested type as the remedy. opensearch.org
  8. MHRA GxP Data Integrity Guidance and Definitions (March 2018): 6.4 data integrity defined as data complete, consistent, accurate throughout the life cycle; 6.1 and 3.10 complete as an ALCOA+ attribute; 6.8 data transfer validated, with an audit trail; 6.15 summary reports are limited. gov.uk
  9. PIC/S PI 041-1 (in force 1 July 2021): 9.1.6 data flow maps for interfaced systems; 9.4 interfaces assessed during validation for correct and complete transfer; 7.8 no sole reliance on summary report data; 5.6.2 review of reported data against raw entries. picscheme.org
  10. EudraLex Volume 4, EU GMP Chapter 1, 1.10 product quality reviews to highlight trends; US 21 CFR 211.180(e) records maintained so data can be used for at least annual evaluation of product quality. health.ec.europa.eu, ecfr.gov
  11. EudraLex Volume 4, Annex 11 Computerised Systems (2011): section 5 built-in checks for data exchanged between systems; 4.8 data not altered in value or meaning during migration. health.ec.europa.eu
  12. FDA, Data Integrity and Compliance With Drug CGMP, Questions and Answers (December 2018), Q1: data integrity refers to the completeness, consistency and accuracy of data. fda.gov
  13. Google, Site Reliability Engineering, Monitoring Distributed Systems (black-box and white-box monitoring); The Site Reliability Workbook, chapter 13 Data Processing Pipelines (freshness and correctness objectives; measuring correctness only per stage can miss end-to-end corruption; golden data). sre.google
  14. W3C Trace Context Level 1 (Recommendation, 23 November 2021); OpenTelemetry, Context propagation (correlating signals across services). w3.org, opentelemetry.io
  15. Ampleshift delivery record: reconciliation, structured logging and error handling in reporting pipelines. Client names withheld.
  • Pharma
  • Manufacturing
  • Data Integrity
  • Reconciliation
  • Observability

Want us in your next story?

Connect with us today and let's turn your integration challenges into opportunities for growth.

Book a call