ETL Mapping: Mapping Document Template, Rule Types and a SQL Example

ETL mapping is the specification of how each field in a source system becomes a field in a target table: which source column it comes from, what happens to it on the way, and what it must look like when it lands. It is usually written down as a source-to-target mapping document, one row per target field, and then implemented as transformation code.

A mapping answers four questions for every target column:

1. Where does it come from? Source system, table and field.
2. What happens to it? The transformation rule, written precisely enough that two engineers would write the same code.
3. What must be true when it lands? Type, nullability, key, allowed values.
4. Who decides? The owner who signs off on the business rule.

This guide covers the mapping document (with a template you can copy), the five types of mapping rule, manual versus automated mapping, how ELT moves mapping into SQL models, and how to test a mapping. Then it works through a complete example: a HubSpot deals extract and a Stripe charges extract mapped into one fct_revenue table, with the mapping rows, the SQL that implements them, and two test queries with their results.

What is data mapping in ETL?

Every pipeline maps data, whether or not anyone wrote the mapping down. When a sync copies a Stripe amount of 150000 into a warehouse column, and a model later turns it into 1500.00 EUR, a mapping decision was made: Stripe stores amounts in the smallest currency unit, and someone decided the target should hold major units.

Data mapping in ETL is the practice of making those decisions explicit and reviewable before they are buried in code. It matters most in three situations:

• Several sources describe the same thing. HubSpot calls it a deal and Stripe calls it a charge, but finance wants one revenue table. The mapping decides how they line up.
• The target is shared. A table that feeds dashboards, reverse ETL syncs and AI agents needs one documented definition of each column, or every consumer guesses.
• The pipeline outlives its author. A mapping document is how the next person finds out why amount is divided by 100 except for yen.

In graphical ETL tools such as Informatica PowerCenter, "mapping" is also the name of the saved object that implements one source-to-target flow. In SQL-based stacks the same thing is a model: a query that reads source tables and produces the target.

The source-to-target mapping document

The mapping document is a table. Each row describes one target field (or one row filter), and each column records one fact about it. These are the columns that pay for themselves:

Keep it in a spreadsheet or a markdown file next to your models, one tab or file per target table. Two habits make it useful:

• Write rules as testable statements. "Clean up currency" is not a rule. "Upper-case the ISO code; empty means EUR" is a rule, and it gives you an accepted-values test for free.
• Add the row filters. Which source rows are included at all (only won deals, only successful charges, no test data) changes totals more than any column rule, and it is the part most mapping documents leave out.

The five types of mapping rule

Most rules in a mapping document fall into one of five types. Naming the type helps reviewers, because each type fails in its own way.

Two more show up in real documents often enough to name: a default (what to write when the source is empty) and a constant (a value the source does not have at all, such as source_system = 'stripe'). Treat them as special cases of direct mapping.

An aggregate mapping usually deserves its own target table. If fct_revenue is one row per revenue event, monthly totals belong in a separate model built from it:

1select
2    date_trunc(revenue_date, month) as month
3  , currency
4  , sum(
5        case
6            when revenue_type = 'booked' then amount
7            else 0
8        end
9    ) as booked
10  , sum(
11        case
12            when revenue_type = 'collected' then amount
13            else 0
14        end
15    ) as collected
16from
17    {{marts.fct_revenue}}
18group by
19    1
20  , 2

Manual, semi-automated and automated mapping

The other common way to classify mapping is by how the rules get written and maintained.

In practice most teams end up semi-automated. The extract and load side is a solved problem: a managed connector decides how a Stripe charge or a HubSpot deal lands as a table, keeps it up to date and handles schema changes. The business side is not: whether a refunded charge counts as revenue, or whether a deal in "contract sent" is booked, is a decision a person makes once and writes down.

AI assistants have moved the line a little. An assistant that can read your warehouse schema can draft the SQL for a mapping row, but it still needs the rule. The mapping document is a good prompt: give it the rows, and review the SQL against them.

How ELT changes where mapping happens

In classic ETL, mapping runs inside the ETL tool before the data reaches the warehouse. The warehouse only ever sees the mapped result, so a wrong rule means re-extracting from the source.

In ELT, the order changes:

1. Extract and load raw. The source tables land in the warehouse close to how the source shapes them. The only mapping at this stage is technical: column names, types and how nested fields are flattened.
2. Map in SQL models. Each target table in the mapping document becomes a SQL model that reads the raw tables. The model is the executable form of the mapping.
3. Test the models. The nullability, key and accepted-value columns of the mapping document become tests.

