Using tables vs. views
In the Celonis Platform, it is important to take care on when to use views and when to use tables, as this has an influence on the duration of the datamodel load and the transformation execution.
Criteria | Table | View | Explanation |
|---|---|---|---|
Datamodel load time | faster | slower | |
Transformation execution time: table/view creation | slower | faster | |
Transformation execution time: access or use of table/view | faster | slower | When analytical functions (like ROW_NUMBER, MAX etc.) are used
|
Memory Consumption Optimization
Please keep in mind, that complex database views are always calculated on runtime (e.g. when loading the data model). So when loading the data model all Joins and Logic is executed at this specific moment. If you encounter memory issues because of complex logic applied it is recommended to use tables and apply temporary tables to optimize your memory footprint.
You should individually test where the loads increase the execution and loading time, and choose carefully where views and where tables make more sense!
Example test result
A test on ~23 Mio. sample cases showed that it is better to use views instead of tables, as the time saving in transformations is bigger than in the data model load.
Overall improvement: 00:31:21
Measure | tables | views |
|---|---|---|
Transformation execution time | 01:17:12 | 00:42:31 |
Data model load time | 00:05:33 | 00:08:53 |
The standard-scripts use views for the creation of tables that are mainly used in the data model, while temporary tables, for example used for joins, are tables.