Boost your SQL Transformations

Introduction Learning Objectives

The way in which your SQL code brings data into Celonis can have an enormous impact on your day-to-day business. Non-performant SQL code can cause an increased utilization of infrastructure, errors, and long data pipeline runtimes. This eventually leads to failures in delivering business insights on time and causes a lot of stress for any department intending to act on the data. Without a doubt, it's essential to prioritize your SQL's performance from day one. With this in mind, this course will show you how to:

Estimate your query performance Write readable and performant SQL transformations Identify common antipatterns and optimize existing SQL transformations within the Celonis platform Get familiar with additional best practice resources

At the end of this course, feel free to share your feedback via the Feedback page.

Best practices for Vertica Engine

The best practices in this course were derived based on the Celonis' Vertica engine. As a new or migrated customer, you may be on the newer ETL engine which has a different set of performance best practices currently only in our documentation.

This content is available in multiple languages: English, German, French, Japanese, Spanish, and Portuguese. To switch your language, use the selector in the top navigation bar.

In Courses: Switching languages will not affect your progress. If you are multilingual, comparing languages can even help deepen your understanding. In Exams: Do not switch the language once you start the exam. If you switch the language when you're already reviewing questions and answers, the page will refresh, causing your exam attempt to end prematurely. (Note: Once inside the exam course, you can switch the language anytime before clicking the Start Exam button.)

If you encounter any technical or access issues during this course, check out our FAQs in the Support area or reach out to us via our Academy contact form.

Introduction Introduction to Vertica

A visual representation of the Vertica database.

All the SQL code written in Celonis is processed by Vertica, an analytic database management software. Vertica fully supports the ANSI STANDARD SQL syntax, meaning you can use standard SQL to create the transformation scripts.

The Vertica Database has some differences to other Database Management Systems (DBMS) you worked with (MSSQL, Oracle DBMS), for example, in the way the data is stored and retrieved. You can learn more about these differences with the resources shared at the end of this course.

Introduction Tables in this Course

Note that the majority of the query examples in this course are based on SAP ECC tables. You'll find further information on particular tables and columns in the leanx website. Not to worry, best practices in this course apply for all process connectors using the Vertica data lake.

Now let's continue with our SQL Best Practices Training and start with a brief look at extractions.

Extraction Best Practices Extract only the Necessary Data

An optimized data pipeline extracts and transforms only necessary data. Skipping an optimization of your extractions can have very detrimental effects on your data pipeline. It leads to:

