PM Automation Cloud - Import Transformation

[nisha.sonawane]

Hello There,

I am working PM automation Cloud and trying to Edit the Transformation,

I have created, Cases_Event_input.sql file with one sample jinja sql query and trying to import this transformation with my PM app,

I am able to execute query successfully with dbt build command and the same is also reflecting with my SQL server, I can see new table and View is created with respect to my query.

However, whenever I am trying to import this transformation - (creating zip file of my project folder and importing same in PM app) it gives me error for one of the filed/column as invalid identifier.

Please suggest me, not getting any reference and things are executing successfully in VS with dbt build and same is reflecting in SQL server as well, please suggest

[nisha.sonawane]

Error Log -

03:45:36 PM 10 of 19 ERROR creating table model a13fa723_2655_20230509101407.Cases_Event_input_table [e[31mERRORe[0m in 0.51s]
03:45:36 PM 9 of 19 ERROR creating view model a13fa723_2655_20230509101407.Cases_Event_input [e[31mERRORe[0m in 0.86s]
03:45:50 PM e[33mDatabase Error in model Cases_Event_input_table (models/5_business_logic/Cases_Event_input_table.sql)e[0m
03:45:50 PM 000904 (42000): SQL compilation error: error line 16 at position 0
03:45:50 PM invalid identifier ‘EVENT_RAW.CASE_ID’
03:45:50 PM compiled SQL at /dbt/dags/workflows/execdbt//target/a13fa723-afb6-4e20-93da-68c55ab42655/run/uipathTemplateOne/models/5_business_logic/Cases_Event_input_table.sql
03:45:50 PM e[33mDatabase Error in model Cases_Event_input (models/1_input/Cases_Event_input.sql)e[0m
03:45:50 PM 000904 (42000): SQL compilation error: error line 18 at position 0
03:45:50 PM invalid identifier ‘EVENT_RAW.CASE_ID’
03:45:50 PM compiled SQL at /dbt/dags/workflows/execdbt//target/a13fa723-afb6-4e20-93da-68c55ab42655/run/uipathTemplateOne/models/1_input/Cases_Event_input.sql

[Martijn_Wijffelaars]

Hey Nisha, there are slight differences between Snowflake and SQL Server that could cause this. See https://docs.uipath.com/process-mining/automation-cloud/latest/user-guide/sql-differences-between-snowflake-and-sql-server.

To prevent problems, put your field names in the queries in quotes like so: table."field". Also, please double check whether you provided the input data in the exact same case as the table and field names in the query.

[nisha.sonawane]

Hello Martijn, thank you for your reply and sharing documents, goin through it, also sharing my query which I am trying reflect in PM app:

[nisha.sonawane]

@Martijn_Wijffelaars thank you for sharing the document, got the solution, it was " missing for field name at the end of query, Cases_raw.“Case_ID”, refer attached screen shot for more details: