When discussing data work with IT teams, the biggest challenge is often not writing SQL, connecting APIs, or building dashboards. The real challenge is usually much deeper: changing requirements, inconsistent definitions, unclear relationships between systems, and data structures that become harder to maintain as the business grows.

This problem appears in almost every industry, but it is especially visible in banking, lending, and collections.

A bank may have customer data in a CRM system, loan data in a core banking platform, repayment data in a payment system, hardship information in a case management platform, and collections activity in a collections system. Each system may describe the same customer or loan from a different angle.

For example, one team may define an "active customer" as a person with an open loan. Another team may define it as a customer with recent repayment activity. The collections team may only care about customers with overdue accounts. Finance may define revenue and loss differently from operations. Risk teams may use their own definition of delinquency, arrears, and exposure.

When management asks a simple question such as "How many customers are currently in arrears?" different reports may return different answers.

One report may count customers. Another may count accounts. Another may count loans. Another may exclude hardship customers. Another may include only accounts overdue by more than 30 days. Technically, each report may be correct according to its own logic, but the organisation still cannot agree on one trusted answer.

At first, these inconsistencies may be manageable. A few experienced staff understand the systems, manual adjustments, hidden rules, and exception cases. They know which tables to use, which joins are risky, and which reports should be treated as the source of truth.

But after a few months, requirements change. New dashboards are created. New staff take over. More tables are added. Business logic becomes scattered across SQL scripts, notebooks, dashboard calculations, Excel files, and pipeline transformations.

Eventually, no one can clearly answer basic questions:

• Where does this field come from?
• Which table should be used for reporting?
• How is this metric calculated?
• Why does this dashboard not match another dashboard?
• Can this data be reused for a new use case?
• Which source system is authoritative?

These issues may look like data quality problems, inefficient development, or poor collaboration. But fundamentally, they often come from the same root cause: the lack of a complete data organisation framework.

That framework is data modelling.

Data modelling is not simply about creating tables. It is about connecting business concepts, data structures, system logic, and rules in a way that is clear, reusable, and scalable. A good data model helps teams understand what the data means, where it comes from, how it should be used, and how it can support future business needs.

To make this practical, this article uses one end-to-end example: building a banking collections data model.

Table of Contents

• 1. The Example Scenario: Collections Analytics in Banking
• 2. What Is Data Modelling?
• 3. Main Types of Data Models
  • Conceptual Model
  • Logical Model
  • Physical Model
  • Analytical Model
• 4. Common Data modelling Methods
  • ER modelling
  • Dimensional Modelling
  • Normalised Modelling
  • Wide Table Modelling
  • Data Vault Modelling
• 5. End-to-End Example: Building a Collections Data Model
  • Step 1: Define the modelling Objective
  • Step 2: Identify Business Objects and Processes
  • Step 3: Define Entities, Attributes, and Relationships
  • Step 4: Design Metric Definitions
  • Step 5: Design Data Layers
  • Application Layer: Dashboards and Data Products
  • Step 6: Implement Data Integration and Pipelines
  • Step 7: Validate Data Quality
  • Step 8: Deploy, Govern, and Iterate
• 6. What the End Result Looks Like
• 7. Summary
• Reference

1. The Example Scenario: Collections Analytics in Banking

Imagine a bank that provides home loans and personal loans.

The organisation wants to improve its collections reporting and operations. It needs reliable data to answer questions such as:

• How many customers are currently overdue?
• What is the total outstanding balance in arrears?
• Which loans are 1–30, 31–60, 61–90, or 90+ days past due?
• Which customers are in hardship arrangements?
• Which customers have broken a repayment arrangement?
• Which accounts require collections action today?
• Which contact strategies are most effective?
• How much exposure exists by risk segment, product, branch, or region?
• Why do collections, finance, and risk reports show different numbers?

The data comes from multiple systems:

• A core banking system stores loans, balances, repayment schedules, interest, and fees.
• A CRM system stores customer details, contact preferences, and customer relationships.
• A payment system stores repayments, failed payments, direct debit results, and transaction history.
• A collections system stores cases, strategies, actions, notes, promises to pay, and arrangements.
• A hardship platform stores financial difficulty applications and assistance plans.
• A data warehouse stores historical snapshots and reporting-ready datasets.
• Spreadsheets may still be used for exception lists, manual adjustments, or operational tracking.

This sounds like a reporting problem, but before building dashboards, the organisation needs a reliable data model.

