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

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.

Transformation indexes

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.

NB. All the fields will be considered as text. You can force the format into timestamp if you mention “timestamp” in the column name.
For example

SELECT
CAST(my_date as date) as this_is_my_date

In the transformation table, this_is_my_date will be under format “text”.

SELECT
CAST(my_date as date) as this_is_my_date_timestamp

In the transformation table, this_is_my_date_timestamp will be under format “timestamp”.

Any edit of the transformation tables will delete and recreate the table from scratch.

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