Editing transformations

Dbt projects

Data transformations are used to transform input data into data suitable for Process Mining. The transformations in Process Mining are written as dbt projects.

This pages gives an introduction to dbt. For more detailed information, see the official dbt documentation.

pm-utils package

Process Mining app templates come with a dbt package called pm_utils. This pm-utils package contains utility functions and macros for Process Mining dbt projects. For more info about the pm_utils , see ProcessMining-pm-utils.

Updating the pm-utils version used for your app template

UiPath® constantly improves the pm-utils package by adding new functions.

When a new version of the pm-utils package is released, you are advised to update the version used in your transformations, to make sure that you make use of the latest functions and macros of the pm-utils package.

You find the version number of the latest version of the pm-utils package in the Releases panel of the ProcessMining-pm-utils.

Follow these steps to update the pm-utils version in your transformations.

1. Download the source code (zip) from the release of pm-utils.
2. Extract the zip file and rename to folder to pm_utils.

3. Export transformations from the inline Data transformations editor and extract the files.

4. Replace the pm_utils folder from the exported transformations with the new pm_utils folder.

5. Zip the contents of the transformations again and import them in the Data transformations editor.

Folder structure

The transformations of a process app consist of a dbt project. Below is a description of the contents of a dbt project folder.

Folder/file	Contains
dbt_packages\	the pm_utils package and its macros.
logs\	logs created when running dbt.
macros\	custom macros.
models\	.sql files that define the transformations.
models\schema\	.yml files that define tests on the data.
seed	.csv files with configuration settings.
dbt_project.yml	the settings of the dbtproject.

See the illustration below.

Data transformations

The data transformations are defined in .sql files in the models\ directory. The data transformations are organized in a standard set of sub directories:

• 1_input,
• 2_entities,
• 3_events,
• 4_event_logs,
• 5_business_logic.

See Structure of transformations.

The .sql files are written in Jinja SQL, which allows you to insert Jinja statements inside plain SQL queries. When dbt runs all .sql files, each .sql file results in a new view or table in the database.

Typically, the .sql files have the following structure:

1. With statements: One or more with statements to include the required sub tables.

   • {{ ref(‘My_table) }} refers to table defined by another .sql file.
   • {{ source(var("schema_sources"), 'My_table') }} refers to an input table.
2. Main query: The query that defines the new table.
3. Final query: Typically a query like Select * from table is used at the end. This makes it easy to make sub-selections while debugging.

   

For more tips on how to write transformations effectively, see Tips for writing SQL

Adding source tables

To add a new source table to the dbt project, it must be listed in models\schema\sources.yml. This way, other models can refer to it by using {{ source(var("schema_sources"), 'My_table_raw') }}. See the illustration below for an example.

Important: Each new source table must be listed in sources.yml.

Note:

The suffix _raw is added to source tables table names when loading data. For example, a table called my_table should be referred to as my_table_raw.

For more detailed information, see the official dbt documentation on Sources.

Data output

The data transformations must output the data model that is required by the corresponding app; each expected table and field must be present.

Practically, this means that the tables in the models\5_business_logic should not be deleted. Also, the output fields in the corresponding queries should not be removed.

If you want to add new fields to your process app, you can use the custom fields that are available for the process app. Map the fields in the transformations to the custom fields to have them available in the output. Make sure the custom fields are named in the output as described in the data model of the process app.

Tip:

You can use the dbt docs commands to generate a documentation site for your dbt project and open it in your default browser. The documentation site also contains a Lineage Graph that provides an entity relationship diagram with an graphical representation of the linkage between each data table in your project.

For detailed information, see the official dbt documentation on dbt docs.

Macros

Macros make it easy to reuse common SQL constructions. For detailed information, see the official dbt documentation on Jinja macros.

pm_utils

The pm-utils package contains a set of macros that are typically used in Process Mining transformations. For more info about the pm_utils macros, see ProcessMining-pm-utils.

Below is an example of Jinja code calling the pm_utils.optional() macro.

Seeds

Seeds are csv files that are used to add data tables to your transformations. For detailed information, see the official dbt documentation on jinja seeds.

In Process Mining, this is typically used to make it easy to configure mappings in your transformations.

After editing seed files, these files are not automatically updated in the database immediately. To instruct dbt to load the new seed file contents into the database, run either

• dbt seed - which will only update the seed file tables, or
• dbt build - which will also run all models and tests.
  Note: If the seed file had no data records initially, the data types in the database might not have been set correctly. To fix this, call run dbt seed --full-refresh. This will also update the set of columns in the database.

Activity configuration

The activity_configuration.csv file is used to set additional fields related to activities. activity_order is used as a tie breaker when two events are happening on the same timestamp. See the illustration below for an example.

Tests

The models\schema\ folder contains a set of .yml files that define tests. These validate the structure and contents of the expected data. For detailed information, see the official dbt documentation on tests.

When the transformations are run in Process Mining, only the tests in sources.yml are run on each data ingestion. This is done to check if the input data is properly formatted.

Note: When you edit transformations, make sure to update the tests accordingly. The tests can be removed if desired.