Without a model, each report may define "arrears", "delinquent customer", "active loan", "hardship", or "collections case" differently. Over time, this creates confusion, rework, and mistrust.

2. What Is Data Modelling?

Data modelling is the process of transforming real-world business objects, relationships, and rules into structured data that can be stored, processed, analysed, and reused.

In the banking collections example, the main business objects include:

• Customer
• Account
• Loan
• Product
• Property
• Repayment schedule
• Payment
• Arrears balance
• Delinquency status
• Collections case
• Collections action
• Promise to pay
• Repayment arrangement
• Hardship application
• Contact event
• Risk segment

These objects are connected by business relationships.

For example:

• A customer may have one or more loans.
• A loan belongs to one product type.
• A home loan may be secured by one or more properties.
• A loan has scheduled repayments.
• A customer makes payments against a loan.
• A missed payment may create arrears.
• An overdue loan may create a collections case.
• A collections case may have many actions and contact attempts.
• A customer may enter a hardship arrangement.
• A promise to pay may be linked to a future repayment.
• A broken promise may trigger a new collections action.

The organisation also needs clear business rules.

For example:

• What counts as an active loan?
• Should closed loans be included in historical reporting?
• How is days past due calculated?
• Is delinquency measured at customer level, account level, or loan level?
• Should hardship customers be included in collections performance reporting?
• How should partial repayments be treated?
• How should broken promises to pay be counted?
• Which balance should be used: current balance, overdue balance, exposure, or arrears balance?
• Which system owns the official repayment status?

Data modelling brings these objects, relationships, and rules into a structured design. This design may include tables, fields, keys, dimensions, facts, metrics, and lineage.

Many people think data modelling is the same as database design, but they are not identical.

Database design focuses mainly on the storage layer: how tables are created, how fields are defined, how indexes are used, and how performance is optimised.

Data modelling has a broader purpose. It ensures that data accurately represents business reality, supports analysis, maintains consistent definitions, and allows future expansion.

A good data model should answer four key questions:

1. What are the core business objects?

In this case: customers, loans, accounts, repayments, arrears, hardship cases, and collections activities.

1. How are these objects related?

For example: one customer can hold multiple loans, and one loan can have many repayment events and collections actions.

1. How are business metrics calculated?

For example: days past due, arrears balance, cure rate, roll rate, broken promise rate, and hardship volume.

1. How does data flow through the organisation?

For example: loan balances come from the core banking system, customer details come from CRM, repayment events come from payment systems, and collections actions come from the collections platform.

This is why data modelling is not only a technical activity. It is the foundation of data engineering, analytics, governance, and decision-making.

3. Main Types of Data Models

Data modelling is usually divided into several levels. Each level serves a different purpose and communicates with a different audience.

Using the collections example, we can see how each model works.

Conceptual Model

A conceptual model is closest to business language. It describes the core business objects and their relationships without going into database details.

For the collections example, a conceptual model may describe the business like this:

• Customers hold loans.
• Loans have scheduled repayments.
• Missed repayments create arrears.
• Arrears may trigger collections cases.
• Collections cases contain actions and contact attempts.
• Customers may make promises to pay.
• Customers may enter hardship arrangements.
• Repayments may cure overdue loans.
• Loans move between delinquency buckets over time.

At this stage, the team does not need to decide column types, partitions, table formats, or indexes. The purpose is to align business understanding.

Important questions include:

• Is a customer the same as an account holder?
• Can one loan have multiple borrowers?
• Should a guarantor be modelled as a customer?
• Is delinquency calculated at loan level or customer level?
• Is a hardship customer still considered delinquent?
• Should collections activity be linked to the customer, loan, account, or case?
• Which system owns the official loan status?
• Which system owns the official customer identity?

These questions need to be answered before technical design begins.

The conceptual model is useful for workshops between business users, data engineers, data architects, analysts, collections operations, finance, risk, and compliance teams.

Logical Model

A logical model adds more structure. It defines entities, attributes, relationships, keys, and business rules.

For the collections example, the logical model may include entities such as:

Customer

• Customer ID
• Customer type
• Customer status
• Date of birth or business registration date
• Contact preference
• Risk segment
• Created date

Loan

• Loan ID
• Customer ID
• Product ID
• Loan status
• Original loan amount
• Current balance
• Settlement date
• Maturity date
• Interest rate type

Repayment Schedule

• Schedule ID
• Loan ID
• Due date
• Scheduled amount
• Principal amount
• Interest amount
• Fee amount

Payment

