107 ) Source to target mapping document ( STTM )

 

Source-to-Target Mapping (STM) Document & Excel Structure

For an Insurance Dimensional Modeling Project (such as tracking Policy, Claims, and Premium data), a Source-to-Target Mapping (STM) document serves as the critical blueprint for data engineers. It dictates how raw source systems (e.g., Policy Administration systems, Billing systems, and CRM databases) map into the dimensional data warehouse (Star Schema / Snowflake Schema containing dimensions like Dim_Policy, Dim_Customer, Dim_Agent and facts like Fact_Claims, Fact_Premiums).

Excel Sheet Column Structure

To build a standard, production-ready STM Excel spreadsheet, configure your workbook with the following columns:

Column Header	Description	Example / Value
Mapping ID	Unique identifier for tracking the mapping row.	MAP_POL_001
Source System Name	The origin operational system.	Guidewire PolicyCenter
Source Schema / Table	The database schema and source table name.	dbo.policy_master
Source Column Name	The physical column name in the source table.	pol_effective_date
Source Data Type	Technical data type of the source field.	DATETIME
Target Layer	The architectural zone in the warehouse.	Data Warehouse (Gold / DWH)
Target Schema / Table	The destination dimensional table name.	dim_policy
Target Column Name	The physical column name in the destination table.	effective_date_key
Target Data Type	Technical data type of the destination field.	INTEGER (YYYYMMDD)
Transformation Rule / Logic	How data is transformed, cleaned, or derived.	Convert DATETIME to YYYYMMDD integer format. If null, default to 19000101.
Business Rule / Definition	The business meaning of the field.	The official date when the insurance policy terms take effect.
Mapping Type	The nature of the movement (Direct, Derived, Hardcoded, Lookup).	Derived / Lookup
Null Handling / Default	What to insert if source value is missing.	Default to -1 (Unknown Policy)
Data Owner / Domain	The business stakeholder accountable for this data.	Underwriting Team
Status	Implementation status of the mapping row.	Approved / In Progress / Tested

Sample STM Rows for an Insurance Dimensional Model

Below is a practical example of how rows are documented inside the Excel sheet for an insurance project spanning a dimension table and a fact table:

1. Dimension Mapping Example (dim_policy)

• Mapping ID: MAP_POL_001

• Source System: Guidewire PolicyCenter

• Source Table: pol_master

• Source Column: policy_number (VARCHAR(50))

• Target Table: dim_policy

• Target Column: policy_number (VARCHAR(50))

• Transformation Rule: Direct copy. Strip out leading/trailing whitespace using TRIM().

• Business Rule: Unique commercial or personal insurance contract identifier.

• Mapping Type: Direct

2. Fact Table Mapping Example (fact_claims)

• Mapping ID: MAP_CLM_042

• Source System: Guidewire ClaimCenter

• Source Table: clm_transaction

• Source Column: payout_amount (DECIMAL(18,2))

• Target Table: fact_claims

• Target Column: claim_payout_amt (DECIMAL(18,2))

• Transformation Rule: Multiply by currency_exchange_rate if source currency is non-USD. If negative values appear, flag to exception table.

• Business Rule: Total monetary payout disbursed for an approved insurance claim.

• Mapping Type: Derived