Worked Examples
The following worked examples use a fictional retail bank, "Horizon Bank", which is onboarding its core banking data into CDM.
Horizon Bank Source System Overview
| Source Table | Description | Rows | Grain |
|---|---|---|---|
| HB_CUST_MASTER | Customer demographic, identity, and contact details combined | 4.2M | One row per customer |
| HB_ACCT_MASTER | Account details for all account types | 9.8M | One row per account |
| HB_ACCT_CUST_LINK | Which customers hold which accounts and in what role | 11.1M | One row per account–customer–role |
| HB_PRODUCT_CAT | Product catalogue with features | 340 | One row per product code + version |
| HB_TRANSACTIONS | All financial transactions across account types | 820M | One row per transaction ID |
Example 1: HB_CUST_MASTER → Four CDM Entities (Pattern A)
HB_CUST_MASTER is a wide table of 64 columns. After analysis, these columns decompose into four CDM subject areas. The column inventory is below, followed by the interface mapping for each target entity.
Source Structure: HB_CUST_MASTER
| Source Column | Source Data Type | Business Meaning | CDM Subject Area → Target |
|---|---|---|---|
| CUST_ID | VARCHAR(20) | Unique customer identifier in Horizon Bank CRM | Party → ldz_party_demographic (PK) |
| FIRST_NM | VARCHAR(50) | Customer first name | Party → ldz_party_demographic |
| LAST_NM | VARCHAR(50) | Customer last name | Party → ldz_party_demographic |
| DOB | DATE | Date of birth | Party → ldz_party_demographic |
| GNDR_CD | CHAR(1) | Gender code: M / F / U | Party → ldz_party_demographic (needs code conversion) |
| MRTL_STA_CD | VARCHAR(10) | Marital status: SGL/MRD/DVR/WID | Party → ldz_party_demographic (needs code conversion) |
| OCCPTN | VARCHAR(50) | Occupation description | Party → ldz_party_demographic |
| INCM_BAND | VARCHAR(20) | Income band: A/B/C/D | Party → ldz_party_demographic |
| PAN_NO | VARCHAR(10) | Permanent Account Number (India tax ID) | Identity → ldz_party_identity (PK) |
| AADHAAR_NO | VARCHAR(12) | Aadhaar national biometric ID | Identity → ldz_party_identity |
| PASSP_NO | VARCHAR(15) | Passport number (nullable) | Identity → ldz_party_identity |
| PASSP_EXP_DT | DATE | Passport expiry date | Identity → ldz_party_identity |
| EMAIL_ADDR | VARCHAR(100) | Primary email address | Contact → ldz_party_contact |
| MOB_NUM | VARCHAR(15) | Mobile number with country code | Contact → ldz_party_contact |
| HOME_PHN | VARCHAR(15) | Home phone (nullable) | Contact → ldz_party_contact |
| RES_ADDR_1 | VARCHAR(100) | Residential address line 1 | Address → ldz_party_address_det |
| RES_ADDR_2 | VARCHAR(100) | Residential address line 2 | Address → ldz_party_address_det |
| CITY_NM | VARCHAR(50) | City name | Address → ldz_party_address_det |
| STATE_CD | VARCHAR(3) | State code (ISO) | Address → ldz_party_address_det |
| PINCODE | VARCHAR(6) | Postal code | Address → ldz_party_address_det |
| CNTRY_CD | VARCHAR(2) | Country ISO code | Address → ldz_party_address_det |
| SRC_SYS_ID | VARCHAR(20) | Source system identifier (always "HB_CRM") | All targets → rec_src |
MID Mapping: HB_CUST_MASTER → ldz_party_demographic
| Source Table | Source Column | Target Column | Mandatory | Target Table | Transformation Logic | DQ Rule | PII |
|---|---|---|---|---|---|---|---|
| HB_CUST_MASTER | CUST_ID | partyid | Yes | ldz_party_demographic | Direct map. Use as natural key. | NOT NULL, UNIQUE | No |
| HB_CUST_MASTER | SRC_SYS_ID | rec_src | Yes | ldz_party_demographic | Hardcode "HB_CRM" | NOT NULL | No |
| HB_CUST_MASTER | FIRST_NM | first_nme | No | ldz_party_demographic | INITCAP(TRIM(FIRST_NM)) | No special chars | Yes – PII |
| HB_CUST_MASTER | LAST_NM | last_nme | No | ldz_party_demographic | INITCAP(TRIM(LAST_NM)) | No special chars | Yes – PII |
| HB_CUST_MASTER | DOB | dob | No | ldz_party_demographic | TO_DATE(DOB,'YYYY-MM-DD'). Reject future dates. | Date range: 1900–today | Yes – PII |
| HB_CUST_MASTER | GNDR_CD | gender | No | ldz_party_demographic | M→MALE, F→FEMALE, U→UNKNOWN | Domain: MALE/FEMALE/UNKNOWN | No |
| HB_CUST_MASTER | MRTL_STA_CD | mrtl_sta | No | ldz_party_demographic | SGL→SINGLE, MRD→MARRIED, DVR→DIVORCED, WID→WIDOWED | Domain check | No |
| HB_CUST_MASTER | OCCPTN | occuptn | No | ldz_party_demographic | UPPER(TRIM(OCCPTN)) | Max length 50 | No |
| HB_CUST_MASTER | INCM_BAND | incm_band | No | ldz_party_demographic | Direct map | Domain: A/B/C/D | No |
MID Mapping: HB_CUST_MASTER → ldz_party_identity
| Source Table | Source Column | Target Column | Mandatory | Target Table | Transformation Logic | DQ Rule | PII |
|---|---|---|---|---|---|---|---|
| HB_CUST_MASTER | CUST_ID | partyid | Yes | ldz_party_identity | Direct map. FK to party_demographic. | NOT NULL | No |
| HB_CUST_MASTER | PAN_NO | id_val | Yes | ldz_party_identity | UPPER(TRIM(PAN_NO)). Set id_typ = "PAN". | Format: [A-Z]{5}[0-9]{4}[A-Z]{1} | Yes – PII |
| HB_CUST_MASTER | AADHAAR_NO | id_val | No | ldz_party_identity | Separate row per id_typ. Set id_typ = "AADHAAR". Mask first 8 digits. | Length = 12, numeric | Yes – PII – Mask |
| HB_CUST_MASTER | PASSP_NO | id_val | No | ldz_party_identity | Nullable. Set id_typ = "PASSPORT". Only insert if not null. | Alphanumeric | Yes – PII |
| HB_CUST_MASTER | PASSP_EXP_DT | id_exp_dt | No | ldz_party_identity | TO_DATE(PASSP_EXP_DT). Only populate when id_typ = PASSPORT. | Must be future date if present | No |
| HB_CUST_MASTER | SRC_SYS_ID | rec_src | Yes | ldz_party_identity | Hardcode "HB_CRM" | NOT NULL | No |
Note: Each identity document type creates a separate row in ldz_party_identity. A customer with PAN + Aadhaar + Passport will generate three rows, each with partyid, id_typ, id_val, and rec_src. The composite natural key is (partyid, id_typ, rec_src).
MID Mapping: HB_CUST_MASTER → ldz_party_contact
| Source Table | Source Column | Target Column | Mandatory | Target Table | Transformation Logic | DQ Rule | PII |
|---|---|---|---|---|---|---|---|
| HB_CUST_MASTER | CUST_ID | partyid | Yes | ldz_party_contact | Direct map. | NOT NULL | No |
| HB_CUST_MASTER | EMAIL_ADDR | cntct_val | No | ldz_party_contact | LOWER(TRIM(EMAIL_ADDR)). Set cntct_typ = "EMAIL". One row per contact type. | RFC 5322 email format | Yes – PII |
| HB_CUST_MASTER | MOB_NUM | cntct_val | No | ldz_party_contact | Normalise E.164 format (+CC-NNNNNNNNN). Set cntct_typ = "MOBILE". | E.164 regex | Yes – PII |
| HB_CUST_MASTER | HOME_PHN | cntct_val | No | ldz_party_contact | Only insert if not null. Set cntct_typ = "HOME_PHONE". Normalise E.164. | Nullable source, numeric only | Yes – PII |
| HB_CUST_MASTER | SRC_SYS_ID | rec_src | Yes | ldz_party_contact | Hardcode "HB_CRM" | NOT NULL | No |
Example 2: HB_ACCT_MASTER + HB_ACCT_CUST_LINK → Two CDM Entities (Pattern C)
HB_ACCT_MASTER contains account attributes. HB_ACCT_CUST_LINK records which customers are linked to each account. These always map to two separate CDM entities.
Source Structure: HB_ACCT_MASTER
| Source Column | Source Data Type | Business Meaning | CDM Target |
|---|---|---|---|
| ACCT_NO | VARCHAR(20) | Unique account number in Horizon Bank | ldz_account_dtl – PK (acct_num) |
| ACCT_TYP_CD | VARCHAR(10) | Account type: SAV/CUR/FD/RD | ldz_account_dtl – PK (acct_typ) + code conversion |
| OPEN_DT | DATE | Date account was opened | ldz_account_dtl (open_dt) |
| CLOSE_DT | DATE | Date account was closed (nullable) | ldz_account_dtl (close_dt) |
| ACCT_STA_CD | CHAR(1) | Status: A=Active, D=Dormant, C=Closed | ldz_account_dtl (acct_sta) – code conversion |
| BRCH_CD | VARCHAR(10) | Branch code where account is held | ldz_account_branch (brch_cde) – relationship entity |
| PROD_CD | VARCHAR(20) | Product code linked to this account | ldz_account_product_dtl (prdt_cde) |
| CURR_CD | CHAR(3) | Currency ISO code | ldz_account_dtl (curr_cde) |
| ACCT_LMT | NUMBER(18,2) | Account limit or credit limit | ldz_account_dtl (acct_lmt) |
| SRC_SYS_ID | VARCHAR(20) | Source system identifier | ldz_account_dtl (rec_src) |
Observation: BRCH_CD and PROD_CD are not attributes of the account — they are relationship keys. BRCH_CD populates ldz_account_branch (a separate relationship table). PROD_CD populates ldz_account_product_dtl. These should not be mapped directly into ldz_account_dtl as attributes.
MID Mapping: HB_ACCT_MASTER → ldz_account_dtl
| Source Table | Source Column | Target Column | Mandatory | Target Table | Transformation Logic | DQ Rule | PII |
|---|---|---|---|---|---|---|---|
| HB_ACCT_MASTER | ACCT_NO | acct_num | Yes | ldz_account_dtl | Direct map. Natural key component 1. | NOT NULL | No |
| HB_ACCT_MASTER | ACCT_TYP_CD | acct_typ | Yes | ldz_account_dtl | SAV→SAVINGS, CUR→CURRENT, FD→FIXED_DEPOSIT, RD→RECURRING. Natural key component 2. | Domain check | No |
| HB_ACCT_MASTER | SRC_SYS_ID | rec_src | Yes | ldz_account_dtl | Hardcode "HB_CBS" | NOT NULL | No |
| HB_ACCT_MASTER | OPEN_DT | open_dt | No | ldz_account_dtl | TO_DATE(OPEN_DT). Reject future dates. | Not null for active accts | No |
| HB_ACCT_MASTER | CLOSE_DT | close_dt | No | ldz_account_dtl | TO_DATE(CLOSE_DT). NULL if account is active. | Must be >= open_dt | No |
| HB_ACCT_MASTER | ACCT_STA_CD | acct_sta | No | ldz_account_dtl | A→ACTIVE, D→DORMANT, C→CLOSED | Domain check | No |
| HB_ACCT_MASTER | CURR_CD | curr_cde | No | ldz_account_dtl | Direct map. Validate ISO 4217. | ISO 4217 3-char code | No |
| HB_ACCT_MASTER | ACCT_LMT | acct_lmt | No | ldz_account_dtl | Direct map. ROUND(ACCT_LMT, 2). | Non-negative | No |
Source Structure: HB_ACCT_CUST_LINK
| Source Column | Source Data Type | Business Meaning | CDM Target |
|---|---|---|---|
| ACCT_NO | VARCHAR(20) | Account number (FK to HB_ACCT_MASTER) | ldz_account_party – acct_num |
| ACCT_TYP_CD | VARCHAR(10) | Account type (FK to HB_ACCT_MASTER) | ldz_account_party – acct_typ |
| CUST_ID | VARCHAR(20) | Customer ID (FK to HB_CUST_MASTER) | ldz_account_party – partyid |
| ROLE_CD | VARCHAR(20) | Role: PRI/JNT/GRN/NOM | ldz_account_party – role_typ (code conversion) |
| LINK_DT | DATE | Date the customer was linked to this account | ldz_account_party (link_dt) |
| SRC_SYS_ID | VARCHAR(20) | Source system identifier | ldz_account_party – rec_src |
MID Mapping: HB_ACCT_CUST_LINK → ldz_account_party
| Source Table | Source Column | Target Column | Mandatory | Target Table | Transformation Logic | DQ Rule | PII |
|---|---|---|---|---|---|---|---|
| HB_ACCT_CUST_LINK | ACCT_NO | acct_num | Yes | ldz_account_party | Direct map. NK component 1. | NOT NULL, FK to account_dtl | No |
| HB_ACCT_CUST_LINK | ACCT_TYP_CD | acct_typ | Yes | ldz_account_party | Same conversion as account_dtl. NK component 2. | Domain check | No |
| HB_ACCT_CUST_LINK | CUST_ID | partyid | Yes | ldz_account_party | Direct map. NK component 3. FK to party_demographic. | NOT NULL, FK to party | No |
| HB_ACCT_CUST_LINK | ROLE_CD | role_typ | Yes | ldz_account_party | PRI→PRIMARY, JNT→JOINT, GRN→GUARANTOR, NOM→NOMINEE. NK component 4. | Domain check | No |
| HB_ACCT_CUST_LINK | SRC_SYS_ID | rec_src | Yes | ldz_account_party | Hardcode "HB_CBS" | NOT NULL | No |
| HB_ACCT_CUST_LINK | LINK_DT | link_dt | No | ldz_account_party | TO_DATE(LINK_DT) | Not null for active links | No |
Example 3: HB_PRODUCT_CAT → ldz_product_dtl (1-to-1 Pattern)
The product catalogue is a relatively simple 1-to-1 mapping to ldz_product_dtl. Note that versioned products generate multiple rows — the composite natural key is (prdt_cde, prdt_ver, rec_src).
Source Structure: HB_PRODUCT_CAT
| Source Column | Source Data Type | Business Meaning | CDM Target |
|---|---|---|---|
| PROD_CD | VARCHAR(20) | Unique product code | ldz_product_dtl – prdt_cde (PK) |
| PROD_VER | VARCHAR(10) | Product version (e.g., "V2", "2024") | ldz_product_dtl – prdt_ver (PK) |
| PROD_NM | VARCHAR(100) | Product name | ldz_product_dtl – prdt_nme |
| PROD_TYP_CD | VARCHAR(20) | Type: SAV/LOAN/CC/INV/INS | ldz_product_dtl – prdt_typ_cde (code conversion) |
| PROD_CATG | VARCHAR(50) | Category: RETAIL/CORPORATE/SME | ldz_product_dtl – prdt_catg |
| INTR_RT | NUMBER(6,4) | Interest rate (annual, % as decimal) | ldz_product_dtl – intr_rt |
| LAUNCH_DT | DATE | Product launch date | ldz_product_dtl – launch_dt |
| END_DT | DATE | Product end / discontinuation date (nullable) | ldz_product_dtl – end_dt |
| SRC_SYS_ID | VARCHAR(20) | Source system | ldz_product_dtl – rec_src |
Example 4: HB_TRANSACTIONS → Multiple Transaction Targets (Pattern D)
HB_TRANSACTIONS is a single 820 million row table covering all transaction types. In CDM, these are routed to separate targets based on TXN_TYPE_CD. This is a filter-and-route pattern — a single source table is split into multiple CDM targets using filter conditions.
Source Structure: HB_TRANSACTIONS (key columns only)
| Source Column | Source Data Type | Business Meaning |
|---|---|---|
| TXN_ID | VARCHAR(30) | Unique transaction identifier |
| ACCT_NO | VARCHAR(20) | Account number involved |
| CUST_ID | VARCHAR(20) | Customer linked to the transaction |
| TXN_DT | TIMESTAMP | Date and time of transaction |
| TXN_TYPE_CD | VARCHAR(20) | Type: DEBIT/CREDIT/LOAN_EMI/CC_PURCHASE/SERVICE_FEE/etc. |
| TXN_AMT | NUMBER(18,2) | Transaction amount (positive value) |
| DEBIT_CRDT_IND | CHAR(1) | D=Debit, C=Credit |
| NARRATN | VARCHAR(200) | Narration / description |
| CHNL_CD | VARCHAR(20) | Channel: ATM/NET/MOB/BRANCH/POS |
| REF_NO | VARCHAR(30) | Reference number (nullable) |
| SRC_SYS_ID | VARCHAR(20) | Source system |
Routing Logic: Filter HB_TRANSACTIONS into CDM targets
| TXN_TYPE_CD values | CDM target entity | Natural key columns | Notes |
|---|---|---|---|
| DEBIT, CREDIT, TRANSFER, NEFT, RTGS, IMPS, UPI | ldz_acct_party_trans | TXN_ID + rec_src | General account transactions |
| LOAN_EMI, LOAN_PREPAY, LOAN_PENALTY, LOAN_DISBURSEMENT | ldz_loan_trans | TXN_ID + rec_src | Loan movements only |
| CC_PURCHASE, CC_PAYMENT, CASHBACK, CC_REVERSAL | ldz_creditcard_trans | TXN_ID + rec_src | Credit card transactions |
| SERVICE_FEE, ANNUAL_FEE, PROCESSING_FEE, PENALTY_CHG | ldz_fees_trans | TXN_ID + rec_src | Fee and charge events |
Important: Each routed sub-set is an independent ETL stream. Do not attempt a single unified load into all four targets. Build four separate ETL mappings, each with its own TXN_TYPE_CD filter as the extraction predicate.