• Payment ID
• Loan ID
• Payment date
• Payment amount
• Payment method
• Payment status

Arrears Snapshot

• Snapshot date
• Loan ID
• Days past due
• Overdue amount
• Current balance
• Delinquency bucket
• Arrears status

Collections Case

• Case ID
• Customer ID
• Loan ID
• Case status
• Open date
• Close date
• Case reason
• Assigned team

Collections Action

• Action ID
• Case ID
• Action type
• Action date
• Action outcome
• Agent ID

Promise to Pay

• Promise ID
• Case ID
• Promise date
• Promised amount
• Due date
• Promise status

Hardship Application

• Hardship ID
• Customer ID
• Loan ID
• Application date
• Hardship status
• Assistance type
• Start date
• End date

The logical model also defines relationships.

For example:

• Customer.Customer_ID links to Loan.Customer_ID.
• Loan.Loan_ID links to Payment.Loan_ID.
• Loan.Loan_ID links to Repayment_Schedule.Loan_ID.
• Loan.Loan_ID links to Arrears_Snapshot.Loan_ID.
• Collections_Case.Case_ID links to Collections_Action.Case_ID.
• Collections_Case.Case_ID links to Promise_To_Pay.Case_ID.
• Hardship_Application links to both customer and loan.

This step is especially important because banking relationships can be complex.

For example, one customer may have multiple loans. One loan may have multiple borrowers. A customer may be both a borrower on one loan and a guarantor on another. A collections case may be opened at customer level, but actions may need to be analysed at loan level.

If these relationships are not modelled clearly, reporting becomes unreliable.

Physical Model

A physical model is the actual implementation in a database, warehouse, or lakehouse. It focuses on technical design, including table names, column types, storage formats, partitions, and performance.

For a modern data platform, the physical model may use bronze, silver, and gold layers.

Example bronze tables:

• bronze.crm_customer_raw
• bronze.core_loan_raw
• bronze.core_repayment_schedule_raw
• bronze.payment_transaction_raw
• bronze.collections_case_raw
• bronze.collections_action_raw
• bronze.hardship_application_raw

Example silver tables:

• silver.customer
• silver.loan
• silver.repayment_schedule
• silver.payment
• silver.arrears_snapshot
• silver.collections_case
• silver.collections_action
• silver.promise_to_pay
• silver.hardship_application

Example gold tables:

• gold.fact_arrears_daily
• gold.fact_payment
• gold.fact_collections_action
• gold.fact_promise_to_pay
• gold.fact_hardship
• gold.dim_customer
• gold.dim_loan
• gold.dim_product
• gold.dim_date
• gold.dim_delinquency_bucket

Physical design decisions may include:

• Partition arrears snapshot tables by snapshot date.
• Store high-volume payment transactions in an efficient columnar format.
• Use incremental loading for daily loan and payment changes.
• Preserve historical snapshots for trend analysis.
• Use surrogate keys for reporting dimensions.
• Apply data quality checks before publishing gold tables.
• Optimise gold tables for dashboard performance.
• Restrict customer-level data based on privacy and access rules.

The physical model is the layer developers are most familiar with. However, if the conceptual and logical models are weak, the physical model can quickly become a collection of temporary tables, duplicated joins, and inconsistent business rules.

Analytical Model

An analytical model is designed for reporting, BI, dashboards, metric systems, and business analysis. It focuses less on how transactions occur and more on how data can be efficiently queried and understood.

For a collections dashboard, the analytical model may use fact and dimension tables.

A daily arrears fact table could include:

Fact Arrears Daily

• Snapshot date key
• Loan key
• Customer key
• Product key
• Delinquency bucket key
• Current balance
• Overdue amount
• Days past due
• Arrears flag
• Hardship flag
• Collections case flag

A collections action fact table could include:

Fact Collections Action

• Action date key
• Case key
• Customer key
• Loan key
• Action type
• Action outcome
• Contact channel
• Agent key

A promise-to-pay fact table could include:

Fact Promise to Pay

• Promise date key
• Due date key
• Customer key
• Loan key
• Promised amount
• Promise status
• Kept promise flag
• Broken promise flag

Dimension tables could include:

Dim Customer

• Customer key
• Customer ID
• Customer type
• Risk segment
• Region
• Customer status

Dim Loan

• Loan key
• Loan ID
• Product type
• Loan status
• Settlement date
• Interest rate type

Dim Delinquency Bucket

• Bucket key
• Bucket name
• Bucket order
• Minimum days past due
• Maximum days past due

