Move the decisions, not the tables

Situation

I have run the team behind a warehouse grown over years into the hundreds of millions of rows, and, elsewhere, modernized one that had fallen behind. A warehouse grows into the first and ages into the second, and the second job is mostly understanding what “behind” means before anyone buys anything. The projects rhymed, and this is what they had in common.

The shape is familiar to anyone who has inherited one. A relational warehouse, loaded nightly by batch jobs that were written for a smaller business and have been patched ever since. The load window that used to end before anyone arrived now ends mid-morning, later every quarter, and the first meetings of the day run on yesterday’s numbers or on nothing. A reporting server carrying more than a hundred reports and dashboards, a good share of them with no owner anyone can name, each one a reason not to change the schema underneath it. Index tuning that lives in one person’s head. A change that would take an afternoon on a modern platform takes weeks here, because every column is load-bearing for something nobody has inventoried.

None of that is what makes the platform behind. Old platforms run fine for years. What makes it behind is the gap between the questions it was built to answer and the questions the business now asks. A new product line that the model has no place for. An operations team that has started making intraday calls on last night’s load. A finance partner who needs a measure the schema cannot produce without a rebuild. The platform answers last year’s questions correctly and this year’s not at all, and the data team has become the queue where this year’s questions wait.

The pull, once that is visible, is toward a replatforming: choose the cloud warehouse, lift the tables across, repoint the reports, switch off the old machine. It can be bought and it has a date. Done that way it produces a faster platform carrying the same model and the same undocumented transformations, now with a larger bill. The questions it could not answer are still unanswered, because the thing that could not answer them was never the hardware.

Constraint

• Month-end does not pause. The close, the board pack and the operational reports do not wait for a migration, and the first one that misses ends the project’s credibility.
• One team. The people who run the old platform are the people who build the new one. Every hour on the migration is an hour not spent keeping the old loads finishing.
• The old platform’s knowledge is in a few heads. The load order, the hand-tuned indexes and the reasons behind a dozen peculiar transformations exist as habit, not documentation. Those people are also the busiest.
• A project budget with an end date. The move was approved as a project, and two platforms running at once is a cost that shows on the bill from the first month, in a project that was sold as saving money.
• Consumers do not move for technical reasons. A report’s readers move when the new version serves them better, and not before.

The last two bind each other. The realistic plan runs two platforms for a while and moves one report at a time, which looks slower and costlier than a cutover weekend, and it is the only version I have seen hold.

Decision

Sequence the migration by the decisions the reports served, not by the tables, run the two platforms side by side on purpose, and set the old platform’s retirement date at the start.

Inventory the consumers, not the schema

The first artifact was not a data model. It was a list: every report, extract and scheduled job on the old platform, with its owner, the decision it served, and when it was last used. Three columns took longer to fill than any diagram, because the answers were in people and some of the people had left.

The list sorted itself into three buckets. Reports that served a live decision with a named owner would move, and where several of them carried the same metric with slight variations, they moved as one, rebuilt around the decision and a measure redefined for it. Reports with no owner, no decision, or no action anyone could point to would be archived, with a notice period and a way to object, rather than revived. And a middle set would stay on the old platform until it went dark, because they were needed until then and not after. The archive bucket was not small, and it is the cheapest migration there is: the work you do not do.

Move a decision at a time, from the source

Each move started from a decision, not a table: the weekly operations review, the channel budget meeting, the month-end close. For that decision, the new platform’s model was built from the source systems, with the definition settled and written down first, rather than copied from the old warehouse’s tables. That choice is the one that makes the migration a modernization. The old transformations encoded years of workarounds that no longer had reasons; re-deriving from the sources, against the definition the decision actually needed, was how those workarounds died instead of moving.

The new report then ran beside the old one, with a daily comparison and a written explanation for every material difference, the discipline I set out in Rebuilding ingestion people trust. When the differences were explained and the owner agreed, the consumers moved, the old report was retired, and the next decision started. The first decision chosen was visible, and I thought forgiving: a weekly review whose readers could tolerate a week of two numbers. The board pack moved late, once the method had a record.

Martin Fowler described this shape on his site in 2004 and later named it the strangler fig application, after the fig that grows around a host tree until the host is gone: new capability wraps the old system until nothing routes to it, and the old system is switched off rather than cut over. The data version routes consumers rather than calls, and the decision is the unit that moves.

Keep one system of record, and label migrated history

Until the last decision moved, the old warehouse stayed the system of record for anything not yet moved, and said so. A single stated source for each report, for the whole overlap, is what keeps two platforms from becoming two truths.

History was reloaded into the new platform from the sources wherever the sources still held it. Where they did not, it was carried over from the old warehouse and labeled as migrated history, with its known caveats attached, so that nobody downstream mistook a copied number for a re-derived one. Some old figures could not be reproduced from any source, and the label said so.

