If you want to discover more about reducing errors in spreadsheets by cleaning data, you can take this “School of Data” course and you will learn about finding and removing pieces of data, formatting data for different tools, dealing with inconsistencies, and structuring data. Validation and testing Now, it’s time to ensure that the data is valid according to the rules defined for the new database. Your validation rules and constraints will determine what you should check, but here we present some of the most common actions: ● Data type validation: the data type determines the characteristics of the values that can be stored. For example: integer (positive and negative whole numbers), positive integer, alphanumeric (combination of text and numbers), real (positive or negative decimal numbers), etc. ● Value range validation: you can set a range of valid values. For example, in the age field, the database only allows numbers between 0 and 120. ● Mandatory field validation: if your new database includes mandatory fields, check for empty slots in those fields. ● Constraint validation: some fields must include certain characters or a combination of characters. The email field, for instance, must contain @. Ensuring that the data meets these criteria will prevent some records from not being loaded in the new system. You can perform simple validation tests with your spreadsheet software. Read these instructions for Excel and LibreOffice Calc. As we mentioned, data migration entails many potential risks, so testing with smaller sets of data is a good strategy before loading. These tests should be conducted with random samples to verify the data quality aspects as well as the correct matching between the source and the target database (the data mapping process, previously explained). 16