EHR data extraction for analytics

Transport is the easy half

Almost every EHR extraction project is scoped as a pipe problem: connect to the source, land the tables, build the dashboard. Then the first analysis comes back and the same lab test appears under eleven different names, a third of the numeric results are not numbers, the encounter counts disagree with the report the chief operating officer looks at every Monday, and a patient's blood pressure appears to have been taken three days after the visit ended. None of that is a transport failure. All of it is the actual project.

So it is worth being efficient about the transport decision and spending the saved effort on meaning. Below is the honest comparison of the five routes, followed by the four problems that will consume most of the schedule regardless of which route you took.

The five routes out

The vendor's reporting database. Most major records systems ship a relational reporting environment alongside the transactional one, refreshed nightly, with a wide and largely complete picture of what is in the chart. When you can get access, it is the richest source available and the one that answers the widest range of questions. The costs are real: thousands of tables with names that assume institutional knowledge, documentation that is licensed rather than public, refresh timing that surprises people, and an access process that runs through the customer's own analytics team, who are busy. It is also the route least available to an outside product company, because it typically means credentials into the customer's environment.

Standardized clinical APIs. Modern certified systems expose resource-oriented interfaces with defined profiles for the common clinical concepts. These are excellent for the workflow they were designed for: a launched application asking about the patient currently on the screen. They are less suited to analytics, because they are patient-scoped, paginated, rate-limited, and frequently do not expose the specific field an analysis needs. Building a population extract by iterating a per-patient API across a hundred thousand people is a pattern that works in a demonstration and fails in production, and the organization's technical team will tell you so.

Bulk export. The population-scale sibling of the above: ask for a defined group, get back newline-delimited resource files. When it is available and enabled, this is the right answer for most analytics use cases and it is the direction the ecosystem is moving. Verify three things before planning around it: that it is turned on for this customer, that the group you need can actually be defined, and that the resources and elements you require are included — because coverage varies and the gap between what the standard permits and what a given deployment emits is where projects lose a month.

Interface message feeds. The messaging standard that has run hospital interfaces for decades: admissions and demographics, orders, results, scheduling events, charges. Nearly every organization already has this infrastructure and can add a subscriber more easily than they can grant database access. The data arrives as events in near real time, which is a genuine advantage for anything operational. The costs are that message content varies by site far more than the standard suggests, that you receive a stream rather than a state and must build the state yourself, and that backfilling history usually is not possible from the feed.

Document exchange. Structured clinical documents, produced for transitions of care and available almost everywhere. Useful when the alternative is nothing, particularly for data from outside the organization. Sections vary, narrative and coded content mix, and parsing a large corpus of them into consistent analytic rows is more work than it looks. This is a good supplement and a poor foundation.

A sixth exists and should not be used. Automating a human's session against the clinical interface to scrape screens is fragile, usually violates the terms the organization operates under, and puts credentials somewhere they should not be. If someone proposes it, the real problem is that a legitimate route was declined and the conversation should go back there.

Route	Best for	What it costs you	Availability to an outside party
Reporting database	Broad retrospective analytics, anything unusual	Access process, table sprawl, licensed documentation	Low — usually requires being inside the customer
Standardized clinical API	Patient-in-context applications	Patient-scoped, rate-limited, field coverage gaps	Good, and increasingly standard
Bulk export	Population analytics, cohort refreshes	Must be enabled; group definition; coverage varies	Moderate to good, improving
Interface feed	Operational and near real time	Site-specific content; you build the state; no history	Good — often the easiest thing to grant
Document exchange	Outside records; filling gaps	Parsing effort; inconsistent sections	Good, and the least consistent
Screen automation	—	—	Do not do this

One legal note worth having straight. Federal rules discourage practices that unreasonably interfere with the exchange of electronic health information, and they have changed the tone of these conversations for the better. They do not entitle a vendor to a bulk copy of a customer's database on the terms that vendor prefers. Do not walk into a negotiation waving them at people; walk in with a narrow, specific request, a clear purpose, and the smallest data set that answers it. That works far more often.

The codes are local, and the mapping table is the deliverable

