Skip to main content

Celonis Product Documentation

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

  • with a table: the calculations are executed once → when the table is created; afterwards the result can be accessed

  • with a view: the calculations are executed multiple times → when the view is accessed

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.