The contract rule that protected us from a serious misreporting problem was simple: we allow an "unknown patient" bucket, but we do not let it grow unnoticed. If too many facts are mapped to the dummy patient key, the pipeline fails and the report does not refresh. We keep a dummy row inside the Dim table with the name and address set to "NA" and give it a real surrogate key. When building the facts, any missing or bad patient IDs are sent to this key. This acts like a quarantine lane. Our data loads stay consistent, we do not lose rows during joins, and we have a clear spot to look for problems instead of searching through many tables. What is worrying is how easy it is to miss these issues. For us, all the checks looked fine, the fact table row count barely changed, and the metric only shifted slightly, just enough that most people would ignore it as normal variation. The real warning came from a small check we had just added: the share of "NA patient" jumped from almost zero to 1.8% overnight. The root cause ended up being a small format change with a big downstream effect. After a vendor update, the patient ID started arriving with a prefix on some records. Our cleaning step was still trying to cast to the old shape, so those prefixed IDs got turned into nulls and then mapped to the dummy "NA" key. No jobs failed and the load finished normally, which is why it was dangerous. If we had refreshed the report, it would have looked like we improved, but the truth was we were quietly excluding real patients because their IDs no longer matched the dimension. The key setup was to put a hard stop right before the report tables, not just log a warning. We added a dbt test on the reporting mart and tied that model to the actual dashboard/report using a dbt exposure. If the test fails, the build fails and nothing downstream updates. The test itself is simple: the "NA patient" row is allowed to exist, but it cannot be used. In other words, the dummy key is there by design, and the moment its row count stops being basically zero, we treat it as a broken contract and stop the refresh. That gate tripped, we fixed the mapping, reprocessed the load, and avoided publishing a "great improvement" that was actually missing patients.
The rule that kept us out of the penalty box was embarrassingly simple: numerator cannot exceed denominator. Every eCQM is a fraction. Patients who got the quality action divided by eligible patients. Rate should never exceed 100%. Upstream issues blow this up constantly—duplicate records from EHR merges, date filters that count patients in the numerator but drop them from the denominator. We caught a 118% sepsis bundle rate. Day before CMS submission. Encounter-stitching bug had doubled patients in the numerator. Without the constraint, we'd have shipped impossible data—inviting a CMS audit and a 25% Market Basket hit. Setup: dbt test, one assertion. Numerator exceeds denominator? Build fails. Baked into CI. Non-negotiable. dbt exposures mapped every measure to its source models. Upstream schema change? We know exactly which eCQMs are at risk. One constraint. One near-miss. Zero penalties.
One rule that genuinely saved us from a costly eCQM misreporting incident was enforcing code set validity at the contract boundary, not downstream in reporting logic. Specifically, we added a hard rule that every clinical event used for numerator or denominator logic had to map to an approved value set version for the reporting period. If the code was valid but from the wrong version window, the pipeline failed. The setup detail that mattered most was implementing this as a Great Expectations validation tied to a dbt model, not as an analyst checklist. We already had schemas enforced, but schemas alone do not protect you from semantic drift. In our case, a new upstream feed introduced updated SNOMED codes that were technically valid but not aligned to the CMS eCQM spec year we were reporting against. The data looked fine, row counts matched, and dashboards rendered. Without the contract, we would have shipped incorrect numerators. The expectation was simple but strict. Every clinical_code had to exist in a reference table keyed by measure_id and reporting_year. Anything outside that join failed fast. No warnings, no soft failures. A simplified example of the rule was essentially: for each fact row used in an eCQM measure, assert that exists(select 1 from value_set_ref where code = clinical_code and measure = X and year = Y). We wired this expectation to block the dbt exposure tied to our CMS submission models. That exposure linkage mattered because it made the failure visible to both data engineering and compliance teams, not just buried in logs. Before this, issues were caught weeks later during measure reconciliation. Afterward, we caught them same day, before any downstream aggregation. That one contract turned eCQM reporting from a trust exercise into a verifiable system.
When enforcing data contracts for eCQM reporting in a lakehouse stack, the one rule that prevented a costly error was a mandatory not null and code set validation on measure critical fields before any model reached production. We built Great Expectations tests that blocked deployment if patient IDs, encounter dates, or value sets failed validation. In one case, a source system changed a date format and would have excluded 12 percent of eligible encounters. The test caught it before submission. I also required dbt exposures to map each model to its downstream report so impact was visible. That visibility kept Legal and compliance teams aligned. Strong pre deploy validation protects accuracy and reputation.