The Data You Should Refuse to Migrate

A migration that brings across every row inherits every unresolved question the old system contained. That is not a criticism of the old system. It was built for a different purpose, over a shorter time, by people doing their best with what they had. Its ambiguities were tolerable while everybody who remembered the circumstances was still there. The moment the data moves, that memory is no longer attached to the record, and the ambiguity becomes a permanent fact that nobody remembers is ambiguous.

The practical effect is that the new system inherits a problem nobody chose to solve, and it inherits it invisibly. A dormant customer does not look wrong. A test invoice does not look wrong. Three customers merged into one do not look wrong, they look like a customer with a busy history. The damage is done at import and discovered at the moment somebody relies on the record, which is always later and always more expensive than the moment the decision was made.

The example below is constructed, not taken from any business, and every count is there to be checked. A customer file of 1,240 rows is loaded. After the duplicates are detected and reviewed, those 1,240 rows resolve to 214 customers, which means 1,026 rows were absorbed into a customer that already existed: 1,240 minus 214 is 1,026. Nothing was lost, and yet the file is now a different shape than the one it came from, and the difference was not a decision anybody made consciously. It was made by a detection routine and a person clicking through candidates.

Of those 214 customers, 61 have had a transaction in the last twelve months and 153 have not: 61 plus 153 is 214. The 61 are the book. The 153 are a list of names, and the question of what to do with them is the first real decision in the project.

This one deserves more than a line, because it is the case where refusing to migrate anything at all does the most damage.

The constructed example is a single row reading one trading name, carrying three phone numbers and 1,84,000 of billing history across the three. It looks like a duplicate problem. It is not. It is three customers who share a name, or a customer and his shop, or a firm that was re-registered, and the difference matters enormously for what the new system will do with the record.

If the three are merged, the new system holds one customer with 1,84,000 of history and one credit behaviour, and every future decision about that customer — whether to chase, whether to extend terms, whether to keep the deposit — is made on the average of three unrelated businesses. If instead the record is left as three, each carries its own history and its own behaviour, and the business is no worse off than it was, which is the point.

The interesting detail is that merging looks like the tidy option. It reduces the row count, it makes the file smaller, and it produces a single record that reads cleanly. The cost of merging is paid later, in a credit decision, in a conversation with a customer who has been told they owe something another entity owes. Refusing to merge is the option that looks untidy and keeps the business able to think.

Refusing to migrate a customer is easy. Refusing to migrate an invoice attached to that customer is not, and this is where migrations acquire quiet holes.

Take the constructed case. After the import, 96 invoices were outstanding. In our worked example they total 1,58,040, and here is the arithmetic so it can be checked: 48,380 plus 22,600 is 70,980; plus 61,440 is 1,32,420; plus 9,720 is 1,42,140; plus 15,900 is 1,58,040. Now suppose three of those five invoices belong to customers that turned out to be duplicates of customers that already exist. Those three are 22,600, 9,720 and 15,900, which comes to 48,220: 22,600 plus 9,720 is 32,320, and 32,320 plus 15,900 is 48,220.

The consequence is that 48,220 of receivables has no home, because the customer record it belonged to is not going to exist as a distinct record. The remaining attached balance is 1,09,820, since 1,58,040 minus 48,220 is 1,09,820. The total outstanding has dropped by 48,220 without a single payment, and the report that shows outstanding receivables will not flag it, because the invoice was not deleted. It simply is not attached to anything the report counts.

This is the specific way a migration loses money quietly, and it is why the refusal decision cannot be made about customers in isolation. Every customer record that is merged, refused or split has to be checked for invoices and payments pointing at it, and the answer has to be written into the customer record rather than held in the migration notes.

Eleven rows in the constructed example contained nothing but a total. No line items, no date, no customer reference, just a number that somebody typed.

A spreadsheet can carry this comfortably, because the person reading it never needed the lines. They were looking at the number. A database cannot carry it at all, and that is not a limitation to be worked around, it is the correct behaviour. There is no record to create, because a total is an answer to a question and the question cannot be reconstructed.

The temptation at this point is to attach the total to the nearest customer and let it ride. Do not. An unattached total that a person can see in a report as an exception is honest. An attached total that quietly inflates one customer's balance is the beginning of a collections conversation the business is not equipped to have, because nobody can produce the invoice.

One of those rows is the one people skip. If a line came across and nobody can say what tax treatment belongs on it, the line should not come across, and the question should go to your own chartered accountant rather than being resolved by whoever is doing the import. This article is not tax advice and deliberately does not decide the question. The 18% GST figure used in the worked examples exists only to keep the totals checkable, and the 18% figure is not a general answer: a base of 41,000 with 18% added gives 7,380 of tax and a total of 48,380, because 41,000 multiplied by 0.18 is 7,380. A line where the same base was treated at 12% would come to 4,920 of tax and 45,920 in total, since 41,000 multiplied by 0.12 is 4,920. Both are arithmetically checkable and neither tells you which is right for the item, which is exactly why the rate has to be data rather than a division performed at import time.

Deciding to leave data behind is only possible if you know what the destination is capable of, because half the reason a spreadsheet is unwieldy is that it can hold things no system should.

An invoice and a payment are different records here, and paid is a projection of allocations rather than a stored flag, so a hand-maintained paid column has no honest translation. There is no change-request record, no milestone record, no deliverable record, no contract editor and no e-signature, so a scope that moved during the old system's life is a new quote against the same project rather than an amendment to an old record. There is no timesheet record and no payroll module, so staff-hour spreadsheets have no destination. Expenses are a Nox-Billings capability and purchasing lives in the Commerce area. NoxOrigin does not file GST returns or any other statutory return. Shift close and day-end reconciliation are assisted-setup maturity rather than self-serve switches.

And migration itself is scoped work, not a self-serve import promise. The hard part was never the loading; it was the decisions above, and a system that offers to load a file without asking any of these questions is going to answer all of them for you, in the same way, silently.