From a slow query and numbers nobody trusts — to a datamart and a dashboard that doesn't lie
We walk through the whole path on one example: a reporting SQL query that was choking a live dashboard, a star schema with materialized views built under it, a ready BI dashboard with 4 charts — and separately, a real case where two reports disagreed on a number, and how we caught it.
Before / after on a real reporting query
The query behind a "revenue by channel over a period" dashboard, joining 4 tables with correlated subqueries for the refund filter.
What it meant for the client
- The Tableau dashboard used to open in >40s and half the filters timed out — now it opens in <1s, filters stay responsive.
- A nightly "just in case" cron report that re-computed the same numbers is gone — the live query is fast enough on its own now.
- The same query now holds up under concurrent access from several analysts at once, with no degradation.
Star schema behind the dashboard
A fact table plus dimensions, with a layer of materialized views on top — that's what the BI tool (Power BI / ClickHouse) actually reads. The dashboard never touches raw transactional tables directly.
A ready dashboard on live (synthetic) numbers
4 charts plus a summary table, reading as one visual system instead of a pile of mismatched screenshots.
Why two reports disagreed — and how we caught it
A real class of discrepancy (the numbers below are a synthetic demo example): the Tableau dashboard and the finance export for the same month showed different revenue.
| Verification step | Finding | Orders | Amount, $ |
|---|---|---|---|
| 1. Diff daily totals between sources | Gap appears exactly at the May 31 / June 1 boundary | — | — |
| 2. Compare the timezone used for cutoffs | The SQL query bucketed timestamps by UTC; the finance export used the store's local business day | — | — |
| 3. Orders placed 21:00–23:59 local on May 31 (18:00–20:59 UTC) | Landed in the June 1 report instead of May 31 | 41 | 14,360 |
| 4. Fixed the day boundary in mv_daily_sales | Both reports now match, day for day | 0 | 0 |
Need your reports faster, or your dashboard numbers reconciled?
Send us a slow query or an example of a mismatch — we'll tell you what's going on and outline a plan. Fixed price, timeline in days.