BI Dashboards skill for Codex

A useful dashboard answers three to five stated questions, every number on it has one written definition that matches finance or product’s, it loads in under about five seconds, and it says how fresh its data is. If nobody can name the decision it supports, it should not be built.

Before building

1. Interview the requester (or ask Codex’s user) for: audience, decisions the dashboard supports, the questions it must answer, refresh needs, and who owns each metric.
2. Read AGENTS.md and find the semantic layer if one exists: dbt metrics or the dbt Semantic Layer, Metabase models and metrics, Superset datasets, LookML. Reuse it.
3. Find existing dashboards on the same topic. A second “revenue” chart with a slightly different filter is how organisations end up with two revenues.
4. Check access: API keys or service accounts for the BI tool, which database user it queries as. Creating or changing dashboards in a shared BI instance is visible to others; confirm with the user before publishing or overwriting anything.

Metric definitions first

Write each KPI as a spec before any chart:

| Metric | Definition | Grain | Filters | Owner | Source |
|---|---|---|---|---|---|
| Net revenue | sum(order_lines.net_amount) - sum(refunds.amount), in EUR at order-date FX | day | excludes test orders, internal accounts | Finance (A. Ruiz) | marts.fct_order_lines, marts.fct_refunds |
| Active customers | distinct customers with >= 1 paid order in trailing 28 days | day | excludes B2B | Growth | marts.fct_orders |
| Conversion rate | orders / sessions, same day, same channel | day | web only | Product | marts.fct_sessions |

Ratios are computed as sum(numerator) / sum(denominator) at the displayed grain, never as an average of daily ratios.

Model, then visualise

Point charts at modelled tables or views with one row per entity per period, never at raw event tables. Push joins and business logic into the warehouse (dbt models or views); keep the BI layer to filters and aggregation.

Tool	Reusable layer	Use it for
Metabase	Models (curated saved questions with column metadata) and Metrics	Every KPI chart starts from a model or metric, not from a raw table
Superset	Datasets (physical tables or virtual SQL datasets) with metrics and calculated columns defined on the dataset	Define net_revenue once on the dataset; charts select it
Looker Studio	Data sources with calculated fields; BigQuery views or custom SQL	Keep heavy logic in BigQuery views; avoid blends for core KPIs

Metabase SQL with filters

select
  date_trunc('week', order_date) as week,
  channel,
  sum(net_amount) as net_revenue
from marts.fct_orders
where 1 = 1
  [[and {{order_date}}]]        -- field filter mapped to fct_orders.order_date
  [[and channel = {{channel}}]] -- text variable, optional
group by 1, 2
order by 1

Field filters ({{order_date}} with no column on the left) are mapped to a column in the variable settings and can be wired to dashboard filters; optional clauses go in [[ ]].

Superset virtual dataset with Jinja

select order_date, channel, net_amount
from marts.fct_orders
where 1 = 1
{% if from_dttm %} and order_date >= '{{ from_dttm }}' {% endif %}
{% if to_dttm %}   and order_date <  '{{ to_dttm }}'   {% endif %}
{% if filter_values('channel') %}
  and channel in {{ filter_values('channel') | where_in }}
{% endif %}

Jinja templating needs the ENABLE_TEMPLATE_PROCESSING feature flag in superset_config.py. Prefer plain datasets with native filters; use Jinja only when the filter must apply inside a subquery.

Layout

Row 1  Headline KPIs (4-6 big numbers), each with comparison to prior period and target
Row 2  Trend of the primary KPI over time, with the target line
Row 3  Breakdown: the KPI by the 1-2 dimensions that explain most variance
Row 4  Detail table for drill-down, sorted by impact, limited rows
Footer Definitions link, data freshness, owner

• One dashboard per audience. Executives get row 1-2; analysts get filters and detail.
• Global filters (date range, region) at the top, applied to every card; say on the page when a card ignores a filter.
• Default date range: complete periods only (last full week or month). Partial current periods produce fake drops.
• Chart choice: line for trends, bar for comparing categories, table for lookup, single number for KPIs. Avoid pies beyond 3 slices, dual axes and gauges.
• For visual design detail (colour, spacing, hierarchy) hand off to $dashboard-design.

Performance and freshness

Symptom	Fix
Card queries scan raw events	Pre-aggregate in a daily rollup model; point the card at it
Every viewer reruns the same query	Enable result caching (Metabase question/dashboard cache, Superset cache timeout on the dataset, Looker Studio data freshness setting)
Dashboard runs 30 cards on load	Split into tabs; Superset and Metabase load tab contents lazily
Looker Studio on BigQuery is slow or costly	Use an extract or a BI Engine reservation, and partitioned, clustered source tables
Filters cause full scans	Filter on partition or clustering columns; avoid functions on the filtered column

Show freshness on the page: a small card with select max(loaded_at) from marts.fct_orders.

Dashboards as code

• Metabase: serialization export/import (metabase export / import CLI) is available on paid plans; on open source, script the REST API (/api/card, /api/dashboard) and store the JSON in git.
• Superset: export dashboards as a ZIP of YAML (UI or superset export-dashboards) and import via CLI or API; keep the YAML in git and review diffs.
• Looker Studio has no first-class code export; document data source SQL and calculated fields in the repo.

Pushing changes to a shared BI instance through the API overwrites what others see. Show the diff and get explicit approval before importing.

Gotchas

• Mixing grains in one chart (daily sessions with monthly targets) without aligning.
• Distinct counts are not additive: weekly active users is not the sum of daily actives. Compute them at the displayed grain.
• Time zones: the warehouse is UTC, the business reports in local time; truncate dates after converting.
• Row-level security and permissions: a dashboard shared to a group exposes every column in its datasets to that group’s SQL access. Check what the group can query.
• Test and internal accounts inflate small numbers; filter them in the model, not per card.

Verification

• Reconcile each headline KPI with its owner’s source (finance close, product analytics) for one complete past period; differences above an agreed tolerance block release.
• Change every global filter and confirm every card responds or is labelled as fixed.
• Load the dashboard cold and warm; note load times.
• Walk through each stated question and confirm it is answered without leaving the page.

Delivery template

**Dashboard:** Weekly Commerce Review (Metabase, collection /Exec)
**Questions:** 1) Are we on track for monthly net revenue? 2) Which channels drive change? 3) Is conversion moving?
**KPIs:** net revenue, orders, AOV, conversion rate, active customers (definitions: docs/metrics.md)
**Sources:** marts.fct_orders, marts.fct_sessions (dbt, refreshed 06:00 UTC daily)
**Reconciled:** September net revenue 1,284,113 EUR vs finance 1,284,090 (0.002%)
**Performance:** cold 4.1 s, cached 0.6 s
**Pending approval:** publish to /Exec and replace the old "Revenue v2" dashboard