Step 1: Configuring Real-Time Transformations for Data Model Tables

To convert your standard Data Model tables into Real-Time Transformations, follow these steps to transition from "Batch" processing to a "Delta" approach.

Before you begin

Before configuring your real-time transformations, ensure you meet the following:

Data Pool Setup: You must have a Data Pool connected to a source system using the Real-Time Extraction (Replication Cockpit).
Target OCDM: The Object-Centric Data Model must be created and the target table schema defined.
Permissions: You must have "Edit" permissions for the Data Pool and the Replication Cockpit.

Step 1.1 - Identifying the Trigger Table

In the Replication Cockpit, identify the primary source table (e.g., KNA1) that should initiate the transformation. You will save and execute your SQL statement directly under this table.

Step 1.2 - Selecting the staging table

Modify your SQL to select data from the temporary staging table instead of the base table. This ensures you only process new or changed records.

Staging Table Syntax

The syntax of the Staging Table is defined as _CELONIS_TMP_TABLE_TRANSFORM_DATA → for KNA1 it is _CELONIS_TMP_KNA1_TRANSFORM_DATA

Syntax: Use _CELONIS_TMP_[TableName]_TRANSFORM_DATA.
Example: Change FROM KNA1 to FROM _CELONIS_TMP_KNA1_TRANSFORM_DATA.

Before

DROP VIEW IF EXISTS "O2C_KNA1";

CREATE VIEW "O2C_KNA1" AS (
        SELECT 
        "KNA1".*, 
        CAST("KNA1"."ERDAT" AS DATE) AS "TS_ERDAT",
        CAST("KNA1"."UPDAT" AS DATE) AS "TS_UPDAT"
        FROM "KNA1" AS "KNA1"
    WHERE EXISTS(
        SELECT * 
        FROM "O2C_VBAK_VBAP" AS "C"
        "C"."MANDT" = "KNA1"."MANDT"
                AND "C"."KUNNR" = "KNA1"."KUNNR"
    )
);

After

DROP VIEW IF EXISTS "O2C_KNA1";

CREATE VIEW "O2C_KNA1" AS (
        SELECT 
        "KNA1".*, 
        CAST("KNA1"."ERDAT" AS DATE) AS "TS_ERDAT",
        CAST("KNA1"."UPDAT" AS DATE) AS "TS_UPDAT"
        FROM "_CELONIS_TMP_KNA1_TRANSFORM_DATA" AS "KNA1"
    WHERE EXISTS(
        SELECT * 
        FROM "O2C_VBAK_VBAP" AS "C"
        "C"."MANDT" = "KNA1"."MANDT"
                AND "C"."KUNNR" = "KNA1"."KUNNR"
    )
);

Step 1.3 - Switching from Views to Tables

Because delta transformations require INSERT, UPDATE, and DELETE operations, you must change your target objects from Views to Tables.

Initial Setup: If the table doesn't exist yet, run a one-time "Create Table" script in a Data Job to initialize it.
SQL Change: Update your script from CREATE VIEW to CREATE TABLE.

Before

DROP VIEW IF EXISTS "O2C_KNA1";

CREATE VIEW "O2C_KNA1" AS (
        SELECT 
        "KNA1".*, 
        CAST("KNA1"."ERDAT" AS DATE) AS "TS_ERDAT",
        CAST("KNA1"."UPDAT" AS DATE) AS "TS_UPDAT"
        FROM "_CELONIS_TMP_KNA1_TRANSFORM_DATA" AS "KNA1"
    WHERE EXISTS(
        SELECT * 
        FROM "O2C_VBAK_VBAP" AS "C"
        "C"."MANDT" = "KNA1"."MANDT"
                AND "C"."KUNNR" = "KNA1"."KUNNR"
    )
);

After

DROP TABLE IF EXISTS "O2C_KNA1";

