The Data Lakehouse is Burning Down

Lessons from a career of cleaning up data dumpster fires.

Imagine building a beautiful lake house on a pristine lake with gorgeous mountains in the surrounding distance, and a clean beach front in a relaxing neighborhood. Then in a couple of years, you find cracks in your foundation, the water becomes polluted by chemical runoff, and your property value plummets. This is exactly what happens when you build a data lakehouse poorly.

All initial attempts start out well. Users are excited early on that they can use one dashboard to find data instead of searching through tons of independent Excel files. Then you start adding more data, lose sight of data quality, and rush out new reports and slowly everything falls apart. Pipelines are built on top of pipelines, the dashboards are fragile and slow, and no one trusts any of the key performance indicators. Your expensive data project has lost value faster than the condo you bought near the Fukushima nuclear disaster.

So, what are common pitfalls that you need to avoid? This article goes into all the dumpster fires that I experienced throughout my career and how they could have been avoided.

"Build the house now and we will fix the plumbing later on"

Yes, this was the quote I heard frequently at one dumpster fire project. The executives wanted reports and they wanted them immediately. Middle management went along with the demands, and the ultimate wreckage was inevitable. The below image from Data Science Hierarchy of Needs - Explained shows the data pyramid. Just like a house, you have to have a solid foundation. If you are just importing data directly from an API into your Power BI report, you already have a problem. This image should be in every breakroom alongside the federal government's employee benefits.

If done properly, you should use a medallion architecture where raw data is piped into a bronze layer, cleaned at a silver layer, and then aggregated to individual data marts in a gold layer. Needless to say, skipping the transformation and governance layer is the fastest way to ruin. Furthermore, building reports with dirty data is a great way to lose any business buy-in. This company was obsessed with creating dashboards with the perfect theme setup and beautiful custom visuals without any care of the data accuracy.

Reusing old processes without understanding how they work

I worked at one company where we spent nearly six months building a dashboard with incorrect data. They did have a bronze layer that everyone assumed was accurate. It was set up years ago to collect a snapshot of the data at 6pm every night for that day. What could possibly go wrong? Well, we discovered that some employees actually worked past 6pm. Although rare, they would add data into the system. We used that data to calculate results further down the pipeline, hence wrecking the final results. It was not until we built the silver and gold layer and imported everything into Power BI, that we discovered the problem. Since the reports were not matching the legacy reports, we spent weeks isolating the problem and transposing it all the way back to the source. When we finally discovered the root cause, we were told that the mainframe developer who built the script was a contractor and no longer worked with the company.

Hiring data engineers who don't understand the business needs

While this may not be the worst cardinal sin, it needs to make the list. Yes, perhaps, these data engineers create clean pipelines and write well-organized code but if they lack any business understanding, it will show in your dashboards. I have seen teams create 40 dashboards when they needed 5. If someone asked for a new data point, they blindly added them. The data warehouse became bloated and confusing. A good data professional will work alongside the business and understand their needs better than them. They would be asking deep questions on how things actually work and pushing back on meaningless KPIs. The ability to focus on the true business needs and to consolidate them into as few visualizations as possible is an art.

Stubbornly hanging onto a warehouse that is beyond fixable

Yes, every consultant has experienced this madness. Your team has been hired to fix the mess of the past three consulting companies. Pipelines are nested inside layers of pipelines. The naming conventions don't make any sense, and nothing has been documented. I worked with one company that had a pipeline nested 4 layers deep. Once I got to the original stored procedure, I had to decode 10 temporary tables. Needless to say, multiple developers over the years tacked on more code and instead of fixing the root cause they band-aided it more. The process was critical to the business but insanely inefficient. The only solution to truly fix this issue is to completely build the architecture from scratch. No AI tool, no person, or god from another realm will ever surgically adjust the code to be usable.

Not checking for data quality issues early in the process

Reusing old processes from before could also fit into this category. Had we checked some of the root tables in the beginning, we would have found the issue before we ever started. Another big issue is joining tables together without checking the table count. I found an issue where one join caused an additional 25 records to be added to a table of over 1 million records. It sounds like a small issue but tracing it back to the source later on takes a lot of effort. It is also wise to build basic constraints. If you are taking a test, the score can only be between 0 and 100. I dealt with one report where a college claimed to have a 900% graduation rate. If you have an accurate report to compare to, then just build one table with the new data and one as the original data and do a hash join to see if they match.

All of these issues could have been easily avoided with a good data architect and smart, patient management. If you are reading this and laughing, you must have experienced at least one of these disasters. To avoid some frustration, here are some key take away for a successful project.

1.)    Dashboards should be simple and easy for users to understand. Remove unnecessary clutter.

2.)    Build your data lakehouse using a medallion architecture.

3.)    Do error checking early and often.

4.)    Work closely with the business to understand their needs.

5.)    Work in agile sprints with demos and strong user feedback.

6.)    Don't patch over a problem. Stop, think, and rebuild when necessary.

7.)    Add constraints to prevent poor data from polluting your dashboard.

8.)    Present a plan to the executive team before they create unmanageable deadlines.