Every business loves a good dashboard. They’re the digital windows into our operational health, sales performance, and customer engagement. They tell us what’s working, what’s not, and where we need to focus. But what happens when the most crucial dashboard you encounter isn’t about your business at all, but about the very infrastructure that powers your insights?
For us, that dashboard was the cloud bill. More specifically, a dynamic, ever-changing visualization of our data warehouse costs that looked less like a steady growth curve and more like a seismograph during an earthquake. A jagged skyline of $800 to $1,400 daily spikes, wild swings between days, and a trendline barely drifting downwards. It wasn’t random; it was a heartbeat monitor for a system under severe stress. This wasn’t the dashboard we wanted to open, but it rapidly became the only one that truly mattered.
The Hidden Cost of “Everything Real-Time”
For years, our Business Intelligence (BI) ecosystem operated much like many others, especially in retail. The mantra was simple: build big semantic models, keep everything real-time “just to be safe,” give stores dashboards connected directly to the warehouse, let self-service teams create dozens of near-duplicate datasets, and refresh everything “as often as possible.” We hoped the cloud bill would stay reasonable.
With hundreds of stores and headquarters teams all hitting the same semantic models, every “live” interaction multiplied our warehouse load instantly. And it worked — until it didn’t. The cost chart revealed a problem we weren’t measuring: DirectQuery was silently multiplying our expenses.
Think about it: every click, every filter application, every user across dozens of stores and HQ teams generated live queries against our data warehouse. Our largest models were simply too big to import into memory, which meant we were operating without crucial safeguards: no caching, no query folding, no cost control, and no way to protect the warehouse from user traffic.
The problem wasn’t our Cloud Warehouse provider. The problem was the architecture we had built on top of it. The chart wasn’t just showing costs; it was a confession of structural BI debt accumulating over years.
From Chaos to Control: Our First Architectural Break
If you were to look at that volatile cost chart, you’d see a sudden, permanent dip around March 28. That marked our first major intervention. We realized that our giant, 500M+ row models – the very ones too large to import – were the bottleneck. We had to rethink their fundamental structure.
Our solution was to split these behemoths into two logical, optimized pieces. First, we created purpose-built, optimized Warehouse tables and views, meticulously aligned to actual reporting use cases. This meant less data being pulled than ever before, and only the data truly needed for a specific report.
Second, we designed a semantic model around those optimized pieces, making import mode not only possible but incredibly fast. This single architectural choice initiated a complete transformation. Our dashboard system transitioned predominantly to an import-based operation. This drastically reduced DirectQuery operations, allowed for successful caching, and provided a far more efficient system to handle all incoming requests.
The cost chart reflected this instantly and profoundly. This wasn’t mere performance tuning; this was a structural correction, a fundamental re-engineering to address years of accumulated BI debt. The ugliest spikes in our daily spending disappeared, making way for a noticeably flatter, more manageable landscape.
The “Live Enough” Solution: A Hybrid Approach
While the first act killed the most egregious spikes, the chart made it clear we weren’t entirely done. The remaining cost fluctuations, though smaller, still signaled inefficiency. The lingering business need for perceived “real-time” dashboard data for critical operational decisions still locked us into some DirectQuery usage, driving those persistent smaller spikes.
The solution we developed turned out to be one of our most important accomplishments: a 5-minute incremental-refresh import model. This hybrid approach allowed us to deliver data that felt “live” to the business, without the exorbitant cost of true real-time DirectQuery.
Here’s how it worked its magic:
- We imported only a small, rolling window of data.
- This small window was refreshed every 5 minutes.
- Critically, we only refreshed the latest partition, minimizing the data processed.
- All stores could then hit a shared Fabric cache, drastically reducing direct warehouse queries.
To the business users, the experience felt identical to real-time. But to our data warehouse, the load was transformed from hundreds of hits per minute to a single, optimized hit every five minutes. If you trace the chart around May 8, you’ll see the second dramatic drop – the point where the remaining chaos vanished. After that date, the architecture finally entered a stable, predictable pattern. Costs became stable, predictable, and architecture-driven. The spikes didn’t return because the architectural cause no longer existed.
90 Days, $110,000 Saved: A Practical Outcome
Here’s what 90 days of focused BI re-architecture delivered:
| Metric | Before | After |
|---|---|---|
| Avg Daily BigQuery Cost | ~$545 | ~$260 |
| Annual Spend | ~$199,000 | ~$95,000 |
| Annual Savings | – | ~$110,000 |
| Model Size | Too large to import | Split + optimized |
| DirectQuery Usage | Heavy | Minimal |
| Stability | Chaotic | Controlled |
And perhaps most importantly, we didn’t sacrifice capability. Data freshness remained fast, query performance improved, and the user experience stayed “live.” We achieved this not by cutting features or downgrading experiences, but by designing our BI intentionally.
Lessons Learned from the Architecture Collapse
These 90 days taught us more about effective BI than any new tool or feature could. The insights gained are universal:
- If a model is too big to import, it’s a cost problem. Not just a data problem or a business problem, but a fundamental structural BI issue that needs addressing.
- Real-time is a feeling, not a feature. Most operational needs in retail don’t demand sub-second freshness; they need predictability and speed that *feels* instant.
- DirectQuery should be the exception, not the default. It seems easy and flexible upfront, but it becomes expensive the moment users actually start using it at scale.
- Refresh schedules are architectural decisions. Every 5-minute refresh that isn’t truly necessary becomes an unnecessary tax on your data warehouse resources.
- BI debt compounds silently until the cloud bill illuminates it. Just like technical debt, architectural shortcuts and unoptimized approaches accrue hidden costs that eventually become impossible to ignore.
Conclusion: The Unfolding Story of BI Architecture
Architecture, by its very nature, is often invisible – until it becomes expensive. That relentless cost chart became our unexpected guide, making the invisible visible and forcing us to confront the reality of our BI infrastructure. Two structural changes, implemented just months apart, were enough to transform months of chaotic, unpredictable spending into a stable, predictable, and affordable BI ecosystem.
This wasn’t achieved through throttling usage or cutting critical features. It was by intentionally designing our BI around how the business actually needs to consume data, rather than passively letting queries happen by default. Every BI team likely has a similar chart somewhere; most just haven’t dared to look at it yet.
And perhaps that’s the real, enduring lesson here: BI is never truly “done.” The architecture stabilizes, the costs drop, and the charts flatten, but the curiosity doesn’t. There’s always a new approach to explore, an old one to rethink, and a way to make BI lighter, faster, and more meaningful. Continuous improvement isn’t merely a requirement in BI; it’s the passion that keeps the entire discipline moving forward.
