The analytics pipeline for Apache Fineract: the open-source core banking platform for financial inclusion.

This project reads data from a Fineract PostgreSQL database, transforms it through a layered dbt pipeline, and serves interactive dashboards in Apache Superset. It is designed to be downstream and separate from Fineract: the only connection is a read-only credential to the Fineract database. Everything else — the analytics warehouse, transformations, and dashboards — runs independently.

This project connects to a real Apache Fineract PostgreSQL database. It does not manage or start the Fineract application — you run Fineract separately and point this project at its database.

For local development, clone and run Fineract locally (see Setup below). In production, set the SOURCE_* environment variables to point at your existing Fineract PostgreSQL instance.

The extractor connects via a read-only credential (SOURCE_REPLICA_USER) created by bootstrap_source.sh. It never writes to Fineract.

┌─────────────────────────────────────────────────────────┐
│  Apache Fineract PostgreSQL  (your Fineract database)    │
│  fineract_default → bi_connector_source (read-only views)│
└───────────────────────────┬─────────────────────────────┘
                            │ read-only (fineract_reader)
                            ▼
┌─────────────────────────────────────────────────────────┐
│  Extractor  (Python)                                     │
│  Incremental watermark-based CDC                        │
│  → raw.raw_m_* tables in Analytics Warehouse            │
└───────────────────────────┬─────────────────────────────┘
                            │
                            ▼
┌─────────────────────────────────────────────────────────┐
│  Analytics Warehouse  (PostgreSQL)                       │
│  raw → staging (views) → facts (incremental) → marts    │
│  Transformation engine: dbt                             │
└───────────────────────────┬─────────────────────────────┘
                            │ read-only (analytics_reader)
                            ▼
┌─────────────────────────────────────────────────────────┐
│  Apache Superset  :8088                                  │
│  Row-level security — branch managers see their office   │
│  Admin sees all offices                                  │
└─────────────────────────────────────────────────────────┘

Pipeline loop (runs automatically inside the extractor container):

backfill once on startup
  └─► loop every PIPELINE_INTERVAL_SECONDS (default: 1 hour):
        1. extractor incremental  — pull changed rows from Fineract DB
        2. dbt build              — rebuild marts (only if step 1 succeeded)
        3. superset refresh       — sync chart metadata (only if step 2 succeeded)

No local Python, Java, or database tools required.

Clone the Fineract repository and start its PostgreSQL container:

