On-prem financial lakehouse

Decisions and trade-offs

1. Open engine over a proprietary one

   I chose an open catalog over Parquet, with embedded columnar-engine capability, instead of a proprietary data warehouse. Portable to any standard SQL engine, with no license dependency. The cost: it is 2025 technology, without the production track record of an established engine — exhaustive validation against the original core was not optional, it was how I compensated for that lack of track record.

2. The certificate bug, diagnosed down to the exact JOIN line

   The core's QRY_PAGARE view chains four INNER JOINs: principal, current interest, late interest, and shares. The last one filters on descripcion='APORTES' — a concept that does not exist in the loan portfolio, only in the deposits domain. Because it is an INNER JOIN, zero matches there collapse the entire result: the view always returns 0 rows, not intermittently. Any certificate issued down that path ends up based on incorrect data or is outright invalid — as the architecture document itself states, at critical severity. Around that bug, the cooperative reported from discovery that issuing a certificate took a team of 15 people up to 3 days — a figure the client related, not one the project measured, but consistent with the real size of that office (25 people). The fix was not a patch on the core —that would have meant touching production live—: it lives in the new Gold model (gold.datos_certificado), which removes the APORTES component from the JOIN (the social share belongs to deposits, not to the loan portfolio) and replaces the four INNER JOINs with a single SUM per concept over one pass of the fact table. It was validated against the 40 real concepts in the catalog (cat_con_descuento.psv), not against a sample — none of the 40 is an ordinary loan-portfolio social share.

3. A second validation, now in Module C: the balance left behind

   While building the Module C issuance engine, a second problem of a different nature surfaced: the core's administrative field VLR_SALDO_ACTUAL can drift out of date against the real balance. The calculation engine used it first, being the obvious field; a validation job cross-checking each result against the original core —to the cent— caught it before it reached production, the same day it was built. The fix: compute the analytical balance by summing the outstanding amounts directly from the fact table, filtered by principal, active status, and not sold, instead of reading the administrative field. It is the same discipline the first decision demands (nothing reaches production without being reproduced exactly against the core), applied twice in the same project, over two distinct bugs.

4. Classify by code, never by text

   The core filters financial concepts by text description (DESCRIPCION = 'CAPITAL') — brittle: if the core vendor renames a concept, the filter breaks silently, with no visible error. The new model classifies by a stable numeric code. Brittle text instead of a stable identifier: the exact root cause of the certificate bug, fixed in the design so it cannot happen again in any other report.

5. The module that stays empty, and is declared so, not hidden

   The deposits module (savings, shares, term deposits) is completely empty in the source used to design and validate. It is not a modeling error: it is a business data set that never arrived. The design prepares the data contract for that table but does not populate it — and meanwhile, the accounting balances of shares and savings per member are available, identified by the chart-of-accounts accounting class. The negative consequence, stated plainly: until the transactional deposits source exists, that view gives a partial picture of the member, not a complete one.