Objective: Consolidate PF data from 6 manufacturing plants into a
single unified reporting database.
1.1 High-Level Architecture
flowchart LR
subgraph SOURCE["SOURCE SYSTEMS"]
S1[("Satna")]
S2[("BJM")]
S3[("Chittore")]
S4[("Corporate")]
S5[("Durgapur")]
S6[("MPBPF")]
end
subgraph ETL["ETL LAYER"]
E1[Extract]
E2[Transform]
E3[Load]
end
subgraph TARGET["UNIFIED SCHEMA"]
T1[(UNI_PF)]
end
subgraph REPORT["APACHE SUPERSET"]
R1[Member 360]
R2[Contributions]
R3[Loans]
R4[Settlements]
end
S1 & S2 & S3 & S4 & S5 & S6 --> E1
E1 --> E2 --> E3 --> T1
T1 --> R1 & R2 & R3 & R4
1.2 Plant-wise Source Summary
| Plant | Schema | Key Tables | Status |
|---|---|---|---|
| Satna | DEVFND2025 | PFUND_CONT_MAS, CONTR_PFDTL, LOAN_APP, SETTLE_AC | ✅ DDL Available |
| BJM | DEVFND2025 | Same as Satna (shared schema) | ✅ Using Satna DDL |
| Chittore | DEVPFS | PFBAL, PAYYTDPF, LOANSMAS, VOUCHMAIN | ✅ DDL Available |
| Corporate | DEVFND2025 | Same as Satna (assumed) | ⚠️ Using Satna as Reference |
| Durgapur | DEVPAY0001 | PFBAL, PFTRN, PFMASTER | ⚠️ Metadata Only |
| MPBPF (Trust) | MPBPF | PF_MEMBER_MAS, PF_CONT, PF_NOMINEE_MAS | ✅ DDL Available |
📝 Notes on Source Schemas:
- BJM: Uses the same DEVFND2025 schema structure as Satna. Satna DDL will be used as reference.
- Corporate: Schema not available. Assuming same structure as Satna (DEVFND2025). Will be validated during discovery.
- Durgapur: Only metadata available (table names, column names). Detailed DDL pending. Will be mapped based on available metadata.
1.3 Low-Level Data Flow (ETL Detail)
flowchart TD
subgraph EXTRACT["EXTRACTION"]
EX1[DB Link to Satna]
EX2[DB Link to Chittore]
EX3[DB Link to MPBPF]
EX4[DB Link to Durgapur]
end
subgraph STAGE["STAGING"]
ST1[STG_MEMBER]
ST2[STG_CONTRIBUTION]
ST3[STG_LOAN]
end
subgraph TRANSFORM["TRANSFORMATION"]
TR1[Standardize Codes]
TR2[Generate Keys]
TR3[Validate DQ Rules]
end
subgraph LOAD["LOADING"]
LD1[MERGE Master Tables]
LD2[INSERT Transaction Tables]
end
EX1 & EX2 & EX3 & EX4 --> ST1 & ST2 & ST3
ST1 & ST2 & ST3 --> TR1 --> TR2 --> TR3
TR3 --> LD1 & LD2
