Behavioral · medium
How do you decide what belongs in the transformation layer versus the BI tool?
Asked in Analytics Engineer interviews.
Short answer
Put anything reused, tested, or definition-critical (metrics, joins, business logic) in the warehouse via dbt so it is version-controlled and consistent, and leave presentation, ad-hoc filtering, and last-mile formatting to the BI tool. The rule of thumb: if two dashboards would compute it, it belongs upstream.
| Transformation layer | BI tool | |
|---|---|---|
| Put here | Shared definitions and joins | Presentation and ad-hoc slicing |
| Reused by | Every downstream consumer | That one dashboard |
| Version controlled | Yes | Usually not |
| Testable | Yes, in CI | Rarely |
| Symptom of getting it wrong | None, this is the default | Two dashboards disagree on active users |
How to answer it
The rule
If two dashboards would need the same logic, it belongs upstream. The table above lays out the split; this is how to apply it.
What belongs where
Transformation layer joins, business definitions, metric logic,
deduplication, anything shared
BI tool presentation, formatting, ad-hoc filtering and
drill-down, chart-specific arrangementThe test is reuse and testability. Logic in the transformation layer is version-controlled, reviewed, tested in CI and reused by everything downstream. Logic in a BI tool is usually none of those, and it is invisible to anyone who does not open that particular dashboard.
Why the pull toward the BI tool
It is faster. A calculated field takes two minutes and a dbt model takes an afternoon plus a review. That is a real advantage under deadline pressure, and it is why the drift happens.
The cost arrives later: the same metric now exists in four dashboards with three slightly different definitions, and nobody can say which is right. The "two dashboards disagree on active users" problem is this problem, a year on.
Signals you got it wrong
- The same CASE statement appears in several dashboards.
- A metric definition changes and someone has to update it in five places.
- Two dashboards disagree and the difference is a filter buried in a chart.
- A new analyst cannot find where a number comes from without opening the BI tool.
Key points
- Shared logic upstream, presentation in the tool.
- Version control and tests are the deciding advantage, not performance.
- The BI tool is faster today and more expensive every month after.
- A semantic layer is the middle ground: defined once, consumed by many tools.
- Ad-hoc exploration in the tool is fine. Promote it upstream once it becomes load bearing.
FAQ
- What about performance? Pre-aggregating upstream usually helps there too, so the two goals point the same way.
- Is any logic acceptable in the tool? Yes: formatting, a chart-specific filter, an exploratory calculation not yet promoted.
- What is a semantic layer? A definition layer between the warehouse and the tools, so every consumer reads one definition.
The 30-second answer
My rule is that if two dashboards would need the same logic, it belongs upstream in the transformation layer. Joins, business definitions and metric logic go there; presentation, formatting and ad-hoc filtering stay in the BI tool.
The reason is not performance, it is that upstream logic is version controlled, reviewed, tested and reusable, and a calculated field in a dashboard is none of those and invisible to anyone who does not open that dashboard.
I would acknowledge the pull the other way, because a calculated field takes two minutes and a model takes an afternoon. That is why drift happens. But the bill arrives as the same metric living in four dashboards with three slightly different definitions, which is exactly how two dashboards end up disagreeing about active users.
In the interview
- What they are checking: the reuse and testability argument, and a concrete rule rather than a preference.
- Common mistake: "whatever is faster to build". It is always faster to build in the tool, once.