Building for a Distribution Business Series: #2 The Parsing Engine
Building for a Distribution Business Series: #2 The Parsing Engine
The article explains why business data should be validated before it enters a database, especially when the source files are ordinary spreadsheets and reports that were never designed for automated use. It provides a practical mental model for data reliability: treat every file as unverified, test it against explicit rules and independent evidence, record disagreements, and hold uncertain data rather than guessing. It supports the project by explaining the first operational layer that makes every later calculation, recommendation and AI-assisted decision trustworthy enough to act on.
The article explains why business data should be validated before it enters a database, especially when the source files are ordinary spreadsheets and reports that were never designed for automated use. It provides a practical mental model for data reliability: treat every file as unverified, test it against explicit rules and independent evidence, record disagreements, and hold uncertain data rather than guessing. It supports the project by explaining the first operational layer that makes every later calculation, recommendation and AI-assisted decision trustworthy enough to act on.
- Status
- Building
- Started
- June 2026
Audio Overview
ENThe Parsing Engine: Validating Data Before It Enters the Database
The second in a series about a system I have been building for a distribution business. This piece covers the component that reads the client's source files.
What it is, and why it exists
The business generates files: exports from its accounting system, stock reports compiled by staff, spreadsheets amended manually before circulation. These are the only source of data available. The parsing engine sits between those files and the database. It reads each file, validates it, and either loads it or holds it back.
It exists because an incorrect figure that reaches the database is indistinguishable from a correct one. Every calculation and report is derived from those figures, so one unverified file propagates through all of them and surfaces months later as a number nobody can account for. Validation therefore has to happen before loading, which is the only point at which a figure can still be traced to a file, a row and a date.
Why the source files were left unchanged
The alternative was to design a structured input format for the team to complete. We decided against it. A new format has to be adopted before the system has demonstrated any value, and one that proves inconvenient will be circumvented, leaving the business with two versions of the same records.
A direct entry screen is the better long-term arrangement, but it depends on the team choosing to work that way, and the build cannot rest on that.
The system therefore reads what already exists. Because the format is outside our control, every file is treated as unverified until it demonstrates otherwise.
What goes wrong in business data files
Six faults turn up repeatedly in the files businesses produce. None of them is visible to someone reading the file, and none is the kind of thing a person notices by looking at a report.
Internal arithmetic. The rows do not add up to the total the file itself states.
Period labelling. A heading names the wrong month or year.
Column alignment. A block of figures sits one position away from where the headings indicate, placing several periods under the wrong labels and losing one entirely.
Part-finished months. A report includes the current month's sales so far. It looks the same as a completed month, but the month has not ended and the figure will keep rising.
Disagreement between files. Two files describing the same period give different figures.
Product identity. One product recorded under several codes, or several products sharing one.
The rules the engine applies
Each rule below is the engine's answer to one or more of those faults.
Trust the figures, not the heading. Which month a column of figures belongs to is decided by comparing those figures against records already confirmed, rather than by reading the heading above them. Headings are frequently wrong; the figures underneath rarely are.
The file must agree with itself. Most reports print their own totals. The engine adds up the rows and compares the result to the printed total. If the two differ, the file is wrong somewhere and there is no way to tell where, so it is not loaded.
Confirmed by a second source. A figure seen for the first time is held as unconfirmed. It is treated as settled only once a second, independent file agrees with it.
Differences between files are recorded, not rejected. Two files can legitimately disagree about the same period. Where they do, the difference is recorded and a defined rule determines which source is treated as correct.
Hold, do not guess. A file that fails is set aside with a note giving the reason in terms the person who produced it can act on.
Codes exactly as written. Product codes are stored as they appear, with no reformatting. Where two codes differ only by a leading zero, the engine raises them for a decision rather than assuming they are the same product.
Judgements are settings, not code. Any figure the engine uses to make a decision, such as how far two files may differ before it raises a flag, is held in a separate settings file rather than inside the program. The client can see what the engine is judging by, and change it, without a developer.
The engine tests itself first. The engine holds a set of test files written for the purpose, each containing a fault it is supposed to catch and a known correct answer. Before it processes anything, it runs itself against those files and confirms it catches every one. If it misses any, it stops and loads nothing.
What to take from this
Why it is necessary. A database is only as reliable as what enters it, and there is no practical means of finding an incorrect figure once it is inside. The point of entry is the only opportunity to check. These faults are found because every file is tested on every run, not because anyone noticed something.
What it provides. The business continues to work as it does today. Errors are found systematically, and early enough to be traced to a file and referred back to whoever produced it. Every figure carries a record of its origin. And the output can be acted on rather than reviewed, which matters more than it appears: one wrong figure that somebody spots ends confidence in the entire report.
What it does not cover. Validation confirms arithmetic, not meaning. Anything without a stated total goes unchecked, so names, categories and codes pass through unverified while quantities are held to account. Where two sources disagree, the rule about which to prefer is an assumption and should remain visible rather than settled and forgotten. And a fault present in every file cannot be detected at all: if the source system has always recorded something incorrectly, consistency will confirm the error rather than expose it.
A figure that can be trusted is only useful if the business can ask something of it. What it can ask is decided by the structure holding it, and that is where the third piece in this series begins.
The Parsing Engine: Validating Data Before It Enters the Database
The second in a series about a system I have been building for a distribution business. This piece covers the component that reads the client's source files.
What it is, and why it exists
The business generates files: exports from its accounting system, stock reports compiled by staff, spreadsheets amended manually before circulation. These are the only source of data available. The parsing engine sits between those files and the database. It reads each file, validates it, and either loads it or holds it back.
It exists because an incorrect figure that reaches the database is indistinguishable from a correct one. Every calculation and report is derived from those figures, so one unverified file propagates through all of them and surfaces months later as a number nobody can account for. Validation therefore has to happen before loading, which is the only point at which a figure can still be traced to a file, a row and a date.
Why the source files were left unchanged
The alternative was to design a structured input format for the team to complete. We decided against it. A new format has to be adopted before the system has demonstrated any value, and one that proves inconvenient will be circumvented, leaving the business with two versions of the same records.
A direct entry screen is the better long-term arrangement, but it depends on the team choosing to work that way, and the build cannot rest on that.
The system therefore reads what already exists. Because the format is outside our control, every file is treated as unverified until it demonstrates otherwise.
What goes wrong in business data files
Six faults turn up repeatedly in the files businesses produce. None of them is visible to someone reading the file, and none is the kind of thing a person notices by looking at a report.
Internal arithmetic. The rows do not add up to the total the file itself states.
Period labelling. A heading names the wrong month or year.
Column alignment. A block of figures sits one position away from where the headings indicate, placing several periods under the wrong labels and losing one entirely.
Part-finished months. A report includes the current month's sales so far. It looks the same as a completed month, but the month has not ended and the figure will keep rising.
Disagreement between files. Two files describing the same period give different figures.
Product identity. One product recorded under several codes, or several products sharing one.
The rules the engine applies
Each rule below is the engine's answer to one or more of those faults.
Trust the figures, not the heading. Which month a column of figures belongs to is decided by comparing those figures against records already confirmed, rather than by reading the heading above them. Headings are frequently wrong; the figures underneath rarely are.
The file must agree with itself. Most reports print their own totals. The engine adds up the rows and compares the result to the printed total. If the two differ, the file is wrong somewhere and there is no way to tell where, so it is not loaded.
Confirmed by a second source. A figure seen for the first time is held as unconfirmed. It is treated as settled only once a second, independent file agrees with it.
Differences between files are recorded, not rejected. Two files can legitimately disagree about the same period. Where they do, the difference is recorded and a defined rule determines which source is treated as correct.
Hold, do not guess. A file that fails is set aside with a note giving the reason in terms the person who produced it can act on.
Codes exactly as written. Product codes are stored as they appear, with no reformatting. Where two codes differ only by a leading zero, the engine raises them for a decision rather than assuming they are the same product.
Judgements are settings, not code. Any figure the engine uses to make a decision, such as how far two files may differ before it raises a flag, is held in a separate settings file rather than inside the program. The client can see what the engine is judging by, and change it, without a developer.
The engine tests itself first. The engine holds a set of test files written for the purpose, each containing a fault it is supposed to catch and a known correct answer. Before it processes anything, it runs itself against those files and confirms it catches every one. If it misses any, it stops and loads nothing.
What to take from this
Why it is necessary. A database is only as reliable as what enters it, and there is no practical means of finding an incorrect figure once it is inside. The point of entry is the only opportunity to check. These faults are found because every file is tested on every run, not because anyone noticed something.
What it provides. The business continues to work as it does today. Errors are found systematically, and early enough to be traced to a file and referred back to whoever produced it. Every figure carries a record of its origin. And the output can be acted on rather than reviewed, which matters more than it appears: one wrong figure that somebody spots ends confidence in the entire report.
What it does not cover. Validation confirms arithmetic, not meaning. Anything without a stated total goes unchecked, so names, categories and codes pass through unverified while quantities are held to account. Where two sources disagree, the rule about which to prefer is an assumption and should remain visible rather than settled and forgotten. And a fault present in every file cannot be detected at all: if the source system has always recorded something incorrectly, consistency will confirm the error rather than expose it.
A figure that can be trusted is only useful if the business can ask something of it. What it can ask is decided by the structure holding it, and that is where the third piece in this series begins.