CREATE TABLE "O2C_KNA1" AS (
        SELECT 
        "KNA1".*, 
        CAST("KNA1"."ERDAT" AS DATE) AS "TS_ERDAT",
        CAST("KNA1"."UPDAT" AS DATE) AS "TS_UPDAT"
        FROM "_CELONIS_TMP_KNA1_TRANSFORM_DATA" AS "KNA1"
    WHERE EXISTS(
        SELECT * 
        FROM "O2C_VBAK_VBAP" AS "C"
        "C"."MANDT" = "KNA1"."MANDT"
                AND "C"."KUNNR" = "KNA1"."KUNNR"
    )
);

Step 1.4 - Implementing Delete + Insert Logic

Instead of dropping the entire table, use a two-step approach to incrementally update your data and avoid duplicates:

Delete: Remove existing entries from your Data Model table that share a primary key with records in the staging table.
Insert: Add the new records from the staging table into your Data Model table.

Before

DELETE FROM O2C_KNA1 
WHERE EXISTS (SELECT 1 FROM _CELONIS_TMP_KNA1_TRANSFORM_DATA as NEW_DATA
                                AND O2C_KNA1.MANDT=NEW_DATA.MANDT 
                                AND O2C_KNA1.KUNNR=NEW_DATA.KUNNR);

INSERT INTO O2C_KNA1
        SELECT 
        "KNA1".*, 
        CAST("KNA1"."ERDAT" AS DATE) AS "TS_ERDAT",
        CAST("KNA1"."UPDAT" AS DATE) AS "TS_UPDAT"
        FROM "_CELONIS_TMP_KNA1_TRANSFORM_DATA" AS "KNA1"
    WHERE EXISTS(
        SELECT 1 
                FROM VBAK 
        "VBAK"."MANDT" = "KNA1"."MANDT"
                AND "VBAK"."KUNNR" = "KNA1"."KUNNR" 
                        AND "VBAK"."VBTYP" = '<%=orderDocSalesOrders%>'
    );

After

DELETE FROM O2C_KNA1 
WHERE EXISTS (SELECT 1 FROM _CELONIS_TMP_KNA1_TRANSFORM_DATA as NEW_DATA
                                AND O2C_KNA1.MANDT=NEW_DATA.MANDT 
                                AND O2C_KNA1.KUNNR=NEW_DATA.KUNNR);

INSERT INTO O2C_KNA1
        SELECT 
        "KNA1"."MANDT", "KNA1"."KUNNR", "KNA1"."LAND1", "KNA1"."NAME1", "KNA1"."NAME2", "KNA1"."ORT01",         "KNA1"."PSTLZ", "KNA1"."REGIO", "KNA1"."SORTL", "KNA1"."STRAS", "KNA1"."TELF1", "KNA1"."TELFX", "KNA1"."XCPDK", "KNA1"."ADRNR", "KNA1"."MCOD1", "KNA1"."MCOD2", "KNA1"."MCOD3", "KNA1"."ANRED", "KNA1"."AUFSD", "KNA1"."BAHNE", "KNA1"."BAHNS", "KNA1"."BBBNR", "KNA1"."BBSNR", "KNA1"."BEGRU", "KNA1"."BRSCH", "KNA1"."BUBKZ", "KNA1"."DATLT", "KNA1"."ERDAT", "KNA1"."ERNAM", "KNA1"."EXABL", "KNA1"."FAKSD", "KNA1"."FISKN", "KNA1"."KNAZK", "KNA1"."KNRZA", "KNA1"."KONZS", "KNA1"."KTOKD", "KNA1"."KUKLA", "KNA1"."LIFNR", "KNA1"."LIFSD", "KNA1"."LOCCO", "KNA1"."LOEVM", "KNA1"."NAME3", "KNA1"."NAME4", "KNA1"."NIELS", "KNA1"."ORT02", "KNA1"."PFACH", "KNA1"."PSTL2", "KNA1"."COUNC", "KNA1"."CITYC", "KNA1"."RPMKR", "KNA1"."SPERR", "KNA1"."SPRAS", "KNA1"."STCD1", "KNA1"."STCD2", "KNA1"."STKZA", "KNA1"."STKZU", "KNA1"."TELBX", "KNA1"."TELF2", "KNA1"."TELTX", "KNA1"."TELX1", "KNA1"."LZONE", "KNA1"."XZEMP", 
 "KNA1"."VBUND", "KNA1"."STCEG", "KNA1"."DEAR1", "KNA1"."DEAR2", "KNA1"."DEAR3", "KNA1"."DEAR4", KNA1"."DEAR5", "KNA1"."GFORM", "KNA1"."BRAN1", "KNA1"."BRAN2", "KNA1"."BRAN3", "KNA1"."BRAN4", "KNA1"."BRAN5", "KNA1"."EKONT", "KNA1"."UMSAT", "KNA1"."UMJAH", "KNA1"."UWAER", "KNA1"."JMZAH", "KNA1"."JMJAH", "KNA1"."KATR1", "KNA1"."KATR2", "KNA1"."KATR3", "KNA1"."KATR4", "KNA1"."KATR5", "KNA1"."KATR6", "KNA1"."KATR7", "KNA1"."KATR8", "KNA1"."KATR9", "KNA1"."KATR10", "KNA1"."STKZN", "KNA1"."UMSA1", "KNA1"."TXJCD", "KNA1"."PERIV", "KNA1"."ABRVW", "KNA1"."INSPBYDEBI", "KNA1"."INSPATDEBI", "KNA1"."KTOCD", "KNA1"."PFORT", "KNA1"."WERKS", "KNA1"."DTAMS", "KNA1"."DTAWS", "KNA1"."DUEFL", "KNA1"."HZUOR", "KNA1"."SPERZ", "KNA1"."ETIKG", "KNA1"."CIVVE", "KNA1"."MILVE", "KNA1"."KDKG1", "KNA1"."KDKG2", "KNA1"."KDKG3", "KNA1"."KDKG4", "KNA1"."KDKG5", "KNA1"."XKNZA", "KNA1"."FITYP", "KNA1"."STCDT", "KNA1"."STCD3", "KNA1"."STCD4", "KNA1"."XICMS", "KNA1"."XXIPI", "KNA1"."XSUBT", "KNA1"."CFOPC",   "KNA1"."TXLW1", "KNA1"."TXLW2", "KNA1"."CCC01", "KNA1"."CCC02", "KNA1"."CCC03", "KNA1"."CCC04", "KNA1"."CASSD", "KNA1"."KNURL", "KNA1"."J_1KFREPRE", "KNA1"."J_1KFTBUS", "KNA1"."J_1KFTIND", "KNA1"."CONFS", "KNA1"."UPDAT", "KNA1"."UPTIM", "KNA1"."NODEL", "KNA1"."DEAR6", "KNA1"."/VSO/R_PALHGT", "KNA1"."/VSO/R_PAL_UL", "KNA1"."/VSO/R_PK_MAT", "KNA1"."/VSO/R_MATPAL", "KNA1"."/VSO/R_I_NO_LYR", "KNA1"."/VSO/R_ONE_MAT",     "KNA1"."/VSO/R_ONE_SORT", "KNA1"."/VSO/R_ULD_SIDE", "KNA1"."/VSO/R_LOAD_PREF", "KNA1"."/VSO/R_DPOINT", "KNA1"."ALC", "KNA1"."PMT_OFFICE", "KNA1"."PSOFG", "KNA1"."PSOIS", "KNA1"."PSON1", "KNA1"."PSON2", "KNA1"."PSON3", "KNA1"."PSOVN", "KNA1"."PSOTL", "KNA1"."PSOHS", "KNA1"."PSOST", "KNA1"."PSOO1", "KNA1"."PSOO2", "KNA1"."PSOO3", "KNA1"."PSOO4", "KNA1"."PSOO5", "KNA1"."ZZVPOPNE", "KNA1"."ZZKFZTYPMIN", "KNA1"."ZZKFZTYPMAX",      "KNA1"."ZZHAENGERKZ", "KNA1"."ZZTOBACCOCUST", "KNA1"."ZZTOBCUSTDAT", "KNA1"."ZZF_TYPE", "KNA1"."ZZTOBSERVICE",
        CAST("KNA1"."ERDAT" AS DATE) AS "TS_ERDAT",
        CAST("KNA1"."UPDAT" AS DATE) AS "TS_UPDAT"
        FROM "_CELONIS_TMP_KNA1_TRANSFORM_DATA" AS "KNA1"
    WHERE EXISTS(
        SELECT 1 
                FROM VBAK 
        "VBAK"."MANDT" = "KNA1"."MANDT"
                AND "VBAK"."KUNNR" = "KNA1"."KUNNR" 
                        AND "VBAK"."VBTYP" = '<%=orderDocSalesOrders%>'
    );

