Clean data automatically with blueprint rules

Default blueprint rules apply simple cleaning logic, such as date formatting, to help you prepare data

Objective

The Platform provides default "blueprint rules" which consist of simple cleaning logic (like dates formatting, thousands of separators, etc.) to help users run simple data preparation. You can choose from available transformations in the Platform store and apply them if the applied condition is met.

It is not mandatory to use blueprint rules when using the platform. If used, the rules will be added automatically to the Data Processing Engine Load actions you will define to ingest the data inside your dataset.

How a rule works

A rule applies to each line independently and allows several things.

If a condition is specified, the rule will apply only under certain conditions expressed in condition and condition_value(s).

If a transformation is filled, the transformation action on each line will be performed, otherwise, it is considered to be a filtering rule (keep).

If no condition is filled, the one set by default is ALL, which allows any line.

If no transformation nor condition is filled, then we consider it to be a default transformation. Otherwise, if no transformation is filled and there is one condition, the action by default is keep. The action applies only to lines that respond to the rule defined by condition.

Fields

Attribute name	Required	Example	Comment
attribute_name	Yes if the format is a list, no if the format is an object	`ca_ttc`, `date`
condition	No (defaults to `ALL`)	`EQUAL`, `IN`	See the condition list below
condition_value	Yes, except if condition is `IN`, `NOT IN` or `ALL`	`1`, `"Categorie1"`
condition_values	Yes if condition is `IN` or `NOT IN`	`["ca_ttc", 2, 3]`, `["categorie"]`
action	No	`keep`, `concat`	See the transformation list below
action_value	Yes if action is `default` or `split`	`1`, `"Categorie1"`
action_values	Yes, except if action is `default`, `split`, `action_error`, `keep` or `skip`	`["ca_ttc", 2, 3]`, `["categorie"]`
else_value	If the condition is not met, replaces the value by `else_value`	`"ca2"`

Configure a blueprint rule

Rules require 3 steps to be configured:

Information: Name of the attribute
Transformation: If the condition is confirmed, which transformation action to apply to the data (can be chosen from the Platform store)
Condition: which rule to apply for the condition

To add a rule on an attribute, on the relevant field, click on the following button:

Transformations

Different transformations are available in the Platform store:

Replace by: Replaced values by a single value
Substring: Values will be truncated
Date format: Replace date formats
Keep line: Keep matching values
Skip line: Skip matching values
Throw error: Matching values will trigger an error
Replace substring: Replace matching substrings
Apply regexp: A new regexp will be applied to matching values
Stop workflow: Stop the ongoing workflow

Conditions

Different conditions are available:

The Platform provides you a variety of Conditions you can apply to your chosen transformation. The list includes the following: Equal, Not Equal, Between, Not Between, Lower than, Lower or Equal than, Greater than, Greater or Equal than, REGEXP, NOT REGEXP, Is a number, Is not a number, Is a date, Is not a date, Is null, Is not null

Info

Once the transformation and condition are set, the rule will be applied to the selected attribute. 🙌🏻

Reference

The names below are the values written in the rule itself, so they are the ones you use when you edit rules as JSON, for example in the Load advanced mode or the Aggregate advanced mode.

Condition list

Condition's name	Description
ALL	Keeps all the lines
EQUAL	Valid lines: those whose attribute_name is equal to condition_value
NOT EQUAL	Valid lines: those whose attribute_name is not equal to condition_value
IN	Valid lines: those whose attribute_name is in the list condition_values
NOT IN	Valid lines: those whose attribute_name is not in the list condition_values
LTE	Valid lines: those whose attribute_name is lower or equal to condition_value
LT	Valid lines: those whose attribute_name is lower than condition_value
GTE	Valid lines: those whose attribute_name is higher or equal to condition_value
GT	Valid lines: those whose attribute_name is higher than condition_value
REGEXP	Valid lines: those whose attribute_name corresponds to the regexp condition_value. Example: `^([0-9]{4})`
NOT REGEXP	Valid lines: those whose attribute_name does not correspond to the regexp condition_value
BETWEEN	Valid lines: those whose attribute_name is between condition_values[0] and condition_values[1]
NOT BETWEEN	Valid lines: those whose attribute_name is not between condition_values[0] and condition_values[1]
OR	Valid lines: those where one of the values contained in condition_values is not empty (empty, null, False, 0)
AND	Valid lines: those where all values contained in condition_values are not empty (empty, null, False, 0)
IS NULL	Valid lines: those whose attribute_name is null
IS NOT NULL	Valid lines: those whose attribute_name is not null
IS INT	Valid lines: those whose attribute_name is an integer
IS NOT INT	Valid lines: those whose attribute_name is not an integer
IS DATE	Valid lines: those whose attribute_name is a date
IS NOT DATE	Valid lines: those whose attribute_name is not a date
IS DEC	Valid lines: those whose attribute_name is a decimal
IS NOT DEC	Valid lines: those whose attribute_name is not a decimal
IS NUM	Valid lines: those whose attribute_name is a number
IS NOT NUM	Valid lines: those whose attribute_name is not a number

