> For the complete documentation index, see [llms.txt](https://spice.docs.medtroniclabs.org/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://spice.docs.medtroniclabs.org/reporting-and-data/data-pipeline-and-analytics.md).

# Data Pipeline & Analytics

SPICE keeps clinical data as FHIR resources and administrative data (locations, users, facilities) in a relational database. Neither is shaped for reporting, so the platform includes a **reporting ETL** that reads both sources on a schedule and writes flat, denormalized tables into a dedicated **reporting database**. Dashboards, exports and ad hoc queries read from that database rather than from the transactional systems.

For larger deployments there is an **optional analytics stack** (orchestration, batch processing, an open table format, a distributed query engine and a BI tool) that sits beside the simple ETL. Both paths are described below, together with guidance on when to choose each.

> Table and column names on this page are described at the level of conceptual groups.

## Data flow

```mermaid
flowchart LR
    subgraph Sources
        A[("Administrative database<br/>locations, users, facilities")]
        B[("FHIR server database<br/>clinical resources")]
    end

    subgraph ETL["Reporting ETL (scheduled batch)"]
        C["1. Sync reference data"]
        D["2. Build in-memory lookups"]
        E["3. Snapshot FHIR window per resource type"]
        F["4. Transform to flat rows"]
        G["5. Insert and advance watermarks"]
        C --> D --> E --> F --> G
    end

    subgraph RDB["Reporting database"]
        H["core: administrative and reference"]
        I["report: RMNCH forms"]
        J["ncd: NCD facts and program indicators"]
    end

    A --> C
    B --> E
    G --> H
    G --> I
    G --> J

    H --> K["Reporting web app and exports"]
    I --> K
    J --> K

    subgraph Optional["Optional large-scale analytics stack"]
        L["Orchestrator"]
        M["Batch processing engine"]
        N[("Object storage: open table format<br/>raw, cleaned, curated layers")]
        O["Distributed SQL query engine"]
        P["BI dashboards"]
        L --> M --> N --> O --> P
    end

    A -.-> M
    B -.-> M
```

## Reporting ETL

### Sources

| Source                  | Content                                                                                                                                                                                                                                                                                           |
| ----------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Administrative database | Location hierarchy (country down to the smallest community unit), facilities, users, roles and their assignments, community health worker catchments.                                                                                                                                             |
| FHIR server database    | Clinical transactions as FHIR R4 resources: encounters (every visit or form submission), observations (vitals, glucose, symptoms), conditions (diagnoses), medication requests and dispenses, immunizations, households (groups) and household members (related persons), and provenance records. |

The ETL reads each resource's FHIR JSON directly from the FHIR server's storage and extracts fields in code. It does not depend on the FHIR server's internal relational layout beyond the resource payload and the version history needed for incremental reads.

### What it produces

The target is one reporting database with three schema groups:

| Group                   | Purpose                                                      | Typical content                                                                                                                                                                                                                                                                         |
| ----------------------- | ------------------------------------------------------------ | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `core` (administrative) | Reference data shared by every report                        | Location tree and levels, facilities, users, roles, and user-to-location assignments, plus the ETL's own progress table.                                                                                                                                                                |
| `report` (RMNCH)        | Flat, denormalized, one table per form or event type         | Households, members, antenatal and postnatal visits, pregnancy outcomes, child follow-up and referral, family-planning registration, eye tests, immunizations, and a unified field-service activity table. Every row carries the full geographic hierarchy and provider metadata.       |
| `ncd` (NCD)             | Non-communicable disease facts, reference data and snapshots | Screening, blood pressure and glucose logs, assessments, patient and patient-tracker records, medical reviews, confirmed diagnoses, prescriptions, eye and cataract details, call register, facility and user reference tables, cohort snapshots, and monthly program-indicator tables. |

Some table definitions exist without a loader (for example some facility-level registers and operational forms). Treat those as empty until a release note says otherwise.

The `report` and `ncd` groups follow a layout originally designed for a downstream analytics pipeline. If you maintain your own warehouse or dashboards, plan to read these tables as a stable, flat interface.

### Incremental processing

The ETL does not rescan history on each run.

1. Every FHIR resource write creates a version row with a globally increasing identifier. The ETL tracks progress as the highest identifier it has processed for each resource type (a **watermark**), not a per-resource version number, which restarts at 1 for each resource.
2. At the start of a run it snapshots the current maximum identifier per resource type, so every transformer in the run sees the same window even while new data keeps arriving.
3. It streams only rows between the stored watermark and the snapshot, in bounded batches, so memory use is proportional to the batch rather than the window.
4. A watermark advances only if every transformer that consumes that resource type succeeded. If one fails, the next run reprocesses the same window.

Observations are loaded only for the encounters admitted by each transformer, not as a whole-table read.

Watermarks are kept separately for RMNCH and NCD processing, so an NCD backfill cannot cause RMNCH data to be skipped, and per district, so each district has its own cursor. A new district must be registered with a full set of watermark entries before it is processed.

### Per-area processing

A run executes in dependency order:

1. **Administrative sync.** Locations, facilities, users, roles and assignments are upserted into `core`. This step is idempotent.
2. **NCD reference sync.** Facility, account, user and call-register reference tables for the NCD group.
3. **Shared lookups.** Geography, community health worker catchments, roles, users, household members and provenance are loaded once and shared.
4. **RMNCH.** Households, then members, then encounter-driven forms (antenatal, postnatal, pregnancy outcome, child, family planning, eye test, field-service activity), then immunizations.
5. **NCD.** Blood pressure, glucose, screening, assessments, eye and cataract details, medical reviews, patient tracker, patient, confirmed diagnoses and prescriptions.
6. **NCD snapshots and indicators.** After all NCD source tables succeed, the ETL appends the current diabetes and hypertension cohort snapshots and rebuilds the current month's program-indicator rows. See [Standard Indicators](https://spice.docs.medtroniclabs.org/reporting-and-data/standard-indicators.md).

Encounters are routed to tables by two identifiers: the clinical area and the form or workflow type.

Several attributes are resolved during transformation:

* **Geography.** The encounter's community-unit identifier is mapped to the full hierarchy.
* **Provider.** The provider is resolved from the provenance record.
* **Facility-based NCD visits.** These may not resolve to a community unit and fall back to the facility's own geography.

### Scheduling

The ETL is a batch job intended to run once per day, typically overnight. The default run does administrative and reference sync, then RMNCH for every registered district, then NCD for every registered district. It exits with:

| Exit code | Meaning                                                                                                           |
| --------- | ----------------------------------------------------------------------------------------------------------------- |
| 0         | All transformers succeeded and watermarks advanced.                                                               |
| 1         | The run completed but one or more transformers failed. Watermarks for the affected resource types were held back. |
| 2         | A fatal error (configuration or connectivity) stopped the run.                                                    |

Any scheduler that can run a container or a Python process on a timer is suitable.

### Run modes

| Mode                | What it does                                                                                  |
| ------------------- | --------------------------------------------------------------------------------------------- |
| Default (nightly)   | Administrative and reference sync, RMNCH, then NCD.                                           |
| Dry run             | Reads and shapes rows but writes nothing and advances no watermarks. Use it to check volumes. |
| Administrative only | Refreshes the `core` group only.                                                              |
| NCD only            | Refreshes NCD reference and NCD facts only. Useful when tuning NCD tables or backfilling.     |
| Report seed         | One-time initial load of RMNCH data from a start date, requiring empty RMNCH watermarks.      |
| District filter     | Limits RMNCH and NCD processing to selected districts. Administrative sync is not filtered.   |
| Date floors         | Drop rows whose clinical date is before a cut-off, for RMNCH and NCD separately.              |

Database schema changes ship as migration files that the ETL applies once at startup and records.

### Refreshing, backfilling and monitoring

* **Frequency.** Daily is sufficient for the dashboards and indicators in this documentation. Running more often is safe because processing is incremental.
* **Re-processing a window.** Lower the relevant watermark for the affected district, run a dry run to confirm volume, then run for that district. Rewind RMNCH encounter and observation watermarks together. Rewinding is a data-team action. A full rebuild means dropping and recreating a schema group, then reloading from scratch.
* **New districts.** Register the district's watermarks, then run. An initial load can be date-seeded.
* **Monitoring.** Alert on three things: the job's final summary line not appearing within the expected window, the newest clinical event date in a high-volume table lagging by more than about a day and a half, and a watermark whose last-updated time stops moving while others move (which points to one stuck transformer, not a dead scheduler).
* **Duplicates.** Re-runs replace rows by a submission identifier, and unique keys on the entity and date prevent most duplicates. A resubmitted form with a new identifier is resolved by the entity key, and the first row wins.

### Data quality and quirks for implementers

| Topic                        | What to expect                                                                                                                                                                                                             |
| ---------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Time zone                    | Clinical dates and stored visit timestamps are converted to a single fixed local offset and stored without a time zone. If a date appears off by a few hours, check for an unconverted UTC value.                          |
| Free-text dates              | Some pregnancy dates (last menstrual period, expected delivery) are free-text and may be `DD-MM-YYYY` or `YYYY-MM-DD` depending on whether the record came from migration or live entry. Detect the format before casting. |
| Variable-length readings     | Blood pressure carries a variable number of component readings per visit. Always iterate readings rather than assuming a fixed count.                                                                                      |
| Patient state                | Operational patient status (for example enrolled) lives on the related-person record, not the patient demographic record.                                                                                                  |
| Controlled/uncontrolled flag | For medical-review visits it is carried on the encounter itself, not on an observation.                                                                                                                                    |
| Identifier URLs              | Identifier system URLs are the configured base plus a suffix, with no separator added. Set the base to match your FHIR mapper.                                                                                             |
| Row counts vs. people        | RMNCH tables are visit-level, so counting rows counts visits. Count distinct members for people.                                                                                                                           |
| Not-yet-implemented tables   | Defined but empty tables exist. Do not build reports on them.                                                                                                                                                              |

## Optional large-scale analytics stack

The simple ETL is a single process writing to one relational database. That is the recommended default. For national-scale volumes, multi-source analysis or self-service BI, SPICE also ships a **data platform** built from open-source components.

### Architecture

| Layer                    | Component                                      | Role                                                                                                             |
| ------------------------ | ---------------------------------------------- | ---------------------------------------------------------------------------------------------------------------- |
| Orchestration            | Apache Airflow                                 | Schedules and monitors the pipeline stages and refreshes views, and sends a status email at the end of each run. |
| Batch processing         | Apache Spark (run on a container orchestrator) | Extracts FHIR and administrative data and moves it between layers.                                               |
| Storage and table format | Object storage with Apache Iceberg tables      | Layered storage: raw ("bronze"), cleaned ("silver") and curated ("gold").                                        |
| Metadata                 | Hive Metastore                                 | Table catalog shared by the engines.                                                                             |
| Query engine             | Trino                                          | Distributed SQL over the curated layer, with materialized views refreshed at the end of the daily pipeline.      |
| BI                       | Apache Superset                                | Dashboards and charts on top of the query engine.                                                                |
| Ingestion (optional)     | Airbyte                                        | Additional source connectors.                                                                                    |

The daily run extracts FHIR resources to the raw layer, cleans them, flattens ("explodes") nested structures, applies transformations, loads administrative data through the same three layers, loads the curated tables, and finally refreshes the materialized views. A separate pipeline can refresh on a shorter interval during the day.

Table maintenance (compacting small files and expiring old snapshots) is part of running Iceberg at scale. Plan for a scheduled maintenance job.

### Choosing between the two

|                         | Reporting ETL                                                 | Analytics stack                                            |
| ----------------------- | ------------------------------------------------------------- | ---------------------------------------------------------- |
| Data volume             | Small to national, within what one relational database serves | Very large or fast-growing, or long retention              |
| Team and infrastructure | One scheduled job and one database                            | Orchestrator, Spark, object storage, query engine, BI tool |
| Data freshness          | Daily                                                         | Daily, or more often with the intraday pipeline            |
| Users                   | Reporting web app, exports, analysts with SQL                 | Self-service BI across many analysts and sources           |
| Cost and effort         | Low                                                           | Substantially higher                                       |

Start with the reporting ETL. Move to the analytics stack when query load, data volume or the number of BI users outgrows a single relational database. The two are not mutually exclusive: the stack reads the same sources and does not depend on the ETL's output tables.

## Related pages

* [Standard Indicators](https://spice.docs.medtroniclabs.org/reporting-and-data/standard-indicators.md)
* [Reporting Web Application](https://spice.docs.medtroniclabs.org/reporting-and-data/reporting-web-application.md)
* [Exports & National Reporting](https://spice.docs.medtroniclabs.org/reporting-and-data/exports-and-national-reporting.md)
