Step-by-Step Mapping Method

Follow all eight steps in sequence for each source system in scope. Do not skip steps to save time — each step produces decisions that the next step depends on.

Step 1 — Understand the Source System

Document what business process the source system supports, its key entities, primary relationships, update pattern (full refresh vs delta vs CDC), and whether historical data is available. Schedule a 1-hour walkthrough with the client data SME if documentation is insufficient.

Step 2 — Classify Each Source Table

For every source table in scope, assign one of five classifications: Master Data (slow-changing core entities), Reference Data (code sets and lookup tables), Relationship Data (who-owns-what, who-is-related-to-whom), Transaction Data (financial movements and events), or Event/History Data (activity logs and audit trails).

Step 3 — Identify the Grain

Document the grain of each source table explicitly. Examples: "one row per customer per source system", "one row per account per day", "one row per transaction". This is the single most important step — wrong grain causes incorrect LDZ natural key design and duplicate data.

Step 4 — Find the Best-Fit CDM Target

Using the subject area guide and the decision rules in the next section, select the target LDZ entity (or entities) for each source table. Document the reason for your choice — this is reviewed by the data architect.

Step 5 — Map Columns

For each source column, complete all MID fields: source table, source column, source data type, source business definition, target entity/table, target attribute/column, mandatory flag, transformation logic, default value, code conversion, DQ rule, PII flag, and any open questions or comments.

Step 6 — Handle Unmapped or Partial-Fit Fields

For any source column that does not map cleanly: consider using an approved attrib_* extension column; raise an extension request to the Design Authority; defer to a later phase if not needed in Phase 1; or document as "out of scope" with a clear reason. Never leave a field in a status of ambiguity.

Step 7 — Validate the Mapping

Before requesting sign-off, self-validate: all mandatory business keys are mapped; grain of each source table matches the grain of the target entity; dates, amounts, code values, and statuses are understood and conversions documented; all relationship data is captured in relationship entities; no sensitive fields are left unclassified; no duplicate mappings without a documented justification.

Step 8 — Sign-off and Handoff

Once the mapping is internally validated: the business stakeholder signs off on semantic correctness; the CDM data architect signs off on canonical fit; the ETL engineer signs off on implementability. The finalised MID is then handed to the engineering team as the formal specification for metadata loading and ETL generation.