Initial situation
Nomenclature, counterparties, balances, order history come in a file. Excel from the client, download from the old system, spreadsheet from the contractor. Then they need to be loaded into the accounting system, CRM or database.
Default process: the file is brought to the required columns and import is started. Next are two scenarios.
First - imports are falling. On the 200th line, the loader encounters a date in a different format and stops. Some lines have already been written, there is no rollback. Next comes analysis of what has gone so far, manual cleaning, restarting from the middle and trying to remember where exactly the middle was.
The second scenario is worse. The import is “successful”. A week later, it turns out that some of the prices have gone up by three orders of magnitude, because in the file the fractional separator is a comma, and the loader was waiting for a period. Or that the reference book of units of measurement did not match and the pieces became kilograms. Or that the positions were doubled because there were continuous spaces in the supplier's articles.
Time is not lost on loading. It gets lost in searching for consequences in a living system, where the loaded data is already mixed with the working ones and there is nothing to separate them with.
What did you watch?
Import errors are divided into three classes, and they are treated differently.
Formal. The type is not the same, the date format is not the same, the required field is empty, the number is not parsed. They are caught trivially if there is a check at all.
References. The value does not find a match in the directory: category, unit of measurement, counterparty, warehouse. The loader either crashes or silently creates a new directory entry - and the directory grows with copies of the same thing.
Meaning. Formally, everything is correct: date is date, number is number, the link is located. At the same time, the delivery date is earlier than the order date, the balance is negative, one item appears twice under different articles, the price differs from neighboring ones by two orders of magnitude. This class survives any type validation and appears in reporting.
What to measure before changing anything:
- Proportion of rows rejected by the loader. Visible immediately.
- The share of lines that were loaded and then edited by hand. Looks for the target system change log: records created by the import and changed in the first days after. This share is usually larger than the first and costs more.
- Number of import restarts per file.
- Time from the start of downloading to the moment when the data began to be trusted.
A mandatory step before writing rules is file profiling. The script goes through each column and displays: fullness, number of unique values, top values with frequencies, minimum and maximum length, recognized date and number formats, presence of leading and trailing spaces, case inconsistency. Completeness is measured by the file, and not by the words of the person who sent the file: “everything is filled there” and the real proportion of empty cells are found in one conversation.
What was changed
1. File contract. One page: column name, type, mandatory, valid values or reference, format, example of a correct value. The document is given to the person preparing the file before preparation, and not after the first fall.
2. Profiling as the first step of the conveyor. A column report is collected automatically for each new file and is viewed by eye. Half of the problems are visible right in the top of the values: three spellings of one warehouse, units of measurement in two languages, articles with invisible symbols.
3. Validation rules as code. Single file: rule name, expression, level (error or warning), message. The run gives a table of violations - line number, column, rule, actual value. The table goes to the data provider as is, without retelling in correspondence.
4. Staging. The file is loaded into an intermediate table or a separate schema, with text columns, without type casting. Reference checks are also performed there: comparison with combat system reference books for reading. Nothing is written to the target tables at this stage.
5. Dry run. Full mapping is performed without a record and prints the result: so many records will be created, so many will be updated, so many will be skipped, so many will fall under each rule. The numbers are verified by a person who knows the subject area. “4,000 out of 4,200 will be updated” on the initial boot is a signal that the matching key has been selected incorrectly, and this signal can only be caught by the eyes.
6. Idempotent key and batch_id. Each record has an external identifier from the source, each load has a batch identifier recorded in the target records or in the service correspondence table. Running it again updates, not duplicates. The entire batch can be found with one request.
7. Loading in batches with progress recording. Not one transaction per hundred thousand lines and not line by line, but in blocks with a record of which block they reached. Stopping in the middle leaves an understandable state.
8. Post-checks and checksums. After loading, the same semantic rules are run against the target data, plus a comparison of aggregates with the source: number of records, sums for several groups, minimums and maximums of dates. Coincidence of units is a cheap way to catch a quiet shift like a broken separator.
Where the model helps: parsing unstructured fields (address in one line, name with merged characteristics), comparison of free names with a directory, explanation of error reports in human language. The decision in this case remains up to the rules: the model offers a match with a confidence assessment, the rule only passes above the threshold, the rest goes to the person as a list. The conclusion about the correctness of the data is not transferred to the judgment of the model.
What was not touched
Combat reference books. At the verification stage, they are read-only. Auto-creation of missing positions is turned off: not being included in the directory is an entry for a person, not a reason to create entities.
Diagram of the target system. Adjust the structure for the next file - the path to the columns of the “Note3” type.
Source file. He can't straighten himself on the spot. All transformations live in the mapping and rules layer, the file is saved as it arrived - otherwise in a month you won’t be able to reproduce where the value came from.
Access rights. The download takes place under an account with rights to exactly the necessary tables, and not under an administrator “to ensure it goes through.”
Manual input. People continue to enter data as they started; the check is only built into the bulk load path.
Reversibility
- batch_id provides a selection of all batch records using one condition. Rollback comes down to deleting or marking as canceled one field at a time.
- Backup before loading —removed and tested for deployment. The copy, which has never been restored, remains a hypothesis.
- Parts allows you to stop after the first block and see the result in the live system interface before starting the rest.
- Staging is saved after loading: original text values next to the mapping result. They are used to resolve any claim to data without contacting the file provider.
- If the target system does not know how to delete records and does not have a cancellation mark, a run is done on a copy of the database, and a rollback means restoring the entire thing - and you need to know this before loading, and not after.
Boundaries
System without external identifiers and without soft deletion. Rollback turns into restoring the entire database, and loading during business hours is excluded.
Data whose correctness cannot be verified formally. The correctness of the price, the relevance of the address, and the correspondence of the name to the real product are checked by people, selectively, before loading.
Stream instead of one-time download. For continuous integration, checks are needed at the receiver and constantly, with quarantine for rejected records, and not just one run before the start.
Source that changes during upload. A file taken from a live database without fixing a slice does not correspond to any point in time, and the reconciliation of aggregates will always diverge.
Small volumes. Three hundred lines are checked with the eyes and loaded by hand faster than the assembly line.
Deadline set instead of verification. The requirement to “download today” does not cancel the check, but moves it to the stage of searching for consequences, where it is more expensive.
One line rule: before the first entry into the combat system - staging, dry run and batch_id, and only then loading.