How to map source data to a target model in DataLens
The document every migration needs and few teams have: which source column becomes which target column, and which ones nothing feeds.
What you will do
You will define the target structure you want, map source columns onto it, and see clearly which target columns have a source and which do not.
The gaps are the valuable output. A mapping that shows five unmapped target columns has told you about five decisions nobody has made yet.
When this is useful
- You are migrating from one system to another and need the mapping agreed before anyone builds.
- You are designing a warehouse layer and need to show where each column comes from.
- A build has stalled on "where does this field come from?" and the answer lives in three heads.
- You need a source-to-target document for a handover or an approval.
Before you start
- A source model — Build it from your loaded datasets first — see the source model guide.
- A view on the target — Either an existing target structure, or enough of a design to start. The mapping process itself will expose the parts you had not decided.
Steps
Open Model → Target Model
Select Model, then the Target Model tab.
The target model is the structure you intend to produce. It is deliberately separate from the source model so that "what we have" and "what we want" never get confused with each other.
Define the target structure
Define the tables and columns you want to end up with, including their types.
Mark facts and dimensions where the distinction applies. Doing it now shapes the mapping that follows.
Target tables on the canvas with their columns.
Open the Mapping tab
Switch to Mapping. This is where source and target meet, column by column.
Source columns on one side and target columns on the other.
Map the columns
Use Map Columns to connect each source column to the target column it feeds.
Work through the target rather than the source. Every target column needs an answer; not every source column needs a destination.
Mapped columns showing their connection.
Find the gaps
Look at the target columns with nothing mapped to them. Each one is a real question: is it derived, does it come from a system not yet connected, or was it never actually needed?
This list is the most useful thing the mapping produces. Answer it before anyone starts building.
A visible set of unmapped target columns.
Set the execution order
Where the target is built in stages, use Execution Order to record the order things must run in. Nodes carry a stage — STAGE 0 for raw and STAGE 1 for what depends on it.
This is what turns a mapping into something buildable rather than merely descriptive.
Export the mapping
Export the model as SQL DDL to create the target structure, or as JSON or CSV to share the mapping itself.
A Source-to-Target Mapping document is also available from the document generation screen if you need a written version.
The exported mapping in your chosen format.
What happens next
A completed mapping is what a pipeline implements. Take it into Orchestrate to build the pipeline that performs the transformation.
The Source-to-Target Mapping document type produces the written artefact that migrations and handovers usually require.
Example
A team is consolidating three order systems into one model. Mapping reveals that two of the three have no equivalent of the target's fulfilment_channel column. That gap — found during mapping rather than during testing — changes the plan while changing the plan is still cheap.
Tips
- Work from the target backwards. Every target column needs a source or a decision; source columns without a destination are usually fine.
- Treat unmapped target columns as your outstanding-decisions list, and resolve them before build starts.
- Set execution order as you go. Reconstructing dependency order later is much harder than recording it now.
- Export DDL once the target is agreed — it gives the build team something concrete rather than a diagram.
Limitations
- The mapping records structure and relationships. Complex row-level transformation logic belongs in Prepare or a pipeline, not in the mapping.
- A source column must exist in a loaded dataset to be mapped.
- Exporting DDL produces the statements. Running them against your database is done by you, outside DataLens.
- Two legacy model tabs remain available alongside the current ones; use the current Source Model and Target Model tabs for new work.
Related how-to guides
Explore a source model
See the tables, columns and relationships of a system you did not design — as a diagram you can read.
Build a data pipeline
Turn a sequence of steps into a repeatable pipeline with a defined execution order.
Generate a data document
Produce an architecture document, a model spec, a mapping or a runbook from what you have already built.
Join datasets and create views
Join datasets on the keys that relate them and save the result as a reusable view.
Related questions
More of these on the DataLens FAQ page.
Can DataLens produce a source-to-target mapping?
Yes. Define the target model, then use the Mapping tab to connect source columns to target columns. The unmapped target columns are shown clearly, which is usually the most useful output — each one is a decision that has not been made yet. A written Source-to-Target Mapping document can also be generated.
What is the difference between a source model and a target model?
The source model documents the structure you actually have. The target model describes the structure you intend to produce. They are separate screens on purpose, so that current state and intended state never get conflated — and the mapping between them is where the real work shows up.
Try this in DataLens
DataLens is in private beta. Request access and work through this guide on your own data.
Request beta access