The practical gain is that a mapping mistake is a code change, not a reload. The raw data is still there, so you fix the rule and rebuild the model. You can also add a target field next month without touching the extract. If you are deciding between the two patterns, ETL vs ELT goes through the trade-offs, and medallion architecture shows how the raw, cleaned and business-ready layers stack up.

This is how it looks in Weld. A data source syncs each selected table into a schema in your warehouse on a schedule, and every synced table gets a _weld_synced column with the time the row was last written (Weld-managed fields). Some mapping can happen at load: you can leave a column out of a sync, or sync a hashed value instead of the original, for example for email addresses. Business rules go in data models: SQL that references synced tables as {{raw.<source>.<table>}} and is published to the warehouse as a view or a table. Those references are what Weld uses to draw lineage from source table to target.

Worked example: HubSpot deals and Stripe charges into fct_revenue

The goal is one table, fct_revenue, with one row per revenue event: booked revenue from closed-won HubSpot deals and collected revenue from successful Stripe charges. Finance can then compare the two by customer and month.

Both sources are synced into the warehouse as raw tables. The column names below are the ones in Weld's HubSpot and Stripe schemas (browse them on the HubSpot schema and Stripe schema pages): deal, pipeline_stage and charge. One column is an assumption: HubSpot has no built-in Stripe customer ID on a deal, so the example uses a custom deal property, stripe_customer_id, which lands as properties_stripe_customer_id like other deal properties.

The source data

Three deals:

Four charges:

Stripe stores amounts in the currency's smallest unit, so 600000 in EUR is 6,000.00, while yen has no minor unit and 90000 is 90,000 JPY (Stripe: supported currencies). In HubSpot, every deal stage carries a probability, and the API defines 1.0 as closed won and 0.0 as closed lost (HubSpot: pipelines API). Matching on probability instead of the stage ID closedwon keeps the rule working in custom pipelines, where stage IDs are numbers.

The mapping document

Because the target has two sources, this version puts the HubSpot and Stripe rules side by side, one row per target field plus the row filters:

In a full document, each row would also name an owner (sales operations for the deal rules, finance for the charge rules) and keep notes such as "EUR is the account's company currency; confirm before adding a second entity". Writing the document surfaced one gap before any SQL existed: deal 9002 has no Stripe customer ID, so booked and collected revenue for that customer will not line up until sales fills in the property.

The SQL that implements it

Each mapping row becomes one line of SQL, and each row filter becomes a where clause. The model below is written for BigQuery and uses Weld's {{raw.<source>.<table>}} references; in another tool, replace them with your schema and table names.

1-- fct_revenue: booked revenue from HubSpot, collected revenue from Stripe
2with
3    won_deals as (
4        select
5            d.*
6          , s.label as stage_label
7        from
8            {{raw.hubspot.deal}} d
9            join {{raw.hubspot.pipeline_stage}} s on s.stage_id = d.properties_dealstage
10            and s.pipeline_id = d.properties_pipeline
11        where
12            s.metadata_probability = 1.0 -- lookup: closed won
13            and not d.archived
14    )
15  , hubspot_revenue as (
16        select
17            concat('hs_', cast(deal_id as string)) as revenue_id
18          , 'hubspot' as source_system
19          , 'booked' as revenue_type
20          , properties_stripe_customer_id as customer_id
21          , date(properties_closedate) as revenue_date
22          , cast(properties_amount as numeric) as amount
23          , upper(
24                coalesce(
25                    nullif(trim(properties_deal_currency_code), '')
26                  , 'EUR'
27                )
28            ) as currency -- default, empty or blank too
29          , stage_label as deal_stage
30          , _weld_synced as synced_at
31        from
32            won_deals
33    )
34  , stripe_revenue as (
35        select
36            concat('st_', id) as revenue_id
37          , 'stripe' as source_system
38          , 'collected' as revenue_type
39          , customer_id
40          , date(created) as revenue_date
41          , case
42                when upper(currency) in ('JPY', 'KRW') -- zero-decimal currencies
43                then cast(amount - amount_refunded as numeric)
44                else cast(amount - amount_refunded as numeric) / 100
45            end as amount
46          , upper(currency) as currency
47          , cast(null as string) as deal_stage
48          , _weld_synced as synced_at
49        from
50            {{raw.stripe.charge}}
51        where
52            status = 'succeeded' -- conditional: only money that arrived
53            and livemode
54    )
55select
56    *
57from
58    hubspot_revenue
59union all
60select
61    *
62from
63    stripe_revenue

On the sample data it returns five rows. Deal 9003 is dropped by the lookup (its stage has probability 0.9), and charge ch_2 by the status filter:

