- Release notes
- Before you begin
- Getting started
- Integrations
- Working with process apps
- Working with dashboards and charts
- Working with process graphs
- Working with Discover process models and Import BPMN models
- Showing or hiding the menu
- Context information
- Export
- Filters
- Sending automation ideas to UiPath® Automation Hub
- Tags
- Due dates
- Compare
- Conformance checking
- Root cause analysis
- Simulating automation potential
- Starting a Task Mining project from Process Mining
- Triggering an automation from a process app
- Viewing Process data
- Creating apps
- Loading data
- Customizing process apps
- Publishing process apps
- App templates
- Additional resources
Tips for writing SQL
- In unions, the names, and order of fields must
exactly match. It may be necessary to create empty fields on parts of the union to
get all the fields aligned, with the
select
statementnull as `Field_X`
. Depending on the SQL dialect you may need to usenull as "Field_X"
. - When writing dbt for SQL Server, all Jinja statements are compiled into SQL code, regardless if they are inside SQL comments or not. For example,
in the following
-- {{ ref('Table1') }}
, the Jinja will be compiled into SQL code. - Do not round numbers, this can lead to inconsistencies. The platform will do this automatically whenever a display value requires this.
In SQL, not all transformations can be computed in the same table. The reason for this may be aggregates or properties that cannot be expressed in a single select statement. You can create supporting tables for this purpose. Creating a supporting table in the database allows you to use the model in multiple transformations. If it is not needed to reuse the model, the supporting transformation can also be added as a preprocessing query in the existing table.
To make a distinction between supporting transformations and the other transformations, you can group them in a separate sub directory.
To make your query execution faster:
-
Avoid
select distinct
where it is also possible to build an aggregate and just take one record using awhere
clause. - Use
union all
instead ofunion
. Usingunion all
, records from tables are concatenated, whileunion
removes duplicates. - If you are working on a large dataset, you can limit the data you are working with during development.
-
All models (except for the models in the
1_input
directory) are materialized as a table by dbt. This is the default setting for all Process Mining Connectors. For more information, see the dbt documentation on Materializations. - When generating an id field, use the
id()
macro available in pm-utils. Examples can be found in the devkit-connector.
The following style guide was used to develop the Process Mining app templates. It is recommended to follow these guidelines for consistency and readability.
- Write SQL commands and functions in lower case.
-
Use the same level of indentation for
select
,from
,where
,join
, etc., to understand the structure of the model more easily.- Use indentation for the usage of functions if this improves readability. For example, use indentation for each statement in
a
case
function.
- Use indentation for the usage of functions if this improves readability. For example, use indentation for each statement in
a
-
Use consistent naming conventions for tables and fields to prevent SQL errors indicating that tables or fields do not exist in the database. Adhere to the following guidelines:
- Tables and fields start with a capital.
- Use an underscore
_
between separate words in tables and fields. - All fields have quotes.
- Tables do not have quotes. This improves readability in combination with fields having quotes.
- All fields are prefixed with the table they originate from, to make the model easier to understand and extend.
- Commas used for the separation of fields are placed at the end of the line.
- Use inline comments when certain constructions need to be explained. When this is done, make sure the comment adds value.
- For readability and maintainability, use explicit select statements in the transformation part of the query and do not use
select *
. Especially for unions, using theselect *
can yield errors.