ETL routes that nobody touches anymore: when the replacement is worthwhile

In almost every company with grown reporting, they exist: the ETL line, which nobody touches anymore. She runs at night, she delivers numbers, and no one can explain in detail what she does. As long as it goes through, this is a marginal issue. It only becomes noticeable when someone wants to change a key figure – or when two reports show different sales. How we set up data routes and reporting is on our side Business Intelligence and ETL.

How to recognize a route at the end of life

The symptoms are astonishingly uniform, regardless of whether the track runs in SSIS, Talend, a batch of SQL scripts, or in a self-built job control:

• Nobody changes it voluntarily. Adjustments are postponed or built alongside, instead of being made on the track itself. This is the most reliable warning signal.
• There are manual intermediate steps. An Excel file that someone puts down in the morning, a macro, an intermediate export. What began as a transitional solution has become part of the productive operation.
• The logic is only in the code. Why a turnover is calculated differently from a certain date knows the colleague who left three years ago – or a nested CASE statement that no one reads.
• The runtime grows faster than the amount of data. A sign that each new requirement package is processed in addition, instead of adapting existing steps.
• Mistakes are first noticed by the specialist department. It is not the track that reports the breakdown, but someone wonders about an empty column in the report.

Two or three of these points are normal in grown systems. Five of them mean that the route pays off technical debts that will eventually mature.

The real risk is not technology

You would expect old tracks to fail in performance. In practice, they fail because of trust. As soon as two reports for the same metric show different values, a parallel world begins in the specialist department: someone builds their own Excel evaluation, because “you can not trust the numbers in the dashboard”. From then on, there are two truths, and the more expensive of them is the one that no one knows.

A dashboard that the department does not believe is worse than none. It costs money in the company and is still not used for decisions.

That is precisely why the question of whether a route should be replaced is not a purely technical one. It decides whether the figures are still binding.

What has changed since the classic ETL

The classic structure transforms data on the go and loads the result into the target system. What happens in between is difficult to trace later – the raw data is nowhere in this form.

Modern superstructures reverse the order: first load, then transform in the target system. The advantage is less the speed than the Traceability. If the raw data remains unchanged and each transformation is based on it as a versioned, testable step, the question “why is this number here?” can be answered without restarting the job. Tools like dbt have made that just the standard; Go to the platform site Power BI and Microsoft Fabric in the same direction.

This does not mean that every existing line must be converted. It is said that a track that has to be touched anyway should not be recreated in the old pattern.

Removal without Big Bang

The most common mistake in the replacement is the deadline change. It sounds clean and creates exactly the situation you want to avoid: On Monday, the new system delivers different numbers than the old one on Friday, and no one can say which ones are true.

The resilient path is parallel operation. The new track runs alongside the old one for a while without anyone surrounding his reports. Both results are compared automatically, and each deviation is clarified individually – with the new track regularly turning out to be right and the old one containing an error that had been unnoticed for years. Exactly these deviations are the real benefit of the conversion, because they reveal what nobody knew about before. Only when the differences are explained, the switch is made; The old track will remain running for a while.

Not everything is changed all at once, but along technical areas – first the turnover, then the stocks, then the controlling. This takes longer and is the only way in which the specialist department comes along.

Data quality cannot be repaired into the line

An expectation that is regularly disappointed: that the new route improves the data quality. It does not. It only makes the existing problems more visible because it no longer implicitly calculates them away.

Duplicates, inconsistent spellings and missing assignments arise in the source systems and must be dealt with there. A transformation layer that conceals this only shifts the problem – and even becomes the opaque logic you just wanted to get rid of. The same mechanism applies to system changes; We have them in our contribution to lossless data migration described.

When the renovation is worthwhile – and when not

It is worthwhile if the route blocks changes, if numbers are in question, if a source system is changed anyway, or if the knowledge about the route depends only on one person. This last point is often underestimated until the person resigns.

It is not worthwhile if the track is stable, its logic is documented and no one needs to change – even if it seems technically outdated. “Runs flawlessly for eight years” is a quality feature, not a defect. Modernisation without cause is employment, not added value.

A pragmatic intermediate step for the grey area: do not rebuild the existing route, but make it visible. Monitoring on runtimes and abortions, an automatic plausibility test on the most important key figures, and the technical logic once written down. This costs a few days and takes away most of the risk.

Conclusion

The question is rarely whether an ETL line is technically obsolete – that is many without causing damage. The question is whether it still has confidence and whether changes can still be made to it. Where both are no longer given, no further cultivation helps, but a detachment in parallel operation, technically in stages and with automated comparison against the stock. If you want to assess where your routes are, talk to us Often enough is a look at runtimes, change history and the question of who can explain the logic.