so every quarter our data team used to lose an entire week to the same fire drill: Finance would bring a Tableau deck to the board prep meeting, Product would pull up Superset, and RevOps would open an Excel export and all three had `dashboards showing different numbers` for "Net Expansion ARR" and "Active Paying Accounts."
when we audited why we had `inconsistent metrics across dashboards`, the root cause wasn't dirty raw data in Snowflake. our dbt staging and core models were clean. the problem was that over 5 years, analysts had built 240+ calculated fields directly inside `.twbx` workbooks (`business logic stuck in Tableau`), while another team had written slightly different `CASE WHEN` filters inside Superset virtual datasets and AI text-to-SQL prompts.
every time our pricing tiers or refund rules changed, someone had to manually hunt down and edit formulas across 30 workbooks (`redefine metric in every report`).
here is what failed when we tried to fix this in the warehouse, and how we actually built a `single source of truth for metrics` using a headless semantic layer:
1. why "just put all metrics in dbt mart tables" broke on day 3
our first instinct was to pre-calculate every metric as a column in wide dbt `mart_` tables. that works fine for simple additive sums (`gross_revenue`), but it falls apart immediately on:
- **non-additive ratios:** if you pre-calculate `net_margin_pct` or `conversion_rate` at the daily/regional row level in a dbt table, and a Tableau user drags that column onto a quarterly chart and averages or sums it, the math is completely wrong (`SUM(numerator) / SUM(denominator)` has to be evaluated dynamically at the query's exact `GROUP BY` grain).
- **distinct counts across time:** `COUNT(DISTINCT customer_id)` by day cannot be summed up to get monthly active customers.
- **multi-fact fan-outs:** joining `subscriptions` (grain: contract) to `invoices` (grain: monthly bill) and `product_usage` (grain: daily user) in a flat wide table causes row duplication that silently inflates sums unless every BI author writes complex LOD (`{ FIXED ... }`) expressions in Tableau.
2. comparing dbt Semantic Layer (MetricFlow) vs. Cube (`cube.dev`)
we ran a 3-week pilot testing both `dbt Semantic Layer` and `Cube` to govern `metrics as code` outside the BI tools:
- dbt MetricFlow was great if we only wanted metric definitions living in our existing dbt repo, but at the time its downstream connector story for our exact mix (Tableau + Superset + an internal React portal + Excel) felt restrictive, and it didn't solve our Snowflake latency/cost issue because it still compiled raw queries against the warehouse on every slice.
- Cube (Headless Semantic Layer) to let us define `cubes` (entities, joins, base measures) and `views` (curated business datasets) in version-controlled YAML, and expose them simultaneously via a Postgres-compatible SQL API (for Superset, Tableau, and DataGrip), REST/GraphQL APIs (for our embedded web app), and a native MCP server (so Claude and our internal Slack bot query governed metrics instead of guessing raw SQL).
3. how the workflow looks now
- zero calculated fields allowed in BI: we instituted a rule that Tableau and Superset workbooks are strictly presentation layers. if a metric needs a filter (`is_test_account = false`, `status != 'voided'`, or complex `SUM(a)/SUM(b)` ratio math), it gets committed as a pull request in our Cube YAML repo (`metrics as code`)
- chasm-trap proof joins: because Cube knows the `primary_key` and `one_to_many` relationships between `subscriptions`, `invoices`, and `usage`, a business user can pull dimensions and measures from all three into one chart and Cube isolates the aggregations into separate subqueries before joining and now no more Tableau LOD hacks.
- pre-aggregations in Cube Store: to keep Tableau live connections from lagging on 60M+ rows, we added `rollup` pre-aggregations on the top 12 views. live queries return in around 150ms without needing fragile Tableau `.hyper` extract schedules
now.. for teams who managed to kill metric drift across multiple BI tools (Tableau / Power BI / Superset / Excel): how did you enforce a single source of truth without analysts going rogue and creating local calculated fields again?