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.

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).
- SourceCount sent.
- SyncReshape, check.
- IndexCount accepted.
- 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 expertThis 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
- 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
- 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
- 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
- Elastic documentation,
ignore_malformed: by default a wrong data type rejects the whole document; withignore_malformedthe field is not indexed and the rest is processed; the_ignoredfield records ignored fields; the logs index template setsignore_malformedto true for logs data streams. elastic.co - 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- W3C Trace Context Level 1 (Recommendation, 23 November 2021); OpenTelemetry, Context propagation (correlating signals across services). w3.org, opentelemetry.io
- Ampleshift delivery record: reconciliation, structured logging and error handling in reporting pipelines. Client names withheld.
Continue reading
Up next.
More perspectives from the Ampleshift dispatch.
- ArticlePharmaNovember 20267 min read
You Cannot Verify What Your Systems Cannot Trace: Why Serialisation Is a Data Integration Problem.
The EU and US rules both give every pack its own identity, but they check it in very different ways, and neither hands you one place where the evidence already lives. Here is what the pipeline underneath has to do, where buying a platform is the right answer, and where the work still sits with you.
- ArticlePharmaNovember 20267 min read
Where Your Training Record Goes When the Audit Asks for It: Build the Proof Before the Question Arrives.
When three systems hold the pieces of a qualification record and nothing joins them, the proof gets assembled by hand while the inspector waits. Here is how to make the record exist before the question, why the case for doing it by hand is stronger than it looks, and where the real engineering effort goes.
- ArticlePharmaOctober 20269 min read
Every Production Line Handles Quality Its Own Way: Build One Route from Shop Floor to Quality System.
Pharma lines tend to wire their own private bridge into the quality system, so the same kind of deviation arrives shaped differently depending on where it happened. Here is what one shared route looks like, why the case for leaving each line alone is stronger than it sounds, and why the hard part is rarely the build.
Want us in your next story?
Connect with us today and let's turn your integration challenges into opportunities for growth.
Book a call