# BMI Validation Framework ## Abstract The BMI Validation Framework is a Python-based tool that validates data in a XLSX file stored on an S3 bucket. The framework is highly extensible, allowing for the addition of new validations with minimal code modifications. The framework creates output files with detailed error reporting, highlighting offending cells and rows, and adding comments to provide additional context about reported errors. The output file is saved to the same location on S3 in the same directory as the input file with the suffix "_validated" appended to the filename. Example output: [example_output.xlsx](data/example_output.xlsx) ## Overview The framework is designed to handle three types of validations: 1. **Cell-wise Validations**: Perform validations on individual cells in isolation. 2. **Intra-row Validations**: Conduct validations on interdependent cells within the same row. 3. **Trans-row Validations**: Execute validations on groups of rows that are correlated by a shared unique identifier found in one of the fields. (i.e. Releases) These validations are implemented through a series of mapping files that correlate validations with specific cells or sets of cells. The framework processes the validation maps and executes the validations sequentially, providing detailed error reporting in the output file. The mappings are stored in the `constants` directory, while the validation logic is stored in the `validations` directory. By separating the validation logic from the overall file processing using the mapping files, we can rapidly add new validations by simply defining new keys in the validation maps or by appending new dictionaries specifying the desired validations using existing validation methods. Custom additional validations can be quickly integrated into the existing framework without extensive code modifications. ## Validation Mapping Each type of validation utilizes a mapping file to correlate validations with specific cells or sets of cells: - **[cell_validation_map.py](constants/cell_validation_map.py)**: Provides a dictionary where each key corresponds to a field that should be validated. Each key points to a list of validation functions. - **[intra_row_validation_map.py](constants/intra_row_validation_map.py)**: Includes a list of dictionary objects that describe validators and the parameters that will be provided to the validator. - **[trans_row_validation_map.py](constants/trans_row_validation_map.py)**: Contains a list of dictionaries that specify descriptors, validators, and additional parameters where applicable. ## Validation Logic There are three main validation logic files: - **[cell_validations.py](validations/cell_validations.py)**: Defines validations for individual cells. - **[intra_row_validations.py](validations/intra_row_validations.py)**: Contains validations that apply to cells within the same row. - **[trans_row_validations.py](validations/trans_row_validations.py)**: Handles validations across groups of related rows. ## Execution For each of these types of validations, the processing engine iterates over the corresponding validation map. There is a slight difference in structure between the cell validations and the intra-row and trans-row validations. ### Cell Validations In the case of cell validations, the validation engine processes a dictionary of lists. In the dictionary, for each field specified (i.e. key in the dict), is a list of validation methods. The engine iterates over the validation list specified in the map for that field (i.e. at that key) and executes each validation sequentially. Success or failure is determined by the return value of the validation method. No return value indicates success, while a errors are surfaced as a tuple containing a code and a message. Auto-correction is also possible, where the validation method returns a value that should be used to replace the cell value. (This is indicated in the output file.) ```python # Success payload None # Failure payload ('Validate Blank', 'This cell cannot be blank') # Auto-correction payload - Third param is the corrected value ('Validate Integer', '1.0 is not an integer', 1) ``` ### Intra-row and Trans-row Validations For intra-row and trans-row validations, the validation engine processes list of dictionaries. Each dictionary contains a descriptor, a validator method, and additional parameters where applicable. The engine iterates over the list of dictionaries and executes the validations sequentially, passing the additional params defined in the mapping to the validator method. The return value of the validator method determines success or failure, with the same payload structure as the cell validations. However auto-correction is not offered in these types of validations. ## Extensibility The framework is highly extensible. Validations range from general use (e.g. validate_int, validate_blank, etc.) to the highly specific (e.g. validate_release_date, validate_release_type, etc.). As such: - New validations can be easily added by defining new keys in the validation maps or by appending new dictionaries specifying the desired validations using existing validation methods. - Custom validations (e.g., bespoke regex validations, complex intra-row or trans-row dependencies) can be quickly integrated into the existing framework without extensive code modifications, simply by defining the new validation method in isolation, and then adding the new validation method to the appropriate validation map. ## Error Reporting After validations are processed, errors are exposed in the output file by highlighting the offending cell or "circling" the offending row. Comments are also added to the cell and/or to the end of each row to provide additional context about reported errors. The output file is saved to the same location on S3 in the same directory as the input file with the suffix "_validated" appended to the filename. I can be saved locally as well via `DEBUG` environment variable. ## Provided Validations - As of 2024-04-24 ### Cell Validations Cell validations are checks that happen at the individual cell level. These validations are performed on a single cell in isolation. 1. `validate_length(val, length=None)`: Checks if the value is not the specified length. `length` is the required length of the value. 1. `validate_int(val)`: Checks if the value is an integer. 1. `validate_constant(val, constant_list)`: Checks if the value is in the constant list. `constant_list` is a list of valid values. 1. `validate_string(val)`: Checks if the value is a string. 1. `validate_isrc(val)`: Checks if the value is an ISRC. 1. `validate_date(val)`: Checks if the value is a date in the format YYYY-MM-DD. 1. `validate_blank(val)`: Checks if the value is blank. 1. `validate_url(val)`: Checks if the value is a URL. 1. `validate_country(val)`: Checks if the value is a valid country. 1. `validate_country_code(val, type=None)`: Checks if the value is a valid country code. `type` is an optional parameter that specifies the type of country code to validate against. 1. `validate_country_code_list(val, type=None)`: Checks if the list of values is comprised exclusively of valid country codes. `type` is an optional parameter that specifies the type of country code to validate against. 1. `validate_yes_or_no(val, full_word=False)`: Checks if the value is 'yes' or 'no'. `full_word` is an optional parameter that specifies whether the value must be the full word or just the first letter. 1. `validate_yes_no_clean(val)`: Checks if the value is 'Yes', 'No', or 'Clean'. 1. `validate_language(val)`: Checks if the value is a valid language. 1. `validate_language_code(val)`: Checks if the value is a valid language code. 1. `validate_genre(val)`: Checks if the value is a valid genre. 1. `validate_subgenre(val)`: Checks if the value is a valid subgenre. (This returns ANY subgenre across all genres) 1. `validate_upc(val)`: Checks if the value is a valid UPC. 1. `validate_p_line(val)`: Checks if the value is a valid P Line. 1. `validate_c_line(val)`: Checks if the value is a valid C Line. ### Intra-row Validations Intra-row validations are checks performed within a single row of data. These validations are performed across multiple fields within the same row. 1. `all_or_none_filled(row, header, description, fields)`: This function ensures that all fields are filled or none are filled. - Example: Ensure that all fields in the Performer 1 Type, Performer 1 Legal, and Performer 1 Main Role fields are filled or none are filled. 2. `conditional_blank(row, header, description, conditional_field, trigger_values, dependent_field)`: This function ensures that the dependent field is blank when the conditional field has specific trigger values. This is currently only used by the `conditional_blank_list` function. 3. `conditional_blank_list(row, header, description, conditional_field, trigger_values, dependent_fields)`: This function ensures that the dependent fields are blank when the conditional field has specific trigger values. - Example: Ensure that the Performer 1 Type, and Performer 1 Main Role fields are blank when the Performer 1 Name field is blank. 4. `conditional_in_list(row, header, description, conditional_field, trigger_values, dependent_field, dependent_values)`: This function ensures that a dependent field is within a certain list of values when a conditional field is within a certain list of values. - Example: Ensure that Performer 2 Type is either 'Featured Performer' or 'Non-Featured Performer' when genre is either 'Pop' or 'Soul. 5. `conditional_filled(row, header, description, conditional_field, trigger_values, dependent_fields)`: This function ensures that the dependent fields are filled when the conditional field has specific trigger values. - Example: Ensure that Conductor, Composer, Ensemble, and Orchestra are filled when genre is 'Classical' or 'Soundtrack'. 6. `at_least_one_exists(row, header, description, fields, required_values)`: This function ensures that at least one of the fields has one of the required values. - Example: Ensure that at least one of the fields in Performer 1 Type, Performer 2 Type, Performer 3 Type, Performer 4 Type, Performer 5 Type, has the value of 'Primary Performer'. 7. `valid_date_range(row, header, description, start_date_field, end_date_field)`: This function ensures that the start date is before or on the end date. - Example: Ensure that the Release Date is before or on the Sales Start Date. 8. `validate_subgenre_to_genre(row, header, description, genre_field, subgenre_field)`: This function ensures that the subgenre is valid for the genre. - Use Case: Ensure that the subgenre is valid for the genre. Each function takes in a row of data, the header row, a description of the validation, and other parameters specific to the validation. If the validation fails, the function returns a tuple containing the error code and error message. Otherwise, it returns None. ### Trans-row Validations Trans-row validations are checks performed across multiple rows of data. For our use case, that's Releases. The rows are correlated by a shared unique identifier found in one of the fields. In this POC, that field is [`fc.FOLDER_NAME_PROJECT_CODE`](constants/trans_row_validation_map.py#L7). 1. `uniform_field_values(rows, header, key_field, description, fields)`: This function ensures that all values in a list of fields are the same across all rows. For each field in the list, it ensures that all rows have the same value at that field. - Example: Ensure that all rows on the same release have the same value for the Release Type field. 2. `distinct_field_values(rows, header, key_field, description, fields)`: This function ensures that all values in a field are distinct across all rows. For each field in the list, it ensures that all rows have a unique value at that field. - Example: Ensure that all rows on the same release have a unique value for the Track Number field. 3. `consecutive_values(rows, header, key_field, description, field)`: This function ensures that the values in a field are consecutive across all rows. The values should start from 1 and proceed sequentially. - Example: Ensure that the Track Number field is consecutive across all rows on the same release. 4. `validate_track_and_volume_numbers(rows, header, key_field, description)`: This function ensures that track and volume numbers are sequential. Volume should start from 1, and proceed sequentially. Track numbers should also start from 1 and proceed sequentially. Each Volume should have a track number starting from 1. There should be no gaps in the track numbers on a volume, and no gaps in the volume numbers. - Use Case: Ensure that track and volume numbers are sequential. Each function takes in a list of rows, the header row, a key field, a description of the validation, and other parameters specific to the validation. If the validation fails, the function returns a tuple containing the error code and error message. Otherwise, it returns None. ## Extending the Framework An example of adding a new validation to the framework can be viewed at [this commit](https://github.com/lealvona/collab/commit/35e5fe1fb57ab368222a25d39663a414457e9555). This commit adds a new validation to the cell validation map that checks if a value is a valid date in the format YYYY-MM-DD. The validation is added to the `cell_validation_map.py` file, and the validation logic is added to the `cell_validations.py` file. Added to the `cell_validation_map.py` file: ```python # ... Prior validations above fc.RELEASE_DATE: [ vda.validate_date, # NEW validation vda.validate_blank # Existing validation ], fc.SALE_START_DATE: [ vda.validate_date, vda.validate_blank ], fc.ITUNES_PREORDER_DATE: [ vda.validate_date, ], # ... Continued validations below ``` Added to the `cell_validations.py` file: ```python def validate_date(val): """Return a validation message if the value is not a date in the format YYYY-MM-DD.""" if not val: return if isinstance(val, datetime): # convert to string fixed_val = val.strftime('%Y-%m-%d') return ( 'Invalid Date', 'This value is not a date in the format YYYY-MM-DD.', fixed_val ) if not re.match(r'^\d{4}-\d{2}-\d{2}$', str(val)): return ('Invalid Date', 'This value is not a date in the format ' '"YYYY-MM-DD".') return ``` That's it! The new validation is now part of the framework and will be used in the validation process. ## Gotchas TBD ## Installation 1. Install the required packages: ```bash pip install -r requirements.txt ``` 1. Copy and fill in the `.env` file: ```bash cp .env.shadow .env ``` ```env AWS_ACCESS_KEY_ID= AWS_SECRET_ACCESS_KEY= BUCKET= KEY= ``` 1. Run the script: ```bash python main.py ```