Freeze the old platform; the new one carries the operating model from day one

From the day the first decision moved, the old platform took fixes and nothing else. Every new request was answered with the date its decision was scheduled to move. Without the freeze the old platform keeps growing while the new one chases it.

The new platform carried the operating model from its first model: transformations in version control, tests before merge, scheduled loads with a freshness target each consumer had agreed to, and definitions documented where the model lived. Those were the rule on the new platform from day one and never retrofitted, because retrofitting is the thing that does not happen.

Put the retirement date and the overlap on the plan

The old platform’s switch-off date was set at the start and published, with the rule for moving it: once, by a stated amount, for a stated reason. The cost of running both platforms went on the budget as its own line for the whole overlap, so that the project’s bill told the truth from the first month and nobody discovered the overlap at a quarterly review.

The tradeoff I accepted

I ran two platforms for longer than anyone wanted, and paid for it twice.

A cutover weekend has a date, a before and an after, and a bill that falls the month after. Moving a decision at a time had none of those: the overlap line grew before the first report moved, and the status at every steering review was “both,” for a project sold as a saving.

I accepted that because a cutover does not fail on the weekend. It fails in the weeks after, when a number nobody trusts sends readers back to the old reports, something still reads the old warehouse so it cannot be switched off, and the organization has paid for a migration and kept what it was migrating from. That is very hard to reverse. The slow version is only expensive.

The costs:

• Double running, on the bill, for the whole overlap. The project cost more than the old platform alone for most of its life. The line on the budget made it explainable; it did not make it small.
• A freeze that frustrated people with real needs. “Not until your decision moves” is a correct answer that lands badly, and some of those requests were months out.
• Slower visible progress at the start. The first decision took longer to move than a lift would have taken to move everything, because the definitions and the sources had to be settled first.
• History with an asterisk. The migrated history, labeled as such, is a permanent caveat on a few long-running trends.
• The archive bucket cost goodwill. A report nobody used still had a sponsor, and the notice period was spent explaining to several of them that retirement was not a judgment on their function.
• Knowledge extraction competed with operations. The people whose heads held the old platform were the people keeping it running, and every hour spent writing down what they knew was an hour the old loads ran unattended.

Outcome

The decisions moved in sequence, each with its readers, and the old warehouse was switched off on the scheduled date, moved once under the rule set at the start. In one of those projects, the inventory had run to more than a hundred dashboards and reports, many of them the same metrics with slight variations, which is where the numbers that never matched had come from. More than fifty did not survive it: the ones collecting dust, the ones whose variations were feeding the disagreements, and the ones no decision or action had ever come out of, all archived rather than revived. The rest were consolidated where they served the same decision, with the measures redefined around it. What came through was about ten dashboards for decisions and about thirty more for transactional visibility and detail, and the archived ones stayed archived.

The questions that had marked the platform as behind were answered on the new one: the new product line, the intraday operational view, and the measure finance could not get. A change that had taken weeks took days. That is a direction I observed rather than measured, and I credit it to the operating model arriving with the platform.

The platform also stopped depending on a few people. A model built from documented definitions, under version control and tests, could be changed by someone who did not build it, which the old platform never allowed.

I cannot claim the chosen platform was the best available, or that the eventual savings beat the cost of the overlap. The overlap line showed what both platforms cost together. It did not show what the old one would have cost alone over the same months, and that comparison, the one a savings claim needs, I did not keep rigorously enough. For the next one I would judge it by:

• Whether each decision’s readers moved with their report, and whether any came back
• How many reports were archived or consolidated against how many were moved, and whether any archived report had to be restored
• The cost of a change before and after, measured as the time from request to a reviewed change in production
• Whether the old platform went dark on the date, and how many extensions it took
• Both bills, for the whole overlap, on one page

What I’d do differently

Write down what the old platform’s keepers knew before building anything. I started the new platform and the knowledge capture in parallel, and the capture lost every week to the operations work. A month spent only on documenting the load order, the odd transformations and the reasons for them would have been the best month of the project.

Freeze the old platform earlier and more publicly. The freeze came after the first decision moved. It should have come with the announcement, so that the request queue stopped growing before the migration started chasing it.

Put the archive bucket in front of its sponsors in the first month. I let the archiving trail the migrations, which meant arguing about a dead report while a live decision waited. Archive notices sent at the start, with a long period to object, would have cleared most of the list before the first move.

Start with a decision whose readers can tolerate two numbers for longer. The first move was visible enough to earn credibility and exposed enough to spend it when the comparison ran a week longer than promised. A less watched decision first, and the visible one second, is the sequence I would run now.

Measure the cost of a change before the migration, not only after. The next time, the first artifact after the inventory would be that number, so that weeks to days is a measurement rather than an impression.