dbt Models

Turno LMS uses dbt with a three-layer architecture: RAW -> BASE -> CORE.

Setup

profiles.yml

Create ~/.dbt/profiles.yml:

fineract_engine:
  target: dev
  outputs:
    dev:
      type: postgres
      host: localhost
      port: 5432
      user: postgres
      password: <password>
      dbname: fineract
      schema: raw
      threads: 4

Commands

cd dbt

# Check connection
../venv/bin/dbt debug

# Run all models
../venv/bin/dbt run

# Run specific models
../venv/bin/dbt run --select BASE_ARTHMATE_NACH_STATUS_RERT CORE_TRANSACTION_DATA

# Full refresh (rebuild from scratch)
../venv/bin/dbt run --full-refresh

# Compile only (no DB changes)
../venv/bin/dbt compile

Model Layers

RAW (Sources)

Raw data loaded by csv_processor.py from S3 files and webhooks. Defined in dbt/models/raw/sources.yml.

Every payment file RAW table has the standardized 10-column schema:

Column	Type	Description
loan_id	text	Loan identifier
amount	numeric	Transaction amount
date	timestamp	Transaction date
status	text	Payment status
charge	numeric	Fees/charges
note	text	Remarks
payment_id	text	UTR/payment reference
sub_reference_id	text	UMRN/mandate reference
source_file	text	S3 key
lender	text	Lender name

BASE Models

Incremental models that clean, type-cast, and enrich RAW data.

Payment File BASE Models (10)

Model	Source	Special Processing
BASE_ARTHMATE_NACH_STATUS_RERT	arthmate NACH	Simple pass-through
BASE_DMI_NACH_STATUS_REPORT	DMI NACH	Simple pass-through
BASE_DMI_PAYMENT_REPORT	DMI Payment	Simple pass-through
BASE_GB_NACH_PAYMENT_REPORT	GB Cashfree	split_part(loan_id, '_', 1) + UMRN join
BASE_IKF_NACH_STATUS_REPORT	IKF NACH	Simple pass-through
BASE_SHIVALIK_NACH_STATUS_REPORT	Shivalik NACH	Simple pass-through
BASE_SHIVALIK_PAYMENT_REPORT	Shivalik Payment	Simple pass-through
BASE_UGRO_NACH_PAYMENT_REPORT	UGRO Cashfree	split_part(loan_id, '_', 1) + UMRN join
BASE_UGRO_RAZORPAY_NACH_STATUS_REPORT	UGRO Razorpay	split_part(loan_id, '_', 1)
BASE_VIVRITI_PAYMENT_REPORT	Vivriti settlement	split_part(loan_id, '_', 1)

Loan ID Parsing

GB and UGRO Cashfree files contain loan IDs embedded in Link ID values:

• GB00682_Apr26_NitinSingh -> GB00682
• TURNO01446_Apr26_Name -> TURNO01446

The BASE model uses split_part(loan_id, '_', 1) to extract the prefix.

UMRN Join

For GB and UGRO, when loan_id is blank (NACH mandate rows resolved via UMRN), the BASE model joins against RAW_UMRN_MASTER:

select
    coalesce(
        u.loan_id,                          -- UMRN master lookup
        split_part(r.raw_loan_id, '_', 1)   -- Parse Link ID prefix
    ) as loan_id
from raw r
left join "RAW"."RAW_UMRN_MASTER" u
    on r.sub_reference_id is not null
    and u.sub_reference_id = r.sub_reference_id

The join is conditional - if the UMRN master table doesn't exist yet, the model still works (skips the join).

Webhook BASE Models (5)

Dynamically discovered from RAW tables using the get_webhook_api_sources() Jinja macro:

Model	Source
BASE_DMI_RAZORPAY_API	DMI Razorpay webhooks
BASE_GB_CASHFREE_API	GB Cashfree webhooks
BASE_UGRO_CASHFREE_API	UGRO Cashfree webhooks
BASE_UGRO_RAZORPAY_API	UGRO Razorpay webhooks
BASE_VIVRITI_RAZORPAY_API	Vivriti Razorpay webhooks

LOS BASE Models (7)

Model	Source
BASE_CLIENT_INFO_LOS	Client create operations
BASE_CLIENT_UPDATE_INFO_LOS	Client update operations
BASE_LOAN_INFO_LOS	Loan create operations
BASE_LOAN_UPDATE_INFO_LOS	Loan update operations
BASE_LOAN_APPROVE_INFO_LOS	Loan approval operations
BASE_LOAN_DISBURSE_INFO_LOS	Loan disbursement operations
BASE_LOAN_DELETE_INFO_LOS	Loan deletion operations

CORE Models

CORE_TRANSACTION_DATA

The central model that unifies all payment sources into one table.

Sources combined:

• 10 payment file CTEs (from BASE NACH/payment models)
• Dynamic webhook CTEs (from BASE webhook models)

Key features:

• Deduplication via source_key = md5(lender + loan_id + date + amount)
• Reconciliation via recon_match_key matching NACH records against webhooks
• Status filtering - only rows with loan_external_id IS NOT NULL and transaction_date IS NOT NULL
• Recon status - matched (has webhook/processed match) or new (needs manual posting)

Recon-enabled lenders (have recon_source set):

• gb_nach - GB NACH
• ugro_nach - UGRO Cashfree NACH
• ugro_razorpay_nach - UGRO Razorpay settlement
• vivriti_nach - Vivriti settlement

Non-recon lenders (arthmate, dmi, ikf, shivalik) have recon_source = NULL and are auto-posted by the 30-minute scheduled job.

Other CORE Models

Model	Type	Description
CORE_TRANSACTION_DATA_PROCESSED	table (app-managed)	Tracks which payments have been posted to Fineract
CORE_LOAN_MASTER	table	One row per loan with lifecycle info
CORE_FINERACT_PENDING_TRANSACTIONS	view	Pending Fineract API calls
CORE_FINERACT_TRANSACTION_ARCHIVE	incremental	Completed Fineract API calls

Incremental Strategy

Pattern	Models	Filter
Payment files	10 BASE models	source_file NOT IN (processed files)
Webhook APIs	5 BASE models	id > max(id)
LOS operations	7 BASE models	id > max(id)
CORE_TRANSACTION_DATA	full table rebuild	No incremental - rebuilt every run

Running dbt in Production

dbt is triggered by:

1. 30-minute scheduled job - webhook_repayment_pipeline.py runs dbt before posting repayments
2. Operate pipeline - fineract_operate_pipeline.py runs dbt for specific BASE models
3. Recon refresh - POST /api/reconciliation/refresh triggers a dbt run
4. Manual - dbt run from the command line