DATA
Charles Trinh — Clinical Data Warehouse Architecture | Las Vegas, NV · Las Vegas, Nevada · Henderson · Clark County, Nevada · North Las Vegas, Nevada

A clinical data warehouse earns its keep when a quality analyst can answer a question in one afternoon instead of filing a ticket. I build the schema, loads and access layer that make that true.
Where clinical warehouses usually fail
Most warehouse rebuilds are triggered by the same three problems. Nightly loads run longer than the window allows, because every source was modelled independently and de-duplication happens after the fact. Report totals disagree with the source system, because a cancelled encounter or a superseded observation was folded in twice. A new department needs a metric and discovers the conformed layer has no concept of it. None of these is a technology failure. Each one comes from designing the schema around a reporting tool instead of the clinical event lifecycle.
I start by classifying every source table by grain: patient, encounter, claim, observation, order, or document. Things that share a grain get a single fact table with declared keys. A patient_age_at_event attribute that gets recomputed differently in three extract views is the classic tell that the grain was never settled. Settling it once, explicitly, removes most of the reconciliation noise I find in existing warehouses and it is the cheapest step in the whole build.
The artefact that proves the work is a conformed dimension map, signed by data stewards, that names the grain of every table and the source of every attribute. When two teams later disagree about a patient count, the map decides the question, not a meeting. Until that document exists, no additional hardware or tuning will close the gap between systems that report different numbers for the same thing.
Staged zones and the load contract
I build in four zones: raw landing, typed staging, conformed dimensions, and a presentation layer that only contains what has been tested. Raw landing preserves source bytes untouched so re-ingestion is always possible. Typed staging applies codes and units. Conformed dimensions apply the identity rules from the master patient index. Presentation is a view layer and it holds nothing the analyst change history cannot see.
Every load has a contract: expected row counts per source, a declared refresh window, a late-arriving rule, and a backfill procedure. The contract lives in a YAML file next to the pipeline, versioned with it. A load that reports success but writes a third of yesterday's rows is a defect, and the contract is what lets the scheduler detect it without a human watching a dashboard. Silent partial loads are the most common cause of stale quality reporting I encounter.
Backfill is the part vendors skip. A correction to a source system six months back must be able to replay through staging and reshaping without touching raw landing. I test backfill on a copy environment before the first production load, because a warehouse that can only go forward will eventually find itself disagreeing with the record it is supposed to mirror, with no practical route to repair.
Columnar storage and query shape
The storage choice matters less than partition and sort keys. I partition the largest fact tables by ingestion month and sort within each partition by patient key and event time. That combination serves the common queries, which ask about one patient across history, or one cohort across a period. It fails badly for full-table joins between two large facts. Where those joins are essential, I materialise a cohort bridge table rather than relying on the planner to find a workable plan at query time.
Queries are reviewed against data volume, not just output. A dashboard that runs in two seconds on development data can take four minutes in production when the planner chooses a different join order. I add query timing budgets to the test harness, run them against a statistically representative copy, and treat a regression beyond the budget as a failing test. That ordering catches the problem before an analyst notices a spinner.
For operational databases underneath, the design principles carry over. The PostgreSQL and SQLite design work I do follows the same separation of ingestion from query, and the same insistence on measured query plans rather than guessed ones. A warehouse is only as fast as its slowest conformed layer., and I record it with the same care as a production incident, because the first audit after a problem is the moment the agreement either holds or needs revisiting.
Conformed dimensions and slowly changing history
Clinical history changes. A provider moves organisations, a diagnosis is corrected, a payer assignment overlaps. The warehouse needs to answer questions about the state at the time of the event, not the state today. I model provider, organisation, payer, and diagnosis assignment as slowly changing dimensions with effective dates, and every fact carries the surrogate key that was valid when the event occurred.
Type two history on the patient dimension is usually the one that gets neglected. Age band, location, and primary care assignment all change, and a standing cohort definition that silently reselects patients each month makes most population health comparisons meaningless. I store the assignment history explicitly and provide a point-in-time cohort view. Analysts use that view and the reselection problem disappears.
Conformance across teams is the goal, but I do not merge two dimensions until both source teams agree they measure the same thing. A merged provider dimension where one source tracks clinicians and the other tracks billing groups produces wrong counts on both sides. Separate, clearly-labelled dimensions are less satisfying to model and far more honest when the linkage is uncertain.
Access control inside the warehouse
Warehouse access is rarely checked as carefully as production clinical access. I treat it with the same discipline: row-level security tied to organisation and care team, column-level masking on identifiers outside the analyst's scope, and a role model that mirrors the access groups already defined in the HIPAA privacy and security review. When the warehouse is a copy of the EHR, the warehouse is subject to the same rules.
Analyst roles get a constrained view, not the raw conformed layer. A researcher who needs patient-level detail for an approved protocol gets a project-scoped dataset with an expiry date and a named owner. The size of the extract is logged. I have found hospital warehouses where every analyst in the organisation could query full patient records, which is a reportable risk under a security review even when nobody has looked.
Access decisions are recorded in the warehouse itself. Each role, each grant, and each expiry lives in a table that is audited monthly. That table is the artefact I hand to a security officer at review. If the access model lives only in the directory, no reviewer can tell whether the warehouse is currently exposing more than its owner believes.
Testing, reconciliation and lineage
Every pipeline is tested three ways: row count reconciliation between layers with a tolerance, column-level null and range tests on derived fields, and a set of standing clinical queries whose answers should not drift between loads without a documented cause. The third test catches regressions that count checks miss, because a cohort that shrinks by a third between loads often leaves total row counts unchanged.
Lineage is generated, not documented by hand. I build from the source table definitions down and emit a lineage graph that names each derived column's parents and each intermediate table's query. When a revenue-cycle metric is disputed, the graph answers whether the problem is in the source, the conformed layer, or the metric definition. That answer usually arrives in minutes rather than days of tracing SQL by hand.
A small clinical store carrying ten thousand patients and a warehouse carrying ten million are different machines. The former can get away with a nightly full rebuild. The latter needs partition pruning, late-arriving handling, and a test harness that runs against realistic volume. I only sign off a warehouse when the tests pass against a realistic copy, not the toy dataset.
Operations and the handover
After go-live the warehouse is run by the hospital's own team. I write the runbook with them, not for them: rotation of load credentials, the procedure for a failed night, the procedure for a late source correction, the procedure for adding a table to a conformed fact, and the expected refresh status at 06:00 each morning. The runbook lists who to call for each failure class, which is usually the source system owner.
Dashboards get owners and review dates. A dashboard nobody owns becomes wrong silently, and an analyst who quotes it in a meeting does not know that. I record the owner, the source tables, and the review date and I retire dashboards whose owner has left. Retiring a dashboard is unpopular at the time and saves the next incident review a wasted week.
An architecture review six months after go-live is the last piece. I re-measure load times against the original contract, test new queries that arrived since, and confirm the access table still matches what is actually granted. Warehouses rot slowly and in specific ways, and a review at six months catches the rot before the team stops trusting the output. The analytics readiness assessment covers the same ground for teams that have not built one yet.
Proof
- ONC (g)(10) self-test PASS, 317 of 317 subtests, April 2026 — public suite URL
- 67 public hostnames, 51 serving, one AWS account
- 104 clearinghouse enrolments across 53 jurisdictions
- USCDI v7 proposal — Patient Digital Identity Credential — listed at isp.healthit.gov
- SAM.gov UEI MVKQW9DKKGB8 · CAGE 20KM5 · NPI 1194645564
- AWS Business Associate Addendum active since 18 August 2026
Where this work is delivered
Engagements run from Las Vegas and reach across the valley: clients in Las Vegas, NV; Las Vegas, Nevada; Henderson; Clark County, Nevada; and North Las Vegas, Nevada — plus remote delivery nationwide.
How an engagement runs
1. Trace one real case
Follow a single patient or claim end to end with a timestamp at every hop, before any document is written.
2. Written findings
A findings list naming the defect, the system, the owner and the date.
3. Monthly review
Review the list monthly and check whether the dates held.
Frequently asked
What is this service?
This page describes charles trinh — clinical data warehouse architecture — the scope, the failure modes it prevents and how the work is measured. It sits in the Security and Compliance category at trinh.io.
Where is the work delivered?
Engagements run from Las Vegas, Nevada across the valley — Las Vegas, Henderson, North Las Vegas, Spring Valley, Paradise, Winchester, Sunrise Manor, Enterprise, Summerlin, Clark County — and remotely anywhere in the United States. On-site work in the Las Vegas valley, remote delivery elsewhere.
What does it cost?
Standard rate $195 per hour, committed $165 per hour at twenty hours or more per week for three months or more; $1,600 standard day, $1,350 committed day. Scoped in writing before work starts, fixed fee where the deliverable is defined.
Who is Charles Trinh?
A technology architect in Las Vegas, Nevada, working in healthcare identity, consent and interoperability. Not a physician, not a medical practice, and not affiliated with any other Charles Trinh.
How does an engagement start?
With one trace of a real case and a written findings list naming the defect, the system, the owner and the date. The first call is twenty minutes and free: 702-559-3489.
Engagement
Scoped in writing before work starts; fixed fee where the deliverable is defined. First call is 20 minutes and free. 702-559-3489 · charles@trinh.io
Talk to Charles
- Phone: 702-559-3489
- Email: charles@trinh.io
- Based in Las Vegas, Nevada · working remotely across the United States
Record
Profile
Healthcare technology architect working where federal regulation becomes production infrastructure. Built and operates a FHIR R4 clinical platform — SMART App Launch authorization server, patient and provider portals, SQLite — on a hardened single-host AWS estate of 67 public hostnames. The platform returned a self-test PASS on the ONC (g)(10) Standardized API test suite in April 2026, 317 of 317 subtests, publicly verifiable. Designed a consent architecture in which a permissioned ledger records patient grant, refusal and revocation as immutable events while all clinical data stays off-chain and encrypted per patient — no PHI on the ledger by design. Filed a proposed USCDI v7 data element with ONC on verifiable patient identity. Translates the Federal Register, HHS/CMS guidance and HITRUST requirements into architecture, controls and audit evidence.
Selected engineering scale
- Self-managed estate of 67 public hostnames, 51 serving, on one AWS account.
- FHIR R4 API with a SMART App Launch authorization server; patient and provider portals; an AI service layer; Hyperledger Fabric peer, orderer and CA; IPFS; a 337-table SQLite clinical store.
- Two permissioned distributed ledgers in production, one operating a consent registry that records grant, refusal and revocation with no PHI on chain.
- X12 005010 pipelines across eight transaction sets.
- Designed, built, deployed and operated end to end by one person.
Credentials and standards work
- Inferno (g)(10) self-test PASS, 317 of 317 subtests, April 2026 — the ONC Standardized API test suite. Standards validated: US Core 6.1.0, USCDI v3, SMART App Launch 2.0.0, Bulk Data 2.0.0. Public suite URL.
- USCDI v7 public comment submission, April 2026 — proposed Patient Digital Identity Credential under the Provenance data class, listed publicly on the ONC Interoperability Standards Platform.
- SAM.gov UEI MVKQW9DKKGB8 · CAGE 20KM5 · NPI 1194645564. AWS Business Associate Addendum active 18 August 2026.
Technical environment
AWS us-east-1 (compute, storage, messaging, SMS, email, voice) · Linux and systemd hardening · nginx · Fabric and EVM ledgers · FHIR R4 · X12 005010 · HL7 · SQLite · self-hosted mail, drive, search and monitoring · Git-based change control and CI release gates.