Higher storage needs (affects your license's APC) Heavier consequences of bad practices in transformations (e.g., SELECT *) Negative performance impact on load times in extractions, transformations, and data model loads

Here are some ways to trim your extractions down to only the necessary data:

Adjust Process Connectors

By default, Connectors extract many columns to cater to many process variations. Validate and adapt standard configurations in Connectors and make sure your extract only the necessary tables and columns for your process.

Trim Large Tables

Important tables often contain a large number of columns. For SAP, tables such LIPS, VBRP, VBAP, EKPO, BSEG, LIKP, EKKO, VBAK contain between 150-380 columns. In reality, you may only need a small portions of the columns.

Use Extraction Filters

Whenever possible, determine what data you can exclude based on time filters, join filters, or additional filters. This way, you can reduce the amount of data directly at extraction.

Those are the basics of optimizing your extractions. In the next lessons, we'll focus on transformations. Let's start with performance estimation.

Performance Estimation Query Execution Plan (EXPLAIN)

An example of a query with EXPLAIN.

For each query submitted to Vertica, the Vertica query optimizer assembles a query execution plan—a sequence of steps and required operations to access data and calculate the result.

To have a look at a query execution plan, you can simply insert EXPLAIN before any SELECT, UPDATE, or DELETE statement.

Using the query execution plan (EXPLAIN) is essential to optimize your queries. It helps you identify inefficient parts of the queries and validate the impact improvements you apply. While analyzing the entire query execution plan might appear overwhelming, in the majority of scenarios for Celonis transformations, it's enough for you to focus on:

the estimated query cost, the join type, and "NO STATISTICS"—the indicator of missing table statistics.

In OCPM data jobs, adding any text such as EXPLAIN into transformations is not allowed, as the whole data job is locked for editing. In case review is needed to optimize transformations or review for any other reason, the transformation query should be copied to a separate testing data job for any tests to be performed.

For now, let's explore the first two: the estimated query cost and join type. In the following lesson, we'll also look at table statistics.

For a detailed explanation of all elements of the query execution plan beyond the scope of this course, have a look at the Vertica documentation on reading query plans.

Performance Estimation Estimated Query cost What is it?

For each query step—also known as a "Path"—Vertica estimates performance costs.

This estimation is an approximation. Although the calculated costs usually reflect the query runtime, they do not provide an estimate of the actual runtime.

For example, if the optimizer determines that Plan A costs twice as much as Plan B, it's likely that Plan A will require more time to run. But this does not necessarily indicate that Plan A will run exactly twice as long as Plan B.

Checking the Actual Runtime in Logs

If you need more accuracy, you can also check the runtime directly in the Event Collection logs after running a query.

For more information on how Vertica calculates query costs, have a look at the Vertica documentation on query plan cost estimation.

Performance Estimation Join Types

Vertica uses one of two algorithms when joining two tables: merge join or hash join

Merge Join

If both tables are pre-sorted on the join column(s), the Vertica optimizer chooses a merge join, which is faster and uses considerably fewer resources than a hash join.

Hash Join

If tables are NOT sorted on the join column(s), the optimizer chooses a hash join, which consumes more memory than a merge join because Vertica has to build a sorted hash table in memory to facilitate the join. Using the hash join algorithm, Vertica picks one table (i.e. the inner table) to build an in-memory hash table on the join column(s). If that sounds a bit cryptic, here is a simplified graphic to give you an idea of what happens in the backend for hash joins:

So which join type is appropriate?

In general, aim for a merge join whenever possible, especially when joining very large tables.

In reality, transformation queries often contain multiple table joins. So, it's rarely feasible to ensure that all tables are joined as a merge type because it requires tables to be presorted on join columns.

A merge join will always be more efficient and use considerably less memory than a hash join. But it's not necessarily faster. If the data set is very small, a hash join may process faster but this is very rare.

How to sort to get a merge join?

To sort properly you should either:

place key columns at the beginning (e.g., MANDT, VBELN, POSNR) of the CREATE TABLE statement, Or add an explicit ORDER BY { key columns } clause at the end of the CREATE TABLE statement.

The sorting ensures a more efficient merge join in later transformations where we use this table.

What you need to remember

In short, ensure that joins between the tables with a large number of records (e.g. transactional tables) are of merge type. If you have several large tables, then consider joining the biggest two with merge type. For smaller tables such as master data tables, you can use a hash join type.

---

Table Statistics Table Statistics Overview What are they?

Table statistics are analytical summaries of tables that assist the query optimizer in making better decisions. Table statistics significantly improve query performance, often reducing the query execution time by over 50%.

When to add them?

For tables extracted from source systems using Celonis extractors—i.e., "Raw" tables such as VBAP, VBAK, EKKO, EKPO—table statistics are automatically gathered for each table after the extraction.

For additional tables created during the transformation phase—e.g., the temporary join table TMP_CDHDR_CDPOS, or data model tables you create—it's necessary to create statistics explicitly. In general, we recommend you add statistics to all tables created and used in your transformations.

Also, if you significantly change existing tables with INSERTs, DELETEs, or UPDATES, we advise you refresh statistics.

How to create or refresh statistics

You simply need to add this after each CREATE TABLE statement that creates and populates a table or after you significantly change the content of a table:

SELECT ANALYZE_STATISTICS ('TABLE_NAME');

For example:

The database will then gather table statistics when the transformation or query is executed.

Table Statistics The Impact of Statistics

Identical queries can have significantly different query costs and performance depending on whether statistics exist for the tables involved. Let's see how table statistics affect the query execution plans with an example.

Query Costs Without Table Statistics

In this example, we created the temporary join table O2C_VBFA_V without statistics as we can see with the "NO STATISTICS" indicator in query execution plan. Without statistics, the projected query cost is 5K.

Query Costs With Table Statistics

In this second example, we see the query execution plan and the estimated cost for the identical query, after gathering statistics for the table O2C_VBFA_V. The "NO STATISTICS" indicator no longer appears. The cost went from 5K to 137.

Why are table statistics so important?

Among many benefits, table statistics are especially crucial for query execution plans with hash joins. They enable the query optimizer to choose the smaller table to build the hash table (instead of the bigger one). In most scenarios, this prevents an “inner join did not fit into memory” error and improves performance.

A small exception for very small tables

For tables that have less than 10K records, we do not recommend creating statistics. This is because the effort to create the statistics for these small tables in Vertica outweighs the time saved by the statistics.

Table Statistics Check if Tables have Statistics

There are two main ways to check for tables with no statistics.

1. Using EXPLAIN

Run the EXPLAIN statement for the query and if there is a “NO STATISTICS” next to a table name, the given table has no statistics.

1. Check the "projections" table

To check all tables in one go, check the projections system table (more information on this Vertica table Vertica documentation on the projections table):

SELECT anchor_table_name AS TableName FROM projections WHERE has_statistics = FALSE ;

This query returns a list of all tables with missing statistics across your Data Pools.

Keep in mind that in most cases your Activity Tables will not need statistics if they are not used in joins in other transformations. That said, a best practice is simply to add statistics once to an activity table at the end of all transformations that affect its creation and edits.

Add Statistics at creation and when significant changes occur

As you already learned, for any table you find with no statistics, make sure to add:

SELECT ANALYZE_STATISTICS ('TABLE_NAME');

Add this to the transformation that creates the respective table. The database will then gather table statistics in the background.

In addition, re-run statistics in queries when one of the following conditions applies:

Table structure changes When new columns are added or existing columns are removed Changes to the data type of a column Data modifications When a large number of rows have been inserted, updated or deleted. A rough guideline is around 10-20% of the table data. Projection (index) changes When new projections are created, changed or deleted. More on projections later in this course.

Here is a cheat script to add statistics to all tables that don't have them in one go. This is a quick fix but do consider adding statistics throughout your transformations instead.

--Create missing statistics for all tables >10K records ------------------------------------------------------- DO LANGUAGE PLvSQL $$ DECLARE table_name VARCHAR(128); table_schema VARCHAR(128); BEGIN FOR table_name, table_schema IN QUERY SELECT DISTINCT p.anchor_table_name AS table_name, p.projection_schema AS table_schema FROM projections p JOIN projection_storage t ON p.projection_id = t.projection_id WHERE p.has_statistics = FALSE AND p.is_super_projection = TRUE GROUP BY p.anchor_table_name, p.projection_schema, p.has_statistics HAVING SUM(t.row_count) > 10000 LOOP EXECUTE 'SELECT ANALYZE_STATISTICS(''' || table_schema || '.' ||table_name || ''');'; END LOOP; END; $$;

In OCPM transformations, STATISTICS are automatically created for all tables. However, any custom tables created during pre-processing to be used in the OCPM transformations should still have statistics properly refreshed.

---

General Best Practices Write Readable and Maintainable SQL Code

To write professional SQL code you should follow formatting best practices. With proper formatting, your SQL code is simply easier to read, understand, and update.

If you work as a team in developing and maintaining SQL scripts, following formatting standards will go a long way in preventing errors and saving you time. Here is what we consider good SQL etiquette:

SELECT Statements SELECT columns, not "stars" —Avoid SELECT * Avoid SELECT DISTINCT (more on this later) If there are more than 3 columns after SELECT, separate them by placing each on a separate line. SELECT "V_ORDERS"."MANDT" AS "MANDT", "V_ORDERS".”VBELN" AS "VBELN", "V_ORDERS"."POSNR" AS "POSNR", ...

Add all the Primary Key columns in the SELECT statement to avoid duplicates. (more on this later)

JOIN Statements Use new lines for the operators INNER JOIN, LEFT JOIN, etc. If there is more than one condition, use a new indented line before the AND or OR conditional operator.

One Query per Transformation

Limit the number of queries in transformations. Ideally, one transformation (e.g. "Create Delivery Documents") should contain only one query or serve only one purpose. Any related auxiliary query or temporary table should be part of a preceding, separate transformation.

Having multiple queries within a single transformation makes it difficult to debug your code, measure the query performance, and identify the query potentially causing issues.

Table Naming Conventions

We suggest naming your tables and views as follows:

OBJECT NAMING CONVENTION EXAMPLE Activity Table _CEL_ProcessShortName_ACTIVITIES _CEL_O2C_ACTIVITIES Temporary/ Auxiliary Table TMP_ProcessShortName_Table1_Table2 TMP_O2C_CDHDR_CDPOS Data Model views/tables ProcessShortName_Table O2C_VBAP

In OCPM transformations, tables are automatically named.

Capital Letters / Uppercase

When writing your queries, use uppercase for:

SQL Keywords such as SELECT, FROM, WHERE, etc, SQL Functions such as CAST, SUBSTRING, AVG, etc SQL Operators such as LIKE, OR, BETWEEN, etc Avoid

select id, name from users where name like 'm%';

Try using

SELECT id, name FROM users WHERE name LIKE 'm%';

Indentation & New Lines

We recommend you:

Indent after keywords Use a new line for each separate column after a comma Avoid SELECT tableA.column1, tableA.column2 ,tableA.column3,tableA.column4, tableB.column1,tableB.column10 FROM tableA INNER JOIN tableB ON 1=1 and tableA.key_column=tableB.key_colum WHERE tableA.column30 < 10 Try Using SELECT tableA.column1 ,tableA.column2 ,tableA.column3 ,tableA.column4 ,tableB.column1 ,tableB.column10 FROM tableA INNER JOIN tableB ON 1=1 AND tableA.key_column=tableB.key_column WHERE tableA.column30 < 10 Table Aliases

Here is what we recommend when using table aliases:

When querying multiple tables, use aliases in your select statement. Include the AS keyword for creating aliases to make the code more readable. Include a table alias for each corresponding column in the select statement. That way, the reader doesn't need to parse which column belongs to which table.

Here you see table aliases defined under FROM and used in the SELECT statement:

Comments

If you create a complex or non-standard query, include useful comments to explain different parts of the code. Query comments help everyone (including yourself) who needs to maintain the query.

Try to keep the comments short and concise.

Avoid 1=1 in JOIN and WHERE conditions

Do not use "1=1" within your transformation code.

1=1 is an unnecessary evaluation that costs extra, albeit minimal time More relevant is that it prevents or slows down the use of projections (indexes). This has a noticeable negative impact on performance.

For example:

WHERE 1=1 AND … AND …

Note that many of our sample queries in this course do not follow this recommendation. We have room for improvement!

General Best Practices WHERE EXISTS instead of JOIN What is the difference? JOIN WHERE EXISTS Used to combine a table with additional fields from another table. Used to test for the existence of a related record in a table or subquery. Why use WHERE EXISTS instead of JOIN?

WHERE EXISTS is in most cases more performant than JOIN. With WHERE EXISTS, as soon as the SQL Engine finds a record where a condition is met, it stops processing additional records.

When to use WHERE EXISTS instead of JOIN

In some cases, you only want records that have a corresponding record in another table. So you need to filter on the related records. If you only want to check for the existence of the related records and do not need columns from the second table anywhere else—in SELECT or another JOIN for example—you should use the WHERE EXISTS.

For example, imagine two simple tables—books and authors. You only want to select authors that have written a book but you don’t need any information on the books themselves. In this case you don't need to join the two tables. You simply check for the existence of the records using WHERE EXISTS with a proper condition.

General Best Practices DISTINCT - Overview What is SELECT DISTINCT for?

SELECT DISTINCT returns only distinct (unique) values in the result set. If your query is complex, you might add DISTINCT as a habit to prevent duplicate records. Generally, you should avoid DISTINCT or only use it on rare occasions.

Why avoid SELECT DISTINCT?

DISTINCT forces the database to perform very expensive deduplication, which significantly extends the query runtime, memory consumption, and increases the risk of query failures. It might give you the results you need, but the cost is often too high and it's most likely masking issues with the data set or query.

Looking for alternatives

Instead of using DISTINCT by default:

Check the data set for duplicates Check the table joins to make sure they do not cause duplicates—e.g., incomplete joins or cartesian products.

Let's take a closer look at these two scenarios in detail.

General Best Practices Check for Duplicates Clarity on the Primary Key

To check for duplicates, it's essential to know the primary key of the table, view, or activity you want to generate. This might be at times difficult, especially for activities, where the key is often based on business logic.

How to check for duplicates

First, check if there are duplicates in your table, view, or activities by counting the distinct rows based on the primary key and comparing the total with the normal count of all rows:

If the results match, there are no duplicates and you do not need to use DISTINCT.

If the results do not match, you can:

revise the extraction logic to ensure a correct extraction based on the primary key, or remove the duplicates from your table.

Note: Make sure to do a duplicate check on a representative data set. If you work with a small data set, you should anticipate duplicates that could occur as your records grow.

Removing duplicates from your table

Investigate why there are duplicates in the raw data. If you can detect a pattern, you might be able to directly write a DELETE statement to clean up these cases.

Otherwise, you can create a table based on the raw table, including a row number to detect entries with the same primary key. In the example below, duplicates in table "BSIK" are marked with a row number greater than 1 based on the primary key of the table.

Copy this code CREATE TABLE BSIK_CLEAN AS( SELECT ROW_NUMBER() OVER( PARTITION BY BSIK.MANDT , BSIK.BUKRS , BSIK.BELNR , BSIK.GJAHR , BSIK.BUZEI ORDER BY BSIK.MANDT , BSIK.BUKRS , BSIK.BELNR , BSIK.GJAHR , BSIK.BUZEI ,_CELONIS_CHANGE_DATE DESC ) AS NUM , BSIK.* FROM BSIK AS BSIK );

DELETE FROM BSIK_CLEAN WHERE NUM → 1;

Take a minute to understand the above query. For more information on ROW_NUMBER(), have a look at Vertica documentation on ROW_NUMBER().

If you only need a subset of the table columns (BSIK in this case) in your deduplicated table (BSIK_CLEAN), select specific columns instead of BSIK.*.

When using the created table (BSIK_CLEAN in the example), make sure to add the WHERE statement to either:

only select items from BSIK_CLEAN with the row number equals 1 (NUM = 1) or delete all entries from the table where the row number is greater than 1 (NUM → 1). In the example above, we delete all entries with a NUM above 1 as they are duplicates.

General Best Practices Check Table Joins Queries with DISTINCT and Multiple Joins

In this example, we want to check whether we need DISTINCT in the query after joining two or more tables. You'll often see multiple joins in transformations that create activities, such as "Create Billing Document". This query has a SELECT DISTINCT along with two joins:

Checking if the DISTINCT is required

Let's slightly change the query to get the record count. We'll put the original query in a subquery and run SELECT COUNT(*) over it, as shown on the screenshot below:

The given query shows 125.001 records with the DISTINCT command used. Now, let's comment out the DISTINCT clause, run the same query again, and compare the results.

The query returns an identical number of the records, which means DISTINCT is not required in this case. You can remove it from the query without disturbing the data quality.

What if the DISTINCT appears to be required after joining two or more tables?

First of all, check why the DISTINCT seems to be unavoidable. You can then approach it accordingly:

1. The table join is for filtering purposes only

We covered this already—sometimes, you only want rows that have a corresponding record in a different table. If that is so, you only need to filter the query on these related records. If the join is causing duplicates but you only need it for filtering purposes, then you can replace it with WHERE EXISTS:

If the join is causing duplicates but you only need it for filtering purposes, then you can replace it with WHERE EXISTS:

1. Incomplete or Incorrect JOIN

An incomplete join means that we have an unwanted 1:n or even an m:n relationship between the tables we want to join.

Ideally, you should find out the correct primary key of the table, view, or activity you want to create. Second, you should ensure every table has a 1:1 or 1:n relationship to the primary key. If both the primary key and relationships are correct, you won't have duplicates.

If you cannot use the full primary key or the relationship causes duplicates, you can handle incomplete joins in two different ways:

WHERE EXISTS: You are already familiar with this one. SUBQUERY: To avoid duplicates in the resulting table, you can subselect only the columns you need for the join and the ones you want to add to the resulting table. If there are multiple records for each join key, you can use aggregate functions (e.g., MIN, MAX, AVG) on them and group the join columns. This is a bit complex, so here is a video explaining the approach:

And here is the sample before and after code of the video's example:

You can read further on this topic in our Celonis documentation on using DISTINCT in statements.

_Media:_

• https://fast.wistia.net/embed/iframe/9be3fk2ne3?videoFoam=true

General Best Practices Validating Table Joins

Checking your joins is an important part of avoiding DISTINCT. In parallel, you should also validate your joins to maximize their performance and avoid anything that could slow down your queries or cause inaccurate data. Here is what you should validate:

Join Keys Make sure that tables are properly joined by using the entire key. Otherwise, joins might cause duplication / or cartesian product. Do not exclude key columns even if they are identical for all the records (e.g. MANDT/ Client Id). If possible, avoid using convert functions in JOIN conditions (e.g. CAST(), RIGHT(), SUBSTRING(), etc. ).

For SAP tables, you can find key columns on leanx for example. Make sure to always check your source system's documentation for join key information.

Table Filters Apply a proper filter so that only relevant records are processed. Filter tables within JOIN instead of within WHERE. This applies for INNER JOINS.

Necessity of all Tables

Validate that all tables are joined purposefully.

Join only tables really required and used in the SELECT statement. If you remove or comment out any columns in your SELECT or WHERE clauses, make sure to remove the redundant joins. If tables are joined for filtering purposes only, use WHERE EXISTS instead of a JOIN.

General Best Practices Use UNION ALL instead of UNION

Despite best efforts to avoid DISTINCT, it may still sneak in in the background when you use UNION to bring tables together. By default, the operator UNION adds a DISTINCT to your query to eliminate duplicates in the tables you bring together.

To check if UNION is needed, simply count the records of your tables of your table after UNION ALL compared to a UNION operator.

-- UNION ALL query with count SELECT COUNT(*) AS TotalItemsIncludingDuplicates FROM ( SELECT ProductName FROM orderitems1 UNION ALL SELECT ProductName FROM orderitems2 );

-- UNION query with count SELECT COUNT(*) AS TotalUniqueItems FROM ( SELECT ProductName FROM orderitems1 UNION SELECT ProductName FROM orderitems2 );

General Best Practices Use BETWEEN instead of AND for Ranges

Whether your conditions are in JOINs or WHERE clauses, use the operator BETWEEN instead of AND if your are targeting values between two dates, or numerical values. BETWEEN is more performant.

Use this:

DATE BETWEEN 1970 AND 1980

Instead of this:

DATE≥1970 AND DATE≤1980

---

Temporary Join Tables Temporary Join Tables - Overview Creating Similar Joins Multiple Times

Sometimes you need to create and execute identical joins multiple times across your transformations. For example, process connectors often contain several transformations that create activities related to record changes performed on different documents (e.g., sales orders). Though these are separate transformations, they each require an identical join between the Change document header (CDHDR) and Change line item (CDPOS) tables.

Instead of joining these two tables repeatedly for each transformation, you can use temporary join tables to store and re-use the query results. This not only saves you expensive query processing time but also makes your code easier to read and maintain.

How do temporary join tables work?

You first create the temporary join table (e.g., TMP_O2C_CDHDR_CDPOS) in a separate transformation. It should contain the frequently joined tables and the corresponding columns needed for later transformations. With the temporary join table, you execute the join only once and temporarily store the query results. Next, you simply replace the repeated JOIN statement in your transformations with your temporary join table. At the very end of your transformations, you add a cleanup transformation to drop your temporary join table(s). This proactive dropping of temporary join tables is Celonis-specific. Example: Joining the table CDHDR on CDPOS in SAP process connectors

We often join the CDHDR and CDPOS tables in O2C (Order-to-cash), P2P (Purchase-to-pay), AR (Accounts receivable), and AP (Accounts payable) process connectors. You need this join to add activities related to different document changelogs. Let's create a temporary table that covers this join:

Now we can replace all joins of CDHDR and CDPOS in existing transformations with a join to the table TMP_CDHDR_CDPOS, as described above.

Temporary tables are only relevant for case-centric transformations or OCPM data preprocessing transformations.

Temporary Join Tables Well-Designed Temporary Tables

Creating temporary join tables (e.g. TMP_CDPOS_CDHDR) during the transformation phase helps reduce the need to repeatedly join the same tables in multiple transformations.

When creating a temporary table, bear in mind that the design of a temporary table significantly affects query performance. You create temporary tables to re-use them in several transformations. Consequently, well-designed temporary tables will significantly improve performance, while poorly designed temporary tables will often cause a bottleneck for the entire data pipeline.

Use the following questions to ensure you design your temporary tables properly:

Is the temporary table properly sorted?

If you recall, sorting properly facilitates a merge join. So you should:

Place key columns (e.g., MANDT, VBELN, POSNR) at the beginning of the CREATE TABLE statement, Or add an explicit ORDER BY { key columns } clause at the end of the CREATE TABLE statement.

The sorting will ensure a more efficient merge join in later transformations where we use this table.

Are all columns and joins required? Temporary join tables should only contain necessary joins and columns and nothing more. If you remove or comment out any columns in your SELECT or WHERE clauses, make sure to remove the redundant joins. Adding unused columns and joins might negatively affect both the storage and query performance. Is there any custom or calculated field?

If your table contains custom or calculated fields, or concatenated columns not essential for joins with other tables, do not place those columns at the beginning of the table. If you do, Vertica might have to perform additional operations (e.g., creating hash tables) while running the queries, which will significantly extend the execution time.

Are table statistics collected for this temporary table?

Each transformation that creates a temporary table should contain the ANALYZE_STATISTICS statement.

Simply add SELECT ANALYZE_STATISTICS('TableName'); after each statement that populates a table with data. The database will then collect statistics after the transformation or query is run.

Temporary Join Tables A Digression on Vertica Projections

Discussing projections may be going too deep into Vertica SQL for the scope of this course, but read on if you are interested. This section is optional.

Unlike traditional databases that store data in tables, Vertica physically stores table data in projections, which are collections of table columns. Projections store data in a format that optimizes query execution. Table statistics are based on a table's projections

Here are two important facts on projections:

Auto-projections (super projections) are created immediately on table creation. They include all table columns and are sorted by the first 8 fields, at the order they are exported. The consequence is that the selection and order of the first 8 columns of a table is highly performance relevant in Vertica.

Special or query-specific projections using primary keys. If the primary keys are known (set up in the table extraction setting or in the transformation table creation), Vertica creates additional projections sorted by and containing only the primary key fields. Such projections may be used by Vertica for JOIN or EXIST operations.

The performance of all queries that use temporary or auxiliary tables (i.e. all tables created during transformations) will heavily depend on the super-projection that is created by Vertica at the time of the table’s creation. This is especially relevant for the type of JOIN applied (recall MERGE vs HASH JOIN).

Here are some useful queries to work with projections:

Display of existing projections and their sorting:

SELECT projection_name, projection_column_name, sort_position from projection_columns where table_name = 'Table_Name'

An example how to create an additional projection:

CREATE PROJECTION IF NOT EXISTS Table_Name_Projection_Name AS SELECT * FROM Table_Name ORDER BY column1, column2, column3 SEGMENTED BY hash(column1, column2, column3) ALL NODES;

Manually carrying out a refresh after creating a projection and whenever a new record is inserted into a table. Vertica normally does this automatically.

SELECT REFRESH ('Table_Name');

Add STATISTICS after you create, change or delete a projection:

SELECT ANALYZE_STATISTICS('Table_Name');

Example how to delete a projection:

DROP PROJECTION IF EXISTS Table_Name_Projection_Name CASCADE;

Temporary Join Tables Common Table Expressions in OCPM Transformations

For Object Centric transformations, Common Table Expressions (CTEs) are used which act as a temporary table, but only to be used in the same transformation.

For example, as with temporary tables, you can use CTEs:

if there is any complex calculation within the query, Or, in case of repetitive joins.

CTEs accelerate processing time and perform faster than having a complex calculation in a main query. Here is a sample CTE capturing creation dates and numbering them for later use. The "WITH ... AS" statement is the syntax to create CTEs.

WITH "CTE_Changes" AS ( SELECT "CDPOS"."MANDANT", "CDPOS"."TABKEY", "CDPOS"."TABNAME", "CDHDR"."UDATE", "CDHDR"."UTIME", "CDHDR"."USERNAME", (ROW_NUMBER() OVER (PARTITION BY "CDPOS"."TABKEY" ORDER BY "CDHDR"."UDATE", "CDHDR"."UTIME" DESC)) AS "rn" FROM "CDPOS" AS "CDPOS" LEFT JOIN "CDHDR" AS "CDHDR" ON "CDPOS"."MANDANT" = "CDHDR"."MANDANT" AND "CDPOS"."CHANGENR" = "CDHDR"."CHANGENR" AND "CDPOS"."OBJECTCLAS" = "CDHDR"."OBJECTCLAS" AND "CDPOS"."OBJECTID" = "CDHDR"."OBJECTID" WHERE "CDPOS"."OBJECTCLAS" = 'EINKBELEG' AND "CDPOS"."TABNAME" = 'EKKO' AND "CDPOS"."FNAME" = 'KEY' AND "CDPOS"."CHNGIND" = 'I' )

Note that STATISTICS are not applied to CTEs.

---

Usage of Tables, Temporary Tables, Views, and CTEs Tables vs Temporary Tables vs Views A comparison

Deciding when to use a table, a temporary table, or a view in transformations may sometimes be a challenge. Here is a comparison of what tables and views do:

TYPE DEFINITION Table Stores results on disk of an executed query Temporary Table Stores results in memory within on transformation task. The table is automatically dropped at the end of the transformation. View Stores a query statement executed every time the view is accessed CTE Defines a temporary result set that exists only during the execution of a single query. When to use them

When building your transformation queries in Celonis, you'll often create temporary join tables and views. So you should know when to use CREATE TABLE, CREATE TEMPORARY TABLE, CREATE VIEW, and WITH ... AS  as this greatly influences the duration of the transformation executions and the data model load.

The right choice highly depends on a table's size and structure, and the query's complexity. Generally, you can follow these rules:

STATEMENT WHEN TO USE CREATE TABLE If the query contains complex definitions (e.g., multiple joins and conditions) If other transformations (e.g., Activity scripts) are accessing the query result

CREATE TEMPORARY TABLE If you use similar joins multiple times within one transformation task

CREATE VIEW If the query simply selects the records from a single table (e.g., VBAP) and applies simple conditions. In general, should be mostly limited to data model tables

WITH ... AS () Common Table Expression (CTE) [Used in OCPM transformations] If the query contains complex definitions and/or repetitive joins. Unlike a temporary table, CTE is not stored in memory or on disk. It's meant to improve readability and can be referenced multiple times within the same query.

If you are still not sure whether our general recommendation suits certain scenarios, you can try both approaches and compare the runtimes.

Usage of Tables, Temporary Tables, Views, and CTEs Load your Data Model with Staging Tables and Views Better performance with staging tables and views

For tables loaded into a Data Model (DM), you'll strike the best balance between required storage and performance for your data pipeline by combining staging tables with views, as shown in the diagram below.

This approach allows you to achieve better performance without significantly affecting storage. Let's break this down.

How to combine staging tables with views

The first step is to create a staging table. A staging table is a type of temporary table that contains only primary keys and calculated or derived columns. The query that creates the staging table should include all the joins and functions required to calculate or reformat the column values.

The second step is to create a Data Model view (e.g., O2C_VBAP). In this view, you join the “raw data” table (e.g., VBAP) with the previously created staging table (e.g., O2C_VBAP_STAGING).

Note that in this view, there is only one join between the raw table (VBAP) and the staging table (O2C_VBAP_STAGING). All other joins, required for calculation of the fields should be part of the previous, STAGING_TABLE statement.

Finally, in the Data Model configuration, you use the created O2C_VBAP view.

When to use this approach

Using a staging table and a view is mostly beneficial for DM tables that require some preparation before being loaded to DM. Most often, process connectors have transformations that prepare and extend the main case and other transactional data tables. If those queries contain joins and several reformatting functions, you should consider using a staging table and a view. That said, this approach brings no significant value for smaller master data tables.

A good rule of thumb is to use this approach for the main case and transactional tables.

Staging tables are used only in case-centric transformations.

Usage of Tables, Temporary Tables, Views, and CTEs A Note on Field Sizes

Field sizes have a significant impact on performance. In general, you should review your table schemas and reduce the size of the fields if it is not used. For example, a reduction from VARCHAR(200) to VARCHAR(20) might have a significant performance impact, depending on the number of records.

For any tables you create, use the column size gently. The most effective way is the "CREATE table as SELECT FROM" statement since it will base field length on the automatically adjusted field length at creation.

In case you have doubts for VARCHAR fields, you can check their actually used field size using this query:

SELECT COALESCE(MAX(OCTET_LENGTH("FIELD_NAME")), 0) FROM TABLE_NAME

If there is a difference, you can adjust the field size accordingly in the table creation script.

Usage of Tables, Temporary Tables, Views, and CTEs A Review on Table Terminology

By now, you've probably encountered quite a few table types and terms in and outside of this course. Here is a quick review of the most common table terms Celonis uses.

In this course: TABLE NAME EXPLANATION Temporary Join Table For Transformations: Saves query costs by capturing recurring joins in one table and re-using it across transformations. Deleted after it's no longer used—typically at the end of your transformations. The naming convention is TMP_{process_name}_{joined_table}_{joined_table}_... e.g., TMP_O2C_CDPOS_CDHDR. Temporary Table This is a table created using CREATE TEMPORARY TABLE and only works within one transformation task, after which it is automatically dropped. Staging Table For data model loads: A type of temporary table that contains only primary keys and calculated or derived columns. The table is added to a view which in turn is used in the data model load. Staging tables save query costs by preventing views from having to calculate or derive custom columns. As opposed to Temporary Join Tables, they are not deleted after creation as they are needed for the data model load. The naming convention is {process_name}_{table_name}_STAGING. Data Model Table A generic term pointing to tables used in the data model load. In most cases, you create these tables in transformations and derive them from raw data tables—removing unnecessary columns and adding others you may need. Projections System Table This table is specific to Vertica. By running a query on it, you can check which of your table have no statistics. This the query used: SELECT anchor_table_name AS TableName FROM projections WHERE has_statistics = FALSE ;

Raw Data Table The tables you directly extract from source systems. Master Data Table Points to a type of raw data table you extract that contains system master data. From other courses: TABLE NAME EXPLANATION Metadata Table Points to a type of raw data table you extract that contains extra information. Often used interchangeably with master data table. Transactional Table Points to a type of raw data table you extract that contains information on cases and transactions involved in your process. Trigger Table In Real-Time data extractions, points to an extracted table that triggers a transformation. Staging Table In Real-Time transformations, points to an intermediary table that stores delta information after an extraction. The naming convention for these tables is _CELONIS_TMP_{trigger_table}_EKPO_TRANSFORM_DATA.

---

DELETE and UPDATE Statements DELETE and UPDATE Can Slow Down Your Pipeline

In Vertica, DELETE and UPDATE queries may not be as performant as INSERT and SELECT queries. Especially on large tables with changes to many records (upwards of 25%):

They can take a long time to complete. They may negatively affect the performance of later queries on a table.

Let's look at the reasons for these two points.

Why can DELETE and UPDATE queries take long to complete?

Reasons for the queries to run slowly can be manifold. Some of the factors include but are not limited to:

The size of your table The size of your deletion chunk The deletion keys and their positioning in the table The deletion filters (WHERE) The data distribution (boolean vs many distinct values) Why can DELETE and UPDATE queries negatively affect later queries?

First you should understand what DELETEs and UPDATEs do in Vertica:

DELETEs do not actually delete data immediately from disk storage but instead simply marks rows as deleted. UPDATEs writes two rows: one with new data and one marked as deleted.

So when you run a SELECT statement on a table following large UPDATEs or DELETEs, Vertica needs to do extra processing to omit the "marked as deleted" records from results. This slows down performance.

Additionally, running DELETE or UPDATE statements puts exclusive locks on tables and prevents other queries from running at the same time.

What happens to rows marked for deletion?

These rows are meant to be eventually "purged" out of the Vertica database. The purging mechanism consumes a lot of resources and its frequency and conditions are handled by EMS maintenance jobs based on Vertica's recommendations. If you would like to read more on this topic directly from Vertica, have a look at Vertica documentation on purging deleted data and Vertica documentation on performance considerations for DELETE and UPDATE queries.

What is the moral of the story?

If you notice your DELETE and UPDATE statements slow down your data pipeline, then consider applying alternative approaches to work around them. More on the next page!

DELETE and UPDATE Statements Avoiding DELETE and UPDATE Considerations

Before setting up a regular DELETE or UPDATE transformation in your data pipeline, reconsider the need and reason for regular massive deletes or updates:

Using extractions - Can adjustments to the extraction filters reduce the need for deletes/updates? Find and fix the cause of duplicates - In case you are using DELETE for deduplication, find which operations creates duplicate records Is there any transformation leading to irrelevant or incomplete records that have to be deleted or updated afterwards?

For example, in a cleanup step, activity records where the columns "activity name" or "event times" are NULL are often being deleted. Reviewing the transformation creating those activities, you could detect and correct the reason why those records are created with null values.

Rebuild Tables instead of DELETE and UPDATE

If none of the above helps and you do need to optimize performance, then we recommend you rebuild your tables instead of using DELETE or UPDATE statements.

Below are the steps for either a DELETE or UPDATE scenario.

Rebuild instead of DELETE

You have Table_A that contains 100M records, and you want to DELETE 30M records. Instead of running a DELETE statement, you create NEW_Table_A that contains the 70M records you want to keep. Then rename Table_A to BACKUP_Table_A and rename NEW_Table_A to the original name Table_A. Here are the steps with sample queries.

Create new table selecting relevant records from the existing table:

CREATE TABLE NEW_Table_A AS (SELECT ... FROM Table_A WHERE ... );

Backup the old table by renaming it:

ALTER TABLE Table_A RENAME TO BACKUP_Table_A;

Rename the new table to the original table name:

ALTER TABLE New_Table_A RENAME TO Table_A;

DROP the old table:

DROP TABLE BACKUP_Table_A;

Add Table statistics

ANALYZE_STATISTICS (‘Table_A’); Rebuild instead of UPDATE

You have Table_A with 50 columns and 30M records. You would like to update the majority of or all 50M records and set the value of Table_A.Column50 to values from another table (e.g., Table_B). Joining tables and running an UPDATE statement often takes a very long time to execute. This would be a non-performant option:

UPDATE Table_A SET Table_A.Column50 = Table_B.ColumnX WHERE Table_A.key=Table_B.key

A quicker way to achieve the same results is to re-build the table, as shown in the DELETE example. For UPDATE, the steps are:

CREATE the new table NEW_Table_A as a select of relevant records, in this case, all the records, with 49 identical fields, and with one field that changes the value:

CREATE TABLE NEW_Table_A AS SELECT Table_A.Column1 ,Table_A.Column2 ,Table_A.Column3 ,Table_A.Column4 ,Table_A.Column5 ,Table_A.Column6 ,Table_A.Column7 ……. ,Table_B.ColumnX as Column50

FROM Table_A Inner join Table_B ON Table_A.key=Table_B.key;

Rename the old table:

ALTER TABLE Table_A RENAME TO BACKUP_Table_A;

Rename the new table to the original table name:

ALTER TABLE New_Table_A RENAME TO Table_A;

DROP the old table:

DROP TABLE BACKUP_Table_A;

Add table statistics:

ANALYZE_STATISTICS (‘Table_A’);

DELETE and UPDATE Statements For Inevitable DELETEs and UPDATEs

We believe you can avoid the majority of DELETE and UPDATE statements by simply finding and fixing the source of your duplicates and/or rebuilding tables.

If you absolutely need to use DELETE or UPDATE, then consider the following best practice:

Execute the query in smaller parts/chunks. This will lead to shorter execution times for the DELETE and UPDATE statements. For example, if you want to delete entries from a table, e.g., BSEG, instead of a large delete you could delete by year:

Delete from BSEG where GJAHR=2020; Delete from BSEG where GJAHR=2021; Delete from BSEG where GJAHR=2022; Select the right conditions - When limiting the query through a condition, select a field that appears at the beginning of the table field list (i.e., Vertica typically sorts tables using initial fields) and whose values are close to evenly distributed across the data set. Such fields may be date and document type.

That is it for this lesson. Now let's move on to other general best practices!

Design Best Practices Avoid Business Logic in Transformations

Business logic (KPI calculations, formulas, analyses, statistical evaluations, if/then/else) belong in the knowledge model and not in the transformation. There they are evaluated in real-time (in memory) and are much faster than in the transformation phase in Vertica.

Transformations should only be used to:

create event logs from the extracted data clean up data restructure data to be suitable as additions to the data model

Design Best Practices Enable Optimized Execution of Data Jobs

This best practice is twofold:

Ensuring the optimized execution feature is active Splitting transformations into separate transformations tasks when there is no dependency Activating the feature

First, ensure that at the data job level, the "optimized execution" is enabled.

With this feature active, tasks in your data job can run in parallel when they are not dependent on one another.

Splitting sequentially independent transformations

If a large transformation has many sequentially independent scripts, then consider splitting them into multiple tasks to allow for a parallel execution. Otherwise, all scripts with a transformation task are processed sequentially.

For example:

CREATE TABLE A INSERT INTO TABLE A FROM TABLE C CREATE TABLE B INSERT INTO TABLE B FROM TABLE D

This could well be split into two tasks to allow for parallel execution, because there is no dependency:

Transformation task 1: CREATE TABLE A INSERT INTO TABLE A FROM TABLE C

Transformation task 2: CREATE TABLE B INSERT INTO TABLE B FROM TABLE D

Course Recap and Next Steps Course Summary

You've almost reached the end! It's time to review a little bit.

In this course, you learned about best practices for writing performant and maintainable SQL code in Celonis transformations.

Take a minute to over the most important points you learned from each lesson. After that, it's time for a final knowledge check!

Query Performance Estimation

Put EXPLAIN before any SELECT or UPDATE statement to display the query execution plan.

In the query plan, concentrate on:

Steps with high estimated query cost The Join Type - Aim for merge join when joining large tables. Hash is ok for smaller tables or when merge is not possible. The Missing table statistics indicator (NO STATISTICS) Table Statistics

Table statistics are one of the most important factors for performant queries.

Table statistics are collected automatically only for the tables extracted using a Celonis Extractor (e.g. "raw source system tables"). You have to create statistics explicitly for every custom or temporary join table in your transformations (e.g. TMP_CDHDR_CDPOS).

Simply add SELECT ANALYZE_STATISTICS ('TABLE_NAME'); after each "Create table" query statement that populates a table (not for empty tables). The system will gather data once the query or transformation is run.

General Best Practices Best practices for writing professional SQL code require good formatting skills. This helps you write readable and maintainable SQL code. Avoid INNER JOIN for the purpose of solely filtering the data set. Use WHERE EXISTS instead. Avoid using DISTINCT by default as it's computationally expensive and slows down queries. Check your data set for duplicates and validate that joins are not causing duplicates. Act according to your findings with deletions, WHERE EXISTS, or subqueries.

Validate table joins:

Tables should be joined using the entire key (don't forget MANDT / ClientId). Filter tables to process only relevant records. Place filters in JOINs instead of WHERE. Join only tables that are really required and used in your SELECT statement. Avoid using UNION as it uses DISTINCT in the background. Use UNION ALL instead. Temporary Join Tables

Create a temporary join table to reduce the need to repeatedly join the same tables all over again for each transformation.

Bear in mind that the design of a temporary table significantly affects query performance.

Place the key columns (e.g. MANDT, VBELN, POSNR) at the beginning of the table OR use the explicit ORDER BY in a CREATE TABLE statement Store only data that you will really need in later transformations. Don't forget to add SELECT ANALYZE_STATISTICS('TableName'); after each statement that creates a temporary join table. Usage of Tables and Views

Wrongly using views instead of temporary tables might significantly decrease query performance.

TABLE STATEMENT EXPLANATION CREATE TABLE If the query contains complex definitions (e.g., multiple joins and conditions) If other transformations (e.g., Activity scripts) are accessing the query result

CREATE TEMPORARY TABLE If joins are re-used within one single transformation task

CREATE VIEW If the query simply selects the records from a single table (e.g., VBAP) and applies simple conditions In general, should be mostly limited to Data Model tables. DELETE and UPDATE Statements

If you notice slow performance, consider avoiding the use of DELETE and UPDATE statements to clean up your data. You can do so by:

Avoiding extra records by filtering in your extractions Finding and fixing the source of duplicates in your transformations Rebuilding your tables instead of using DELETE and UPDATE statements Design Best Practices Activate optimized executions on your data jobs and split sequentially independent transformations into separate transformation tasks. Avoid business logic in your transformations and the query engine can run it faster on the Studio side.

---