Why does an analytics project spend most of its budget before the first dashboard exists?

The iceberg illusion: the expectation vs. reality

When companies realize they need to upgrade their data infrastructure, the ultimate destination is typically clear to decision-makers: they want a clean dashboard. By looking at a screen, they want to know what the company bought in the past or how many resources it spent.

To achieve this, the companies give the data analysts the data they have: usually years of Excel sheets. Analysts assume that once this information is in a proper analytics warehouse, it will be easy to visualize and derive insights from.

This is an iceberg illusion.

Companies that don't realize this raw data has a history of manual upkeep in the spreadsheets stay blind to the amount of preparatory work needed before anyone can build the first dashboards. Information stored in Excel sheets lacks rigor and reliability required for systematic reporting. A human can make a mistake entering a comma, omit a column identifier, or change column format from year to year, and a spreadsheet will not object to any of these actions. A data pipeline, on the other hand, does not have this flexibility. Once the data has to follow a certain logic, the weaknesses of the manual process show.

When the analysts attempt to ingest this information into the new automated system, it results in a large number of errors at the beginning of the project. We cannot simply plug in the raw Excel sheets into the visualization tools. The company would find itself quickly disillusioned with the new platform. Instead, the engineering team has to become an excavation team. For the first several weeks or months after the project or feature has started, the engineers have to reverse-engineer the client’s data preparation methods to bring this information to the level of quality suitable for systematic analysis. The company expects a short time to value. The engineering team has to deal with the majority of its allocated resources spent on fixing the legacy data before anyone can build any charts.

Exposing the weaknesses in legacy data: a real-world example

A global manufacturing company has asked for our help to report on their annual purchasing and various other statistics. Their current process involves a large number of manual steps. They tasked us with helping them build an integrated data platform to consolidate this information.

At the beginning of the project, the only source was Excel sheets. We saw real-life examples of how the manual process failed its owners. Over the first three years of data, the format of these sheets changed every year. We had to create separate pipelines for every year to parse and transform this information and bring it to a consistent format for our internal tables. We could not validate that any of this data was correct because it came straight from Excel, so we had to rely on the client to validate their figures.

The real problem manifested itself when we tried to apply business logic to this dataset. For the reporting in particular, the client wanted to track their spending habits and other statistics per supplier across years. To do that, we had to bind particular rows of information together.

Spreadsheets let you do whatever you want, pipelines do not.

As soon as we implemented the business logic necessary to chain this data together, we saw a large number of records that failed this logic test. The reason was simple: manual entry didn't produce data in the format needed for systematic analysis. The logic we designed to extract value from this information acted as a litmus test, exposing the weaknesses in the company’s data management practices. As a result, we had to rework this historical information before we could put it to real use.

Data integrity issues are the reason why discovery scope in such projects is so large, and this is when clients tend to start questioning the value of the project or feature. To get to value faster, the client tried to use the data we parsed from the Excel sheets before we had validated it properly. It did not take long for the company to realize that the data coming out of the pipeline does not match their expectations. There was nothing wrong with the data engineering itself. It simply showed the reality of the flawed manual process. To get to this point, the client has had to go through an iterative process of pulling this information out of Excel, validating it against their internal controls, and pushing it back. At this point, we were spending weeks taking the rejected rows out of the pipeline and sending them back to the client for their review. We had to correct the manual entries row by row before they could go through the pipeline again.

Engineering for a moving target

When historical data reveals weaknesses in the existing processes, it rarely stops there. To get their data in order, the clients we work with often revisit the ruleset they apply to their data management, and this creates a number of complications for the ongoing project. We had to re-engineer a substantial number of processes several times throughout the project for the manufacturing company we mentioned earlier, as the client worked to update their business rules.

In our case, we eventually got access to the internal tables the client used for some of this information. One of them was effectively a live feed of the data. The other table contained the data from closed years, in other words, the information that was supposed to be appended once a year. We expected this data to be static, but it turned out to be inconsistently updated several times throughout the year. We also didn't have access to the information showing how purchased products split across business units. We had to rely on the client to provide us with the Excels for those datasets during a specific time period.

The client had to retrofit their understanding of the costs to data that was inconsistent and updated periodically. Every time the client saw a problem with the way the data showed up in the pipeline, they identified a set of changes they wanted to see reflected in the automated process. Every single one of these requests represented a significant engineering effort. When the changes affected the data structure and transformation logic, we had to redesign parts of the data pipeline. In most cases, we could not simply update the instructions the data had to follow. The complexity of changes required us to sometimes write new pipelines to process the data in certain edge cases.

To most buyers of data engineering services, this stage represents wasted time and money. But the data engineering team was doing exactly what it had to do to fulfill its mandate, making sure that the data reflected the reality the client wanted to see. Without this step, they would not have been able to proceed to the next step of creating consolidated reports, as the automated process would fail to account for all the nuances of the manual Excel-based process.

Building the invisible architecture

This brings us to the next critical step of data engineering for historical information, building an invisible architecture that hides the complexity of the process and serves as a shock absorber for the constant changes in the business logic.

When you design the data ingestion rules directly in the production reports, every change to the business logic breaks everything. To mitigate the risk, we had to build an architecture that isolated the production reports from the constant tinkering with the logic of data transformations. We used Medallion Architecture for this.

To fulfill the client's needs, we also had to design a two-step process. The first step separated the initial data we loaded from the client's other department, the same data we used on the application side. The second step processed that data using the Databricks platform.

That first step consisted of designing a schema in the SQL database to capture and store the client's historical information and a pipeline that ingested the data into the database. At this point, we had to make several important decisions about what data quality standards we would hold this information to. That meant asking ourselves what information we were willing to sacrifice to automate the data management process. In this particular case, we had to keep some data points in the system, such as product costs without identifiers, even when they did not meet all the quality control rules, for the sake of preserving historical records.

Once the information had passed this stage, we moved it into the Databricks environment, where it underwent additional transformations and entered the Medallion system, first, the Bronze section, then the Silver, and finally the Gold. We focused most of the processing power on the middle layer, where the transformations and validations that made this information useful for reporting and analytics took place. We accommodated the client’s constant rule changes with iterative upgrades to the transformation logic in the Silver section. Once this layer was ready, we could pull the information from it to produce the actual dashboards, without further disruptions to the production rules. This architecture hid the complexity of the data transformation process from end users, who never needed to understand the challenges of turning raw data into a reliable analytical instrument.

The ROI of patience

Despite the complexity of this step, it was essential if the company wanted to automate its reporting and analytics functions. The amount of time and resources spent on this stage was substantial, and there was a lot of pressure from the stakeholders to finish this part of the project as soon as possible. Other departments wanted to start using this information for their own reports. But it was at this stage that the project spent the majority of its resources, and with good reason. Once the data could pass the quality control tests reliably and regularly, the time spent preparing it paid off.

The ROI of this step became apparent when the reporting dashboard was finally launched: the company now had a true single source of truth. It could retire its manual Excel processes in favor of an automated solution that provided it with consistent, reliable data fit for analysis. This was the promise of data engineering, an assurance that the company’s decisions were based on facts.

Conclusion

Companies that wish to benefit from data engineering need to be aware of the challenges associated with automated processing of historical information. This process is much more resource-intensive than working with data that already follows the required logic and quality control standards. To minimize the costs, companies should clearly define their reporting requirements, provide direct access to source systems, and, if possible, analyze the data before the work starts.

Frequently asked questions