Looker for healthcare: readmissions, length of stay, patient data

A hospital steering dashboard rests on a handful of metrics, each hiding definition choices: which stay is an index stay, from which day a readmission counts, what a night is. On an OMOP-shaped warehouse in BigQuery, the approach that lasts is to fix those choices once in dbt, test them on every build, and let Looker expose nothing but counts and ratios of totals, behind permissions set at the model level. We have published a complete reference repository on a public, synthetic OMOP dataset to show the mechanics.

OMOP in two paragraphs

The OMOP Common Data Model (Observational Medical Outcomes Partnership) is an open standard maintained by the OHDSI community to give healthcare data the same structure whatever its origin. Every clinical event table links to the person table: visit_occurrence for stays and consultations, condition_occurrence for diagnoses, drug_exposure for medications, death, and observation_period for the window during which a patient's data is reliably captured. The current version is 5.5.

The second half of the model is its standardised vocabulary. Every source code (an ICD-9 or ICD-10 code, say) is kept as is and mapped to a standard concept, usually SNOMED for diagnoses, through the concept table. The concept_ancestor table holds the transitive closure of the hierarchy: every descendant of "diabetes mellitus" in one join. For BI this has two consequences: one dbt model serves several hospitals, and a cohort is defined by a concept identifier rather than a string search.

30-day readmission: the choices that move the number

The readmission rate is not one number but a family of numbers, and each choice below shifts it by several points.

Choice	Reference repository	Common alternative	Effect
Window	Day 1 to day 30 after discharge, day 0 being a transfer	Day 0 included	Higher
Cause	All-cause	Unplanned only, by algorithm	Lower
Index stay	Inpatient stay with a discharge date, patient discharged alive, at least 30 days of data after discharge	No censoring of the last 30 days	The final month looks artificially low
Facility	Any site in the network	Same site only	Lower
Concept 262 (emergency then inpatient)	One inpatient stay	Two stays	Double counting

The reference measure used by CMS in the United States, the hospital-wide all-cause unplanned 30-day readmission, excludes planned readmissions from the numerator through an algorithm that treats a few types of care as always planned (obstetric delivery, transplant, maintenance chemotherapy, rehabilitation) and never treats an admission for acute illness or a complication as planned. The reference repository applies a simplified, all-cause version and says so in the glossary. The window and the visit concept lists are dbt variables: a stricter local definition is a configuration change followed by a test, not a rewrite.

The mechanics matter as much as the choice. The int_readmissions model orders each patient's inpatient stays and uses LEAD and LAG to find the next admission and the previous discharge. Two flags land on fct_encounters at stay grain, is_index_admission and is_readmitted_within_30d, and a dbt test checks on every build that the second is never true when the first is false. In Looker the rate becomes a ratio of two filtered counts, which slices by date, site, provider or age band without a second query and drills down to the stays that make it up.

Length of stay: nights, as a ratio of totals

Average length of stay is total nights between admission and discharge divided by the number of inpatient discharges. Nights, not days: a same-day discharge counts zero. And a ratio of totals, never an average of per-site averages, otherwise the small unit that keeps patients a long time weighs as much as the large one.

measure: average_length_of_stay {
  label: "Average length of stay"
  description: "M4. Total nights divided by inpatient discharges. A ratio of totals."
  type: number
  sql: ${total_nights} / NULLIF(${inpatient_discharge_count}, 0) ;;
  value_format: "0.0"
}

The denominator, inpatient discharges, is the same one the inpatient mortality rate uses, which prevents a dashboard from showing two different populations under two neighbouring tiles.

Chronic prevalence through concept hierarchies

Prevalence of diabetes, hypertension, COPD or heart failure is defined by a parent SNOMED concept (201820, 316866, 255573, 316139) expanded to all its descendants through concept_ancestor. No LIKE '%diabet%', no hand-typed ICD list: the definition survives vocabulary updates, is auditable as a single identifier, and adding a fifth group is one line in dbt_project.yml.

The denominator is active patients, those with at least one encounter, not every person: someone with no contact with the system has no opportunity to be diagnosed in the data. Source codes with no standard mapping land on concept 0, labelled "No matching concept" and kept rather than dropped, so the diagnosis total still reconciles with the source.

Protecting patient-level fields in Looker

Three mechanisms complement each other, and none replaces the other two.

Mechanism	What it protects	In the repository
access_grant with required_access_grants	Identifying fields	Attribute phi_access, value yes or no
access_filter on every Explore	Which rows are visible	Attribute care_site_access, % for the whole network
Query history (System Activity)	Traceability	Who queried which field, when, on which Explore