Dim Date

• Date key
• Calendar date
• Month
• Quarter
• Year
• Financial year

This model allows business users to analyse collections performance by time, product, customer type, risk segment, delinquency bucket, and action outcome.

For example:

• Total arrears balance by month
• Number of loans in each delinquency bucket
• Roll rate from 30 days past due to 60 days past due
• Cure rate by product type
• Broken promise rate by contact strategy
• Hardship volume by customer segment
• Collections action effectiveness by channel

This is where the analytical model turns operational system data into business insight.

4. Common Data modelling Methods

There is no single modelling method that fits every situation. In real projects, teams often combine multiple methods depending on requirements, system complexity, and analytical needs.

A banking collections platform may use several methods at the same time.

ER modelling

Entity-Relationship modelling focuses on entities, attributes, and relationships. It is commonly used for business system database design.

In the collections example, ER modelling is useful for understanding operational relationships such as:

• Customer to loan
• Loan to repayment schedule
• Loan to payment
• Loan to arrears snapshot
• Customer to hardship application
• Loan to collections case
• Collections case to collections action
• Collections case to promise to pay

The advantage of ER modelling is that it clearly represents business logic. It is especially useful when designing source-aligned tables, master data structures, and integration models.

However, ER models can become difficult to query directly for reporting. If a dashboard needs arrears balance by customer segment, product, delinquency bucket, hardship status, and month, the query may require many joins across operational tables. This can reduce report development efficiency and increase the risk of inconsistent logic.

Dimensional Modelling

Dimensional modelling is widely used in data warehouses, lakehouses, and BI scenarios. Its core components are fact tables and dimension tables.

For the collections dashboard, dimensional modelling can be used to create reusable facts and dimensions:

• fact_arrears_daily
• fact_payment
• fact_collections_action
• fact_promise_to_pay
• fact_hardship
• dim_customer
• dim_loan
• dim_product
• dim_delinquency_bucket
• dim_date

This makes analysis easier.

Instead of forcing dashboard developers to repeatedly join raw loan records, payment transactions, customer tables, hardship cases, and collections actions, they can use business-ready fact and dimension tables.

Dimensional modelling is suitable for management dashboards, operational reporting, collections performance monitoring, and self-service analytics.

Normalised Modelling

Normalised modelling aims to reduce redundancy and improve consistency.

In the collections example, customer information should not be repeated in every loan, payment, and collections action record. Instead, customer details should be maintained in a customer table, and related records should reference the customer through a key.

This avoids unnecessary duplication and improves maintainability.

For example:

• Customer details are stored once in the customer table.
• Loan details are stored in the loan table.
• Payments are stored separately and linked to loans.
• Collections actions are stored separately and linked to cases.
• Hardship applications are stored separately and linked to customers and loans.

The advantages of normalized modelling are:

• Lower redundancy
• Better consistency
• Cleaner structure
• Stronger update control

The disadvantage is that reporting may require multiple joins. Therefore, normalized models are often useful in silver layers, while dimensional or wide models are often more suitable for gold reporting layers.

Wide Table Modelling

Wide table modelling combines frequently used analytical fields into a single large table. This reduces the need for joins and makes data easier to use.

For a collections dashboard, a wide table may include:

• Snapshot date
• Customer ID
• Loan ID
• Product type
• Customer segment
• Loan status
• Current balance
• Overdue amount
• Days past due
• Delinquency bucket
• Hardship flag
• Open collections case flag
• Last contact date
• Last contact outcome
• Promise-to-pay status

This can be useful for fixed reports or operational dashboards where performance and ease of use are important.

However, wide tables also introduce redundancy. If delinquency rules, hardship logic, or customer segmentation changes, the wide table must be updated carefully.

Wide tables are useful for consumption, but they should be built from well-designed reusable models rather than replacing proper modelling altogether.

Data Vault Modelling

Data Vault modelling is designed for complex enterprise data warehouses. It focuses on scalability, traceability, and adaptability to change.

In a banking environment, Data Vault may be useful when integrating many source systems over a long period.

For example:

• A customer hub stores customer business keys.
• A loan hub stores loan business keys.
• A case hub stores collections case business keys.
• A customer-loan link stores borrower relationships.
• A loan-case link stores the relationship between loans and collections cases.
• A customer satellite stores customer attributes over time.
• A loan satellite stores balance, status, and product changes over time.
• A case satellite stores collections case attributes and status changes.

