Mortgage Origination & Servicing data model
Kimball warehouse for mortgage lending: an application accumulating snapshot, funded-loan origination, servicing-transaction ledger, monthly loan-performance/delinquency snapshots, and escrow disbursements over conformed borrower, property, loan, investor, and delinquency-status dimensions.
Residential mortgage analytics across the full lifecycle: application and underwriting, funded-loan origination, servicing transactions, monthly performance and delinquency snapshots, and escrow disbursements — a Kimball star schema with conformed borrower, property, and loan dimensions supporting pull-through, HMDA reporting, delinquency-roll, and investor analysis with regulated PII (SSN) masked. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 13
- tables
- 118
- columns
- 20
- relationships
- 8
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Application as an accumulating snapshot
An application moves through submission → processing → underwriting → decision with milestone dates; an accumulating snapshot measures pull-through and time-to-decision.
Monthly performance snapshot for delinquency
Delinquency status and balances are evaluated monthly for investor and regulatory reporting; a periodic snapshot enables delinquency-roll and transition-matrix analysis.
HMDA action-taken on the application fact
HMDA requires reporting the action taken (originated/denied/withdrawn) and denial reason; capturing them on the application fact makes LAR submission a direct query.
Full masking on SSN
RESPA/GLBA require restricting access to borrower SSNs; classified restricted and fully masked.
Tables in the Mortgage Origination & Servicing model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (5)
fct_application
Grain: one row per application (accumulating snapshot)
Accumulating snapshot through the application funnel (pull-through).
| Column | Type | Keys |
|---|---|---|
| application_key | bigint | PK |
| date_key | bigint | |
| borrower_key | bigint | |
| property_key | bigint | |
| loan_officer_key | bigint | |
| channel_key | bigint | |
| decision_date_key | bigint | |
| application_id | varchar | |
| requested_amount | decimal | |
| ltv_ratio | decimal | |
| dti_ratio | decimal | |
| application_status | varchar | |
| hmda_action_taken | varchar | |
| denial_reason | varchar | |
| days_to_decision | integer |
fct_loan_origination
Grain: one row per funded loan
Funded-loan fact — booking-grain origination measures.
| Column | Type | Keys |
|---|---|---|
| loan_origination_key | bigint | PK |
| date_key | bigint | |
| loan_key | bigint | |
| borrower_key | bigint | |
| property_key | bigint | |
| investor_key | bigint | |
| original_balance | decimal | |
| note_rate | decimal | |
| ltv_ratio | decimal | |
| cltv_ratio | decimal | |
| dti_ratio | decimal | |
| discount_points | decimal | |
| closing_costs | decimal | |
| has_mortgage_insurance | boolean |
fct_servicing_transaction
Grain: one row per servicing transaction
Transaction-grain servicing ledger event.
| Column | Type | Keys |
|---|---|---|
| servicing_transaction_key | bigint | PK |
| date_key | bigint | |
| loan_key | bigint | |
| borrower_key | bigint | |
| transaction_id | varchar | |
| transaction_type | varchar | |
| transaction_amount | decimal | |
| principal_paid | decimal | |
| interest_paid | decimal | |
| escrow_paid | decimal | |
| fees_paid | decimal | |
| currency | varchar |
fct_loan_performance_snapshot
Grain: one row per loan per month (periodic snapshot)
Monthly performance snapshot driving delinquency-roll analysis.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| loan_key | bigint | |
| borrower_key | bigint | |
| delinquency_status_key | bigint | |
| investor_key | bigint | |
| scheduled_balance | decimal | |
| actual_balance | decimal | |
| current_interest_rate | decimal | |
| days_past_due | integer | |
| escrow_balance | decimal | |
| unpaid_principal_balance | decimal | |
| is_modified | boolean | |
| is_in_forbearance | boolean |
fct_escrow_disbursement
Grain: one row per escrow disbursement
Escrow disbursements for taxes and insurance.
| Column | Type | Keys |
|---|---|---|
| escrow_disbursement_key | bigint | PK |
| date_key | bigint | |
| loan_key | bigint | |
| disbursement_type | varchar | |
| payee_name | varchar | |
| disbursement_amount | decimal | |
| escrow_balance_after | decimal |
Dimension tables (5)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| month_number | smallint | |
| quarter | smallint | |
| year | smallint |
dim_borrower
Grain: one row per borrower per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| borrower_key | bigint | PK |
| borrower_id | varchar | BK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| ssn | varchar | PII |
| date_of_birth | date | PII |
| credit_score | smallint | |
| annual_income | decimal | |
| employment_status | varchar | |
| is_first_time_buyer | boolean | |
| citizenship_status | varchar | |
| varchar | PII |
dim_property
Grain: one row per property per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| property_key | bigint | PK |
| property_id | varchar | BK |
| address_line1 | varchar | PII |
| city | varchar | |
| state | varchar | |
| postal_code | varchar | PII |
| county | varchar | |
| property_type | varchar | |
| occupancy_type | varchar | |
| appraised_value | decimal | |
| purchase_price | decimal | |
| year_built | smallint |
dim_loan
Grain: one row per loan per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| loan_key | bigint | PK |
| loan_number | varchar | BK |
| loan_type | varchar | |
| loan_purpose | varchar | |
| lien_position | smallint | |
| term_months | smallint | |
| rate_type | varchar | |
| amortization_type | varchar | |
| interest_rate | decimal | |
| is_conforming | boolean |
dim_loan_officer
Grain: one row per loan officer
| Column | Type | Keys |
|---|---|---|
| loan_officer_key | bigint | PK |
| loan_officer_id | varchar | BK |
| officer_name | varchar | PII |
| nmls_id | varchar | |
| branch | varchar | |
| region | varchar |
Lookup tables (3)
dim_investor
Grain: one row per secondary-market investor
| Column | Type | Keys |
|---|---|---|
| investor_key | bigint | PK |
| investor_id | varchar | BK |
| investor_name | varchar | |
| investor_type | varchar |
dim_delinquency_status
Grain: one row per delinquency bucket
| Column | Type | Keys |
|---|---|---|
| delinquency_status_key | bigint | PK |
| status_code | varchar | BK |
| status_name | varchar | |
| days_past_due_min | smallint | |
| is_seriously_delinquent | boolean |
dim_channel
Grain: one row per origination channel
| Column | Type | Keys |
|---|---|---|
| channel_key | bigint | PK |
| channel_code | varchar | BK |
| channel_name | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_application.date_key → dim_date (many-to-one)
- fct_application.borrower_key → dim_borrower (many-to-one)
- fct_application.property_key → dim_property (many-to-one)
- fct_application.loan_officer_key → dim_loan_officer (many-to-one)
- fct_application.channel_key → dim_channel (many-to-one)
- fct_loan_origination.date_key → dim_date (many-to-one)
- fct_loan_origination.loan_key → dim_loan (many-to-one)
- fct_loan_origination.borrower_key → dim_borrower (many-to-one)
- fct_loan_origination.property_key → dim_property (many-to-one)
- fct_loan_origination.investor_key → dim_investor (many-to-one)
- fct_servicing_transaction.date_key → dim_date (many-to-one)
- fct_servicing_transaction.loan_key → dim_loan (many-to-one)
- fct_servicing_transaction.borrower_key → dim_borrower (many-to-one)
- fct_loan_performance_snapshot.date_key → dim_date (many-to-one)
- fct_loan_performance_snapshot.loan_key → dim_loan (many-to-one)
- fct_loan_performance_snapshot.borrower_key → dim_borrower (many-to-one)
- fct_loan_performance_snapshot.delinquency_status_key → dim_delinquency_status (many-to-one)
- fct_loan_performance_snapshot.investor_key → dim_investor (many-to-one)
- fct_escrow_disbursement.date_key → dim_date (many-to-one)
- fct_escrow_disbursement.loan_key → dim_loan (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.