Hi, I use this coding:

DROP TABLE IF EXISTS NEW_TABLENAME

CREATE TABLE NEW_TABLENAME AS(

SELECT

* FROM "EXISTING_TABLE_1"

UNION ALL

SELECT

* FROM " EXISTING_TABLE_2"

UNION ALL

SELECT

* FROM "EXISTING_TABLE_3"

);

1) Easier solution - SQL JOIN

Just one comment, when using UNION it's much better to select exact column - if for any reason you'll add any new column to "EXISTING_TABLE_1/2/3" then everything will stop working.

It's also better to use UNION instead of UNION ALL which will remove duplicates and potentially avoid problem with N;N error occurring during Data Model load to engine.

Opinion: that's the easiest way however it has it's flaws - you always need to know how many files should be joined. In some cases the number of files can be changing, and then that solution won't be applicable in long term.

2) Harder solution - ML wokrbench Python code

There you can join into single Dataframe ALL files from specific folder - the number of files can change in time, but you'll receive single output table pushed to Backend each time. I'm using that approach with custom python extractors that download csv files, and I'm also removing the old ones.

I have tried the solution but when I try to Load data I am getting error "Preview not available" . I have tried creating new table with both union as well as Union all

It seems like Celonis error. Check on https://status.celonis.com/ if your cluster is not affected by problems with data pools, and data loading. I can also sugest contacting service desk, as that's not regular SQL error that you could see.