About Vertica SQL in transformation stage


According to the data engineering track, the Celonis transformation uses Vertica SQL. I searched online and find Vertica SQL website: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/ConceptsGuide/Other/SQLOverview.htm?tocpath=SQL%20Reference%20Manual|_____1

I follow the Vertica SQL examples and syntax, but for some examples, I cannot execute them in Celonis transformation statement editor part. Why this happens? Where can I find the exact documentation/manuals for Celonis transformation?


Hi David,

you are right! Celonis uses Vertica for the transformations. It would be great if you could provide an example of what does not work and then we could debug it together.



Hi, Justin.

For example, https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/DataTypes/Date-Time/TIMESTAMP.htm?tocpath=SQL%20Reference%20Manual|SQL%20Data%20Types|Date%2FTime%20Data%20Types|_____7
SELECT (TIMESTAMP ‘2014-01-17 10:00’ - TIMESTAMP ‘2014-01-01’);
The output of Celonis is [object Object]; but I modified a little bit and it works:
SELECT TO_CHAR(TIMESTAMP ‘2014-01-17 10:00’ - TIMESTAMP ‘2014-01-01’);

Another things are how to implement in Celonis:
SELECT MAX(1,2,3);
Is it possible to set variables in Celonis?


Another question:

SELECT ‘1970-01-01 00:00:00’::TIMESTAMP + INTERVAL ‘1572508986 second’; --work in Celonis
SELECT “T”.“TRANS_TIME” || ’ second’ FROM “T”; --work in Celonis
Note: “T”.“TRANS_TIME” are numbers, such as 1572508986

But the below statement doesn’t work, why?
SELECT ‘1970-01-01 00:00:00’::TIMESTAMP + INTERVAL “T”.“TRANS_TIME” || ’ second’ FROM “T”

Error shows: Execution error: [Vertica]VJDBC ERROR: Syntax error at or near ““T””