The advantage is stronger traceability and flexibility when source systems change. The trade-off is complexity. Data Vault requires clear standards, strong automation, and a mature team.

5. End-to-End Example: Building a Collections Data Model

Now let’s walk through the full modelling process from start to finish.

The organisation wants to build a trusted collections analytics model. The model must support operational dashboards, arrears monitoring, hardship reporting, collections strategy analysis, and management reporting.

The goal is not just to build one dashboard. The goal is to create a reusable data product.

Step 1: Define the modelling Objective

The first step is to clarify the purpose of the model.

For this example, the objective is:

Build a trusted collections analytics model that provides consistent arrears, delinquency, repayment, hardship, and collections activity metrics across the organisation.

This immediately tells the team that the model is not just a technical database structure. It must support business reporting, operational decision-making, and cross-team alignment.

The team should clarify:

• Which dashboards will use this model?
• Which teams will rely on the data?
• Is the model used for daily reporting, intraday monitoring, or both?
• How much historical arrears data should be retained?
• Who owns the definition of delinquency?
• Who owns the definition of hardship?
• Which users can see customer-level information?
• Which users should only see aggregated results?
• Which system is authoritative for customer, loan, payment, hardship, and collections data?

For this example, the agreed requirements are:

• Arrears snapshots refresh daily.
• Payment data refreshes multiple times per day.
• Customer-level access is restricted.
• The core banking system owns loan balance and repayment schedule.
• The payment system owns repayment events.
• The collections system owns case and action history.
• The hardship platform owns hardship status.
• The data warehouse owns standardised reporting metrics.

This step prevents the team from building a model that is technically correct but unsuitable for business use.

Step 2: Identify Business Objects and Processes

Next, the team maps the collections process.

A simplified process may look like this:

1. A customer takes out a loan.
2. The loan has scheduled repayments.
3. A repayment is missed or partially paid.
4. The loan becomes overdue.
5. Days past due and arrears balance are calculated.
6. The loan enters a delinquency bucket.
7. A collections case may be created.
8. The collections team contacts the customer.
9. The customer may make a promise to pay.
10. The customer may enter a hardship arrangement.
11. A repayment may cure the arrears.
12. The case may be closed or escalated.

From this process, the team identifies the main business objects:

• Customer
• Loan
• Repayment schedule
• Payment
• Arrears snapshot
• Delinquency bucket
• Collections case
• Collections action
• Promise to pay
• Hardship application

The team also identifies important business questions:

• What is an active loan?
• What is an overdue loan?
• How is days past due calculated?
• What is the official delinquency bucket?
• What is the difference between account-level and customer-level arrears?
• Should hardship customers be included in collections reporting?
• How should partial repayments be handled?
• How should promises to pay be tracked?
• When is a collections case considered open or closed?
• Which actions should count as successful contact?

After discussion with business and technical teams, the organisation agrees on example rules:

• An active loan must have an open status and outstanding balance.
• Days past due is calculated from the oldest unpaid due date.
• Delinquency buckets are grouped as current, 1–30, 31–60, 61–90, and 90+ days past due.
• Arrears amount is the total overdue scheduled amount not yet paid.
• Customers in hardship are flagged separately but not removed from arrears history.
• A promise to pay is considered broken if the expected payment is not received by the agreed date.
• A collections case is open until it is resolved, cured, transferred, or closed by business rule.
• Test accounts and migrated dummy records are excluded from operational reporting.

These rules are now part of the data model.

Step 3: Define Entities, Attributes, and Relationships

The team then converts business objects into entities.

A simplified logical model may look like this:

Customer

• Customer ID
• Customer type
• Customer status
• Risk segment
• Contact preference
• Created date

Loan

• Loan ID
• Customer ID
• Product ID
• Loan status
• Current balance
• Original loan amount
• Settlement date
• Maturity date

Repayment Schedule

• Schedule ID
• Loan ID
• Due date
• Scheduled amount
• Paid amount
• Outstanding amount
• Schedule status

Payment

• Payment ID
• Loan ID
• Payment date
• Payment amount
• Payment method
• Payment status

Arrears Snapshot

• Snapshot date
• Loan ID
• Days past due
• Delinquency bucket
• Overdue amount
• Current balance
• Hardship flag
• Collections case flag

Collections Case

• Case ID
• Customer ID
• Loan ID
• Case status
• Open date
• Close date
• Case reason
• Assigned team

Collections Action

• Action ID
• Case ID
• Action date
• Action type
• Contact channel
• Action outcome

