Behind every daily hotel pickup report lies a tangle of data processes, failed exports, and 3 a.m. troubleshooting sessions. Here’s how I rebuilt a faster, smarter, and more reliable system and what I learned along the way.
The problem
It started like many data challenges do; with a process that technically worked, but only just.
Each morning, the hotel’s daily pickup report was generated from multiple XML files exported overnight. These files tracked booking activity and performance, forming the foundation for critical business decisions. The issue? Processing these files took around four hours every day, starting at 3 a.m. Each file was handled sequentially, one after the other, which meant that if anything failed, everything was delayed.
Worse still, the process opened entire XML files into memory, converted them into unstructured JSON, and only then loaded them into the data warehouse, leaving the system at constant risk of memory failures. Once the data made it in, it still needed to be flattened before it could be used. Inefficient, fragile, and slow.
Something had to change.
The plan
The goal was clear: solve all the major issues in one go.
Issue 1: Process files asynchronously, without forcing them to queue.
Issue 2: Avoid loading full files into memory, reducing the risk of crashes.
Issue 3: Ensure data was flattened before loading into the warehouse, improving performance and usability.
The solution
For asynchronous processing, I built a solution focused on multi-threading and scaling using Azure Functions. This allowed files to be processed in parallel, automatically scaling based on demand.
To handle the large XML files safely, I introduced streaming and data sharding. Instead of loading the entire file into memory, the process read data in batches, tracked progress using a cursor, and wrote the parsed results into smaller CSV files before clearing memory. This not only prevented memory issues but also made processing significantly faster and more reliable.
At this stage, the system was running smoothly. But as always, once you fix one set of problems, another challenge reveals itself.
The next challenge: Evolving the pickup report
The existing pickup report sent via email each day only compared today vs. yesterday. The new requirement was more ambitious: to show cumulative pickup over any given date range.
It sounded straightforward enough. But designing it correctly required rethinking how daily data was captured and stored.
I started with the basics: every day, we need to record what’s “on the books.” The pickup for a given day is simply the difference between todays and yesterday’s figures. Easy enough in theory.
But I didn’t want the data warehouse to reprocess the same data every day, that would waste time and computing power. The answer was an incremental approach. Since each row of data already had a UUID, I could safely append only new data each day, avoiding duplication and unnecessary processing.
The unexpected problem
Just as everything seemed to be working, a familiar issue appeared:
“We need to re-run the daily export for last Thursday. The process didn’t finish that day.”
The problem? My incremental models were designed to ignore previously processed data. They wouldn’t accept a replacement file for an existing date.
This required a rethink.
I built a new model, the “daily_file” model, to determine which file was the correct one for each day and each hotel. If a new export was generated, it automatically replaced the old data. Then, I modified the incremental process to perform a merge instead of a simple append, ensuring old or incorrect data was overwritten cleanly.
This made the system resilient to late or corrected data. Even if a file from three months ago was re-exported, it would be picked up and handled correctly.
The final test
Then came the real test.
“Fifteen hotels failed their day-end process last night. Can we re-run them all?”
This was the moment everything had been built for.
Fifteen files landed within thirty seconds. The new pipeline processed them simultaneously. Within a minute, all files were completed successfully. The incremental merge strategy worked exactly as designed. No duplication, no delays, and no manual intervention required.
The result was a fully automated, resilient, and efficient data pipeline that produced accurate pickup figures for any date range.
When asked how I fixed it, I could honestly say:
“I didn’t have to do anything. The system did what it was designed for.”
Reflections
Rebuilding the pickup report taught me that data engineering isn’t just about speed or clever code. It’s about thinking carefully before building. It’s easy to rush into a solution, but taking time to fully understand the problem almost always leads to cleaner, more sustainable results.
The key lessons?
Design for failure – because it will happen.
Think incrementally – both in your data and your approach.
Build for automation – your future self will thank you.
Today, the process runs in minutes instead of hours, and the hotel teams get the insights they need when they need them.
And while there will always be new challenges, new data formats, new requirements, new edge cases; this project stands as a reminder that good engineering isn’t just about solving problems. It’s about building systems that keep solving them long after you’ve moved on.
“Assured Insights helped us unify disparate data into a powerful BI solution that’s highly flexible and, compared to our current solution, faster and more accurate. They quickly grasped our complex business model and delivered with professionalism and a customer-first mindset. Friendly, accommodating, and truly committed – highly recommended!”
Rick Medford, VP Change Management & Technology – Meininger Hotels
The post appeared first on Assured Insights.
Posted by