Healthcare organizations generate clinical event data at a volume that most analytics systems can't keep up with. A 500-bed hospital produces ADT (Admit/Discharge/Transfer) events, lab results, vitals readings, medication administration records, and billing codes continuously across every patient encounter. At a health system level, that scales to hundreds of millions of events per month.
The analytics layer needs to answer questions across all of it in real time: Which patients in the ICU have deteriorating SOFA scores? What is today's emergency department boarding time by hour? Which medication administration patterns deviate from protocol? Standard relational databases answer these slowly if at all. ClickHouse® handles the volume and the latency healthcare analytics requires.
This post covers the schemas, queries, and patterns for clinical event tracking, real-time monitoring, population health analytics, quality metrics, and compliance reporting in healthcare.
Clinical event schema
The core table captures clinical events as an append-only log. Healthcare data is fundamentally event-driven: a patient encounter produces a stream of events (assessments, orders, results, administration records) that form the audit trail for care decisions. ClickHouse's MergeTree engine fits this pattern:
CREATE TABLE clinical_events
(
event_time DateTime64(3),
event_type LowCardinality(String), -- 'adt', 'lab_result', 'vitals', 'medication', 'order'
encounter_id String,
patient_id String,
facility_id LowCardinality(String),
unit_id LowCardinality(String),
provider_id String,
diagnosis_code LowCardinality(Nullable(String)), -- ICD-10
procedure_code LowCardinality(Nullable(String)), -- CPT
observation_code LowCardinality(Nullable(String)), -- LOINC code
observation_value Nullable(Float64),
observation_unit LowCardinality(Nullable(String)),
severity LowCardinality(Nullable(String)), -- 'critical', 'abnormal', 'normal'
properties String -- JSON for event-specific data
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (facility_id, unit_id, event_time, encounter_id);
LowCardinality on event_type, facility_id, unit_id, diagnosis_code, and observation_code keeps GROUP BY performance high on the high-frequency queries that clinical dashboards run. LOINC codes for lab observations and ICD-10 for diagnoses typically have fewer than 10,000 distinct values in any deployment, which is exactly the range where ClickHouse's dictionary encoding provides the most benefit according to the official ClickHouse documentation.
Real-time patient monitoring
For clinical operations teams watching patient status across units, a real-time vitals aggregation query over the last hour:
SELECT
unit_id,
encounter_id,
argMax(observation_value, event_time) AS latest_value,
argMax(severity, event_time) AS latest_severity,
max(event_time) AS last_reading_time,
dateDiff('minute', max(event_time), now()) AS minutes_since_last
FROM clinical_events
WHERE event_type = 'vitals'
AND observation_code = '8867-4' -- LOINC: heart rate
AND facility_id = {{ String(facility_id, required=True) }}
AND event_time >= now() - INTERVAL 1 HOUR
GROUP BY unit_id, encounter_id
HAVING latest_severity = 'critical'
OR minutes_since_last > 30
ORDER BY minutes_since_last DESC;
The argMax pattern gets the latest value per encounter without waiting for background deduplication, which is important for dashboards that need to display current patient state.
For early deterioration detection using the NEWS2 (National Early Warning Score) components:
WITH latest_vitals AS (
SELECT
encounter_id,
maxIf(observation_value, observation_code = '8867-4') AS heart_rate,
maxIf(observation_value, observation_code = '9279-1') AS resp_rate,
maxIf(observation_value, observation_code = '59408-5') AS spo2,
maxIf(observation_value, observation_code = '8310-5') AS temperature,
max(event_time) AS last_update
FROM clinical_events
WHERE event_type = 'vitals'
AND facility_id = {{ String(facility_id, required=True) }}
AND event_time >= now() - INTERVAL 4 HOUR
GROUP BY encounter_id
)
SELECT
encounter_id,
heart_rate,
resp_rate,
spo2,
temperature,
last_update,
multiIf(
resp_rate < 8 OR resp_rate > 25, 'high_risk',
spo2 < 91 OR heart_rate > 130, 'high_risk',
resp_rate BETWEEN 21 AND 24 OR spo2 BETWEEN 91 AND 93, 'medium_risk',
'low_risk'
) AS deterioration_risk
FROM latest_vitals
WHERE deterioration_risk != 'low_risk'
ORDER BY last_update DESC;
Real-time anomaly detection on lab results
Flagging lab results that fall outside expected ranges for the clinical context, using Z-score against the patient's own 90-day baseline:
WITH patient_baselines AS (
SELECT
patient_id,
observation_code,
avg(observation_value) AS avg_value,
stddevPop(observation_value) AS stddev_value
FROM clinical_events
WHERE event_type = 'lab_result'
AND observation_value IS NOT NULL
AND event_time >= today() - 90
GROUP BY patient_id, observation_code
HAVING count() >= 3
)
SELECT
e.encounter_id,
e.patient_id,
e.observation_code,
e.observation_value,
b.avg_value,
round((e.observation_value - b.avg_value) / nullIf(b.stddev_value, 0), 2) AS z_score
FROM clinical_events AS e
INNER JOIN patient_baselines AS b
USING (patient_id, observation_code)
WHERE e.event_type = 'lab_result'
AND e.event_time >= now() - INTERVAL 2 HOUR
AND abs((e.observation_value - b.avg_value) / nullIf(b.stddev_value, 0)) > 3
ORDER BY abs(z_score) DESC;
A Z-score above 3 means a lab result is more than 3 standard deviations from the patient's own 90-day baseline, a meaningful outlier signal that complements absolute reference ranges.
Quality metrics and readmission analytics
30-day readmission rate is a CMS quality metric that affects hospital reimbursement. Computing it across a full patient population in ClickHouse:
SELECT
toStartOfMonth(index_discharge) AS month,
primary_diagnosis_code,
count() AS index_discharges,
countIf(readmitted_within_30d) AS readmissions_30d,
round(
countIf(readmitted_within_30d) /
count() * 100, 2
) AS readmission_rate_pct
FROM (
SELECT
e1.encounter_id,
e1.patient_id,
argMax(e1.event_time, e1.event_time) AS index_discharge,
argMax(e1.diagnosis_code, e1.event_time) AS primary_diagnosis_code,
min(e2.event_time) IS NOT NULL
AND min(e2.event_time) <= index_discharge + INTERVAL 30 DAY
AS readmitted_within_30d
FROM clinical_events AS e1
LEFT JOIN clinical_events AS e2
ON e1.patient_id = e2.patient_id
AND e2.event_type = 'adt'
AND JSONExtractString(e2.properties, 'adt_type') = 'admission'
AND e2.event_time > argMax(e1.event_time, e1.event_time)
WHERE e1.event_type = 'adt'
AND JSONExtractString(e1.properties, 'adt_type') = 'discharge'
GROUP BY e1.encounter_id, e1.patient_id
)
GROUP BY month, primary_diagnosis_code
HAVING index_discharges >= 10
ORDER BY month DESC, readmission_rate_pct DESC;
Population health and cohort analytics
Identifying patients with multiple chronic conditions (comorbidities) for care management programs:
SELECT
patient_id,
uniq(
CASE WHEN diagnosis_code LIKE 'E11%' THEN 'diabetes' END,
CASE WHEN diagnosis_code LIKE 'I10%' THEN 'hypertension' END,
CASE WHEN diagnosis_code LIKE 'I25%' THEN 'coronary_artery_disease' END,
CASE WHEN diagnosis_code LIKE 'J44%' THEN 'copd' END,
CASE WHEN diagnosis_code LIKE 'N18%' THEN 'ckd' END
) AS chronic_condition_count,
count() AS total_encounters_12m,
max(event_time) AS last_encounter
FROM clinical_events
WHERE event_type IN ('adt', 'order')
AND diagnosis_code IS NOT NULL
AND event_time >= today() - 365
GROUP BY patient_id
HAVING chronic_condition_count >= 3
ORDER BY chronic_condition_count DESC, total_encounters_12m DESC;
Medication safety and ADE detection
Adverse drug events (ADEs) are a leading cause of preventable harm in hospitals. ClickHouse can run multi-step sequence detection to flag patients who received a potentially harmful drug combination within a short window:
SELECT
encounter_id,
patient_id,
windowFunnel(7200)(
event_time,
JSONExtractString(properties, 'drug_class') = 'anticoagulant',
JSONExtractString(properties, 'drug_class') = 'antiplatelet',
event_type = 'lab_result'
AND observation_code = '3173-2'
AND observation_value < 50 -- low platelet count
) AS risk_stage
FROM clinical_events
WHERE event_type IN ('medication', 'lab_result')
AND event_time >= today()
GROUP BY encounter_id, patient_id
HAVING risk_stage >= 2
ORDER BY risk_stage DESC;
risk_stage = 3 indicates a patient received both drug classes and then showed a low platelet count within a 2-hour window: a potential ADE signal that warrants clinical review.
Emergency department and bed management analytics
ED throughput metrics are operationally critical and involve aggregating across thousands of encounters in real time:
SELECT
toStartOfHour(event_time) AS hour,
facility_id,
count() AS total_adt_events,
countIf(JSONExtractString(properties, 'adt_type') = 'admission') AS admissions,
countIf(JSONExtractString(properties, 'adt_type') = 'discharge') AS discharges,
countIf(JSONExtractString(properties, 'adt_type') = 'transfer') AS transfers,
avg(
JSONExtractFloat(properties, 'length_of_stay_hours')
) AS avg_los_hours,
quantile(0.9)(
JSONExtractFloat(properties, 'door_to_provider_minutes')
) AS p90_door_to_provider_min
FROM clinical_events
WHERE event_type = 'adt'
AND event_time >= now() - INTERVAL 24 HOUR
GROUP BY hour, facility_id
ORDER BY hour DESC, facility_id;
Door-to-provider time at p90 is a key ED quality metric. A single facility seeing p90 above 60 minutes in this query is an operational alert worth escalating immediately. ClickHouse runs this across all facilities simultaneously, so the operations center dashboard refreshes across the entire health system in the same query.
Compliance and audit queries
Healthcare audit requirements mandate that access to patient records is logged and queryable. Because patient_id is first in the sort key, building real-time APIs on ClickHouse for HIPAA audit requests returns the full event history for a given patient ID in milliseconds regardless of how many total events the table contains:
SELECT
event_time,
event_type,
encounter_id,
provider_id,
observation_code,
observation_value,
severity
FROM clinical_events
WHERE patient_id = {{ String(patient_id, required=True) }}
ORDER BY event_time DESC;
The same pattern works for provider activity audits (all events by provider_id), facility compliance reports (all events by facility_id and date range), and payer audits (all events with specific procedure_code values).
Tinybird for healthcare
Healthcare analytics has two requirements that are difficult to satisfy simultaneously: the speed that clinical operations demand and the compliance posture that healthcare data requires.
Tinybird is managed ClickHouse and is SOC 2 Type II certified. The enterprise-grade real-time analytics pattern applies directly: the clinical monitoring queries in this post become parameterized HTTP endpoints your nurse station dashboard, care management platform, or clinical decision support system calls in real time. SQL enforces that each request only returns data for the specified facility, unit, or patient, with no application-level row filtering required.
The ingestion path for healthcare event data: HL7 FHIR resources from your EHR system map to ClickHouse rows via the Kafka to ClickHouse connector, or directly through the Events API for systems that emit JSON. ADT events, lab results, and vitals readings become queryable within seconds of the clinical event occurring.
For population health and quality metrics that run on longer time horizons, what are materialized views and why do they matter for real-time explains the pre-aggregation pattern: define a materialized view that computes monthly readmission rates as data arrives, and the quality dashboard query reads the pre-aggregated result rather than scanning the full events table. Tinybird's own engineering team documented that pre-aggregated rollups reduce query time from 20-30 seconds on raw event tables to 250ms on a 500M-row dataset.
For real-time streaming data architectures in healthcare, where EHR systems, lab systems, pharmacy systems, and device integrations all produce data on different schedules, Tinybird acts as the unified analytical layer. Each source writes to its own datasource. SQL Pipes join and aggregate across sources. HTTP endpoints expose the results. Your EHR vendor manages the OLTP layer. Tinybird manages the analytical layer.