Promise to Pay

• Promise ID
• Case ID
• Promise date
• Promised amount
• Promise due date
• Promise status

Hardship Application

• Hardship ID
• Customer ID
• Loan ID
• Application date
• Hardship status
• Assistance type
• Start date
• End date

The relationships are also defined:

• One customer can have many loans.
• One loan can have many repayment schedule records.
• One loan can have many payments.
• One loan can have one arrears snapshot per reporting date.
• One customer can have many hardship applications.
• One loan can have many collections cases over time.
• One collections case can have many actions.
• One collections case can have many promises to pay.

This step is where grain becomes critical.

For example, if one dashboard counts overdue loans and another counts overdue customers, both may be correct, but they answer different questions. A customer with three overdue loans should count as one overdue customer but three overdue loans.

If the model does not clearly define the grain of each table, reports will conflict.

A good data model should state:

• fact_arrears_daily is at loan per snapshot date grain.
• fact_payment is at payment transaction grain.
• fact_collections_action is at action event grain.
• fact_promise_to_pay is at promise event grain.
• dim_customer is at customer grain.
• dim_loan is at loan grain.

Clear grain prevents confusion.

Step 4: Design Metric Definitions

Next, the team defines official metrics.

This is one of the most important steps because inconsistent metrics are one of the main reasons dashboards do not match.

Example metrics include:

Active Loan Count

Count of loans with open status and outstanding balance as of the reporting date.

Overdue Loan Count

Count of active loans with days past due greater than zero.

Overdue Customer Count

Count of distinct customers with at least one overdue active loan.

Days Past Due

Number of days from the oldest unpaid scheduled repayment due date to the reporting date.

Arrears Balance

Sum of overdue scheduled amounts that remain unpaid as of the reporting date.

Delinquency Bucket

Grouping of loans based on days past due, such as current, 1–30, 31–60, 61–90, and 90+.

Cure Rate

Percentage of overdue loans that return to current status within a defined period.

Roll Rate

Percentage of loans moving from one delinquency bucket to a worse bucket over a defined period.

Broken Promise Rate

Percentage of promises to pay where the expected payment was not received by the agreed date.

Each metric should include:

• Business definition
• Calculation formula
• Data source
• Filters
• Time logic
• Owner
• Refresh frequency
• Exclusion rules

For example:

Days Past Due

• Owner: Collections operations and risk
• Source: repayment schedule and payment data
• Formula: reporting date minus oldest unpaid due date
• Time logic: daily snapshot date
• Exclusions: closed loans, test accounts, invalid schedules
• Refresh: daily
• Grain: loan per snapshot date

Broken Promise Rate

• Owner: Collections operations
• Source: collections system and payment system
• Formula: broken promises divided by total matured promises
• Time logic: promise due date
• Exclusions: cancelled promises, duplicate promises, invalid cases
• Refresh: daily
• Grain: promise event

Once these definitions are agreed, they should be documented in a shared metric catalogue, semantic layer, data catalogue, or governance platform.

This prevents every dashboard from creating its own version of "arrears", "delinquency", "cure", or "broken promise".

Step 5: Design Data Layers

The organisation then designs a layered warehouse or lakehouse model.

A practical structure may include bronze, silver, gold, and application layers.

The bronze layer stores source data with minimal transformation.

Example tables:

• bronze.customer_raw
• bronze.loan_raw
• bronze.repayment_schedule_raw
• bronze.payment_transaction_raw
• bronze.collections_case_raw
• bronze.collections_action_raw
• bronze.promise_to_pay_raw
• bronze.hardship_application_raw

The purpose of this layer is traceability.

If a number in a dashboard is questioned, the team can trace the final figure back to the original source record.

The silver layer cleans, standardises, and integrates source data.

Example tables:

• silver.customer
• silver.loan
• silver.repayment_schedule
• silver.payment
• silver.arrears_snapshot
• silver.collections_case
• silver.collections_action
• silver.promise_to_pay
• silver.hardship_application

Common transformations include:

• Standardising date and timestamp formats
• Standardising customer and loan identifiers
• Removing duplicate payments
• Mapping loan statuses to standard categories
• Mapping action outcomes to standard values
• Calculating outstanding scheduled amounts
• Applying hardship flags
• Excluding test accounts
• Creating daily arrears snapshots

For example, different source systems may represent closed loans differently:

• CLOSED
• Closed
• Paid Out
• Finalised
• Inactive

