airajena opened a new issue, #101:
URL: https://github.com/apache/fineract-business-intelligence/issues/101
## Background
Today the BI pipeline has one way of getting data out of Fineract: a Python
extractor that polls
`last_modified_on_utc` on a schedule. That works well for one database and a
few hundred
thousand rows. It starts to hurt when you picture a bank with hundreds or
thousands of Fineract
databases:
- freshness is only as good as the polling interval (minutes to hours)
- hard deletes in the source are invisible to the extractor
- the raw tables are keyed by `id` only, so two databases that both have
loan 42 collide
- there is no safe way to move a source from one ingestion method to another
- nothing coordinates onboarding, scheduling, dbt runs or health checks
across many sources
## What I'd like to add
**1. Kafka CDC ingestion (Debezium + Kafka + a small Java sink)**
Debezium reads each source's PostgreSQL change log and publishes one Kafka
topic per table,
shared by all sources. A Java sink writes micro-batches into the warehouse
and stores its Kafka
offsets in the same transaction as the data, so a crash can neither lose nor
double-count rows.
Bad events go to a dead-letter topic instead of being dropped. Hard deletes
are kept as
soft-delete markers.
**2. A feature flag for Python vs Kafka, per source and table**
Ownership lives in `meta.ingestion_ownership`. Switching engines follows a
fixed procedure:
start a shadow copy with the new engine, run a row-by-row reconciliation
gate, then a second
person approves the cutover. Rollback is the same procedure in reverse.
Stale writers are
fenced off by an epoch the database itself checks, and every step is audited.
**3. One write path**
Both engines write through the same warehouse function
(`meta.apply_staged_batch`), so the raw
rows are identical whichever engine produced them. Raw tables are now
source-qualified
(`source_id`) and hash-partitioned.
**4. Airflow control plane**
DAGs for connector reconciliation, Python ingestion, dbt builds, freshness
monitoring, source
onboarding, the engine-switch workflow and daily checksum reconciliation.
Airflow does not
carry data, so if it goes down CDC keeps flowing.
**5. dbt and Superset changes to match**
- incremental models driven by a change log, so only loans that changed are
recomputed
- KPIs defined once as certified macros, with tests that the marts agree
- Superset row-level security keyed by (source, office), because office ids
repeat across banks
**6. Tooling**
A shared table contract, a `retire-source` command that removes a source
from every layer,
configurable heaps, partitions, threads and replicas through `.env`, and a
Java load
generator that checks the warehouse ends up exactly right.
## Testing
- Unit tests, integration tests, Java tests, and dbt tests pass.
- Python to Kafka cutover and rollback run end to end, with all 13 tables
matching in the gate.
- Load tests on a system with synthetic events, including
duplicates, reordering and deletes: exact results, about 8,000 events/s
sustained, p95 delay
around 1 second, and no data lost after a killed sink or a 30-second
warehouse outage.
## Dependencies
All new dependencies are ASF Category A (Apache-2.0, MIT or BSD). The Java
build enforces this
with a license gate.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
