# Tableau ↔ dbt Workflows

How to translate a Tableau KPI into an equivalent dbt model, how to validate that translation without trusting a single side, and how to debug a "numbers are wrong in Tableau" ticket — which is almost always a dbt-side problem, not a workbook problem. See the main `SKILL.md` for when to reach for this reference (triage cases 2 and 3) versus pure workbook XML editing.

## Translating a Tableau KPI into dbt

The real work in porting a Tableau-defined KPI into a dbt model is **not** mechanical formula transcription. A worksheet's displayed number is the composite of three things, and all three need to be identified before writing any SQL:

1. **The base measure** — the underlying field and aggregation (e.g. `SUM(net_revenue)`).
2. **The tab's filter state** — every filter applied to the worksheet (or the dashboard it sits on), especially boolean/categorical filters that silently restrict the base measure's scope.
3. **Any table calculation layered on top** — percent-of-total, running total, rank, etc. — which changes what's displayed without changing the underlying formula.

Concretely:

- **Boolean Tableau filters become bare boolean predicates.** A worksheet filter that reduces to `<groupfilter function='member' member='true'/>` on a boolean field is exactly `WHERE <boolean_column>` (or `WHERE NOT <boolean_column>` for the false branch) — per the Gemma SQL style guide, write it as a bare predicate, not `= true`/`= 1`.
- **Percent-of-total table calcs become a ratio of two `SUM`s** in a single query. When the table calc's "total" is the whole group's sum (the common case), no window function is needed — a conditional numerator over an unconditional denominator, e.g. `safe_divide(sum(if(<condition>, <value>, 0)), sum(<value>))`, reproduces Tableau's `PctTotal` exactly.
- **Check the actual dbt model for representation quirks the Tableau formula can't tell you about** — e.g. one fact table lowercases a `brand` column while a sibling table doesn't; the Tableau formula that reads from the sqlproxy datasource gives no hint of this, only the dbt model's actual column values do. Read the model, don't infer from the formula alone.
- **For a long-format (EAV-style) KPI target model**, each ported KPI becomes one new CTE emitting exactly the model's existing column set, following whatever "add a KPI" pattern the file already establishes — don't introduce a new shape for one KPI when the file has a house style.

## Two-layer validation — never compare dev directly against Tableau

The single most important validation rule for this kind of work: **never compare a dev-environment computed value directly against Tableau's live displayed number.** Dev upstream tables can be weeks stale relative to what a live Tableau dashboard shows against production data, and a raw dev-vs-Tableau diff will surface entirely spurious "regressions" that are really just staleness artifacts.

Instead, validate in two independent layers:

1. **Dev vs. dev, for logic consistency** — run the same formula/logic against dev data both the old way (if there's a prior implementation to compare against) and the new way, or against a hand-written reference query, entirely within dev. This confirms the *logic* is internally consistent, independent of any data-recency question.
2. **Prod vs. Tableau, for definition match** — run the same formula against production upstream data (or as close to production as you can get) and compare *that* result against what Tableau actually displays. This confirms the *definition* was decoded correctly from the workbook XML, using data recency comparable to what Tableau itself is showing.

Before trusting any comparison across environments (dev vs. prod, or before-code-change vs. after), make sure both sides share upstream data recency. If dev's upstream tables are stale, rebuild them first: `dbt run --select +<model>` for every model feeding the comparison, then re-run the comparison. Skipping this step reliably produces misleading "regressions" that are actually just staleness — a real incident produced three separate false anomalies this way before rebuilding upstream and getting a clean comparison.

Small numeric residuals against a point-in-time manual reference (e.g. a value someone wrote down at a specific moment) can be expected and are not necessarily bugs — weekly values spread across multiple posting days, or late-arriving sell-out data, can shift a number slightly after the fact. "Looks wrong but isn't" is a real category here; don't chase every residual to zero before checking whether it's explained by timing.

## Exact-string metadata-join validation

For long-format (EAV) KPI models where each row carries a KPI-name string that gets joined against a separate metadata/lookup sheet or table: a mismatch between the KPI-name string your new CTE emits and the string the metadata sheet expects will **not** be caught by `dbt build`/`dbt test` passing — those checks validate the model builds and its declared tests pass, not that every row successfully joins against downstream metadata. The only thing that actually catches this class of bug is a query that explicitly performs the join and checks for KPI rows that fail to match any metadata row. Run this as an explicit validation step whenever a new KPI is added to a model with this shape — don't rely on a clean `dbt build` as proof the KPI is wired correctly end-to-end.

## Debugging wrong or missing numbers in Tableau

Most "Tableau shows a wrong/missing number" tickets are dbt/warehouse-side problems wearing a Tableau costume — the dashboard is just rendering whatever the feeding model computes. Do not start by opening the `.twbx`. Instead:

1. **Identify which dbt model(s) feed the affected worksheet/dashboard** first. If the mapping isn't already obvious from naming conventions, a narrow read of the workbook is fine here: grep the datasource `caption` attributes out of the `.twb` (see `twb-xml-reference.md`) — that's recon, not the XML surgery the triage rule warns against. Then compare *those models* against each other and against expectations — not the workbook.
2. **Dispatch a read-only Explore-style pass up front to build a complete root-cause map in one shot**: the feeding models' SQL, upstream column derivations, YAML docs, `git blame`/`git log` for whichever commit introduced the relevant logic, and a check of downstream consumers of the same models. Doing this in one pass avoids a long back-and-forth of incremental "let me also check X."
3. **Use git history, not just the current SQL, to classify the root cause** — the difference between "a deliberate design decision that turned out to encode the wrong semantics" and "an accidental bug" changes both how the fix should be framed and how it's communicated back to the ticket reporter. In one real case, an aggregate model's restrictive JOIN predicate had been introduced deliberately in an earlier commit as a considered design choice — it just turned out to be the wrong semantic once a new product line needed to flow through it.
4. **Check whether the feeding model is a dbt leaf model** (grep the repo for `ref()` calls pointing at it). A Tableau-only leaf model carries no downstream dbt blast radius, which de-risks a fix considerably — you don't need to trace impact through the rest of the DAG.
5. **When an aggregate model and a breakdown/detail model disagree**, the durable fix is not just patching the one query — it's a **singular dbt test** that sums the breakdown model at the aggregate model's grain and diffs the two (with a small float tolerance for revenue-style sums). This is the test that would have caught the original divergence automatically, and it prevents the same class of bug recurring silently in the future.
6. **Before trusting any dev-vs-prod or before-vs-after comparison, confirm both sides share upstream data recency** — see the two-layer validation rule above; stale dev schemas are a recurring source of false "regressions."
7. **When a comparison surfaces an anomaly, ask first whether your change could possibly cause it** — if it's structurally impossible for the change under review to produce the observed anomaly, look for an environment or staleness confound before spending time on the wrong hypothesis.

A related pitfall worth flagging explicitly: getting a user to choose between hypothetical options during a debugging Q&A does not substitute for validating the premise those options were built on. If the framing of the question itself might be wrong, say so and check before asking the human to pick from a menu built on an unverified assumption.