The silver layer maps these values into standard categories such as:

• Open
• Closed
• Written Off
• Paid Out

This allows downstream reports to use consistent definitions.

The gold layer provides reusable business-ready tables.

Example tables:

• gold.fact_arrears_daily
• gold.fact_payment
• gold.fact_collections_action
• gold.fact_promise_to_pay
• gold.fact_hardship
• gold.dim_customer
• gold.dim_loan
• gold.dim_product
• gold.dim_delinquency_bucket
• gold.dim_date

The gold layer contains standard metric logic.

For example:

• Days past due
• Arrears balance
• Delinquency bucket
• Active loan flag
• Hardship flag
• Collections case flag
• Promise kept flag
• Broken promise flag
• Cure flag

This layer should be reusable across dashboards, reports, and analytical use cases.

Application Layer: Dashboards and Data Products

The application layer supports specific outputs.

Example outputs include:

• Executive collections dashboard
• Daily arrears dashboard
• Delinquency roll-rate report
• Hardship monitoring dashboard
• Promise-to-pay performance report
• Collections team productivity report
• Risk exposure dashboard
• Data quality monitoring dashboard

Because these outputs are built from the same gold model, they are more likely to produce consistent results.

This is the difference between building individual reports and building reusable data products.

Step 6: Implement Data Integration and Pipelines

After the model is designed, it must be implemented as a reliable data pipeline.

The pipeline may follow this flow:

1. Ingest customer, loan, repayment schedule, payment, collections, and hardship data into the bronze layer.
2. Validate file arrival, schema, record counts, and source freshness.
3. Clean and standardise records into the silver layer.
4. Calculate outstanding repayment amounts.
5. Generate daily arrears snapshots.
6. Assign delinquency buckets.
7. Apply hardship and collections case flags.
8. Build gold fact and dimension tables.
9. Refresh dashboards and semantic models.
10. Send alerts if data quality checks fail.

This stage includes technical concerns such as:

• Scheduling
• Dependency management
• Incremental loading
• Error handling
• Retry logic
• Data quality checks
• Monitoring
• Access control
• Audit logging
• Production release management

In small projects, scripts may be enough. But as the number of sources increases, pipelines become more complex and manual maintenance becomes expensive.

For example:

• If payment data arrives late, arrears may be overstated.
• If repayment schedules are not refreshed, days past due may be wrong.
• If hardship data is missing, operational teams may contact customers incorrectly.
• If loan status mapping changes, active loan counts may shift unexpectedly.
• If collections actions are duplicated, agent productivity reports may be inflated.

This is why implementation is not just about moving data. It is about continuously and reliably executing the model rules.

Step 7: Validate Data Quality

Before deploying the model, the team must validate the data.

Validation should not only check whether the pipeline runs successfully. It should check whether the output is correct, explainable, and trusted.

Example checks include:

Record Count Checks

• Does the number of active loans match the core banking system?
• Does the number of payment records match the payment platform?
• Does the number of open cases match the collections system?

Primary Key Checks

• Are there duplicate customer IDs?
• Are there duplicate loan IDs?
• Are there duplicate payment IDs?
• Are any payments missing loan IDs?

Relationship Checks

• Does every loan link to a valid customer?
• Does every payment link to a valid loan?
• Does every collections action link to a valid case?
• Does every promise to pay link to a valid case?

Metric Checks

• Does total overdue balance reconcile with operational reports?
• Does days past due align with repayment schedule logic?
• Does delinquency bucket assignment match agreed rules?
• Are hardship customers flagged correctly?
• Are broken promises calculated correctly?

Boundary Scenario Checks

• Partial repayments
• Payment reversals
• Loan closures
• Loan write-offs
• Backdated payments
• Rescheduled repayments
• Hardship arrangements
• Multiple borrowers
• Multiple loans per customer
• Broken promises
• Reopened collections cases

Many data model issues appear in these edge cases. A model that only works for normal repayments is not production-ready.

Step 8: Deploy, Govern, and Iterate

Once the model passes validation, the dashboards and data products can be deployed.

However, the work does not stop there.

After deployment, users may ask new questions:

• Can we predict which customers are likely to roll into 30+ days past due?
• Can we identify which contact strategy improves cure rate?
• Can we compare hardship outcomes across customer segments?
• Can we combine collections activity with digital channel behaviour?
• Can we monitor vulnerable customer treatment?
• Can we analyse arrears trends by interest rate type or product type?
• Can we detect customers at risk before they miss a payment?