The two attributes are separate because they map to two roles: a site manager may see every row of their site without seeing identifiers. hidden: yes is not protection: the field remains queryable through the API and by agents.

Which fields to protect? The Safe Harbor rule of the US HIPAA regulation lists eighteen categories of identifiers, among them names, all elements of dates other than year (birth, admission, discharge, death), ages over 89 and any geographic subdivision smaller than a state. The repository applies that grid: source identifier, birth date, death dates and zip code carry the grant; year of birth, age band (whose top band, 85 and over, absorbs the over-89 rule), a deceased flag and state stay open; the staging layer never loads street address lines. Under GDPR the legal basis differs, but the mechanics in Looker are the same.

Why synthetic data

The repository runs on bigquery-public-data.cms_synthetic_patient_data_omop. That dataset is the CMS DE-SynPUF, a synthetic sample of 2008 Medicare beneficiaries and their claims from 2008 to 2010, built with statistical disclosure limitation techniques from real seed beneficiaries, converted to OMOP CDM 5.2 by the OHDSI community and published by Google. About two million synthetic patients, no real health data.

The grants and the site filter therefore protect nothing real in the demo. They are there because the pattern must exist before real data arrives, not after. The dataset's limits are those of a claims file: sparse facility and provider names, a specialty that often falls back to the source value.

The reference repository, step by step

The code is public: looker-semantic-layer-health, MIT licence, deployable in fifteen minutes on a Google Cloud project.

dbt. Thirteen staging models, an intermediate layer that classifies visits (int_encounters_classified), computes readmission windows (int_readmissions), expands the chronic concept groups and rolls facts up per patient, then six marts: fct_encounters, fct_diagnoses, dim_patients, dim_providers, dim_care_sites, dim_dates. The glossary dbt/metrics.md lists seventeen metrics M1 to M17.

LookML. Three Explores (encounters, diagnoses, patients), all many_to_one, and the facts never join each other: the visit type and care site the diagnoses Explore needs are denormalised onto fct_diagnoses by dbt. One datagroup, health_daily. One derived table, condition_volume_rank, for the "top twenty conditions versus long tail" grouping. Every date uses datatype: date and convert_tz: no, because the model stores calendar dates and a timezone shift would break partition pruning.

Dashboard. clinical_operations, six tiles and three filters, each title carrying the glossary identifier.

Continuous integration. The same linter as the sibling repositories: mandatory descriptions, relationship on every join, no hard-coded dates, one primary key per view. Then dbt build, incremental on pull requests.

FAQ

Our warehouse is not in OMOP format, is the repository still useful?

Yes for everything downstream of staging: the readmission windows, length of stay and the separation between row and field permissions do not depend on OMOP. What changes is the staging layer and, without a standardised vocabulary, the definition of chronic groups, which goes back to being a code list to maintain.

Why does the repository's rate not match the official indicator?

Because the official indicator is risk-adjusted and excludes planned readmissions, which a steering semantic layer does not need to reproduce. The right use is to track a raw rate defined once and stable over time, and leave the regulatory calculation to the pipeline responsible for it.

Is Looker enough for healthcare data compliance?

No. Looker brings field permissions, row permissions and query history. Compliance also depends on hosting, contracts, upstream pseudonymisation and access governance. The model shown here is the part of that setup that lives in code.

Can a conversational agent be connected to this data?

That is exactly where the semantic layer matters most. The agent inherits the grants, the row filters and the descriptions; a model where identifiers are merely hidden and "diabetic" is a text search would produce wrong answers and leaks. The repository's model is built so that the agent's answer is the dashboard's answer.

Next step

If your steering committee compares readmission rates computed in three tools, start with the five-choice table above: one column per option retained, one signature from the medical director. The repository then shows how those choices become two tested flags and one measure.

See the Semantic layer on BigQuery, dbt and Looker service, and read next: Why your numbers disagree and Conversational Analytics prerequisites.

Sources

• OHDSI, OMOP Common Data Model, official documentation of the model and of version 5.5.
• OHDSI, The Book of OHDSI, Common Data Model chapter, design principles, clinical tables, mapping of source codes to standard concepts.
• CMS, Hospital-Wide All-Cause Unplanned Readmission Measure (NQF 1789), measure definition and planned readmission algorithm.
• Legal Information Institute, 45 CFR 164.514, the eighteen identifiers of the Safe Harbor method.
• CMS, 2008-2010 Data Entrepreneurs' Synthetic Public Use File (DE-SynPUF), origin and construction of the synthetic dataset.
• Registry of Open Data on AWS, DE-SynPUF in OMOP Common Data Model, OMOP conversion by the OHDSI community.