BI and dashboarding tools
Tableau, Power BI, Looker, Superset, Metabase. The engineering question is never which is prettiest - it is where the metric is defined and what the tool does to your warehouse.
The one distinction that matters
Where does the calculation live?
| Model | Consequence |
|---|---|
| In the tool | Fast to build; logic per dashboard |
| In a semantic layer | One definition; needs maintaining |
| In the warehouse | dbt models; the tool just reads |
The failure this predicts is the one every company hits: three dashboards report three different values for “active users”, because three analysts each wrote the filter slightly differently in the tool. Nobody can say which is right, and trust in all dashboards drops.
The fix is defining metrics once, upstream - as dbt models or in a semantic layer - and letting the BI tool present rather than compute. Saying that unprompted is what separates a data-engineering answer from a tool preference.
The tools
Power BI dominates where Microsoft does, because it is bundled with E5 licensing and integrates with Entra ID and Fabric. Its modelling language, DAX, is powerful and genuinely hard - the row-context versus filter-context distinction is where most Power BI bugs live. Import mode copies data into a compressed in-memory model; DirectQuery pushes to the source and is slower but current.
Tableau is the visualisation-first option with the strongest exploratory experience. Extracts are the same idea as Power BI’s import mode. Cost is per-user and noticeable at scale.
Looker puts the semantic layer first: LookML defines metrics in version control, and every query compiles to SQL against the warehouse. It is the tool whose architecture matches the “define metrics once” principle, at the cost of requiring that modelling work upfront.
Superset and Metabase are the open-source options. Superset is more capable and heavier to run; Metabase is the fastest to stand up and the easiest for non-analysts. Both are the sensible answer when per-seat licensing is the constraint.
What a backend engineer is actually asked
Rarely how to build a dashboard. Usually one of these:
“The dashboard is slow.” Almost always the query, not the tool. The tool generates SQL - read it. Common causes: no partition filter so it scans the full table, a join fanning out rows, or a dashboard firing twenty queries on load because every tile queries independently.
-- What the tool generated, before blaming the tool.
SELECT date_trunc('day', created_at), count(*)
FROM events
WHERE created_at >= now() - interval '90 days'
GROUP BY 1;Without a partition or index on created_at this reads everything. Pre-
aggregate into a daily rollup table and the dashboard reads thousands of rows
instead of millions.
“Why is the warehouse bill up?” Because dashboards auto-refresh. A tile on a fifteen-minute schedule against a per-scan-billed warehouse is a recurring cost nobody attributed to it. Cache, schedule refreshes to match how often the data actually changes, and materialise what many dashboards share.
“Can the dashboard hit production Postgres?” For a small dataset, yes, with a read replica and a statement timeout. Past that, analytical scans and OLTP traffic on one instance is how the transactional workload gets starved - see Snowflake, BigQuery, Databricks and Synapse.
Serving BI from a Python service
Occasionally the ask is an embedded dashboard rather than a BI tool. The decision is the same as always - do not compute in the presentation layer:
@router.get("/metrics/active-users")
async def active_users(days: int = 30):
# reads a pre-aggregated rollup, not raw events
return await repo.active_users_rollup(days)If the endpoint aggregates raw rows on every request, it is a dashboard tile with a slow query, and it will fail the same way.
Related
Interview angle 5
- “How do you choose a BI tool?” - mostly by the ecosystem and licensing already in place: Power BI where Microsoft is, Looker when the semantic layer matters most, Superset or Metabase when per-seat cost is the constraint. The engineering decision is where metrics are defined, not which tool renders them.
- “Three dashboards disagree about active users. What went wrong?” - the metric was defined in the tool, three times, slightly differently. Define it once upstream as a dbt model or in a semantic layer and let the tool present rather than compute.
- “A dashboard is slow. How do you debug it?” - read the SQL the tool generated before blaming the tool. Usually a missing partition filter causing a full scan, a join fanning out rows, or a page firing a query per tile. Pre-aggregate into a rollup so the dashboard reads thousands of rows, not millions.
- “Why did the warehouse bill rise after a BI rollout?” - scheduled auto-refresh on per-scan pricing. Match refresh frequency to how often data actually changes, cache, and materialise what many dashboards share.
- “Can BI query production Postgres directly?” - for small data, via a read replica with a statement timeout. Beyond that, analytical scans starve OLTP traffic on the same instance and the workloads need separating.