Working with Transformation Flows

A transformation flow is designed for in-platform data shaping, enrichment, and consolidation. It reads from one or more source objects already available inside SAP Datasphere, applies transformation logic, and writes the result into a target local table.

Transformation flows are part of the ELT (Extract, Load, Transform) paradigm, meaning data is first loaded into SAP Datasphere (via a replication flow or other means) and then transformed. A transformation flow is a one-time event that completes when the relevant data is successfully loaded to the target table. It can be run multiple times, for example to load incremental changes.

Sources are objects within SAP Datasphere and the target is always a local table within the same SAP Datasphere space.

A transformation flow offers the following load types:

• Initial Only: Loads the full set of data from the source table(s) to the target table. This also functions as an UPSERT (INSERT + UPDATE): existing records in the target are updated, while new records are inserted. A Delete All Before Loading option is also available to clear all existing records before loading new ones.
• Initial and Delta: On the first run, the system loads the full set of data to the target table. On subsequent runs, only delta changes (inserts, updates, and deleted records) are loaded to the target table. To use this load type, Delta Capture must be enabled on both the source table and the target table.

Transformation flows are supported in spaces with storage type SAP HANA Database (Disk and In-Memory) and storage type SAP HANA Data Lake Files. When using a space with storage type SAP HANA Data Lake Files, the runtime is automatically set to Apache Spark instead of SAP HANA. Python script operators can be used and native large-scale parallel data processing on object store data is enabled.

Creating a transformation flow involves the following steps:

1. Choose the View Transform node.

   You can choose the following transform types:

   • Graphical View Transform: a visual, drag-and-drop editor with operator nodes. This is recommended for most use cases.
   • SQL View Transform: a code editor where you write SQL directly. You use this for complex logic that is easier to express in SQL than through the graphical interface.

2. Add sources.

   The following SAP Datasphere sources are available:

   • Local tables
   • Views referencing local tables
   • Open SQL Schema objects
   • Remote tables in SAP BW Bridge spaces
   • Views referencing remote tables in SAP BW Bridge spaces
   Note

   For details about the requirements of these sources, you can find more information here: https://help.sap.com/docs/SAP_DATASPHERE/c8a54ee704e94e15926551293243fd1d/f7161e6c20204672ac4a6d90c81762e4.html?locale=en-US&version=LATEST

3. Add transformation logic.

   You can use SQL-based transformations (join, union, projection, filter, aggregation and calculated columns). Python script is only available when using the Spark runtime.

4. Add a target table.

   You can define only one target per flow. You can create a new local table directly from the flow or map to an existing one. In the target tables properties, you can specify the load type and delta capture. Verify the column mapping between the view transform output and the target table. Adjust data types or column assignments if necessary.

Before you run a transformation flow, you can use Simulate Run to execute the flow without writing data to the target and verify the logic. You can use the option Explain Plan to inspect the execution plan and operator details.

A transformation flow can be started form the Data Builder editor, the Data Integration Monitor or via a task chain.

A transformation flow run is a one-time event. If it fails, it must be re-run manually. There is no automatic restart from the point of failure. Because transformation flows do not maintain an active connection like replication flows, there is no pause/resume concept. The flow is simply re-executed when needed. For the Initial and Delta load type, a watermark is maintained. If needed, the watermark can be reset to force a full reload on the next run without redeploying the flow.

In spaces with storage type SAP HANA Database (Disk and In-Memory), transformation logic is pushed down to the HANA engine, which handles parallelism natively. Batch processing can be configured to break large datasets into chunks to avoid out-of-memory errors during HANA runtime runs. When using a space with storage type SAP HANA Data Lake Files, Spark manages distributed parallel processing automatically.

Transformation flows have a dedicated Schedule button available both in the Data Builder editor and in the Data Integration Monitor. You can also assign a transformation flow to a task chain.

Transformation flows support input parameters, which allow dynamic filtering or configuration at runtime. Parameters can be passed when running the flow from the editor, from a schedule, or from a task chain. This enables the same flow to be reused with different filter values.