Data Mapping
A practical guide to source-to-target field mapping for ETL projects, database migrations, file imports, APIs, and integrations.
Data mapping defines how source fields connect to target fields during ETL, database migration, and system integration. It answers the practical question: which source value goes into which target column, and what rule should be applied on the way? Miss that detail and your import may still run. It will just run confidently into the wrong table, which is not ideal unless your hobby is reconciliation.
What Is Data Mapping?
Data mapping is the process of defining relationships between fields in a source system and fields in a target system. A mapping says that `CustomerNo` becomes `customer_id`, `Invoice Date` becomes `invoice_date`, `Qty` becomes `quantity`, and `Amount` becomes `invoice_amount`. It also says whether a value is copied directly, converted, calculated, looked up, defaulted, rejected, or routed for review.
In ETL, mapping sits between understanding the source and loading the target. The usual flow is: extract the data, profile the source fields, define the target fields, map source to target, apply any required rules, validate the result, then load accepted rows. It is one part of the wider data transformation process, but it has a narrower job: field relationships.
That distinction matters. Data mapping does not mean every transformation task. It does not automatically mean schema transformation, where tables, keys, relationships, and nested structures are reshaped. It does not mean data standardization, where formats and labels are made consistent. It means source-to-target field definition. The mapping is the contract. Other transformation steps may implement parts of that contract.
A good mapping is specific enough that a developer, DBA, analyst, and tester read the same rule and expect the same output. A bad mapping says "map customer details" and then leaves everyone guessing. That is not documentation. That is a treasure hunt with worse snacks.
For example, a source export may contain `CustNo`, `Customer Name`, `Post Code`, `Sales Rep`, and `Credit Limit`. The target customer table may expect `customer_id`, `customer_name`, `postcode`, `account_manager_id`, and `credit_limit_amount`. Data mapping defines those relationships. It also explains that `Sales Rep` must be matched against an employee lookup table, while `Credit Limit` must be loaded as a decimal value.
This is why source-to-target mapping is usually written from the target backwards. Start with the target field and ask where its value comes from. If there is no source value, define a default, generated value, lookup, or rejection rule. If nobody knows the answer, do not guess. Guessing is quick until the first audit asks why 18,000 customers were assigned to the wrong sales territory.
Why Data Mapping Matters
Data mapping matters because source systems rarely name, store, and structure fields the same way as target systems. Database migrations need it. System integrations need it. Data warehouses need it. Master data management, reporting, cloud migrations, and file imports all depend on it.
During database migration, mapping explains how source tables and columns move into the new platform. It reduces migration risk because every target field has an origin or a deliberate default. During integrations, mapping keeps CRM, ERP, finance, warehouse, and support systems speaking the same language without pretending their field names were agreed by a committee of reasonable people.
Data warehouses depend on mapping because operational source fields must land in staging, dimensions, facts, and reporting tables. Master data management depends on mapping because customer, product, supplier, and location attributes must be aligned across systems. Reporting depends on mapping because a dashboard built on the wrong field is just modern fiction with charts.
Mapping also reduces data errors. It catches missing fields, duplicate source values, incompatible types, wrong code sets, and ambiguous business rules before the load. It maintains consistency by making the same source value follow the same rule every time the workflow runs.
Source fields Mapping rules Target fields ------------- ------------- ------------- CustNo -----------> customer_id ------------> customer.customer_id FullName -----------> split name ------------> first_name, last_name Country -----------> country lookup ------------> country_id Qty + Price -----------> Qty * Price ------------> line_total
Use mapping documents when people must agree what a load means before it is automated. If the task is one small one-off file, a quick spreadsheet may be enough. If the same mapping feeds production every week, put it in a governed ETL workflow with validation, logging, and version history. Computers are excellent at repetition. People are excellent at accidentally sorting only half a spreadsheet.
Cloud migrations also depend on clean mapping. Moving an application from an on-premise database to a cloud database is not just a transport job. Field lengths, nullable rules, generated keys, audit columns, reserved words, and timestamp behaviour may change. A mapping document gives the migration team a checklist that survives meetings, test runs, and the inevitable moment someone says, "We forgot about archived customers."
Reporting teams benefit because mapping creates traceability. If a BI model shows `net_revenue`, the mapping should explain whether it came from invoice lines, payment records, order totals, or a calculated expression. Without that trace, two reports may show different totals and both teams will claim victory. I have seen this film. The sequel is called "Reconciliation Friday" and nobody enjoys it.
Common Types of Data Mapping
Most ETL data mapping work uses a mix of these patterns. Name the pattern in the mapping document. It makes reviews faster and stops calculated rules being mistaken for direct field movement.
One-to-one mapping
What it is: One source field populates one target field.
When to use it: Use it when names differ but meaning is the same.
ETL example: `CustNo` maps to `customer_id` during a SQL Server to PostgreSQL migration.
One-to-many mapping
What it is: One source value populates several target fields.
When to use it: Use it when a source field contains values needed in more than one target column.
ETL example: `FullName` maps to `full_name`, then also feeds `first_name` and `last_name`.
Many-to-one mapping
What it is: Several source fields combine into one target field.
When to use it: Use it when the target expects a combined value.
ETL example: `Address1`, `Address2`, and `City` combine into `delivery_address` for an ERP import.
Many-to-many mapping
What it is: Several source fields populate several target fields through multiple rules.
When to use it: Use it for complex entities, relationship tables, or mixed-source integrations.
ETL example: CRM account, contact, and address fields populate customer, contact, and account-site tables.
Direct field mapping
What it is: Values move without calculation, apart from optional trimming or type handling.
When to use it: Use it for identifiers, names, codes, and descriptions already fit for the target.
ETL example: `ProductCode` writes directly to `product_code`.
Calculated field mapping
What it is: The target value is calculated from one or more source fields.
When to use it: Use it for totals, margins, periods, flags, and derived keys.
ETL example: `quantity * unit_price` maps to `line_total`.
Conditional mapping
What it is: The target field depends on IF, CASE, or routing logic.
When to use it: Use it when business rules vary by status, source system, country, or record type.
ETL example: If `Status = Closed`, map `ClosedDate` to `completed_at`; otherwise leave it null.
Lookup-based mapping
What it is: The workflow finds a target value from a reference table.
When to use it: Use it for codes, surrogate keys, regions, currencies, products, and customer matches.
ETL example: Look up `CountryName = United Kingdom` and map target `country_id = 826`.
Default value mapping
What it is: The target receives a fixed value when the source does not provide one.
When to use it: Use it for audit fields, source-system flags, status defaults, and required target columns.
ETL example: Map missing `is_active` to `true` when importing approved customers.
Hierarchical mapping
What it is: Parent and child values are mapped while preserving relationships.
When to use it: Use it for orders and lines, invoices and payments, categories and products, or nested source data.
ETL example: Map an XML order header to `sales_order` and each order line to `sales_order_line`.
JSON/XML mapping
What it is: Nested nodes, properties, and arrays map to target fields or related tables.
When to use it: Use it for API integrations, document feeds, catalogues, and event streams.
ETL example: `customer.addresses[0].postcode` maps to `customer_address.postcode`.
One-to-one mapping is the most common pattern, but it is also where teams get complacent. Matching names does not prove matching meaning. A source `Status` may hold values such as `A`, `I`, and `H`, while the target `status` expects `active`, `inactive`, and `on_hold`. The fields look aligned, but the mapping still needs a code rule.
One-to-many and many-to-one mappings deserve extra testing because they change how values are distributed. Splitting `FullName` into first and last name may fail for company names, middle names, or cultural naming patterns. Combining address fields may accidentally hide the line that contains the apartment number. These are small details until the delivery label says the customer lives at "Floor 3 Null".
Lookup-based mappings are often the most important in database migration. Source systems frequently store display values while target systems store keys. A mapping that loads `United Kingdom` into a field expecting `country_id` is not nearly right. It is wrong with excellent spelling.
Data Mapping Techniques
There is no single perfect data mapping technique. The right approach depends on source complexity, target risk, data volume, team skills, and whether the mapping needs business sign-off. In practice, mature ETL teams combine visual design, metadata, SQL, lookups, and tests.
Visual mapping
Advantage: Shows source fields, target fields, and links between them. It is easier for analysts and developers to review together.
Disadvantage: Large mappings still need naming discipline, grouping, and documentation or the canvas becomes spaghetti with confidence.
Manual mapping documents
Advantage: Useful for workshops, sign-off, audits, and early design before a workflow exists.
Disadvantage: Spreadsheets go stale quickly unless they are versioned and reconciled with the actual ETL job.
Metadata-driven mapping
Advantage: Stores mappings in tables or configuration so repeatable loads can be generated or reused.
Disadvantage: Requires strong metadata design and testing. A wrong metadata row is still a wrong mapping.
SQL expressions
Advantage: Clear for database-to-database mapping, joins, CASE logic, data type conversion, and set-based processing.
Disadvantage: Business rules may become hidden in long scripts if ownership and comments are weak.
Lookup tables
Advantage: Keeps code translations and surrogate key assignment consistent across workflows.
Disadvantage: Needs maintenance, effective dates, duplicate handling, and exception routing for failed matches.
Business rules
Advantage: Connects the mapping to real business meaning rather than just column names.
Disadvantage: Rules change. If nobody owns them, the mapping becomes folklore with a database connection.
Schema discovery
Advantage: Profiles source and target columns quickly, helping teams spot likely matches and type conflicts.
Disadvantage: Discovery suggests structure. It does not know whether `Code` means product, customer, tax, or trouble.
Python scripting
Advantage: Useful for unusual matching rules, fuzzy matching, API-specific handling, and advanced parsing.
Disadvantage: Adds code ownership, dependencies, tests, and deployment concerns.
AI-assisted mapping
Advantage: Good for suggesting candidate matches, summarizing field meaning, and reducing repetitive setup.
Disadvantage: Needs human review. AI is helpful, but it will occasionally map a postcode to a phone number while sounding very pleased with itself.
For database migrations, vendor tools such as Microsoft SQL Server Migration Assistant, PostgreSQL table documentation, and Oracle Database documentation help with platform details. ETL mapping still needs business rules, exceptions, and target ownership.
Visual mapping works well when teams need a shared view. A DBA may care about target types and constraints. A business analyst may care about field meaning. A developer may care about expressions, lookups, and failure paths. A visual design gives everyone something concrete to review, which is more useful than twenty minutes of pointing at column names in a meeting room.
Metadata-driven mapping is useful when the same pattern repeats across many files or tables. For example, a staging process may read mapping rows from a configuration table and apply the same import structure to many supplier files. That saves setup time, but it raises the importance of metadata validation. Configuration is still code. It just wears a nicer shirt.
AI-assisted mapping is best treated as a drafting aid. It can propose likely field matches, summarize a source file, or explain possible transformations. It should not be the final authority for production mappings. Use it to reduce typing, then validate every suggested relationship with sample data and target rules.
Real ETL Examples
SQL Server to PostgreSQL migration
Map SQL Server `dbo.Customer.CustID` to PostgreSQL `sales.customer.customer_id`, convert identity handling separately, and document case-sensitive target names. The mapping should list every source column, target column, type expectation, and rejected-row rule.
Oracle to MySQL migration
Map Oracle `NUMBER(12,2)` amount fields to MySQL `DECIMAL(12,2)`, Oracle `DATE` values to target datetime fields, and sequence-generated identifiers to target keys. Finance tables need sample reconciliation before the cutover.
Excel to SQL Server import
Map worksheet column `Customer No` to `customer_id`, `Invoice Date` to `invoice_date`, and `Amount` to `invoice_amount`. Validate required fields before loading because Excel will happily keep text, dates, blanks, and surprise formulas in the same neighbourhood.
CSV imports
Map headers to staging fields first, then map staging fields to target tables. This protects the target when vendors reorder columns, add fields, or send a file that appears to have been assembled during a fire drill.
XML to relational tables
Map XML header nodes to parent tables and repeating child nodes to child tables. Keep the source document ID so each loaded row can be traced back to the original XML.
JSON API integration
Map top-level API properties to an entity table, nested arrays to child tables, and provider IDs to source-system identifiers. Archive raw JSON before mapping so API changes can be replayed.
CRM to ERP integration
Map CRM accounts, contacts, addresses, tax fields, and status values into the ERP customer model. Use lookups for payment terms, territory, VAT status, and account owner.
Building a data warehouse
Map operational source fields into staging, then dimension and fact tables. Customer IDs, product codes, dates, measures, and source-system identifiers need explicit source-to-target rules.
Example source-to-target mapping table
| Source field | Target table | Target field | Rule | Failure handling |
|---|---|---|---|---|
| CustNo | customer | customer_id | Trim and preserve as text | Reject if blank or duplicate |
| FullName | customer | customer_name | Trim repeated spaces | Reject if blank |
| Country | customer | country_id | Lookup by country name or ISO code | Route to country exception file |
| customer_contact | email_address | Lowercase and trim | Warn if invalid, load if optional | |
| SourceFile | customer | source_system | Default to `legacy_crm` | Never blank |
Before and after mapping example
| Customer No | Name | Post Code | Status | Balance |
|---|---|---|---|---|
| C-1007 | Northwind Ltd | sw1a 1aa | Active | 1250.50 |
| customer_id | customer_name | postcode | is_active | account_balance |
|---|---|---|---|---|
| C-1007 | Northwind Ltd | SW1A 1AA | true | 1250.50 |
The postcode casing above is standardization, not mapping. The mapping rule defines that `Post Code` populates `postcode`. The standardization rule defines how the value is formatted. Keeping those separate makes the workflow easier to test.
Database migration mapping example
| SQL Server source | PostgreSQL target | Mapping rule | Notes |
|---|---|---|---|
| dbo.Customer.CustomerID | customer.customer_id | Direct map | Preserve existing ID for audit matching. |
| dbo.Customer.CompanyName | customer.customer_name | Trim and load | Reject blank values. |
| dbo.Customer.CreditLimit | customer.credit_limit | Convert money to numeric(12,2) | Check scale and rounding. |
| dbo.Customer.SalesRepCode | customer.account_manager_id | Lookup employee by source code | Route missing matches to exception table. |
| System date | customer.loaded_at | Generated at load time | Used for audit and rerun checks. |
JSON API mapping example
| JSON path | Target table | Target field | Rule |
|---|---|---|---|
| $.order.id | sales_order | source_order_id | Direct map from API identifier. |
| $.order.customer.id | sales_order | customer_id | Lookup internal customer key. |
| $.order.createdAt | sales_order | order_timestamp | Parse ISO timestamp and preserve time zone. |
| $.order.lines[*].sku | sales_order_line | product_code | One child row per array item. |
| $.order.lines[*].quantity | sales_order_line | quantity | Convert to integer and reject negative values. |
The JSON example mixes data mapping with schema transformation. Mapping identifies the field relationships. Schema transformation handles the fact that one nested document becomes parent and child relational rows. If you keep those two ideas separate, the design is easier to explain and much easier to debug.
Related implementation guides include copy data between databases, import FoxPro data into SQL Server, import Excel into a database, load Excel data into any database, transform CSV data to JSON, JSON vs XML, and XML transformation with XSLT.
Common Data Mapping Challenges
Preserving data type meaning
Mismatched data types are the first problem. A source may store customer IDs as text with leading zeros while the target expects an integer. Convert too aggressively and the identifier changes meaning. This is why data type conversion belongs in the rule column, not in someone's memory.
Handling missing source fields
Missing source fields are common during migration and integration. A target may require `created_at`, `country_id`, or `tax_status`, while the source has no equivalent field. The mapping must define whether the value is defaulted, looked up, generated, rejected, or supplied from another source.
Detecting schema changes
Changing schemas create recurring pain. Source owners add columns, rename fields, move spreadsheet headings, or alter API payloads. A robust mapping process detects changed schemas before load time. Otherwise the workflow may map the right data into the wrong target field, which is the ETL equivalent of posting a letter through the wrong door and calling it logistics.
Resolving inconsistent field names
Inconsistent naming conventions waste time. `CustomerID`, `Cust_ID`, `AccountNo`, and `ClientCode` may all refer to the same business entity. Or they may not. Guessing is quick. Fixing guessed mappings is not.
Choosing between duplicate source fields
Duplicate source fields also cause trouble. A CSV may contain `Phone`, `Mobile`, and `ContactPhone`, while the target has one `phone_number`. The mapping must define precedence and fallback rules.
Documenting transformation logic
Complex transformations should be documented without pretending they are simple mappings. If a target value requires splitting, merging, lookup, calculation, filtering, or aggregation, call that out. The strongest opinion on this page is simple: a mapping document that hides business logic is worse than no mapping document, because it looks trustworthy while quietly sharpening the rake.
Keeping documentation aligned with workflows
Maintaining mapping documentation is the final challenge. Documents drift unless they are tied to workflow versions. If the ETL job changes, update the mapping. If the mapping changes, update the job. Do not let them become two different stories told by two different spreadsheets.
Getting the right ownership
Another challenge is ownership. Technical teams often understand field types and constraints, while business teams understand meaning. A mapping signed off only by developers may be technically valid and commercially wrong. A mapping signed off only by business users may ignore target constraints. The safest review includes both. Yes, that means one more meeting. Sorry. Bring biscuits.
Testing with known records
Finally, test mappings with known records. Choose a customer, order, invoice, or product that the business recognizes. Trace it from source to target and prove each field. This catches problems that row counts miss, such as swapped identifiers, wrong status codes, and amounts mapped before currency conversion.
Best Practices for ETL Data Mapping
- Start with the target field list, not the source file, because the target defines what must be loaded.
- Keep raw source data unchanged for audit, replay, and troubleshooting.
- Create a source-to-target mapping document before building the recurring workflow.
- Record source field, target field, data type, required flag, rule, default value, lookup, and rejection behaviour.
- Separate direct mappings from calculated, conditional, lookup, and default mappings.
- Use staging tables when source files are unstable, undocumented, or supplied by third parties.
- Validate row counts, required fields, keys, duplicates, and control totals after mapping.
- Version mapping documents and ETL workflows together.
- Use meaningful target names instead of preserving cryptic legacy names unless compatibility requires them.
- Test with awkward samples containing blanks, long text, invalid dates, duplicate keys, and unknown codes.
- Assign a business owner to each mapping rule that has business meaning.
- Do not automate a mapping that nobody can explain.
A practical mapping document layout
| Column | Purpose | Example |
|---|---|---|
| Source object | File, table, worksheet, API, or XML/JSON path. | `CRM.CustomerExport` |
| Source field | Exact source field name or path. | `CustNo` |
| Target object | Target table, file, endpoint, or report section. | `customer` |
| Target field | Exact target column or property. | `customer_id` |
| Mapping type | Direct, calculated, conditional, lookup, default, or generated. | Direct |
| Rule | Plain-English rule plus expression when needed. | Trim spaces and preserve leading zeros |
| Validation | Checks applied before loading. | Required, unique, max length 20 |
Keep the document boring. Boring is good. Boring means every row has an owner, every target field has a source or rule, and every exception has somewhere to go. Exciting mapping documents are usually exciting for the wrong reasons.
When the mapping changes, rerun representative samples. Do not assume a harmless column rename is harmless. A renamed field may break a lookup, change a nullable rule, or expose a target length issue. The test set should include ordinary rows and awkward rows, because awkward rows are the ones that apply for overtime.
Data Mapping vs Schema Transformation
Data mapping and schema transformation often appear in the same migration project, but they answer different questions.
| Question | Data mapping | Schema transformation |
|---|---|---|
| Main purpose | Define how source fields populate target fields. | Change the structure of data between source and target models. |
| Example | Map `CustomerName` to `customer.customer_name`. | Split one flat CSV into customer, order, and order line tables. |
| Main artefact | Source-to-target field mapping document or workflow mapping. | Target schema design, table structure, keys, and relationship rules. |
| Related guide | This data mapping guide. | Schema Transformation. |
Data Mapping vs Data Transformation
Data transformation is the broader category. Mapping is one technique inside it. Link back to the pillar page when you need the wider concept, process, and list of transformation types.
| Question | Data mapping | Data transformation |
|---|---|---|
| Scope | Narrow: source fields to target fields. | Broad: change data format, type, value, shape, quality, and meaning. |
| Example | `OrderDate` maps to `order_date`. | Convert date text, standardize formats, calculate totals, cleanse values, and validate output. |
| Typical timing | Designed before or during transformation build. | Runs as the main ETL step before loading. |
| Related guide | This source-to-target guide. | What is Data Transformation? |
Data Mapping vs Data Standardization
Standardization makes values consistent. Mapping decides where those values go. The two often sit side by side in ETL.
| Question | Data mapping | Data standardization |
|---|---|---|
| Main purpose | Connect source and target fields. | Make values follow agreed formats and code sets. |
| Example | `Post Code` maps to `postcode`. | `sw1a 1aa` becomes `SW1A 1AA`. |
| Risk if wrong | Values load into the wrong field or are missed entirely. | Values load in inconsistent formats that break matching and reporting. |
| Related guide | This data mapping guide. | Data Standardization. |
Checklist for Source-to-Target Mapping Documents
Use this checklist before building or approving a mapping. It is less dramatic than discovering `Amount` mapped to `Discount` after month-end, which is exactly why it exists.
- List every source table, file, worksheet, API response, and XML or JSON document.
- List every target table and field, including required fields and constraints.
- Define one row of target output: customer, invoice, order line, event, dimension row, or fact row.
- Map every target field to a source field, expression, lookup, default, or generated value.
- Mark direct, calculated, conditional, lookup, default, and unmapped fields clearly.
- Define source-to-target data type handling, including precision, scale, length, dates, and booleans.
- Document lookup tables, match keys, missing-match handling, and duplicate-match handling.
- Define rejection rules for missing required values, invalid references, duplicates, and truncation risk.
- Add control totals and row-count checks for each source and target step.
- Review the mapping with both technical and business owners before scheduling it.
- Store mapping changes with dates, owners, and reasons.
- Run a small sample and trace individual records from source to target.
For broader workflow design, use the Transformation hub. For validation rules after mapping, use the Data Validation hub.
Frequently Asked Questions
What is data mapping?
Data mapping defines the relationship between source fields and target fields. It explains which source value populates each target column, plus any expression, lookup, default, condition, or rejection rule used during ETL.
What is data mapping in ETL?
Data mapping in ETL happens after source profiling and before loading. The ETL workflow uses the mapping to move, calculate, look up, or route values so the target database, file, report, or application receives the correct fields.
Is ETL data mapping the same as data transformation?
No. Data mapping defines where values go. Data transformation is broader and includes mapping, type conversion, cleansing, aggregation, enrichment, masking, and schema changes.
What is source-to-target mapping?
Source-to-target mapping is a document or configuration that lists each target field and shows the source field, rule, lookup, default, or expression that populates it. It is the practical contract between the source and target system.
What is field mapping?
Field mapping connects one source field to one target field or defines a rule for producing the target field. For example, `CustNo` may map to `customer_id` and `OrderDate` may map to `order_date`.
What is database mapping?
Database mapping defines how fields from one database table or schema map to another database table or schema. It is common in SQL Server, Oracle, PostgreSQL, MySQL, and data warehouse migrations.
What are common data mapping examples?
Common examples include SQL Server to PostgreSQL migration, Oracle to MySQL migration, Excel to SQL Server import, CSV import, JSON API loading, XML to relational tables, CRM to ERP integration, and warehouse staging.
What are common data mapping techniques?
Common techniques include visual mapping, manual mapping documents, metadata-driven mapping, SQL expressions, lookup tables, business rules, schema discovery, Python scripting, and AI-assisted mapping.
How do lookup-based mappings work?
Lookup-based mappings use a reference table to translate a source value into a target value. For example, a country name from the source may be looked up to find the target `country_id`.
How is data mapping different from schema mapping?
Data mapping focuses on field relationships. Schema mapping or schema transformation focuses on the structure of tables, files, nodes, keys, and relationships. They often work together during migrations.
What should a source-to-target mapping document include?
It should include source name, source field, target table, target field, target type, required flag, mapping rule, lookup rule, default value, validation rule, rejection rule, and notes.
When should data mapping not be automated yet?
Do not automate it when source meaning is unknown, target requirements are disputed, or exception rules have not been agreed. Map a small sample first and get sign-off before scheduling production loads.
Does Advanced ETL Processor support data mapping?
Yes. Advanced ETL Processor supports visual mappings, drag-and-drop field links, expressions, lookup tables, SQL, Python, workflow automation, and AI workflows for repeatable ETL data mapping.
Automate Data Mapping in Advanced ETL Processor
Advanced ETL Processor automates mapping for CSV, Excel, XML, JSON, SQL Server, Oracle, PostgreSQL, MySQL, and other sources.
If the mapping is a one-off and low risk, a spreadsheet may be enough. If it runs every week, download the 30-day fully functional trial and automate it.
Map the fields. Test the rules. Keep the source evidence. The target will sleep better.