Standard vocabularies exist and they are good: laboratory observations, medications, clinical findings, billing diagnoses and procedures each have a widely used coding system. What arrives from a real deployment is a mixture of those standard codes and a much larger population of local codes that the organization created over twenty years, with local descriptions written by whoever set up the order.

The consequences are concrete. The same test carries several local codes because it was configured separately for the main lab, the outpatient clinic and the point-of-care device. Local descriptions are inconsistent in spelling, abbreviation and case. Codes are retired and replaced but historical rows keep the old ones. And a mapping to a standard vocabulary, where it exists at all, may cover the common codes and stop.

The work is unglamorous and it is the difference between a usable dataset and a misleading one. Build a mapping table as a first-class, reviewed, versioned artifact: local code, local description, source system, standard code, mapping confidence, who decided, when. Sort by volume and map from the top down, because in almost every corpus a small number of codes account for the large majority of rows and the tail is genuinely low-value. Have a clinician or a laboratory contact review the top of the list. Then keep the table under version control and treat a change to it as a change to every analysis built on it.

Language models help here and should be used carefully. Proposing candidate mappings and clustering near-duplicate local descriptions is a good fit and saves real time. Deciding a mapping without review is not, because a wrong mapping is invisible downstream — the numbers all look plausible, and nothing ever throws an error.

The four timestamps, and the one you probably used

Nearly every clinical fact carries several times, and they can differ by hours or days. For a laboratory result: when the specimen was collected, when the analyzer produced the result, when it was filed into the record, and when the row was last updated. For a medication: ordered, dispensed, administered, documented. For an encounter: scheduled, arrived, roomed, closed, and the date the billing was finalized.

Pipelines default to whatever field is most conveniently populated, which is often the last-updated column, because it makes incremental loading easy. That single choice produces analyses in which results appear to occur after the visit they belong to, trend lines that move when nothing clinical changed, and cohort definitions that silently include or exclude people based on when a clerk touched a record.

Decide deliberately, per fact type, and write it down where an analyst will see it. Clinical questions almost always want the clinical time — collected, administered, arrived. Operational and incremental-loading logic wants the system time. Carry both. Then check time zones and daylight saving, because clinical systems store these inconsistently and a one-hour shift is invisible until it moves an event across midnight and changes a day-level count.

Related and equally common: rows change. A corrected laboratory result overwrites the original. A diagnosis is removed from an encounter after coding review. A note is amended. If you snapshot nightly and never reconcile, your copy drifts away from the source in a way nobody notices until an audit compares them. Decide whether you need the current truth or the history of what was believed when, and build accordingly — the second is more work and is the right answer for anything that will be used to explain a past decision.

Numbers that are not numbers

A laboratory result column is not a numeric column. In any real corpus a meaningful share of the values are text: a censored value below a detection limit, a qualitative result, a comment directing the reader to a report, a cancelled specimen, an unparseable free-text entry. Casting the column to a numeric type silently discards all of them, and the discarded rows are not random — censored values cluster at the clinically interesting end of the distribution.

Handle it explicitly. Keep the raw value as text. Add a parsed numeric column, a comparator column for the censoring symbol, a units column, and a flag for what could not be parsed. Report the unparsed fraction per test. If it is large for a test you care about, look at the actual strings before deciding what to do, because the pattern is usually one or two specific formats rather than chaos.

Units deserve their own attention. The same analyte is reported in different units by different instruments and different laboratories, and mixing them produces a distribution that looks like a real biological finding. Convert with a reviewed conversion table keyed on test and unit, never with an inferred rule, and never silently. Reference ranges are also instrument-specific and change over time, so an analysis based on flags rather than values inherits every change the laboratory ever made.

Reconcile before you ship anything

The single most useful engineering practice in this domain: before an extract is used for anything, reconcile its counts against a report the operational team already trusts.

Pick the monthly volume report the department reviews, or the encounter count finance uses, or the census the clinical team looks at. Compute the same number from your extract for the same period. They will differ. The differences are informative: a category of encounter you included and they exclude, telehealth counted differently, a location that closed, cancelled visits, a definition of a visit that turns on billing rather than arrival.

