airajena commented on code in PR #102:
URL:
https://github.com/apache/fineract-business-intelligence/pull/102#discussion_r4380519720
##########
dbt/models/marts/presentations/mart_repayment_behavior.sql:
##########
@@ -13,274 +13,271 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
-
+-- depends_on: {{ ref('int_change_window') }}
+-- depends_on: {{ ref('fact_loan_snapshot') }}
{{
config(
- materialized = 'incremental',
- unique_key = ['tenant_id', 'reporting_date', 'office_id',
'product_id', 'currency_code', 'client_hash'],
- incremental_strategy = 'delete+insert',
- on_schema_change = 'sync_all_columns',
+ materialized='incremental',
+ incremental_strategy='append',
+ on_schema_change='append_new_columns',
+ indexes=[{'columns': ['source_id', 'reporting_date']}, {'columns':
['reporting_date']}],
+ pre_hook=["{{ heavy_model_settings() }}", "{{
mart_delete_recompute_window('mart_repayment_behavior', 'reporting_date',
'changed_from') }}"]
)
}}
with loan_dimensions as (
select
+ l.source_id,
l.tenant_id,
l.loan_id,
l.product_id,
l.currency_code,
l.client_id,
c.client_hash,
c.office_id
- from {{ ref('stg_m_loan') }} l
- inner join {{ ref('stg_m_client') }} c
- on l.tenant_id = c.tenant_id
- and l.client_id = c.client_id
+ from {{ ref('stg_m_loan') }} as l
+ inner join {{ ref('stg_m_client') }} as c
+ on
+ l.source_id = c.source_id
+ and l.tenant_id = c.tenant_id
+ and l.client_id = c.client_id
),
-repayment_transactions as (
- select
- t.tenant_id,
- t.transaction_date as reporting_date,
- ld.office_id,
- ld.product_id,
- ld.currency_code,
- t.loan_id,
- ld.client_id,
- ld.client_hash,
-
- t.amount as
repayment_amount,
- t.principal_portion_derived as
principal_collected,
- t.interest_portion_derived as
interest_collected,
- t.fee_charges_portion_derived as fee_collected,
- t.penalty_charges_portion_derived as
penalty_collected,
- coalesce(t.overpayment_portion_derived, 0) as
overpayment_collected,
-
- coalesce(t.outstanding_loan_balance_derived, 0) as
post_transaction_outstanding_balance
- from {{ ref('stg_m_loan_transaction') }} t
- inner join loan_dimensions ld
- on t.tenant_id = ld.tenant_id
- and t.loan_id = ld.loan_id
- where t.transaction_type_enum = 2
- and t.is_reversed = false
+recompute as (
+ {% if is_incremental() %}
+ {{ mart_recompute_window('mart_repayment_behavior', 'changed_from') }}
+ {% else %}
+ select distinct
+ source_id,
+ office_id,
+ product_id,
+ date '1900-01-01' as recompute_from
+ from loan_dimensions
+ {% endif %}
),
-recovery_transactions as (
+transactions as (
select
+ t.source_id,
t.tenant_id,
- t.transaction_date as reporting_date,
- ld.office_id,
- ld.product_id,
- ld.currency_code,
- ld.client_hash,
t.loan_id,
- t.amount as recovery_amount
- from {{ ref('stg_m_loan_transaction') }} t
- inner join loan_dimensions ld
- on t.tenant_id = ld.tenant_id
- and t.loan_id = ld.loan_id
- where t.transaction_type_enum = 8
- and t.is_reversed = false
-),
-
-due_amounts as (
- select
- s.tenant_id,
- s.duedate as reporting_date,
+ t.transaction_type_enum,
+ t.transaction_date,
+ t.amount,
+ t.principal_portion_derived,
+ t.interest_portion_derived,
+ t.fee_charges_portion_derived,
+ t.penalty_charges_portion_derived,
+ t.overpayment_portion_derived,
+ t.outstanding_loan_balance_derived,
ld.office_id,
ld.product_id,
ld.currency_code,
- ld.client_hash,
-
- sum(
- coalesce(s.principal_amount, 0)
- + coalesce(s.interest_amount, 0)
- + coalesce(s.fee_charges_amount, 0)
- + coalesce(s.penalty_charges_amount, 0)
- ) as
contractually_due_amount,
-
- sum(coalesce(s.penalty_charges_amount, 0)) as
overdue_penalty_charged,
- sum(coalesce(s.penalty_charges_waived_derived, 0)) as
overdue_penalty_waived,
-
- sum(coalesce(s.principal_completed_derived, 0)) as
schedule_principal_received,
- sum(coalesce(s.interest_completed_derived, 0)) as
schedule_interest_received,
- sum(coalesce(s.fee_charges_completed_derived, 0)) as
schedule_fee_received,
-
- sum(
- coalesce(s.interest_waived_derived, 0)
- + coalesce(s.fee_charges_waived_derived, 0)
- + coalesce(s.penalty_charges_waived_derived, 0)
- ) as waived_amount,
-
- sum(coalesce(s.total_paid_in_advance_derived, 0)) as
paid_in_advance_amount,
- sum(coalesce(s.total_paid_late_derived, 0)) as
paid_late_amount,
-
- count(case
- when s.completed_derived
- and s.obligations_met_on_date is not null
- and s.obligations_met_on_date < s.duedate
- then 1
- end) as
early_payment_count,
-
- count(case
- when s.completed_derived
- and s.obligations_met_on_date is not null
- and s.obligations_met_on_date = s.duedate
- then 1
- end) as
on_time_payment_count,
-
- count(case
- when s.completed_derived
- and s.obligations_met_on_date is not null
- and s.obligations_met_on_date > s.duedate
- then 1
- end) as
late_payment_count,
-
- count(case when s.is_re_aged = true then 1 end) as
restructured_installment_count,
-
- count(*) as
total_installments_due
- from {{ ref('stg_m_loan_repayment_schedule') }} s
- inner join loan_dimensions ld
- on s.tenant_id = ld.tenant_id
- and s.loan_id = ld.loan_id
- group by 1, 2, 3, 4, 5, 6
+ ld.client_id,
+ ld.client_hash
+ from {{ ref('stg_m_loan_transaction') }} as t
+ inner join loan_dimensions as ld
+ on
+ t.source_id = ld.source_id
+ and t.tenant_id = ld.tenant_id
+ and t.loan_id = ld.loan_id
+ inner join recompute as w
+ on
+ t.source_id = w.source_id
+ and ld.office_id is not distinct from w.office_id
+ and ld.product_id is not distinct from w.product_id
+ and t.transaction_date >= w.recompute_from
+ where t.is_reversed = false
),
repayment_aggregated as (
select
+ source_id,
tenant_id,
- reporting_date,
+ transaction_date as reporting_date,
office_id,
product_id,
currency_code,
client_hash,
-
- count(*) as
repayment_transaction_count,
- count(distinct loan_id) as
repaid_loan_count,
- count(distinct client_id) as
repaying_borrower_count,
-
- sum(repayment_amount) as
repayment_amount,
- sum(principal_collected) as
principal_collected,
- sum(interest_collected) as
interest_collected,
- sum(fee_collected) as fee_collected,
- sum(penalty_collected) as
penalty_collected,
- sum(overpayment_collected) as
overpayment_collected,
- sum(post_transaction_outstanding_balance) as
post_transaction_outstanding_balance
- from repayment_transactions
- group by 1, 2, 3, 4, 5, 6
+ count(*) as repayment_transaction_count,
+ count(distinct loan_id) as repaid_loan_count,
+ count(distinct client_id) as repaying_borrower_count,
+ sum(amount) as repayment_amount,
+ sum(principal_portion_derived) as principal_collected,
+ sum(interest_portion_derived) as interest_collected,
+ sum(fee_charges_portion_derived) as fee_collected,
+ sum(penalty_charges_portion_derived) as penalty_collected,
+ sum(coalesce(overpayment_portion_derived, 0)) as overpayment_collected,
+ sum(coalesce(outstanding_loan_balance_derived, 0)) as
post_transaction_outstanding_balance
+ from transactions
+ where transaction_type_enum = 2
+ group by 1, 2, 3, 4, 5, 6, 7
),
recovery_aggregated as (
select
+ source_id,
tenant_id,
- reporting_date,
+ transaction_date as reporting_date,
office_id,
product_id,
currency_code,
client_hash,
- sum(recovery_amount) as
recovery_repayment_amount,
- count(distinct loan_id) as
recovery_loan_count
- from recovery_transactions
- group by 1, 2, 3, 4, 5, 6
+ sum(amount) as recovery_repayment_amount,
+ count(distinct loan_id) as recovery_loan_count
+ from transactions
+ where transaction_type_enum = 8
+ group by 1, 2, 3, 4, 5, 6, 7
),
-combined as (
+due_amounts as (
select
- coalesce(ra.tenant_id, da.tenant_id) as tenant_id,
- coalesce(ra.reporting_date, da.reporting_date) as reporting_date,
- coalesce(ra.office_id, da.office_id) as office_id,
- coalesce(ra.product_id, da.product_id) as product_id,
- coalesce(ra.currency_code, da.currency_code) as currency_code,
- coalesce(ra.client_hash, da.client_hash) as client_hash,
-
- coalesce(ra.repayment_transaction_count, 0) as
repayment_transaction_count,
- coalesce(ra.repaid_loan_count, 0) as
repaid_loan_count,
- coalesce(ra.repaying_borrower_count, 0) as
repaying_borrower_count,
- coalesce(ra.repayment_amount, 0) as
repayment_amount,
-
- coalesce(ra.repayment_amount, 0) as
actual_collected_amount,
- coalesce(da.contractually_due_amount, 0) as
contractually_due_amount,
-
- coalesce(ra.principal_collected, 0) as
principal_collected,
- coalesce(ra.interest_collected, 0) as
interest_collected,
- coalesce(ra.fee_collected, 0) as fee_collected,
- coalesce(ra.penalty_collected, 0) as
penalty_collected,
- coalesce(ra.overpayment_collected, 0) as
overpayment_collected,
- coalesce(ra.post_transaction_outstanding_balance, 0) as
post_transaction_outstanding_balance,
-
- coalesce(da.waived_amount, 0) as waived_amount,
- coalesce(da.paid_in_advance_amount, 0) as
paid_in_advance_amount,
- coalesce(da.paid_late_amount, 0) as
paid_late_amount,
- coalesce(da.early_payment_count, 0) as
early_payment_count,
- coalesce(da.on_time_payment_count, 0) as
on_time_payment_count,
- coalesce(da.late_payment_count, 0) as
late_payment_count,
- coalesce(da.restructured_installment_count, 0) as
restructured_installment_count,
- coalesce(da.overdue_penalty_charged, 0) as
overdue_penalty_charged,
- coalesce(da.overdue_penalty_waived, 0) as
overdue_penalty_waived,
- coalesce(da.total_installments_due, 0) as
total_installments_due
- from repayment_aggregated ra
- full outer join due_amounts da
- on ra.tenant_id = da.tenant_id
- and ra.reporting_date = da.reporting_date
- and ra.office_id = da.office_id
- and ra.product_id = da.product_id
- and ra.currency_code = da.currency_code
- and ra.client_hash = da.client_hash
+ s.source_id,
+ s.tenant_id,
+ s.duedate as reporting_date,
+ ld.office_id,
+ ld.product_id,
+ ld.currency_code,
+ ld.client_hash,
+ sum(
+ coalesce(s.principal_amount, 0) + coalesce(s.interest_amount, 0)
+ + coalesce(s.fee_charges_amount, 0) +
coalesce(s.penalty_charges_amount, 0)
+ ) as contractually_due_amount,
+ sum(coalesce(s.penalty_charges_amount, 0)) as overdue_penalty_charged,
+ sum(coalesce(s.penalty_charges_waived_derived, 0)) as
overdue_penalty_waived,
+ sum(
+ coalesce(s.interest_waived_derived, 0) +
coalesce(s.fee_charges_waived_derived, 0)
+ + coalesce(s.penalty_charges_waived_derived, 0)
+ ) as waived_amount,
+ sum(coalesce(s.total_paid_in_advance_derived, 0)) as
paid_in_advance_amount,
+ sum(coalesce(s.total_paid_late_derived, 0)) as paid_late_amount,
+ count(
+ case
+ when s.completed_derived and s.obligations_met_on_date <
s.duedate then 1
+ end
+ ) as early_payment_count,
+ count(
+ case
+ when s.completed_derived and s.obligations_met_on_date =
s.duedate then 1
+ end
+ ) as on_time_payment_count,
+ count(
+ case
+ when s.completed_derived and s.obligations_met_on_date >
s.duedate then 1
+ end
+ ) as late_payment_count,
+ count(case when s.is_re_aged then 1 end) as
restructured_installment_count,
+ count(*) as total_installments_due
+ from {{ ref('stg_m_loan_repayment_schedule') }} as s
+ inner join loan_dimensions as ld
+ on
+ s.source_id = ld.source_id
+ and s.tenant_id = ld.tenant_id
+ and s.loan_id = ld.loan_id
+ inner join recompute as w
+ on
+ s.source_id = w.source_id
Review Comment:
Thanks, that was my regression. The due_amounts join to the recompute window
now also matches office_id and product_id (null-safe), so schedule rows are no
longer repeated per office/product pair. I added
assert_repayment_due_matches_schedule, which compares the mart's
contractually_due_amount and installment count with the schedule table
directly, per source. It failed on the old join and passes now.
--
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]
