Hospital Billing, Modeled Once

The problem

Revenue cycle reporting ran directly against Epic Clarity and Caboodle. Those schemas are built for the EHR’s convenience rather than for analysis, so every new question became another one-off query written against raw source tables by whoever asked it. Reports disagreed with each other and no two people held the same definition of a billed encounter. That was the visible problem, and the smaller one. The larger one was that a report could be internally consistent and still wrong: bucket close status was derived by null-checking a close date, which silently counted rejected claims as closed. A close rate that counts rejections as closures is not a close rate, and nothing in the reporting layer surfaced it. Revenue cycle day counts decide where a health system puts its collections staff, so a wrong number does not only mislead a dashboard. It sends people at the wrong bottleneck.

The approach

I rebuilt the reporting as a layered dbt model. The base population comes from the Caboodle slowly-changing dimension. An enrichment layer joins 18+ Epic Clarity tables for lifecycle dates, provider, denial detail, and bucket status, and a unified format layer feeds a materialized gold table of roughly 19M rows. Two details carry most of the correctness. Every CTE in the enrichment layer deduplicates with a row number over the bucket key, because four join paths fan out otherwise. And the Caboodle source has to be filtered to current rows: without that filter it returns about 150M historical versions instead of 19M live ones, an eightfold overcount that still renders a plausible dashboard. Status resolution was rewritten to read all nine Epic bucket status codes instead of inferring closure from a null date. Alongside it I migrated 26 Epic Clarity cube views into the silver layer at 1:1 parity, and built the reconciliation that signed the work off. It is exclusion-first: every excluded record carries a reason code and lands in an audit log, so nothing drops silently. The platform runs under HIPAA constraints on a VNet-injected workspace reached through an on-premises gateway, with Unity Catalog row- and column-level security and SCIM-synced group access.

The outcome

Reconciliation covered 7.4M source keys across the AR and transaction cubes and accounted for every one of them, with 18 of 18 tables signed off against Epic extracts. The most useful run was the one that looked like a failure: 524,494 transaction IDs appeared missing under a service-area filter, and every one turned out to be present in the gold layer under a reassigned service area. The limit worth naming is that reconciliation runs one direction only, source extract into Databricks. It proves every source record arrived; it does not prove the target holds nothing extra, and closing that would have cost time the cutover did not have. What made the work durable was the handover: five knowledge-transfer and self-service guides, a published glossary, and four documented access paths, so analysts pull their own numbers instead of queuing behind the engineering team.

What I owned

Mine

The layered dbt model and its correctness rules, the status resolution that stopped rejected claims counting as closed, the reconciliation methodology and its exclusion codes, and the split into two semantic models for two audiences.

Inherited

The client’s Databricks platform and the HIPAA network controls around it, the Epic Clarity and Caboodle source systems, and the cube definitions the migrated views had to reproduce.

The trade-off

The call

Serve the executive dashboard and the self-service model from two semantic models over one gold table.

Instead of

A single DirectQuery model, which is one thing to build, one thing to refresh, and one definition to maintain.

Because

The dashboard reports median lifecycle days, and median is unavailable under DirectQuery, so a single model would have quietly reduced the headline metric to an average that outliers distort. Import mode gives the dashboard its medians over a pruned two-year window; DirectQuery gives analysts the full unfiltered set, which would not fit in memory. Both read the same gold table, so the two cannot drift apart.