Then document each difference and its cause. That document is the most valuable artifact the project will produce, because it is what lets a skeptical stakeholder accept a number that does not match the one they know. Shipping an extract that disagrees with the operational report without an explanation is the fastest way to lose an analytics program, and it is entirely avoidable.

Keep the reconciliation running as a test, not as a one-time exercise. When it breaks, something upstream changed — a new location, a configuration change, a code retired — and you would rather learn that from a failing check than from a stakeholder.

Common data models, and when they are worth it

There are established common data models for clinical research and analytics, and they are genuinely useful. They give you a defined schema, an ecosystem of analytic tooling, and the ability to run the same analysis across organizations without rewriting it.

They also cost real effort, because conforming to one means making every mapping and every semantic decision explicitly rather than deferring them. That cost is worth paying when you will run many analyses over the same data, when you need to run identical logic at several organizations, or when you are participating in a network that expects it. It is not worth paying when you have one question, one site, and a deadline. In that case a well-documented purpose-built table with the mapping and timestamp decisions written down is the honest answer, and pretending otherwise wastes months.

Whichever you choose, keep the raw extract. Storage is cheap and every model decision is a lossy transformation you will want to revisit.

Governance is a design input, not a step at the end

Three decisions shape the architecture and should be made in week one rather than discovered in month three.

What you are allowed to hold, and how much of it. The principle of using the minimum data necessary is not only a legal notion; it is good engineering, because every additional identifier expands your obligations, your review process and your breach exposure. Start from the analysis and work backwards to the fields it needs.

Whether you need identified data at all. There are recognized routes to producing a data set that is no longer treated as identifiable, and they have specific requirements — one is a defined list of identifier categories to remove, with particular handling for dates and for very high ages, and one depends on a qualified expert's determination. If your analysis works on de-identified data, that path is usually far faster than the alternative, and you should ask the question before designing anything.

Where it lives and who can reach it. Environment separation, access logging, retention and deletion should be decided with the extraction design because they constrain it. Retrofitting an environment boundary onto a pipeline that already runs is one of the more expensive kinds of rework, and it always happens at the worst time, usually during a customer's security review.

Where you do not need us

If you need one number once, ask the person who already has access to the reporting environment to run a query. Building a pipeline to answer a single question is a common and expensive mistake, and the analyst who knows the schema will be finished before your access request is approved.

If a certified reporting engine already produces the measures you are graded on, do not rebuild them. Extract for the operational and product work where you need something the engine will not give you, and reconcile to it rather than replacing it.

What we see go wrong

• Scoping the project as transport and discovering the semantic work in month three
• Casting result values to numeric and silently dropping the censored and qualitative ones
• Joining on the last-updated timestamp because it made incremental loading convenient
• Mixing units and reporting the result as a clinical finding
• Mapping local codes without review, so a wrong mapping never surfaces as an error
• Shipping an extract that disagrees with the operational report with no explanation of why
• Iterating a patient-scoped API across a population and calling it a pipeline
• Deciding governance last, then rebuilding the environment during a security review

What good looks like

• The transport decision made in a week, with the reason written down
• A versioned local-to-standard mapping table with reviewer and confidence on every row
• Mapping coverage reported by row volume, not by distinct code count
• An explicit timestamp choice per fact type, documented where analysts read it
• Raw value, parsed value, comparator, units and parse-failure flag on every result
• A reviewed unit conversion table, never an inferred rule
• Counts reconciled to a trusted operational report, with every difference explained
• The reconciliation running as an automated test
• The raw extract retained alongside any modelled form
• Minimum necessary fields, with de-identification considered before the design was fixed

Bottom line

Pick the transport quickly. If you can get the reporting environment, take it; if you are an outside product company, plan on bulk export where it is available and an interface feed where it is not, and use documents to fill gaps. Then spend the project on the parts that decide whether anyone can rely on the result: a reviewed code mapping, a deliberate timestamp choice, honest handling of values that are not numbers, and a reconciliation to a report the operational team already believes. Those four things are what separate a dataset people build on from one that quietly produces wrong answers for a year.

Frequently asked questions