Mapping #
Mapping represents transformations between tables. It is based on SQL definition, i.e., it expects that a data engineer can write SELECT statements and use them for modeling transformations into the target structure. It has an advantage when it is necessary to approve transformations with business users—they can evaluate if the target dataset contains all necessary data. The SQL command can be easily transformed into core mapping metadata.
The output scripts can be based on this SQL command (PL/SQL procedure, PySpark script), but it is not mandatory—mapping contains metadata that allows it to be used in pure Python transformation scripts using libraries like Pandas, etc.
Mapping stereotype #
Mapping stereotype is an important attribute that determines the ETL pattern and proper template selection. A template should exist for each stereotype to generate an output script with proper behavior. The built-in stereotypes are described in the table:
| Stereotype | Type | Description |
|---|---|---|
| Reference table mapping | behavioral | Mapping with this stereotype is automatically generated when the table stereotype is set to Reference. It does not contain explicit column mappings because both reference and translation mappings can be derived from the reference table structure. |
| History generic mapping | behavioral | Mapping with this stereotype is automatically generated when a table is marked as having a historical table. Historical mapping does not contain explicit column mappings because ETL to the historical table (SCD4) can be derived from the table structure |
| File generic | behavioral | Generic stereotype available for tables with Interface stereotype |
| File custom | behavioral | Stereotype used for data ingestion from files with an explicit column mapping |
| DB link generic | behavioral | Generic stereotype available for tables with Interface stereotype |
| DB link custom | behavioral | Stereotype used for data ingestion from a database with an explicit column mapping |
| Diff update | default | Most common patterns for data transformations |
| Diff merge | default | Most common patterns for data transformations |
| Diff insert | default | Most common patterns for data transformations |
Other stereotypes with the type of Custom can be added in Settings/Stereotypes.
Overview #
The Overview card contains all attributes described in Overview card description in the common area of this documentation.
The mapping-specific attributes here are:
| Attribute | Description |
|---|---|
| Truncate | Flag used in script to delete table content before loading data. |
| Distinct | Flag used in script to work with distinct key values. |
Sources #
As mentioned above, mapping is typically based on SQL commands. The Sources tab contains data from the FROM clause, ideally in structured form to keep links between sources and targets (it is possible to write the whole FROM clause to the TEXT tab, but it is better to break it into separate parts).
Each source has a stereotype—it specifies how the source is joined. Options are:
- FROM
- JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL OUTER JOIN
It is possible to use several kinds of sources:
- TABLE
- VIEW
- SUBSELECT
- DUMMY
The rest of the attributes are known from SQL:
| Attribute | Description |
|---|---|
| Source object | Source object is selected from the list of objects of the selected type |
| Owner | Owner of the selected object, one of the System users |
| Source subselect | Only for SUBSELECT stereotype. It allows entering a subselect. |
| Expression | How the source is connected to the rest of the sources (ON condition) |
| Source Context | Other text metadata that can be used in the template for specific purposes |
| Sources description | A free text field that allows commenting on sources if necessary |
| Criteria | WHERE clause from the SQL |
| Criteria description | Description of the criteria |
| Target criteria | HAVING clause from the SQL. |
| Target criteria description | Description of the target criteria |
Column mappings #
Column mappings tab contains information about algorithms that fill each column.
| Attribute | Description |
|---|---|
| Key flag | This flag marks columns used for records identification |
| Update flag | This flag marks columns that should be updated by the mapping |
| Group by flag | Used for aggregation key columns |
Users can add columns one by one or add a set of columns by clicking the Fill columns… button.
Others #
To enhance the flexibility of mappings, there are several additional metadata fields that can be used in templates to enrich the generated script. These fields can be utilized by their specific names (e.g., Pre-mapping script or Post-mapping script) or used freely as part of the final script.
Mapping context menu #
There are mapping actions available in the project tree:
| Action | Description |
|---|---|
| Open in new tab | Opens a new tab with the mapping |
| Lock/Unlock | Management of locks |
| Duplicate | Creates a copy of the mapping |
| Delete | Deletes the mapping |
| Export | Exports mapping metadata in JSON format |