The transformation tables are managed in the folder “Transformation” in the Dashboards section.
You can create a transformation table by clicking on the “+”

Parameters

Transformation title

Name of your transformation table. It should not have any specific character / blank space.

For example: name_of_the_table

Transformation query

The query needs to return the different columns of your table. The names defined in your queries will be the column names.

SELECT
1 as example_column

This query will create a transformation table with one column named example_column.

Transformation primary key

This is the primary key of your table: it needs to be unique. If you have duplicates of your primary key, it will trigger some conflicts.
One way to create a good primary key is to see how you aggregate your data and concatenate the values that you aggregate.
For example, if you aggregate your data per place and per user, you can create this unique id and use it as a primary key: CONCAT(place_id,user_id) as new_id

The field defined as the primary key in the primary key section can't be called primary_key. It can be any tag except primary_key (id, primary_id, key_id, ...)

Transformation delta time

It is the refresh frequency in minute.
If you set the delta time = 120, the transformation table will be refreshed every 2 hours.

Transformation aggregation period

If you aggregate your data per day and refresh your data every 2 hours, you don’t want to have only the data for the last 2 hours, but the data for all the day.
This parameter will enable you to configure the backtrack time. It is key to get the right value : if you aggregate per day, you need an aggregation period that is at least daily.

Transformation historical load days

This parameter will enable you to define how many days are necessary for the initialisation.

For the historical load, you need to make sure to select the entire aggregation period otherwise the historical load won't be fully made and you might have some missing data.
For example, for a weekly aggregation, the historical load needs to include the entire weeks starting from monday.
For a monthly aggregation, the historical load needs to include the first day of the month in order to be fully completed.

Transformation indexes

It is better to define the new indexes in the optimisations section (see article) and not to use this section anymore for new transformations.

You can put the column names on which you want to have indexes. It is very useful for big tables : you can select the fields that will be used in queries and calculations to optimise the running time.

Dependent on

A table can be dependent on another table if the query is calling this table. You can select in this section any other transformation table.
Table A being dependent on table B means that table A won't refresh until table B is up to date.
It has the following consequences:

  • If table B fails to refresh, table A won't be refreshed

  • Forcing the transformation of table B will force te transformation A over the same time period.

Destination columns

The type of the columns can be defined in this section.
If the type is not defined in this section, it will be considered as text. It is particularly useful to define the time and the numeric values.

You need to put the column name as written in the query and then select the type.

Once the table is created, you can't change the column types

Any edit of the transformation tables will delete and recreate the table from scratch except if all these conditions are met:

  • destination columns are unchanged

  • the query is unchanged

  • the destination table name is unchanged

  • the primary key is unchanged

Status

In the transformation section, you have a status column for each transformation table.

  • VALIDATION: the table is not created yet, some checks are made to make sure everything is okay (loading time, …)

  • INACTIVE : no refresh scheduled for now, it is considered as up to date

  • WAITING: the transformation has to be preformed, but either the number of current transformation running is too high already to start (max concurrent transformation = 5), or there are some transformations that need to be performed first (if A is dependent on B, A will have the WAITING status until B is finished)

  • RUNNING: it is currently being refreshed

  • FAILURE: there is an issue, you might check the job logs to find out what happened.

Job Logs

You can see in the default folder a dashboard called “Job Logs”. It will enable you to make sure your transformation table is created and refreshed.


The action_code you need to check is UPDATE_TRANSFORMATION_TABLE.
The status can be either SUCCESS either FAILURE.
In case of failure, you can find the FAILURE MESSAGE to get more details