๐Ÿ—ƒ๏ธSet validation rules

Add some data validation steps to detect errors in your datasources sooner.

When a new data file is dropped through the studio or extracted during preprocess, it can be validated. You can check the data types of the columns, the number of rows of a data set, the presence of some required columns, that there is no duplicated rows, etc.

The list of validation rules for data files are defined inside the etl_config.cson file under the validation key.

Example

domain: "market-breakdown"
type: "excel"
file: "data-market-breakdown.xls"
sheetname: "Data"
validation:[
  type: "data_type"
  expected:
    "breakdown": "string"
    "pays": "string"
    "part": "number"
    "clients": "number"
,
  type: "pattern"
  expected: "[0-9]+"
  params:
    columns: ['metric']
]

Rows

Check the number of expected rows.

Keys:

  • type: โ€˜rowsโ€™

  • expected: (number) number of expected rows

Columns

Ensure that a given list of columns is a subset of the datasetโ€™s columns

Keys:

  • type: โ€˜columnsโ€™

  • expected: (list(str)) columns you expected to find

type: 'columns'
expected: ['metric', 'target']

Unique values

Ensure that the list of unique values of a given column corresponds exactly to a list of expected values.

Keys:

  • type: โ€˜unique_valuesโ€™

  • expected: (list) unique values

  • params:

  • column: (string) column name

type: 'unique_values'
expected: ["France", "Germany", "Spain"]
params:
    column: 'target'

No duplicates

Duplicated rows can be assessed based on all the columns or only a subset of columns.

Keys:

  • type: โ€˜no_duplicatesโ€™

  • params:

  • columns: (list or string) list of columns to use or โ€˜allโ€™

type: 'no_duplicates'
params:
    columns: ['metric']

Value

Check the value of a column (one value only). If the query returned more than one row, only the first one will be used.

Keys:

  • type: โ€˜valueโ€™

  • expected: (string or number) expected value

  • params:

  • column: (string) in which to check the value

type: 'value'
expected: 30
params:
    column: 'target'

Data type

Check column data types. Three possible types: number, string, date, or category.

Keys:

  • type: โ€˜data_typeโ€™

  • expected: : <โ€™stringโ€™, โ€˜numberโ€™, โ€˜dateโ€™, or โ€˜categoryโ€™>

type: 'data_type'
expected:
    'metric': 'number'
    'target': 'number'

Pattern

Check if string values correspond to a defined pattern

Keys:

  • type: โ€˜patternโ€™

  • expected: pattern/regex as a string

  • params: object with a columns key: the list of columns to check.

type: 'pattern'
expected: '[0-9]+'
params:
    columns: ['metric']
]

Not null

Check if some columns donโ€™t have null value.

Keys:

  • type: โ€˜not_nullโ€™

  • params: object with a columns key: list of columns.

type: 'not_null'
params:
    columns : ['zone']

Tutorial : Product Corporation

You can download the CSV file for our tutorial.

data-product-corporation.csv

  • Add validation for your datasource in etl_config.cson

    DATA_SOURCES: [
     {
       domain: 'data-product-corpo'
       file: 'data-product-corporation.csv'
       skip_rows: 0
       separator: ','
       encoding: 'utf-8'
       type: 'csv'
       validation:[
      type: 'rows'
      expected: 46
       ,
      type: 'columns'
      expected: ['brand', 'country', 'product_line']
       ]
     }
    ]
  • Drag and drop your new etl_config.cson in the CONFIG FILES page

  • Go to your โ€˜DATA SOURCESโ€™ page and drop your datasource.

  • Validation should be ok. If not, the file is not uploaded.

Last updated