October totals: 15,000 EUR booked, 7,000 EUR and 90,000 JPY collected. Charge ch_3 counts as 1,000, not 1,500, because 500 of it was refunded.

Two tests for the mapping

The first test checks the nullability, key and accepted-value columns of the mapping document in one pass. Every column except row_count should be 0:

1select
2    count(*) as row_count
3  , count(*) - count(distinct revenue_id) as duplicate_ids
4  , countif(revenue_id is null) as null_ids
5  , countif(
6        revenue_date is null
7        or amount is null
8        or currency is null
9    ) as null_required
10  , countif(source_system not in ('hubspot', 'stripe')) as bad_source_system
11  , countif(revenue_type not in ('booked', 'collected')) as bad_revenue_type
12  , countif(amount < 0) as negative_amounts
13from
14    {{marts.fct_revenue}}

On the sample data: row_count 5, everything else 0.

The second test reconciles the target to the source. It recomputes row counts and totals per month and currency straight from the raw Stripe table, applying the same row filter, and returns only the months where source and target disagree. No rows means the mapping holds:

1with
2    source_totals as (
3        select
4            date_trunc(date(created), month) as month
5          , upper(currency) as currency
6          , count(*) as source_rows
7          , sum(
8                case
9                    when upper(currency) in ('JPY', 'KRW') then cast(amount - amount_refunded as numeric)
10                    else cast(amount - amount_refunded as numeric) / 100
11                end
12            ) as source_amount
13        from
14            {{raw.stripe.charge}}
15        where
16            status = 'succeeded'
17            and livemode
18        group by
19            1
20          , 2
21    )
22  , target_totals as (
23        select
24            date_trunc(revenue_date, month) as month
25          , currency
26          , count(*) as target_rows
27          , sum(amount) as target_amount
28        from
29            {{marts.fct_revenue}}
30        where
31            source_system = 'stripe'
32        group by
33            1
34          , 2
35    )
36select
37    *
38from
39    source_totals
40    full outer join target_totals using (month, currency)
41where
42    source_rows is distinct from target_rows
43    or source_amount is distinct from target_amount

On the sample data it returns nothing. If the model forgot to subtract refunds, it would return one row for October EUR with a source amount of 7,000 and a target amount of 7,500. The same pattern works for HubSpot: count won deals and sum their amounts from the raw tables, then compare with the booked rows.

We ran the model and both tests on DuckDB with the sample rows above and got the results shown. The queries are written in BigQuery syntax; in Snowflake, use count_if instead of countif and date_trunc('month', ...) instead of date_trunc(..., month).

How to test any ETL mapping

The two queries above cover the checks that catch most mapping errors. For any target table, work through this list:

• Row counts. Source rows that pass the filters should equal target rows, per source and per period. A mismatch means a join fanned out or a filter is wrong.
• Uniqueness. The primary key in the mapping document must be unique. Duplicates usually come from joining to a table at a finer grain than you thought.
• Not null. Every field marked not nullable must have no nulls. A sudden jump in nulls often means a source column was renamed.
• Accepted values. Constants, statuses and codes must be in the documented set. New values at the source show up here first.
• Reconciliation to source totals. Sums of money and quantities should match the source per month and currency, after the documented filters. This is the test finance trusts.
• Spot checks. Pick three or four records, such as a refunded charge or a deal with no currency, and follow them by hand from source to target.

Run them every time the model rebuilds, not only once. A mapping that was right in October breaks when someone adds a pipeline, a currency or a new charge status in November.

Common ETL mapping mistakes

• Units. Minor versus major currency units, milliseconds versus seconds, percentages stored as 0.15 or 15.
• Grain. Joining deals to line items and then summing deal amounts. Decide the grain of the target first and write it at the top of the document.
• Time zones. Converting a timestamp to a date in UTC when the business reports in local time moves revenue across month boundaries.
• Undocumented filters. Test data, archived records and internal accounts removed in the code but not in the document, so nobody can explain why the totals differ from the source system.
• Mapping by name. Two fields called status in two systems rarely mean the same thing. Map by meaning, and write down the meaning.

Mapping in Weld

Weld covers both halves of an ELT mapping. Its data sources handle extract and load: each table lands in your warehouse, syncs incrementally where the source allows it, and carries _weld_synced. Your mapping rules live in data models, SQL published to the warehouse as views or tables. Lineage shows which raw tables feed each model, and Ed, Weld's AI agent, can write a model from your description and checks the SQL against your warehouse before it proposes it. Paste in the rows of your mapping document and review what it writes against them.

FAQ