git clone -b develop https://github.com/apache/fineract.git
cd fineract
$env:PWD = (Get-Location).Path.Replace('\', '/')
docker compose -f docker-compose-postgresql.yml up -d db

Wait ~10 seconds for the container to become healthy.

Fineract publishes a pre-built image to Docker Hub — no local build needed:

docker pull apache/fineract:latest
docker tag apache/fineract:latest fineract:latest

cd "C:\Users\<you>\Desktop\fineract"
$env:PWD = (Get-Location).Path.Replace('\', '/')
docker compose -f docker-compose-postgresql.yml up -d fineract

Watch the logs until Flyway finishes creating all m_* tables (2–5 minutes):

cd "C:\Users\<you>\Desktop\fineract"
$env:PWD = (Get-Location).Path.Replace('\', '/')
docker compose -f docker-compose-postgresql.yml logs -f fineract

Wait for this line then press Ctrl+C:

Started FineractApplication in X.XXX seconds

cd "C:\Users\<you>\Desktop\fineract"
$env:PWD = (Get-Location).Path.Replace('\', '/')
docker compose -f docker-compose-postgresql.yml exec db psql -U root -d fineract_default -c '\dt m_*'

You should see 100+ tables like m_loan, m_client, m_office, etc.

Load demo data into the Fineract DB. This gives you:

• 25 clients, 71 loans across 3 offices and 4 products
• Staggered vintages from 1 month to 36 months ago — enough history for trend charts
• PAR-30, PAR-60, PAR-90, and NPA loans — every delinquency bucket populated

Run from PowerShell (from the fineract-business-intelligence directory):

Get-Content "C:\Users\<you>\Desktop\fineract-business-intelligence\warehouse\seed\seed_fineract_source.sql" | docker exec -i fineract-db-1 psql -U root -d fineract_default

Skip this step if pointing at a real Fineract instance that already has data.

cd "C:\Users\<you>\Desktop"
git clone https://github.com/apache/fineract-business-intelligence.git
cd fineract-business-intelligence
Copy-Item .env.example .env

The defaults in .env work for local development without any edits.

One-time step. Run from Git Bash inside the fineract-business-intelligence directory:

bash scripts/bootstrap_source.sh

This creates the bi_connector_source schema with compatibility views on the Fineract DB and grants read-only access to fineract_reader. Safe to re-run — all operations are idempotent.

Expected output:

[bootstrap-source] Connection OK
[bootstrap-source] Compatibility views created in schema 'bi_connector_source'
[bootstrap-source] Creating replica user if not exists...
[bootstrap-source] Read access granted to 'fineract_reader'
[bootstrap-source] === Source bootstrap complete. You can now run the pipeline. ===

cd "C:\Users\<you>\Desktop\fineract-business-intelligence"
docker compose up -d warehouse superset dbt extractor

This starts 4 services:

The extractor waits 30 seconds for Superset to initialise, runs a full backfill, then loops every hour. Watch it:

docker compose logs -f extractor

Wait for:

Done. PASS=81 WARN=0 ERROR=0
[pipeline] Pipeline run complete in Xs

http://localhost:8088

Navigate to Dashboards → Portfolio Health and Delinquency & PAR.

The extractor runs the full pipeline every hour automatically. No action needed.

Force an immediate update instead of waiting for the next hour:

docker compose restart extractor

docker compose logs --tail=30 extractor   # check current status first

Then from Git Bash:

docker exec fineract-bi-extractor bash -c "bash /app/scripts/run_pipeline.sh backfill"

docker compose logs --tail=30 warehouse superset dbt extractor

What to look for:

Fineract DB and the BI stack are separate — restart both:

# 1. Restart Fineract DB
cd "C:\Users\<you>\Desktop\fineract"
$env:PWD = (Get-Location).Path.Replace('\', '/')
docker compose -f docker-compose-postgresql.yml up -d db fineract

# 2. Restart BI stack
cd "C:\Users\<you>\Desktop\fineract-business-intelligence"
docker compose up -d warehouse superset dbt extractor

No need to re-run bootstrap or re-seed — data is persisted in Docker volumes.

Set these in .env:

SOURCE_DB_HOST=<your-fineract-db-host>
SOURCE_DB_HOST_PORT=5432
SOURCE_DB_NAME=fineract_default
SOURCE_BOOTSTRAP_USER=<admin-user>
SOURCE_BOOTSTRAP_PASSWORD=<secret>
SOURCE_REPLICA_USER=fineract_reader
SOURCE_REPLICA_PASSWORD=<secret>
SOURCE_DB_SCHEMA=bi_connector_source

Run bootstrap once against the remote database (from Git Bash):

bash scripts/bootstrap_source.sh

Then start the BI stack — it connects to the remote Fineract DB directly via SOURCE_DB_HOST.

# Generate with: python -c "import secrets; print(secrets.token_hex(32))"
SUPERSET_SECRET_KEY=<64-char-hex>

# Run pipeline daily after COB completes
PIPELINE_INTERVAL_SECONDS=86400

# All passwords via your secrets manager
WAREHOUSE_ADMIN_PASSWORD=<secret>
WAREHOUSE_LOADER_PASSWORD=<secret>
WAREHOUSE_READER_PASSWORD=<secret>
SUPERSET_ADMIN_PASSWORD=<secret>
SUPERSET_NORTH_MANAGER_PASSWORD=<secret>
SUPERSET_SOUTH_MANAGER_PASSWORD=<secret>

fineract_default.public.*          Fineract source tables
        │
        │  bi_connector_source.*   Compatibility views (bootstrap_source.sh)
        │                          Normalises schema differences across Fineract versions
        │
        ▼
raw.raw_m_*                        Raw layer — exact copy of source rows
                                   + tenant_id + source_loaded_at
        │
        ▼
staging.stg_m_*                    Staging views — rename columns, cast types,
                                   drop PII (date_of_birth → age_band),
                                   add pseudonymous client_hash
        │
        ▼
analytics.fact_loan_snapshot       Daily grain: one row per (loan, date)
analytics.fact_delinquency_event   One row per delinquency tag lifecycle event
        │
        ▼
analytics.mart_portfolio_health    Grain: office × product × currency × date
analytics.mart_delinquency_par     Grain: office × product × bucket × date

• date_of_birth is dropped in stg_m_client and replaced with age_band (6 cohorts)
• client_id is replaced downstream by client_hash = MD5(tenant_id || '::' || id)
• All presentation marts are aggregated at office × product level — no individual client rows reach Superset
• Row-level security in Superset restricts branch managers to their own office data

The extractor tracks a per-table last_modified_on_utc cursor in meta.watermarks. Each incremental run fetches only rows changed since the last successful extraction. A 10-minute lookback window (EXTRACT_LOOKBACK_SECONDS=600) handles clock skew and late-arriving updates.

fineract-business-intelligence/
├── compose.yaml                    Docker Compose — 4-service BI stack
├── .env.example                    Environment template (copy to .env)
│
├── scripts/
│   ├── common.sh                   Shared helpers (docker checks, env loading)
│   ├── bootstrap_source.sh         One-time: create views + grants on Fineract DB
│   └── run_pipeline.sh             Full pipeline: extractor → dbt → superset
│
├── extractor/                      Python ETL service
│   ├── cli.py                      Entry point: backfill | incremental
│   ├── extractor.py                Extraction logic (11 tables, watermark-based)
│   ├── config.py                   Config from environment variables
│   └── watermark_manager.py        Per-table cursor tracking
│
├── dbt/                            dbt transformation project (fineract_bi)
│   ├── models/
│   │   ├── staging/                stg_* views (clean + rename)
│   │   └── marts/
│   │       ├── dimensions/         dim_office, dim_client, dim_product, …
│   │       ├── facts/              fact_loan_snapshot, fact_delinquency_event
│   │       └── presentations/      mart_portfolio_health, mart_delinquency_par
│   └── macros/
│       └── safe_divide.sql         NULL-safe division macro
│
├── warehouse/
│   ├── schema/                     DDL for raw, staging, mart, meta schemas
│   └── seed/
│       └── seed_fineract_source.sql  Demo data (25 clients, 71 loans)
│
└── docker/
    ├── postgres-warehouse/         Warehouse init scripts (roles, permissions)
    ├── extractor/                  Extractor Dockerfile (includes Docker CLI)
    ├── dbt/                        dbt Dockerfile (dbt-postgres pinned <2.0)
    └── superset/
        ├── Dockerfile
        ├── init_superset.sh        First-run: DB migrate, admin user, dashboards
        ├── refresh_superset_assets.sh
        ├── bootstrap_superset_assets.py
        └── superset_config.py

# Enter the dbt container
docker compose exec dbt bash

# Run all models
dbt build

# Run a specific model
dbt run --select mart_portfolio_health

# Run tests only
dbt test

# Full rebuild (drops and recreates incremental tables)
dbt build --full-refresh

1. Add SQL in the appropriate dbt/models/ subdirectory
2. Add schema tests in the corresponding _*.yml file
3. Add Apache license header to the SQL file
4. Run dbt build --select <your_model> to verify

• All source files must carry the Apache License 2.0 header
• SQL: snake_case identifiers, explicit column lists (no SELECT * in models)
• Python: type annotations, dataclasses for config, no hardcoded credentials
• Shell: set -euo pipefail, source common.sh for shared helpers

Bootstrap has not been run, or the Fineract DB was recreated. Re-run from Git Bash:

bash scripts/bootstrap_source.sh

Then restart the extractor:

docker compose restart extractor

The extractor connects to the Fineract DB container by name (fineract-db-1) on the shared Docker network. If your Fineract DB container has a different name, update SOURCE_DB_HOST in .env:

SOURCE_DB_HOST=<your-fineract-db-container-name-or-host>

The pipeline has not completed yet. Check:

docker compose logs --tail=30 extractor

Look for Pipeline run complete. If it shows ERROR, check the specific error and consult the relevant section below.

Force an immediate run:

docker exec fineract-bi-extractor bash -c "bash /app/scripts/run_pipeline.sh backfill"

dbt 2.0 dropped PostgreSQL support. The dbt image pin (dbt-postgres<2.0.0a1) should prevent this, but if it happens rebuild the image:

docker compose build dbt
docker compose up -d --force-recreate dbt

Duplicate rows in the incremental table from a previous run. Fix with a full-refresh of that model:

docker exec fineract-bi-dbt bash -c "cd /app/dbt && dbt build --full-refresh --select fact_delinquency_event"

Windows Git added CRLF line endings. Fix with:

sed -i 's/\r//' scripts/bootstrap_source.sh scripts/common.sh scripts/run_pipeline.sh

An init script has CRLF line endings. Fix and restart:

sed -i 's/\r//' docker/postgres-warehouse/initdb/002_create_warehouse_roles.sh

docker compose down -v
docker compose up -d warehouse superset dbt extractor

# Stop everything and remove volumes
docker compose down -v

# Restart from Step 8
docker compose up -d warehouse superset dbt extractor

docker compose down -v deletes all warehouse data and Superset metadata. The Fineract DB (managed separately) is unaffected. Re-run bootstrap (Step 7) before starting the BI stack again.

Apache License 2.0 — see LICENSE.

This project is part of the Apache Fineract ecosystem, started as part of Google Summer of Code 2026.