airajena commented on code in PR #102: URL: https://github.com/apache/fineract-business-intelligence/pull/102#discussion_r4318490804
########## dbt/models/intermediate/int_snapshot_scope.sql: ########## @@ -0,0 +1,145 @@ +-- Licensed to the Apache Software Foundation (ASF) under one or more +-- contributor license agreements. See the NOTICE file distributed with +-- this work for additional information regarding copyright ownership. +-- The ASF licenses this file to You under the Apache License, Version 2.0 +-- (the "License"); you may not use this file except in compliance with +-- the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, + +{{ config( + materialized='table', + indexes=[{'columns': ['source_id', 'tenant_id', 'loan_id'], 'unique': True}], + post_hook=['analyze {{ this }}'] +) }} + +{% set fact = adapter.get_relation(database=target.database, schema='analytics', identifier='fact_loan_snapshot') %} + +with change_window as ( + select * from {{ ref('int_change_window') }} +), + +loans as ( + select + source_id, + tenant_id, + loan_id, + client_id, + disbursedon_date, + coalesce(maturedon_date, current_date) as effective_maturity_date + from {{ ref('stg_m_loan') }} + where disbursedon_date is not null +), + +changes as ( + select c.* + from {{ source('meta', 'entity_change_log') }} c + cross join change_window w + where + not w.full_rebuild + and c.change_id > w.last_change_id + and c.change_id <= w.max_change_id +), + +fact_max as ( + {% if fact is not none %} + select + source_id, + max(snapshot_date) as max_date + from {{ fact }} + group by source_id + {% else %} + select + null::text as source_id, + null::date as max_date + where false + {% endif %} +), + +scope_rows as ( + select + l.source_id, + l.tenant_id, + l.loan_id, + date '1900-01-01' as from_date, + false as is_roll_forward + from loans l + cross join change_window w + where w.full_rebuild + + union all + + select + c.source_id, + c.tenant_id, + c.entity_id as loan_id, + c.min_affected_date as from_date, Review Comment: Fixed. The change log now records LEAST(old, new) from the pre-image. The fact pre-hook also deletes rows dated before disbursedon_date for loans in scope. The repayment mart's incremental window now matches its full-refresh start, and the equivalence check confirms incremental equals full refresh. -- 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]
