Source-to-Target Mapping (SSM) Studio

The Source-to-Target Mapping (SSM) Studio automates the design, specification, and code generation required to ingest source operational systems into canonical analytical schemas, data warehouses, or lakehouses.

---

1. SSM Concepts & Workflow

An SSM Workbook represents a structured mapping project that translates one or more source tables into a target schema entity:

flowchart LR
    subgraph Sources["Source Systems"]
        S1["Oracle: CUSTOMER_INFO"]
        S2["Postgres: CUST_KYC"]
    end

    subgraph SSM["SSM Mapping Studio"]
        M1["Column Alignment"]
        M2["Transform Logic & Cleaning"]
        M3["Automated Join Discovery"]
    end

    subgraph Target["Target Dimension / Lakehouse"]
        T1["Databricks Delta: DIM_CUSTOMER"]
    end

    S1 --> M1
    S2 --> M1
    M1 --> M2
    M2 --> M3
    M3 -->|Generated SQL / Spark Job| T1

---

2. Creating a Mapping Workbook

1. Navigate to SSM Studio and click New Mapping.
2. Provide a Name (e.g., MAP_CORE_TO_DIM_CUSTOMER) and select the target system dialect (Databricks SQL, Snowflake, PostgreSQL, or ANSI SQL).
3. Select the Target Dataset: Choose an existing catalog schema or define target column attributes on the fly.
4. Select the Source Tables: Add all operational tables that contain fields needed for the target entity.

---

3. Visual Column Mapping & Expressions

In the mapping canvas, configure each target column row:

Field	Description	Example
Target Column	Name and datatype of the destination attribute	FULL_NAME VARCHAR(255)
Source Field(s)	Dropdown selector mapping one or more source columns	CUSTOMER_INFO.FIRST_NAME, CUSTOMER_INFO.LAST_NAME
Transformation Logic	SQL expression, formatting, or business logic	TRIM(UPPER(FIRST_NAME || ' ' || LAST_NAME))
Preprocessing	Filtering or null-handling directives	COALESCE(MIDDLE_NAME, '')
Business Rule	Documentation explaining why the mapping exists	"Format full name in uppercase for regulatory reporting"

[!TIP] Use the ColumnInput selector to pick multiple source columns across tables. VDA registers precise { table_uri, column_name } linkages, enabling automatic column lineage registration upon saving.

---

4. Automated Join Path Resolution

When mapping fields from multiple source tables, VDA's relationship engine automatically determines the necessary join paths: 1. Inspects primary key, foreign key, and historical semantic lineage relationships. 2. Formulates the minimal join graph (e.g., FROM CUSTOMER_INFO c JOIN CUST_KYC k ON c.CUST_ID = k.CUST_ID). 3. You can review, override, or specify custom join conditions directly in the Join Graph tab.

---

5. Generating & Exporting Production SQL

Click Generate SQL to produce production-grade ETL code: - Automatically formats CTEs, join statements, datatype casting, and column aliases. - Verified against the target dialect syntax rules. - Export options: - Copy SQL directly to clipboard. - Download as a standalone .sql script. - Commit directly to Git via the integrated Git Control Service.