Data Models

Staging

Staging schema: staging materialized: table

The staging layer is the first landing zone for raw source data. Each source system has its own subdirectory under models/staging/, and models in this layer map closely to the source schema.

Rules for staging models:

• No business logic. Cleaning only — cast types, normalise nulls, apply cleaning macros.
• One model per source table or entity.
• All models materialise as tables so downstream layers always query pre-built data.

Source subdirectories: cemr/, crvs/, ebuzima/, efiche/, hmis_tracker/, hpemr/, idsr_dhis2/, immunization/, moh_rhmis/, seeds/.

-- Example: a typical staging model
select
    {{ clean_key_integer('patient_id') }}       as patient_id,
    {{ clean_text('first_name') }}              as first_name,
    {{ clean_date('date_of_birth') }}           as date_of_birth,
    {{ clean_key_text('facility_code') }}       as facility_code
from {{ source('cemr', 'patients') }}

Intermediate

Intermediate schema: intermediate materialized: table

The intermediate layer is where business logic lives. Models here join and enrich staging data, apply deduplication, and implement slowly changing dimension (SCD) patterns for historical change tracking.

What belongs here:

• Multi-source joins that resolve entities across systems (e.g., linking a patient across CEMR and eFICHE)
• Deduplication logic using hash_column keys
• SCD Type 2 history tables for tracking changes over time
• Derived business metrics that are reused across multiple DWH models

Models in this layer should not be consumed directly by BI tools — they are stepping stones to the DWH layer.

DWH

DWH schema: dwh materialized: table

The DWH layer is the analytical core of nhic_dbt. It is split into two subdirectories:

• dwh/dimensions/ — conformed dimension tables (e.g., dim_patient, dim_facility, dim_date)
• dwh/facts/ — fact tables that record events and measures (e.g., fct_visits, fct_immunizations)

All DWH models are full-refresh tables. This keeps data consistent and avoids incremental state drift in the core analytical layer.

Superset and GreenRiver dashboards should not query the DWH schema directly — use the reporting layer as the stable interface.

Reporting

Reporting schema: reporting materialized: view

The reporting layer is a thin buffer between the DWH and BI tools. All models are materialised as views — they add no storage overhead and always reflect the current DWH state.

Purpose:

• Expose renamed or re-shaped versions of DWH tables optimised for specific dashboards
• Shield BI tools from DWH schema changes — when a DWH model is refactored, only the reporting view needs updating
• Apply final column selection and aliasing expected by Superset datasets or GreenRiver queries

Adding a new reporting model is the correct way to expose data to a new BI report.