Step 1.5 - Removing temporary tables

Real-Time Transformations do not support global temporary tables. Instead, implement any necessary temporary logic as subqueries directly within your main transformation script. Always select from raw tables extracted via the Replication Cockpit, never from tables created in other transformations.

Before

DELETE FROM O2C_KNA1 
WHERE EXISTS (SELECT 1 FROM _CELONIS_TMP_KNA1_TRANSFORM_DATA as NEW_DATA
                                AND O2C_KNA1.MANDT=NEW_DATA.MANDT 
                                AND O2C_KNA1.KUNNR=NEW_DATA.KUNNR);

INSERT INTO O2C_KNA1
        SELECT 
        "KNA1".*, 
        CAST("KNA1"."ERDAT" AS DATE) AS "TS_ERDAT",
        CAST("KNA1"."UPDAT" AS DATE) AS "TS_UPDAT"
        FROM "_CELONIS_TMP_KNA1_TRANSFORM_DATA" AS "KNA1"
    WHERE EXISTS(
        SELECT * 
        FROM "O2C_VBAK_VBAP" AS "C"
        "C"."MANDT" = "KNA1"."MANDT"
                AND "C"."KUNNR" = "KNA1"."KUNNR"
    );

After

DELETE FROM O2C_KNA1 
WHERE EXISTS (SELECT 1 FROM _CELONIS_TMP_KNA1_TRANSFORM_DATA as NEW_DATA
                                AND O2C_KNA1.MANDT=NEW_DATA.MANDT 
                                AND O2C_KNA1.KUNNR=NEW_DATA.KUNNR);

INSERT INTO O2C_KNA1
        SELECT 
        "KNA1".*, 
        CAST("KNA1"."ERDAT" AS DATE) AS "TS_ERDAT",
        CAST("KNA1"."UPDAT" AS DATE) AS "TS_UPDAT"
        FROM "_CELONIS_TMP_KNA1_TRANSFORM_DATA" AS "KNA1"
    WHERE EXISTS(
        SELECT 1 
                FROM VBAK 
        "VBAK"."MANDT" = "KNA1"."MANDT"
                AND "VBAK"."KUNNR" = "KNA1"."KUNNR" 
                        AND "VBAK"."VBTYP" = '<%=orderDocSalesOrders%>'
    );

Note

Only select from raw tables (the tables that are extracted via the Replication Cockpit). Do not use tables that are created within other transformations!

Step 1.6 - Explicitly reference columbs

To prevent the transformation from breaking during source system metadata changes, avoid using SELECT *. Specifically list every column name required for your Data Model table.

Step 1.7 - Defining dependencies

Identify any tables used in INNER JOIN or EXISTS statements and list them as Dependencies in the Replication Cockpit. This ensures the transformation only runs when all necessary related data is synchronized.

Next steps

Step 2: Setting up Real-Time Transformations for Activities

Step 1: Configuring Real-Time Transformations for Data Model Tables

Before you begin

Step 1.1 - Identifying the Trigger Table

Step 1.2 - Selecting the staging table

Step 1.3 - Switching from Views to Tables

Step 1.4 - Implementing Delete + Insert Logic

Step 1.5 - Removing temporary tables

Step 1.6 - Explicitly reference columbs

Step 1.7 - Defining dependencies

Next steps

Search results