Discover segmentation based on the date perimeter configured in the workflow#

#Objective

Often, for a daily usage, you will need to ask for your Data Processing Engine (DPE) actions to manage data corresponding to the X last days until today.

Some other times, you will need your actions to act only on a historical set of dates. This segmentation type basically works like the “Segmentation over the values of an attribute of a table.”, except that the values comes from the dates you set in the workflow configuration, rather than coming from a Lakehouse Manager table.

#Concrete examples of usage

• Get data from a source only from X days ago to today. (daily relative dates usage)
• Get data from a source between 2 precise dates (get historical data, oneshoot usage)
• Recalculate an aggregate table only between 2 dates (daily relative dates usage or oneshoot usage )

If your source data contains a date attribute, it is possible to split the action in separate processes which will individually address a specific date or a group of dates. In this case, each task will apply a filter on the date that it needs to extract.

#Requirements

Before using this segmentation type, some points must be checked:

#1. Is the var_name attribute indexed in the source table?

If not, the extraction query will be much slower.

#2. Is there enough CPU on the source and destination databases?

• Keep in mind that select and insert operations are CPU intensive.
• For example, if your database has only 1 CPU, it's probably a bad idea to set 6 workers to select and insert simultaneously against the DBMS instance...

#3. Avoid generating too much tasks in the same stage.

To keep the Data Processing Engine (DPE) performs well, we advice to avoid setting stages of more than 500 tasks.

This is not a hard limit as it won't bug right after this threshold, but performance degradations can be observed. In order to reduce the number of tasks, you can set a higher bucket size, so that each task will manage more values. (meaning less tasks at the end).

#Compatibility

This segmentation option is only compatible with the following actions and sources:

#How to use this type of segmentation?

Here is a quick view between the visible mode and the advanced JSON mode.

Source's attribute / var_name: SQL attribute that will be used for the filtering of the source.

Bucket size / Chunksize: Number of values to be filtered for each task.

#Other tips

#You can use SQL formulas

For segmentation.var_name (in the attribute last part), you can use SQL formulas, as long as they are compatible with your DBMS. Please note that the DBMS can change over time and so you might have to recheck and correct those SQL formulas if you decide to use them.

For instance, you may have :

"segmentation": 
{
   "active": true,
   "type": "workflow_dates",
   "var_name": "DATE(datetime)",
   "chunksize": 1
 }

#How does it work behind the scenes?

This segmentation mode works the same as segmentation based on an attribute of a Project table. The only difference is that the values comes from the dates perimeter of the workflow, instead of a Lakehouse Manager table attribute, so there is no pre-stage needed.

#Go further

If you need training or technical assistance to implement our solutions, contact your sales representative or click on this link to get a quote and ask our Professional Services experts for a custom analysis of your project.

Ask questions, give your feedback and interact directly with the team building the Data Platform on the dedicated Discord channel.

If you need support with your OVHcloud services, create a request in our Help Centre.

Join our community of users.