The model needs to evolve, but changes must be controlled.

Each change should include:

• Business justification
• Impact assessment
• Updated metric definitions
• Updated data lineage
• Testing
• Version control
• Communication to downstream users

For example, if the business changes the definition of delinquency from "oldest unpaid due date" to "contractual arrears status from the core banking system", the team must assess which dashboards, historical metrics, risk reports, and operational processes will be affected.

Without governance, each change adds more confusion. Over time, the model becomes harder to trust and harder to maintain.

Good data modelling requires ongoing ownership.

6. What the End Result Looks Like

After completing the modelling process, the organisation now has more than a dashboard.

It has a reusable collections data product.

The organisation can now answer:

• What is the official arrears balance?
• What is the official delinquency bucket?
• Which business rules are applied?
• Which source systems provide each field?
• How are customers, loans, payments, hardship cases, and collections actions related?
• Which tables should analysts use?
• How can new dashboards reuse existing logic?
• How can data issues be traced back to the source?
• Which metrics are trusted for executive reporting?

The collections dashboard is now supported by a clear model:

• Business objects are defined.
• Relationships are documented.
• Grain is clear.
• Metrics are standardised.
• Data layers are structured.
• Pipelines are monitored.
• Data quality is validated.
• Access rules are respected.
• Changes are governed.

This is the real value of data modelling.

It turns data work from isolated development tasks into a reliable organisational capability.

7. Summary

The core purpose of data modelling is to transform business reality into a clear, stable, and reusable data structure.

For data engineers, data modelling is not an extra burden. It is a way to reduce rework, improve collaboration, and build systems that can grow with the business.

In a banking collections environment, the data is naturally complex. Customers, loans, repayment schedules, payments, arrears, hardship applications, collections cases, and contact actions all need to be connected correctly. Many metrics depend on business rules. Many reports rely on the same underlying concepts. Many users need the same information, but at different levels of detail.

Without proper modelling, data work depends heavily on individual experience, ad-hoc SQL, temporary tables, and scattered dashboard logic. This may work in the short term, but it becomes fragile as systems expand and requirements change.

Before writing SQL, creating wide tables, or building dashboards, teams should first clarify:

• What are the core business objects?
• How are they related?
• What is the grain of each table?
• What do the metrics mean?
• Where does the data come from?
• Which source system is authoritative?
• Where will the data be used?
• Which modelling method best fits the scenario?
• How will the model be integrated, validated, and maintained?

The collections example shows that a good data model is not just a technical design. It is a bridge between business requirements and trusted data products.

A strong data model gives an organisation more than clean tables. It gives teams a shared language, a reusable foundation, and a reliable path from operational data to trusted business insight.

Reference

1. DAMA International. DAMA-DMBOK: Data Management Body of Knowledge.

Used as a general reference for data management, data governance, data quality, metadata, data architecture, and enterprise data practices.

1. Chen, Peter P. S. (1976). The Entity-Relationship Model: Toward a Unified View of Data. ACM Transactions on Database Systems.

Used as a foundational reference for entity-relationship modelling and the idea of representing real-world entities, relationships, and attributes in data design.

1. Kimball Group. Fact Tables and Dimension Tables.

Used as a reference for the distinction between facts as measurable business events and dimensions as descriptive business context.

1. Kimball Group. Dimensional Modeling Techniques.

Used as a reference for dimensional modelling concepts such as fact tables, dimension tables, conformed dimensions, slowly changing dimensions, and analytical warehouse design.

1. Databricks. What Is Medallion Architecture?

Used as a reference for the bronze, silver, and gold layering pattern in lakehouse architecture.

1. Microsoft Learn. What Is the Medallion Lakehouse Architecture?

Used as a reference for layered lakehouse design and the progressive improvement of data quality across bronze, silver, and gold layers.

1. ScaleFree. Data Vault 2.0 Definition.

Used as a reference for Data Vault concepts, including hubs, links, satellites, business keys, relationships, and historical attribute tracking.

1. DAMA NL. Dimensions of Data Quality.

Used as a reference for data quality considerations such as completeness, consistency, validity, uniqueness, accuracy, and reliability.

1. The Data Warehouse Toolkit and related Kimball methodology materials.

Used as background reading for dimensional modelling, star schemas, business process modelling, grain definition, and analytical data warehouse design.

1. Enterprise data governance and data catalogue practices.

Used as supporting context for documenting metric definitions, ownership, lineage, source-of-truth rules, access controls, and change management.