Transformation list

keep: Keep the line
skip: Skip the line
default: Replace the value by action_value. For dates, there is:
- $today returns yyyy-mm-dd
- $now returns yyyy-mm-dd hh:mm:ss
- $yesterday returns yyyy-mm-dd
regexp_replace: Replace the value corresponding to regexp action_values[0] by action_values[1]
replace: Replace the value corresponding to action_values[0] by action_values[1]
substring / substr:
- If action_values[0] >= 0: Starts at the *action_values[0]*th character
- If action_values[0] < 0: Only takes into account the action_values[0] last characters
- If action_values[1] is filled: The 2nd figure represents the max number of characters wanted
date_replace: Replace the date format action_values[0] by action_values[1]. Examples for action_values:
- ["%d/%m/%Y", "%Y-%m-%d"]
- See the date format directives below
add: Add several fields or hard numbers, automatically detecting if for each value of action_values it is an attribute available in the line, otherwise it is considered to be a number. Examples for action_values:
- ["ca_ht", "taxes"]
- ["nb_clics", 1]
sub: Substract action_values[0] from all the other data present in action_values
mul: Multiply action_values[0] by action_values[1]
div: Divide action_values[0] by action_values[1]
mod: Modulo action_values[0] by action_values[1]
concat: Concatenate one or several fields with hard strings. Each element of action_values detects if it is the name of a column available in the line, if not we consider it to be a string of hard characters. Examples for action_values:
- ["site_name", " - ", "country"]
- ["civilite", " name", "lastname"]
ceil: Rounded up action_value
floor: Rounded down action_value
disaggregate / split: Used to create multiple lines if a field contains multiple values that should be split into separate lines. There can only be one disaggregate rule per DPE action, and this rule always applies last. The field is split on the character action_value, and for each split element only non-empty values are kept.
action_error: Leads to an error in the action, and consequently considers that all the lines are false
stop_workflow: Stop the workflow

Date format directives

Directive	Meaning	Example
%w	Weekday as a decimal number, where 0 is Sunday and 6 is Saturday.	0, 1, ..., 6
%d	Day of the month as a zero-padded decimal number.	01, 02, ..., 31
%m	Month as a zero-padded decimal number.	01, 02, ..., 12
%y	Year without century as a zero-padded decimal number.	00, 01, ..., 99
%Y	Year with century as a decimal number.	1970, 1988, 2001, 2013
%H	Hour (24-hour clock) as a zero-padded decimal number.	00, 01, ..., 23
%I	Hour (12-hour clock) as a zero-padded decimal number.	01, 02, ..., 12
%p	Locale's equivalent of either AM or PM.	AM, PM (en_US); am, pm (de_DE)
%M	Minute as a zero-padded decimal number.	00, 01, ..., 59
%S	Second as a zero-padded decimal number.	00, 01, ..., 59
%f	Microsecond as a decimal number, zero-padded on the left.	000000, 000001, ..., 999999
%z	UTC offset in the form +HHMM or -HHMM (empty string if the object is naive).	(empty), +0000, -0400, +1030
%Z	Time zone name (empty string if the object is naive).	(empty), UTC, EST, CST
%j	Day of the year as a zero-padded decimal number.	001, 002, ..., 366
%U	Week number of the year (Sunday as the first day of the week) as a zero-padded decimal number. All days in a new year preceding the first Sunday are considered to be in week 0.	00, 01, ..., 53
%W	Week number of the year (Monday as the first day of the week) as a decimal number. All days in a new year preceding the first Monday are considered to be in week 0.	00, 01, ..., 53
%%	A literal '%' character.	%

Rule examples

Replace a date format:

[
    {
        "attribute_name": "date",
        "action": "date_replace",
        "action_values": ["%d/%m/%Y", "%Y-%m-%d"]
    }
]

Add 1 to the year attribute, only on the lines where it is 2015 or 2016:

[
    {
        "attribute_name": "year",
        "condition": "IN",
        "condition_values": ["2015", "2016"],
        "action": "add",
        "action_values": ["year", 1]
    }
]

Set year to 2015 on the lines where it is lower than 2015:

[
    {
        "attribute_name": "year",
        "condition": "LT",
        "condition_value": "2015",
        "action": "default",
        "action_value": "2015"
    }
]

Set the date attribute to today's date:

[
    {
        "action": "default",
        "action_value": "$today",
        "attribute_name": "date"
    }
]

Convert a temperature from Kelvin to Celsius:

[
    {
        "action": "sub",
        "action_values": [
            "temp_min",
            273.15
        ],
        "attribute_name": "temp_min"
    }
]

Normalise a timestamp:

[
    {
        "action": "date_replace",
        "action_values": [
            "%a %b %d %H:%M:%S +0000 %Y",
            "%Y-%m-%d %H:%M:%S"
        ],
        "attribute_name": "statuses_created_at"
    }
]

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.

Clean data automatically with blueprint rules#

#Objective

#How a rule works

#Fields

#Configure a blueprint rule

#Transformations

#Conditions

#Reference

#Go further