Lending data warehouse architecture shows what each decision used
The internal auditor asks the data team for the income used when a loan was approved in March. The analyst joins the loan to the customer’s income in the warehouse and sends back a single figure: income that arrived in June. The request is about what the decision used at the time, but the query has returned the value attached to that customer later on. You are responsible for the warehouse risk, finance and audit read from, and the figure is now in the auditor’s hands. Which date should the query have used, when the income arrived or when the loan was approved?
Keep records that show what a decision used
You cannot recover what a lending decision saw by filtering on a single date. The warehouse needs both when a value was effective and when it arrived, plus a record written at the decision with the inputs and rule version actually used.
That is the architecture this article builds on the reason for keeping decision-time records in a lending warehouse. The four layers are landing, vault, marts and an outer area. Each preserves a different part of the answer.
Preserve source deliveries and their history
The landing area records each application form, payroll check, servicing update and core banking balance file, and when each arrived. Do not overwrite a delivery with a cleaned or corrected version. If a load needs to be rerun, or a source value is disputed, the original delivery remains available.
Then organize those deliveries in the vault. A hub holds the customer as a business key, a link holds the relationship between the customer and the loan, and a satellite holds the declared income and its history. Load satellite rows append-only, with the source, time loaded and date effective. The load time answers when the warehouse received a value. The effective date answers when it was true in the business.
The raw vault keeps granular source history with minimal transformation. The business vault applies business rules and adds structures that help downstream queries. A point-in-time table can help select relevant vault history for a particular time. It cannot supply the inputs or rule version used in a decision unless those were recorded at the time.
Set the fact grain before choosing dimensions
In the marts, declare what one row represents before choosing dimensions or measures. That definition is the grain. Keep different grains in separate fact tables, because a decision event, a month end and an application moving through milestones answer different questions.
Kimball Group describes three useful forms. A transaction fact records one row per decision. A periodic snapshot records one row per loan at each month end. An accumulating snapshot keeps one row per application and updates it as its milestones pass.
| Kind | One row | Written | Changes |
|---|---|---|---|
| Decision event | One decision | At the decision | No |
| Month end | One period close | At the close | Frozen, then restated beside the original if corrected |
| Application pipeline | One application | As milestones pass | Yes |
Keep dimension history with type 2 rows. A change adds a new row with a new surrogate key, and facts refer to the version in force at the fact’s date. A retroactive correction needs more than a new dimension row. The associated fact rows must also be restated.
Publish complete data sets for readers
The outer area is where live decisions and systems outside the warehouse read published values. A live decision can look up values by key, and outside systems can receive extracts.
Build the publication from the marts, replace the previous publication as a whole, and record when the new one was published. That way, a reader gets a complete published set rather than values from a mart partway through a rebuild.
Use both timelines to verify the decision record
The example is invented. An applicant declares monthly income of 4,000, effective from 20 February, in a delivery received on 2 March. A payroll check received on 25 March reports 2,600 for that same effective date. A later servicing delivery reports 3,400, effective from 1 June.
For the decision on 3 March, joining to current data returns 3,400. Filtering to information received before the decision returns 4,000, the value available to it. Asking what was effective on 3 March using today’s information returns 2,600.
These answers use two timelines. Effective time says when a value was true in the business. Load time says when the warehouse recorded it. Bitemporal history keeps both, so a query can distinguish what was known then from what is now understood to have been true then. Martin Fowler describes these as actual history and record history.
For the auditor’s question, the decision record is the authority on what the decision actually used. Look up the record for that decision and verify its stored income and affordability rule version. In this example, the record says the decision used 4,000 and rule version 12. Then reconcile those values to the vault by checking what was effective on the decision date and what had arrived before the decision ran. The vault explains the surrounding history. The decision record tells you what ran.
Keep the inputs, derived values, rule and strategy version, and outcome in that record, then load it back as a source. Those records also support replaying a strategy over past decisions, as described in champion challenger strategies.
Keep original and restated month ends
A month end also has a reported view and a later corrected view. In the example, the close for 31 March is frozen on 5 April at a balance of 2,950 and 0 days past due. A delivery on 12 April reverses a payment and gives a corrected balance of 3,120 and 12 days past due for that same month end.
Keep the frozen close and write the restated version beside it. Do not overwrite the value finance used when reading loan loss provisions and roll rates. The two versions answer different questions: what the close reported, and what it should have reported using later information.
Keep the risk view from origination readable for the life of each loan as well. IFRS 9 compares risk at a reporting date with risk at initial recognition when determining the loss allowance. If the origination view is replaced by current data, that comparison is no longer available from the warehouse history.
Answer the auditor with the decision record
For the March request, first establish whether the auditor wants what the decision saw or what is now understood to have been true on that date. The date filters help reconcile the history, but only the decision record establishes the inputs and rule version actually used.
- Land every delivery as received, with its source and receipt time, before cleaning or transformation.
- Load the vault append-only. Use hubs for business keys, links for relationships, and satellite rows that carry the effective date and load time.
- Write each decision’s inputs, derived values, rule and strategy version, and outcome when the decision runs. Load that record back as a source.
- Declare what one row represents in each fact table before choosing its dimensions or measures.
- Keep type 2 dimension versions under separate surrogate keys, and key facts to the version in force at the fact date. Restate associated facts when a type 2 attribute changes retroactively.
- Freeze each month end at a declared load cutoff. Put later restatements beside the original.
- Retain each loan’s risk view from origination for the life of the loan.
- Publish values and extracts from an outer area that is replaced as a whole, and record when each publication was made.
- For the auditor’s request, retrieve the decision record and verify its stored inputs and rule version. Reconcile the record to the vault using both the time received and the date effective, according to whether the question is what the decision saw or what was true then.
Questions about point in time warehouse history
What is a point-in-time table in a data vault?
It is a business-vault structure that helps downstream queries select relevant vault history for a point in time. It can help reconstruct the history available at a decision date, but the decision record is still needed to establish which inputs and rule version the decision actually used.
What does bitemporal mean in a data warehouse?
It means retaining both when a fact was true in the business and when the warehouse recorded it. In the example, use both timelines to distinguish the income available to the decision from the corrected view of income on that date.
What are the layers of a modern data warehouse?
The four layers are landing, vault, marts and an outer area. The vault includes raw and business vault structures, which preserve source history and apply business rules.
What is the difference between a data vault and a Kimball star schema?
A data vault organizes source history into hubs, links and satellites, with raw and business vault structures. Kimball marts organize facts and dimensions around a declared grain, such as one row per decision or one row per loan at month end.
What is the difference between a periodic snapshot and an accumulating snapshot?
A periodic snapshot records one row per loan at each month end. An accumulating snapshot keeps one row per application and updates it as milestones pass.
How do you correct a month end that was already published?
Keep the original month end and write the corrected version beside it rather than overwriting the published close. In the example, the 31 March balance changes from 2,950 to 3,120, and days past due change from 0 to 12.
Why does a lender need the credit risk seen at origination?
IFRS 9 compares risk at a reporting date with risk at initial recognition when determining the loss allowance. Keeping the origination view readable for the life of the loan preserves that comparison.